NVarchar field sizes

Question

NVarchar field sizes

ErwinB

SSC Enthusiast

Points: 180
More actions
April 30, 2020 at 11:10 am

#3747618

Hi,
In my database I have a table with product information. The number of records in the table varies from 10.000 to max 50.000. There are approx. 20 text fields that are defined as NVarchar. There are no indexes on these fields. Activities on the table are in most cases less than 1000 times access per day, and are primarily SELECT and INSERT, and the occasional UPDATE.
The app that accesses the database does allow each text field to be 255 characters long, even though in most cases the texts will be more like 30-60 characters max, and some fields may even be empty.
I was wondering if there is any downside in defining all the text fields in the table as NVarchar(255) or even NVarchar(500) instead of a mixture of NVarchar(20), NVarchar(50), etc. with the risk that a user may experience a failed save or data truncation?
Thanks,
Erwin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What you say is a risk can actually be seen as a benefit. If the data is too large for the column, and the column is properly defined, you want the insert to fail. It's about understanding your data - define each column for the maximum permissible length. It's another line of defence against bad data.

John

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 2

Recommendations: keep it within the applications specs.

Downside of having nvarchar too big:

More memory reserved in applications / sql server for retrieval of data as each field can hold more data.

No longer indexeable ( has a certain size limit)

Data constraints no longer respected.

Bigger sizes need to be respected in ETL upstreams ...

ErwinB SSC Enthusiast Points: 180 More actions · Answer 3

John,

I agree with you, and it feels most logic as it is the "classic" approach. However, in this case I am more often than not dealing with clients where the application and database are operating "on an island", and little to no formal rules exist. So, if the user has to name a product, he/she is allowed to use any number of characters, as long as it's practical. And while they are used to saving this data to Excel, the concept of limitations on length is totally alien for them.

The question I get back is "well, why don't you make all of those fields 255 characters long?". Which prompted me to investigate what the downside of that approach is. As far as I see, it doesn't change the storage size, doesn't impact indexes as they are not there. I understand that SQL Server reserves more memory space for the queries, but is this really going to have an impact on performance, given the size of the dataset?

Regards,

Erwin

ErwinB SSC Enthusiast Points: 180 More actions · Answer 4

Forgot to add:

While the total number of records in the table can go up to 50.000, the result from a single query usually is between 100 and 1000 records.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Erwin

You could use this opportunity to introduce some formal rules, I suppose. Failing that, agree with users what "practical" means for each individual column, and set that as the column size.

Jo made a good point about the memory grant thing, and it could have a big impact. Say you have four nvarchar(255) columns that all hold strings of no more than 20 characters. That's 1880 bytes per row more than necessary. If the query optimizer decides to scan the whole table (or any index including those four columns), it may take a memory grant about 100MB more than it would have done had the column definitions been more realistic.

John