Sift index and Sql index

Balello
Balello Member Posts: 61
edited 2007-08-27 in SQL Performance
I know that it's possible not to mantain SIFT indexing on SQL or not to mantain a sql index at all.
The question is: is it possible (and does make sense) to mantain sift index and not sql index. For example, consider Item Ledger Entry. I'm interested to mantain the Entry Type, Item No. sum, but not the corresponding key on sql, that's not "selective" on the first field.
To better explain, I think that a good key strategy is to mantain SIFT sums on "not selective" Navision indexes (but not create the SQL index) and mantain SQL indexes only on "selective" indexes (and not SIFT index on them, because of the small quantity of records to sum).
Does the idea make sense?

Thanks in advance.
I don't envy anybody who caught up his aim. I travel gladly.

Comments

  • DenSter
    DenSter Member Posts: 8,307
    It does make sense, and it is a good strategy. The index on SQL Server is something physically different than the SIFT index. In fact it shouldn't even be called SIFT Index, because it corresponds with a separate physical table on SQL Server.

    Try to figure out what the different key properties mean. Open any table in design mode, open the keys form, and make sure that you make all columns visible. Then, for each column, read what F1 help says.

    Open the SQL Server Management Studio (or Enterprise Manager for SQL Server 2000), browse to the table you are looking for, and see if you can figure out the link between NAV keys and SQL Server indexes. See what happens when you disable MaintainSQLIndex (don't forget the refresh SQL Server).

    As far as SIFT goes, when you enable MaintainSIFTIndex, it creates a separate table for the sumindexfields on SQL Server. See if you can figure out how that link works. Hint: <company name>$<table number>$<SIFT index number>
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • Balello
    Balello Member Posts: 61
    Thanks Denster. I knew the Sql Server option architecture.
    My question focus was on the "completely indipendent" use of the "mantain (SIFT or SQL) index" flag on key properties. And as you answer to me, not only that's possible but it's a good strategy too.

    To the next time,
    Enrico.
    I don't envy anybody who caught up his aim. I travel gladly.
  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from Navision forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories