How to tell what SQL Server versions you are running

By:   |   Updated: 2023-10-31   |   Comments (32)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Configurations


Problem

One issue as a MS SQL Server Database Administrator that I am often faced with is determining the version of the database engine installed on SQL Server. In this tip we look at how to find and read the version of SQL Server that is running the workload and also what service pack, hotfix, security update or cumulative update is installed.

Solution

There are a few ways to determine the version of SQL Server that is installed on Windows or Linux which we will cover in this tutorial.

How to tell what version of Microsoft SQL Server you are running?

  1. One simple way to do this is to run SELECT @@version
  2. Another option is to right click on the SQL Server instance name in SSMS and select Properties
  3. You can also use the SERVERPROPERTY function to get details about the version of SQL Server
  4. Check out all of the methods in this tip.

Method 1 - Find Microsoft SQL Server Version with SSMS

The first is by using the functionality in SQL Server Management Studio and right clicking on the instance name and selecting Properties. In the general section you will see information such as on the following screenshots. The "Product version" or "Version" gives you a number of the version that is installed. This would apply regardless of the edition such as Enterprise, Standard, Web, Express or Developer. As you can see with the SQL Server 2000 screenshot it also shows you the service pack that is installed, this is similar for SQL Server 6.5 and SQL Server 7.0 in Enterprise Manager.

SQL Server 2000

SQL Server 2000 version information

SQL Server 2005

SQL Server 2005 version information

SQL Server 2008 / 2008 R2 / 2012 / 2014 / 2016 / 2017 / 2019 / 2022

This will look similar for all of these versions of SQL Server.

SQL Server 2008 version information

Method 2 - Find Microsoft SQL Server Version with T-SQL

Another option is to run the T-SQL command to give you this information. As you can see the output from the different versions of SQL Server and Windows Server is pretty much the same. The one thing that you do not get when you do this is the Service Pack name like you do in SQL Server Management Studio (SSMS).

 SELECT @@VERSION

When this is T-SQL code or a stored procedure with this command is run it will give you information such as the build number and Windows Server operating system:

SQL Server 2000

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

SQL Server 2005

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

SQL Server 2008

Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)
Feb 4 2011 11:27:06
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

SQL Server 2008 R2

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.0 <X64> (Build 6002: Service Pack 2)

SQL Server 2012

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

SQL Server 2014

Microsoft SQL Server 2014 - 12.0.2254.0 (X64)
Jul 25 2014 18:52:51
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1) (Hypervisor)

SQL Server 2016

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
Apr 29 2016 23:23:58
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 <x64> (Build 14393: )

SQL Server 2017

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 16299: )

SQL Server 2019

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )

SQL Server 2022

Microsoft SQL Server 2022 (CTP2.0) - 16.0.600.9 (X64)
May 20 2022 13:29:42
Copyright (C) 2022 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Method 3 - Find SQL Server version by exe file details

Another option to determine the version of the SQL Server database install is to look at the version of the SQL Server files. This is an option if SQL Server is not running and you need to find the version.

Open Windows Explorer and navigate to the folder where SQL Server is installed such as "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn", for SQL Server 2008.

Find file sqlservr.exe and right click and select properties and a window will open like the one below. Here we can see that this file is version 10.0.2573.0.

SQL Server version from file

Method 4 - Find SQL Server version with SERVERPROPERTY

One of our readers, Ben Pomicter, also suggested that DBAs could use the SERVERPROPERTY function.

SELECT
  CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '16%' THEN 'SQL2022' 
     ELSE 'unknown'
  END AS MajorVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion

When run it returns results similar to the following:

SQL Server server property version

What do these SQL Server Build Numbers Mean?

So now that you have this number such as 9.00.1399.06 or 8.00.760 what do these build numbers even mean? The first digits refer to the version of SQL Server such as:

  • 8.0 for SQL Server 2000
  • 9.0 for SQL Server 2005
  • 10.0 for SQL Server 2008
  • 10.5 for SQL Server 2008 R2
  • 11.0 for SQL Server 2012
  • 12.0 for SQL Server 2014
  • 13.0 for SQL Server 2016
  • 14.0 for SQL Server 2017
  • 15.0 for SQL Server 2019
  • 16.0 for SQL Server 2022

