nchar and nvarchar (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
Use nchar when the sizes of the column data entries are probably going to be similar.
Use nvarchar when the sizes of the column data entries are probably going to vary considerably.
sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable. sysname is used to reference database object names.
Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is assigned using the COLLATE clause.
SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF does not apply to the nchar or nvarchar data types.
Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.
For information about converting character data, see char and varchar (Transact-SQL).
CREATE TABLE dbo.MyTable ( MyNCharColumn nchar(15) ,MyNVarCharColumn nvarchar(20) ); GO INSERT INTO dbo.MyTable VALUES (N'Test data', N'More test data'); GO SELECT MyNCharColumn, MyNVarCharColumn FROM dbo.MyTable;
Here is the result set.
MyNCharColumn MyNVarCharColumn --------------- -------------------- Test data More test data (1 row(s) affected)