Introduction to SQL Server 2005 Management Studio

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.

Introduction

SQL Server provides a number of tools that serve different purposes, such as installation, database query, and replication. All these tools have user-friendly graphical interfaces. This chapter examines first the Start menu and discusses briefly Books Online, the online reference source with all necessary information concerning SQL Server. After that, the most important SQL Server front-end component, SQL Server Management Studio, will be introduced. By the end of this chapter, you should be able to create and manage database objects using this tool. You will also learn all the functions necessary for creating and executing any Transact-SQL statements.

NOTE: This chapter is dedicated to the activities of the end user. Therefore, only the functionality of SQL Server Management Studio with respect to the creation of database objects is described in detail. All administrative tasks and all tasks related to Analysis Services and other components, which this tool also supports, will be discussed later, beginning with Chapter 17.

The SQL Server Program Group and Books Online

To see the SQL Server program group, you have to click Start | Programs and, finally, Microsoft SQL Server 2005. The SQL Server program group comprises all applications you will use during your work with SQL Server. In this chapter we will look only at particular applications, starting with SQL Server Books Online (BOL).

Books Online is the online documentation that is delivered and installed with all other SQL Server software components.

The toolbar of the Books Online has, among others, buttons for four different features:

• Contents
• Index
• Search
• Favorites

If you click the Contents button, you will see the content of the whole online documentation divided into different books. Each specific topic (such as MS SQL Server 2005 Mobile Edition, for instance) is divided into subtopics, which can be opened by clicking the corresponding plus (+) symbol of the tree. You can view the content of each subfolder in the same way—that is, by clicking the + symbols of the corresponding book and all superfolders of that folder.

The Index window shows an alphabetically sorted sequence of each keyword that appears in the online documentation. There are two ways to select one of the keywords: by double-clicking the keyword in the list or by typing the starting letters of it. In the latter case, the system selects (and highlights) the alphabetically first keyword in the list that has the typed letters at its beginning.

The Search button is the most used button. It allows you to type a phrase (or a single word) that is then used in the search process to display all topics where they appear. (SQL Server uses its own Full Text Search component to support this process.) Besides Books Online, SQL Server 2005 searches the phrase also in MSDN Online and Codezone Community. (The Codezone Community is a site for third-party tips, code samples, advice, and news from independent experts on the Microsoft .NET Framework and Microsoft Visual Studio.)

The results of the search process are displayed in the middle of the default pane. After clicking the Search button, the corresponding results appear in the pane. Each result includes the title of the document and a short description of the topic. The total number of search results appears also in the upper-right corner of the pane.

The Favorites tab allows you to store selected keywords (and phrases). To select again one of the stored topics, double-click the entry in the list of your favorites.

Introduction to SQL Server Management Studio

The SQL Server administrator's primary tool for interacting with the system is SQL Server Management Studio. Both administrators and end users can use this tool to administer multiple servers, develop databases, and replicate data, among other things. To open this tool, click the Start menu, Programs, Microsoft SQL Server 2005, and then SQL Server Management Studio in the SQL Server program group. Every user with access to SQL Server can also use SQL Server Management Studio. Figure 2-1 shows the Connect to Server dialog box, which appears right after choosing SQL Server Management Studio. In the dialog box, you have to choose the server type (Database Engine, in this case) and select or type the name of the server that you want to use. Also, you have to choose between two existing authentication types.

If you select Windows Authentication, you will be connected to SQL Server using your Windows account. If you select the other option (SQL Server Authentication), SQL Server uses its own authentication. The former option is much simpler, while the latter enhances SQL server security with an additional security level. (For more information concerning SQL Server security, see Chapter 12.)

After connecting to a database server, the default window settings appear (see Figure 2-2). The default appearance is similar to Visual Studio, so users can leverage their experience of developing in Visual Studio to use this SQL Server component more easily.

Note: SQL Server Management Studio is a completely new application that gives you unique interface to manage servers and create queries across all SQL Server components. This means that SQL Server Management Studio offers one interface for Database Engine, Analysis Services, SQL Server Mobile (former SQL Server CE), Integration Services, and Reporting Services . As such, SQL Server Management Studio replaces Enterprise Manager and Query Analyzer.

Figure 2-1: The Connect to Server dialog box

Figure 2-2: SQL Server Management Studio: default windows settings

SQL Server Management Studio opens by default with several component windows: the Registered Servers window, the Object Explorer window, and the Document window. (If any of these windows do not appear, select the View menu and choose the omitted window.)

The Registered Servers window lists servers that you manage. The list can be either enhanced with new servers or one or more existing servers can be removed from the list.

The Object Explorer window contains a tree view of all the database objects in a server. This includes Database Engine databases, objects of Analysis Services, Integration Services, and Report Services, as well as objects of and SQL Server Mobile.

The Document window contains query editors and browser windows. When you first start up Management Studio, the Summary window is displayed, containing the information of the default instance of SQL Server on the current computer. All windows of Management Studio can be grouped into two groups: The central, non-movable area with the Document window and the peripheral, movable areas with all component windows.

Each of the component windows can be docked or hidden. By right-clicking the bar over the top of the corresponding window, you can choose between the following presentation possibilities: Floating, Dockable, Tabbed Document, Hide, and Auto Hide.

To hide a window, right-click the bar at the top of the window and choose Hide. (The alternative way is to click the x in the upper-right corner of the window.) The corresponding window closes. A removed window can be retrieved by selecting the window name from the View menu.

To minimize a window, right-click the bar and choose Auto Hide. SQL Server Management Studio minimizes the window and stores it on the left side of the screen. (The alternative way is to click the Auto Hide button in the upper-right corner of the window.) To reopen such a window, move your mouse over the tabs on the left side of the screen. Click the pushpin to pin the window in the open position. (The difference between the Hide and Auto Hide options is that the former option removes the window from SQL Server Management Studio, while the latter collapses the window to the side panel.)

If you use the Dockable option, you can also move components of SQL Server Management Studio and dock them in different positions. To move a component, click and drag the title bar of the component into the middle of the document window. The component undocks and will remain floating until you drop it.

To restore the default configuration, click the Window menu and select Reset Window Layout.

NOTE: You will find that there will often be several ways of accomplishing the same task within SQL Server Management Studio. This chapter will indicate more than one way to do things; later, only a single method will be given. Different people prefer different methods (some like to double-click, some like to click the +/– signs, some like to right-click, others like to use the pull-down menus, and others like to use the keyboard as much as possible). Experiment with the different ways to navigate, and use the methods that feel most natural to you.

A subobject appears only if you click the plus (+) sign of its direct predecessor. A right-click on an object and the selection of the Properties function displays the properties of that object. The minus (−) sign indicates that an object is currently expanded (for example, server). To compress all subobjects of an object, click its minus sign. (Another possibility would be to double-click the folder, or press the LEFT-ARROW key while the folder is selected.)

Click for the complete book excerpt series.