Here are a couple of links to other articles that give you the details of what is installed for SQL Server 6.0, 6.5, 7.0, 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, 2019 and 2022 based on the version number that is returned.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-10-31

Comments For This Article




Thursday, April 27, 2023 - 12:37:08 PM - Greg Robidoux Back To Top (91136)
Thanks C Little. Using @@VERSION is one of the options listed above.

Thursday, April 27, 2023 - 11:55:17 AM - C Little Back To Top (91135)
Why not just run this?

SELECT @@SERVERNAME AS ServerName, @@VERSION AS SQLVersion

Wednesday, December 15, 2021 - 2:57:18 PM - TANIKA DIXON Back To Top (89587)
Thank you!!

Wednesday, August 25, 2021 - 12:33:19 AM - Sumit Back To Top (89165)
Thank you. Great Article!!

Friday, June 4, 2021 - 8:37:39 AM - Travis Back To Top (88798)
Thank you. Great article and it helped me quickly find the version of SQL we are running on multiple servers.

Wednesday, December 16, 2020 - 4:20:06 PM - ALVARO S. Back To Top (87921)
Hi,
You can also perform it with powershell and the new sql assessment api. You will find by LATESTCU Rule.
https://docs.microsoft.com/en-us/sql/tools/sql-assessment-api/sql-assessment-api-overview?view=sql-server-ver15

Friday, September 11, 2020 - 9:46:04 AM - Non DBA answer Back To Top (86460)
We don't give network and server admins access to our databases so these TSQL methods won't work as server admins can't login to the database instance. However, since they are charged with server patching, they need a way to see what SQL Server version is present.
This way seems odd as I only go to the Install utility to install SQL Server but there are other tools present if you look.

Go to the START menu, go to Microsoft SQL Server 2016 folder, SQL Server 2016 Installation Center. Tools, and then select Installed SQL Server Features Discovery Report.
This will create an HTML file that shows in a table, the product, Instance name, feature, edition, version number.

Monday, July 13, 2020 - 10:14:29 AM - Gary Mazzone Back To Top (86130)

This is what I use for server info

SELECT
SERVERPROPERTY( 'MachineName') AS [Machine Name/Server Name]
,SUBSTRING(@@VERSION ,1,CHARINDEX(')',@@VERSION,1)) AS [Product Version Name]
,CASE SERVERPROPERTY('IsClustered')  
WHEN 1 THEN  'Clustered' 
ELSE 'Not Clustered' 
 END  AS [Is Clustered]
,CASE SERVERPROPERTY('IsHadrEnabled')
WHEN 1 THEN 'AG Enabled - concider running AG info and health scripts'
ELSE 'AG Not Enabled'
 END AS AvailabilityGroupEnabled
,CASE SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(100)), 1,CHARINDEX('.',CAST(SERVERPROPERTY('productversion') AS VARCHAR(100)))+1 ) 
WHEN '6.5'  THEN 'SQL Server 6.5'
WHEN '7.0'  THEN 'SQL Server 7'
WHEN '8.0'  THEN 'SQL Server 2000'
WHEN '9.0'  THEN 'SQL Server 2005'
WHEN '10.0' THEN 'SQL Server 2008'
WHEN '10.5' THEN 'SQL Server 2008R2'
WHEN '11.0' THEN 'SQL Server 2012'
WHEN '12.0' THEN 'SQL Server 2014'
WHEN '13.0' THEN 'SQL Server 2016'
WHEN '14.0' THEN 'SQL Server 2017'
WHEN '15.0' THEN 'SQL Server 2019'
ELSE 'Unknown Version'
 END AS [Product Name]
,SERVERPROPERTY('edition') AS [Edition]
,SERVERPROPERTY('productlevel') AS [Product Patch Level]
,SERVERPROPERTY('productversion') AS [Product Version Number]
,ISNULL(SERVERPROPERTY ('InstanceName'),'Default') AS [Instance Name]
,SERVERPROPERTY('collation') AS [Collation]
,CASE 
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security'
        WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Mixed Mode Security'
ELSE 'Unknown'
     END AS SecurityMode
