Categories
SQL Server Step By Step

Data Type in SQL Server

Data Type in SQL Server

Hi friends, in this post we will learn about Data Type in SQL Server. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.We can also define our own data types in Transact-SQL or Microsoft .NET Framework.

Earlier we had discussed about Drop-Delete SQL Server Database Using Management Studio or QueryAlter-Rename SQL Server Database Using Management Studio or Query and How To Create Database in SQL Server.

Create Data Type Example: 

CREATE TYPE CCARD
FROM INT NOT NULL ;
-------------------OR----------------
CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

Data Type in SQL Server List:

Data Type Range From Range To Storage
Bigint -2^63 2^63-1 8 bytes
Int -2,147,483,648 2147483647 4 bytes
Smallint -32,768 32767 2 bytes
Tinyint 0 255 1 bytes
Bit 0 1 1 byte to 2 bytes
Decimal -10^38+1 10^38–1  5 bytes to 17 bytes
Numeric same as Decimal same as Decimal same as Decimal
Money -2^63 / 10000 2^63-1 / 10000 8 bytes
Smallmoney -214,748.3648 214748.3647 4 bytes
Float -1.79E + 308 1.79E + 308 4 bytes to 8 bytes
Real -3.40E + 38 3.40E + 38 4 bytes
Datetime 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 8 bytes
Smalldatetime 1900-01-01 00:00 2079-06-06 23:59
Date 0001-01-01 9999-12-31 3 bytes
Time 00:00:00.0000000 23:59:59.9999999
Datetime2 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999  6 bytes to 8 bytes
Datetimeoffset 0001-01-01 00:00:00.0000000 -14:00 9999-12-31 23:59:59.9999999 +14:00 8 bytes to 10 bytes
Char 0 chars 8000 chars Defined width
Varchar 0 chars 8000 chars 2 bytes + number of chars
Varchar(max) 0 chars 2^31 chars 2 bytes + number of chars
Text 0 chars 2,147,483,647 chars 4 bytes + number of chars
Nchar 0 chars 4000 chars Defined width x 2
Nvarchar 0 chars 4000 chars
Nvarchar(max) 0 chars 2^30 chars
Ntext 0 chars 1,073,741,823 chars
Binary 0 bytes 8000 bytes
Varbinary 0 bytes 8000 bytes
Varbinary(max) 0 bytes 2^31 bytes
Image 0 bytes 2,147,483,647 bytes