Creating Tables with SQL Scripts

We can create a Database tables in SQL Server using any of the following two modes:

• Using Graphical Environment – Object Explorer
• Using Scripts

The graphical environment is intuitive and is easy to use. It is a great tool for the beginner to get started quickly.

An experienced programmer prefers to use scripts to create the Database tables. A script is a batch file which is run in the SQL Server environment.

The following CREATE TABLE DDL command creates the Customer table. The table name, including the name of the schema is provided, followed by the table’s columns. The code directs SQL Server to create the table on the primary filegroup. Apart from the columns, the only information you normally supply when creating a table is the name.

CREATE TABLE mySchema.Customer (
CustID INT  NOT NULL PRIMARY KEY NONCLUSTERED,
CustName VARCHAR(30) UNIQUE NOT NULL,
City VARCHAR(50) NOT NULL
)
ON [Primary]

Note:

A filegroup is a collection of files. A file or filegroup cannot be used by more than one database. Every database comprises of a Primary filegroup which contains the system tables.

If you are developing a database for mass deployment or repeat installations, having the code in one location helps in mass deployment. Working with SQL scripts is similar to developing an application with Visual Basic or C#.

Another advantage of using scripts is that the existing database can be upgraded to the most current version of the database without running ‘change scripts’ or restoring the backup.

Drawbacks

The T-SQL commands may be unfamiliar and the size of the script may become overwhelming.

If the foreign-key constraints are embedded within the table, the table-creation order is very strict. If the constraints are applied after the tables are created, the table-creation order is no longer a problem; however, the foreign keys are distanced from the tables in the script.