It is generally mandatory to have clustered indexes in each of the tables. It helps in performance optimization, and in most cases it is the best starting point for a table.
Now the tedious job is to find the table names from existing database in which primary key (clustered index) is not implemented, the manual process is to right click on each of the table and go in design view to check it.
We can also do it by executing below T-SQL in the database; it will show the table names in which primary key are not implemented.
SELECT SCHEMA_NAME(o.schema_id) AS [schema], object_name(i.object_id ) AS [table], p.rows, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.OBJECT_ID INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.type_desc = 'HEAP' ORDER BY rows desc
Now the tedious job is to find the table names from existing database in which primary key (clustered index) is not implemented, the manual process is to right click on each of the table and go in design view to check it.
We can also do it by executing below T-SQL in the database; it will show the table names in which primary key are not implemented.
SELECT SCHEMA_NAME(o.schema_id) AS [schema], object_name(i.object_id ) AS [table], p.rows, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.OBJECT_ID INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.type_desc = 'HEAP' ORDER BY rows desc
No comments:
Post a Comment