CODEWITHSUNDEEP
sql-server-2008
Patrick
Patrick

Reputation: 2577

Does sql nvarchar length matter?

I'm looking at a table set up as nvarchar(50) The contents are only ever 2 characters or null. Is it important to change it? What's the optimal type if it's often used in an IN clause?

Upvotes: 0

Views: 699

Answers (2)

SqlZim
SqlZim

Reputation: 38023

I would use nchar(2) for unicode or char(2) for non unicode if the size is always 2 or null.

Using an oversized variable-width column impacts the memory grant and row size estimates, as SQL Server will assume that each row holds a value that is half the declared column size; while this may not be a significant factor with a width of 50 it makes a more noticeable difference on larger sizes.

Reference:

Upvotes: 2

Igor
Igor

Reputation: 62213

So why not use nvarchar(2) to ensure that only 2 characters ever are written? It's a constraint, there to ensure that the data conforms to an expectation.

Also if you know the values will only ever be ASCII then use varchar instead of nvarchar.

Finally, the length constraint will not have any affect on IN clause assuming that all the values never exceed the length of 2.

Upvotes: 1

Related Questions