Every table can have one and only Clustered Index because index is built on unique key columns and the key values in data rows is unique. It stores the data rows in table based on its key values. Table having clustered index also called as clustered table.
There are 2 modes for non-clustered indexes, Non-unique and unique. Non-Unique means that the index does not act as a constraint on the table and does not prevent identical rows from being inserted. Unique constraints mean that the index prevents any identical rows from being inserted.
There are few drobacks of indexes. While indexes provide a substantial performance benefit to searches, there is also a downside to indexing.
Indexes and Disk Space
Indexes are stored on the disk, and the amount of space required will depend on the size of the table, and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users. To see the space required for a table, use the sp_spaceused system stored procedure in a query window. EXEC sp_spaceused Customers
name rows reserved data index_size unused
Customers 91 200 KB 24 KB 176 KB 0 KB
From the above output, the table data uses 24 kb, while the table indexes use about 18 times as much, or 176 kilobytes. The ratio of index size to table size can vary greatly, depending on the columns, data types, and number of indexes on a table.