T-SQL 101: 40 String data types in SQL Server

You’ll notice when we discuss strings that there are many data types compared to what we had with numbers:

char was the original data character data type. By fixed length, I mean that if it’s defined as 10 characters long, it always returns 10 characters, even if there are only 5 characters stored in the string.

Prior to SQL Server 2019, char could only be used with ANSI characters (i.e. single byte characters). From 2019 onwards, char can also be used to store multi-byte characters by using UTF-8 encoding. I’ll write more about that another day.

nchar was the same thing except that it stored multi-byte characters to allow it to work with the full Unicode character set. Originally these were only 2 bytes per character, but in SQL Server 2012, an option for 4 byte characters was also added.

varchar and nvarchar are the variable length versions of char and nchar. When you select data from them, they only return the characters that are stored i.e. if there are 5 characters stored in an nvarchar(10) string, when you query it, you only get 5 characters back.

Originally, Sybase had a text data type that was used for long ANSI characters, and so it became part of SQL Server too. It allowed up to 2GB of data in a single value. ntext was the Unicode equivalent.

Nowadays, you shouldn’t use text or ntext, and should use varchar(max) or nvarchar(max) instead.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-10-21