,CASE 
WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed'
        WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed'
        WHEN SERVERPROPERTY('IsFullTextInstalled') IS NULL THEN 'Error'
ELSE 'Unknown'
     END AS IsFullTextInstalled
,CONVERT(VARCHAR(100), SERVERPROPERTY('ResourceLastUpdateDateTime')) AS ResourceDatabaseLastUpdateDateTime
,CONVERT(VARCHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceDatabaseVersion
,CASE SERVERPROPERTY('FilestreamConfiguredLevel') 
WHEN 0 THEN 'No FileStream support'
WHEN 1 THEN 'Enables FILESTREAM for Transact-SQL access'
WHEN 2 THEN 'Enables FILESTREAM for Transact-SQL and Win32 streaming access'
WHEN 3 THEN 'Enabled for T-SQL access, local file system access, and remote file system access '
ELSE 'Unknow value returned'
 END AS FileStreamConfiguredLevel
,CASE SERVERPROPERTY('FilestreamConfiguredLevel') 
WHEN 0 THEN ''
ELSE SERVERPROPERTY('FilestreamShareName') 
 END AS FileStreamShareName

Monday, August 12, 2019 - 7:31:06 AM - EMC Back To Top (82035)

Find server details All:

Declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
print @SmoRoot
 
 
SELECT  LOGINPROPERTY(name, 'VSMonitor') AS 'VSMonitor'
,LOGINPROPERTY(name, 'VSBackup') AS 'VSBackup'
,LOGINPROPERTY(name, 'sa') AS 'sa'
,is_expiration_checked  As 'is_expiration_checked'
FROM    sys.sql_logins
WHERE   is_policy_checked = 1
 
USE [master]
GO
SELECT value_data as [AccountName], registry_key
FROM sys.dm_server_registry
WHERE
registry_key LIKE '%MSSQLSERVER%' --For named instance change to MSSQL$<instanceName>
AND value_name = 'ObjectName'
UNION
SELECT value_data as [AccountName], registry_key
FROM sys.dm_server_registry
WHERE
registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
AND value_name = 'ObjectName'
 
declare @version varchar(4)
select @version = substring(@@version,22,4)
 
IF CONVERT(SMALLINT, @version) >= 2012
EXEC ('SELECT    
        SERVERPROPERTY(''ServerName'') AS [Instance Name],
        CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4)
            WHEN ''11.0'' THEN ''SQL Server 2012''
            WHEN ''12.0'' THEN ''SQL Server 2014''
            ELSE ''Newer than SQL Server 2014''
        END AS [Version Build],
        SERVERPROPERTY (''Edition'') AS [Edition],
        SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
        CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'')
            WHEN 0 THEN ''SQL Server and Windows Authentication mode''
            WHEN 1 THEN ''Windows Authentication mode''
        END AS [Server Authentication],
        CASE SERVERPROPERTY(''IsClustered'')
            WHEN 0 THEN ''False''
            WHEN 1 THEN ''True''
        END AS [Is Clustered?],
        SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
        SERVERPROPERTY(''Collation'') AS [ SQL Collation],
        [cpu_count] AS [CPUs],
        [physical_memory_kb]/1024 AS [RAM (MB)]
    FROM    
        [sys].[dm_os_sys_info]')
ELSE IF CONVERT(SMALLINT, @version) >= 2005
EXEC ('SELECT    
        SERVERPROPERTY(''ServerName'') AS [Instance Name],
        CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4)
            WHEN ''9.00'' THEN ''SQL Server 2005''
            WHEN ''10.0'' THEN ''SQL Server 2008''
            WHEN ''10.5'' THEN ''SQL Server 2008 R2''
        END AS [Version Build],
        SERVERPROPERTY (''Edition'') AS [Edition],
        SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
        CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'')
            WHEN 0 THEN ''SQL Server and Windows Authentication mode''
            WHEN 1 THEN ''Windows Authentication mode''
        END AS [Server Authentication],
        CASE SERVERPROPERTY(''IsClustered'')
            WHEN 0 THEN ''False''
            WHEN 1 THEN ''True''
        END AS [Is Clustered?],
        SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
        SERVERPROPERTY(''Collation'') AS [ SQL Collation],
        [cpu_count] AS [CPUs],
        [physical_memory_in_bytes]/1048576 AS [RAM (MB)]
    FROM    
        [sys].[dm_os_sys_info]')
