Datatype |
Min |
Max |
Storage |
Type |
Notes |
---|---|---|---|---|---|
Bigint | -2^63 | 2^63-1 | 8 bytes | Exact | |
Int | -2,147,483,648 | 2,147,483,647 | 4 bytes | Exact | |
Smallint | -32,768 | 32,767 | 2 bytes | Exact | |
Tinyint | 0 | 255 | 1 bytes | Exact | |
Bit | 0 | 1 | 1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc... | Exact | |
Decimal | -10^38+1 | 10^381 | Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes | Exact | The Decimal and the Numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38. |
Numeric | same as Decimal | same as Decimal | same as Decimal | Exact | |
Money | -2^63 / 10000 | 2^63-1 / 10000 | 8 bytes | Exact | |
Smallmoney | -214,748.3648 | 214,748.3647 | 4 bytes | Exact | |
Float | -1.79E + 308 | 1.79E + 308 | 4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53 | Approx | Precision is specified from 1 to 53. |
Real | -3.40E + 38 | 3.40E + 38 | 4 bytes | Approx | Precision is fixed to 7. |
Datetime | 1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997 | 8 bytes | Datetime | If you are running SQL Server 2008 or later and need milliseconds precision, use datetime2(3) instead to save 1 byte. |
Smalldatetime | 1900-01-01 00:00 | 2079-06-06 23:59 | Datetime | ||
Date | 0001-01-01 | 9999-12-32 | 3 bytes | Datetime | New in SQL Server 2008 |
Time | 00:00:00.0000000 | 23:59:59:9999999 | 3 to 5 bytes | Datetime | New in SQL Server 2008 |
Datetime2 | 0001-01-01 00:00:00.0000000 | 9999-12-31 23:59:59.9999999 | 6 to 8 bytes | Datetime | 100 nanosecond accuracy. New in SQL Server 2008 |
DatetimeOffset | 0001-01-01 00:00:00.0000000 | 9999-12-31 23:59:59.9999999 | 8 to 10 bytes | Datetime | Like DateTime2 but includes offset in +-hh:mm from UTC. New in SQL Server 2008 |
Char | 0 chars | 8000 chars | Defined width | String | Fixed width |
Varchar | 0 chars | 8000 chars | 2 bytes + number of chars | String | Variable width |
Varchar(max) | 0 chars | 2^31 chars | 2 bytes + number of chars | String | Variable width |
Text | 0 chars | 2,147,483,647 chars | 4 bytes + number of chars | String | Variable width |
Nchar | 0 chars | 4000 chars | Defined width x 2 | Unicode | Fixed width |
Nvarchar | 0 chars | 4000 chars | Unicode | Variable width | |
Nvarchar(max) | 0 chars | 2^30 chars | Unicode | Variable width | |
Ntext | 0 chars | 1,073,741,823 chars | Unicode | Variable width | |
Binary | 0 bytes | 8000 bytes | Binary | Fixed width | |
Varbinary | 0 bytes | 8000 bytes | Binary | Variable width | |
Varbinary(max) | 0 bytes | 2^31 bytes | Binary | Variable width | |
Image | 0 bytes | 2,147,483,647 bytes | Binary | Variable width. Prefer to use the varbinary(max) type as the image type will be removed in future versions. | |
Sql_variant | Other | Stores values of various SQL Server-supported data types, except text, ntext, and timestamp. | |||
Timestamp | Other | Stores a database-wide unique number that gets updated every time a row gets updated. | |||
Uniqueidentifier | Other | Stores a globally unique identifier (GUID). | |||
Xml | Other | Stores XML data. You can store xml instances in a column or a variable. | |||
Cursor | Other | A reference to a cursor. | |||
Table | Other | Stores a result set for later processing. | |||
HierarchyID | Tree | Stores position of nodes of a hierarchal tree structure as defined by the user. New in SQL Server 2008. | |||
FileStream | Stream | Stores VARBINARY(MAX) binary large objects (BLOBs) outside of the database in the NTFS file system. New in SQL Server 2008, but only available if the feature is enabled. | |||
Geometry | Spatial | Stores XY locations. New in SQL Server 2008. | |||
Geography | Spatial | Stores Latitude and Logitude locations. New in SQL Server 2008. |
Notes:
GEOMETRY and GEOGRAPHY data types are implemented as .NET CLR data types, which means they can support various properties and methods specific to the data. For example, a method can be used to calculate the distance between two GEOMETRY XY coordinates, or the distance between two GEOGRAPHY latitude and longitude coordinates. Another example is a method to see if two spatial objects intersect or not. Methods defined by the Open Geospatial Consortium standard, and Microsoft extensions to that standard, can be used. To take full advantage of these methods, you will have to be an expert in spatial data, a topic that well beyond the scope of this chapter.
Another feature of spatial data types is that they support special spatial indexes. Unlike conventional indexes, spatial indexes consist of a grid-based hierarchy in which each level of the index subdivides the grid sector that is defined in the level above. But like conventional indexes, the SQL Server query optimizer can use spatial indexes to speed up the performance of queries that return spatial data.
file: /Techref/language/sql/2008DataTypes.htm, 9KB, , updated: 2017/8/7 09:33, local time: 2024/9/9 08:23,
52.14.21.52:LOG IN
|
©2024 These pages are served without commercial sponsorship. (No popup ads, etc...).Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE. Questions? <A HREF="http://linistepper.com/techref/language/sql/2008DataTypes.htm"> Microsoft Transact SQL Server 2008 Data Types</A> |
Did you find what you needed? |