Mar 24, 2014

Pseudo columns in sql server


In sql server there are two types of pseudo columns

1. $IDENTITY
2. $ROWGUID

We can think these pseudo columns as alias of identity columns and ROWGUIDCOL columns respectively of any table.

$IDENTITY:

To use this column table must have identity column. For example we have created a table as follow:

CREATE TABLE tblUser(
    ID BIGINT IDENTITY,
    [Name] VARCHAR(100)
 )

INSERT tblUser VALUES('Scott'),('Greg')

Now executing following sql query:

SELECT $IDENTITY,[Name] FROM tblUser

Output:

ID
Name
1
Scott
2
Greg

$ROWGUID:

To use this column table must have ROWGUIDCOL column. For example we have created a table as follow:

CREATE TABLE tblUser(
    ID UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT(NEWID()),
    [Name] VARCHAR(100)
 )

INSERT tblUser(Name) VALUES('Scott'),('Greg')

Now executing following sql query:

SELECT $ROWGUID,[Name] FROM tblUser

Sample output:

ID
Name
F3F1DEA5-3F06-4A2A-B12F-10E9157F037C
Scott
73CF2944-5682-4741-98C1-29F5F2B2A9F1
Greg

No comments:

Post a Comment