web analytics

Tech Guidence

Guidance at Ease

Datatype in sql : Full description

Datatype in sql

Datatype in sql

DATATYPE IN SQL

It is the most important factor as DATATYPE should be according to our in need in Project and should be accurate so we should have proper knowledge of DATATYPES in SQL.

VARCHAR

Varchar datatype is used to store the string values. It can have maximum of 8000 characters. It was added in MS SQL Server 2005. Before 2005 TEXT was used in place of Varchar.

If we want to store more than 8000 characters then we use VARCHAR (MAX). It can store data upto 8 GB.

Why Varchar was used over TEXT??

String Functions cannot be applied over TEXT but the String functions can be applied over Varchar.

NVARCHAR

NVARCHAR is used for storing Unicode values. It can store maximum of 4000 characters. Nvarchar also added in MS SQL Server 2005 and before 2005 NTEXT was used If we want to store more than 4000 characters then we use NVARCHAR(MAX).

NVARCHAR(MAX) has the size of 2 GB.

What is the difference between VARCHAR and NVARCHAR??

Varchar is used for storing non Unicode values but Nvarchar is used for storing Unicode values. It means Varchar supports till ASCII but NVARCHAR is to store characters of any other languages like French, Japanese etc.

CHAR has fixed length.

What is the difference between CHAR and VARCHAR ??

CHAR has fixed length but VARCHAR has variable length. If memory of 100 characters is assigned to CHAR that is CHAR(100) then it will use memory of 100 characters no matter if  use 100 characters or less than 100 characters in database.

IN VARCHAR if memory of 100 characher is assigned to Varchar that is VARCHAR(100) and we store only 20 characters in database it will use memory of 20 characters but 1 or 2 bytes extra for conversion.

Conversion bytes taken depends upon the following : -

If 0 – 255 characters then it will take one byte extra for conversion

If more than 255 characters then it will take two bytes extra for conversion.

Example of conversion is

CONVERT (VARCHAR, GETDATA ())

At the time of conversion if you not give the size of VARCHAR as shown in above example then it will take default size of VARCHAR by default.

Let us take one simple example

If we assign ename with datatype varchar(50) then it can have maximum of 50 characters and varchar uses one byte extra for conversion.

First step to make a project

First create your database and then make tables as per your need. If you don’t have proper knowledge of datatype in SQL than you cannot assign proper value.

After creating a table you must know the path where your table got saved. Path where the table will get saved in your system is

My computer —-> Window Drive (C:) —–> PF86 —-> MS SQL Server —–> MS SQL 10 SQL Express.

Hence Some of the important datatypes in sql and there difference is discussed above.

 You may Also Like to Read Some related Topics:

You May also like to read :

Tags:

About

I am B.tech Holder and have completed my B.tech from Hisar in Electronics and communications. I am asp.net developer and certified in this language. I love to share my experience and knowledge

View all posts by

One Response

  1. Database And Database Handling says

    […] Datatype in sql […]

POST A COMMENT


*
CommentLuv badge

a