SQL Server Management Studio completely replaces Enterprise Manager and Query Analyzer. It also replaces some of the functionality formerly found in Analysis Manager. It does an excellent job of both replacing the old tools and exceeding them in almost every possible way.
The SQL Server Management Studio interface looks a lot like the Visual Studio IDE and is in actuality a Visual Studio shell. The Visual Studio shell brings many very useful tools and features to the creation and organization of database objects, as well as the full feature set of the old tools.
If you have SQL Server 2000 or SQL Server 7 experience, you will notice that when SQL Server Management Studio is first launched, the default view is a great deal like the old Enterprise Manager, with a slight Query Analyzer influence (see Figure IN The Below).
Many different windows can be viewed in Management Studio, so the management of screen real estate becomes critical. Most of the windows have the ability to either be pinned open or configured to fly out when the mouse pointer is placed over the menu bar or auto hide when the mouse cursor is placed elsewhere. If you are familiar with the Visual Studio IDE, this will be very familiar; if not, it may take a little while to get used to.
For those of you who are unfamiliar with the Visual Studio interface, the following bit of instruction is offered: Any window that supports the pinned and unpinned option will have a pin at the top right of the window. When the window is pinned, the pin will appear vertically oriented. When the window is unpinned, it will be horizontal (see Figure IN The Below) and the toolbar will auto hide or fly out, depending on the mouse cursor location.
Most of the toolbars and windows also support the ability to be repositioned or configured to float. To reposition a tool window, you can just grab it by the title bar using the mouse pointer, and then drag to undock and move it around the design surface (see Figure IN The Below). Something interesting happens when you do this.
When a window is undocked and you drag it around the main window, guide diamonds are displayed, like points of a compass, to assist with the docking window placement (see Figure IN The Below).
When you hover over one of the guide diamonds, the docking target area of the window is designated with a translucent shaded rectangle. As you see in Figure IN The Below, you can also use the guide diamonds in the center cluster of the window. If a window is already docked in that area, using the center guide will dock your window adjacent to the existing window.
Microsoft SQL Server Management Studio
If you hover over an existing docked window, a separate set of guides will appear, allowing you to dock within this space or to create tabbed documents where the windows share screen real estate with other windows in the same space (see Figure IN The Below).
If you get into trouble and can't place a window where you want it, click to set focus to the window and then use the Window menu on the standard menu bar to toggle the window back to either Floating or Dockable. This should allow you to reposition the window as you like. If you don't have any experience with Microsoft's development environment, the Visual Studio interface may take a little getting used to, but once you do, it is hard to imagine any interface that works as well. It offers the advantage of being able to hide windows when you don't need them but make them visible when you do, without having to reconfigure the interface. This conserves a great deal of screen real estate without having to click on several menus to expose the features you want.
SQL Server Management Studio offers many different tool windows that facilitate the development and modification of database objects as well as the effective management of SQL Server. The various views are accessible from the View menu as well as the Standard toolbar. Each window can be configured as dockable, which is the default, but it can also be configured as a tabbed document or a floating window.
Object Explorer is more than just a way to explore the database objects on a server; it is also a tool that can be used to create basic template scripts for selecting, inserting, updating, and deleting data. As shown in Figure (IN The Below), Object Explorer is arranged in a standard tree view with different groups of objects nested in folders.
Object Explorer's functionality is exposed through the context menu. Right-clicking on any object or folder within Object Explorer exposes the list of available options. For example, right-clicking on a table exposes the context menu shown in Figure (IN The Below). Putting the mouse over the Script Table As option exposes additional options for creating basic scripts for that table, as also shown in Figure (IN The Below).
The context menu also presents the ability to create scripts that manipulate the object. For example, right-clicking on a table exposes a context menu that allows the user to either view or modify the table structure through the graphical interface. This functionality exists for virtually every object that is visible in Object Explorer.
Another great feature of SQL Server Management Studio that is exposed through Object Explorer and other areas of the studio interface is the ability to create scripts based on actions performed in the graphical designers. For instance, right-clicking on the table folder and choosing to create a new table launches a graphical interface where the table structure can be defined. Once the table design is complete, you can either save the table, which creates it, or you can click the Generate Change Script button on the Table Designer toolbar (see Figure (IN The Below)), which will write the appropriate T-SQL to complete the task.
Likewise when working with other objects in Management Studio, a Script button will appear at the top of the respective designer, which will cause the actions performed in the designer to be scripted to a new editor window. This feature is especially useful when several different objects of the same type are to be created. The first one can be designed in the designer, the script generated for it, and that script modified to create the remaining objects. It is also very useful to learn the syntax for creating and modifying objects.
In this example you will use Object Explorer to create a script to select data from the Human Resources. Department table.
SQL Server Management Studio's Code Editor provides the ability to open, edit, or create new queries. When you click New Query, the query window that opens is also known as the Code Editor. The Code Editor supports the following types of queries:
The Code Editor is essentially a word processor. It provides color coding of syntax, multiple query windows, and partial code execution by highlighting the desired code and clicking the execute button or pressing F5. SQL Server documentation refers to the Code Editor as the Query Editor, the Text Editor, or simply the Editor, depending on what aspect of SQL Server you are reading about.
In SQL Server 2008, the basic functionality that the Code Editor brings is the same for all the possible types of queries it supports. For example, the Code Editor provides support for basic IntelliSense functions and code completion such as those found in Visual Studio. In SQL Server 2005 the code-completion and IntelliSense features are not available with T-SQL queries. IntelliSense and code-completion are discussed later in this chapter. The Code Editor window also provides direct access to a graphical query builder. Right-clicking on the Code Editor window, when that window is associated with a database engine query, results in a context menu that includes the Design Query in Editor option (see Figure (IN The Below)). The Query Designer is very useful when writing queries against databases that you are not familiar with. At the end of this chapter is an exercise to show you how to use this useful tool to quickly create T-SQL queries.
Before SQL Server 2005, organizing queries and object definitions was completely left to the DBA or database developer. The ability to organize and group scripts together or to check them in to a source control system was completely manual. SQL Server Management Studio takes full advantage of Visual Studio's solution system by providing the means of grouping one or more projects that contain various connection objects and scripts into a single solution called a SQL Server Management Studio solution. Each solution can have one or more projects associated with it. For example, if you are developing several objects for a new application that includes both database engine and analysis engine objects, you can create a new solution that links them all together by creating a SQL Server Management Studio solution and creating both a SQL Server Scripts and Analysis Server Scripts project in that solution. You do this, oddly enough, not by creating a new solution, which there is no option for, but instead by creating a new project by clicking the File menu and choosing New Project, which launches the New Project dialog (see Figure (IN The Below)).
If no solution is currently open, Management Studio will create a new solution. If a solution is presently open, you will be given the choice of adding the project to the existing solution or closing the present solution and creating a new one. As you can see in Figure (Above), there are three types of projects from which to choose:
The solution is managed through a solution file with an .ssmssln extension. The example shown in Figure (IN The Below) created a new solution folder called Adventure Works Web App that contains a project folder called Product Catalog. By default the solution folder and the first project folder will have the same name, so it is generally a good idea to change the name of the solution. The Create directory for solution option can also be cleared and a base solution folder specified. In this way only a project folder will be created in the specified directory. If a solution is already opened, creating a new project can add the project to the solution or be configured to create a whole new solution and close the open one.
In the solution folder are two files. One file is the solution file, which in this case is called AdventureWorksWebApp.ssmssln and contains a list of all the projects in the solution and their location. The second file is the solution options file, called Adventure Works WebApp. sqlsuo. The solution options file contains information about the options that customize the development environment.
The solution folder will contain a project folder for every project added to the solution. The project folder contains all the project files including the project definition file. The project definition file is a XML file with the .ssmssqlproj extension. In the previous example this file is called ProductCatalog.ssmssqlproj. The project definition file contains the connection information as well as metadata about the remaining files in the project.
A rather annoying aspect of the Solution Explorer is that new queries are not automatically added to the solution if they are not added from the Solution Explorer itself. For example, suppose that you have created a new project and then click on the New Query button at the top left of Management Studio. A new query window will open, but the query will not be part of your open project. To ensure that new queries are added to the project, you need to right-click on the Queries folder and select New Query.
As shown in Figure (IN The Below), the Properties window is linked to the Solution Explorer and simply displays the properties for the currently selected item in the Solution Explorer window. Editable properties will be bolded. If the Properties window is not visible, you can open it from the View menu or by pressing F4.
Multiple servers can be registered and connected to with Management Studio. This allows the DBA to manage multiple servers in a single environment. The Registered Servers window is not visible by default. To display it, click the View menu and choose Registered Servers, or press Ctrl+Alt+G. Right-clicking anywhere in the Registered Servers window will expose a context menu that allows for the addition of new server registrations. It also allows for the creation of server groups. If you have multiple servers in your organization, server groups can be very useful. For instance, server registrations can be segregated so that all the test and development servers are in one group and the production servers are in another, or servers could be grouped based on function or department. Instances of the database engine, analysis services, reporting services, integration services, and SQL Server Compact Edition can be registered in the Registered Servers window. Once registered, the Registered Servers window provides the ability to manage the associated services or quickly connect Object Explorer to that server instance, as shown in Figure ((IN The Below)).
When working with very large scripts in the Code Editor, it is very useful to be able mark a location in the script. Bookmarks enable this functionality. The Bookmark window is made visible with the View menu and is enabled when working with any SQL Server script type. Any number of bookmarks can be created and then renamed with an intuitive name that identifies the bookmark. If the script is part of a solution, the bookmarks are saved with the solution in the solution options file. Bookmarks can be organized in to multiple folders for each project. Figure ((IN The Below)) shows the bookmark window open and a bookmark called Catch Block.
The toolbox contains maintenance plan tasks that can be dragged to the Maintenance Plan designer used by database administrators to create maintenance plans for routine scheduled maintenance, such as database backups and index maintenance operations.
Object Explorer Details
The Object Explorer Details pane is displayed by default and is a great deal like the list or detail view in Windows Explorer.
Your default web browser can be launched from within SQL Server Management Studio to minimize the number of open applications and to allow direct access to Internet content from within the Management Studio application. This is done from the View menu by choosing Web Browser.
The Template Explorer contains hundreds of SQL Server, Analysis Server, and SQL Mobile scripts. Each script is grouped into folders based on their function. The template scripts can be opened by being dragged on to an open query window. If no query window is open, the templates can be opened through a double mouse click, the Edit menu, or right-click context menu, all of which cause a new query window to open. Once a template is open in the Query Editor, the parameters of the template can be replaced with actual values by launching the Specify Values For Template Parameters dialog. This dialog can be launched from the SQL Editor toolbar or through the Query menu.
The Error List window is available only with SQL Server 2008 and contains a list of all errors detected in the open query window (see Figure (IN The Below)).
SQL Server Management Studio provides several different toolbars that expose features from various menus. Each toolbar can be displayed or hidden in the typical Windows toolbar method of selecting View ■=> Toolbars and choosing which toolbars to display. In addition, you can customize the toolbars to display only the buttons most often used by right-clicking the toolbar and choosing Customize.
Database Diagram Toolbar
The Database Diagram toolbar exposes a great deal of functionality for use on database diagrams. The toolbar is not used just for diagramming the database, but also for modifying or creating database objects from within the diagram interface. The toolbar is not displayed by default when working with database diagrams. It must be selected through the View ■=> Toolbars menu. In addition, a menu item labeled Database Diagram will appear on the menu bar when a database diagram is open. The menu has the same options as the toolbar. The Database Diagram toolbar features are described in the following table.
The Help toolbar provides a very easy and convenient mechanism for consulting online help articles while using Management Studio.
Query Designer Toolbar
The Query Designer toolbar is enabled when a table is opened with SQL Server 2005's Object Explorer or when the top 200 rows are opened for edit in SQL Server 2008.
To open a table in SQL Server 2005:
To open the top 200 rows for edit in SQL Server 2008:
If the Query Designer toolbar was not visible, it will be when the table is opened. If it was visible, it will now be enabled. Although opening a table in a test and development environment is probably acceptable, opening a table in this manner in a production environment is not recommended. Opening a table with Object Explorer dumps the data from the table into a memory object called an update able scrollable cursor. What this means is that while the table data is exposed in the results window, any change to the displayed data is made to the underlying data in the table. This can be very dangerous. Displaying the entire contents of the table also can consume a great deal of server resources if the table is large. As a general rule, if the entire contents of a table need to be exposed the best way is to write a query with no filters, such as:
SELECT * FROM Person.Address
This exposes the same information as opening the table but does not populate an updateable cursor, so the results are read-only. If the data in that table needs to be updated, an update command (which is covered in Chapter Transactions) is more appropriate than modifying the data in an open table results window. Because the ability to open a large table is generally not a good idea, SQL Server 2008 replaced that ability with the new Edit Top 200 Rows feature.
The following table describes the Query Designer toolbar features.
Source Control Toolbar
The Source Control toolbar is enabled when working with scripts and a Source Control plug-in has been configured, such as Visual Source Safe or Visual Studio Team System. The following table describes the toolbar options.
SQL Editor Toolbar
The SQL Editor toolbar becomes visible, or is enabled if already visible, when a new SQL query window is opened. The toolbar provides the most common features used by SQL programmers and DBAs. The supported features are described in the following table.
SQL Compact Edition Toolbar
The SQL Compact Edition toolbar becomes visible, or is enabled if already visible, when a new SQL Compact Edition query window is opened. The tools on the toolbar are a subset of the SQL Editor tools showing only those that are applicable for SQL Compact Edition queries.
SQL Server Analysis Services Editor Toolbar
The Analysis Services toolbar also becomes visible, or is enabled if already visible, when a new analysis query is opened or created. The tools on this toolbar are also a subset of the SQL Editor tools, but contain only those tools applicable to Analysis Services queries (DMX, MDX, XMLA).
The Standard toolbar provides buttons to execute the most common actions, such as opening and saving files. It also provides buttons that will launch new queries and expose different tool windows.
Table Designer Toolbar
The Table Designer toolbar becomes visible, or is enabled if already visible, when either a new table is created using Table Designer or an existing table is modified using the Table Designer. The Table Designer is launched by right-clicking on the table node in the Object Explorer and choosing New Table from the context menu, or by right-clicking on an existing table in the table node of Object Explorer and choosing Design. The Table Designer toolbar has buttons that enable the creation or deletion of primary keys on a table, as well as launching dialogs for creating and managing indexes, constraints, and table relationships.
Text Editor Toolbar
As previously described, Management Studio supports a few different languages, with each language having its own specific toolbar. The Text Editor toolbar offers additional shortcuts to those provided in the other language-specific editors. The features are described in the following table.
View Designer Toolbar
The View Designer toolbar is almost exactly like the Query Designer toolbar, with the exception of being limited to writing SELECT queries. In addition, queries written with the View Designer are saved as views and not just as query scripts.
SQL Server Management Studio Configuration
Management Studio's look and feel can be customized through the Tools ■=> Options menu. The Options dialog, shown in Figure (See In Figure Below), enables you to customize the Management Studio IDE. The configuration options are divided into the following seven areas.
The Environment section is broken down into the following four sub-areas:
The Text Editor section, which enables you to customize various text editors, is divided into the following four sub-areas:
The Query Execution section provides configuration options for how queries are executed as well as connection properties and timeout settings. The Query Execution section is divided into two sub-areas:
The Query Results section provides configuration options for how query results are formatted and is also divided into the same two sub-areas as the Query Execution settings.
SQL Server Object Explorer
SQL Server 2008 provides an additional section that enables you to configure the amount of records displayed by default when querying the audit log, as well as the amount of records returned in SQL Server 2008's new context menu choices of Edit Top <n> Rows and Select Top <n> Rows (see Figure (IN The Below)). The new choices are shown when right-clicking a table in Object Explorer, as shown in Figure (IN The Below). This section also provides the ability to configure scripting defaults. The scripting menu is also available in SQL Server 2005.
The Designers section provides configuration options for the graphical designers used in Management Studio. The Designers section is divided into three sub-areas:
The Source Control configuration section allows for the integration of a source control plug-in, such as Visual Source Safe or Visual Studio Team System.
T-SQL Related Interview Questions
|PL/SQL Interview Questions||MSBI Interview Questions|
|SQL Database Interview Questions||MySQL Interview Questions|
|DB2 Using SQL Interview Questions||SQL DBA Interview Questions|
|SQL Interview Questions||PL/SQL and Informatica Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||MYSQL DBA Interview Questions|
|PL/I Interview Questions||DB2 SQL Programming Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||NoSQL Interview Questions|
|SQL Server Architect Interview Questions|
T-SQL Related Practice Tests
|PL/SQL Practice Tests||MSBI Practice Tests|
|SQL Database Practice Tests||MySQL Practice Tests|
|DB2 Using SQL Practice Tests||SQL DBA Practice Tests|
|SQL Practice Tests||SSIS(SQL Server Integration Services) Practice Tests|
|MYSQL DBA Practice Tests||PL/I Practice Tests|
|DB2 SQL Programming Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.