Finding the Version of Your SQL Server Database with T-SQL

• T-SQL is a dialect of SQL (Structured Query Language) specifically designed for working with Microsoft SQL Server relational databases.
• It allows you to create, manipulate, and query data within SQL Server databases.

Checking SQL Server Version with T-SQL:

1. SELECT @@VERSION Query:

   • SELECT @@VERSION;
     

   • This query returns a single string containing information about the current SQL Server version, including:

     • Major version number (e.g., 15 for SQL Server 2019)
     • Minor version number
     • Build number
     • Product version information (e.g., "Microsoft SQL Server 2019 (RTM-GDR)")

2. Parsing the Version String (Optional):

Example:

When you execute SELECT @@VERSION; in a database connected to SQL Server 2019, you might see output similar to:

Microsoft SQL Server 15.0.2000.5 (K16.04.0-21001-Seraph [2019-04-26]) - 
(X64) Copyright (C) Microsoft Corporation. All rights reserved.

This indicates:

• Major version: 15 (SQL Server 2019)
• Minor version: 0
• Build number: 2000.5

Additional Considerations:

• The @@VERSION system function is specific to SQL Server and won't work in other database management systems.
• For programmatic version checking within your T-SQL code, you might explore server properties or other techniques depending on your specific needs.

SELECT @@VERSION AS 'SQL Server Version';

This code retrieves the version information using @@VERSION and aliases it as "SQL Server Version" for clarity in the output.

Extracting Major Version:

DECLARE @majorVersion INT;

SELECT @majorVersion = PARSEINT(LEFT(@@VERSION, CHARINDEX('.', @@VERSION)));

SELECT @majorVersion AS 'Major Version';

This code first declares an integer variable @majorVersion. Then, it uses PARSEINT to convert the left portion of the @@VERSION string (up to the first dot) into an integer, which represents the major version. Finally, it selects the value of @majorVersion with an alias "Major Version".

Checking for Compatibility (Example):

DECLARE @requiredMajorVersion INT = 15; -- Assuming minimum required version is 15 (SQL Server 2019)

SELECT @@VERSION AS 'SQL Server Version';

IF PARSEINT(LEFT(@@VERSION, CHARINDEX('.', @@VERSION))) < @requiredMajorVersion
BEGIN
  PRINT 'This script requires SQL Server version ' + CAST(@requiredMajorVersion AS varchar(5)) + ' or later.';
END;

This example demonstrates a basic compatibility check. It defines a required major version (@requiredMajorVersion) and then uses the same logic as the previous example to extract the actual major version. If the actual version is less than the required version, it prints an error message.

Remember to replace @requiredMajorVersion with the actual version you need for your specific scenario.

This is the most straightforward method. You can check the version in SSMS without writing any code:

• Open SSMS and connect to your SQL Server instance.
• Right-click on the server name in the Object Explorer and select "Properties."
• In the "Properties" window, the "Version" field will display the current SQL Server version.

Windows Registry:

• This method requires accessing the Windows registry and might not be suitable for everyone.
• Open the Registry Editor (search for "regedit").
• Navigate to the key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{VersionNumber}\Tools\Setup
  • Replace {VersionNumber} with the actual version number you suspect (e.g., 150 for SQL Server 2019).
• Look for the value named "Edition" in this key. This will indicate the installed SQL Server version and edition.

SQL Server Error Log:

• The SQL Server error log file often contains information about the server version during startup.
• The default location for the error log is: C:\Program Files\Microsoft SQL Server\MSSQL\DATA\<instancename>\SQLErrorLog.txt
  • Replace <instancename> with the actual name of your SQL Server instance.
• Open the error log file and look for lines mentioning the version during startup.

PowerShell Command (Windows):

• If you're comfortable with PowerShell, you can use the Get-Service cmdlet to retrieve information about the SQL Server service:

  Get-Service mssqlserver | Select-Object DisplayName, Status
  

  This will display the service name (which includes the version number) and its current status.

Choosing the Right Method:

• For quick checks within SSMS, using the graphical interface is the easiest option.
• If you need the version programmatically within your code, T-SQL with @@VERSION is the way to go.
• For offline checks or if SSMS isn't available, the registry or error log might be helpful.
• If you're working within a Windows environment, PowerShell offers a convenient alternative.