Archive for the ‘Denali’ Category

RowID or RowNumber for each rows in SQLServer!

April 15, 2012

Yesterday when I was interviewing candidates for one of our requirement, I asked a simple select query to write that “how to get, only the last two rows of an Employee table?”

The answer were ( only from few 😦 ) : SELECT TOP 2 * FROM Employee ORDER BY EmployeeId DESC (Provided EmployeeId INT, PrimaryKey)

Then, just i altered the question with “we don’t have an “INT” datatype for EmployeeId column, but UniqueIdentifier (GUID) as well, so now how would you get the last 2 rows?

Some of them were tried to use ROW_NUMBER(), but as that requires OVER(ORDER BY <Column> DESC), and the primary key is with GUID, so they got blocked!

But here is one solution I had, and with that I was asking them: 🙂

SELECT TOP 2 *, %%physloc%% AS PhysLoc FROM Employee ORDER BY PhysLoc DESC

Ref:

http://www.codeproject.com/Articles/159785/Physical-location-of-a-row-in-SQL-Server

 

DENALI (SQLServer 2012):

The special thing is that, we have got the cool and long waiting stuff  “SEQUENCE” in DENALI as it is in ORACLE. That helps great for the above question, with the elegant solution.

Here you go…

CREATE SEQUENCE RowId
    AS int
    START WITH 1
    INCREMENT BY 1 ;

SELECT TOP 2 *, NEXT VALUE FOR RowId AS NewId FROM Employee ORDER BY NewId DESC

Ref:

http://www.sqlservercentral.com/articles/sequence/88422/

http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx

 

Hope this helps!