Assuming I have a multicolumn index in a table and the rows in the index are guaranteed to be unique (one of the later columns is the IDENTITY of this table) is it better to create the index with or without ISUNIQUE? The table is often queried and very seldom modified.
I would think that with the ISUNIQUE=yes the index could be stored more efficiently, but what do I know.
Not sure what you mean 'the index could be stored more efficiently'. For unique index, sql will check values when update those columns. Otherwise, will not check.
I'm talking about the internal structure of the index. For a unique index the m-n tree, hash or whatever MSSQL decides to use may point directly to the individual rows. For a non-unique it has to point to lists of rows with the same value of the indexed columns instead.
Is the column combination really unique?. If it is then you should mark it unique, it will help optimizer to get better query plans.