Get the list of the tables on the server which dont have the clustered index.
"Surely this one will get a clustered index scan."
In which SQL Server stubbornly insists on doing key lookups way, way past the tipping point.
2019-01-04
2,905 reads
Mitesh Oswal,
2014-02-24 (first published: 2014-02-17)
Get the list of the tables on the server which dont have the clustered index.
--Verify if any table dnt have the cluster key DECLARE @tbl TABLE ( ID INT IDENTITY(1,1), DBName NVARCHAR(100), TableName NVARCHAR(100) ) DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ' SELECT ''?'',ts.Name FROM ?.sys.tables ts LEFT JOIN ?.sys.indexes si ON ts.object_id = si.object_id AND si.type = 1 INNER JOIN ?. sys.databases d ON d.Name=''?'' AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'') WHERE si.index_id IS NULL ' INSERT INTO @tbl EXECUTE sp_MSforeachdb @SQL select * from @tbl
In which SQL Server stubbornly insists on doing key lookups way, way past the tipping point.
2019-01-04
2,905 reads
When databases are being designed and developed, your developers might have overlooked creating clustered indexes on some of your database tables. Having a useful clustered index on your tables will improve the performance of your queries. Here Greg Larsen shows a simple script to identify those tables in your database that don’t have a clustered index.
2018-05-29
4,777 reads
We’ve blogged a couple times about how clustered index key columns get stored in your nonclustered indexes.
But where they get stored is a matter of weird SQL trivia. You see, it depends on how you define your nonclustered index.
2018-05-25
3,638 reads
Aaron Bertrand kicks off his "Performance Myths" series, showing a "redundant" non-clustered index outperforming the clustered index with the same key.
2017-04-17
5,314 reads
2015-08-06
1,931 reads