Reset Identity Column Value in MSSQL Server

One of those simple tips....

While setting up some test data on two different machines my row data became out of synch, a quick Google and "hey presto"

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.

 

DBCC CHECKIDENT('Product', RESEED, 0)

The SQL DISTINCT clause is case insensitive by default

Solution:  The COLLATE clause can make any string operation case sensitive.

The solution turned out to be pretty easy, use a COLLATE clause with a case sensitive collation.  Here's a sample query with both case senstive and case insensitve collations.

Case insensitive:

select distinct (Item) COLLATE sql_latin1_general_cp1_cs_as
    FROM (
           select  'abcd' item
 union all select  'ABCD'
 union all select  'defg'
 union all select  'deFg') items

 

Case sensitive:

All that is different in the next query is the name of the collation:

select distinct (Item) COLLATE sql_latin1_general_cp1_ci_ai
    FROM (
           select  'abcd' item
 union all select  'ABCD'
 union all select  'defg'
 union all select  'deFg') items