cost of non-clustered indexes

Question

If I create a non-clustered index on a table, does SQL server make a copy of data in that table and stores it separately? I am just thinking about cost of creating non-clustered indexes. I guess selected by that key used in index will be faster but all inserts, updates and delete will be slow as sql server will have to maintain two copies of data. Is my understanding correct?

Answer 1

SQL Server will not make a copy of all the data in the table, just the data contained in the columns of the index and any 'covering' columns, plus additional overhead data.

Yes, inserts / updates will be somewhat slower but the cost you potentially incur by not having indexes for Selects can far outweigh that. For the most part, unless you are inserting hundreds/thousands of rows per second, on a regular basis, you probably will not notice much impact on inserts / updates by having an appropriate number of indexes on a table.

We try to limit indexes on our production database but use far more indexes on our reporting database, which gets replicated from our production database. The overhead of having many indexes on a reporting databases (for inserts / updates) is not noticed.

Answer 2

The data is not "copied" for non-clustered indices. A "map" of sorts (sometimes using a full copy of the indexed column[s] only) is created to make lookups faster for some queries on that field. For a basic guideline to this, think of a B-Tree http://en.wikipedia.org/wiki/B-tree where the different nodes are at well-known locations and that you can determine, based on the query, where to start looking. Yes, you will need to spend some resources creating/maintaining the map... but how much time are you spending on searches?

The most fundamental difference between clustered and non-clustered indices in SQL Server is that a clustered index describes the physical storage order of the rows on the disk... this is why a sequential clustered index is usually preferred for good insert performance.

On the other hand, for non-clustered indices, you need to gauge the importance of search performance versus the cost of insert performance / disk space. I usually will use an index on any commonly searched field. If the same field is has very frequent inserts it becomes a bit more complicated but I've never personally had to deal with insert performance convincing me not to use an index.