SELECT INTO statement can be useful in the following scenarios.
1.Create a new table with same structure of the existing table
2.Copy all/some records from existing table to a new table
This leads to the question, will SELECT INTO copy the indexes and keys?.No.The new table created using SELECT INTO will not have indexes,primary key,foreign keys,default values and triggers.I find these reasons why Microsoft not implemented this feature of copying indexes in SELECT INTO:
i). Its not the best practice to create the indexes while creating the tables.You can determine the columns to be indexed while facing the serious performance issue.
ii).Indexes cannot be created in the same name like how column names are similar while copying a table using SELECT INTO.
But SSIS provides the solution to copy the table with the indexes.We will see how to implement this in the future post.When you want to copy the huge amount of data , better go for DTS or Import/Export wizard (make sure that you script out the table structure).It is certainly faster than SELECT INTO.
2 comments:
Magesh,
Thanks for the information.
I have some questions regarding indexes:
* What is the difference between clustered and non-clustered indexes?
* When specifying primary key, is the index automatically created? What about identity?
* Which columns should be indexed?
* I once read that you should not create index on tables that are frequently updated. Is this true or not, and why?
Ali , Answers here...
1. In Clustered index, the table is sorted on the index key.In Non-clustered index ,the data rows are not sorted and stored in order based on their nonclustered keys.For these reasons u can have only one clustered index per table and a table can have 249 non-clustered indexes.
2.Yes,a clustered index will be created when a primary key is created.Identity on a column specifies 'sequence number on that column'.This sequence number will be generated by the SQL server based on the given seed and indent.
3.When the application faces the performance issues,you need to index your columns,if it is not in place.Usually columns in the WHERE clause and JOIN conditions need to be indexed.SQL profiler will give you the good idea about the columns to be indexed.
4. Its true that frequently updated columns should not be indexed.Even though indexes improve the performance of the query,it takes the considerable amount of space in the DB.As i said earlier,index on a table defines how the data will be stored in a table.When you creatan index on the frequently updated columns , the UPDATE operation will affect the storage characteristics of the table like sorting the records etc.So it will result in performance degrade.
I guess , I answered your questions.But still INDEX in SQL server itself a big topic.:)
Post a Comment