Creating Tables with an SQL Script
Knowing how to read, interpret, and write SQL scripts is useful, even if you use the GUI most of the time. For recreating a database, a script is much easier than the typing-and-clicking option. For example, the script that creates the authors table in the Pubs database looks like this:
USE pubs
GO
CREATE TABLE dbo.authors (
au_id varchar(11) NOT NULL ,
au_lname varchar(40) NOT NULL ,
au_fname varchar(20) NOT NULL ,
phone char(12) NOT NULL ,
address varchar(40) NULL ,
city varchar(20) NULL ,
state char(2) NULL ,
zip char(5) NULL ,
contract bit NOT NULL )
This script simply builds the table; each line adds a column to the table. You can use Transact SQL (T-SQL) for creating indexes, adding constraints, and other features, all of which I'll cover in future columns.
Specify Nullability
The author table example script specifies NULL or NOT NULL for each column. Specifying nullability is a good habit to get into. SQL Server defaults to NOT NULL for all columns. However, ODBC and OLE DB applications set the null default to the ANSI standard, to allow nulls, which is why the GUI sets all the columns to allow nulls. (For more details on using NULLs, see SQL by Design, "The Reason for NULL," page 65.) If you rely on the defaults, remember that you can change the default nullability for the server, the database, or the session you used to run the script.
For more practice, see the sidebar, "Practice Questions: Building Tables." WebSQL subscribers can find a practice lab at the link to this article at http://www.sqlmag.com. Also at this site, you can find references to books that can help you acquire the knowledge you need for certification.
End of Article
Prev. page
1
[2]
next page -->
