Search code examples
sql-serverdatabasestringvarcharnvarchar

SQL Server data types to store strings and take less space

I have a question in regards to data types that are available in SQL language to store data into the database itself. Since I'm dealing with database that is quite large, and has a tendency to expand over 150GB+ of data, I need to pay close attention and save up every bit of space on the server's hard drive so that the database doesn't takes up all the precious space. So my question is as following:

  1. Which data type is the best to store 80-200 character long string in database?

  2. I'm aware of for example varchar(200) and nvarchar(200) where the nvarchar supports unicode character. Which one of these would take up less space in database, or if there's a 3rd data type that I'm not aware of, and which I could use to store the data (if I know for a fact that the string I would store is just a combination of numbers and letters, without any special characters)

  3. Are there some other techniques that I could use to save up space in database so that it doesn't expands rapidly ?

Can someone help me out with this ?

P.S. Guys, I have a 4th question as well:

  1. If for example I have nvarchar(max) data type which is in a table, and the entered record takes up only 100 characters, how much data is reserved for that kind of record?

  2. Let's say that I have ID which is of following form 191697193441 ... Would it make more sense to store this number as varchar(200) or bigint ?

Solution

  • The size needed for nvarchar is 2 bytes per character, as it represents unicode data. varchar needs 1 byte per character. The storage size is the actual number of characters entered + 2 bytes overhead. This is also true for varchar(max).

    From https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql:

    varchar [ ( n | max ) ] Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.

    So for your 4th question, nvarchar would need 100 * 2 + 2 = 202 bytes, varchar would need 100 * 1 + 2 = 102 bytes.