SQL Server 2008 Data types

When you create a table, you must assign a data type to each column in the table. This section describes some of the most common data types in SQL Server. Even if you create a custom data type, it must be based on a standard SQL Server data type. For example, you can use the following syntax to create a custom data type (Address), but be aware that it is based on the SQL Server standard varchar data type.

1. CREATE TYPE Address
2. From varchar (+) not NULL

If you change the data type of a column in a large table in the table design interface of SQL Server Management Studio, the operation may take a long time. You can see the reason for this by scripting this change in the Management Studio interface. Management Studio creates a temporary table, takes a name like Tmptablename, and then copies the data to the table. Finally, the interface deletes the old table and renames the new table with the new data type. Of course, there are other steps involved in working with indexes and any other relationships in the table.

If you have a large table with millions of records, the process can take 10 minutes, sometimes hours. To avoid this situation, you can change the data type of the column by using a simple single-line T-SQL statement in the Query window. For example, to change the data type of the JobTitle column in the Employees table to varchar (70), you can use the following syntax.

1. Alter TABLE HumanResources.Employee ALTER COLUMN JobTitle Varchar (70)

Description

You may lose important data when you convert to a data type that is incompatible with the current data. For example, if you want to convert the numeric data type that contains some data, such as 15.415, to an integer data type, then 15.415 this data will be rounded to an integer.

You might want to write a report to the SQL Server table that shows the data type for each column in the table. There are many ways to do this, but the most common way is to connect the sys.objects and sys.columns tables. In the following code, there are two functions that may not be familiar to you. The function type_name () converts the data type ID to the appropriate name. You can use the type_id () function to reverse the operation. Another function to be aware of is schema_id (), which is used to return the identity value of the schema. This is especially useful when you need to write a report about SQL Server metadata.

1. SELECT O.name as ObjectName,
2. C.name as ColumnName,
3. Type_name (c.user_type_id) as DataType
4. From sys.objects o JOIN sys.columns C
5. On o.object_id = c.object_id
6. WHERE o.name = ' Department '
7. and o.schema_id = schema_id (' HumanResources ')

The code returns the following results (note that name is a user-defined data type).

2. ObjectName ColumnName DataType
4. ---------------------------------------------------
6. Department DepartmentID smallint
8. Department Name
   Name
10. Department GroupName Name
12. Department ModifiedDate
    Datetime

1. character data type

Character data types include varchar, char, nvarchar, nchar, text, and ntext. These data types are used to store character data. The main difference between varchar and char types is data padding. If a table column is named FirstName and the data type is varchar (20), and the value Brian is stored in the column, only 5 bytes are physically stored. However, if the same value is stored in a column with a data type of char (20), all 20 bytes will be used. SQL inserts trailing blanks to fill 20 characters.

If you want to save space, why use the char data type? Using the varchar data type adds some overhead to the system. For example, if you want to store two-letter state abbreviations, it is best to use the char (2) column. While some DBAs believe that space should be most likely to be saved, it is generally good practice to find a suitable threshold in the organization and specify a char data type below that value, whereas the varchar data type is used instead. As a general rule, any column that is less than or equal to 5 bytes should be stored as a char data type instead of a varchar data type. If this length is exceeded, the benefit of using the varchar data type will outweigh its additional overhead.

The nvarchar data type and the nchar data type work in the same way as the equivalent varchar data type and the char data type, but both data types can handle international Unicode characters. They require some extra overhead. Data stored in Unicode is two bytes for one character. If you want to store the value of Brian in a nvarchar column, it will use 10 bytes, and if it is stored as nchar (20), 40 bytes will be required. Because of these extra overhead and increased space, you should avoid using Unicode columns unless you do have business or language needs to use them.

The next data types to be mentioned are text and ntext. The text data type is used to store large character data inside and outside the data page. You should use as few of these data types as possible, because you can affect performance, but you can store up to 2GB of data in a single row of columns. A better choice than the text data type is to use the varchar (max) type, because better performance will be achieved. In addition, the text and ntext data types will not be available in some future versions of SQL Server, so it is still best to use varchar (max) and nvarchar (max) instead of the text and ntext data types.

Table 1-1 lists these types, gives a brief description of them, and illustrates the required storage space.

Table 1-1

