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

  Modified On Sep-18-2014 01:24:04 PM

Leave Comment