Insert into identity columns

Identity field is usually used as a primary key. When you insert a new record into your table, this field automatically assign an incremented value from the previous entry. Usually, you can’t insert your own value to this field.

Let’s consider the table TableName:

Reseed the identity field:

Compare Schema of Two SQL Server Databases

Note to myself: source

SQL Server performance queries

If a popular query does not have an associated index it will have a large impact on performance. This script identifies the queries without an index.

Here are some links with scripts that can help.

1. Find queries with missing indexes

2. Find Most Commonly Used Indexes

3. Find Long Running Queries

Identify Table Fragmentation

Note to myself: source

Compare Data in SQL Server Databases

Note to myself: source

Move inactive rows to achive table via SQL statements

Check this link if you want to generate automated migrated scripts:
http://eyearchive.codeplex.com/
or create your own migration scripts.

Let’s consider the two tables:

We need a clear, transactional way to move rows from the active to the inactive tickets table.

Solution 1:

Solution 2:

How to reset the identity column of a SQL table

To reset the value of the identity column in a Microsoft SQL table run the below query:

You will need to change TableName to the name of the table you wish to reset. This query will reset the identity column to 0, meaning the next row will have the identity 1. You can change 0 to whatever identity you wish to start from.

Posted in Sql

Count and remove duplicate rows

Let’s consider the Customer table rows:

Customer:

FirstName nvarchar(50)
LastName nvarchar(50)

Some records:

FirstName LastName
AAAAA XXXX
AAAAA XXXX
AAAAA XXXX
AAAAA XXXX
AAAAA XXXX
BBBBB YYYY
BBBBB YYYY
BBBBB YYYY
CCCCC ZZZZ
CCCCC ZZZZ

In order to find duplicate rows from your table (eg. Customer) you can use this snippet:

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. For table in example I added CustomerID as Identity Column and Columns which have duplicate data are FirstName and LastName.