Working with Queries
SQL Management Studio includes a query tool that lets you type SQL commands and execute them. You can type any SQL statement you want in a query Window, and then press the Execute button to execute the query.
There are two important rules to follow when you work with queries:
- First, each SQL statement in the query should end with a semicolon.
- Second, you should begin the query with a USE statement that provides the name of the database.
Here's an example of a query that follows these two rules:
use VideoStore; select * from movies;
The USE statement indicates that the query applies to the VideoStore database, and the select statement retrieves all the data from the Movies table.
To use the query tool, click the New Query button on the toolbar. Then, enter the statements for your query in the window and click the Execute button on the toolbar. The results of the query will be displayed in the window.
Working with Scripts
Although you can create databases by using the graphical features presented earlier in this tutorial (in the sections "Creating a New Database" and "Creating Tables"), it's actually a much better practice to write SQL scripts that contain the CREATE commands necessary to create the database as well as its tables and other objects. That's because during the development and operation of any database application, there's often a need to delete the database and re-create it from scratch. By scripting these actions, you can delete the database and re-create it simply by running a script.
Fortunately, SQL Management Studio can generate scripts from existing databases and tables. Thus, you can use the visual design features of SQL Management Studio to initially create your databases. Then, you can generate scripts that will let you easily delete and re-recreate the database. To create a script for a database or table, just right-click the database or table, and then choose one of the Script As commands.
After you've created the script, you can save it to a text file by clicking the Save button. Then, you can run the script at any time by following these steps:
- Choose File → Open → File.
- Select the file you saved the script to.
- Click Open.
- Click the Execute button to run the script.
The only limitation of this technique is that although you can generate scripts to define your databases and tables, you can't automatically generate scripts to insert data into your tables. If you want scripts that insert data, you have to manually create the INSERT statements to insert the data.