Creating a New Table

1. Click on the plus sign next to the newly created 'booksdb' database to expand the folder's contents.
2. Right click on the table folder and click on 'New Table...'
3. A new table editor that looks like the next screen shot will appear.
4. Let us create the simple 'category' table first. Enter 'categoryid' under 'Column Name'.
5. Press tab or the right arrow key and select 'int' from the list that appears when you press the left mouse button over the downward arrow symbol (you may also enter 'int' using the keyboard.
6. Click on the check mark inside the 'Allow Nulls' box so that the box becomes empty.
7. Right click the black right arrow at the start of the column and select 'Set Primary Key'. This will be the primary key of our table. After you do this, you should see a gold key next to the 'categoryid' column definition.
8. Click on the column under 'Column Name' in the next row and enter 'categorydesc'.
9. Enter 'varchar(100)' in the next field under 'Data Type'
10. Uncheck the 'Allow Nulls' box next to categorydesc.
11. Now, let us define an automatic increment that SQL server keeps track of for the categoryid field so that we do not have to work out and specify one manually. Click on the 'categoryid' column.
12. Click on any item within the 'ColumnProperties' tab underneath the table tab.
13. Use the down arrow key to scroll through the properties until you get to 'Identity Specification'. Expand this item by clicking on the plus sign next to it.
14. Type or select 'Yes' next to '(Is Identity)' and press enter.
15. The Identity Increment and Identity Seed properties should now be automatically populated with '1'. You table definition will now look like the following screenshot:

Category Table Definition

16. Right click on 'Table-dbo.table1' tab and click on the first item in the menu (Save table_1)
17. Enter 'category' under 'Enter a Name for the Table' and press OK
18. This action creates and saves the new table. Right click on the 'Tables' folder and click 'Refresh'. You will now see 'dbo.category' underneath the 'Tables' folder under booksdb.
19. Now, delete this table by right clicking on its name and selecting 'delete' from the menu.
20. Select 'OK' on the 'Delete Object' pop up window.

Now that we have examined the GUI method of table creation, let us look at the equivalent in T-SQL code:

CREATE TABLE [dbo].[category](
[categoryid] [int] IDENTITY(1,1) NOT NULL,
[categorydesc] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED
(
[categoryid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

• To run this code, click on the 'New Query' icon on the left hand side of the top toolbar.
• A Query tab will open up. Copy the above query into the tab.
• Select the entire Query and click the 'Execute' exclamation point symbol on the top toolbar (or Query->Execute from the menu or by just hitting the F5 key). Note that all the contents of the Query window will automatically be executed if no commands are selected.
• Right click on the 'Tables' folder and click 'Refresh'. Once again, you should see the new category table identical to the one you created with the GUI.
• Now, delete the contents of the query edition window and enter 'drop table category'.
• Highlight the above command and hit the F5 key. You will see the 'Command(s) Completed Successfully' confirmation in the Messages tab in the lower half of the window. Note that the SQL command to drop a table does not evoke a confirmation step unlike the GUI-based command. You may have to refresh the tables folder to see your changes.

Included in this tutorial are several T-SQL and SQL scripts that automatically create the books database table and populate them. Download the scripts booksdbddl.sql, book.sql, author.sql, category.sql, customer.sql, custorder.sql, orderdetails.sql, and subject.sql onto an appropriate directory on your computer.

Now, run the booksdbddl.sql script. Click on Open -> File in the file menu on the top bar and browse to the location of booksdbddl.sql. Click on the file and then the open button. The file will open on a query window. Execute the contents of the window by hitting the F5 key or clicking the 'Execute' exclamation point (!) icon.

After the above command, run the rest of the files in the exact order specified below. Each of these files populates a table created in the previous step. You may see many '(1 row(s) affected)' messages flash past after running each script.

author.sql
category.sql
subject.sql
book.sql
customer.sql
custorder.sql
orderdetails.sql

Now, go ahead and familiarize yourself with the tables by opening the definition of each table in the booksdb database. Go through the data in the tables and the table structure.

Books Database Tables

The above diagram displays all the relationships in the books database. Note that every table except orderdetails has a primary key (the first column) and most dependant tables have foreign keys. The author, category, and customer tables are top level tables that do not depend on other tables. The subject table contains a foreign key that refers to a category id. The book table contains foreign keys for both the author id and subject id fields. The custorder table contains the id of the customer who placed the order and the order date. The orderdetails table is organized by the id corresponding to each item within an order. We use four data types - varchar for string/character data, date, integer based types, and money. The price column in the book table and the unitprice column in the orderdetails table alone are of the money datatype; all other number fields contain integers.

Let us start by going over some of the scripts in the booksdbddl.sql files. Note that this file contains mostly Data Definition Language (DDL) commands. DDL deals with table and data format and creation. DML or Data Manipulation Language deals with data retrieval, querying and so on. First, let us look at the statements that created the author table and associated objects.

]
CREATE TABLE [dbo].[author](
[authorid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](100) NOT NULL,
[firstname] [varchar](100) NOT NULL,
CONSTRAINT [PK_author] PRIMARY KEY CLUSTERED
(
[authorid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

The 'create table' statement creates a new table within the current database. The table's column definitions and other constraints should be specified within a pair of brackets after the table name. Note that the square brackets are optional and added to make the statement more readable. The author table has three columns. LASTNAME and FIRSTNAME contain strings (VARCHAR(100)) that may be upto a hundred characters long. The AUTHORID field contains a number that may be upto five digits long with no decimal digits (as defined by int). Further, we define authorid to be an identity column with a seed of 1 and increment 1 through the directive IDENTITY(1,1). Recall that we did this through setting the '(Is Identity)' property to Yes in the GUI. Also, we declare authorid to be a Primary key by associating it with a constraint called PK_author.

The PK_author constraint basically restricts the AUTHORID field so that it contains no duplicates even across clustered instances. Such constraints maintain the integrity of the data and are therefore called integrity constraints. A constraint does not have to be a primary key or a foreign key. We could even define 'check' constraints through a formula based on constants or values in other fields. The following command adds a constraint to the orderdetails table. This constraint restricts discounts: a discount cannot be applied to a single copy order of a book that costs below $25. The NOCHECK directive causes SQL Server to skip checking existing rows against the constraint.

ALTER TABLE [dbo].[orderdetails] WITH NOCHECK
ADD CONSTRAINT [CK_orderdetails] CHECK
(([QUANTITY]>(1) OR [UNITPRICE]>(25.00)) OR [DISCOUNT] IS NULL)
GO

Issue the following command to delete this constraint:

ALTER TABLE [dbo].[orderdetails]
DROP CONSTRAINT [CK_orderdetails]
GO

Let us now use the Management Server GUI to run the same command.

• Click on the plus sign next to the icon for the orderdetails table under the booksdb database in the tree on the left hand side window.
• The expanded view will reveal a folder named 'Constraints'. Right click on this folder and click on 'New Constraint...'
• In the pop-up window, click on the add button. Leave the name in the left hand window as is.
• Click on the ellipsis symbol ('...') next to 'Expression' under General. Copy and paste the following text into the pop up window:

((QUANTITY>1 OR UNITPRICE>25.00) OR DISCOUNT IS NULL)

• Click OK to close the window.
• Now, click the 'close' button.

Constraint Definition

• Right click on the 'orderdetails' table tab and choose 'Save orderdetails'.
• You have now added a constraint through the GUI.
• Do not forget to delete the constraint - right click on the constraints folder, select 'New Constraint' and hit the delete button twice (once to delete the default new constraint and once to delete our constraint). Save the table after deleting the constraint to apply changes. You may also delete the constraint by issuing the 'ALTER TABLE [dbo].[orderdetails] DROP CONSTRAINT' command.

Let us take a look at the create command for the 'book' table to get a better idea about establishing foreign key constraints. The bookid field is the primary key of the book table. The table contains a title column, a price column, and a pubdate column. Note that the price column may range upto seven digits with two decimal digits (in effect, 5 digits before the decimal - NUMBER(7,2)). The book table also contains references to the book's author and subject through the AUTHORID and SUBJECTID foreign keys.

CREATE TABLE [dbo].[book](
[bookid] [int] IDENTITY(1,1) NOT NULL,
[authorid] [int] NOT NULL,
[subjectid] [int] NOT NULL,
[title] [varchar](200) NOT NULL,
[price] [money] NOT NULL,
[pubdate] [char](4) NOT NULL,
CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED
(
[bookid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [FK_book1] FOREIGN KEY([authorid])
REFERENCES [dbo].[author] ([authorid]),
CONSTRAINT [FK_book2] FOREIGN KEY([subjectid])
REFERENCES [dbo].[subject] ([subjectid])
) ON [PRIMARY]
GO

Note that the foreign key constraint is defined through the CONSTRAINT - FOREIGN KEY - REFERENCES clause of the create table command. The references section specifies the table whose primary key is referenced by the foreign key (in our case, author for the foreign key authorid and subject for the foreign key subjectid).

An Index on a column improves efficiency when specific values of the column are often looked up within large tables. An index works by storing a sorted value of each indexed column(s) with the exact location (as an offset in bytes) of the row where the value occurs in the table. Thus, the value is looked up in the index (quite fast, as it is sorted) and the row is retrieved by 'jumping' to the specified location. All tables are automatically indexed on the primary key. Indexes are useful in cases where there are many requests for single rows of data via the indexed column. We create two indexes - one based on the Author's name and another based on the book's title. The index command is quite basic, it creates the named index on the specified table on the columns within the parenthesis in the order specified. A non-clustered index pertains to just the table rows stored on the current machine rather than across a cluster of servers. The 'WITH' directive specifies certain optional parameters such as sorting space for the index and so on.

CREATE NONCLUSTERED INDEX [IX_author] ON [dbo].[author]
(
[lastname] ASC,
[firstname] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

The data types we used are just a small subset of SQL Server data types; following are the names and descriptions of some of the more frequently used data types:

• bit - An Integer with value either 0 or 1
• int - A standard integer in the range -2^31 (-2,147,483,648) to 2^31 - 1 (2,147,483,647)
• smallint - An integer in the range 2^15 (-32,768) to 2^15 - 1 (32,767)
• decimal - Fixed precision numeric data in the range -10^38 -1 to 10^38 -1 (may also be referred to as numeric - a synonym)
• money - a data type that may be used for currency from -2^63 (-922,337,203,685,477.5808) to 2^63 - 1 (+922,337,203,685,477.5807) with four decimal places
• float - floating precision number data from -1.79E + 308 to 1.79E + 308.
• datetime - Date and time data from January 1, 1753, to December 31, 9999
• char - Fixed-length character data of up to 8,000 characters.
• varchar - Variable-length stings or character data upto 8,000 characters.
• text - Variable-length non-Unicode strings up to 2^31 - 1 (2,147,483,647) characters.
• nvarchar - Variable-length Unicode dataof up to 4,000 characters.

The drop table command may be used to delete a table. Obviously, right clicking on a table name and clicking 'delete' also opens a confirmation and deletes a table. Drops and alter should be used with great care to prevent data invalidation. Note that 'dbo' is prefixed to every table and object name. This is because we are assuming that the user 'DBO' owns the booksdb database and all constituent objects.

DROP TABLE User.MYTABLE

The alter command may be used to redefine a table and can be used in different ways. The following commands changes the type of the price column and adds a new column to the author table and deletes the newly added sample column.

ALTER TABLE dbo.book ALTER column price smallmoney
ALTER TABLE dbo.author ADD totalbooks smallint
ALTER TABLE dbo.author DROP column totalbooks