User Id :    Password :      New Member   Forgot Password  
Topic: Index in sql server
Anamika singh
13 Aug 2011 at 06:31 AM
Index in sql server?
Re: Index in sql server
Gaurav
14 Aug 2011 at 07:03 AM

Clustered Index
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.
Non-Clustered Index
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.
Index Drawbacks
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  
Result
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.

Total Replies: 1
 
Featured Text Ad

Featured Ad