Post reply

IndexProperty and create Index

  • December 5, 2003 at 9:37 am

    #358696

    Indexproperty Function has this tow parmaters that can be suplied to It

    IsPageLockDisalowed and IsRowLockDisalwoed

    how Can It Be Applied to An Index and when

    Create Index Has this options on an Index

    DROP Existing ,STATISCS_NORECOMPUTE ,SORT_IN_TEMPDB,IGNORE_DUP_KEY

    Where IS This Information stored And How Can It Be Retrived To For Logging The Index Status And Later To Create IT

  • December 5, 2003 at 11:40 am

    #484892

    Apply the locking override properties with sp_indexoption.

    The creation options DROP_EXISTING and SORT_IN_TEMPDB are only relevant during creation, so they're not stored anywhere other than if you save the DDL.

    IGNORE_DUP_KEY is the 2^0 bit and STATISTICS_NORECOMPUTE is the 2^24 bit in SQL Server 2000 sysindexes.status; to find which indexes have those option set:

    

    SELECT name,

    CASE WHEN status & 16777216 = 16777216 THEN 'Yes' ELSE 'No' END 'STATISTICS_NORECOMPUTE',

    CASE WHEN status & 1 = 1 THEN 'Yes' ELSE 'No' END 'IGNORE_DUP_KEY'

    FROM sysindexes

    As this is undocumented by Microsoft, you should not rely upon it in future versions.

    The locking override options are stored in sysindexes.lockflags.

    --Jonathan

    Edited by - Jonathan on 12/05/2003 3:46:35 PM



    --Jonathan

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply