RowID or RowNumber for each rows in SQLServer!

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!

Focus

Delete a TFS task

Simply i was trying to create a sample task in TFS, its done successfully. Then tried to delete from there as it is a live one , and everybody would be looking into it. But, searching the options in TFS for long, i felt tired and nervousness. Finally, started googling and got few forums with no direct stuffs told. So thought of giving the stuff simple and humble 🙂

First of all, we need the exe file Witadmin.exe for deleting the tasks in TFS.

Where it resides? –  C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE  (you have to check the folder, depending upon the system type you have)

Initially, I was searching underneath C:\Program Files (x86)\Microsoft Team Foundation Server 2010 Power Tools, and got confused.

Here is the list of available commands with Witadmin:

Possible options:

So, finally, your command would look like…

>witadmin destroywi /Collection:http://%5BServerName%5D:8080/tfs/id:%5BId%5D

Note: Text kept inside “[]” differs as its provided to you. Also provided the permission to delete 😉

Hope, this helps! 🙂