Manage Learn to apply best practices and optimize your operations.

Management Studio Exercises

Wrap up your lesson on SQL Server 2005 Management Studio with a few exercises to test what you learned, excerpted from SQL Server 2005: A Beginner's Guide.

Microsoft SQL Server 2005: The Complete Reference The following was excerpted from Chapter 2, 'SQL Server 2005 Management Studio,' of the book SQL Server 2005: A Beginner's Guide written by Dusan Petkovic and published by McGraw-Hill Osborne Media. Click here for the book excerpt series.

Bonus: TechTarget members take 45% off the price of this book at McGraw-Hill's online bookstore. Just enter promotion code AETT511 on the homepage.


This chapter covers the new SQL Server component called SQL Server Management Studio. This tool is very useful for end users as well as administrators. First, it allows many administrative functions to be performed. These are touched on here but are covered in more detail later in the book. The most important functions of SQL Server Management Studio concerning end users—database and table creation—are discussed in more detail in this chapter.

SQL Server Management Studio contains several component windows:

  • Registered Servers
  • Object Explorer
  • Query Editor
  • Solution Explorer
  • The Registered Servers component window allows you to register SQL Server instances and connect to them. The Object Explorer window contains a tree view of all the database objects in a server.

    Query Editor is a subcomponent of SQL Server Management Studio that allows end users to generate, execute, and store Transact-SQL statements. Additionally, it provides the ability to analyze queries by displaying the execution plan, and it also provides the ability to create suggestions with respect to index creation.

    The Solution Explorer component allows you to create solutions. A solution can have zero or more projects associated with it.

    The next chapter introduces the Transact-SQL language and describes its main components. After introducing the basic concepts and existing data types, the chapter also describes system functions that Transact-SQL supports.



    Using SQL Server Management Studio, create a database called test. The database is stored in the file named testdate_a of the directory C:tmp and the space allocated is 10MB. The file in which the database is located should grow with portions of 2MB and should not be larger than 20MB.


    Using SQL Server Management Studio, change the transaction log for the test data¬base. The initial size of the file is 3MB, with growth of 20 percent. Allow the file for the transaction log to autogrow.


    Using SQL Server Management Studio, allow the use of the test database only to the database owner and system administrator. Is it possible that several users could use the database at the same time?


    Using SQL Server Management Studio, create all four tables of the sample database (see Chapter 1) with all their columns.


    Using SQL Server Management Studio, view which tables the AdventureWorks database contains. After that, choose the person.address table and view its properties.


    Using Query Editor, type the following Transact-SQL statement:

    Explain the error message shown in the result pane.


    Store the Transact-SQL statement (E.2.6) in the file C:tmpcreatedb.sql.


    Using Query Editor, how can you make the test database the current database?


    Using Query Editor, make the AdventureWorks database the current database and execute the following Transact-SQL statement: SELECT * FROM Sales.Customer Stop the execution of that statement. How can you do it?

    Click for the complete book excerpt series.

    Dig Deeper on SQL Server Database Modeling and Design