Data Definition Language

If you have used Enterprise Manager, Visual Studio, Access, or any other tools to create and design SQL Server databases, you have used Data Definition Language (DDL) — perhaps not directly but by using these user interface tools to manage database objects. Nearly all database maintenance operations are scripted and then that script is executed. This is one reason why there are so many scripting options in the SQL Server management tools. The scripting engine has been there for years in one form or another.

This is a simple topic because you can do only three things with any database object: create it, modify it, or delete it. Subsequently, the corresponding DDL statements are as follows:

Statement	Description
CREATE	Used to create a new object. This applies to many common database objects including Database, Table, View, Procedure, Trigger, and Function.
ALTER	Used to modify the structure of an existing object. The syntax for each of these objects will vary depending on its purpose.
DROP	Used to delete an existing object. Some objects cannot be dropped because they are schema-bound. This means that you may not be able to drop a table if it contains data participating in a relationship or if another object depends on the object you intend to drop.

The syntax of DDL statements is quite simple. A quick tour through each of the common database objects and an example for each follows. Because this isn't a database programming book, it won't be exploring the nuances and uses for these objects, but the syntax used to manage them.

Creating a Table

In its simplest form, to add a new table to the current database, you specify the table name and then list the table's new columns in parentheses, followed by their data type. Here's an example:

 CREATE TABLE Appointment  (   AppointmentID     Int , Description     VarChar(50) , StartDateTime   DateTime , EndDateTime     DateTime , Resource        VarChar(50) Null ) 

You can specify several options for each column definition. Briefly, this might include options such as auto-sequencing identity, default values, constraints, and whether the column value may be set to Null. For a complete list of options, check the SQL Server Books Online documentation.

Creating a View

A view is similar to a table in that users can select from a view like a table. Views are stored in the database but they don't really store data. A view is really just a SQL SELECT query that gets optimized to make it execute more efficiently than if you were to make up the query every time you wanted to select data. However, views can do some very interesting things that we're not going to get into (like actually storing data.). They can be indexed and they can be used with other programming objects to make SQL Server do some very powerful things. Enough for now. The finer points of views are discussed in Chapter 10.

When you create a view, you're really just naming a SQL SELECT statement. The syntax looks like this:

 CREATE VIEW vwProductOrderDetails AS SELECT CustomerID , OrderDate , OrderQty , UnitPrice , Product.Name AS Product FROM SalesOrderHeader  INNER JOIN SalesOrderDetail ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID INNER JOIN Product ON SalesOrderDetail.ProductID = Product.ProductID 

Creating a Stored Procedure

Stored procedures can perform a wide range of actions and business functionality. For example, a stored procedure can insert, update, or delete records in a table. By passing parameter values, it can make decisions and either select data or perform other operations accordingly. Because stored procedures can be used in so many unique ways, it's hard to exemplify a "typical" stored procedure. The syntax for creating a stored procedure is similar to that of a view. Note the input parameters defined just before the word AS:

 /****************************************************** Checks for existing Product record If exists, updates the record.  If not, inserts new record ******************************************************/ CREATE PROCEDURE spInsertOrUpdateProduct -- Input parameters --  @ProductName nVarChar(50), @ProductNumber nVarChar(25), @StdCost Money AS IF EXISTS(SELECT * From Product Where ProductNumber = @ProductNumber) UPDATE Product SET NAME = @ProductName, StandardCost = @StdCost WHERE ProductNumber = @ProductNumber ELSE INSERT INTO Product (Name, ProductNumber, StandardCost) SELECT @ProductName, @ProductNumber, @StdCost 

Creating a Trigger

Creating a trigger is similar to a stored procedure. Actually, a trigger is a special type of stored procedure that gets executed when specific operations are performed on the records in a table (such as an Insert, Update, or Delete). Business logic similar to that of a standard stored procedure may be performed within a trigger, but it is typically used to apply specialized business rules to ensure data integrity. Some of the unique characteristics of triggers include their assignment to a DML operation (Insert, Update, and/or Delete), implicit transaction control, and virtual table references that are used to represent the record or records involved in the transaction that caused the trigger to fire.