Data type	Description	Storage space
Char (N)	n is between 1~8000 characters	N bytes
Nchar (N)	n is between 1~4000 Unicode characters	(2n bytes) + 2 words Extra cost of knots
Ntext	Up to 230–1 (1?073?741?823) Unicode characters	2 bytes per character
Nvarchar (max)	Up to 230–1 (1?073?741?823) Unicode characters	2x characters + 2 words Extra cost of knots
Text	Up to 231–1 (2?147?483?647) character	1 bytes per character
Varchar (N)	n is between 1~8000 characters	1 bytes per character +2 Bytes Extra Overhead
Varchar (max)	Up to 231–1 (2?147?483?647) characters	1 bytes per character +2 Bytes Extra Overhead

2. Exact numeric data type

Numeric data types include bit, tinyint, smallint, int, bigint, numeric, decimal, money, float, and real. These data types are used to store different types of numeric values. The first data type bit stores only 0 or 1, and is converted to true or false in most applications. The bit data type is ideal for use with switch markers, and it occupies only one byte space. Other common numeric data types are shown in table 1-2.

Table 1-2

Data type	Description	Storage space
Bit	0, 1, or null	1 bytes (8 bits)
tinyint	An integer between 0~255	1 bytes
smallint	An integer between –32?768~32?767	2 bytes
Int	–2?147?483?648~ An integer between 2?147?483?647	4 bytes
bigint	–9?223?372?036?854?775?808~ 9?223?372?036?854?775?807 An integer between	8 bytes

(Continuation of table)

Data type	Description	Storage space
numeric (P,s) or Decimal (P,s)	The value between –1?038+1~1?038–1	Up to 17 bytes
Money	–922?337?203?685?477.580?8~ 922?337?203?685?477.580?7	8 bytes
SmallMoney	–214?748.3648~2?14?748.3647	4 bytes

SQL Server Data Type ( 2 )

Numeric data types, such as decimal and numeric, store the number of variable-length digits to the right or left of the decimal point. Scale is the number of digits to the right of the decimal point. Precision (Precision) defines the total number of digits, including the number of digits to the right of the decimal point. For example, because 14.88531 can be numeric (7,5) or decimal (7,5). If you insert 14.25 into the numeric (5,1) column, it is rounded to 14.3.

3. Approximate numeric data types

This category includes the data types float and real. They are used to represent floating-point data. However, because they are approximate, it is not possible to represent all the values accurately.

N in float (n) is the number of bits used to store the mantissa (mantissa) of the number. SQL Server uses only two values for this. If the designation is between 1~24, SQL uses 24. If you specify between 25~53, SQL uses 53. When float () is specified (empty in parentheses), the default is 53.

Table 1-3 lists the approximate numeric data types, describes them briefly, and describes the required storage space.

Table 1-3

Data type	Description	Storage space
float[(N)]	–1.79e+308~–2.23e– 308,0,2.23e–308~1.79e+308	n< = 24-4 bytes N> 24-8 bytes
Real ()	–3.40e+38~–1.18e– 38,0,1.18e–38~3.40e+38	4 bytes

Attention:

The synonym for Real is float (24).

4. binary data types

Binary data types such as varbinary, binary, varbinary (max), or image are used to store binary data, form files, Word documents, or MP3 files. The value of the 0X0~0XF is 16 binary. The image data type stores up to 2GB of files outside the data page. The preferred alternative data type for the image data type is varbinary (max), which holds up to 8KB of binary data, which typically performs better than the image data type. A new feature of SQL Server 2008 is the ability to store varbinary (max) objects in the operating system files through the FILESTREAM storage option. This option stores the data as a file and is not limited by the 2GB size of the varbinary (max).

Table 1-4 lists the binary data types, gives a brief description of them, and illustrates the required storage space.

Table 1-4

Data type	Description	Storage space
Binary (N)	n is between 1~8000 hexadecimal digits	N bytes
Image	Up to 231–1 (2?147?483?647) hexadecimal digits	1 bytes per character
Varbinary (N)	n is between 1~8000 hexadecimal digits	1 bytes per character + 2 bytes Extra overhead
Varbinary (max)	Up to 231–1 (2?147?483?647) hexadecimal digits	1 bytes per character + 2 bytes Extra overhead

5. Date and time data types

