New Indexes - Schema Extension vs SSMS (v8.0)

Hi Experts,

I need to create some new indexes and stats on a few tables, and wanted to know is there a difference if these are created via the schema extension tool compared to regular SQL statements executed in SSMS?

The reasoning here is that it I already have a lot of index SQL code ready to go and if I'm not restricted on using the schema extension tool then I can deploy these much quicker.

Thanks

Kin

  • Hi Kin,

    using the schema extension tool enforces some naming restriction specific to One Identity Manager. Also One Identity Manager does not support every index option SQL Server supports.

    If your indexes match the following restrictions, creating them by SSMS should be okay.

    • index name must not exceed 30 characters
    • index name should begin with CCC
    • index name must not contain Unicode characters or any character that requires the index name to be embedded in quotes or [ ]
    • index must be a relational index. (XML, SPATIAL, COLUMNSTORE, ... indexes are not supported)
    • the Index creation must not use any of the following syntax elements
      [ WHERE <filter_predicate> ]
      [ WITH ( <relational_index_option> [ ,...n ] ) ]
      [ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ]
      [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

    I doubt the creation of statistics is supported and save for migration. I'm certain that there are maintenance operations that are unaware of and will wipe out manually created statistics. 

    Best regards
    Andreas

  • Thanks a lot for the detailed answer Andreas, much appreciated