question

Indexes are part of the data in SQL Server. They are stored in .mdf-file (or .ndf-files if you have more than one datafile for your database) along with the data. Indexes are there to assist SQL Server in finding specific rows or ranges of rows in your tables. I find a phonebook a good example of indexes. In a phonebook, all the data is physically sorted by lastname, firstname. In a database table, you have a clustered index to sort the actual data pages. So if you create a clustered index on your table (you should have one), your data gets sorted on disk. There can be only one clustered index per table. If you think about the phonebook, that makes total sense. A phonebook cannot be sorted in more than one way. At the end of the phonebook, one can usually find a address register, with references to posts in the phonebook. In SQL Server, that's a nonclustered index. Just like in the phonebook, a non clustered index in SQL Server keeps the clustered index key for the page it references on its lowest level. That means, the space needed for the leaf level of the clustered index is multiplied by as many non clustered indexes as you have in your table. Therefore, when choosing clustered index, one should think about how much space the clustered index needs. Indexes **do** take up space in your database. Indexes **might** slow down some operations in your database (mainly inserts and sometimes updates). Indexes needs to be updated or inserted to when a row is updated or inserted. That's both a read operation to find the index row and a write operation to update or insert it. On the other hand, your indexes will (if well designed) help you find your rows. If you have a table with names and phonenumbers, and you want to find out who has the phonenumber 555-1234, you will want your table to have an index on phonenumber. Otherwise, SQL Server will have to scan the whole table to find out who has 555-1234. In that case, you are glad you have an index on phone number. On the other hand, if your application never searches on phone number, but updates and inserts lots of rows every day, you'll loose some performance because SQL Server needs to maintain the index tree and the pointers in the phonenumber-index. That's the long version. The short version is: There are few general truths about indexes. If you gain or loose performance depends on how you choose your indexes and how you query your database. For more specific answers, ask more specific questions.