Introductory Transact-SQL, Page 2
Creating a Database
You might already have a database in which you can create some temporary tables for the purpose of working through the examples in this book. If you don't, creating one is easy enough. In Transact-SQL, you create databases using the CREATE DATABASE command. The complete syntax can be quite complex, but here's the simplest form:
CREATE DATABASE GG_TS
Run this command in Query Analyzer to create a scratch database for working through the examples in this book. Behind the scenes, SQL Server creates two operating system files to house the new database: GG_TS.MDF and GG_TS_Log.LDF. Data resides in the first file; transaction log information lives in the second. A database's transaction log is the area where the server first carries out changes made to the data. Once those changes succeed, they're applied atomically—in one piece—to the actual data. It's advantageous for both recoverability and performance to separate user data from transaction log data, so SQL Server defaults to working this way. If you don't specifically indicate a transaction log location (as in the example above), SQL Server selects one for you (the default location is the data directory that was selected during installation).
Notice that we didn't specify a size for the database or for either of the files. Our new database is set up so that it automatically expands as data is inserted into it. Again, this is SQL Server's default mode of operation. This one feature alone—database files that automatically expand as needed—greatly reduces the database administrator's (DBA's) workload by alleviating the need to monitor databases constantly to ensure that they don't run out of space. A full transaction log prevents additional changes to the database, and a full data segment prevents additional data from being inserted.
Creating TablesOnce the database is created, you're ready to begin adding objects to it. Let's begin by creating some tables using SQL's CREATE TABLE statement. To ensure that those tables are created in the new database, be sure to change the current database focus to GG_TS before issuing any of these commands. You can do this two ways: You can execute a USE command—USE GG_TS—in the query edit window prior to executing any other commands, or (assuming you're using Query Analyzer) you can select the new database from the DB: combo-box on the edit window's toolbar (select
Execute the following command to create the customers table:
USE GG_TS Change the current database context to GG_TSGOCREATE TABLE customers(CustomerNumber int NOT NULL,LastName char(30) NOT NULL,FirstName char(30) NOT NULL,StreetAddress char(30) NOT NULL,City char(20) NOT NULL,State char(2) NOT NULL,Zip char(10) NOT NULL)
Once the customers table is built, create the orders table using similar syntax:
CREATE TABLE orders(OrderNumber int NOT NULL,OrderDate datetime NOT NULL,CustomerNumber int NOT NULL,ItemNumber int NOT NULL,Amount numeric(9,2) NOT NULL)
Most SQL concepts can be demonstrated using three or fewer tables, so we'll create a third table. Create the items table using this command:
CREATE TABLE items(ItemNumber int NOT NULL,Description char(30) NOT NULL,Price numeric(9,2) NOT NULL)
These commands are fairly self-explanatory. The only element that might look a little strange if you're new to SQL Server is the NOT NULL specification. The SQL NULL keyword is a special syntax token that's used to represent unknown or nonexistent values. It is not the same as zero for integers or blanks for character string columns. NULL indicates that a value is not known or completely missing from the column—that it's not there at all. The difference between NULL and zero is the difference between having a zero account balance and not having an account at all. (See Chapter 3, "Missing Values," for more information on NULLs.) The NULL/NOT NULL specification is used to control whether a column can store SQL's NULL token. This is formally referred to as column nullability. It dictates whether the column can be truly empty. So, you could read NULL/NOT NULL as NOT REQUIRED/ REQUIRED, respectively. If a field can't contain NULL, it can't be truly empty and is therefore required to have some other value.
Note that you don't have to specify column nullability when you create a table—SQL Server will supply a default setting if it's omitted. The rules governing default column nullability go like this:
- If you explicitly specify either NULL or NOT NULL, it will be used (if valid—see below).
- If a column is based on a user-defined data type, that data type's nullability specification is used.
- If a column has only one nullability option, that option is used. Timestamp columns always require values, and bit columns can require them as well, depending on the server compatibility setting (specified via the sp_dbcmptlevel system stored procedure).
- If the session setting ANSI_NULL_DFLT_ON is set to true (it defaults to the setting specified in the database), column nullability defaults to true. ANSI SQL specifies that columns are nullable by default. Connecting to SQL Server via ODBC or OLEDB (which is the normal way applications connect) sets ANSI_NULL_DFLT_ON to true by default, though this can be changed in ODBC data sources or by the calling application.
- If the database setting ANSI null default is set to true (it defaults to false), column nullability is set to true.
- If none of these conditions specifies an ANSI NULL setting, column nullability defaults to false so that columns don't allow NULL values.