SSMS T-SQL column for double row number
I have a database with approximately 10 million rows (and 20 columns - about 4 GB) where about 10% of the rows have a duplicate column. The database is in SQL Server 2014 Express and uses SSMS.
I created a new column CNT (int, null) to count the occurrences of each row where I have a duplicate ID. The desired output will look like this:
ID CNT
100 1
100 2
101 1
102 1
102 2
103 1
104 1
Not being familiar with the advanced features of SQL, I did some research and came up with using CTE to set a CNT column. Worked fine on a small test bench - but it was obvious that this was not the way to go for a large table (I killed it 5 hours later on a pretty decent system.)
Here is the code I tried to implement:
with CTE as
(select dbo.database.id, dbo.database.cnt,
RN = row_number() over (partition by id order by id)
from dbo.databasee)
update CTE set CNT = RN
The column identifier is of type Int. All columns are nullable - no keys or indexed columns.
source to share
Edit: Martin is right, I can suggest an alternative solution than CTE at the moment. Create a new table just like your old one and insert the old table data into it.
INSERT INTO newTable
SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)
FROM oldTable;
Then you can delete your old table. Definitely not a perfect solution, but it should work.
source to share