CODEWITHSUNDEEP
sql.netsql-serverdatabase
Karthik G
Karthik G

Reputation: 1262

Database column data type

My SQL Server database has many tables with variety of data types being used. The current design has a standard datatype length for columns ex: NVARCHAR like 200, 1000 or MAX (this is a standard we are adopted) depending on what type of data I saved, I use datatype accordingly.

For a NAME column I believe it does not makes sense to make it 200/1000, forget about MAX (I am not saying we did it). So, is it fine to make a field 200/1000 if you know it for sure occupies not more than 75 characters? Does it have any impact on the query performance at all? What other problems/benefits are there for this approach.

Which way is better, use a standard datatype length irrespective of the data stored or is it good to use a specific datatype length for the column?

Any help in this regard is highly appreciated

Thanks!!!

Upvotes: 4

Views: 213

Answers (3)

Arun
Arun

Reputation: 951

Don't use MAX. If you use nvarchar(max) or varchar(max) SQL Server will treat it like a large text value, and it will take longer to process.

To answer your question. It is better to use varchar(N), and N should be the largest number you are expecting to store. There is no need to make N 1000, or even 200. 99.999% of names are going to be under this. That being said, there will be a difference in query performance by making it 200, but it will be negligible.

In terms of storage size nvarchar is 2 bytes per length, and varchar is 1 byte per length.

If you are expecting names in (unicode) different characters/language use nvarchar(N).

Upvotes: 0

Alejandro
Alejandro

Reputation: 7813

Generally I tend to use the smaller types whenever I can, but it's not always possible or convenient, and there are certainly trade-offs in each choice. For one, 200 don't seems a disparate amount but if the data is guaranteed to be limited at 75 chars a smaller type is perhaps a better option. I would analyze the pros and cons for using smaller sizes:

Pros

  • Helps prevent errors from user putting a larger text into controls.
  • Also prevents possible malicious users if a bug appears due to long texts.
  • The client application must be ready to handle longer strings, should the full 200 characters are used.
  • Indexes on that column may be smaller, which helps improving performance.

Cons

  • Not "future-proof". Some day, a name might appear longer than 75 characters, forcing an update in the DB and front-end application. User's needs change with time.
  • UI must gracefully respond to the user entering longer texts, either by not accepting or by displaying a message or something.
  • If you ever want to import data from other systems, those might have longer texts that you must handle.
  • Breaking or changing company standards (200/1000/MAX) may not be easy at all, specially if the team had a long time following it.

Of course you must evaluate your exact situation and if it's worth the change or not. From memory and personal experience I had to balance all those things.

Upvotes: 1

zXSwordXz
zXSwordXz

Reputation: 176

I always try to keep the data type and size realistic. I do think 200 is way too much and why Nvarchar? I think Varchar(50) or 75 as you think is suffice. It does have an impact on application if you were to create class because an application would have to reserve the byte size causing an application to require more memory. That is just a personal opinion, I'm no expert.

Upvotes: 0

Related Questions