Most commonly used SQL Server Data Types

In our previous blog, we have discussed the fourth SQL Server Data Type category i.e., Character Strings Data Type.

In this blog, we are going to discuss the fifth category i.e., Unicode Character Strings Data Type.

V. Unicode Character Strings Data Type: - are string Unicode data type of either fixed length or variable length.

Note: What does Unicode means? Unicode is an international encoding standard for use with different languages and scripts, by which each letter, digit, or symbol is assigned a unique numeric value that applies across different platforms and programs.

The following table lists the Unicode Character Strings Data Types in SQL Server with minimum and maximum range value: -

 

Unicode Character Strings Data Type

Things to Remember:

1. Prefix ‘N’ with Unicode Character Strings Data Types.

2. We use ‘nchar’ as ‘nchar(n)’, here n indicates the digits it can hold. The storage size is 2-times n-bytes. Similarly, we use ‘nvarchar’ as ‘nvarchar(n)’ or ‘nvarchar(MAX)’.

3. When we declare a variable with ‘nvarchar(MAX)’ data type, then the variable can hold the maximum storage size of 2GB.

4. The default length for ‘nchar’ or ‘nvarchar’ data type when n is not specified is 1. And every single character will occupy two times its storage size.

Practical Implementation: -

In this practical we’ll see how the default length is 1? when given data types are used as variable declaration. And also the number of bytes used to represent a single character.

A) For ‘nchar’ data type:

declare @strValue nchar;

set @strValue=N'LIVEVIAR';

select @strValue as Value,LEN(@strValue) as StringLength,

DATALENGTH(@strValue) as StringStorageSize;

Output:

Value   StringLength    StringStorageSize

-------   -----------------   ------------------------

L          1                      2

B) For ‘nvarchar’ data type:

declare @strValue nvarchar;

set @strValue=N'LIVEVIAR';

select @strValue as Value,LEN(@strValue) as StringLength,

DATALENGTH(@strValue) as StringStorageSize;

Output:

Value   StringLength    StringStorageSize

-------   -----------------   ------------------------

L          1                      2

5. The default length during the CONVERT and CAST function when n is not specified is 30.

Practical Implementation: -

In this practical, when one of the data type is converted with the CONVERT and CAST function to another data type, the default length is 30.

Important note: When ‘n’ is not specified in ‘nchar’ or ‘nvarchar’ with CONVERT and CAST function.

A) For ‘CONVERT’ function:

declare @strValue nchar(35);

set @strValue= N'abcdefghijklmnopqrstuvwxyz0123456789'

select @strValue as InputString, LEN(@strValue) as InputStringLength,

DATALENGTH(@strValue) as InputStringStorageSize,

convert(nvarchar,@strValue) as OutputString_Convert,

LEN(convert(nvarchar,@strValue)) as OutputStringLength,

DATALENGTH(convert(nvarchar,@strValue)) as OutputStringStorageSize

Output:

 

Result Window

B) For ‘CAST’ function:

declare @strValue nchar(35);

set @strValue= N'abcdefghijklmnopqrstuvwxyz0123456789'

select @strValue as InputString,

LEN(@strValue) as InputStringLength,

DATALENGTH(@strValue) as InputStringStorageSize,

CAST(@strValue AS nvarchar) as OutputString_CAST,

LEN(CAST(@strValue AS nvarchar)) as OutputStringLength,

DATALENGTH(CAST(@strValue AS nvarchar)) as OutputStringStorageSize

Output:

 

Result Window

6. Storage space or the number of digits it may hold is a very important thing. You may understand this by reading the next two lines. If you unknowingly convert a ‘nvarchar(MAX)’ type to ‘nchar’ type, then the extra digits will be truncated, which is not good. This result in heavy data lost.

7. Choosing right data type for each column reduce backup time, improve SQL Server performance and improve the execution time for queries, views, joins.

NOTE: Microsoft News Alert- Data Type like ntext, text, and image will be removed in a future version of SQL Server. Try not to use or avoid using these data types in new development work, and plan to modify applications that currently use them. Replacement for these data types are nvarchar(max), varchar(max), and varbinary(max).

Now the question comes to our mind. How to use these Data Type?

Let’s understand and learn how to use these data types in some real situation. The situation is something like – we have a complaint page where users post his complaint by entering his username, an image relating to his complaint and his brief complaint query.

Practical Implementation:

For this, we will create a table, i.e., tbl_UnicodeCharacterStringsDataTypes. In which, values are inserted into each column with respect to their column data type. After inserting the record successfully. We’ll also learn how to return or view those inserted records using a select statement. Just follow the below-mentioned steps:

Step-1: Open a New Query window in the SQL Management Studio instance.

Step-2: Create a table using create statement. As you can see in the create statement - I have used all the data type just to get an idea - How and where to use each and every type?

create table tbl_UnicodeCharacterStringsDataTypes

(

CustomerName nvarchar(40),

ImagePathUrl nvarchar(max),

AccountStatus nchar(3),

ProductQuery ntext

)

Step-3: Just press Execute button or F5 key to run that creates statement query. I am hoping you have written the exact query that I have mentioned. Once clicking the execute button you will get a message “Command(s) completed successfully.”

Step-4: Now you need to insert a record based on the column type. No problem, just copy the below code that I have created for you. Before copying, I would strongly encourage you to understand each and every column value. Now, Execute the insert query. You will get the message “(1 row(s) affected).” It implies that you are doing things in a right way.

insert into tbl_UnicodeCharacterStringsDataTypes(CustomerName,ImagePathUrl,AccountStatus,ProductQuery)

values(N'Imagination Hunt',N'C:\Users\Liveviar\Pictures\872918c190b4368e9c52e5ccfba2a6ef.jpg',N'No',

N'Imagination Hunt Blogs are all about - Detailed and best way to learn and clear doubt on Asp.Net, C#, SQL, HTML, Finance, Mutual Funds, English Vocabulary and Etiquette's, Computer Basics, Interview Tactics, Exam Preparations, Jobs Updates')

Step-5: Lastly, view the inserted record by executing the select query.

select * from tbl_UnicodeCharacterStringsDataTypes

 

Output Window

More Explanation On Table Column: -

1. CustomerName: This column accepts a string value as we are using ‘nvarchar’ data type. We have also restricted the user that his name must fall within the 40 characters’ limit.

2. ImagePathUrl: Data type like ‘nvarchar(max)’ or ‘ntext’ can be used to store the large path or string or the image path or the file location.

3. AccountStatus: Account status column reminds us whether the query comes from a registered user or from some anonymous user. For ex- if status is ‘Y’ user is registered else ‘N’ un-registered user. And for storing a single fixed-length character, we are using ‘nchar’ data type by limiting its size to 1.

4. ProductQuery: For storing large string values, queries or text, we can use ‘ntext’ or ‘nvarchar(MAX)’ data type.

Lastly, I always force coders to act smart while working with different data types. It’s not a good practice to use high precision data type every time.

By this, we learn how and where to use Unicode Character Strings Data Type in SQL Server.

For any query, comment us below.

Previous - Most commonly used SQL Server Data Types #5

Next - Types of Joins in SQL Server #1.

Related Questions: -

#newtoprgm try the first program

Q-1 In this below mentioned SQL Server query, what will be the output for StringStorageSize column?

declare @strValue nvarchar(4);

set @strValue=N'LIVEVIAR';

select @strValue as Value,

DATALENGTH(@strValue) as StringStorageSize;

A) 4

B) 8

C) None of the above

Ans. Option (B).

Explanation- As per the query we have to undergo certain keys:

(i) the variable ‘@strValue’ is of nvarchar data type, which fall under Unicode Character Data Type category.

(ii) Length is 4. [because the value of n=4 in nvarchar(4)].

(iii) DATALENGTH function return number of bytes used i.e., 2*4=8.

Hence, the output for StringStorageSize is 8.

Q-2 Which one of the following is invalid Unicode Character String Data Type?

A) varbinary

B) ntext

C) nvarchar(max)

D) nvarchar

Ans- Option(A).

Explanation: varbinary belongs to Binary String Data Type. And the rest ntext, nvarchar(max) and nvarchar belongs to Unicode Character String Data Type.

Click imagination hunt to read latest blogs.

Posted By - +Manish Kumar Gautam +LIVE VIAR +ASP.NET SOLUTIONS

Keep learning and sharing...