SQL Server- SQL Server Data Types

SQL SERVER Data types

In SQL server, we have many data types and in this moment we no need to concentrate on all the data types. We will learn frequently used data types.

What is data type?

Data types is a category which will define the characteristics of the data

For E.g. INT 

It will accept only Numeric values. String, decimal values are not allowed to store here.

I will try to relate some of the data types with some properties of person

Name	ABC	Here we will take char, varchar, nvarchar data types to store string values
DOB	01-01-1900	Date
Height	5.3	numeric, float, secimal
Weight	62	int, smallint, tinyint, bigInt
Birth date and time	01-01-1900,06:30:02	Datetime, datetime2,smalldatetime, datetimeoffset
Birth time	06:30:02	Time

Below is the data type and its details

Data Type	Max Size	Comments	Points to remember
char(size)	1-8000	Fixed-length, non-Unicode string data. The storage size is (size) bytes.	->When size is not specified in a data definition or variable declaration statement, the default length is 1. When size is not specified when using the CAST and CONVERT functions, the default length is 30. ->If we want to store Chinese, Japanese and other kind of special character then we need to use Unicode. If we use plain English then char and varchar would be enough
nchar(size)	1-4000	Fixed-length, Unicode string data. The storage size is 2x (size).
varchar(size) or varchar(max)	1-8000	Variable-length, non-Unicode string data.
nvarchar(size) or nvarchar(max)	1-4000	Variable-length, Unicode string data.
bit	1,0, NULL	It will take 1 bit	These are all fixed numeric
TINYINT	0 to 255	1 byte
SMALLINT	-32,768 to 32,767	2 byte
INT	-2,147,483,648 to 2,147,483,647	4 byte
BIGINT	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	8 bytes
DECIMAL(m,d)	1-18 (m for total digits and  d for total precisions	Numeric data types that have fixed precision and scale.
NUMERIC(m,d)	1-18 (m for total digits and  d for total precisions
FLOAT(n)	N defaults to 53
DATE	0001-01-01' to '9999-12-31'.	'YYYY-MM-DD'	All these are related to date and time data types.
DATETIME	Date: 1753-01-01 00:00:00' to '9999-12-31 23:59:59' Time: '00:00:00' to '23:59:59:997'	'YYYY-MM-DD hh:mm:ss[.mmm]'
DATETIME2	Date: '0001-01-01' to '9999-12-31' Time: '00:00:00' to '23:59:59:9999999'	'YYYY-MM-DD hh:mm:ss[.fractional seconds]'
SMALLDATETIME	Date: '1900-01-01' to '2079-06-06' Time: '00:00:00' to '23:59:59'	'YYYY-MM-DD hh:mm:ss'
TIME	Time: '00:00:00.0000000' to '23:59:59.9999999'	'YYYY-MM-DD hh:mm:ss[.nnnnnnn]'
DATETIMEOFFSET	Along with Dateime2 it will display time zone Time zone offset range from -14:00 to +14:00.	'YYYY-MM-DD hh:mm:ss[.nnnnnnn]' [{+|-}hh:mm]