Worth a try - SQL 2005 - Included Columns and SIFT

Nobody

I just heard this from somebody who heard it from somebody at MS, no name so take it for what it is worth.

In SQL 2005 you there is a new feature where you can add columns to a clustered index as "Included Columns" that exist on the index page but do not require stats maintenance that the actual index columns do so they add far less overhead then actual indexed columns but speed up reading from the table because the fields exist on the index page and does not require a split query to go back to the clustered index to pull the field data for those columns.

Scenario
You disable all SIFT indexes on a table such as Value Entry and disable the maintain SQL index. Then you create a new nonclustered index in SQL with the key columns as the index columns and the SIFT columns as included columns to the index.

The table has a few million rows. with this configuration the SUM or CALCSUM reads from table are a few milliseconds slower but the INSERT, UPDATE, and DELETE statements are 100 times faster because there is no SIFT maint.

If this works you can see the potential performance and concurrency benefits.

Like I said it is just something I heard not even sure where it really originated from. Sounds like it is worth a shot though

NavStudent

this is the same thing I suggested in this thread. But blacktiger said it was stuppid idea.


http://www.mibuso.com/forum/viewtopic.p ... 3&start=15

CREATE INDEX IX_Table ON Table (Key_Index_Column) INCLUDE (Column1, Column2, Column3)

Maybe it is a solution that can be used instead of sift tables afterall.

Nobody

I think the real key is Judicous application and testing of the before implementing. I have ran a few simple scenarios on a large DB I have in-house and it seems to prove out so far for what it is worth.

kriki

[Topic moved from Navision forum to SQL General forum]