In the following example, note the reference to a virtual table called Deleted. This "table" is actually a virtual set of rows that are in the process of being deleted as the trigger is automatically executed. There is no script to explicitly begin processing a transaction because the trigger execution is the result of a transaction in progress. The Rollback Transaction script affects this inherent transaction and prevents the delete operation from being completed.

 /****************************************************** Checks for existing sales orders using the product being deleted. Prevents deletion if orders exist. ******************************************************/ CREATE TRIGGER tr_DelProduct ON Product FOR DELETE  AS  IF (SELECT Count(*) FROM SalesOrderDetail  INNER JOIN Deleted ON SalesOrderDetail.ProductID = Deleted.ProductID) > 0 BEGIN RAISERROR 50009 'Cannot delete a product with sales  ROLLBACK TRANSACTION RETURN END 

Creating a User-Defined Function

User-defined functions are used to apply custom business logic such as performing calculations, parsing values, and making value comparisons. Functions are often called within views and stored procedures to reduce code redundancy and to encapsulate functionality. The script used to create a new user-defined function is similar to that of a stored procedure. The function is defined on the first executable line of the script (preceded in this example by a comment block). Immediately following the CREATE command, the function name references one or more parameters followed by a data type, in parentheses. The text following the Returns keyword indicates the data type that the function will return. This is a simple scalar (single value) function that returns a DateTime type value. In Chapter 10 you also learn how user-defined functions can return complex, multi-value results and table-type result sets; similar to a view or stored procedure. This function utilizes local variables and system functions to perform its internal logic.

 /********************************************************** Returns a date representing the last date of any given month. **********************************************************/ CREATE Function dbo.fn_LastOfMonth(@TheDate DateTime)  Returns DateTime AS BEGIN DECLARE @FirstOfMonth  DateTime DECLARE @DaysInMonth Int DECLARE @RetDate DateTime SET @FirstOfMonth = DATEADD(mm, DATEDIFF(mm,0,@TheDate), 0) SET @DaysInMonth = DATEDIFF(d, @FirstOfMonth, DATEADD(m, 1, @FirstOfMonth)) RETURN  DATEADD(d, @DaysInMonth - 1, @FirstOfMonth) END 

Scripting Practices

When scripting objects, a common practice is to check for the existence of the object before creating it. Although this isn't necessary when you know the object isn't already in the database, if you generate script using Enterprise Manager, Query Analyzer, or SQL Server Management Studio, logic is typically included to remove the object if it exists and then re-create it. Keep in mind that dropping and re-creating an object will remove any security privileges that have been granted to users. If you simply need to modify an object to add capabilities, it may be advisable to use the ALTER command rather than DROP followed by the CREATE command. A number of different scripting options can be used to customize auto-generated script, and many of the non-default options may be unnecessary.

Every SQL Server 2000 database contains a number of standard system tables. Generally, you need not be concerned with these tables and really ought to leave them alone. However, you can get a lot of useful information about your database from these tables. The following script searches the sysobjects system table to find out if the Product table exists in the current database. If it does exist, the DROP statement is conditionally executed to delete the table. This script will also work on a SQL Server 2005 instance, but sysobjects isn't a table in SQL Server 2005, it is a view. There is no direct access to system tables in SQL Server 2005.

 IF EXISTS ( SELECT * FROM sysobjects WHERE Name =  ) DROP TABLE Product GO 

