What's the SQL Server version?

  • Often, you will need to check for SQL Server versions and service packs. Some of the features that you are using are only available from a service pack onward.
  • Microsoft provides the function serverproperty to query for that kind of information.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

ServerProperty

  1. SELECT CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(17)) AS 'Computer physical name',
  2.        CAST(SERVERPROPERTY('ServerName') AS VARCHAR(17)) AS 'Server name',
  3.        CAST(SERVERPROPERTY('MachineName') AS VARCHAR(17)) AS 'Machine name',
  4.        CAST(SERVERPROPERTY('Edition') AS VARCHAR(25)) AS 'Server edition',
  5.        CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(17)) AS 'Instance name',
  6.        CAST(SERVERPROPERTY('LicenseType') AS VARCHAR(15)) AS 'License type',
  7.        CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(15)) AS 'Version',
  8.        CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(15)) AS 'Service pack';
  9. go
Computer physical name Server name       Machine name      Server edition            Instance name     License type    Version         Service pack
---------------------- ----------------- ----------------- ------------------------- ----------------- --------------- --------------- ---------------
SQLSERVER              SQLSERVER         SQLSERVER         Standard Edition          NULL              DISABLED        9.00.3050.00    SP2

(1 row(s) affected)

Starting with SQL Server 2000, Microsoft changed the naming convention:

  • Version 8.xx.xxxx.xx is SQL Server 2000
  • Version 9.xx.xxxx.xx is SQL Server 2005
  • Version 10.xx.xxxx.xx is SQL Server 2008