If you’ve used SQL Server for a while, you’re probably familiar with the @@version function. @@ variables are system variables that are automatically populated by SQL Server. Selecting the @@version function in a query window produces the following results on my system.

SELECT @@VERSION;

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 6.1 <X86> (Build 7600: ) (Hypervisor)

In the results, you can easily see that I’m running SQL Server 2008 R2  Developer Edition on an Intel X86 processor. Notice that the results also contain operating system information as well. In this case, it shows that SQL Server is installed on a Windows NT 6.1 machine.

This can cause confusion for many who are unfamiliar with the @@version variable. Frequently, the service pack of the operating system is placed toward the end of the text. It’s easy to assume that this is the service pack level for the SQL Server, after all we are asking SQL Server for the information. That’s not the case. It’s the operating system system pack level.

The ServerProperty Function

To determine SQL Server’s service pack level, use the built-in SERVERPROPERTY function with appropriate parameters – EDITION, PRODUCTLEVEL, and PRODUCTVERSION.

SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion;

In another post, I’ve shown how the SERVERPROPERTY function can be used to find the location of the SQL Server ErrorLog file.

Not familiar with terms like RTM, Service Pack, GDR, and CU? I’ve written about the terms in another post.

Enjoy.

Filed under: Administration, SQLServerPedia Syndication, T-SQL