Transact-SQL
As we have seen in the previous instalment of this tutorial, the SQL Server Management Studio (SSMS) graphical user interface can be used to create and configure new databases. This use of SSMS is ideal for development scenarios where full access to the server is available. However, in many real-life situations you may have no direct access to a SQL Server. For example, with off-the-shelf software you may have to rely upon executing commands from your script or program against a SQL Server installation that you have never seen.
To allow commands to be executed against a SQL Server instance or database, the Transact-SQL (T-SQL) language is used. This is Microsoft's variant of the structured query language (SQL). It contains textual commands, or statements, that are used to create databases and their constituent parts, to query tables and views and to manipulate data. All tasks that can be undertaken with SQL Server are controlled using T-SQL.
NB: When creating a database using a graphical user interface such as SSMS, the selections made are converted into an appropriate T-SQL statement for execution. In many cases, you will find a "Script" button on the screen that allows you to view the underlying T-SQL code.
Executing T-SQL in SQL Server Management Studio
There are many commercial and freeware tools available for purchase or download that can be used to issue T-SQL commands to a SQL Server instance. T-SQL statements may also be executed from program code, such as .NET framework-based software. However, for the purposes of this article and tutorial we will use either SQL Server Management Studio or the free SQL Server Management Studio Express software.
To run T-SQL commands in SSMS, a query window must be opened and connected to a SQL server instance and database. If you have not already done so, open SSMS and enter the details of the SQL Server that you are using for the tutorial's examples. You can now open a new query window by opening the File menu, selecting the New submenu and clicking the option, "Query with Current Connection". You can also open such a window by clicking the "New Query" toolbar button or by pressing Ctrl-N.
Windows in SSMS appear in a tabbed layout by default, allowing you to click between tabs to show the other open windows. The tab at the top of the query window shows the name of the current SQL server and the name of the database that has been selected automatically. The database chosen will vary according to your security privileges.
NB: It is very important to ensure that the database selected is the one that you want to execute commands against. If the database is incorrect, you can quickly change it using the drop-down list of databases in the toolbar.
Executing a Simple Command
Query windows allow T-SQL statements to be typed and executed. We can test this with a simple command that outputs some text. Type the following command into the new query window. Note the use of apostrophes around the literal text. This is T-SQL's way of declaring string data.
To execute the command, ensure that no text is selected and then choose Execute from the Query menu. You can also click the Execute button or press the F5 key to run a command. On completion, the query window will update to show the output of the print statement.
Executing a Multiple Line Command
T-SQL commands do not need to be run individually. A script of multiple statements can be created, simply by adding more commands to the query window. Try changing the query as follows and then hitting F5 to see the results. Again, ensure that no text is selected in the query window before executing.
PRINT 'Hello'
PRINT 'world'
Executing a Selection
In the last two examples no selection was made when the Execute command was given. When no text is selected, the entire content of the query window forms the script to be run. One very useful aspect of SSMS is the ability to select one or more lines, or even parts of lines, from a query window and only run that part. You can try this by selecting either of the two print statements and pressing F5. This is advantageous when you have multiple statements in a single window but want to run them individually.
The CREATE DATABASE Command
Now that we can run statements, we can investigate the CREATE DATABASE command. This command allows a new database to be generated. In its simplest form, only a name for the new database is required with all settings and file locations automatically using their defaults.
To create a new database for the JoBS example, try executing the following statement. After execution, right-click the "Databases" branch of the object explorer tree and choose "Refresh" from the context-sensitive menu that appears to see the new database in the list.
NB: In this tutorial we will be creating a database named JoBS. In the event that your SQL Server instance already has a JoBS database, select a different name and use this new name in place of JoBS for the entire tutorial.
If you have been following the tutorial examples so far, the above command will fail with an error explaining that the database already exists. A database can be deleted, or dropped, using the DROP DATABASE command. To drop the JoBS database, execute the following statement.
NB: If you are using a different name than "JoBS", replace this in the drop statement. Check this carefully to ensure that you do not delete the wrong database!
You may receive an error when trying to drop a database if the database is in use. Any connection to the database, including having the database selected as current for any open query window, will prevent it from being deleted.
21 June 2008