What version of SQL Server do I have?

If you’re wondering how to check SQL Server version you are running, then wonder no more. Here at Sequallity, we want to help. And to that end here is a script to find out which version you are currently running with. This is version 1.0 of the script and I shall be adding to it over time.

The script is built to run on-premise SQL Servers, virtual SQL Servers and SQL Azure, but let us know if it breaks or you can think of an improvement.

What Version Of SQL Server do I have Text Picture

Please check out our warranty page.


-- 17th February 2020
-- Sequallity Limited
-- What version of sql server do I have?

IF SERVERPROPERTY('Edition') NOT IN ('SQL Azure')
	SELECT 1 AS WhatID
		,'SQL Server - SQL Edition' AS 'Property'
		,cast(substring(@@version, 1, charindex('(', @@version) - 2) AS NVARCHAR) + ' ' + cast(SERVERPROPERTY('Edition') AS NVARCHAR) AS 'Information'
	
	UNION
	
	SELECT 2
		,'SQL Server - Product Level'
		,CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR)
	
	UNION
	
	SELECT 3
		,'SQL Server - Product Version'
		,CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR)
	
	UNION
	
	SELECT 4
		,'SQL Server - Instancename'
		,CAST(ISNULL(SERVERPROPERTY('InstanceName'), 'Default') AS NVARCHAR)
	
	UNION
	
	SELECT 5
		,'Windows - OS'
		,substring(@@version, charindex('Windows', @@version), len(@@version))
	
	UNION
	
	SELECT 6
		,'Windows - Logical Cores'
		,cast(cpu_count AS NVARCHAR)
	FROM sys.dm_os_sys_info
	
	UNION
	
	SELECT 7
		,'Windows - Physical CPUs'
		,cast(cpu_count / (hyperthread_ratio * 1) AS NVARCHAR)
	FROM sys.dm_os_sys_info
	
	UNION
	
	SELECT 8
		,'Windows - Virtual Machine'
		,lower(virtual_machine_type_desc)
	FROM sys.dm_os_sys_info
	
	UNION
	
	SELECT 9
		,'SQL Server - Restart DateTime'
		,(
			SELECT cast(sqlserver_start_time AS NVARCHAR)
			FROM sys.dm_os_sys_info
			)
	
	UNION
	
	SELECT 10
		,'SQL Server - Restart Days'
		,(
			SELECT cast(datediff(d, sqlserver_start_time, sysdatetime()) AS NVARCHAR)
			FROM sys.dm_os_sys_info
			)
	ORDER BY WhatID
ELSE
	SELECT 1 AS WhatID
		,'SQL Server - SQL Edition' AS 'Property'
		,cast(substring(@@version, 1, charindex('(', @@version) - 2) AS NVARCHAR) + ' ' + cast(SERVERPROPERTY('Edition') AS NVARCHAR) AS 'Information'
	
	UNION
	
	SELECT 2
		,'SQL Server - Product Level'
		,CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR)
	
	UNION
	
	SELECT 3
		,'SQL Server - Product Version'
		,CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR)
	
	UNION
	
	SELECT 4
		,'SQL Server - Instancename'
		,CAST(ISNULL(SERVERPROPERTY('InstanceName'), 'Default') AS NVARCHAR)
	
	UNION
	
	SELECT 5
		,'Windows - OS'
		,substring(@@version, charindex('Windows', @@version), len(@@version))

Sometimes, we find that our clients don’t know what version they have of SQL Server. That’s ok. They’re busy running their own business, besides we’re supposed to be the experts on SQL Server not them.

This script makes it easy for them to find this information, and they can run the script and tell us on the phone, even before we dial-in. It gives us an early heads-up of what version we are dealing with. This is really significant as latter versions have extra features you have to enable.

There are lots of new features in latter versions of SQL Server and we provide training courses on these too.

So there we have it, how to check SQL Server version via an easy to run script.

Leave a Reply