1

I have imported a .csv with 60 million rows and I want to index them for faster queries. If I need to add a column with automatically generated unique identifiers, how can I do that? It's my first time with SQL Server.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Kaushal Kumar
  • 9
  • 1
  • 2
  • Use Identity property on a new column of data type INT, GUID is 16 bytes data type and not the best option for a unique identifier column. INT is only 4 bytes and sequential and much faster for SQL Server to work with. – M.Ali Aug 12 '18 at 20:21
  • Use [IDENTITY](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property). – JohnyL Aug 12 '18 at 20:24
  • Thanks for the answer. As I am very new to this and your answer leaves me with no clues. Could you point me to a article with step by step instructions. – Kaushal Kumar Aug 12 '18 at 20:29
  • 1
    See: @marc_s' answer https://stackoverflow.com/a/4862427/880990 – Olivier Jacot-Descombes Aug 12 '18 at 20:32

1 Answers1

3

Now, after importing data, you have a table with some columns.

If you want to add an index, you can index already existing columns, see CREATE INDEX (Transact-SQL) for reference.

But, if you want to have some ID column, you have to first add it to your table using following command:

ALTER TABLE my_table
ADD ID INT IDENTITY(1, 1) PRIMARY KEY

Which will automatically number your existing records starting with 1, make it primary key, which automatically make it an clustered index.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • If you want to do this using the Management Studio instead of writing Transact-SQL code, have a look at [this doc](https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-2017). – BobRodes Aug 12 '18 at 20:35
  • Hi friends, I liked the answers, however I can not upvote it as my reputation is less than 15, probably because I am a new user.. – Kaushal Kumar Aug 14 '18 at 18:14
  • @KaushalKumar If my answer helped, you should accept it by checking green mark on the left. – Michał Turczyn Jan 30 '19 at 22:14