Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

SQL : How to get count of duplicate records?

In the following article we will go through a solution to a very common requirement of getting count of duplicate rows from SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column....

APEX SQL

APEX SQL APEX in SQL is a product from Microsoft that is used for synching of data stored on different servers. We have our main data stored in a main server and replica of those data are stored on different servers. Consider...

Temporary Tavles in SQL

Temporary tables in SQL are used for manipulation of data for a short period of time. It can be used as a accumulator like we have in microprocessor which acts as mediator to store and perform operations into it. Temporary table in ...

SQL : How to get Nth record in SQL Table ?

In the following article we will go through a solution to a very common requirement of getting Nth record in a SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column. CREATE TA...

SQL : How to find all tables containing column with specific name ?

Many times we come across a requirement of finding names of all tables which contain specific columns. We can get the answer by using the below query: SELECT COL.name AS ColumnName, TAB.name AS TableName FROM sys.columns COL JOIN sys.tab...

SQL : Difference between UNION and UNION ALL

Both UNION and UNION ALL operators are used to combine the results of two or more SELECT statements. However the two differ as below: 1) UNION performs a DISTINCT on the result set, removing any duplicate rows.There is a performance hit when ...

Endpoints in SQL Server

Endpoints in SQL For providing TCP/IP communication between your SQL and other applications you need to create end point for that . Creating end points is a series of steps Message Contract Queue Service Route...

CTE in SQL

CTE Common Type Expression For providing pivoting in tables say you want to view your data in such a form such that rows comes in form of columns and columns in form of rows. For doing that SQL provide facility to use CTE in SQL Ex : Suppo...

SQl Server : How to insert results of a stored procedure to a table?

In the following article we will learn how to insert results of a stored procedure to a table. There are two approaches to do this depending on whether we know the table schema beforehand or not. We will be using the following stored procedure t...

SQL Server : How to fetch records in one table that are not present in another table?

In this article we will see how to fetch records in one table that are not present in another table. First let us create two tables which will be used for illustration purpose: CREATE TABLE Table1 ( ID INT ) GO CREATE TABLE Tab...

SQL Server : How to insert values in identity column programmatically?

Identity field is typically used as a primary key in database. When a new record is inserted into a table, this field automatically assigns an incremented value to this column.However, inserting our own value into this column is not straightforwa...

SQL Server : How to truncate a table being referenced by a FOREIGN KEY constraint ?

Many times we need to truncate a table which has an FK constraint on it. Typically we get the following error: Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint. The solution to the above problem i...

SQL Server : How to shrink or clear the transaction log file ?

Many times the large size of transaction log file (.ldf) in Microsoft SQL is too big which leads to performance issues and loss of valuable disk space.Therefore it's imperative to periodically do database maintenance. In order to clear or ...

SQL Server : Comparison of stored procedure and UDF

Stored Procedures are pre-compile database objects which are compiled for first time and execution plan is saved for further use. However UDF is compiled and executed every time when it is invoked.This article provides a detailed comparison of st...

SQL Server : Use of sysobjects table

SQL Server sysobjects Table contains one row for each object created. In other words, it has a row for every constraint, default, log, stored procedure etc in the database.The two columns most useful in this table are sysobjects.name and sysobje...

SQL Server : Instead of Trigger

A trigger is a special kind of a store procedure that executes in response to specific actions on the table like insertion, updation or deletion. It is a database object which is bound to a table. Triggers cannot be explicitly invoked. The only ...

Schemas in SQL

Schema or Structure can be defined in SQL A schema is a collection of logical structures of data objects. A schema is associated with a database user and has the same name as that user. Each user have a single schema. Schema is mainly collecti...

SQL Server Management Data Warehouse (MDW)

Management Data warehouse is a database containing a warehouse of data useful for managing SQL Server. Ensuring the excellent and high performance for all the users and customers is a high priority for Database Administrators. Troubleshooting SQL...

Compression and Decompression in SQL Server 2016

In the upcoming version of SQL 2016 a new component of COMPRESS and DECOMPRESS T-SQL functions is added. SQL Server 2016 provides built in functions for compression and decompression.The COMPRESS and DECOMPRESS functions can store and retrieve da...

Recovery Models in SQL server

There are three types of Recovery model in SQL server Simple recovery model -- gives you the backup to replace the entire database in the event of a failure . In simple recovery model data is recoverable only to the most recent backup ....

Exporting SQL Server table data to Excel

Hi Readers ! In this Blog we will find how we can Export SQL Server table data to Excel using .net code . using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.Web...

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

In most of our applications, We need last inserted identity value in SQL Server table. For getting the last inserted identity value we have multiple options: @@IDENTITY SCOPE_IDENTITY IDENT_CURRENT All these three return the l...

Overview to Common Table Expressions(CTE) in SQL SERVER :

An Overview to Common Table Expressions(CTE) in SQL SERVER : Common Table expressions or CTE helps in making complex sql statements easier to understand and maintainable.         It is very difficult to read or wri...

How to set default value of column with datatype varchar to empty string

We usually face this kind of issue when we are doing programing with respect to a column with varchar data-type. If we don't add any value to a particular column it takes it as NULL. Thus forcing the developer to check for NULL every-time other t...

Finding Distance on basis of Longitude and Latitude from Database

Below is an example of finding all the user who are stored in db and lies in the given range of Distance on basis of Longitude and Latitude. CREATE proc [dbo].[SP_UserByDistance] ( @UserID uniqueidentifier,--UserId @Date datetime, --Date @...

Inserting data from one table to another

Inserting Data from one database table to different Database table. we can insert or copy data from one database table to another table of different database in SQL Server 2008 using following query keeping one thing in mind that the datatype...
prev 1 2 next
Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: