Here’s a quick post about one of my favorite pages on the Microsoft Support site. The page explains how to retrieve your SQL Server version, and also mentions the different version numbers of all major releases and service packs.
One of my favorite statements since many years is the following:
Here’s the output that it produces on my laptop running Windows 7, while connected to my R2 server:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
It not only retrieves the version, edition and CPU architecture of SQL Server, on top of that it also shows you the operating system version on which it is running!
The statement is easy to remember and gives you all the details that you need. Well, that depends on the situation of course. In one of my previous jobs I was, as developer, last-line support for an application that was used worldwide. Which means that now and then I had people on the phone that had “some vague issue” with our application. And even though I speak four languages (more or less), I can assure you that getting details through a phone line can be a tough job. I remember one case where I had to spend half an hour to get the person on the other side find the Start button. Yes, that stupid thing on the bottom left, the thing that you need all the time as a regular Windows user. And no, they we’re not on Windows 3.x anymore! In those times, it even had the letters S T A R and T all over it! But alas, it remained impossible to find. Then I switched to finding the clock. Guess what: wasn’t in the right bottom corner either! Further during the conversation I tried all other corners, also tried to explain that the status bar may have been set to automatically hide. Oh well, at a certain point they’d suddenly found it! I don’t know where, maybe on another PC, at that point I didn’t care anymore, I just wanted them to open up the Management Studio, er, Query Analyzer more likely.
Anyway, at times like that, you’re glad that easy SQL commands still exist.
The statement above is not the only possibility to get details about the SQL Server version. Another method is through the SERVERPROPERTY function. Here’s a sample statement (from the Books Online) to retrieve version information:
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition;
Try getting that to the other side of the phone line! And no, email was not an option either…
Here’s the statement’s output:
Not only is the statement more difficult to remember, the output is less readable too. For instance you need to know that EngineEdition 3 means Enterprise Edition.
Nevertheless, it is a useful function with more possibilities than @@version. Check out the BOL for all the details and available properties.
Let’s get back on topic now. I was going to mention a certain page on the Microsoft Support site. Here it is: How to identify your SQL Server version and edition.
I think this page is a great reference in terms of versions. If someone wants to know what version of SQL Server he’s running and doesn’t know the version numbers, this is the place to be! I just whish that Microsoft would keep it more up-to-date. At this moment it’s not even mentioning SQL Server 2008 R2, while that was released about half a year ago. No sign of SP2 for SQL Server 2008 either. I’m going to provide feedback through the textbox at the bottom of the page with this exact statement. Hopefully that has some effect.
In case you are looking for those latest version numbers, here they are:
|SQL Server 2008 SP2||10.00.4000.00|
|SQL Server 2008 R2 RTM||10.50.1600.1|