Transact-SQL is a powerful implementation of the ANSI standard SQL database query language. In order to build effective database applications, you must gain a thorough understanding of creating and managing database objects. This excerpt from Chapter 12 of Beginning Transact-SQL with SQL Server 2000 and 2005 covers the use of database objects related to tables, views, stored procedures, and user-defined functions.
My friend Eric said that I should have titled this chapter "Going Fishing." I commute and do much of my writing on the Washington State ferries and I get a lot of input from my fellow commuters. When I asked Eric "Why Going Fishing," he said that managing database objects is boring and that people would be more interested in fishing stories than creating tables. The fact is that creating and managing database objects is pretty simple (and, yes, mundane) stuff. However, there is little doubt that this is an essential part of the process. After all, how can you use a database until it's been designed and created?
Anything you can do with SQL Server's graphical or console management tools can be done with script. Many of the menu options in Enterprise Manager and Management studio actually use the Transact-SQL script you'll be using in this chapter.
Data Definition Language
You can do only three things with any database object, other than actually use it; you can create it, alter it, or drop it to get rid of it. Every type of database object at least supports the CREATE and DROP statements. Of course, there is actually a little more to this story. Depending on the object type, there are a number of options affecting certain capabilities and settings. You've already seen a number of these statements, because it would have been difficult to cover earlier topics without first showing you how to create some objects.
Unless permissions are explicitly assigned, only members of the following roles can execute these statements:
It's a good idea to use role assignments rather than changing permissions for individual users. In the long run, this creates a more manageable environment.
SQL Server 2005 introduces several new objects that have corresponding CREATE and DROP statements. Many of these objects also have a corresponding ALTER statement. Managing all of these special-purpose objects is beyond the scope of this book. This chapter covers the use of these statements related to tables, views, stored procedures, and user-defined functions.
The basic pattern of the CREATE statement is the same for all objects. However, due to the unique characteristics of different objects, each statement may have a number of different options. To keep things simple, this chapter does not include examples of every incarnation of CREATE statements, but it does includes the most common.
Generally, any options or changes that can be applied with the CREATE statement can be applied to an existing object using the ALTER statement. If security permissions have been granted or denied for an object, it's a good idea to use the ALTER statement to make changes to an object rather than dropping and re-creating it. This way, the security settings are preserved and the database doesn't make a new entry into the sysobjects system table. A consequence of dropping and re-creating some objects is that this may affect related dependencies. For example, dropping a table with indexes is a cumbersome task and any dependent objects created with schema binding will cause errors to be raised. Altering tables instead wouldn't have the same impact, especially if you didn't make changes to columns that would affect other objects.
The syntax for dropping most any object is pretty much the same. You cannot drop objects that would render schema-bound dependencies invalid. For example, you cannot drop a table if it is referenced in a dependent foreign key constraint. In this case, you must either drop the related table or alter the table and remove the constraint.
The rules for naming objects in SQL Server are extraordinarily flexible. They are so flexible, in fact, that it becomes very easy to create objects with names that will cause pain and suffering from the moment you create them. The following sections explain two naming guidelines. The first I would consider more a set of rules than a guideline, and the second is strictly a naming convention. You can disagree with it if you like, but examine the merits of the convention and then come up with your own.
This chapter excerpt from Beginning Transact-SQL with SQL Server 2000 and 2005 by Paul Turley and Dan Wood is printed with permission from Wrox Press, Copyright 2005.