Data Types in SQL Server – Sql Server Tutorial

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.

Leave a Reply

Your email address will not be published. Required fields are marked *