Tuesday, June 28, 2011

SQL Server Indexes Fact Sheet

MS SQL Server

1. What is the maximum allowed size of index key in an index ( total size of columns used in the index definition) ?

900 bytes

2. How many indexes a table can have ?

 1 Clustered  and
999 non-clustered

3. How many columns can be combined in a composite index key ?
16

4. What is a heap ?
If there is no clustered index on a table, the table is called a heap.

5. How do I identify heaps and clustered indexed tables in my SQL Server database ?
The following query gives listing of clustered , non-clustered indexes and heaps :
select
(
join
(
on
order by b.type_desc ,a.name
a.id = b.object_id
select object_id , name , type_desc from sys.indexes ) B
select id , name from sysobjects where xtype = 'U' ) A
a.name , b.type_desc from

No comments:

Post a Comment