none
ROW_NUMBER for a computed column

    Question

  • Hi,

    I know that I cannot use the ROW_NUMBER function for a computed columns. Is there an alternative manner to add a row numbet respect to an order/partition? I don't want to use an identity column.

    Thanks


    • Edited by pscorca Friday, July 26, 2013 9:28 AM
    Friday, July 26, 2013 9:28 AM

Answers

  • Hi, the code that I'd like to use as a formula for the computed column is:

    ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) FROM MYSTGTABLE

    Obiously, using the Select statement I can write:

    SELECT *, ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) as RowNum FROM MYSTGTABLE

    I'd like to have the row number inside the staging table in order to do not calculate it with a query.

    Materializing the row number in the table is problematic.  I think a view is the right tool for this job:

    CREATE VIEW dbo.VIEW_MYSTGTABLE AS
    SELECT 
    	  [YEAR]
    	, [MONTH]
    	, ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) AS RowNum 
    FROM dbo.MYSTGTABLE;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 27, 2013 3:05 PM

All replies

  • There is no straight forward way for the same. You need to programatically acheive the same in your insert statement. However, the difficult task is that you may need to re-order the existing also.

    I would suggest if the significance of the ordering/partition at front end (presentation layer), it would be better if you can do it at the time of fetching the data.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Kalman TothModerator Friday, July 26, 2013 11:32 AM
    • Unproposed as answer by pscorca Friday, July 26, 2013 1:29 PM
    Friday, July 26, 2013 9:34 AM
  • If you post the code maybe I can help you, I had the same issue time ago...
    Friday, July 26, 2013 10:20 AM
  • Why do you need it in the table?

    You can generate it dynamically with ROW_NUMBER OVER PARTITION BY whenever needed:

    http://www.sqlusa.com/bestpractices2005/overpartitionby/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Friday, July 26, 2013 11:34 AM
    Moderator
  • If you post the code maybe I can help you, I had the same issue time ago...

    Glad to provide you the code perhaps you provide your table structure and sample data.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, July 26, 2013 11:39 AM
  • Hi, the code that I'd like to use as a formula for the computed column is:

    ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) FROM MYSTGTABLE

    Obiously, using the Select statement I can write:

    SELECT *, ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) as RowNum FROM MYSTGTABLE

    I'd like to have the row number inside the staging table in order to do not calculate it with a query.

    Thanks

    Friday, July 26, 2013 1:28 PM
  • Well, you can't do what you want.  And logically the value in any particular row depends on the presence of "similar" rows at the instant the value is "viewed".  At some point you will need to compute the value.  Whether you do this when rows are added/modified/deleted, as some sort of bulk update when the population of rows is considered complete, or at query time is your choice.  You don't have to do it every time you query the table, but you have to do it at least once. 
    Friday, July 26, 2013 5:30 PM
  • You can use Sequence object if you are using SQL Server 2012.

    http://msdn.microsoft.com/en-us/library/ff878091.aspx

    Friday, July 26, 2013 7:33 PM
    Moderator
  • still could not understand what exactly your requirement is?


    Best Luck, Shenoy

    Saturday, July 27, 2013 9:59 AM
  • Hi, the code that I'd like to use as a formula for the computed column is:

    ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) FROM MYSTGTABLE

    Obiously, using the Select statement I can write:

    SELECT *, ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) as RowNum FROM MYSTGTABLE

    I'd like to have the row number inside the staging table in order to do not calculate it with a query.

    Materializing the row number in the table is problematic.  I think a view is the right tool for this job:

    CREATE VIEW dbo.VIEW_MYSTGTABLE AS
    SELECT 
    	  [YEAR]
    	, [MONTH]
    	, ROW_NUMBER() OVER(ORDER by [YEAR], [MONTH]) AS RowNum 
    FROM dbo.MYSTGTABLE;
    GO


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 27, 2013 3:05 PM