Figure 2-1. SQL Server Management Studio Connect to Server dialog box without expanded options

• Server Type: For the purposes of the examples in this book, leave the server type as Database Engine. The other options are other types of servers that are available for connection.
• Server Name: The second combobox lists the SQL Server installations that the Connect to Server dialog box can find, or knows about. In the dialog box in Figure 2-1, you will see the name of the computer that the local install is on. If you open the Server Name combobox, you can search for more servers locally or over a network connection using <Browse for more...>. The first time you connect to SQL Server, you will need to browse for or type in the details. You will have already found the server in Chapter 1.
• Authentication: The final combobox specifies which type of connection you want to use. You installed SQL Server with Windows authentication in Chapter 1; therefore, this is the option to use. If you had installed SQL Server with Mixed mode or if in the future you wish to connect by this method, then you could change this option to SQL Server authentication, which would enable the next two text boxes and allow you to enter a username and password.

Figure 2-2. SQL Server Management Studio connection properties

• Connect to Database: This combobox provides a list of databases based on the server and login details in the Login tab. Clicking the down button for this combobox allows you to browse for and select a database on the server to which you want to connect. Only the databases that the Windows account or SQL Server login can connect to will populate this list. Also, any error in the login details will cause an error message to be displayed here instead of listing databases.
• Network: This area details how you want this connection to be made with SQL Server. At the moment, you don't need to change the current settings.
• Connection: This area deals with connection timeouts. The first item, Connection Time-out, defines how long the connection should wait before returning an error. For local installs and even most network installs, a setting of 15 seconds should be more than enough. The only situation that may require you to increase this setting is if you were connecting over a WAN or to a SQL Server installation at an ISP and that connection was slow. A connection from New York to Moscow could exceed this value, for example. The second option, Execution Time-out, details the timeout value for any T-SQL code that you execute. A setting of 0 means that there is no timeout; there should be few, if any, occasions when you would want to change this setting.

  Note Any subsequent connection parameters would appear in the third tab. This will be empty for this book.

Figure 2-3. SQL Server Management Studio graphical interface for SQL Server

Figure 2-4. A list of registered servers

Figure 2-5. The New Server Registration dialog box

Figure 2-6. Object Explorer nodes

• Databases: Holds the system and user databases within the SQL Server you are connected to
• Security: Details the list of SQL Server logins that can connect to SQL Server; you will see more on this in Chapter 4.
• Server Objects: Details objects such as backup devices and provides a list of linked servers, where one server is connected to another remote server
• Replication: Shows the details involving data replication from a database on this server to another database (on this or another server) or vice versa
• Management: Details maintenance plans, policy management, data collection, and Database Mail setup, which you will learn more about in Chapter 7, and provides a log of informational and error messages that can be very useful when troubleshooting SQL Server
• SQL Server Agent: Builds and runs tasks within SQL Server at certain times, with details of successes or failures that can be sent to pagers, e-mail, or operators defined within SQL Server; the running of these jobs and the notifications of these failures or successes are dealt with by SQL Server Agent, and the details are found in this node. You will look at this more in Chapter 7, where you will schedule backup jobs.

Figure 2-7. Summary page

Figure 2-8. The View menu options

• Utility Explorer: Enables a central point for monitoring disparate servers; this is ideal for database administrators who monitor several servers within their organization that could be in one location, several locations, and even with external clients. From this one explorer, you can monitor how the servers are performing, if they have issues, and how the servers are performing against policies defined as part of the monitoring process.
• Template Explorer: Provides access to code templates; in the examples in this book, you will be building objects using T-SQL. Rather than starting from scratch, you can use code templates that contain the basic code to create these objects.
• Solution Explorer: Displays solutions, which are convenient groupings of objects, T-SQL, or special programs called stored procedures, among other items; a solution is a collection of objects such as tables, stored procedures, queries, and so on, that are collected together to address a specific business or technical problem. If you use Visual Studio, files are collected into projects, and one or more projects create the overall solution. I don't use the solution method of developing projects for SQL Server, as I find that good use of development and source control utilities such as Visual SourceSafe removes the need for that level of thought and complexity.
• Bookmark Window: Allows you to create bookmarks, which you place into various locations in your code to allow you to jump quickly to those locations
• Call Hierarchy: As your application expands, you will find that you will have code that executes code in a different program and so on. This is a call hierarchy, and it is sometimes advantageous to see that hierarchy to debug a problem that you are experiencing.
• Error List: Shows errors found in the code you have entered in the editor; this can be compilation or syntax errors.
• Output: Unlike the Error List, this window is used to print output while debugging code or status messages from some commands. If output is being displayed from more than one location, then you can filter the output to be displayed.
• Task List: If you need a method to keep a list of work items, or tasks, then you can use this area to keep a note and to track them. The task list is ideal if you are debugging your code and you want take notes of areas that need working on, or if you wish to make a task list of items you need to complete to finish developing your application.
• Toolbox: Holds a list of objects that are database maintenance tasks, and details where these tasks can be altered
• Find Results: If you need to search via a number of files for a particular phrase, then the results will be kept in the Find Results window.
• Other Windows: Allows you to access other windows generated when running T-SQL from Query Editor, which may hold error messages or results from queries
• Toolbars: Brings up toolbars such as for Query Editor, diagramming the database, and integration with Visual SourceSafe for source control, if they are not opened by default among many others
• Full Screen: Removes title bars and explorer windows, and then maximizes SSMS to show as much of the main pages as possible
• Navigate Backward/Forward: Allows you to navigate through the different Query Editor windows by either navigating forward or backward through the tabs
• Next Task/Previous Task: Once you have created tasks using the Task window described earlier, you can navigate through the list of tasks using this menu option. You may find using the arrow keys easier and quicker.
• Properties Window: Displays the set of properties for each object; you will see this from time to time throughout the chapters of the book.

