T-SQL SERVERPROPERTY function

The SERVERPROPERTY function in T-SQL allows you to retrieve various properties of the SQL Server instance.

SELECT SERVERPROPERTY('Edition'); -- Returns the edition of the SQL Server instance

Here is a list of some commonly used options along with their descriptions:

1. ProductVersion: Returns the full version number of the SQL Server instance.
2. ProductLevel: Returns the product level (e.g., RTM, SP1, CU2) of the SQL Server instance.
3. Edition: Returns the edition of the SQL Server instance (e.g., Enterprise, Standard, Express).
4. EngineEdition: Returns the edition of the database engine (1 = Personal or Desktop Engine, 2 = Standard Edition, 3 = Enterprise Edition, 4 = Express Edition, 5 = SQL Database, 6 = SQL Data Warehouse, 8 = Managed Instance).
5. InstanceName: Returns the name of the instance.
6. IsClustered: Returns 1 if the SQL Server instance is part of a clustered environment; otherwise, returns 0.
7. IsSingleUser: Returns 1 if the SQL Server instance is in single-user mode; otherwise, returns 0.
8. IsIntegratedSecurityOnly: Returns 1 if the SQL Server instance supports only Windows Authentication; otherwise, returns 0.
9. Collation: Returns the default collation of the SQL Server instance.
10. BuildClrVersion: Returns the CLR (Common Language Runtime) version that the instance of SQL Server is using.
11. IsFullTextInstalled: Returns 1 if Full-Text Search is installed; otherwise, returns 0.
12. IsHadrEnabled: Returns 1 if High Availability and Disaster Recovery (HADR) is enabled; otherwise, returns 0.
13. IsPolyBaseInstalled: Returns 1 if PolyBase is installed; otherwise, returns 0.
14. FilestreamConfiguredLevel: Returns the level of FILESTREAM support for the instance (0 = No support, 1 = Only FILESTREAM enabled, 2 = Transact-SQL and FILESTREAM enabled).
15. IsXTPSupported: Returns 1 if In-Memory OLTP (XTP) is supported; otherwise, returns 0.
16. IsAdvancedAnalyticsInstalled: Returns 1 if Microsoft R Server is installed; otherwise, returns 0.
17. IsRServicesInstalled: Returns 1 if R Services (in-database) is installed; otherwise, returns 0.

These are just a few of the options available through the SERVERPROPERTY function. You can use these properties to gather information about your SQL Server instance, which can be useful for administration, monitoring, and troubleshooting tasks.