Database Fundamentals #2: SQL Server Management Studio

The best way to learn any software is to start using it. There are a bunch of software tools in the SQL Server toolbox, but the biggest and most important is SQL Server Management Studio (SSMS). SSMS is where you’ll spend most of your time when you start to work with SQL Server. It provides a very large series of graphical user interfaces for creating databases, setting up security, reading data out of the database, and all sorts of other things within your SQL Server instances, the databases stored there, and all the stuff inside those databases.

It also supplies you with an interface to the basic scripting language of SQL Server, through which you can do almost anything to the server. The scripting language is called Transact Structured Query Language, or Transact-SQL, or just T-SQL for short. This is your chance to finally start to explore the tool that’s going to enable you to manage your own databases and servers.

Connecting to SQL Server

To get started with SQL Server after you have installed it (See Database Fundamentals #1 for an overview on installing SQL Server), you open SQL Server Management Studio from the Start menu on your computer (and you can install SQL Server Management Studio completely on its own). When it opens, you’ll first be presented with a connection window. Depending on how you installed SQL Server, you have choices and decisions to make at this point. The installation may have been a default install, which means that the server name is all you need to connect to SQL Server. If not, then an instance name may have been specified, with a backslash between the server name and the instance name.

In addition to the server and/or instance name, you also have to worry about how security was configured. Most installations use Windows Integrated Security. This means that your Windows login gives you access to SQL Server. In some places, you'll be given a username and password. This is known as a SQL Server Login. When working with Azure, you could also use certificates or multi-factor authorization. Those are outside the beginning level scope we’re addressing here.

Most of the time, your connection to SQL Server will look something like this:

After you configure the connection correctly, you can click Connect. This will connect you to SSMS directly. If you have been following along, you’re now connected to your SQL Server instance. You’re seeing a bunch of things that are new to you. I’ll run though through the basic functionalities of the various windows in preparation for all the work you’ll be doing with this software in the future.

Working With the Object Explorer

The default configuration of SSMS has a menu bar across the top of the screen. The menu bar has a list of menu options that can change depending on what other windows or processes you have open. The menus are one way to access commands and functions within SQL Server. Below the menu bar is a toolbar. The tool bar, like the menu’s, can change depending on what else you have open. The buttons on the tool bars are configurable. These buttons represent yet another way to access commands and functions within SQL Server. The remaining window is then split into two parts. On the left side of the screen is the Object Explorer window. On the right side, when you first open the server on a default install, there won’t be anything visible. Across the bottom of the window is a status and information bar that shows different information depending on what you have selected or open in the windows above.

SSMS is built out of Microsoft’s programming environment, Visual Studio, and shares a lot of the same functionality. Windows are very customizable. You can have the windows float independently of each other or you can mount them in various locations on the screen, creating tabbed environments if that’s what you prefer. You can also pin and unpin the windows causing them to float in and out of view as your mouse pointer gets close to them by clicking on the push pin icon on the window. Try clicking on the push pin visible in the Object Explorer window. The window should disappear, leaving a little tab on the left side of your screen that is labeled Object Explorer. If you move your mouse pointer over that tab and leave it there in what is called a hover, the Object Explorer window will pop back out. Click on the pushpin icon again and the Object Explorer window will be docked back on the left side of the screen as it was before.

You don’t need to know about every possible combination of configurations now. It’s enough to know that you can get the system set up the way you would most like to see it in a manner that works best for you. You’ll be spending a lot of time working with the Object Explorer so it makes sense to become familiar with it first. SSMS will allow you to connect to multiple SQL Server instances. As you connect to each instance, you’ll see them listed in the Object Explorer. Each server will have a little + sign next to it that opens the tree for that server. The tree is a list of the different types of objects that you can begin to manage from SSMS. When you connect to a server, the server name is right at the top of the Object Explorer windows. Below that are the different parts of the tree, as you can see below. My server is a default VM name, WIN-MA9H6M2E1QF. My SQL Server instance is named DOJO after a martial arts training all. You can see them here:

Navigating Within the Object Explorer

Learning the behavior of the SSMS tool is going to be an ongoing process because of all the options available to you. Get started by clicking on the + sign next to the folder labeled Databases. This opens the list of databases for your server. Since there are no databases installed by default and you probably haven’t created any, you should only see two more folders on the tree: System Databases and Database Snapshots. Click on the + sign next to System Databases and you’ll see some of the databases that SQL Server uses to manage its own operations: master, model msdb, and tempdb. Don’t worry about what these do yet; you really should not even consider changing options or objects inside these databases. But you can look at them to see what is stored there. Click the - sign that is now next to the System Databases label. This closes that list. Click the - sign next to the Databases label and you’ll close that list as well. This is how all the various objects can be accessed through the Object Explorer. There are other ways to manipulate objects in the Object Explorer as well.

Right click with your mouse on the Databases label inside Object Explorer. This action opens a context window that provides other functionality for you. The menu choices that show an ellipsis (…) will open other windows. The menu choices that show a black arrow pointing to the right will open other menus. You can see the context menu here:

The actions outlined in the menu are probably self-explanatory like New Database, or cryptic like Start PowerShell. You’ll be using a number of these functions throughout the book and now you have a better idea how to track them down.

Across the top of the Object Explorer are a number of icons which allow you to perform other actions. On the left is a drop-down menu that lets you connect to another instance of SQL Server, labeled as Database Engine, Analysis Services, Integration Services,or Reporting Services. Next to this is a row of icons. The first is for connecting to another instance of SQL Server. Next to that is an icon that will disconnect you from the server that is currently selected in the Object Explorer window. Try clicking on that icon. The tree in the window of the Object Explorer will go away. To get it back you’ll need to reconnect to your server, as you did before. But you can open the connection window using one of the two methods explained above.

SQL Server Management Studio Menus and Shortcuts

Take a look at the top of the screen where the menu bar is located. There are a number of options here, but the one that should concern you the most at the moment is all the way on the right: Help. Click on the Help menu and you’ll see a number of different choices. The most important one at the moment is right at the top, View Help. Click on this menu choice. If this is the first time you’ve tried to open help, you’ll be presented with a choice: Do you want to connect to online help or not? Online help is more up-to-date than any help files installed on your system. This means that you’ll get much more accurate responses, but it is slower and a little more difficult to manipulate than the local help file, which is called Books Online. Production servers in some companies can’t connect to the Internet either, so this decision depends on your situation.

The most important thing to learn about SQL Server is that there are always a lot of ways to get things done. Getting to the Help window is important. Even more important is getting to the right kind of help. You can click on the menu as we did above, but you also have the ability to open help in the context of the window you’re currently working in. Any time you’d like to see help for the window you’re on, press the F1 key on your keyboard. That will open the Help screen.

The menus across the top of the screen can change depending on what you’re doing and which windows are open in the screen below. Don’t be surprised when you see new menu items sometimes appearing as you work through the examples in this series. The menus are taken from Visual Studio so they may not always seem applicable to an environment for managing databases, but you’re given a lot of functionality through these menus. Some of it duplicates the functionality you’ve already seen. For example, clicking on the File menu choice will show a number of menu items. At the top is the option to Connect Object Explorer with an ellipsis. This will open the connection window that you’ve already seen.

Information in Object Explorer Details

Click on the View menu. This shows you a very large number of things that you can bring up for viewing or modification on your screen. The only one that concerns you at the moment is the second one down, Object Explorer Details. Click on that and you should see a second window open to the right of your Object Explorer window. Depending on what is currently selected in the Object Explorer, you may see things displayed in the Object Explorer Details window. You’ll notice that this window is slightly different at the very top. It has a tab. This larger area within SSMS is set up for tabbed viewing by default. Like in the other windows, you can customize this. There are a number of different types of windows that will be displayed here and they will all remain open until you close them, arrayed in a series of tabs across the top of the screen.

To see Object Explorer Details at work, click on the server and instance name at the top of your Object Explorer window. This will bring up a list of objects in the Object Explorer Details window that looks basically identical to the tree list in Object Explorer. You can select these objects in this window, but unless you double click them, you won’t open any of the folders. Don’t explore just yet. If you have something selected in the Object Explorer detail window, click in the white space to the right or below the list of objects. Look down near the bottom of the window. You should see a gray box stretching across the whole Object Explorer Detail window that looks something like below. With the server and instance selected in Object Explorer and no other objects selected in Object Explorer Details, this gray box should show you information about your server and instance.

Scrolling around, you can see various pieces of information about your server such as the current version of SQL Server that is installed there, the number of databases, which is probably four (mine is 23), and other information such as the Max Server Memory. All this information is available to you, and more, as you select different objects in the Object Explorer window. Try selecting a few and see how the information on display in the Object Explorer Details window changes.

As you can see, there is a lot of information available to you in SQL Server in different forms. Learning all the different ways you can see this information and knowing what it means are just part of what you need to learn in order to get good at manipulating the server.

There are a number of other windows and views into the data that will be explored in the rest of this series. You now have the basics to enable you to begin to explore your system.

Conclusion

SQL Server Management Studio (SSMS) is where you will spend most of your time while working with SQL Server, so getting introduced to it as early as possible is a great way to start learning. When you work with your own systems, you’ll be better equipped to understand why they store data in the manner that they do because you have an acquaintance with both relational models and dimensional models and their uses.