Home > DeveloperSection > Blogs > Usage of $ROWGUID and $IDENTITY in SQL Server

Usage of $ROWGUID and $IDENTITY in SQL Server


Database Database 
Ratings:
1 Comment(s)
 7587  View(s)
Rate this:

$ROWGUID

The point of setting the ROWGUIDCOL property is to enable you to use $ROWGUID instead of the column name in a SELECT list. That means we can use $ROWGUID for access ROWGUIDCOL from table if ROWGUIDCOL is used.

Demonstration

--Create Table

CREATE TABLE MYTABLE

(

[ID] UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),

[NAME] VARCHAR(MAX)

)

-- UNIQUEIDENTIFIER DataType support 16 bit binary value, combination of 0-9 and A-F

-- PRIMARY KEY is used for uniquely identify of column values

-- DEFAULT is used for auto generated default string

-- NEWID() function is used for generated non sequential(randomly) id

--Insert Record

INSERT INTO MYTABLE ([NAME]) VALUES ('A')

INSERT INTO MYTABLE ([NAME]) VALUES ('B')

INSERT INTO MYTABLE ([NAME]) VALUES ('C')

INSERT INTO MYTABLE ([NAME]) VALUES ('D')

DROP TABLE MYTABLE

--Fatch record of UNIQUEIDENTIFIER

SELECT ID FROM MYTABLE

Screen Shot

Here we see, if we want to Fetch UNIQUEIDENTIFIER column record than we have know about column name. if we want to access UNIQUEIDENTIFIER column record without using column name, than use ROWGUIDCOL and  $ROWGUID.

CREATE TABLE MYTABLE_2

(

[ID] UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID(),

[NAME] VARCHAR(MAX)

)

-- UNIQUEIDENTIFIER DataType support 16 bit binary value, combination of 0-9 and A-F

-- ROWGUIDCOL property is to enable you to use $ROWGUID instead of the column name in a SELECT list

-- PRIMARY KEY is used for uniquely identify of column values

-- DEFAULT is used for auto generated default string

-- NEWSEQUENTIALID() function is used for generated sequential id

--Insert Record

INSERT INTO MYTABLE_2 ([NAME]) VALUES ('A')

INSERT INTO MYTABLE_2 ([NAME]) VALUES ('B')

INSERT INTO MYTABLE_2 ([NAME]) VALUES ('C')

INSERT INTO MYTABLE_2 ([NAME]) VALUES ('D')

DROP TABLE MYTABLE

--Fatch record of UNIQUEIDENTIFIER

SELECT $ROWGUID FROM MYTABLE_2

Screen Shot

$IDENTITY

If  we don't know the IDENTITY column name but wanted to display its column values or Without mentioning the IDENTITY column name how to list all the IDENTITY values in a table.

Syntax

--Syntax

SELECT $IDENTITY FROM <TABLE NAME>

Example

-- Create Table

CREATE TABLE IDENTITY_DEMO

(

[ID] INT IDENTITY(1,1) PRIMARY KEY,

[NAME] VARCHAR(20)

)

-- Insert Values

INSERT INTO IDENTITY_DEMO([NAME])

SELECT 'A'

UNION ALL

SELECT 'B'

UNION ALL

SELECT 'C'

 --Fetch Identity Column Record

SELECT $IDENTITY FROM IDENTITY_DEMO

Screen Shot


$ROWGUID

By Carl Pieterson on   3 years ago
nice blog.

Don't want to miss updates? Please click the below button!

Follow MindStick