January 9, 2009 at 2:56 am
Hopefully someone out there will have a solution to hand rather than reinventing a wheel. I tried looking at the SQL Server Central scripts location but nothing I saw covered this as far as I could see.
At our site, all the SQL Server applications are vendor supplied packages. In some cases the number of tables runs into the hundreds. In relation to a recent request, I noted a table from one vendor package, that not only had no clustering index, but had no indexes at all. So their resultant request, (a script to deliver some data changes, that needed to be run for each user (450) and change 12 values) was going to do rather a lot of table scans!
(Is that a Steaming Heap?).:D
Anyhow, given the mantra that any index might be better than no indexing, I was hoping someone had a script that could ferret out these non-clustered tables as a general task, with a view to pro-actively challenging the vendors on this design quality.
Thanks
January 9, 2009 at 5:21 am
Use this Script to find the Tables without any Primary Keys or Clustered Indexes!
SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK' )
January 9, 2009 at 5:42 am
Dugi (1/9/2009)
Use this Script to find the Tables without any Primary Keys or Clustered Indexes!
SELECT c.name, b.nameFROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK' )
That script is only going to find tables without a primary key. To find tables with no indexes you could do something like this:
[font="Courier New"]SELECT
*
FROM
SysObjects SO
LEFT JOIN SysIndexes SI ON SI.ID = SO.ID
WHERE
SO.Type = 'u'
AND SI.ID IS NULL[/font]
As far as having heaps being a bad thing, there are good arguments on both sides. I tend to try to find a good clustered index even on tables that really have no use for one, but it could be argued that it is better to have a heap if the table is getting constant inserts in a completely unordered way. In this case, any column you index is going to continuously fragment the index. A heap would avoid this, but it is not a very common problem.
You would really have to look at each individual table to determine if adding indexes is going to be good, bad, or change nothing.
January 9, 2009 at 7:45 am
Michael Earl (1/9/2009)
Dugi (1/9/2009)
Use this Script to find the Tables without any Primary Keys or Clustered Indexes!
SELECT c.name, b.nameFROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK' )
That script is only going to find tables without a primary key. To find tables with no indexes you could do something like this:
[font="Courier New"]SELECT
*
FROM
SysObjects SO
LEFT JOIN SysIndexes SI ON SI.ID = SO.ID
WHERE
SO.Type = 'u'
AND SI.ID IS NULL[/font]
As far as having heaps being a bad thing, there are good arguments on both sides. I tend to try to find a good clustered index even on tables that really have no use for one, but it could be argued that it is better to have a heap if the table is getting constant inserts in a completely unordered way. In this case, any column you index is going to continuously fragment the index. A heap would avoid this, but it is not a very common problem.
You would really have to look at each individual table to determine if adding indexes is going to be good, bad, or change nothing.
I tested the script in Test DB where there are some table without any key or index and no results!
:w00t:
January 9, 2009 at 7:54 am
Sure, but try this:
[font="Courier New"]CREATE TABLE MyTable (ID INT)
GO
CREATE CLUSTERED INDEX idxMyTable_CL_ID ON MyTable(ID)
GO
CREATE NONCLUSTERED INDEX idxMyTable_ID ON MyTable(ID)
GO
SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK' )[/font]
The table MyTable will have both a clustered index and a non-clustered index and still show up in your result set - because it has no constraints. The original post was looking for tables without indexes.
January 9, 2009 at 7:58 am
Thanks for the offerings.
In terms of heaps, you are right in terms of what sort of data is being applied. In the specific case I rerfered to, the table has 70K rows and the 'key' (not PK) is effectively an identity column. On these I have views as to whether a PK on an identity column should necessarily be the Clustered index or (dependant on the requirements of the app), some other column(s).
With both scripts so far, to determine tables that are index-less heaps or indexed-heaps is both interesting and relevant so the prompt response is both useful and very much appreciated.
As always, I find this site full of eager contributors so keep up the good work!
January 9, 2009 at 7:59 am
Michael Earl (1/9/2009)
Sure, but try this:[font="Courier New"]CREATE TABLE MyTable (ID INT)
GO
CREATE CLUSTERED INDEX idxMyTable_CL_ID ON MyTable(ID)
GO
CREATE NONCLUSTERED INDEX idxMyTable_ID ON MyTable(ID)
GO
SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = 'U'
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = 'PK' )[/font]
The table MyTable will have both a clustered index and a non-clustered index and still show up in your result set - because it has no constraints. The original post was looking for tables without indexes.
Yes, Michael I can see that no results, but how to use your code to find them ..I tested MyTable without nothing ( no indexes no constraints no keys nothing at all) and still no results!
How do you explain that!?
January 9, 2009 at 8:21 am
Dugi - your query looks for tables without an entry in key_constraints - so a heap with no constraints will appear. However, you can have key constraints without an index or an index without key constraints. By default, the UI makes a PK constraint with a clustered index, but that does not have to be the case.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply