It’s more common than you would expect to have SQL Server installed on your machine without knowing the exact version or edition. With tools and applications installing SQL Server behind the scenes, you may have unknowingly installed SQL Server. In this post, I’ll try to quickly explain some easy ways to investigate this question. This will include:

  • Using the Programs and Features control panel tool.
  • Understanding and viewing SQL Server instances.
  • SELECT @@VERSION


If you’d rather watch the techniques, here is a quick video:

Control Panel

One well-known way of investigating installed versions is the Programs and Features control panel tool (which used to be Add/Remove Programs). In Windows 7, an easy way to get there is to type “Programs and Features” in the search box on the start menu like this:

Programs Andd Features on the Start Menu

Your intent here is not necessarily to uninstall anything (although you could). Instead look through the list of installed programs for programs named “Microsoft SQL Server”. For example, in my list I see around ten items that start “Microsoft SQL Server 2008 R2″. Note that it is possible to have multiple versions of SQL Server installled at the same time, so you could also see “Microsoft SQL Server 2005″ in the same list.

SQL Server Instances

Even though the control panel is easy and well-known, it doesn’t really tell the whole story well. In order to really understand what versions of SQL Server are installed, you have to understand SQL Server Instances. Each instance of SQL Server operates independently and contains its own collection of databases and settings. There are two types of instances:

Default Instance a single instance named MSSQLSERVER. You connect to this instance using only the server name (ex: localhost) 
 Named Instance one or more instances. You connect to these instances uing the server name + instance name (ex: localhost\SQLEXPRESS)

It’s possible have only one instance installed on your machine. But it’s also possible to have many instances. And each of those instances could be a different version of SQL Server!

To understand your scenario, use the SQL Server Configuration Manager tool that installs with both SQL Server 2005 and 2008. On the start menu, expand the “Microsoft SQL Server [YOURVERSION]” menu, and then expand the “Configuration Tools” submenu. Launch the SQL Server Configuration Manager tool. Here is a screen shot to use as an example:

SQL Server Configuration Manager

In this example, I clicked on “SQL Server Services” in the left pane. The right pane shows the different services installed on my machine. The ones that we’re interested in are the ones that have “SQL Server” in the Service Type column. You’ll notice that there are two rows with this distinction that are highlighted here. The instance name is listed in parantheses in the first column. In this case there are two instances:

  • MSSQLSERVER: the default instance.
  • SQLEXPRESS: a named instance, which is the default name of the SQL Server Express instance.

Each instance shows that it is running in its own process. This is useful to know, but the important step is determining the version. To do this, right-click on the instance, and then click Properties. In the Properties dialog, select the Advanced tab. Scroll down to the Version row. Here is what it looks like for the MSSQLSERVER instance on my machine:

SQL Server Configuration Manager Advanced PropertiesThe version number for this instance is 10.50.1600.1. In order to translate this, you could search for that version number on the internet. There are several resources that have compiled a list of SQL Server version numbers for you. Here is one example:

SQL Server Version Database on SQLSecurity.com

Resources like this tell me that my default instance is SQL Server 2008 R2 RTM. The other great thing about a version database is that you might find that you’re not using the latest release. For example, I don’t have any of the post-RTM updates installed on my machine.

One final note is that you can see the “Stock Keeping Unit Name” which is a strange way of calling out the edition. In this case my default instance is the “Developer Edition” of SQL Server 2008 R2. The SQLEXPRESS instance on my machine says “Express Edition with Advanced Services”. Sometimes capabilities require a specific edition of SQL Server, so this is sometimes as important as the version information.

SELECT @@VERSION

I think looking at SQL Server Configuration Manager is the best way to truly understand what is on your machine. But there is an easier way if you already know your instances. You can run a simple query. Here’s how:

  1. On the Start Menu, go to All Programs, Microsoft SQL Server [YOURVERSION].
  2. Launch SQL Server Management Studio.
  3. When the Connect to Server dialog appears, log onto the SQL Server Instance of interest. For example, if you’re on the local machine, you can use localhost or localhost\SQLEXPRESS in the Server name text box. Instances were discussed in the previous section of this post. Make sure that the Server type is set to “Database Engine”.
  4. Click on the New Query button on the toolbar.
  5. In the query window, type the following query: SELECT @@VERSION
  6. Press the Execute button to run the query.
  7. Look at the version text in the Results window.

On my machine, for the LOCALHOST\SQLEXPRESS instance, this is the version information from this query:

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

This is perhaps the easiest way of detecting the version of whatever SQL Server instance you’re connected to. But you can see why I covered the control panel and instances first. If you don’t even know what instances are on your machine, you won’t know how to connect to them to run this query in SQL Server Management Studio! One final note, it is possible to have SQL Server installed on your machine without SQL Server Management Studio. If that is the case, you can still use the previous methods for determining your version, but you’d have to install the SQL Server Management Tools to run a query. This will most likely be covered in a later post.

Other resources on this topic: