Most commonly used SQL Server Data Types

In our previous blog, we have discussed the third SQL Server Data Type category i.e., Date and Time Data Type.

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

IV. Character Strings Data Type: - are string non-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 Character Strings Data Types in SQL Server with minimum and maximum range value: -

 

Character Strings Data Types

Things to Remember:

1. We use ‘char’ as ‘char(n)’, here n indicates the digits it can hold. The storage size is n-bytes. Similarly, we use ‘varchar’ as ‘varchar(n)’ or ‘varchar(MAX)’.

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

3. The default length for ‘char’ or ‘varchar’ data type when n is not specified is 1.

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 ‘char’ data type:

declare @strValue char;

set @strValue='LIVEVIAR';

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

DATALENGTH(@strValue) as StringStorageSize;

Output:

Value   StringLength    StringStorageSize

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

L          1                      1

B) For ‘varchar’ data type:

declare @strValue varchar;

set @strValue='LIVEVIAR';

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

DATALENGTH(@strValue) as StringStorageSize;

Output:

Value   StringLength    StringStorageSize

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

L          1                      1

4. 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 ‘char’ or ‘varchar’ with convert or cast function.

A) For ‘CONVERT’ function:

declare @strValue char(35);

set @strValue='abcdefghijklmnopqrstuvwxyz0123456789'

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

convert(varchar,@strValue) as OutputString_Convert, DATALENGTH(convert(varchar,@strValue)) as OutputStringLength

Output:

 

Result Window

B) For ‘CAST’ function:

declare @strValue char(35);

set @strValue='abcdefghijklmnopqrstuvwxyz0123456789'

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

CAST(@strValue AS varchar) as OutputString_CAST, DATALENGTH(CAST(@strValue AS varchar)) as OutputStringLength

Output:

 

Result Window

5. 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 ‘varchar(MAX)’ type to ‘char’ type, then the extra digits will be truncated, which is not good. This result in heavy data lost.

6. 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_CharacterStringsDataTypes. 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_CharacterStringsDataTypes

(

CustomerName varchar(40),

ImagePathUrl varchar(max),

AccountStatus char(1),

ProductQuery text

)

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_CharacterStringsDataTypes(CustomerName,ImagePathUrl,AccountStatus,ProductQuery)

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

'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_CharacterStringsDataTypes

 

Output Window

More Explanation On Table Column: -

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

2. ImagePathUrl: Data type like ‘varchar(max)’ or ‘text’ 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 ‘char’ data type by limiting its size to 1.

4. ProductQuery: For storing large string values, queries or text, we can use ‘text’ or ‘varchar(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 Character Strings Data Type in SQL Server.

For any query, comment us below.

Previous – Most commonly used SQL Server Data Types #4

Next - Most commonly used SQL Server Data Types #6

Related Questions: -

#newtoprgm try the first program

Q-1 Which SQL SERVER function is used- to return the number of bytes used to represent any expression?

A) PATINDEX()

B) SUBSTRING()

C) CONVERT()

D) DATALENGTH()

Ans- Option (D).

Q-2 Describe the following data type- char, varchar and varchar(MAX) in SQL Server?

Ans- Elucidate for the data types: -

(a) char- this data type is used when you want your column entries or string to be fixed length.

(b) varchar- this data type is used when you want your column entries or string to be of variable length.

(c) varchar(MAX)- this data type is used when the column entries or string length is exceeding the size 8000 bytes.

Click imagination hunt to read latest blogs.

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

Keep learning and sharing...