patternMinor
Are indexes bloated if using nvarchar field sizes that are much bigger than needed?
Viewed 0 times
bloatedfieldneededmuchnvarcharbiggerarethanindexessizes
Problem
I've been provided with a database where it appears the designer has used much larger field sizes than are needed. Commonly nvarchar (128) fields have been used but the data being stored in them is nowhere near that size.
If I create an index on one of those fields, does the index reserve the whole field size or just use the number of bytes used by the actual value stored in that field?
If I create an index on one of those fields, does the index reserve the whole field size or just use the number of bytes used by the actual value stored in that field?
Solution
Aaron Bertrand's comments copied as an answer:
SQL Server just uses the actual number of bytes for storage, but take a look at your data size estimates - they'll be (half the declared byte size x number of rows) rather than (the average size x number of rows), and this can have a big impact on memory grants. Right-sizing is a good thing. These values can be found in the estimated data size (and any memory grant-related information) in a query plan.
SQL Server just uses the actual number of bytes for storage, but take a look at your data size estimates - they'll be (half the declared byte size x number of rows) rather than (the average size x number of rows), and this can have a big impact on memory grants. Right-sizing is a good thing. These values can be found in the estimated data size (and any memory grant-related information) in a query plan.
Context
StackExchange Database Administrators Q#126115, answer score: 2
Revisions (0)
No revisions yet.