Data Type in SQL Server

Data Type

By using Data type We can easily identify what kind of information we are going to store in the table.

What is a table?

•  Table is combination of row and column

• We use data type with column

Data Type:
- Data type is nothing but what kind of information we are going to store in a database that is identified by using data type.

Data Type Types:
1. Numeric Data Type

   1.1 Bit

• It stores values 0 or 1.

   1.2: TINY Int

• 0 to 255. 

 1.4 SmallInt
        -32768 to 32767

1.5 Decimal,Float Real

1.6 Int

-2147483648 to 2147483647

2. String Data Type

• Collection of character 

    2.1 Char 

• It will store 0-9,a-z,A-Z and special symbol as a 1 bit.

• It is a static memory allocation and it will store upto 8000 character

           char(20)  - AMAR

declare @val1 char(8000)='AMAR Patil';

print @val1

print datalength (@val1)

print len(@val1)

  2.2 varchar

• It will store 0-9,a-z,A-Z and a special symbol as a 1 bit.

• It is a dynamic memory allocation and it will store upto 8000 character

declare @val1 varchar(8000)='AMAR Patil';

print @val1

print datalength (@val1)

print len(@val1)

2.3 nchar

• It will store 4000 characters

• It is static memory allocation (1char will occupy 2 byte)

declare @val1 nchar(4000)='AMAR Patil';

print @val1

print datalength (@val1)

print len(@val1)

2.4 nvarchar

• It will store 4000 characters

• It is dynamic memory allocation.(1 char will occupy 2 byte)

declare @val1 nvarchar(4000)='AMAR Patil';

print @val1

print datalength (@val1)

print len(@val1)

Date and time Data type

Date Data Type

• It is allowed to insert dates in the table or We use it to store dates.

• We store date but without time and timezone info.

• date can be stored in multiple formats in the table. Formats are YYYY/MM/DD,DD/MM/YYYY,MM/DD/YYYY (dmy,ymd,mdy)

• We will see date example at the time table creation
  
  select GETDATE() //It will return us date with time

declare @todaydate date = GETDATE() //Here we cast datetime format into date

print @todaydate

Date Range:

Min date: 0001/01/01

Max date: 9999/12/31

Time Data Type

• It is allowed to insert time in the table without Date and timezone info.

• Time format in table is HH:MM:SS AM/PM
  
  

            declare @time1 time = GETDATE()

       print @time1

DataTime2:

• It is used to store both date and time together.

• DATETIME2 format is YYYY/MM/DD HH:MM:SS AM/PM

• DateTime and SmallDataTime are also available but Microsoft recommends use DateTime2.
  
  

            declare @datetime1 Datetime2 = GETDATE()
      print @datetime1