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