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
Get the all the tables on the server which do not have 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 Related content
Tables Without Clustered Indexes
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
Where Clustered Index Keys Dare
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
Performance Myths : Clustered vs. Non-Clustered Indexes
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,987 reads