The traditional way of working out which version of SQL Server is running on a specific server is to run the following query : This returns the version, processor architecture, build date of SQL Server and the operating system it is running on. Here are a few examples of the result of this query when run on different servers :

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Microsoft SQL Server 2000 - 8.00.2282 (Intel X86) Dec 30 2008 02:22:41 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 6.0 (Build 6001: Service Pack 1)

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

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 7601: Service Pack 1)

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

One thing you might have noticed is that with earlier versions of SQL Server the service pack level is not explicitly identified. To determine this a lookup would be needed on Microsoft's website to find the service pack (and cumulative update) associated with the particular build. Also bear in mind that the service pack listed at the end of the result is actually the service pack of the operating system, not SQL Server !

Another possible issue is that the information is returned as a single string. However the information can be obtained in a more structured way using the SERVERPROPERTY function with various arguments as follows (this only works with SQL 2000 and later) :

The result of this query (on my server) is :


The SERVERPROPERTY function can also be used with different arguments to obtain further information such as default collation, and the edition (express, standard, workgroup, enterprise etc). The various arguments are documented in books online : http://msdn.microsoft.com/en-us/library/ms174396.aspx

Which method you use is really down to personal preference. Microsoft haven’t announced the deprecation of @@VERSION (as of SQL Server 2012), so it’s going to be around for at least another two versions. For me, if I’m doing a quick check I tend to use @@VERSION because it is easy to remember the syntax, but if I’m doing an audit across several SQL Servers then I use the SERVERPROPERTY method as this provides the information in a more structured way.