Sql Server - Tips & Tricks
Requirement : Creating Identity Column on a table which already contains data.
Need: There may be various reasons. One possible reason could be to create an Index on view of a table.
Problem Described: In one of our project we have a table which contains around 30 million records. There are different reports where we display the records from the same table. Suppose I have ten different reports which uses different columns from the table. In order for better performance for such heavy table I must create appropriate indexes. But if we create ten different indexes including multiple columns it will again become performance bottleneck.
Solution Approached:
So we decided to create multiple VIEWs based on our reports on the same table and decided to create unique clustered indexes for those views. Since clustered indexes store data physically and will update the index on change in base table. It will boost application performance. But here comes another hurdle. Since our table does not contain a primary key as well as no unique column was introduced ( when that table was created ), it was not possible to create index on VIEWs since it requires unique column or key.
It is possible to alter table and add one more column as identity column but it will internally create another table and will move all records from current table to new table with values in identity column and finally delete the previous table. It was not possible because our table contains huge amount of data and we had less disk space for the table to be copied
For that the solution to used is as follows.
1. Alter the table(eg mytable) to add one extra column (eg newintcolumn) which should of type int.
2. use the following code to populate this column for existing data.
declare @cnt int
set @cnt = 1
update mytable set newintcolumn = @cnt , @cnt = @cnt+1
3. Alter the newintcolumn to identity.
This has solved my problem.
All Suggestion/Feedback highly appreciated.
Need: There may be various reasons. One possible reason could be to create an Index on view of a table.
Problem Described: In one of our project we have a table which contains around 30 million records. There are different reports where we display the records from the same table. Suppose I have ten different reports which uses different columns from the table. In order for better performance for such heavy table I must create appropriate indexes. But if we create ten different indexes including multiple columns it will again become performance bottleneck.
Solution Approached:
So we decided to create multiple VIEWs based on our reports on the same table and decided to create unique clustered indexes for those views. Since clustered indexes store data physically and will update the index on change in base table. It will boost application performance. But here comes another hurdle. Since our table does not contain a primary key as well as no unique column was introduced ( when that table was created ), it was not possible to create index on VIEWs since it requires unique column or key.
It is possible to alter table and add one more column as identity column but it will internally create another table and will move all records from current table to new table with values in identity column and finally delete the previous table. It was not possible because our table contains huge amount of data and we had less disk space for the table to be copied
For that the solution to used is as follows.
1. Alter the table(eg mytable) to add one extra column (eg newintcolumn) which should of type int.
2. use the following code to populate this column for existing data.
declare @cnt int
set @cnt = 1
update mytable set newintcolumn = @cnt , @cnt = @cnt+1
3. Alter the newintcolumn to identity.
This has solved my problem.
All Suggestion/Feedback highly appreciated.
Comments