Mar 24, 2014

Invalid pseudocolumn "" sql server


Suppose we have created a tblUser User in sql server:

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

Now executing following sql query:

SELECT $ROWID,[Name] FROM tblUser

We will get error message:

Invalid pseudocolumn "$ROWID".

Cause:  In sql server there only two pseudocolumn
1. $IDENTITY
2. $ROWGUID

Here $ROWID is not valid pseudocolumn

Solution:

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