SQL SERVER – Follow up – Usage of $rowguid and $IDENTITY

The most common question I often receive is why do I blog? The answer is even simpler – I blog because I get an extremely constructive comment and conversation from people like DHall and Kumar Harsh. Earlier this week, I shared a conversation between Madhivanan and myself regarding how to find out if a table uses ROWGUID or not? I encourage all of you to read the conversation here: SQL SERVER – Identifying Column Data Type of uniqueidentifier without Querying System Tables.

In simple words the conversation between Madhivanan and myself brought out a simple query which returns the values of the UNIQUEIDENTIFIER  without knowing the name of the column. David Hall wrote few excellent comments as a follow up and every SQL Enthusiast must read them first, second and third. David is always with positive energy, he first of all shows the limitation of my solution here and here which he follows up with his own solution here. As he said his solution is also not perfect but it indeed leaves learning bites for all of us – worth reading if you are interested in unorthodox solutions.

Kumar Harsh suggested that one can also find Identity Column used in the table very similar way using $IDENTITY. Here is how one can do the same.

DECLARE @t TABLE (
GuidCol UNIQUEIDENTIFIER DEFAULT newsequentialid() ROWGUIDCOL,
IDENTITYCL INT IDENTITY(1,1),
data VARCHAR(60) )
INSERT INTO @t (data) SELECT 'test'
INSERT INTO @t (data) SELECT 'test1'
SELECT $rowguid,$IDENTITY FROM @t

There are alternate ways also to find an identity column in the database as well. Following query will give a list of all column names with their corresponding tablename.

SELECT
SCHEMA_NAME(so.schema_id) SchemaName,
so.name TableName,
sc.name ColumnName
FROM sys.objects so
INNER JOIN sys.columns sc
ON so.OBJECT_ID = sc.OBJECT_ID
AND sc.is_identity = 1

Let me know if you use any alternate method related to identity, I would like to know what you do and how you do when you have to deal with Identity Column.

Reference: Pinal Dave (http://blog.sqlauthority.com)

3 thoughts on “SQL SERVER – Follow up – Usage of $rowguid and $IDENTITY

  1. Hi Pinal, excelent post!!!
    Just one request, indicate on each post which version of SQL Server you are working with, thanks for all the help!

    Like

  2. I’have problem
    every day i copy some data from excel sheet and past it in sql table for some how my data come not be sort , like if did finish at 17/7/2012 the next date it will be 18/7/2012,although the next date it will 2/1/2013 some thing like that .
    i fix this problem by copy the table to anther dbo (by order date asc) but this problem come again .could tell me what’s the problem and how i can fix it ?

    Like

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s