24 March 2011

MS SQL RowID equivalent

Hello everyone,

Many MS SQL developers ask if there is an equivalent to RowID from ORACLE in MS SQL.

The answers is NO.

But there is a workaround. You can use ROW_NUMBER() instead and where you will need to specify based on which columns to generate RowID, the order in which the ROW_NUMBER value is assigned to the rows in a partition.

Here is an example:

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode
FROM Sales.vSalesPerson

You can see more details on Microsoft's page about ROW_NUMBER() function: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Simple, isn't it?

1 comment:

metalkonik said...

Simple but slow, wrong and dangerous for your data. Without opportunity of giving 100% sure order_by_clause you can loose your data when for example updating rows with row_number(). Will not recommend using this.

Post a Comment

your thoughts are welcome:

Need more? Leave comments and subscribe to my blog.