The cost of non-clustered indexes

Question

If I create a non-clustered index in a table, does the SQL server make a copy of the data in this table and save it separately? I'm just thinking about the cost of creating non-clustered indexes. I think that the one selected by this key used in the index will be faster, but all insertions, updates, and deletions will be slow, since the sql server will have to store two copies of the data. Do I understand correctly?

Answer 1

Data is not copied for non-clustered indexes. A "map" has been created (sometimes using only a full copy of the indexed column [s]) in order to quickly search for some queries in this field. For a basic guide to this, think of the B-Tree http://en.wikipedia.org/wiki/B-tree where the different nodes are in well-known places, and you can determine, based on the query, where to start looking. Yes, you will need to spend some resources on creating / maintaining a map ... but how much time do you spend searching?

SQL Server , ... , , ,

, / . . , , , .

Answer 2

SQL Server , , "", .

, / , , , Selects, . , / , , , /, .

, , . ( /) .

Answer 3

The answer also depends on whether you are using a coverage index that will contain a copy of some or all of the columns in the table. The article linked to above does an excellent job explaining the issue.