ELSE
SELECT 'This SQL Server instance is running SQL Server 2000 or lower! You will need alternative methods in getting the SQL Server instance level information.'
 
 
 
 
SELECT
 CASE
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
    WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
    ELSE 'unknown'
 END AS MajorVersion,
 SERVERPROPERTY('ProductLevel') AS ProductLevel,
 SERVERPROPERTY('Edition') AS Edition,
 SERVERPROPERTY('ProductVersion') AS ProductVersion
 
 
 
SELECT distinct(volume_mount_point), 
  total_bytes/1048576 as Size_in_MB, 
  available_bytes/1048576 as Free_in_MB,
  (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
FROM sys.master_files AS f CROSS APPLY 
  sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
  available_bytes/1048576 order by 1
 
 
 
SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
mf.physical_name PhysicalFileLocation,
dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO
 
select
total_physical_memory_kb/1024 AS total_physical_memory_mb
from  sys.dm_os_sys_memory;
SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);
 
DECLARE @AuthenticationMode INT  
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',   
N'LoginMode', @AuthenticationMode OUTPUT  
SELECT CASE @AuthenticationMode    
WHEN 1 THEN 'Windows Authentication'   
WHEN 2 THEN 'Windows and SQL Server Authentication'   
ELSE 'Unknown'  
END as [Authentication Mode]  
 
 
select 
p.name as 'Maintenance Plan'
,p.[description] as 'Description'
,p.[owner] as 'Plan Owner'
,sp.subplan_name as 'Subplan Name'
,sp.subplan_description as 'Subplan Description'
,j.name as 'Job Name'
,j.[description] as 'Job Description'  
from msdb..sysmaintplan_plans p
inner join msdb..sysmaintplan_subplans sp
on p.id = sp.plan_id
inner join msdb..sysjobs j
on sp.job_id = j.job_id
where j.[enabled] = 1

Monday, February 18, 2019 - 10:07:56 AM - Greg Robidoux Back To Top (79053)

Hi Sergei, sure you could use the RTM version, but there have been several cumulative updates since the RTM release and it would make sense to apply the updates.

-Greg

Sunday, February 17, 2019 - 7:27:30 PM - Sergei Back To Top (79048)

 Hi,

Could rtm version of SQL Server 2017 be installed in a production environment?

Regards,
Sergei

Wednesday, October 3, 2018 - 11:51:11 AM - Matt Radenz Back To Top (77825)

Thank you very much! Method 4 was exactly what i was looking for. 

Wednesday, February 28, 2018 - 11:40:57 PM - Abrar Uddin Back To Top (75325)

 Thanks a lot for your 

 productive information.

Tuesday, August 8, 2017 - 11:26:49 AM - Ben B. Back To Top (64322)

 Method 4 was an excellent additoin and worked great.  Thanks

Saturday, September 24, 2016 - 11:20:06 AM - Raghu Back To Top (43407)

 How to check sql server version [All the instances], IS, AS and RS version along with service startup type and service acount details, 

 

 

Monday, September 12, 2016 - 4:37:51 PM - Aaron Nelson Back To Top (43308)

 Method 5

 Works on SQL Server 2016 & SQL Server 2014 SP1+

SELECT SERVERPROPERTY ('ProductMajorVersion')  AS 'ProductMajorVersion'
 ,  SERVERPROPERTY ('ProductMinorVersion')   AS 'ProductMinorVersion'
 ,  SERVERPROPERTY ('ProductBuild')     AS 'ProductBuild'
 ,  SERVERPROPERTY ('ProductUpdateLevel')   AS 'ProductUpdateLevel'
 ,  SERVERPROPERTY ('ProductUpdateReference') AS 'ProductUpdateReference'

Friday, September 9, 2016 - 10:58:19 AM - Greg Robidoux Back To Top (43289)

Thanks Ben for another way to get this data.  I will update the tip to include your script.

-Greg

Friday, September 9, 2016 - 10:52:14 AM - Ben Pomicter Back To Top (43288)

