SERVERPROPERTY T-SQL Enhancement in SQL Server 2016

While, this article focuses on the metadata power of SERVERPROPERTY, it is the final piece of our comprehensive 10-part T-SQL Enhancements in SQL Server 2016 Series designed to modernize your SQL Server Skills.

Introduction

As we reach the conclusion of our 10-part series on SQL Server 2016 T-SQL enhancements, we look at a tool that is vital for every DBA: the SERVERPROPERTY function.

While this function has existed for years, SQL Server 2016 introduced several new properties that eliminate the need for complex registry hacks or XP_MSVER calls. These enhancements allow you to programmatically identify patching levels (CUs and SPs) and default file paths with a simple query—features that are essential for automation and health checks.

Key New Properties in SQL Server 2016

The 2016 update added properties that specifically help with environment discovery and installation tracking.

1. Tracking Patching & Versions

Historically, knowing if a server was on a specific Cumulative Update (CU) required comparing the build number against a chart. Now, it's native:

• ProductUpdateLevel: Returns the update level of the current build (e.g., 'CU1' or 'SP1').
• ProductBuild: Returns the actual build number.
• ProductUpdateReference: Returns the KB article link or reference for that specific update.

2. Identifying Default Paths

Finding where a server stores its data by default used to involve reading the registry. SQL Server 2016 makes this accessible via T-SQL:

• InstanceDefaultDataPath: The path to the default data file directory.
• InstanceDefaultLogPath: The path to the default log file directory.

Practical Implementation Script

To see these enhancements in action, you can run the following script on your SQL Server 2016 (or higher) instance. This provides a "Quick Glance" dashboard of your environment.

SELECT
    SERVERPROPERTY('MachineName')               AS [MachineName],
    SERVERPROPERTY('InstanceName')              AS [InstanceName],
    SERVERPROPERTY('ProductVersion')            AS [ProductVersion],
    SERVERPROPERTY('ProductLevel')              AS [ProductLevel],
    SERVERPROPERTY('Edition')                   AS [Edition],
    SERVERPROPERTY('EngineEdition')             AS [EngineEdition],
    SERVERPROPERTY('InstanceDefaultDataPath')   AS [InstanceDefaultDataPath],
    SERVERPROPERTY('InstanceDefaultLogPath')    AS [InstanceDefaultLogPath],
    SERVERPROPERTY('ProductBuild')              AS [ProductBuild],
    SERVERPROPERTY('ProductBuildType')          AS [ProductBuildType],
    SERVERPROPERTY('ProductMajorVersion')       AS [ProductMajorVersion],
    SERVERPROPERTY('ProductMinorVersion')       AS [ProductMinorVersion],
    SERVERPROPERTY('ProductUpdateLevel')        AS [ProductUpdateLevel],
    SERVERPROPERTY('ProductUpdateReference')    AS [ProductUpdateReference];
GO

Why These Enhancements Matter for DBAs

Automation and Migration

If you are writing PowerShell scripts or T-SQL migration scripts, you can now use InstanceDefaultDataPath to automatically place new databases in the correct drive without hard-coding paths.

Compliance and Auditing

The ProductUpdateLevel property is a lifesaver for security audits. You can quickly run a central query across all your servers to identify which ones are lagging behind on security patches.

Conclusion: Celebrating the 10-Part Series

This concludes our comprehensive 10-part series, "T-SQL Enhancements in SQL Server 2016 for Developers and DBAs." Over the course of this tutorial, we have deep-dived into the most transformative features of the 2016 release. We began with fundamental improvements like TRUNCATE TABLE WITH PARTITIONS and the highly requested DROP IF EXISTS syntax. We explored critical maintenance enhancements including Online ALTER TABLE operations and fine-tuning MAXDOP for DBCC commands. We also covered essential database-level optimizations like the new AUTOGROW settings, modern string handling with COMPRESS/DECOMPRESS and STRING_SPLIT, and advanced messaging with FORMATMESSAGE.

Finally, we wrapped up with the SERVERPROPERTY enhancements, giving you the tools to programmatically manage and audit your modern SQL Server environment. SQL Server 2016 represented a massive shift toward a more flexible, efficient, and developer-friendly database engine. We hope this series on MyTechMantra helps you leverage these features to build faster and more resilient database solutions.

Series Quick Links: T-SQL Enhancements in SQL Server 2016

• Part 1: TRUNCATE TABLE WITH PARTITIONS
• Part 2: DROP IF EXISTS
• Part 3: ALTER TABLE WITH (ONLINE = ON | OFF)
• Part 4: MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• Part 5: ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• Part 6: ALTER DATABASE SET AUTOGROW_ALL_FILES
• Part 7: COMPRESS and DECOMPRESS Functions
• Part 8: STRING_SPLIT and STRING_ESCAPE Functions
• Part 9: FORMATMESSAGE Statement
• Part 10: SERVERPROPERTY Function

Frequently Asked Questions (FAQ)

1. Does SERVERPROPERTY work on SQL Server on Linux?

Yes! Starting with 2017 (which built upon the 2016 foundation), SERVERPROPERTY is the primary way to identify the underlying platform via the ComparisonStyle or Edition properties.

2. Why does ProductUpdateLevel return NULL on my server?

If you are on the RTM (Release to Manufacturing) version without any Cumulative Updates or Service Packs installed, this property may return NULL.

3. Is InstanceDefaultDataPath different from the master database path?

Yes. This property returns the path configured in the Server Settings for new databases, which may be different from where the system databases are stored.

4. Can I use these properties in SQL Server 2014?

No, these specific properties (ProductUpdateLevel, InstanceDefaultDataPath, etc.) were introduced in SQL Server 2016.

5. How do I find the SQL Server version?

You can still use SELECT @@VERSION, but SERVERPROPERTY allows you to pick specific pieces of that information for cleaner reporting.

6. Does EngineEdition tell me if I'm in the Cloud?

Yes, an EngineEdition value of 5 indicates Azure SQL Database, and 8 indicates Azure SQL Managed Instance.

7. Can I filter by these properties in a WHERE clause?

Absolutely. You can use them to create conditional logic in your scripts, such as IF SERVERPROPERTY('ProductBuild') < '13.0.4001'.

8. What is the benefit of ProductBuild over ProductVersion?

ProductVersion returns the full string (e.g., '13.0.1601.5'), while ProductBuild specifically targets the build integer, which is easier for numerical comparisons.

9. Is this series officially over?

Yes! This is Part 10. However, stay tuned to MyTechMantra.com for more deep dives into SQL Server 2019, 2022, and Azure SQL!

Trending SQL Server Articles and Tips