Showing posts with label data type in sql server 2014. Show all posts
Showing posts with label data type in sql server 2014. Show all posts

Sunday, December 27, 2015

Data Type in SQL

SQL have following data types

1-Integer value

Integer value have following data types

Type Storage
TINY One(1) byte
SMALL INT Two(2) byte
INT Four(4) byte
BIG INT Eight (8) byte

2-Float/Decimal values

Syntax

decimal(p,s)

where p=(Permission)

The permission means total number of digits can be present in the value (maximum value is 38 digits).

s=(scale)

It means your number of digits that can present after decimal.

3 - Numeric (p,s)

Syntax

Numeric (p,s)

It is equal to decimal data type.

4 - Boolean Values

Syntax

bit

It is bit type and take 1 byte space. It holds either 0 or 1 where 0 means "false" and 1 means true.

5 - Character Values

Syntax

char(n)

The maximum value of n is 8000. It is called as fixed data type. once you specified n value, the sql uses all the size.

6 - Varchar values

Syntax

varchar(n)

The maximum value of n is 8000. It is variable length character data type. In this case it does not fill any blank space.

7 - Text

Syntax

Text

It is in 2 GB size. It is variable length character data type. In this case it does not fill any blank space.

8 - NChar

Syntax

nchar(n)

It is similar to the char data type but can store multi language character. The maximum capacity is 4000 characters.

9 - Currency Values

It can only use for US currency format, The currency values are two type

a- Small Money - 4 bytes

b- Money - 8 bytes

10 - For Storing Binary Values

If you want to store images or video files or audio files in the database, you can use binary data type

a - Binary (n) - The maximum value is 8000  bytes, it is fixed length data type similar to char and nchar data type.

b- VarBinary(n) - The maximum value is 8000  bytes.

c - Image - The maximum Size is 2 GB.

11 - For Storing Date Values

a - Small Date Time

Syntax

smalldatetime

It is 4 bytes, It can hold data ranging between  Jan 1, 1900 to Jun 6,2079.

b - Date Time

Syntax

datetime

Size of Date Time is 8 bytes.It can hold data ranging between January 1,1753 to Dec 31, 9999

c- Time Stamp

Syntax

timestamp

If this data type is used in column wherever a modification is made to a record SQL Server will enter the date & time into the column. The size is 8 bytes

12 - For Storing XML Values


It is size in 2 GB.