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 Query, Alter-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 |