Nonclustered index count

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Nonclustered index count

Nonclustered index count

Rate Topic

Display Mode

Topic Options

Author

Message

luckysql.kinda

Posted Wednesday, July 22, 2009 12:34 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645

I want to find all the tables in the DB where I have clustered index and more than two non clustered indexes.

I can use following command:

SELECT object_name(id), count(1) as 'nonclustered index' FROM SYSINDEXES
WHERE INDID > 1
GROUP BY id
having count(1) > 1

But this doesn't give me the correct value. There are many NULL columns in the sysindex for all the tables. Need help.

Post #757165

GilaMonster

Posted Wednesday, July 22, 2009 1:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 37,687, Visits: 29,946

Since you're on SQL 2005, use sys.indexes rather than the old sysindexes. sys.indexes contains only indexes, sysindexes can contain the column statistics as well.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #757184

luckysql.kinda

Posted Wednesday, July 22, 2009 4:10 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 11:19 AM
Points: 309, Visits: 645

Thanks Gila. Is column statistics also needful? MS should remove sysindexes otherwise.

-Lucky

Post #757280

GilaMonster

Posted Wednesday, July 22, 2009 6:30 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 37,687, Visits: 29,946

luckysql.kinda (7/22/2009)

Thanks Gila. Is column statistics also needful?

What do you mean by 'needful'?

They're not a deprecated feature, they're essential for the optimal performance of the query engine.
Check Books Online for more details on statistics

MS should remove sysindexes otherwise.

The sysindexes view is deprecated and will be removed in a future version of the product. It's only included for backward compatibility with SQL 2000

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #757355

« Prev Topic | Next Topic »

Permissions