How to add Row_Number computed column to an existing table

Question

I'm using SSMS 17.4. I need to add a new column that uses Row_Number() function. But that syntax seems to be invalid

I tried three different ways, but none of them worked out.

ALTER TABLE MYDB.[dbo].[Table1]
ADD Rn INT NOT NULL AS (ROW_NUMBER() OVER (PARTITION BY [MyColumn] ORDER BY [MyColumn]));

ALTER TABLE MYDB.[dbo].[Table1]
ADD ROW_NUMBER() OVER (PARTITION BY [MyColumn] ORDER BY [MyColumn]) INT NOT NULL AS Rn;


ALTER TABLE MYDB.[dbo].[Table1]
ADD Rn INT NOT NULL ROW_NUMBER() OVER (PARTITION BY [MyColumn] ORDER BY [MyColumn]);

I also tried this following recommendation on this link

https://ss64.com/sql/table_a_computed.html

   ALTER TABLE MYDB.[dbo].[Table1]
ADD Rn INT NOT NULL ADD Rn AS ROW_NUMBER() OVER (PARTITION BY [MyColumn] ORDER BY [MyColumn]);

Answer 1

What you are trying to do is create a calculated column for a table that may change a number of other records, whenever a record is inserted, deleted, or updated. I do not believe any SQL engine will allow this considering the significant performance implications.

I would recommend creating a view instead that has the row number column you are looking for and then reference the view like a table. In this case it will be dynamically calculated whenever the view is referenced.