3 4
Once you have created a database, you can create the tables that store data within that database. In order to create those tables, however, you must first identify the data type that will be defined for each column. A data type is an attribute that specifies what type of data can be stored in a column, parameter, or variable. SQL Server provides a set of system-supplied data types. In addition, you can create user-defined data types that are based on the system-supplied data types. This lesson describes system-supplied data types and user-defined data types, and it explains how to identify which data type you should use when defining a column.
In SQL Server, each column has a related data type, which is an attribute that specifies the type of data (integer, character, monetary, and so on) that the object can hold. Certain objects other than columns also have an associated data type. The following objects have data types:
Assigning a data type to each column is one of the first steps to take toward designing a table. SQL Server supplies a set of system data types that define all of the types of data that you can use with SQL Server. You can use data types to enforce data integrity, because the data that is entered or changed must conform to the type specified in the original CREATE TABLE statement. For example, you cannot store someone's last name in a column defined with the datetime data type, because a datetime column accepts only valid dates.
Assigning a data type to an object defines four attributes of the object:
The following table provides descriptions of the categories of data types that SQL Server supports and descriptions of the base data types that each category contains:
Category | Description | Base Data Type | Description |
---|---|---|---|
Binary | Binary data stores strings of bits. The data consists of hexadecimal numbers. For example, the decimal number 245 is hexadecimal F5. | binary | Data must have the same fixed length (up to 8 KB). |
varbinary | Data can vary in the number of hexadecimal digits (up to 8 KB). | ||
image | Data can be variable length and exceed 8 KB. | ||
Character | Character data consists of any combination of letters, symbols, and numeric characters. For example, valid character data includes the "John928" and "(0*&(%B99nh jkJ" combinations. | char | Data must have same fixed length (up to 8 KB). |
varchar | Data can vary in the number of characters, but the length cannot exceed 8 KB. | ||
text | Data can be ASCII characters that exceed 8 KB. | ||
Date and time | Date and time data consists of valid date or time combinations. There are no separate time and date data types for storing only times or only dates. | datetime | Date data should range from January 1, 1753 through December 31, 9999 (requires 8 bytes per value). |
smalldatetime | Date data should range from January 1, 1900 through June 6, 2079 (requires 4 bytes per value). | ||
Decimal | Decimal data consists of data that is stored to the least-significant digit. | decimal | Data can be a maximum of 38 digits, all of which can be to the right of the decimal point. The data type stores an exact representation of the number; there is no approximation of the stored value. |
numeric | In SQL Server, the numeric data type is equivalent to the decimal data type. | ||
Floating point | Approximate numeric (floating-point) data consists of data preserved as accurately as the binary numbering system can offer. | float | Data is a floating-point number from –1.79E + 308 through 1.79E + 308. |
real | Data is a floating-point number from –3.40E + 38 through 3.40E + 38. | ||
Integer | Integer data consists of negative and positive whole numbers, such as –15, 0, 5, and 2,509. | bigint | Data is a number in the range from –2^63 (–9223372036854775808) through 2^63–1 (9223372036854775807). Storage size is 8 bytes. |
int | Data is a number in the range from –2,147,483,648 through 2,147,483,647 only (requires 4 bytes of storage per value). | ||
smallint | Data is a number in the range from –32,768 through 32,767 only (requires 2 bytes of storage per value). | ||
tinyint | Data is a number in the range from zero through 255 only (requires 1 byte of storage per value). | ||
Monetary | Monetary data represents positive or negative amounts of money. | money | Data is a monetary value in the range from –922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store value). |
smallmoney | Data is a monetary value in the range of –214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). | ||
Special | Special data consists of data that does not fit in any of the other categories of data. | bit | Data consists of either a 1 or a 0. Use the bit data type when representing TRUE or FALSE or YES or NO. |
cursor | This data type is used for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable. | ||
timestamp | This data type is used to indicate the sequence of SQL Server activity on a row and is represented as an increasing number in a binary format. | ||
uniqueidentifier | Data consists of a 16-byte hexadecimal number indicating a globally unique identifier (GUID). The GUID is useful when a row must be unique among many other rows. | ||
SQL_variant | This data type stores values of various SQL Server-supported data types except text, ntext, timestamp, image, and sql_variant. | ||
table | This data type is used to store a result set for later processing. The table data type can be used only to define local variables of type table or the return value of a user-defined function. | ||
Unicode | Using Unicode data types, a column can store any character defined by the Unicode Standard, which includes all of the characters defined in the various character sets. Unicode data types take twice as much storage space as non-Unicode data types. | nchar | Data must have the same fixed length (up to 4000 Unicode characters). |
nvarchar | Data can vary in the number of Unicode characters (up to 4000). | ||
ntext | Data can exceed 4000 Unicode characters. |
All data stored in SQL Server must be compatible with one of these base data types. The cursor data type is the only base data type that cannot be assigned to a table column. You can use this type only for variables and stored procedure parameters.
Several base data types have synonyms (for example, rowversion is a synonym for timestamp, and national character varying is a synonym for nvarchar).
User-defined data types are based on the system data types in SQL Server 2000. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability. For example, a user-defined data type called postal_code could be created based on the char data type.
When you create a user-defined data type, you must supply the following parameters:
When nullability is not explicitly defined, it will be assigned based on the ANSI null default setting for the database or connection.
NOTE
You can create a user-defined data type by using the sp_addtype system stored procedure or by using SQL Server Enterprise Manager.
In this exercise, you will identify the data types that you should use in your column definitions when you create the tables for the database that you created in Exercise 1. The tables and columns will be based on the objects and data constraints that you identified when you developed your database design. You will use system-supplied base data types for your database, rather than user-defined data types. Each column must have a data type. To perform this exercise, you will need paper and a pencil to write down the data type for each column.
A list of tables in the Northwind database appears in the right pane.
The Table Properties - Employees dialog box appears.
The Table Properties - Orders dialog box appears.
Close the Table Properties - Orders dialog box.
Which data constraints apply to the AuthorID column of the Authors table?
At this point, you are concerned only with identifying the data type for the AuthorID column and determining what type of data that column will contain. In this case, you want SQL Server to generate this ID automatically, which means that when you define this column, you will need to include the IDENTITY property in the definition. The IDENTITY property can be used only with an integer or decimal data type. You will learn more about defining this type of column in the next lesson.
You decide to use an integer data type rather than decimal, because decimal is unnecessary as an ID. You also decide that the smallint data type is adequate to use to identify authors. The smallint data type supports an ID of up to 32,767—many more authors than you anticipate the database ever needing to store.
What type of data will you store in this column?
Because a name can vary in length but will not likely exceed 30 characters, you decide to use the varchar(30) data type for each column.
You can assume that each column will contain only four characters. Because date and time data types do not include a year-only data type, you decide to use a character data type.
Which data type should you use for the YearBorn and YearDied columns?
What type of data will you store in this column?
Because the description can vary in length but will not likely exceed 200 characters, you decide to use the varchar(200) data type for each column.
What is the data type for each column in the BookShopDB tables?
NOTE
NOTE
SQL Server provides a set of system-supplied data types, which are attributes that specify what type of data can be stored in a column, parameter, or variable. Assigning a data type to each column is one of the first steps to take toward designing a table. There are nine categories of data types: binary, character, date and time, decimal, floating point, integer, monetary, special, and Unicode. Each category contains a set of base data types. All data stored in SQL Server must be compatible with one of these base data types. You can also create user-defined data types that are based on the system-supplied data types. When a user-defined data type is created, you must supply a name for the data type, the system data type upon which the new data type is based, and nullability (whether the data type allows null values). You can create a user-defined data type by using the sp_addtype system stored procedure or by using SQL Server Enterprise Manager.