I'm surprised the SERVERPROPERTY function isn't mentioned.  That would seem to be the most straightforward way, especially if you're pulling version/edition/SP into a report or repository (easier than parsing the output of @@VERSION), e.g.

select CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
            WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
            WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
            WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
            WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
            WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
            ELSE 'unknown'
       END AS MajorVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('ProductVersion') AS ProductVersion

which would give output such as

MajorVersion   ProductLevel   Edition                            ProductVersion
SQL2008 R2    SP1               Standard Edition (64-bit)   10.50.2796.0

 

Thursday, July 14, 2016 - 6:15:19 AM - Someone Back To Top (41883)

Thanks,

 

Method 3 was able to easily provide me with all the info I needed.

 

*thumbs up*

Monday, August 10, 2015 - 4:40:06 PM - Steve Back To Top (38415)

Unfortunately, I have found that the version # does not reflect the patch level. The version # represents the base level for the SQL Server installation. However, if you want to know if your server has the latest patches, you need to check  the OS Registry. Do you know of a tool that can provide the patch level for SQL server? The MAP tool only provides the version #.

Tuesday, August 4, 2015 - 10:18:40 AM - John Suit Back To Top (38360)

Awesome, thanks so much for taking the time to write this. As usual, a third party source of information is far superior to that of Microsoft themselves. Kudos.

Friday, May 29, 2015 - 4:09:56 AM - Bart Thieme Back To Top (37317)

You could also use xp_msver to retrieve the SQL version and the OS version, but in a table form that is more comprehensable.

Thursday, April 30, 2015 - 1:56:11 PM - Greg Robidoux Back To Top (37075)

Take a look at these posts to see if this gives you want you need:

http://blogs.msdn.com/b/sqlosteam/archive/2013/02/17/when-will-my-sql-server-evaluation-edition-expire.aspx

http://mssqlfun.com/2013/09/16/how-to-check-sql-server-evaluation-version-expire-date/

I think if you run SELECT @@VERSION it will say it is an evaluation edition.

 

Thursday, April 30, 2015 - 12:53:16 PM - Susan Back To Top (37074)

How do you decipher if it is a Demo version with an expiration date?

 

Friday, January 9, 2015 - 2:32:55 PM - Manuel Back To Top (35886)

Another link in Spanish:

http://www.sysadmit.com/2015/01/mssql-saber-la-version-de-sql-server-instalada.html

 

Regards.

 

Thursday, June 19, 2014 - 9:15:56 AM - Greg Robidoux Back To Top (32313)

Hi Geoff, I guess technically if the only access to SQL Server is the server itself and there are no connections from other machines just having the server level license should be sufficient.  Take a look at this document for more info on SQL 2005 licensing: http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc

Wednesday, June 18, 2014 - 10:33:35 PM - Geoff Koo Back To Top (32301)

Hi Greg,


If you purchase: SQL Server 2005 Standard Full edition with Product Key...That the owner says "does not have a CAL with it."  What does that really mean?  (*Understand, that we are not wanting to network version)

Can you still run SQL 2005 as a single User on a single PC?  

OR do you have to purchase an actual CAL to make it work?

Thanks


Geoff

Thursday, February 7, 2013 - 8:26:00 AM - AGL Back To Top (21973)

Another link: http://www.sqlserverbuilds.blogspot.com.es/

Regards.

Tuesday, April 3, 2012 - 9:49:22 AM - Greg Robidoux Back To Top (16751)

Iain, thanks for letting me know. 

That link has been updated and now when you get to that page you need to select the version of SQL Server you are working with.

 

Tuesday, April 3, 2012 - 7:49:29 AM - Iain Back To Top (16748)

Good article apart from

http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx - SQL Server versions (this is the most complete list)

 

That's a dead link.

Friday, March 30, 2012 - 2:11:33 PM - Greg Robidoux Back To Top (16714)

The tip has been updated to reflect the note above from James.

Thursday, March 1, 2012 - 9:56:23 AM - James Collett Back To Top (16230)

You write:-

"The first is by using either Enterprise Manager or SQL Server Management Studio and right clicking on the database instance name and selecting properties."

[Italics mine]

I think this should read:-

"right clicking on the SQL Server instance name"

They are different entities. It certainly threw me.














get free sql tips
agree to terms