Lesson 2:Identifying Data Types | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

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.

After this lesson, you will be able to:

Describe system-defined data types.
Explain how to create user-defined data types.
Identify which data types to use when defining columns in tables.

Estimated lesson time: 25 minutes

System-Supplied Data Types

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:

Columns in tables and views
Parameters in stored procedures
Variables
Transact-SQL functions that return one or more data values of a specific data type
Stored procedures that have a return code (which always has an integer data type)

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 kind of data contained by the object. For example, the data might be character, integer, or binary.
The length of the stored value or its size. The lengths of image, binary, and varbinary data types are defined in bytes. The length of any of the numeric data types is the number of bytes required to hold the number of digits allowed for that data type. The lengths of character string and Unicode data types are defined in characters.
The precision of the number (numeric data types only). The precision is the number of digits that the number can contain. For example, a smallint object can hold a maximum of five digits; therefore, it has a precision of five.
The scale of the number (numeric data types only). The scale is the number of digits that can be stored to the right of the decimal point. For example, an int object cannot accept a decimal point and has a scale of zero. A money object can have a maximum of four digits to the right of the decimal point and has a scale of four.

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

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:

Name
System data type upon which the new data type is based
Nullability (whether the data type allows null values)

When nullability is not explicitly defined, it will be assigned based on the ANSI null default setting for the database or connection.

NOTE

If a user-defined data type is created in the Model database, it exists in all new user-defined databases. If the data type is created in a user-defined database, however, the data type exists only in that user-defined database.

You can create a user-defined data type by using the sp_addtype system stored procedure or by using SQL Server Enterprise Manager.

Exercise 2: Identifying Column Data Types

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.

To review existing tables and columns and their data types

Open SQL Server Enterprise Manager.
Expand the console tree until you can view the list of objects in the Northwind database.
Click the Tables node listed beneath the Northwind node.

A list of tables in the Northwind database appears in the right pane.

Right-click the Employees table, then click Properties.

The Table Properties - Employees dialog box appears.

Review the list of columns and their data types. Notice that the size of each column is listed to the right of the data type.
Close the Table Properties - Employees dialog box.
Right-click the Orders table, then click Properties.

The Table Properties - Orders dialog box appears.

Review the list of columns and their data types.

Close the Table Properties - Orders dialog box.

Open the properties for several other tables, and review the columns and data types.

To identify the data types for the Authors table

Make a list of each column in the Authors table.
Refer to the data constraints that you identified for the Authors table when you developed your database design.

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.

Write down smallint next to the AuthorID column.
Review the database design and the data constraints for the FirstName and LastName columns.

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.

Review the database design and the data constraints for the YearBorn and YearDied columns.

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?

Review the database design and the data constraints for the Description column.

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.

Be sure to write down the name of the correct data type next to the name of each column in the Authors table.

To identify the column data types for tables in the BookShopDB database

Write down the name of each table in your database design.
Review the database design and the data constraints for each column in the tables.
Identify the data type for each column.

What is the data type for each column in the BookShopDB tables?

NOTE

It is sometimes difficult to predict exactly what length you should use for data types such as char and varchar. You can get a feel for lengths, however, by reviewing column properties in existing databases, such as the Pubs database or the Northwind database.

NOTE

Notice that the State column in the Customers table uses the varchar(7) data type rather than char(2), as in the Employees table. Because a value is not required for this column in the Customers table, a default value of "unknown" will be defined (rather than permitting a null value). Nullability and default values are discussed in more detail in Lesson 3.

Be certain to write down the data type next to the name of each column (or at least record this information in some way). You will need this information for later exercises.

Lesson Summary

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.