A line of script may fail for a variety of reasons. Due to referential constraints and other dependencies, tables must be dropped in the right order. In case the table isn't successfully dropped, it may be a good idea to check again for the existence of the table before attempting to create it. This is performed in the following script fragment (shortened for simplicity):

 IF NOT EXISTS ( SELECT * FROM sysobjects WHERE Name =  )  BEGIN CREATE TABLE Product  ( ProductID Int Identity , Name nVarChar(50) Not Null , ProductNumber nVarChar(25) Null , DiscontinuedDate DateTime Null ... 

System tables were not designed for ease of use or readability, so they can be somewhat cryptic. Another problem if you use system tables is that there are no guarantees that they won't change in later versions of SQL Server, possibly breaking your code if you were to upgrade and migrate your database. This is the case if you plan to upgrade from SQL Server 2000 to SQL Server 2005. As previously mentioned, there is no direct access to system tables. Microsoft created a number of system views in SQL Server 2005 to replace the system tables from SQL Server 2000. The new views have the same name as the old tables so scripts created on a SQL Server 2000 instance should still work. However, Microsoft has been recommending not querying system tables directly for years and there is no guarantee that SQL Server 2000 scripts that ran against system objects will continue to work. In lieu of directly querying system tables, a set of views is provided with both SQL Server 2000 and 2005 to simplify the structure and data in the system tables and the new system views. These Information Schema views are stored in the Master database and can be used in any database on the server. Each view is prefixed with the name INFORMATION_SCHEMA, followed by a period and a general object type. In place of the script in the previous example, which selects from the sysobjects table, similar script may be used with the INFORMATION_SCHEMA.TABLES view, such as in the following:

 IF EXISTS ( SELECT * FROM Information_Schema.Tables WHERE Table_Name = 'Product' ) DROP TABLE Product GO 

Altering Objects

The script used to modify some existing objects is very similar to the syntax used to create objects, using the ALTER command in place of CREATE. This is the case for objects that contain SQL expressions such as views, stored procedures, and user-defined functions. The following script is very similar to the example used to demonstrate how to create a stored procedure. An additional input parameter and a line to handle error conditions have been added.

 /****************************************************** Checks for existing Product record If exists, updates the record.  If not, inserts new record Revised: 4-12-06 PT ******************************************************/ ALTER PROCEDURE spInsertOrUpdateProduct -- Input parameters --  @ProductName nVarChar(50), @ProductNumber nVarChar(25), @StdCost Money, @ListPrice Money AS IF EXISTS (SELECT * FROM Product WHERE ProductNumber = @ProductNumber) UPDATE Product  SET Name = @ProductName , StandardCost = @StdCost , ListPrice = @ListPrice WHERE ProductNumber = @ProductNumber ELSE INSERT INTO Product (Name, ProductNumber, StandardCost, ListPrice) SELECT @ProductName, @ProductNumber, @StdCost, @ListPrice IF @@Error <> 0 RAISERROR ('spInsertUpdateProduct execution  

After the ALTER statement has been executed, the object retains all of its previous properties and security access privileges or restrictions, but its definition is updated with any of the script changes. This includes the comment block before the ALTER statement line.

Some objects require different syntax used to alter their definition than the language used to create them. For example, when creating a table, columns are defined within parentheses after the table name. To alter the design of a table and change the columns, you would use the ADD or DROP keyword before each column definition. Any existing columns that are not addressed in the Alter Table script remain in the table's definition.

 ALTER TABLE Appointment  ADD LeadTime SmallInt Null 

Each column or constraint change must be performed in a separate ALTER TABLE statement. For example, if my goal was to add the LeadTime column and to drop the Resource column, this can be performed using the previous and next statements but can't be done in a single statement.

 ALTER TABLE Appointment  DROP COLUMN Resource 

Dropping Objects

Why is it that the most dangerous commands are the easiest to perform? Dropping an object removes it from the database catalog (from the system tables), completely deleting it from the database. Tables containing data and their related indexes are de-allocated, freeing the storage space for other data. To quote a well-known former president of the United States, "let me make one thing perfectly clear". . . There is no Undo command in SQL Server. If you have dropped an object or deleted data, it's gone. However, the storage space occupied by dropped or truncated objects is not actually wiped clean and made available to the operating system unless the database is set to AutoShrink.

The syntax for dropping all objects is the same: DROP objecttype objectname. Here are a few examples of script used to drop the objects I previously created:

 DROP TABLE Appointment DROP VIEW vwProductOrderDetails DROP PROCEDURE spInsertOrUpdateProduct DROP TRIGGER TR_Del_Product DROP FUNCTION dbo.fn_LastOfMonth 

Some objects cannot be dropped if there are dependent objects that would be affected if they no longer existed. Examples are tables with foreign key constraints, user-defined types, and rules. This safety feature is called schema binding. Some objects don't enforce schema binding by default but it can be created to explicitly enforce this rule. Views, stored procedures, and user-defined functions can optionally be created with schema binding and prevent orphaned dependencies. This feature is discussed in greater detail in Chapter 10.