Which version of SQL Server am I running?

Question

I am looking for a SQL Query which will help me differentiate between the various versions of SQL Server:

2000/2005/2008

Development/Standard/EE.

Answer 1

SELECT @@version

Answer 2

Will

Select @@version

suffice?

Answer 3

Perhaps this will help you http://support.microsoft.com/kb/321185

Answer 4

Select @@Version

On my system, this returns:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (x64) Mar 29 2009 10.11.52 Copyright (c) 1988-2008 Microsoft Corporate Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

Answer 5

I tend to use this short script:

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Patch Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

which gives me an output something like:

Product Version Patch Level Product Edition             CLR Version Default Collation Instance LCID Server Name
10.0.2531.0     SP1         Developer Edition (64-bit) v2.0.50727 Latin1_General_CI_AS NULL 1033 XYZABC

Answer 6

We will use simple technique to detect the server type using the serverproperty('ProductVersion') for a list of options visit SERVERPROPERTY (Transact-SQL). So lets see what are the pros and cons

DECLARE @sql2005Code varchar(255)             

SET @sql2005Code = 'print ''SQL 2005 Code'' '

DECLARE @ver nvarchar(128)

SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)

SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

        -- SQL 2000

IF ( @ver = '8' ) BEGIN

        print 'SQL 2000'

END

        -- SQL 2005

ELSE IF ( @ver = '9' )BEGIN

  exec (@sql2005Code)

END

Here is my full entry on this hope this helps you. Detect SQL server version

Answer 7

The shortest SQL query is xp_msver

But SELECT @@VERSION does what you want