Data Types in SQL Server
Data Types in SQL Server
Topic for this article seems very simple. But worth it to know because improper data types will impact to suboptimal database performance.
Every database columns have data types that limit the kind of data that can be stored in them. Here is the list
Name | SQL Name | Range | Sample |
Character | | | |
Character | Char(n) | 1 to 8000 | ‘string’ |
Character varying | Varchar(n) | 1 to 8000 | ‘string’ |
Unicode character | Nchar(n) | 1 to 4000 | N’string’ |
Unicode varchar | Nvarchar(n) | 1 to 4000 | N’string’ |
| | | |
Date | | | |
Datetime | Datetime | 01/01/1753 to 12/31/9999 | ‘Jan 15, 1996 09:01:22.1’ |
Small datetime | Smalldatetime | 01/01/1900 to 6/6/2079 | ‘Jan 15, 1996 09:01’ |
| | | |
Logical | | | |
bit | bit | 0 or 1 | 1 |
| | | |
Money | | | |
money | money | +-$992,337,203,685,477. 5807 | $314453789.22 |
Small money | smallmoney | +- $214,748.3647 | $453789.22 |
| | | |
Exact Numeric | | | |
Decimal | Decimal (p,s) | +1E38 to -1E38-1 | 1234.56 |
Numeric | Numeric (p,s) | +1E38 to -1E38-1 | 1234.56 |
| | | |
Approximate Numeric | | | |
Float | Float(n) | +-2.23E-308 to 1.79E+308 | 123.456 |
real | Real | +-1.18E-38 to 3.40E+38 | 123.456 |
| | | |
Integer | | | |
integer | Int | +2,147,483,647 to -2,147,483,648 | 12334556 |
Small integer | Smallint | +32,767 to -32,768 | -2345 |
Tiny integer | tinyint | 0 to 255 | 12 |
| | | |
Special | | | |
Cursor | Cursor | N/A | N/A |
Image | Image | Length up to 2,147,483,647 bytes | |
Sysname | Sysname | N/A | N/A |
Timestamp | Timestamp | N/A | N/A |
Text | Text | Length up to 2,147,483,647 bytes | ‘string’ |
Ntext | Ntext | Length up to 1,073,741,823 bytes | N’string’ |
You can create new data type names of your own, based on one of SQL Server’s data types or SQL Server data types in .NET. In the pubs database, you will find three examples. User defined types allow developers to impose some constraints on a column’s data. However, they limit the kinds of queries that can be done with the data.
Written by,
Kasim Wirama, MCSD.NET, MCDBA
Database Administrator