-1

I've been having trouble figuring out how to add a simple integer-index column to an existing table in a SQL Server database. The closest question I've been able to find on StackOverflow isn't as simple as my question.

I'm trying to figure out how to turn this:

enter image description here

into an actual column.

I've seen this: 

CREATE INDEX idx_SomeColumn
ON SomeTable (SomeColumn);

but I do not understand the significance of the SomeColumn reference and the database is shared so I don't have much leeway to test.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob Truxal
  • 5,856
  • 4
  • 22
  • 39
  • You want a row number? Does it need to be stored in the table? Or just displayed when you query the table? – Dale K Dec 18 '18 at 22:27
  • Stored in the table – Rob Truxal Dec 18 '18 at 22:28
  • Do you care what order its allocated in? – Dale K Dec 18 '18 at 22:29
  • 1
    `alter table TableName add ColumnName int identity(1,1)` https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017 – Dale K Dec 18 '18 at 22:31
  • Do you mean you need a Row Number column? Like the one, you highlighted in the picture? If so, you may take a look at Row_Number() function https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 – KRM Dec 18 '18 at 22:33
  • Possible duplicate of [Adding an identity to an existing column](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) – GMB Dec 18 '18 at 22:45
  • @GMB - My question is more rudimentary than the question you're citing. – Rob Truxal Dec 18 '18 at 22:59

1 Answers1

3

First of all you need new column populated with id.

DECLARE @id INT 
SET @id = 0 
UPDATE YourTable
SET @id = ID_COL = @id + 1 
GO

then you need to create index on it

CREATE INDEX idx_Id_Col
ON YourTable (Id_Col);
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72