search

PolarClawGIT / DataDictionary

Data Dictionary Manager for MS SQL.
MIT License
0 stars 0 forks source link

Indexing size limitation #5

Open PolarClawGIT opened 10 months ago

PolarClawGIT commented 10 months ago

TSQL limits indexes 900 bytes for clustered index and 1700 for non-clustered index. SysName is 256 bytes (NVarChar(128). Three part (Database, Schema, Object) fits in this limitation. Four Part names needed for lower level detail violate the clustered index limit. Examples: columns, index column, parameter, constraint column.

This occurs in the Database Table Column and Database Routine Parameter tables because the all the name fields are carried in the detail table.

The fix is to switch to surrogate keys with indexed views to enforce the natural key. This will be similar to the structure used for Library where each table will have a GUID for the primary index and a second GUID pointing to the parent (Table -> Schema ...). An Indexed view will have a clustered index on the primary index GUID with a non-clustered index with all the component of the natural key.

The application will continue to index on the natural key. This makes the GET and SET functions have the additional complexity as they need to translate the natural key into the internal key.

PolarClawGIT commented 10 months ago

A solution has been worked out but it is taking time to implement.