Search code examples
sql-serverrownum

Create column to constantly update with rownum in SQL Server

I have a table in SQL Server database. It has a column testrownum. I want to update the testrownum column to be rownum whenever row is created in the table automatically.

Is there any setting that I can turn on to achieve this?

Solution

  • Perhaps it is best to get a row number at the time you SELECT your data, by using the ROW_NUMBER() function.

    As others have pointed out (comments section) the data in a table is essentially unordered. You can however assign a row number for a certain ordering when you select data as follows (suppose a table that only has one column being name):

    SELECT
  name,
  rownr=ROW_NUMBER() OVER(ORDER BY name)
FROM
  name_table