Difference between different string types in SQL Server? -

what difference between char, nchar, ntext, nvarchar, text , varchar in sql?

is there application case each of these types, or of them deprecated?

text , ntext deprecated, lets omit them moment. left, there 3 dimensions:

• unicode (ucs-2) vs. non-unicode: n in front of name denotes unicode
• fixed length vs. variable length: var denotes variable, otherwise fixed
• in-row vs. blob: (max) length denotes blob, otherwise in-row value

so this, can read type's meaning:

• char(10): in-row fixed length non-unicode of size 10
• nvarchar(256): in-row variable length unicode of size up-to 256
• varchar(max): blob variable length non-unicode

the deprecated types text , ntext correspond new types varchar(max) , nvarchar(max) respectively.

when go details, meaning of in-row vs. blob blurs small lengths engine may optimize storage , pull blob in-row or push in-row value 'small blob' allocation unit, implementation detail. see table , index organization.

from programming point of view, types: char, varchar, nchar, nvarchar, varchar(max) , nvarchar(max), support uniform string api: string functions. old, deprecated, types text , ntext not support api, have separate, deperated, text api manipulate. should not use deprecated types.

blob types support efficient in-place updates using update table set column.write(@value, @offset) syntax.

the difference between fixed-length , variable length types vanishes when row-compression on table. row-compression enabled, fixed lenght types , variable length stored in same format , trailing spaces not stored on disk, see row compression implementation. note page-compression implies row-compression.