In SQL Server, each column, local variable, expression and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold. Integer data, character data, monetary data, date and time data, binary strings, and so on.
Now, we will discuss about Datatypes in SQL Server one by one.
1. Int :- Int datatype contains or holds an integer values only like 1,2,3,4........
An integer can be written without a fractional component e.g. it is 1, 100, 4, -10, etc., and it cannot be 1.2, 5/3, etc
There is a minimum and maximum range specified for int datatype.
Minimum range is: -2147483648
Maximum range is: 2147483647
And the byte size is 4 bytes.
2. Char(Size), Varchar(Size), NChar(Size) and NVarchar(Size) :- All are used to store text or string data in SQL Server database. But there is some light difference b/w all of them. We will discuss what the difference between them one by one
Difference between Char and Varchar :-
- Used to store character string value of fixed length.
- It's more faster than Varchar.
- Uses static memory allocation. (Suppose we declare char(50) and set the value of 10 character then this will allocate memory as it declare for 50 character)
- Used to store alphanumeric data.
- Its slower than Char.
- The maximum character allowed is 8000.
- Uses dynamic memory allocation. (Suppose we declare varchar(50) and set the value of 10 character then this will allocate memory only for 10 character)
Difference between Nvarchar and Varchar
- Nvarchar store UNICODE data. If we have required to store UNICODE data or multi language data then we use nvarchar datatype.
- Nvarchar uses 2 bytes per character.
- Maximum character allowed is 4000.
- Varchar store ASCII data and should be our datatype of choice for normal use.
- Varchar uses 1 byte per character.
If we want to store maximum number of character then we can use varchar or nvarchar with Max size.
Ex. Varchar(max) or Nvarchar(max)
3. Decimal :- In decimal datatype, We can store only numeric value with decimal point only. Decimal(18,0) allow us to use 0 digits after point. And Decimal(18,2) allow us to use 2 digits after points.
4 Uniqueidentifier :- This datatype is referred to as GUIDs. The uniqueidentifier datatype in SQL Server is stored natively as a 16 byte binary value. We can call NEWID() to generate GUIDs in SQL Server. Main use is as an identifier that must be unique in a network that has many computers at many sites.