Creating Your First Table in Microsoft SQL Server

Understanding SQL Server data types

SQL Server contains four distinct data type categories, as shown in Figure 5-3.

Each of the four categories contains subcategories. All columns within a table, declared variables, and parameters must have a corresponding data type. A data type simply specifies what type of data can be placed into the object (column, variable, parameter, and so on). Database integrity depends heavily on appropriately scoped data types; therefore, you should not always depend or rely on an application to enforce data type usage.

Numeric data types

The numeric data type has two subcategories: exact and approximate. Exact data types fit within a finite range of numbers. Table 5-1 lists and defines each exact numeric data type.

Table 5-1 Exact numeric data types

Data Type	Range	Storage
bigint	–9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	8 bytes
int	–2,147,483,648 to 2,147,483,647	4 bytes
smallint	–32,768 to 32,767	2 bytes
tinyint	0 to 255	1 byte
money	–922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	–214,748.3648 to 214,748.3647	4 bytes

If you need a column in a table that stores only values between 1 and 10, you should use a tinyint.

In addition to the data types in Table 5-1, the exact numeric category includes two more data types: decimal and numeric. They are slightly different from the others in that they allow decimal places, which are restricted by two values: precision and scale. Essentially, they are very similar in what and how they store data. Precision is the total number of digits that can be stored on both sides of the decimal place. This value can only be between 1 and 38. Scale is the number of digits that can be stored to the right of the decimal place and is specified only when precision is provided. This value will be between 0 and the specified precision. Therefore, if you wanted to store a four-digit number with only two digits to the right of the decimal place, you would use decimal(4,2). Table 5-2 lists precision ranges and their corresponding storage requirements.

Table 5-2 Precision ranges and storage requirements

Precision	Storage
1–9	5 bytes
10–19	9 bytes
20–28	13 bytes
29–38	17 bytes

The approximate subcategory is similar to the decimal and numeric data types in that one accepts a precision value, which is float. The other does not accept a precision value; instead, it can store up to seven digits, which includes digits on both sides of the decimal. For example, if you attempt to store the number 1234.5678 in a real data type, the value rounds up to 1234.568. However, if you want to maintain the precision of that value, you can store it in a float(25).

The main difference between the decimal and float data types is that you have a more precise level of storage with decimal than float. Table 5-3 lists precision ranges and their storage requirements for approximate numeric data types.

Table 5-3 Approximate precision ranges and storage requirements

nvalue	Precision	Storage
1–24	7 digits	4 bytes
25–53	15 digits	8 bytes

String data types

The string data type contains three subcategories: character, Unicode, and binary. Each contains three specific data types. The data types are similar in that each subcategory contains a fixed-length data type, a variable-length data type, and a data type that has been deprecated.

The character string subcategory will store non-Unicode data. The three types are as follows:

• char(n) Fixed-length string data type with a string length between 1 and 8,000.

• varchar(n) Variable-length string data type that can store up to 2 GB of data.

• text Deprecated data type. Replace it with a varchar(max).

The Unicode string subcategory will store both Unicode and non-Unicode data. The three types are as follows:

• nchar(n) Fixed-length string data type with a string length between 1 and 4,000.

• nvarchar(n) Variable-length string data type that can store up to 2 GB of data.

• ntext Deprecated data type. Replace it with nvarchar(max).

The binary string subcategory will store binary data. The three types are as follows:

• binary(n) Fixed-length binary data type with a string length between 1 and 8,000.

• varbinary(n) Variable-length binary data type with a string length up to 2 GB.

• image Deprecated data type. Replace with varbinary(max).

As a best practice, you should use the fixed-length (char, nchar, binary) data types across all subcategories when the values being stored are a consistent size. When the values are not consistent, you should use the variable-length data types (varchar, nvarchar, varbinary).

Date and time data types

Date and time data types are used widely in SQL Server databases. They offer the convenience of storing the date and time in various ways. There are six date and time data types.

• time(n) This data type stores the time of day without time-zone awareness based on a 24-hour clock. time accepts one argument, which is fractional seconds precision. You can provide only values between 0 and 7. As the number increases, so does the fractional precision. If you specify a data type of time(2), you can store a value similar to 11:51:04:24. Changing 2 to 3 increases the precision to three numbers, similar to 11:51:04:245.

• date This data type stores a date value between 01-01-01 and 12-31-9999.

• smalldatetime This data type stores a date and time value. The value of the date is between 1/1/1900 and 6/6/2079. The time precision is down to seconds. A value of 4/1/2012 11:15:04 can be stored using this data type.

• datetime This data type is similar to smalldatetime, but it offers a larger date range and a higher level of precision with regard to time. It offers the same date range as the date parameter, 01-01-01 to 12-31-9999, and it has a more precise value of time. A value of 4/1/2012 11:15:04:888 can be stored using this data type.

• datetime2(n) This data type is similar to datetime, but it offers extended flexibility of time. Unlike with datetime, you can control the fractional second precision with a value. You can provide only values between 0 and 7. If you specify a data type of datetime2(2), you can store a value similar to 4/1/2012 11:51:04:24. Changing 2 to 3 increases the precision to three numbers, similar to 4/1/2012 11:51:04:24.

• datetimeoffset This data type includes all the capabilities of datetime2, and it also has time-zone awareness. This makes it unique among the date and time data types. Using this data type, you can store the time-zone offset along with the date and time. A value of 4/1/2012 03:10:24 -06:00 can be stored using this data type.

Other data types

In addition to the data types covered in the preceding sections, SQL Server includes several other data types. Table 5-4 lists each additional data type with a brief description.

Table 5-4 Other SQL Server 2012 data types

Data Type	Description
cursor	A temporary copy of data that will be used for recursive or iterative processes. Of all the data types, this is the only one that cannot be included as part of a table.
rowversion(timestamp)	This data type automatically generates an 8-byte value similar to 0x0000000000000001. rowversion replaces the timestamp data type, which has been deprecated. This data type is typically used to detect changes in data.
hierarchyid	This is a positional data type. It represents a position in a hierarchy. hierarchyid is used to organize data such as a bill of materials and organizational charts.
sql_variant	This is the chameleon of data types. sql_variant can assume the identity of just about any data type in the list of SQL Server data types. Prior to performing any types of operations on it, you must convert it to the respective data type. For example, if you want perform addition, you must cast this data type to an int or some other numeric data type that supports that operation.
xml	You can store actual XML data using this data type.
geospatial	SQL Server supports two geospatial data types: GEOGRAPHY and GEOMETRY. GEOGRAPHY represents data in a round-earth coordinate system. GEOMETRY is a flat or planar data type in which you can store points, lines, and other geometric figures.
filestream	This data type allows you to store common unstructured data such as documents and images. SQL Server has been coupled with the NTFS file system, allowing the storage of varbinary(max) on the file system.

Since the data types in Table 5-4 are typically used for advanced operations, details regarding how to use them are beyond the scope of this book. If you feel the need to delve deeper into these data types, you can search SQL Server Books Online for some great examples.