Tuesday, 9 April 2013

INDEXES


Difference between a clustered and a non clustered index-

Clustered Index
  • Only 1 allowed per table
  • Physically rearranges data in the table to conform to the index constraints
  • For use on columns that are frequently searched for ranges of data
  • For use on columns with low selectivity
Non-Clustered Index
  • Up to 249 (for SQL Server 2005) and 999 (for SQL Server 2008) allowed per table
  • Creates a separate list of key values with pointers to the location of the data in the data pages
  • For use on columns that are searched for single values
  • For use on columns with high selectivity




Find Table without Clustered Index – Find Table with no Primary Key


USE master ----Replace AdventureWorks with your DBName
GO
SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO


No comments:

Post a Comment