Figure 2-9. The Debug menu options

• Windows: There are three window options possible from this menu option.
• Start Debugging: This starts the debugging process and will execute the first line of code.
• Stop Debugging: Allows you to stop debugging at the line of code you are on; it is not possible to restart from that point, and you would have to start over again.
• Step Into: When stepping through your code and reaching a stored procedure, you can step into that stored procedure and step through that code.
• Step Over: If you do not wish to step into the stored procedure, you can execute the whole code set and then step to the next line in your code.
• Toggle Breakpoint: Switches a breakpoint from being set to not being set, or vice versa
• New Breakpoint: Creates a new breakpoint at the line of code; the cursor is on. A breakpoint, where the execution of the code will pause to allow you to complete other actions.
• Delete All Breakpoints: This selection is a good option if you are debugging code and you are happy with the code from the point you are at and do not wish to break any further. Deleting the breakpoints will allow this to happen.
• Clear All DataTips: A datatip is the ability to watch the contents of a variable as you debug your code. This menu option will remove all tips that you have stored.
• Export DataTips: Any datatips created can be saved and exported, allowing you to share datatips.
• Import DataTips: Any exported datatips can be imported.

Figure 2-10. The Tools menu options

• SQL Server Profiler: There will come a time when you'll wish to monitor SQL Server's performance. This tool will monitor and log events, running code, and so on that you have informed it to check when they happen within SQL Server.
• Database Engine Tuning Advisor: It is possible to take a workload of data and process it through your solution. This advisor can suggest ways to improve the performance of this process.
• Code Snippets Manager: It is possible to create useful short sections of code that will be used more than once. Unlike templates that you will see in several chapters where a template of code for a whole action such as creating a new table, code snippets are at most a few lines of code and tend to be organizational or project-specific. An example of a code snippet would be a code snippet that performs logging of information as code executes. Another is for error logging where there is specific code to log any errors that occur. You will see how to test for errors in Chapters 13 and 14.
• Options: This option lets you access different options you can use to set up your SSMS as you like. I will take a look at each of these options in the next section.

Figure 2-11. Main toolbar

• New Query: In the next section, you will see how you can write code to add objects, work with data, and so on. Clicking the New Query button opens up a new query window, which allows you to do this using the connection already made with SQL Server.
• Database Engine Query: Similar to the New Query button, the New Database Engine Query button also creates a new query window. However, it gives you the option of having a different connection to SQL Server through which to run your code. This is a good way of testing code with a different connection to ensure that you cannot see data that should be secure, such as wages, via that connection.

  Caution The Database Engine Query also allows you to multitask with two different databases. This can be dangerous, but with care and the right circumstances it can be very useful, as the only ways to know which connection a query is running against are by using the window bar of SSMS or by hovering over the particular tab. If you are working with different databases or instances, especially if you are connecting to a development and production instance of a similar database, then you should open another instance of SSMS for safety. Although the method of knowing which connection is the same via the toolbar or the tab, Object Explorer on the left is a quick reminder of which connection your SSMS is dealing with. By having different SSMS's you should see which instance you are connected to.

• Analysis Services MDX/DMX/XMLA Query: Data within a specialized database known as an analysis database allow you to interrogate the data and analyze the information contained within. The three New Analysis Service Query buttons allow you to build different types of analysis queries. I include this information here only for your reference, as analysis databases fall outside the scope of this book.
• Open/Save/Print: As with every other Windows-based product, it is possible to open and save files. The Open button allows you to search for a T-SQL file. The next button changes its function depending on what you are doing, but in the main, the Single Save button gives you the option to save the details of the window that is active in the main documents area of SSMS. The Print button will print the query details.
• Navigate Backward/Forward: These two buttons allow you to move backward and forward through your query editors and to select the query editor you wish to use. As you work on your application, you will find that you have several query editors open achieving different tasks from querying the data multiple different ways, building T-SQL code. These buttons allow you to switch between the editors. The buttons work using Ctrl+Shift with either plus (+) or minus (-) and are very similar to using Ctrl+Tab when you switch between applications in Windows. These buttons work as the navigate menu options described in the View menu option.

  Note A query editor is a tabbed document, sometimes known as a multiple document interface (MDI), and will be used extensively throughout the book to write code against SQL Server.

• Activity Monitor: This is a useful tool that allows you to monitor your SQL Server and investigate some performance issues within your server or your database. As a developer, optimizing your code so that it performs as efficiently as possible is crucial to a successful database application.
• Find in files: This final button allows you to search for a particular piece of text in any files that form part of the SQL Server solution regardless of whether they are open in a Query Editor pane.