DATA TYPES IN SQL SERVER:-
1) String type: –
In sql server, string comparison is not case sensitive Small or alphabet, lower or upper case
(a) Char :
It allows char Data up to 4,000 bytes
Example:- ename char (20)
- It is called fixed length character data types.
- Example: ravi 20bytes
Arungini 20bytes.
Note: “here memory is wasted”.
(b) varchar:
- It allows character data up to 8,000bytes (or) character.
Example:-ename varchar(20).
- It is also variable length character data type.
Example:- Ravi 4bytes
Arungini 9 bytes
Note: – Here memory is not wasted
When no of character more than 8000 then we have declared like this
Comments varchar (max)
(c) nchar / nvarchar :
- Char/varchar is based on Ascii.
- Nchar/varchar is based on Unicode. -> range(0-65535bytes) -> it is char of diff. languages
Char/varchar -> 1bytes occupies 1 char
nchar/nvarchar -> 2bytes occupies 1char
(2) Integer Types:-
It allows whole number.
- Tinyint -> 1 bytes
- Smallint ->2bytes
- Int ->4bytes
- Bigint ->8bytes
Example: empno smallint
(3) Decimal types:-
- It allows real number
Example: decimal (p.s)
- P -> precision -> total no of digits -> max38
- S -> scale -> the number of digits allowed after decimal -> it can be maximum 0 to p
Example: sal decimal (7, 2)
5000.50
(4) CURRENCY:-
The currency fields have two data types:
- small money->it occupies 4bytes
- Money->it occupies 8bytes.
Example: – salary small money
Bank balance money
(5) DATE TYPES :–
- This types also two data types
(i) Small date time———–4bytes
(ii) Date time————-=8bytes
Example: — dob smalldatetime
Date datetime
- If small datetime it is range is Jan1 1900 to dec 31 9999.
- If datetime,it’s range is Jan 1753 to 31 dec 9999.
(6) Pictures & Images :-
it’s have 2 data types
- (i)Binary -> 4000bytes
- (ii)Varbinary ->8000bytes
Example: – empno to binary(500)
If picture size exceeds more than 8000bytes then declared
Empno varbinary (max)
Columns are declared with varchar (max) and varbinary(max).its called “LOBS”.
(7) UNQUE IDENTIFIER:–
- A column declared with unique identifier will uniquely identify each record.
- It’s similar to row id.
Example: – F1 UNQUE IDENTIFIER
- Unique identifier can be assign to f1 by using a function called “NEWID”.
(8) XML:
- It allows xml document.
(9) Sql variant:-
- It allows any type of data.
- Example :strings, numbers etc
(10) Time stamp:-
It allows date and time.
Empid ename sal last update
1 x 5000 null
Here time stamp is last update.