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

SQL SERVER - Follow up - Usage of $rowguid and $IDENTITY identidentity

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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – ColumnStore Index – Batch Mode vs Row Mode
Next Post
SQL SERVER – Introduction to Function SIGN

Related Posts

No results found.

3 Comments. Leave new

  • 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!

    Reply
  • 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 ?

    Reply

Leave a Reply

Menu