Join Adam Wilbert for an in-depth discussion in this video Using DDL statements to create database objects, part of Database Foundations: Core Concepts.
- Though you can accomplish many tasks through the right-click menus in SQL Server Management Studio, it's going to be worth your time to learn how to do things using the SQL language. By writing out the code, you can begin to better understand how things are working behind the scenes, as well as have a more detailed control over the tasks that you're performing. There are essentially two branches of the language: Data definition language and Data manipulation language. Let's focus on the first one now and then we'll come back to Data manipulation language in the next chapter.
Data Definition Language or DDL is used to define data structures in SQL Server. These statements create and manipulate database objects and use the keywords: USE, CREATE, ALTER, DROP, TRUNCATE, and DELETE. We'll focus on using this language to modify tables, but the same statements can be used to modify other database objects as well such as views or users. When typing out SQL commands, it's best to follow some of the Syntax Conventions. First, it's good to know that SQL is case insensitive.
Meaning that it doesn't matter if you use uppercase or lowercase letters. It all means the same thing. I'll place an asterisk on this, though because, even though you can ignore case, you probably shouldn't. Things get confusing when you're inconsistent in this way. When filling out commands, SQL keywords are typically typed in all capitals. This helps you quickly identify statements and clauses. SQL is also white-space insensitive. So if it helps legibility, feel free to add extra spaces, tabs, or line breaks. Finally, at the end of your statements, use the statement termination character which is the semicolon.
In the past, this has also been an optional component, but my understanding is that it will be a requirement in future versions of SQL Server, so it's best just to get in the habit of using it now. When typing out commands, you can also place comments that can be helpful for others who might try to understand what you were doing, or to remind yourself the result you're expecting from a particular statement. To create an inline comment, use two hyphens. If you have multiple lines, you can wrap a block of text with a forward slash, followed by an asterisk, and at the end, an asterisk followed by a forward slash.
In the SQL editor window, comments will be noted in a dark green font color. Finally, the SQL editor window uses Microsoft's IntelliSense code hinting system to help provide context sensitive options while you type. So if you start typing the name of a table, it'll make suggestions. Or, it'll inform you if you try and create an object with a duplicate name. Just like with the object explorer, though you'll sometimes have to refresh the IntelliSense cache so that it provides up-to-date suggestions. And you can do that with the shortcut, Control+Shift+R.
One of the really nice things about writing SQL commands, is that you can save them in simple text files or scripts to reuse over and over again. Once you get a feel for how they work, you'll be writing your own commands in no time.
Note: This course will also prepare certification candidates for the Microsoft Technology Associate Exam 98-364, Database Administration Fundamentals.
- Understanding flat file, hierarchical, and relational databases
- Installing SQL Server 2014 Express
- Creating your first database and tables
- Creating and editing database objects
- Writing commands in DML
- Selecting and inserting data
- Updating and deleting data
- Establishing relationships and file naming conventions