Data-processing systems involve the storage, processing, and retrieval of information. You must define where data will be stored before it can be processed and retrieved. All units of information, from CHARacters to the logical definition of the entire database, can be defined through SQL Server components.
You may recall from prior chapters that the structure of a SQL Server system is:
In this chapter, you find out how to create the tables and the components of the table, the row and column. As with other database systems, each column can have a specific datatype associated with it, and each datatype has certain CHARacteristics.
Data in a relational database such as SQL Server is stored in tables that are two-dimensional arrays. You'll recall from Chapter 1 that you have experience working with tables from everyday life, such as train or bus schedules.
The columns and rows of a table are already familiar to database users. Tables were chosen as the logical structure for storing data because of their familiarity to users and ease of use for retrieving, displaying, and manipulating data.
You can create SQL Server database tables with the CREATE TABLE Transact-SQL statement, or with SQL Enterprise Manager. In the next two sections, you see how to create tables using these two approaches.
You can create up to two billion tables in each database. The major part of the creation of a table is the definition of the datatypes for columns, explained in the remainder of this chapter.
In Transact-SQL, you can create tables using the Create Table statement. Create Table lets you set up several different options to the table, including where it's located, how the columns are defined, and so on. The syntax for CREATE TABLE is as follows:
CREATE TABLE [[database.]owner.]table_name (column_name datatype [not NULL | NULL] IDENTITY[(seed, increment)][constraint] [, column_name datatype [not NULL | NULL IDENTITY[(seed, increment)]]]. [constraint]...) [ON segment name]
NOTE: For more information on identity columns, see the "Identity Property" section later in this chapter.
Enter the name of the table (table_name) following the keywords CREATE TABLE. You can use up to 30 CHARacters to name a database object, such as a table. The column names are entered within parentheses. You define the name and type of the column by entering a column name up to 30 CHARacters long, followed by a Transact-SQL datatype.
NOTE: Everything stored in a database is stored as an object. A database object, such as a table, has information kept about it in system tables. For example, a table created by you has the name of the table, the type of data that is stored in its columns, and other CHARacteristics stored in the system table sysobjects.
Optionally, you can enter the database in which the table is created, as well as an owner of the table. You'll find it more convenient to define the current database in which you're working first with a USE database-name command. After you define your current database with the USE command, all subsequent commands are performed within the database specified with the USE command.
NOTE: Your SQL Server account is defined with a current database. The default database that you're directed to should be the one in which you work exclusively or most often.
If you don't enter the name of the owner of an object, such as a table, when you create a table, you'll be its owner. Often, tables are created from the SA account, to restrict subsequent access to the tables.
The owner of the database in which the table is defined is automatically granted the CREATE TABLE permission, which allows the creation of tables. The database owner or the SA can grant CREATE TABLE permission to other users so that they can create tables in the database. You don't have to grant permission to create temporary tables in a database.
You can use ON segment_name to specify the segment on which the table is created. The segment must have already been created and assigned to a logical device. If you later create a non-clustered index for your table and don't specify a segment, the table and the non-clustered segment are placed in the default segment.
One use you might find for defining specific segments for a table is the separation of the table information from the clustered indexes that support it. You can optimize the system by placing the table on a different segment from its non-clustered indexes. If you specify the creation of a table and its non-clustered index on different segments, SQL Server can perform queries that use the index faster.
TIP: Before using segments, consider carefully whether you can effect the same performance gains by using Windows NT's RAID capabilities. By placing information on different segments, the goal is typically to help lessen the read-write head movement on the disk drive. If you have drives that are striped using the capabilities of NT, you'll have the same results, or at least close enough to them, to warrant consideration of the RAID approach.Using striped drives can also save you the hassle of working with and remembering segments when you create databases, tables, and indexes.
Queries that use a table's non-clustered index can be performed faster if both are located on different segments, and if the segments are on different logical devices located on different physical disks. Sets of information, such as an index and its associated table, that are stored on different physical disks can be accessed faster than a set stored on a single disk.
Data can be referenced faster because the underlying operating system and disk subsystems can perform a large part of the data transfer from two physical disks simultaneously. Sets of data located on a single physical disk must be accessed separately due to the physical layout of the disk drive.
See "Defining Non-Clustered Indexes," Chapter 11.
Using SQL Enterprise Manager to create tables gives you the advantage of the graphical interface to specify the different attributes to the table. This includes things like datatypes, column lengths, and so on. To create tables with Enterprise manager, you still need to know the same information as you do when you create the tables with ISQL. The difference is in how the interface takes care of some of the details for you.
To begin working with tables, start Enterprise Manager, and then select the server you want to work with. Next, open the Databases tree, and open the database to which you want to add the new table. When you do, you'll see two different options: Groups/Users and Objects. Select and open the Objects tree and you'll be presented with the following list:
Right-click the Tables option; you now have two options. You should select New Table. When you do, a dialog box is displayed, allowing you to define a table for your system. See Figure 6.1 for an example with some of the values already entered.
FIG. 6.1
SQL Enterprise Manager will walk you through the options necessary to create a table.
When you create a new column, you're prompted for the different datatypes, including any custom datatypes you've created, with a drop-down list box in the datatype column in the dialog box. This ensures that you select valid data types, and in some cases, the Size attribute is determined for you based on the datatype selection (see Figure 6.2).
FIG. 6.2
You'll be prompted for the datatype, and Enterprise Manager may try to indicate pre-set
sizes for you based on your selection.
When you're satisfied with your table, select the disk button to save the table. In Figure 6.2, you'll notice several different options displayed on the bottom portion of the dialog box. These are the Advanced Options, which you should browse through when you're creating or managing a table. Each of the options presented can be changed after the fact, but changes may require some updating of your table.
Other options, for example, in order to indicate the identity column for your table, must be completed when the table is created. They cannot be added after the fact. One way around this is to create a new table, based on the original but with the modifications you need. Then, using the transfer utility, you can copy the contents of the original table into the newly created table.
Finally, rename the original table, something like "old_tablename" and rename the new table to the original name. This step allows any applications you have that reference the table to keep working with the table without modification. Of course, saving the original with the modified name is a safety precaution, and you should extensively test any applications that use the new table prior to dropping the original table.
The major part of defining a table is specifying the datatypes for the columns of the tables. Transact-SQL lets you define several datatypes, including those for the storage of CHARacters, numbers, and bit patterns. You can also define your own datatypes for use in stored procedures, tables, and other work that you'll be doing with the database tables.
You must define at least one column for a table, and you can define up to 250 columns. You're also limited to a maximum row length of 1,962 bytes.
TIP: You can use image or text datatypes to get around the 1,962-byte limit for rows. Columns that are defined by using the image and text datatypes are stored outside the table and can store more than two billion bytes of data.You should be careful to follow the rules for relational database design, whenever feasible, to ensure the optimum response time and use of your SQL Server engine.
For more information on database design, refer to Chapter 4, "Data Modeling, Database Design, and the Client/Server Model."
Tables are created using a unit of measure called an extent. When you create a new table, the allocation of space for the table is initially set at one extent, which is eight pages, each of which is 2K in size. When the table fills the space in the already allocated extents, additional extents are automatically implemented--up to the space allocated to the overall database size.
TIP: Use the system procedure sp_spaceused to obtain a report on the space allocated to a table or the graphical display in SQL Enterprise Manager. See Chapter 5, "Creating Devices, Databases, and Transaction Logs," for information on displaying allocated space.
Creating a temporary table is a useful technique. You can create two types of temporary tables in SQL Server: local and global. A local temporary table is created if the first CHARacter of the table name is a pound sign (#). A local temporary table can be accessed only in the session in which it is created. A local temporary table is automatically dropped when the session in which it is created ends. You can't use more than 20 CHARacters, including the pound sign, to name a local temporary table.
You create a temporary table that can be accessed in any session by defining a table with two pound signs (##) as the first two CHARacters of the table name. Each session can be created by a different user on a different client system. A temporary table that is accessible from multiple sessions is called a global temporary table and is automatically dropped when the last session using it ends.
TIP: Constraints can be defined for temporary tables but foreign key constraints are not enforced.
You can use temporary tables to store sets of data that need to be operated on before permanent storage. For example, you can combine the results of the data from multiple data sets into a temporary table and then access the combined data in the temporary table throughout your session. Data that has already been combined in a temporary table can be accessed faster than data that must be dynamically accessed from multiple tables. A temporary table that combines the results of two tables is faster to access because SQL Server doesn't need to reference the database tables to retrieve the information.
See "Performing Relational Joins," Chapter 8.
NOTE: Sessions are established differently depending on how you'll be accessing the server. A session is associated with a live connection to the database. For example, if you query a table and use a Dynaset type of dataset, that is, one that's active and updatable, the session will remain active. If you then connect to the database for another dataset inquiry, a separate and distinct session will be opened. Information in the temporary tables from the first session won't be available to the second session.
You might also find it convenient to use a temporary table to make a set of data available to a stored procedure that's invoked from another procedure. You'll find it easier to make data available to another procedure within a temporary table rather than pass data as a set of parameters.
See "Managing Stored Procedures and Using Flow-Control Statements," Chapter 14.
The datatype is the first CHARacteristic you define for the column of a table. The datatype of a column controls the type of information that can be stored within the column. Define the datatype by following the column name with a keyword that may also require some parameters. After you define the datatype of a table column, it's stored as a permanent CHARacteristic and can't be changed.
You can also use datatypes to define other data-storage structures, such as parameters and local variables. Parameters and local variables are storage structures defined in RAM instead of on disk. You're limited to a subset of the datatypes for the definitions of parameters and variables.
See "Using Parameters with Procedures," and "Defining and Using Variables," Chapter 14.
The next sections review each of the different system-defined datatypes that you can use in the definition of your SQL Server tables and stored procedures.
Numeric integers are the first of several datatypes that you can use to define storage objects. Integer datatypes enable you to store whole numbers. You can directly perform arithmetic operations on integers without using functions. Numbers stored in integer datatypes always occupy the same amount of storage space, regardless of the number of digits within the allowable ranges for each integer datatype.
NOTE: The name of a datatype, such as integer, is case-insensitive.
int or integer int (or integer) is the first of three integer datatypes. You can store negative and positive whole numbers within the range of -(2**31) to 2**31--approximately 4.3 billion numbers. The range is -2,147,483,648 to 2,147,483,647. Each value that is stored in an int datatype is stored in four bytes, using 31 bits for the size or magnitude and one bit for the sign.
NOTE: A set of two asterisks is used to denote exponentiation. The range of numbers for numeric digits is frequently referenced using a base number raised to a power because it allows the range to be specified precisely and compactly.
smallint smallint is the second integer datatype. You can store whole numbers within the range -32768 to +32767. Each value that is stored in a smallint datatype occupies two bytes and is stored as 15 bits for the magnitude and one bit for the sign.
tinyint You can store only whole positive numbers in a storage structure defined as tinyint within the range 0 to 255. Each value stored as a tinyint occupies one byte. The following example shows the creation of a table with three columns (see Listing 6.1). The columns are defined as the int, smallint, and tinyint datatypes. A single row is inserted into the number_example table with values within the acceptable range for storage of each datatype. Select is subsequently used to retrieve the row.
CAUTION: Database languages and programming languages have keywords. Keywords are the words that force an action to occur in an environment. To avoid confusion and error, avoid using keywords when naming tables, columns, and so on.
create table number_example (int1 int,int2 smallint,int3 tinyint) INSERT into number_example values (400000000,32767,255) (1 row(s) affected) select * from number_example int1 int2 int3 ----------- ------ ---- 400000000 32767 255 (1 row(s) affected)
Enforcing Value Ranges SQL Server automatically enforces the insertion of values within the range of each datatype. In the following example (see Listing 6.2), values are inserted into columns, defined as smallint and tinyint, although the values are outside the range of acceptable values. The column values are specified in the values clause of the INSERT statement in the same order in which the columns were defined in the table. SQL Server returns an error message that describes the reason for the failed row insertion, such as the attempted insertion of a value that is outside the allowable range for the datatype.
INSERT into number_example values (1,32768,1) Msg 220, Level 16, State 1 Arithmetic overflow error for type smallint, value = 32768. INSERT into number_example values (1,1,256) Msg 220, Level 16, State 2 Arithmetic overflow error for type tinyint, value = 256.
TIP: Use a tinyint or smallint to store integer values in one-quarter or one-half the storage space used for storing integer values in an int datatype. These are especially useful as flags, status indicators, and so forth.
Floating-point datatypes are the second group of several numeric datatypes you can use to define storage structures, such as table columns. Unlike the integer datatypes, floating-point datatypes can store decimal numbers.
Unfortunately, the floating-point datatypes are subject to the rounding error. The storage of a value in a numeric datatype, which is subject to the rounding error, is accurate only to the number of digits of precision specified. For example, if the number of digits of precision is 15, a number larger than 15 digits can be stored, but the digits beyond 15 may inaccurately represent the initial number inserted into the storage.
Also, the number may inaccurately return results of computations that involve floating-point datatypes. The rounding error affects a number's least-significant digits, the ones at the far right. You can accurately store numbers within the number of digits of precision available in floating-point datatype.
NOTE: Microsoft calls datatypes, such as the floating-point datatypes, approximate numeric datatypes because values stored in them can be represented only within the limitations of the storage mechanism. You should avoid performing comparisons, such as in a WHERE clause, of data that is stored in approximate datatypes because a loaded value that is larger than the number of digits of precision is altered by the rounding effect during storage.
The REAL Datatype The first of the floating-point datatypes is REAL, which is stored in four bytes. You can store positive or negative decimal numbers in the REAL datatype, with up to seven digits of precision. You can store numbers in a column defined as REAL within the range of 3.4E-38 to 3.4E+38. The range of values and representation is actually platform-dependent. Remember that SQL Server evolved from the original Sybase SQL Server implementation. The REAL datatype stored on each of the several computer systems that a Sybase version was written for varied in the range of allowable CHARacters and the actual representation of CHARacters. For example, the range of decimal numbers stored by OpenVMS on Digital's VAX computers is 0.29E - 38 to 1.7E+38.
The underlying operating system that SQL Server runs on is supported on Intel, MIPS, PowerPC, and Alpha AXP systems. You should consider the previously stated value, the range of 3.4E-38 to 3.4E+38, as approximate and check the range of allowable numbers for the floating-point datatype that is stored in four bytes on your Windows NT system.
You should also realize that data stored in floating-point datatypes, that is moved between different NT platforms with different processor architectures may require conversion to compensate for different representations and range of values.
float[(n)] Datatypes The second of the floating-point datatypes is float, that is stored in eight bytes if a value for n is omitted. You can store positive or negative decimal numbers in the float datatype with as many as 15 digits of precision. You can store numbers in a column defined as float within the range of 1.7E-308 to 1.7E+308. If you specify a value for n within the range of 1 to 7, you're actually defining a real datatype. If you specify a value within the range of 8 to 15, the datatype has the identical CHARacteristics as if n were omitted.
In the following example (see Listing 6.3), a table is created with two columns defined as real and float. A single row is added with identical numbers that are subsequently added to each column of the table. The retrieval of the row from the table shows that the number stored in the real column was stored accurately to only 7 digits, the maximum number of digits of precision for a real datatype. The same 11-digit number was stored correctly in the column defined with the datatype float because float allows up to 15 digits to be stored accurately.
create table precision_example (num1 real,num2 float) INSERT into precision_example values (4000000.1234,4000000.1234) select * from precision_example num1 num2 ------------------- -------------------- 4000000.0 4000000.1234 (1 row(s) affected)
decimal[(p[, s])] and numeric[(p[, s])] Datatypes You can use either the name decimal or numeric to select a datatype that, unlike float or real, allows the exact storage of decimal numbers. The scale and digits of precision are specified in the arguments p and s. You can store values within the range 10**38-1 through -10**38 using 2 to 17 bytes for storage. Use p to define the number of digits that can be stored to the left and right of the decimal point. Use s to define the number of digits to the right of the decimal point that must be equal to or less than the value of p. If you omit a value for p, it defaults to 18; the default of s is 0. Table 6.1 shows the number of bytes that are allocated for the specified precision (value of p).
Bytes Allocated | Precision |
2 | 1-2 |
3 | 3-4 |
4 | 5-7 |
5 | 8-9 |
6 | 10-12 |
7 | 13-14 |
8 | 15-16 |
9 | 17-19 |
10 | 20-21 |
11 | 22-24 |
12 | 25-26 |
13 | 27-28 |
14 | 29-31 |
15 | 32-33 |
16 | 34-36 |
17 | 37-38 |
Listing 6.4 shows the storage and subsequent retrieval of a single row stored with the columns of a table defined as numeric/decimal datatypes. This example shows the default precision and scale and an explicit precision and scale being displayed.
create table definition_example (num1 decimal,num2 numeric(7,6)) INSERT into definition_example values (123456789123456789,1.123456) select * from definition_example num1 num2 --------------------------------- 123456789123456789 1.123456 (1 row(s) affected)
NOTE: The maximum precision permitted in the numeric/decimal datatypes is 28 unless you start SQL Server from the command line and change the precision. Use the command sqlservr with the option /p, which has the following syntax:sqlservr [/dmaster_device_path][/pprecision_level]For example, the following command starts SQL Server with a maximum precision of 38:
sqlservr /dg:\sql60\data\master.dat /p38.If no value is specified after the precision qualifier /p, the precision for the numeric/decimal datatype is set to the maximum of 38.
You'll frequently use CHARacter datatypes to define table columns or other storage structures. CHARacter datatypes allow the storage of a wider variety of symbols than numeric datatypes. CHARacter datatypes enable you to store letters, numeric symbols, and special CHARacters such as ? and >. You enter CHARacter data in either single or double quotation marks (' or ") when loading it into a storage area, such as the column of a table.
CHAR Datatype CHAR is the first type of CHARacter datatype. When you store data in a CHAR datatype, each symbol or CHARacter stored uses one byte. The number in parentheses specifies the size of storage for all sets of CHARacters. For example, if you define a table column as the datatype CHAR(15), each value of the column is 15 bytes in size and can store 15 CHARacters. If you enter fewer than 15 CHARacters, SQL Server adds blanks after the last specified CHARacter. You can define a CHAR(n) datatype to contain up to a maximum of 255 CHARacters. Remember, the column value always contains the specified number of CHARacters. SQL Server automatically adds spaces to the end of a CHAR value to fill the defined length of space.
NOTE: If a column is defined CHAR and allowed to be NULL, it will be treated as a varCHAR column.
NOTE: Although the default installation of Windows NT uses the ASCII code to store CHARacter datatypes, you can install Windows NT so that UNICODE is used instead of ASCII. UNICODE stores CHARacter symbols in 16 bits, two bytes, rather than ASCII's one byte. If Windows NT is defined to use UNICODE instead of ASCII, you must confirm the size of each CHARacter for CHARacter datatypes in applications, such as SQL Server.
varCHAR Datatype You can use the varCHAR datatype to store a variable-length string of up to 255 CHARacters. Unlike the CHAR datatype, the storage space used varies according to the number of CHARacters stored in each column value of rows of the table. For example, if you define the table column as varCHAR(15), a maximum of 15 CHARacters can be stored in the corresponding column of each table row. However, spaces aren't added to the end of the column value until the size of each column is 15 bytes. You can use a varCHAR to save space if the values stored in a column are variable in size. You can also specify a varCHAR datatype using the keyword CHAR varying.
Using CHARacter Datatypes In Listing 6.5, a table is created with two columns defined as CHAR and varCHAR datatypes. The inserted row stores only two CHARacters in each column of the row. The first column is padded with three spaces so that it occupies five bytes of storage. The second column of the row isn't padded and occupies only two bytes of storage to store the two CHARacters. The retrieval of the row in the example displays each column value identically, masking the underlying storage difference.
create table string_example (CHAR1 CHAR(5),CHAR2 varCHAR(5)) INSERT into string_example values (`AB','CD') select * from string_example CHAR1CHAR2 ---------- AB CD (1 row(s) affected)
In the following example (Listing 6.6), a row is inserted into the table that contains column values that are longer by one CHARacter than the maximum length of the datatypes of the table columns. The select statement in the example shows that the column values of the inserted row were truncated, or cut off, and contain only the first five CHARacters of the column values. You don't receive a message that the truncation occurs when a row is inserted.
INSERT into string_example values (`abcdef','abcdef') select * from string_example CHAR1CHAR2 ---------- AB CD abcde abcde (2 row(s) affected)
TIP: Use the text datatype, which allows the storage of more than four billion CHARacters to store sets of CHARacters that are longer than 255 CHARacters.
Here are a few points about text and other CHARacter data types that will help when you begin using them:
The datetime and smalldatetime datatypes store a combination of the date and time. You'll find it more convenient to store dates and times in one of the date and time datatypes instead of a datatype, such a CHAR or varCHAR. If you store data in one of these datatypes, you can easily display them because SQL Server automatically formats them in a familiar form. You can also use specialized date and time functions to manipulate values stored in this manner.
If you store date and time in CHAR or varCHAR, or if you store time in numeric datatypes, date and time values aren't automatically formatted in conventional ways when they're displayed.
datetime Datatype datetime lets you define storage structures, such as table columns. In the datetime datatype, you can store dates and times from 1/1/1753 AD to 12/31/9999 AD. The total storage of a datetime datatype value is eight bytes. SQL Server uses the first four bytes to store the number of days after or before the base date of January 1, 1900. Values that are stored as negative numbers represent dates before the base date. Positive numbers represent dates since the base date. Time is stored in the second four bytes as the number of milliseconds after midnight.
NOTE: Datetime values are stored to an accuracy of 1/300th of a second (3.33 milliseconds) with values rounded downward. For example, values of 1, 2, and 3 milliseconds are stored as 0 milliseconds; the values of 4 through 6 milliseconds are stored as 3 milliseconds.
When you retrieve values stored in datetime, the default format for display is MMM DD YYYY hh:mmAM/PM, for example, Sep 23 1949 11:14PM. You must enclose datetime values in single quotation marks when they're used in an INSERT or other statement. You can enter either the date or time portion first, because SQL Server can recognize each portion and store the value correctly.
You can use upper- or lowercase CHARacters for the date and one or more spaces between the month, day, and year when you enter datetime values. If you enter time without a date, the default date is January 1, 1900. If you enter the date without the time, the default time is 12:00 A.M. If you omit the date and the time, the default value entered is January 1, 1900 12:00 A.M.
You can enter the date in several ways. Each is recognized and stored correctly by SQL Server. You can enter the date in an alphabetic format, using either an abbreviation for the month or the full name of the month. You can use or omit a comma between the day and year.
If you omit the century part of the year, decades that are less than 50 are represented as 20 and those that are 50 or more are entered as 19. For example, if you insert the year 49, the complete year stored is 2049. If you enter the year as 94, the complete year stored is 1994. You must explicitly enter the century if you want a century other than the default. You must supply the century if the day is omitted from the date value. When you enter a date without a day, the default entry is the first day of the month.
The option set dateformat isn't used if you specify the month of the year in alphabetic format. If you installed SQL Server with the US_English Language option, the default order for the display of datetime values is month, day, and year. You can change the default order for the display of the date portion of a datetime value using the set dateformat command.
See "Understanding Server, Database, and Design Query Options," in Chapter 16.
You can enter dates several ways, including the following examples:
The numeric format for datetime values permits the use of slashes (/), hyphens (-), and periods (.) as separators between the different time units. When you use the numeric format with a datetime value, you must specify the month, day, and year of the date portion of the value.
In the numeric format, enter a separator between the month, day, and year entered in the order defined for dateformat. If you enter the values for a datetime datatype that is in the incorrect order, the month, day, or year will be misinterpreted and stored incorrectly. If you enter the information in the incorrect order, you may also receive an error message that tells you the date is out of range.
The following is an example of several entries for the numeric form of the date portion of a datetime datatype value with set dateformat defined as month, day, and year and the language as US_English:
The last of the possible formats for the date portion of a datetime datatype value is an unseparated 4-, 6-, or 8-digit value, or a time value without a date value portion. The dateformat controlled through set dateformat doesn't affect datetime datatype values referenced as the unseparated digit format.
If you enter a 6- or 8-digit unseparated value, it's always interpreted in the order of year, month, day. The month and day are always interpreted as two digits each; four unseparated digit values are interpreted as the year; the century and the month and day default to the first month and the first day of that month. Table 6.2 lists the possible interpretations of unseparated digit datetime datatype values:
Digits | Equivalent Representation in Alphabetic Format |
710624 | June 24, 1971 |
19710624 | June 24, 1971 |
1971 | January 1, 1971 |
71 | Not valid |
'' | January 1, 1900 12:00 A.M. |
When working with the datetime datatype, keep in mind that if you insert a column with an empty string as a value, and that column is defined as a datetime column, you won't get a NULL entry as you might expect. When two single quotation marks are used with no CHARacters inserted between them as the value for either of the date and time datatypes, the entry January 1, 1900, and 12 midnight is always inserted by SQL Server.
You must enter the time with the time units in the following order: hours, minutes, seconds, and milliseconds. You must have a colon as a separator between multiple time units to allow a set of digits to be recognized as a time rather than a date value. You can use AM or PM, specified in upper- or lowercase, to specify before or after midnight.
You can precede milliseconds with a period or a colon, which affects the interpretation of the millisecond unit. A period followed by a single digit specifies tenths of a second; two digits are interpreted as hundredths of a second; three digits are interpreted as thousandths of a second. A colon specifies that the following digits will be interpreted as thousandths of a second. Table 6.3 shows several possible interpretations of the time portion of a datetime datatype value.
Time | Interpretation |
11:21 | 11 hours and 21 minutes after midnight |
11:21:15:871 | 11 hours, 21 minutes, 15 seconds, and 871 thousandths of a second A.M. |
11:21:15.8 | 11 hours, 21 minutes, 15 seconds, and eight tenths of a second A.M. |
6am | 6 A.M. |
7 PM | 7 P.M. |
05:21:15:500 AM | 5 hours, 21 minutes, 15 seconds, and 500 milliseconds after midnight |
smalldatetime smalldatetime is the second of the date and time datatypes you can use to define storage structures, such as table columns. In the smalldatetime datatype, you can store dates and times from 1/1/1900 AD to 6/6/2079 AD. The total storage of a smalldatetime datatype value is four bytes. SQL Server uses two bytes to store the number of days after the base date of January 1, 1900. Time is stored in the other two bytes as the number of minutes after midnight. The accuracy of the smalldatetime datatype is one minute. You can use smalldatetime to store values that are within its more limited range and lesser precision when compared to datetime.
TIP: Use the smalldatetime datatype instead of the datetime datatype to store values in half the storage space.
In Listing 6.7, one column is defined using the datetime datatype, and the second column is defined using the smalldatetime datatype. After the table is created, a minimum value is inserted into each column of a single row for the respective datatypes.
create table date_table (date1 datetime,date2 smalldatetime) INSERT into date_table values (`Jan 1 1753','Jan 1 1900') select * from date_table date1 date2 ------------------------------------------------------ Jan 1 1753 12:00AM Jan 1 1900 12:00AM (1 row(s) affected)
In the following example, successive INSERT statements insert a date that is beyond both the range of the columns defined using the smalldatetime and the range of datetime datatypes. An error is returned as a result of both INSERT statements.
In the following example, the conversion of CHAR to smalldatetime results in a smalldatetime value out of range:
INSERT into date_table values (`May 19 1994', `Jun 7 2079') Msg 296, Level 16, State 3
The following example shows a syntax error converting datetime from a CHARacter string.
INSERT into date_table values (`Jan 1 10000','May 19 1994') Msg 241, Level 16, State 3
Transact-SQL contains a set of specialized datatypes for data storage. Most of the time you'll store data in more conventional datatypes such as integer, floating-point, and CHARacter. You can store dates and times in the datetime or smalldatetime datatypes.
Although you'll probably find that you can use the integer, floating-point, CHARacter, and date/time datatype formats for storing 90 percent of your data, in some cases you'll probably need a more custom solution.
In these cases, you can use one or more of the specialized datatypes. For example, you may need to store only data that can be represented as true or false, yes or no. Because this is a binary condition, you may decide to create a custom datatype. As another example, you may need to store sets of data in a column that is larger than the 255 CHARacter limitation of the conventional CHARacter datatypes. Several additional datatypes are available, so you can choose the best datatype for storing your information.
bit You can use the bit datatype to store information that can be represented in only two states. A bit datatype is stored in a single bit. As a result, only two possible patterns can be stored--zero(0) or one(1). If you enter any other value than zero or one in a data-storage structure, such as a table column, one is stored. You can't define the bit datatype to allow NULL entries.
TIP: Although it is not explicitly stated in the SQL Server documentation, the bit datatype corresponds to the Boolean datatype in other DBMSes and programming languages.
You can also use a single byte to define up to eight different bit columns of a table using the bit datatype. The amount of space allocated for one or more bits is a single byte, and the bit columns don't have to be contiguous. If you define nine columns of a table using the bit datatype, two bytes are used for the total of nine bit datatypes.
NOTE: SQL Server stores information about columns defined using bit datatypes in the syscolumns system table by storing an offset to the bit column in the status column. You can't define an index that uses a column defined as a bit datatype.
timestamp If you define a column of a table using the timestamp datatype, a counter value is automatically added to the timestamp column whenever you insert a new row or UPDATE an existing row. You can't explicitly enter a value into the column defined as a timestamp. A uniformly increasing counter value can be implicitly inserted only into a timestamp column by SQL Server. The counter value inserted by SQL Server into a timestamp column specifies the sequence of operations that SQL Server has performed. Values entered into a timestamp column are stored in a varbinary(8) format, not a datetime or smalldatetime format. NULL values are permitted in a timestamp column by default. A timestamp value isn't a date and time, but it's always unique within the table and database. You can define only a single column of a table as a timestamp.
NOTE: Timestamps are often used to ensure that a row can be uniquely identified. If you're updating columns in a row, it's a common practice to specify the timestamp field in the where clause of your UPDATE statement. This ensures that you UPDATE only one row of the table. You can be assured of the uniqueness of the value because the server maintains and UPDATEs it any time you insert or UPDATE a row.Timestamps are also used, again, as part of the where clause, to prevent two people from updating the same row. Because the timestamp is UPDATEd automatically whenever an UPDATE is made to the row, you can be sure that you're not going to overwrite someone else's information. If someone else UPDATEs a row that you're now working on, when he or she saves the UPDATE, the row's timestamp will be UPDATEd, no longer matching your copy. When you issue the UPDATE command to save your changes, the where clause will fail because it can't find the specific row that you retrieved. Timestamps are excellent, server-maintained ways to make sure that you have a unique row identifier.
If you define a column with the column name timestamp and don't specify a datatype, the column is defined using the timestamp datatype. You can display the current timestamp value that is applied to the next timestamp column of an UPDATE row, or UPDATEd to a new row added using the global system variable @@dbts.
NOTE: You can use a select statement to reference the global variable @@dbts using the following syntax:select @@dbtsFor example, the execution of this statement during the preparation of this chapter returned the following current timestamp value:
0x01000000a3d2ae08
binary(n) You can use the binary datatype to store bit patterns that consist of up to 255 bytes. Use the integer specified in parentheses to define the length of all bit patterns from one to 255 bytes. You must specify the size of a binary column to be at least one byte, but you can store a bit pattern of all zeroes. You must enter the first binary value preceded with 0x. You can enter binary data using the CHARacters zero through nine and A through F. For example, enter the value A0 by preceding it with 0x, in the form 0xA0. If you enter values greater than the length that you defined, the values are truncated. Values are also padded with zeroes after the least significant digit.
Here's another example (see Listing 6.8). A column defined as binary(1) can store up to the maximum value of ff. In the following example, a table is defined as having two columns, with the datatypes binary(1) and binary(2). Three INSERT statements are used to enter successive pairs of values of 0, 1, ff, and fff in both columns.
create table binarytable (x binary(1),y binary(2)) INSERT into binarytable values (0x0,0x0) INSERT into binarytable values (0x1,0x1) INSERT into binarytable values (0xff,0xff) INSERT into binarytable values (0xfff,0xfff) select * from binarytable ... x y ---------- 0x00 0x0000 0x01 0x0100 0xff 0xff00 0x0f 0x0fff (4 row(s) affected)
varbinary(n) You can use the varbinary datatype to store bit patterns that consist of up to 255 bytes. You use the integer specified in parentheses to define the maximum length of all bit patterns from one to 255 bytes. You must specify the size of a binary column to be at least one byte, but you can store a bit pattern of all zeroes. Unlike the binary datatype, varbinary datatype storage is limited to just enough space for the length of the actual value. Like the binary datatype, you must enter the first binary value preceded with 0x. You can enter binary data using the CHARacters zero through nine and A through F. If you enter values that are greater than the maximum length you defined, the values are truncated.
In Listing 6.9, a table is defined with two columns with the varbinary(1) and varbinary(2) datatypes. Three INSERT statements are used to enter successive pairs of values of 0, 1, ff, and fff in both columns.
create table varbinarytable (x varbinary(1),y varbinary(2)) INSERT into varbinarytable values (0x0,0x0) INSERT into varbinarytable values (0x1,0x1) INSERT into varbinarytable values (0xff,0xff) INSERT into varbinarytable values (0xfff,0xfff) select * from varbinarytable ... x y ---------- 0x00 0x00 0x01 0x01 0xff 0xff 0x0f 0x0fff
Unlike the values entered into a table in which the columns are defined as binary(1) and binary(2), the values are stored in only the amount of space required. Values are truncated if they're greater than the maximum space defined when the table is created.
Use text and image datatypes to store large amounts of CHARacter or binary data. You can store more than two billion data bytes in either a text or image datatype. It's wasteful to pre-allocate space for text or image datatypes to any significant extent, so only a portion of the space is pre-allocated. The remaining space is dynamically allocated.
NOTE: image datatypes are sometimes used for embedded OLE objects that are part of a row.
text Use a text datatype for storing large amounts of text. The CHARacters stored in a text field are typically CHARacters that can be output directly to a display device, such as a monitor, window, or printer. You can store from one to 2,147,483,647 bytes of data in a text datatype.
NOTE: You can store an entire r'esume' in a single column value of a table row.
Your data is stored in fixed-length strings of CHARacters in an initially allocated 2K (2,048 bytes) unit. Additional 2K units are dynamically added and are linked together. The 2K data pages are logically, but not necessarily physically, contiguous. If you use an INSERT statement to insert data into a column defined as text, you must enclose the data within single quotation marks.
TIP: If you define a column using the text datatype and permit NULLs, using an insert statement to place a NULL value in the column doesn't allocate even a single 2K page, which saves space. However, any UPDATE statement will allocate at least one 2K page for the text column regardless of any value that may or may not be supplied for that column.
image You can use the image datatype to store large bit patterns from 1 to 2,147,483,647 bytes in length. For example, you can store employee photos, pictures for a catalog, or drawings in a single column value of a table row. Typically, the data stored in an image column isn't directly entered with an INSERT statement. Your data is stored in fixed-length byte strings in an initially allocated 2K (2,048 bytes) units. Additional 2K units are dynamically added and are linked together like the pages for a text column. The 2K data pages are logically, but not necessarily physically, contiguous.
Using text and image Datatypes Values that are stored as either text or image datatypes are displayed just as other columns are when you use a select statement. The number of bytes displayed is limited by the global value @@Textsize, which has a default value of 4K. You can specify the NULL CHARacteristic for text or image columns. A NULL for a text or image column of a table doesn't allocate any 2K pages of storage, unless an UPDATE is performed on a row containing the NULL value. In Listing 6.10, two table columns are defined using image and text. Values are inserted into each column of a single row using an INSERT statement. The row is then retrieved from the table with a select statement.
create table imagetext_table (image1 image,text1 text) INSERT into imagetext_table values (`123456789aczx+=\','12345678aczx+=') select * from imagetext_table image1 text1 ------------------------------------------------------- 0x31323334353637383961637a782b3d5c 12345678aczx+=
(1 row(s) affected)
Data in a column defined as an image datatype isn't automatically translated from its ASCII representation when it's displayed with a select statement. Data stored in a column defined as the text datatype is automatically translated to ASCII CHARacters when the data is output with a select statement. An image column isn't meant to be direct output. It can be passed on to another program, perhaps running on a client system that processes the data before it's displayed.
Restrictions on text and image Columns You'll encounter several restrictions on the use of data stored in text and image datatypes. You can define only table columns using the text or image datatypes. You can't define other storage structures, such as local variables or parameters, as text or image datatypes. The amount of data that can be stored in a text or image table column makes each datatype unsuitable for use or manipulation in many Transact-SQL statements. This is simply because the amount of data that would have to be manipulated is too great. You can't specify a table column in an ORDER BY, GROUP BY, or compute clause that is a text or image datatype. SQL Server won't try to sort or group a table's rows using a column that can contain more than four billion bytes of data because too much data would have to be moved around and too large a space would have to be allocated in which to order the rows.
Here are some things to keep in mind when you create your query:
The MONEY datatype stores monetary values. Data values stored in the MONEY datatype are stored as an integer portion and a decimal-fraction portion in two four-byte integers. The range of values that you can store in the MONEY datatype is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The accuracy of a value stored in the MONEY datatype is to the ten-thousandth of a monetary unit. Some front-end tools display values stored in the MONEY datatype rounded to the nearest cent.
The smallMONEY datatype stores a range of monetary values that is more limited than the MONEY datatype. The values you can store in the smallMONEY datatype ranges from -214,748.3648 to 214,748.3647. Data values stored in the smallMONEY datatype are stored as an integer portion and a decimal-fraction portion in four bytes. Like values stored in a table column defined using the MONEY datatype, some front-end tools display values stored in the smallMONEY datatype rounded to the nearest cent.
TIP: You can store your monetary values in half the storage space if you choose the datatype smallMONEY instead of the MONEY datatype.
When you add values to a table column defined as MONEY or smallMONEY, you must precede the most significant digit with a dollar sign ($) or a sign of the defined monetary unit.
In Listing 6.11, a table is created with two columns that are defined using the MONEY and smallMONEY datatypes. In the first INSERT statements, values are incorrectly added because they aren't preceded with a dollar sign. A select statement shows that the values displayed are identical to those that were stored.
create table monetary_table (MONEY1 MONEY,MONEY2 smallMONEY) INSERT into monetary_table values (16051.3455,16051.3455) select * from monetary_table MONEY1 MONEY2 ---------------------------------------------------- 16,051.35 16,051.35 (1 row(s) affected)
In a continuation of the same example (see Listing 6.12), a three-digit monetary value is added to both table columns, followed by a value that is outside the storage bounds for the datatype on the computer architecture.
INSERT into monetary_table values ($123,$123) INSERT into monetary_table values (922337203685477,214748.3647) Msg 168, Level 15, State 1
The integer value 922337203685477 is out of the range of machine representation, which is four bytes.
A large monetary value, which is defined as a MONEY datatype, is added to the first column. It's incorrectly entered because it isn't preceded by a dollar sign ($). The select statements show that the number is stored incorrectly. If you enter a value into a table column defined as MONEY or smallMONEY, it's stored as a floating-point datatype, which makes it subject to the rounding error (see Listing 6.12).
INSERT into monetary_table values (922337203685476.,0) MONEY1 MONEY2 ----------------------------------- 16,051.35 16,051.35 123.00 123.00 922,337,203,685,475.98 0.00
In Listing 6.13, the same large number that was previously entered without a dollar sign has been correctly entered with the dollar sign. A subsequent select statement shows that the large monetary value was correctly stored.
INSERT into monetary_table values ($922337203685476.,0) select * from monetary_table MONEY1 MONEY2 ----------------------------------- 16,051.35 16,051.35 123.00 123.00 922,337,203,685,475.98 0.00 922,337,203,685,476.00 0.00
Added to the table are values that contain four digits to the right of the decimal place. When the values are subsequently displayed with a select statement, the values are displayed to two decimal places, to the nearest cent (see Listing 6.14).
INSERT into monetary_table values ($922337203685477.5807,$214748.3647) select * from monetary_table MONEY1 MONEY2 ----------------------------------- 16,051.35 16,051.35 123.00 123.00 922,337,203,685,475.98 0.00 922,337,203,685,476.00 0.00 922,337,203,685,477.58 214,748.36
The sysname datatype is a user-defined datatype, defined as varCHAR(30), and doesn't allow NULLs. sysname is used for defining columns in system tables. You shouldn't use sysname to define the datatype of columns in your tables. You can use varCHAR(30), or you can define your own user-defined datatypes. See the section titled "Creating User-Defined Datatypes" later in this chapter.
Now that you've learned about the additional datatypes that can be defined for Transact-SQL storage structures, such as columns, parameters, and local variables, you should understand a second CHARacteristic that you can define. In addition to specifying the datatype of a table column, you can specify an additional CHARacteristic for each datatype: NULL or not NULL.
The NULL CHARacteristic for a table column lets you omit the entry of a column value in the column. If you define the CHARacteristic for a column as not NULL, SQL Server won't let you omit a value for the column when you insert a row. The NULL CHARacteristic provides a type of validation.
The default CHARacteristic for a column is not NULL, which doesn't allow an undefined column value. A NULL that is defined for a column is stored differently than a space, a zero, or a NULL ASCII CHARacter, which is all zeroes. The interpretation of a NULL entry is undefined or unavailable because no explicit or implicit value is assigned to the column when a row is inserted.
If you reference a row that contains a NULL, the entry (NULL) is displayed in place of a column value to indicate that there's no entry in the row for that column.
There are two ways to designate that a column or storage structure contains a NULL:
In the following example (see Listing 6.15), a table is created that permits a NULL entry for numeric integer datatypes and CHARacter datatypes. A NULL is explicitly inserted into both columns of a single row in the table. A select statement displays (NULL) for both column values of the row.
create table NULLtable (x int NULL, y CHAR(10) NULL) INSERT into NULLtable values (NULL,NULL) select * from NULLtable x y 1 --------------------- (NULL) (NULL) (1 row(s) affected)
NOTE: You can specify the keyword NULL in lower- or uppercase when you specify a NULL entry for the column of a row. The default display of a column that contains a NULL entry is "(NULL)."
To continue the example (see Listing 6.16), NULL is entered in the second column (y) because only the x column precedes the values clause. A NULL value is added to the second column y implicitly because no value is specified in the list port, signified by the values within parentheses separated by a comma, of the values clause.
INSERT into NULLtable (x) values (5) select * from NULLtable x y -------------------------- 5 (NULL) (2 row(s) affected)
You can change the behavior of SQL Server to automatically permit NULLs on table columns, or user-defined datatypes, if no reference to the NULL CHARacteristic is specified when a column, or user-defined datatype, is defined. You can use a set command to change the NULL CHARacteristic for columns or user-defined datatypes defined during a client session. You can also change the NULL CHARacteristic for an entire database using the system procedure sp_dboption.
Use the following set command to cause NULLs to be permitted automatically in table columns or user-defined datatypes:
SET ANSI_NULL_DFLT_ON
Use the following sp_dboption command to cause NULLs to be permitted automatically in table columns or user-defined datatypes:
sp_dboption database-name, `ANSI NULL default', true
ANSI NULLability permits SQL Server not only to conform to a standard form of SQL, but to be tailored to match the SQL dialect of other SQL used with other server databases. You can more easily use SQL Server if you can modify the syntax and behavior of Transact-SQL to match a dialect of SQL that you've used previously. For example, if you change the default NULLability of SQL Server by defining the sp_dboption option as true, it automatically permits NULLs in column definitions, like in Gupta's SQLBase database.
NOTE: If you've changed the NULL CHARacteristic during a session, or for a database, you can set it back to the default using one of the following commands:SET ANSI_NULL_DFLT_OFF
sp_dboption database-name, `ANSI NULL default', false
When you compare a NULL value to any non-NULL value of a column or other data-storage structure, the result is never logically true. If you compare a NULL value to another NULL value, the result is also never a logical true. NULL values don't match each other because unknown or undefined values aren't assumed to be identical.
However, rows that contain multiple NULL values in a column referenced in an ORDER BY, GROUP BY, or DISTINCT clause of a select statement are treated as identical values. All three clauses group together rows with identical values. ORDER BY is used to sort rows, and, in the case of NULLs, all entries in the same column are sorted together. Columns containing NULLs appear at the beginning of a sequence of rows that are sorted in ascending order.
GROUP BY forms groups using identical values, and all NULLs of a column are placed in a single group. The distinct keyword used in a select clause removes all duplicates from one or more column values and removes multiple NULL values as well. Columns that contain NULLs are considered to be equal when you define an index that uses a NULL column.
See "Using an ORDER BY Clause," "Using a GROUP BY Clause," and "Using DISTINCT to Retrieve Unique Column Values," Chapter 7.
If you perform computations with columns or other data structures that contain NULLs, the computations evaluate to NULL. In the following example, the evaluation of the expression x=x+1 evaluates to NULL because the x column contains only a single row with a NULL defined for the x column:
select * from NULLtable where x=x+1 x y --------------------- (0 row(s) affected)
The following example returns an error because a column defined as not NULL is compared with a NULL expression:
select * from employees where badge=NULL Msg 221, Level 16, State 1
A column of the datatype integer doesn't allow NULLs. It may not be compared with NULL.
ISNULL() is a system function that returns a string of CHARacters or numbers in place of (NULL) when a NULL is encountered in a data-storage structure, such as a table column. The syntax of the function is as follows:
ISNULL(expression,value)
The expression is usually a column name that contains a NULL value. The value specifies a string or number to be displayed when a NULL is found. In the following example, ISNULL() is used to return a number when a NULL is encountered in the value of a row, or return the CHARacter string `NO ENTRY' when a NULL is encountered.
select x,ISNULL(x,531),y, ISNULL(y, `NO ENTRY') from NULLtable x y ---------------------------------------- (NULL) 531 (NULL) NO ENTRY (1 row(s) affected)
NOTE: You may decide that it's easier to avoid using NULL rather than deal with the intricacies of working with NULLs. You can decide to use a specific pattern that is entered for a datatype that has the meaning of no entry or undefined, instead of NULL.
In addition to defining the datatype of a column to allow or disallow NULLs, you can define a column with the property of identity. When you define a column with the property identity, you can specify both an initial value (seed), which is automatically added in the column for the first row, and a value (increment), which is added to that last value entered for the column. When you add rows to the table, you omit entering a value for the column defined with the identity property. The value for the identity column is automatically entered by adding the increment value to the column value of the last row.
In the following example, the second column is defined with the property identity (see Listing 6.17). After two rows are added to the table, a subsequent retrieval of the table rows shows that the identity column values were generated by the identity mechanism.
create table identity_table (name CHAR(15),row_number integer identity(1,1)) INSERT into identity_table (name) values (`Bob Smith') INSERT into identity_table (name) values (`Mary Jones') select * from identity_table name row_number -------------------------- Bob Smith 1 Mary Jones 2 (2 row(s) affected)
You can assign the identity property only to a column that is defined with the datatypes int, smallint, tinyint, decimal(p,0), and numeric(p,0)--but not if the column permits NULLs. If you omit a seed and increment value when specifying the identity property on a table column, they default to one. Also, only a single column of a table can be defined with the property identity. The identity property doesn't guarantee that rows will be unique. You must establish a unique index on the identity column to guarantee unique table rows.
TIP: You can use the keyword identitycol, as well as the name of the column, to reference the column of a table that is defined with the property identity.
Constraints are defined to provide data integrity on a table and in individual columns. The create table statement enables you to create primary and foreign keys, define unique columns and rows, and specify check and default constraints.
You use PRIMARY KEY constraints for column integrity, as well as referential integrity. The definition of a PRIMARY KEY constraint for a table has several effects. The PRIMARY KEY constraint ensures that all rows of a table are unique by ensuring that one or more columns don't permit duplicate values to be entered.
A PRIMARY KEY constraint also disallows NULL for the column(s) in which the constraint is defined. A PRIMARY KEY constraint creates a unique index on the column(s) defined in the constraint. A secondary effect is that the index can be used for faster retrieval of rows of the table than if no index were defined on the table.
NOTE: The definition of a PRIMARY KEY constraint on a single table doesn't by itself permit referential integrity. You must also define corresponding foreign keys in the tables whose rows will be combined with the table in which you define the PRIMARY KEY constraint.
The syntax of the PRIMARY KEY constraint is as follows:
CONSTRAINT constraint_name PRIMARY KEY CLUSTERED (column_name_1 column_name_n)
In the following example, the employees table has a PRIMARY KEY constraint defined on the badge column.
Create table employees4 (name CHAR(20),department varCHAR(20),badge integer, constraint badge_pays2 foreign key (badge) references employees4 (badge))
A FOREIGN KEY constraint is used along with a previously defined PRIMARY KEY constraint on an associated table. A FOREIGN KEY constraint associates one or more columns of a table with an identical set of columns that have been defined as a PRIMARY KEY constraint in another table. When the column values are UPDATEd in the table in which the PRIMARY KEY constraint is defined, the columns defined in another table as a FOREIGN KEY constraint are automatically UPDATEd.
The PRIMARY KEY and FOREIGN KEY constraints ensure that corresponding rows of associated tables continue to match so that they can be used in subsequent relational joins. The automatic updating of the corresponding columns of different tables after they're defined as PRIMARY KEY and FOREIGN KEY constraints is called declarative referential integrity, a feature added to SQL Server in version 6.0.
The syntax of the FOREIGN KEY constraint clause is as follows:
CONSTRAINT constraint_name FOREIGN KEY (column_name_1 column_name_n) REFERENCES _table_name (column_name_1 column_name_n)
The table named after the keyword REFERENCES is the table in which the corresponding column(s) are defined as a PRIMARY KEY constraint. In the following example, the badge column in the pays2 table is defined as a FOREIGN KEY constraint that associates, or references, with the badge column in the employees4 table.
Create table pays (hours_worked integer, pay_rate integer,badge integer, constraint badge_pays2 foreign key (badge) references employees4 (badge))
NOTE: The corresponding columns that are defined as PRIMARY KEY and FOREIGN KEY constraints don't have to have the same names. However, it's simpler to understand the columns in different tables that are defined as associated PRIMARY KEY and FOREIGN KEY constraints if their names are identical.
You apply the unique constraint to any table column to prevent duplicate values from being entered into the column. A restriction is that the column can't be defined as the primary key or part of the primary key of the table. The unique constraint is enforced through the automatic creation of a unique index for the table that is based on the column. In the following example, a unique constraint is applied to the badge column of the employees2 table:
Create table employees2 (name CHAR(20), department varCHAR(20),badge integer, constraint badge_nodupes unique nonclustered (badge))
A check constraint limits the values that can be entered into one or more columns of a database table. You can use a check constraint, for example, to limit the range of values that can be stored in a column defined as numeric datatypes.
The process of associating a check with a table column is called binding. You can define and associate multiple checks with a single column. A check can be defined for a column, even though a rule is already defined on the column. In the following example, a check constraint is defined on the column department to restrict subsequent entries to valid departments:
Create table employees5 (name CHAR(20), department varCHAR(20),badge integer check valid_department (department in [ccc] (`Sales','Field Service','Software','Logistics')))
NOTE: Check and other constraints can seem as though they duplicate the function of other mechanisms in SQL Server. If you've had this perception, it's accurate. In version 6.0 of SQL Server, Microsoft changed its version of SQL, Transact-SQL, to conform to a standardized form of SQL, ANSI SQL. Although Transact-SQL already had existing ways of performing some operations, such as rules and defaults, the addition of ANSI SQL syntax to Transact-SQL added alternate ways of performing the same operations.You can often choose to implement a feature, such as a restriction on the values that can be entered into a column, in the way that you feel is the easiest to set up. For example, you can choose to restrict the values entered into the columns of a table using a check constraint instead of defining a rule and binding it to the column.
However, you should investigate each mechanism, because one may be more appropriate for your use. Although a check constraint is quicker and simpler to set up to restrict the column values than a rule, a rule is more flexible in one way.; after you define a rule, it can be bound to a column in multiple tables. A rule may prove more useful to you if you're going to use it to restrict column values on columns that are in multiple tables.
You use a default constraint to have a value automatically added to a table column when no value is entered during an insert. You can define a default constraint to the most frequently occurring value within a table column and thus relieve a user of the need to enter the defined default constraint value when a new row is added to the table. The syntax of the default constraint clause is as follows:
DEFAULT default_name value FOR column_name
In the following example, a default value is specified for the department column for the employees6 table:
Create table employees6 (name CHAR(20),department varCHAR(20),badge integer, DEFAULT department_default `Sales' for department)
You can also use a default, which you must define and then bind to a table column, to have a value automatically added to a table column. Although Microsoft recommends that you use a default constraint to add a value automatically to a table column, after a default is defined, it can be bound to columns in different tables, instead of in a single table.
See "Managing and Using Rules, Constraints, and Defaults," Chapter 13.
Microsoft recommends that you use a default constraint instead of a default when you're defining a default value for a column in a single table because a default constraint is stored with the table, instead of as a separate database object. If you drop a table, the constraint is automatically dropped. When a table is deleted, a default bound to a column of the table isn't deleted.
NOTE: With a default constraint, you can use a set of functions called niladic functions. A niladic function inserts a value that is generated by SQL Server.Niladic functions allow a system-supplied value, such as information about the current user or a timestamp, to be inserted when no value is specified. The ANSI niladic functions that can be used with a default constraint are current_user(), session_user(), system_user(), user(), and current_timestamp(). current_user(), session_user(), and user() return the user name stored in the database of the user issuing an insert or UPDATE. The system_user() function returns the SQL Server logon ID of the user, and the current_timestamp() function returns the date in the same form as that which is returned by the getdate() function.
You can define your own datatype, which can then be used as a datatype for a storage structure, such as a table column. You always define a user-defined datatype as one of the existing system datatypes. A user-defined datatype enables you to define a datatype that can contain a length specification, if necessary, and a NULL CHARacteristic.
You can use a descriptive name, which describes the type of data that it contains, for the user-defined datatype.
Creating User-Defined Datatypes with sp_addtype You can define a user-defined datatype with the system procedure sp_addtype, which uses the following syntax:
sp_addtype user_defined_datatype_name, system_datatype, NULL | NULL
After you define a user-defined datatype, you can use it to specify the datatype of a storage structure such as a table column. You can use the system procedure sp_help to display a user-defined datatype. You can create and then bind defaults and rules to user-defined datatypes. You bind rules and defaults to user-defined datatypes with the same procedures used for system datatypes sp_bindefault and sp_bindrule.
An error message is generated if you specify not NULL for a column and don't create a default, or specify a value at insertion. You can also change the NULL or not NULL CHARacteristic for a user-defined datatype when you define a column in a table.
In Listing 6.18, a user-defined datatype is created by using sp_addtype. The CHARacteristics are displayed with sp_help. A new table is created in which the column is defined using the user-defined datatype.
sp_addtype names, `CHAR(15)', NULL Type added. sp_help names Type_name Storage_type Length NULLs Default_name Rule_name ------------- --------------- ------ ----- --------------- --------- names CHAR 15 1 (NULL) (NULL) create table usertype_table (CHARstring names)
In Lisiting 6.19, a value is inserted into the table and subsequently retrieved. The insertion of the string results in a truncation of the inserted string to 15 CHARacters. The example also displays the table in which a column is defined by using a user-defined datatype.
INSERT into usertype_table values (`this is a string') select * from usertype_table CHARstring --------------- this is a strin (1 row(s) affected) sp_help usertype_table Name Owner Type ------------------------------ ------------------------------ ------------ usertype_table dbo user table Data_located_on_segment When_created ------------------------------ --------------------------- default May 19 1994 12:46PM Column_name Type Length NULLs Default_name Rule_name --------------- --------------- ------ ----- --------------- ------------- CHARstring names 15 1 (NULL) (NULL) Object does not have any indexes. No defined keys for this object.
Creating User-Defined Datatypes with the Enterprise Manager To define a user-defined datatype using the Enterprise Manager, follow these steps:
FIG. 6.3
You can list the user-defined datatypes from the Manage User-Defined Datatypes dialog
box.
Dropping User-Defined Datatypes You can use the system procedure sp_droptype to remove a user-defined datatype. The procedure uses the following syntax:
sp_droptype typename
You can't drop a user-defined datatype if one or more tables have a column, which is defined using it. You can drop the user-defined datatype only if it isn't in use by any tables. In the following example (see Listing 6.20), a user-defined datatype can't be dropped until you first drop the sole table in which a column is defined using the user-defined datatype:
sp_droptype names Type is being used. You cannot drop it. object type owner column datatype ----------------------- ---- ----------------- -------------------- --------- usertype_table U dbo CHARstring names (1 row(s) affected) drop table usertype_table This command didn't return any data or rows: sp_droptype names Type has been dropped.
You can also drop a user-defined datatype through the Enterprise Manager. To do so, follow these steps:
You can't name objects with names of commands or other reserved words because datatypes are objects in the database. Datatypes are stored in the SysTypes system table along with their coded values. You can use the following select statement (see Listing 6.21) to display the datatypes and their code value in the type column:
select name,type from systypes order by type name type ------------------------- ---- image 34 text 35 timestamp 37 varbinary 37 intn 38 sysname 39 varCHAR 39 binary 45 CHAR 47 badge_type 47 tinyint 48 bit 50 smallint 52 int 56 badge_type2 56 smalldatetime 58 real 59 MONEY 60 datetime 61 float 62 floatn 109 MONEYn 110 datetimn 111 smallMONEY 122
(24 row(s) affected)
You can use sp_helpsql to display information about the CHARacteristics of system datatypes, as shown in Listing 6.22:
sp_helpsql `datatype' helptext ---------------------------------------------------------------------- Datatype Datatype Definition Binary(n) Fixed-length binary data. Maximum length=255 bytes. Bit A column that holds either 0 or 1. CHAR(n) CHARacter data. Maximum length=255 bytes. Datetime Dates and times with accuracy to milliseconds. Float Floating-point numbers. Image Large amounts of binary data (up to 2,147,483,647 CHARacters.) Int Integers between 2,147,483,647 and -2,147,483,648. MONEY Dollar and cent values. Real Floating point numbers with 7-digit precision. Smalldatetime Dates and times with accuracy to the minute. Smallint Integers between 32,767 and -32,768. SmallMONEY Monetary values between 214,748.3647 and -214,748.3648. Text Large amounts of CHARacter data (up to 2,147,483,647 CHARacters). Timestamp Automatically UPDATEd when you INSERT or UPDATE a row that has a timestamp column, or use BROWSE mode in a DB-LIBRARY application. Tinyint Whole integers between 0 and 255. Varbinary(n) Variable-length binary data. Max length=255 bytes. VarCHAR(n) Variable-length CHARacter data. Max length=255 bytes.
In addition to creating a table with the create table statement, you can create a table through the Enterprise Manager. To do so, follow these steps:
See "SQL Enterprise Manager To Create Tables," Chapter 6.
Figure 6.4 shows the Manage Tables window after information for three columns has been entered.
FIG. 6.4
The NULL property is automatically enabled for each column that you enter,
though you can deselect it.
FIG. 6.5
To complete the operation, enter the name you want to use to reference the new table.
You also can define properties on columns such as an identity, primary key, or constraint by clicking the Advanced Features tool on the toolbar of the Manage Tables window while you're defining the table. For example, in Figure 6.6, a column has been defined as an identity column in the Identity Column combo box of the Manage Tables window after the Advanced Features tool is clicked.
FIG. 6.6
You can specify an initial value (seed) and increment for your identity column.
After you create a table, you can add data using an INSERT statement. Several forms of the INSERT statement can be used to add new rows to a table. Each INSERT statement can add only a single row to a table. The complete form of the INSERT statement uses the following syntax:
INSERT INTO table_name (column_name_1,...,column_name_n) VALUES (`string_1',...'lstring_')
List the table columns to receive values separately, enclosed by parentheses and separated by commas, after the INSERT clause. Enter the values that will be added to the table columns in parentheses in the same order as the column names in the previous line. The list of column values is preceded by the VALUES keyword.
You don't have to list the columns and their values in the same order as they are defined in the table. You must, however, enter the values in the VALUES clause in the correct order as the column names listed in the previous line. If you do not, data values may be inserted into the wrong columns.
In the following example, the columns are listed in the order in which they were defined in the table, but they don't have to follow the same order. The values are entered in the VALUES clause in an order that corresponds to the order of the columns named in the previous line.
INSERT into employees (name, department, badge) values (`Bob Smith', `SALES', 1834)
If you omit one or more column names, a NULL or default value is entered into the table row. In the following example, the name and badge columns are listed in the INSERT statement. The values clause omits a value for the department.
INSERT into employees (name, badge) values (`Bob Mariah', 1999)
Table 6.4 shows the resulting entry for a table if an explicit value isn't listed in the values list of an INSERT statement.
Column CHARacteristic(s) | User Entry | Resulting Entry |
NULL defined | No default value defined | NULL |
not NULL defined | No default value defined | Error, no row inserted |
NULL defined | Default value defined | Default value |
not NULL defined | Default value entered | Default value |
NOTE: Use (NULL) within the values list to insert a NULL into a column that has been defined to permit the NULL.
In this chapter, you've learned to create database tables. The process of creating a table involves the selection of the appropriate datatypes for your table columns. In addition, you've learned to add the additional CHARacteristics that can be defined for a table column including the NULL CHARacteristic and the various constraints. You learned to create, drop, and list the CHARacteristics of a table using Transact-SQL syntax and the Enterprise Manager.
For further discussion about topics mentioned in this chapter, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.