Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best data type for storing strings in SQL Server?

Tags:

sql

sql-server

What's the best data type to be used when storing strings, like a first name? I've seen varchar and nvarchar both used. Which one is better? Does it matter?

I've also heard that the best length to use is 255, but I don't know why. Is there a specific length that is preferred for strings?

like image 562
Steven Avatar asked Sep 07 '10 02:09

Steven


People also ask

Which data type is used for the string in SQL?

The string data types are CHAR , VARCHAR , BINARY , VARBINARY , BLOB , TEXT , ENUM , and SET . In some cases, MySQL may change a string column to a type different from that given in a CREATE TABLE or ALTER TABLE statement.

Should I use VARCHAR or Nvarchar?

Today's development platforms or their operating systems support the Unicode character set. Therefore, In SQL Server, you should utilize NVARCHAR rather than VARCHAR. If you do use VARCHAR when Unicode support is present, then an encoding inconsistency will arise while communicating with the database.

How can store long string in SQL Server?

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). DECLARE @longText varchar(max); SET @longText = REPLICATE('X', 8000); SET @longText = @longText + REPLICATE('X', 8000); SELECT DATALENGTH(@longText); Returns 16 K of characters.

What's best SQL DataType for storing JSON string?

JSON text is stored in VARCHAR or NVARCHAR columns and is indexed as plain text. Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features.


3 Answers

nvarchar stores unicode character data which is required if you plan to store non-English names. If it's a web application, I highly recommend using nvarchar even if you don't plan on being international. The downside is that it consumes twice as much space, 16-bits per character for nvarchar and 8-bits per character for varchar.

like image 125
Mike Valenty Avatar answered Oct 26 '22 11:10

Mike Valenty


What's the best data type to be used when storing strings, like a first name? I've seen varchar and nvarchar both used. Which one is better? Does it matter?

See What is the difference between nchar(10) and varchar(10) in MSSQL?

If you need non-ASCII characters, you have to use nchar/nvarchar. If you don't, then you may want to use char/varchar to save space.

Note that this issue is specific to MS SQL Server, which doesn't have good support for UTF-8. In other SQL implementations that do, you can use Unicode strings with no extra space requirements (for English).

EDIT: Since this answer was originally written, SQL Server 2019 (15.x) finally introduced UTF-8 support. You may want to consider using it as your default database text encoding.

I've also heard that the best length to use is 255, but I don't know why.

See Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?

Is there a specific length that is preferred for strings?

If you data has a well-defined maximum limit (e.g., 17 characters for a VIN), then use that.

OTOH, if the limit is arbitrary, then choose a generous maximum size to avoid rejecting valid data. In SQL Server, you may want to consider the 900-byte maximum size of index keys.

like image 42
dan04 Avatar answered Oct 26 '22 11:10

dan04


nvarchar means you can save unicode character inside it. there is 2GB limit for nvarchar type. if the field length is more than 4000 characters, an overflow page is used. smaller fields means one page can hold more rows which increase the query performance.

like image 23
Russel Yang Avatar answered Oct 26 '22 09:10

Russel Yang