Data Definition Language - T-SQL

If you have used Management Studio, 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. The table that follows lists the corresponding DDL statements.

Data Definition Language

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, we 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

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, and, in some cases, update or delete values as well. Views are stored in the database, but they don't really store data. A view is really just a 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 (Creating and Managing Database Objects).
When you create a view, you're really just naming a SELECT statement. The syntax looks like this:

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:

Coding -2

Creating a Trigger

Creating a trigger is similar to creating 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 dataintegrity. 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 command affects this inherent transaction and prevents the delete operation from being completed.

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 (Transactions), 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.

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 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 database contains a number of standard system views that provide information about the different objects in a database. This information is known as metadata. Ask any database professional to define metadata and you will undoubtedly get the same response: "Metadata is data about data.” Although this definition is technically correct, it is also pretty much useless in defining what metadata really is. The problem with defining metadata is that metadata is contextual. Depending on how you are looking at the information changes its context. It can be metadata or just data. For example, is the name of the columns on the Production.Product table data or metadata? In the context of the table, it is metadata, but if you were to query the sys.columns system view, the names become data, as the query in Figure (Show IN below) shows.

Data-Definition-Language-Figure

So, yes, metadata is data about data. But more important, it is data that describes other data and gives it context.

You can get a lot of useful information about your database from these views. The following script searches the sys.objects system view to find out if the Person.Address table exists in the current database. If it does exist, the DROP statement is conditionally executed to delete the table.

A line of script may fail for a variety of reasons. Because of 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:

As useful as they are, system views didn't appear to be designed for ease of use or readability, so they can be somewhat cryptic. Another problem if you use system views 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. In lieu of directly querying system views, a set of views is provided with SQL Server to simplify the structure and data in the system views. These information schema views are available in each database. Essentially, these views are just saved queries that extract information from the underlying system views, but the data is formatted and cleansed so that it's easy to read and query. 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 sys.objects table, a similar script may be used with the INFORMATION_SCHEMA.TABLES view, such as in the following:

As Figure (Show In The Below) shows, the output and query of the information schema view is much simpler and straightforward. Appendix D contains a reference for the information schema views included with SQL Server.

Data-Definition-Language-Figure

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 error handling have been added.

/******************************************************
  • Checks for existing Product record
  • If exists, updates the record. If not,
  • inserts new record
  • Revised: 2-12-08 Dan Wood
******************************************************/

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.

Each column or constraint change must be performed in a separate ALTER TABLE statement. For example, if my goal was to add the Lead Time 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.

Dropping Objects

Why is it that the most dangerous commands are the easiest to perform? Dropping an object removes it from the database catalog, 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.

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

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 (T-SQL Programming Objects).

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

T-SQL Topics