Up until now, the examples in the T-SQL 101 series have accessed tables that already existed or were created by running T-SQL code included with the lesson. Now I’ll show you two techniques you can use to create your own tables. The first technique uses the CREATE TABLE command. The second technique uses a SELECT INTO command.
With the CREATE TABLE command, you specify the new table name and define the table’s column structure. With the SELECT INTO command, you use a SELECT statement to query a database and store the results in a new table, as specified by the INTO clause. The column definitions of the new table exactly match the column definitions in the result set.
At this point, you might be wondering, “Isn’t it a little archaic to manually create tables with T-SQL statements when there are graphical tools you can use to create tables?” My response is that no matter what tool you use to design tables, the tables don’t actually get created in the database without T-SQL statements being generated, submitted, and executed. Much like an artisan using a hand planer rather than an electric sander to smooth wooden surfaces, I find that creating tables “the old-fashioned way” leads to a better understanding of the tables and the columns therein. Manually typing the T-SQL code forces you to slow down and seriously consider the names and data types you assign to each column, and how they relate to other tables in the database. A T-SQL table creation script also documents the table’s structure and lets you easily recreate the table.
The Prerequisites
To run the examples in this lesson, make sure your database contains the Employee table created in Lesson 3 and the Movie and Genre tables created in Lesson 5. If you haven’t created these tables, you’ll find the code in the 99448.zip file, which you can download from the "Download the Code" icon at the top of the page.
Using the CREATE TABLE Command
CREATE TABLE is one of those syntax-heavy commands that can be a bit intimidating at first. Instead of rehashing the excellent documentation for this command in SQL Server 2005 Books Online (BOL), I’ll just cover the basics to get you started creating your own tables. After you become familiar with the basics, I highly encourage you to delve into this command’s complexities.
The basic syntax of the CREATE TABLE command is
CREATE TABLE DatabaseName. SchemaName.TableName ( ColumnName1 datatype, ColumnName2 datatype, ColumnName3 datatype )
DatabaseName.SchemaName.TableName is referred to as the fully qualified table name, where
- DatabaseName is the name of the database in which the new table will reside. This argument is optional. If left out, the table will be created in the current database.
- SchemaName is the schema to which the table will belong. A schema is a collection of uniquely named objects that aren’t automatically tied to a particular user. This argument is optional. If left out, the table will be owned by the dbo schema, which is the default schema of the database owner (DBO). Note that in SQL Server 2000, you need to specify the table’s owner rather than the schema name as the second element in the fully qualified table name. A table’s owner has full rights to alter the table’s structure and contents, including the right to delete it. In SQL Server 2005, Microsoft opted to separate users and schemas, so the second element in the fully qualified table name became the schema name. The reason for this change is beyond the scope of this introductory lesson.
- TableName is the name of the table you want to create. Table names can be a maximum length of 128 characters and must follow the rules of identifiers.
Inside the parentheses is where you list your column definitions, each of which must include a column name and column data type. Column names are limited to 128 characters, must be unique within the table, and must follow the identifier rules. Column data types must be valid SQL Server data types (e.g., integer, varchar, char). When defining columns, you can also specify constraints, whether null values are allowed, and other optional arguments (more on this shortly).
Now that all the technical information is out of the way, it’s time to start having fun creating your own tables. The following examples assume that the current database is MyDB and you’re the DBO, so the optional DatabaseName and SchemaName arguments aren’t included. When you run the script, SQL Server will create the table, which will be owned by the dbo schema, in MyDB. The MyDB database needs to include the Employee table from Lesson 3 and the Movie and Genre tables from Lesson 5.
Some of the examples provided in Lesson 4 used the MovieReview table, which contained four columns: EmployeeID (employee IDs), Genre (textual descriptions for movies’ genres), MovieName (textual descriptions for movies’ names), and Stars (movies’ ratings, where 1 star is the worst rating and 5 stars is the best rating). Let’s create a new version of the MovieReview table that contains IDs rather than textual descriptions for the movie names and that doesn’t include a Genre column. (The genre information will be obtained later through a join operation.)
Listing 1 shows the code to create the new and improved MovieReview table. This code begins by deleting the original table if it exists. To do so, the code executes the OBJECT_ID function, passing in the name of the target table (MovieReview) as a parameter. If the table exists in the current database, the function returns the table’s object ID. If the table doesn’t exist, the function returns a null value. If the value isn’t null, the DROP TABLE command deletes the MovieReview table. If the value is null, the DROP TABLE command is skipped. (Trying to use DROP TABLE to delete a non-existent table raises an error.)
Next, the code uses the CREATE TABLE command to create the new MovieReview table. The table has four columns: MovieReviewID, EmployeeID, MovieID, and Stars.
The MovieReviewID column is an identity column, which is specified by the IDENTITY argument. As I explained in Lesson 2, the identity column is special in that the data for this column is automatically added and its values incremented with each record being inserted. The (1,1) parameter indicates that the values in the IDENTITY column should start at number 1 and increment by 1 each time a record is inserted into the table. The PRIMARY KEY argument indicates that this column will be used to uniquely identify records in this table.
The EmployeeID and MovieID columns contain the employee and movie IDs, respectively. The Stars column contains a number representing how many stars the movie received. In these three columns, notice the use of NOT NULL in the column definitions. NULL is the term applied to a missing or unknown value. By designating a column as being NOT NULL, you’re telling SQL Server that every record in the table must have a known value in that column. So, if you tried to insert a record that had NULL for EmployeeID, SQL Server would reject the record and raise an error. In other words, the table will accept only valid integers. However, this doesn’t mean that the table will accept only correct values. If you want to allow only correct values to be entered in a column, you need to create constraints. Constraints are beyond the scope of this article, but if you’d like more information about them see “Enforcing Data Integrity” in SQL Server 2005 BOL at msdn2 .microsoft.com/en-us/library/ms190765.aspx.
NOT NULL, IDENTITY, (1,1), and PRIMARY KEY are only a few of the many optional arguments you can include in column definitions. See the CREATE TABLE documentation in SQL Server 2005 BOL for a complete list.
You can find the code in Listing 1 in the CodeTo- CreateNewMovieReviewTable.sql file, which is part of the 99448.zip file. Run this code in the query window in SQL Server 2005’s SQL Server Management Studio (SSMS) or SQL Server 2000’s Query Analyzer.
After you create the new MovieReview table, you need to populate it. Run the code in the CodeTo PopulateNewMovieReviewTable.sql file, which is also part of the 99448.zip file above, in the query window. Now let’s use the new MovieReview table to get a list of who reviewed which movies, the reviewers’ ratings, and the movies’ genres. Listing 2 shows the code to accomplish this. This code performs three inner joins:
- The MovieReview table is joined with the Employee table to retrieve the first and last names of the employees who reviewed the movies. The first names and last names come from the Employee table’s FirstName and LastName columns, respectively. The first and last names are concatenated together with a space in between. The column containing this information is titled Reviewer.
- The MovieReview table is joined with the Movie table to get the movie names from the Movie Table’s Movie column.
- The MovieReview table is joined with the Genre table to get the genre name from the Genre table’s Genre column.
The Stars column of the new MovieReview table provides the movie ratings. However, instead of displaying each rating as a number, the REPLICATE function displays an asterisk (*) for every star a movie received. (For a detailed explanation of how this works, see Lesson 4.)
Only movies that received more than 3 stars are included in the results, which are ordered first by descending number of stars, then alphabetically by genre, and finally alphabetically by movie name.
You can find the code in Listing 2 in the CodeToList WhoReviewedWhichMovies.sql file, which is part of 99448.zip above. After you execute this code in the query window, you should get results like that in Figure 1.
Using the SELECT INTO Command
The second way to create a table is to use the SELECT INTO command to query a database and store the results in a new table. This technique is fairly straightforward. Plus, it lets you create and populate the target table with one command. However, it lacks the flexibility offered by the CREATE TABLE command. For example, when you use the SELECT INTO command to create a table, the table’s columns will be of the same datatype as the columns in the SELECT statement. When you use the CREATE TABLE statement, you have the option of customizing each column’s datatype.
The basic syntax to follow when you’re using the SELECT INTO command to create a table is
SELECT Column1, Column2, Column3 FROM YourExistingTable INTO NewTable
where YourExistingTable is the name of the existing table (or tables) being queried and NewTable is the name of the table you’re creating. (The new table’s name can’t already exist in the database.) In this case, the SELECT statement selects three columns from the existing table for inclusion in the new table, but you can use any valid SELECT statement to create the table. SQL Server will automatically assign appropriate column definitions based on the expressions in the SELECT statement.
Listing 3 shows this technique in action. This code joins the MovieReview table with the Movie table to get the movie names and the MovieReview table with the Genre table to get the genre names. The Stars column in the MovieReview table provides the movie ratings, which are averaged for each movie. The average ratings are displayed as asterisks rather than numbers. The Count(*) function is used to count how many reviews each movie has; these counts are assigned to a column named Reviews. After grouping the results by movie name, then genre, the code stores the result set in the MovieReviewSummary table.
You can find the code in Listing 3 in the CodeTo Create&PopulateMovieReviewSummaryTable .sql file, which is part of 99448.zip. After you execute this code in the query window, you can run the query
SELECT * FROM MovieReviewSummary ORDER BY Stars, Reviews
to get the result set in Figure 2.
If you were to look at the structure of the new table to see how SQL Server defines the columns, you’d see that the Movie and Genre columns are the same type and length in the new table as they were in the source tables. Because the Stars and Reviews columns were computed within the SELECT statement and not directly pulled from physical tables, their type and length matches the result of the corresponding expressions.
Knowing How to Create Tables Isn’t Knowledge Reserved Just for DBAs
Creating tables doesn’t have to be the exclusive domain of DBAs. Application developers, report writers, business analysts, or anyone else who stores data in a relational database will benefit from knowing how to create their own tables. Being able to write the table creation scripts when requesting new tables goes a long way in convincing the DBA to do your bidding.