datetime and smalldatetime data types are used to store date and time data. The smalldatetime is 4 bytes and stores the time between June 6 and January 1, 1900 ~2079, and is only accurate to the nearest minute. The datetime data type is 8 bytes and stores the time between January 1, 1753 ~9999 December 31 and is accurate to the nearest 3.33 milliseconds.

SQL Server 2008 has 4 new date-related data types: datetime2, Dateoffset, date, and time. Examples of using these data types can be found through SQL Server Books Online.

The DateTime2 data type is an extension of the datetime data type and has a wider range of dates. Time is always used, minutes, seconds to store. You can define DATETIME2 data types with variable arguments at the end--such as datetime2 (3). 3 In this expression indicates that the fractional precision of seconds is stored at 3 bits, or 0.999. Valid values are between 0~9 and the default value is 3.

The DateTimeOffset data type is the same as the DateTime2 data type, with a time zone offset. The time zone offset is up to +/-14 hours and contains the UTC offset, so you can rationalize when the different time zones are captured.

The date data type stores only dates, which is a feature that is always needed. The time data type stores only the times. It also supports time (n) declarations, so you can control the granularity of fractional seconds. As with DateTime2 and DateTimeOffset, n can be between 0~7.

Table 1-5 lists the date/time data types, describes them briefly, and describes the required storage space.

Table 1-5

Data type	Description	Storage space
Date	January 1, 9999 ~ 31st	3 bytes
Datetime	January 1, 1753 ~9999 December 31, Accurate to the nearest 3.33 milliseconds	8 bytes
Datetime2 (N)	January 1, 9999 ~ 31st N between 0~7 specifies the fractional seconds	6~8 bytes
Datetimeoffset (N)	January 1, 9999 ~ 31st N between 0~7 specifies the fractional-second +/– offset	8~10 bytes
smalldatetime	January 1, 1900 ~2079 year June 6, accurate to 1 minutes	4 bytes
Time (N)	Hours: minutes: seconds. 9999999 N between 0~7 specifies the fractional seconds	bytes

6. Other system data types

There are also some data types that have not been seen before. Table 1-6 lists these data types.

Table 1-6

Data type	Description	Storage space
Cursor	Contains a reference to a cursor and a Can be used only as a variable or stored procedure parameter	Not applicable
Hierarchyid	Contains a reference to a position in the hierarchy	1~892 byte +2 Extra cost of bytes
sql_variant	May contain any system data classes Type, except text, ntext, Image, timestamp, XML, varchar (max), nvarchar (max), varbinary (max), sql_variant to and user-defined data types. Maximum Ruler Inch is 8000 bytes of data + 16 bytes (or meta-data)	8016 bytes
Table	Used to store a number for further processing Data sets. Definition is similar to create Table. Used primarily to return the result set of a table-valued function, They can also be used in stored procedures and batch processing	Depends on the table setting Number of rows that are justified and stored
Timestamp or Rowversion	Unique for each table, self- The value of the dynamic store. Typically used for version stamping, This value is automatically changed during insertion and each update	8 bytes
uniqueidentifier	Can contain globally unique identifiers (Globally Unique Identifier, GUID). The GUID value can be from newid () function is obtained. The value returned by this function pairs is unique to all computers. Although stored as a 16-bit binary value, But it appears as char (36)	16 bytes
Xml	Can be stored in Unicode or non-Unicode form	Up to 2GB

Attention:

The cursor data type may not be used in the CREATE TABLE statement.

The HierarchyID column is a new occurrence in SQL Server 2008. You might want to add a column of this data type to a table in which the data in its table row is represented in a hierarchy, just like an organization hierarchy or a manager/employee hierarchy. The value stored in the column is the path of the row in the hierarchy. Levels in the hierarchy appear as slashes. The value between the slashes is the number level of the member in the row, such as/1/3. You can use some special functions that are used with this data type.

XML data stores XML documents or fragments. Depending on the document, use UTF-16 or UTF-8, which is stored in the same size as text or ntext. xml data types are searched and indexed using special constructs. This is covered in more detail in the 15th chapter.

7. CLR Integration

In SQL Server 2008, you can also use the common language runtime (Common Language runtime,clr) to create your own data types and stored procedures. This allows users to write more complex data types using Visual Basic or C # to meet business needs. These types are defined as the class structure in the underlying CLR language. The 8th chapter will introduce the contents of its Management section in more detail.

SQL Server 2008 Data types