Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Transact-SQL: Data Definition Language (DDL) Basics
Last updated Mar 28, 2003.
Before you're able to work with data within a SQL Server database, you need to create the database and data-bearing objects within it. The statements within Transact-SQL (T-SQL) that create objects are grouped into a family called Data Definition Language, or DDL.
DDL is one of three kinds of commands within the Structured Query Language (SQL). The other two are Data Manipulation Language (DML) which queries data and transfers it in and out of the system, and the Data Control Language (DCL) that deals with security of access to the data. In this tutorial I will focus on the Data Definition Language Statements you need to know, and in others I'll follow up with Data Manipulation Language Statements. In the Security section here in the SQL Server Guide on InformIT, I'll cover the DCL statements, although I won't call them out as such. It's actually kind of rare to hear those statements (such as GRANT and DENY) referred to that way.
One final note before I get started — you might also hear "DDL" refer to "Data Description Language," depending on the reference work you review. The term Data Definition Language actually pre-dates the SQL creation, and comes from an older database product called "Codasyl".In most cases, however, the DDL acronym stands for Data Definition Language. This doesn't make any difference to the concepts I cover here, just making sure that you understand all of the acronyms.
There are three major commands I'll focus on in this article:
- CREATE — Makes a new database or database object
- ALTER — Changes an existing database or database object
- DROP — Deletes an existing database or database object
Actually, there are other statements for DDL (at least in Transact-SQL, Microsoft's implementation of SQL) that involve things like disabling Triggers and other kinds of actions — and one DDL command really stands out as a bit surprising is the TRUNCATE statement. The TRUNCATE statement removes all data from a table by de-allocating the page-structures where the data lives, so in fact it's a DLL operation. Since it's always used for data, you might think it was DML.
With that background, there are a few concepts that will help you as you create, alter and delete objects in a database — or even the database itself.
Who You Are is What You Can Do
Before you use the DDL statements, it's important to understand that The Data Control Language (DCL) statements directly affect the DDL statements. What that means is that you need specific rights and permissions to issue them.
Although you might first think that being part of a certain group (called a Role in SQL Server) is what gives you these rights, it's actually the fact that a particular Role has a right granted to it that gives the members of the Role the ability to issue a DDL command.
You'll find a full list of the DDL commands you can enter in Books Online here: http://msdn.microsoft.com/en-us/library/ff848799.aspx
Within that list, you can click through to find the particular DDL command you're interested in. Within each command, you'll see a heading called "Permissions," and that's what you need to check for the rights you need to run the command.
For instance, for the CREATE DATABASE command, you need the CREATE DATABASE, CREATE ANY DATABASE or ALTER ANY DATABASE permission either on your account or on a Role you're a member of. Remember, the combination of all your permissions is what matters — called your "effective" permissions. And also remember that if you are denied a permission that takes precedence over your other rights.
Also, the permissions are different based on the specific DDL you are running. Having the permission to create a database does not necessarily imply that you can create a table or view.
First Things First
Creating an object, altering it or deleting it should be the first part of a "Batch". A Batch in Transact-SQL is simply a set of commands that is kept together.
You can do that in two ways — the first is to run the commands within a transaction, meaning that the command is run to completion with no other commands included. You can do this from a program, PowerShell or in SQL Server Management Studio (SSMS) by typing the CREATE, ALTER or DROP statements, highlighting them and then pressing the "Execute" icon.
One of the most common ways of creating a Batch within code is to use the word "GO" to group the order of operations. you should be in the habit of terminating each line of code with a semi-colon, and then add a GO statement at the end of the group of operations you want to keep together, and DDL should be kept at the top of that group.
Location, Location, Location
Another VERY important concept within DDL is to ensure you know the context of your connection. What I mean by that is that you need to know who you have logged in as and which database you're in.
You can find both with this simple script:
SELECT 'Currently logged in as ' + USER_NAME() + ' on database ' + DB_NAME(); GO
Here's why this matters. Not only does your account need the proper permissions to create, alter or drop the objects, but when you create an object you become its owner. Of course, you can change that later, but you might not think to do that. The owner of an object can determine the rights for others and so on. Certain tracking and auditing also shows who created, altered or dropped an object, so you need to ensure you are logged in as the account you want to show for that. I've seen even experienced professionals have issues with not thinking this through.
You also need to check the database you're in. I won't ask you to raise your hand if you have accidentally created a table in the master system database — because I'd have to raise mine as well. In fact, it's a best practice to place a USE databasename statement at the top of any DDL, just in case.
Implementation and Final Thoughts
Now you're ready to use the statements. Each has its own syntax, and sometimes learning that syntax can be tricky. In SSMS you can highlight a statement like CREATE DATABASE and then press SHIFT-F1 to jump directly to that syntax in Books Online.
But there is another method I often use for not only DDL, but for many T-SQL statements. In SSMS, click the "View" menu item, and then "Template Explorer". Now you can explore the various Templates that Microsoft provides to create objects. Double-click one, like the Create Database Template under the Database item, and you're presented with something like this:
-- ============================================= -- Create database template -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'<Database_Name, sysname, Database_Name>' ) DROP DATABASE <Database_Name, sysname, Database_Name> GO CREATE DATABASE <Database_Name, sysname, Database_Name> GO
Now you can simply press CTRL-SHIFT-M and SSMS will bring up a panel that lets you fill out the parameters quickly and easily. Of course, you still need to know what these mean, so a review of the specific command link I showed you earlier is always a good plan.
Almost any CREATE command needs parameters, with the exception of creating a database. With no other parameter than the name, the CREATE DATABASE command uses the model system database and simply makes a duplicate of it. For instance — I'll use the model database, and put one table in it:
USE model; GO CREATE TABLE TestTable (c1 int); GO Now I'll create a new database, with no parameters: USE master; GO CREATE DATABASE TestDatabase; GO
With a simple query, you can see that the table exists in the new, blank database,
USE TestDatabase; GO SELECT * FROM sys.tables; GO
The ALTER statement requires that the object already exist. If you try to run it without the object, you'll get an error. The advantage of the ALTER statement is that it leaves the permissions on the object intact. So even if you want to completely change a table, don't just delete and recreate it — use the ALTER statement.
The DROP statement is the most deadly within SQL. We are often so conditioned to get an "are you sure?" message in almost every operating system that we might expect the system to protect us. But it doesn't — if you have the rights, the system will delete the object immediately and with no questions asked. Some DDL operations (such as the TRUNCATE command) can in fact be wrapped in a Transaction (BEGIN and END) and allow you to roll it back in some cases, but things like DROP DATABASE are immediate and non-refundable. You'll have to restore from backup to get the database back.
So a great tip is always to test everything you do first on your testing server, which of course you back up first. Which brings up another point — good backups are important always, but when you issue a DDL statement, you should take a backup as quickly as you are able. From that point on, your database is in a different state, so you don't have a current backup, even if you had just taken one prior to the event.
Finally, whenever you use a DDL statement, ensure that you capture a change script so that you can repeat the action, or roll back the database to another state should you need to recreate it elsewhere.
With the documentation complete, ensure that the DDL is captured properly in your High Availability and Disaster Recovery planning. I've seen this forgotten many times — a column of data is added to a table, for instance, with no change to the Recovery Point Interval and Recovery Time Interval being tested and changed in the company's Business Continuity Plan.
Finally, and for the same reason. ensure that you know the maintenance impact of your DDL change. If you have removed or added a database, make sure your automated maintenance is aware of it.