Saturday, April 10, 2010

Create Clustered Index Script

I had this requirement where we identified that a no of tables in AXapta were missing the clustered index which was resulting in some performance issues. It was then recommended by Microsoft to create clustered indexes on all these tables this is what we did.

--list of all tables missing an clustered index
select SO.name, SI.*
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'



--script to generate the script
select 'create clustered index [IDX_' + SO.name + '] on [' + SO.name + '] (recid, dataareaid )'
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'




another important query is to list all the columns in a index in this case clustered index
--list of columns in the clustered index
select OBJECT_NAME(SI.id) TableName, SI.name IndexName, SIK.indid IndexId, SC.name ColName
from sysindexes SI
inner join sysindexkeys SIK
on SI.id = SIK.id
and SI.indid = SIK.indid
inner join syscolumns SC
on SC.id = SIK.id
and SC.colid = SIK.colid
where SIK.id = OBJECT_ID( 'ContactMapping' )
and SIK.indid = 1
order by SIK.indid, SC.colorder

2 comments:

Anonymous said...

Sweet blog! I found it while browsing on Yahoo News.
Do you have any suggestions on how to get
listed in Yahoo News? I've been trying for a while but I never seem to get there!
Thanks

Also visit my web blog - Click Here
my page - Go to my website

Anonymous said...

Doh! I was domain searching
at namecheap.com and went to type in the domain name: http://www.
blogger.com/comment.g?blogID=31846650&postID=2322735421051253758
and guess
who already had it? You did! haha j/k. I was about to shop for this domain name but realized it was taken so I
decided I'd come check it out. Wonderful
blog!

Feel free to surf to my blog - View here