Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
SQL database in Microsoft Fabric Preview
Returns property information about the server instance.
Transact-SQL syntax conventions
Note
Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).
Syntax
SERVERPROPERTY ( 'propertyname' )
Important
The Database Engine version numbers for SQL Server and Azure SQL Database and Microsoft Fabric aren't comparable with each other, and represent internal build numbers for these separate products. The Database Engine for Azure SQL Database is based on the same code base as the SQL Server Database Engine. Most importantly, the Database Engine in Azure SQL Database always has the newest SQL Database Engine bits. For example, version 12 of Azure SQL Database is newer than version 16 of SQL Server.
Arguments
propertyname
An expression that contains the property information to be returned for the server. propertyname can be one of the following values. Use of a propertyname that is invalid or not supported on that version of the Database Engine returns NULL
.
Property | Values returned |
---|---|
BuildClrVersion |
Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server. NULL = Input isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
Collation |
Name of the default collation for the server. NULL = Input isn't valid, or an error. Base data type: nvarchar(128) |
CollationID |
ID of the SQL Server collation. Base data type: int |
ComparisonStyle |
Windows comparison style of the collation. Base data type: int |
ComputerNamePhysicalNetBIOS |
NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster. On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property.Note: If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.NULL = Input isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
Edition |
Installed product edition of the instance of SQL Server. Use the value of this property to determine the features and the limits, such as Compute capacity limits by edition of SQL Server. 64-bit versions of the Database Engine append (64-bit) to the version. Use the following Edition table to identify possible values. Base data type: nvarchar(128) |
EditionID |
Represents the ID of the installed product edition of the SQL Server instance. Use the value of this property to determine features and limits, such as Compute capacity limits by edition of SQL Server. Use the following Edition table to identify possible values. Base data type: bigint |
EngineEdition |
Database Engine edition of the instance of SQL Server installed on the server. 1 = Personal or Desktop Engine (Not available in SQL Server 2005 (9.x) and later versions.) 2 = Standard (For Standard, Web, and Business Intelligence.) 3 = Enterprise (For Evaluation, Developer, and Enterprise editions.) 4 = Express (For Express, Express with Tools, and Express with Advanced Services) 5 = SQL Database 6 = Azure Synapse Analytics 8 = Azure SQL Managed Instance 9 = Azure SQL Edge (For all editions of Azure SQL Edge) 11 = Azure Synapse serverless SQL pool, or Microsoft Fabric 12 = Microsoft Fabric SQL analytics endpoint. Base data type: int |
FilestreamConfiguredLevel |
The configured level of FILESTREAM access. For more information, see filestream access level. 0 = FILESTREAM is disabled 1 = FILESTREAM is enabled for Transact-SQL access 2 = FILESTREAM is enabled for Transact-SQL and local Win32 streaming access 3 = FILESTREAM is enabled for Transact-SQL and both local and remote Win32 streaming access. Base data type: int |
FilestreamEffectiveLevel |
The effective level of FILESTREAM access. This value can be different than the FilestreamConfiguredLevel if the level has changed and either an instance restart or a computer restart is pending. For more information, see filestream access level. 0 = FILESTREAM is disabled 1 = FILESTREAM is enabled for Transact-SQL access 2 = FILESTREAM is enabled for Transact-SQL and local Win32 streaming access 3 = FILESTREAM is enabled for Transact-SQL and both local and remote Win32 streaming access. Base data type: int |
FilestreamShareName |
The name of the share used by FILESTREAM. NULL = Input isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
HadrManagerStatus |
Indicates whether the Always On availability groups manager has started. 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
InstanceDefaultBackupPath |
Name of the default path to the instance backup files. Applies to: SQL Server 2019 (15.x) and later versions. Base data type: nvarchar(128) |
InstanceDefaultDataPath |
Name of the default path to the instance data files. Applies to: SQL Server. Base data type: nvarchar(128) |
InstanceDefaultLogPath |
Name of the default path to the instance log files. Applies to: SQL Server. Base data type: nvarchar(128) |
InstanceName |
Name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, if the input isn't valid, or error.NULL = Input isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
IsAdvancedAnalyticsInstalled |
Returns 1 if the Advanced Analytics feature was installed during setup; 0 if Advanced Analytics wasn't installed. Base data type: int |
IsBigDataCluster |
Introduced in SQL Server 2019 (15.x) beginning with CU 4. Returns 1 if the instance is SQL Server Big Data Cluster; 0 if not. Base data type: int |
IsClustered |
Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
IsExternalAuthenticationOnly |
Returns whether Microsoft Entra-only authentication is enabled. 1 = Microsoft Entra-only authentication is enabled. 0 = Microsoft Entra-only authentication is disabled. Applies to: Azure SQL Database and Azure SQL Managed Instance. Base data type: int |
IsExternalGovernanceEnabled |
Returns whether Microsoft Purview access policies are enabled. 1 = External governance is enabled. 0 = External governance is disabled. Applies to: SQL Server 2022 (16.x) and later versions. Base data type: int |
IsFullTextInstalled |
The full-text and semantic indexing components are installed on the current instance of SQL Server. 1 = Full-text and semantic indexing components are installed. 0 = Full-text and semantic indexing components aren't installed. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
IsHadrEnabled |
Always On availability groups is enabled on this server instance. 0 = The Always On availability groups feature is disabled. 1 = The Always On availability groups feature is enabled. NULL = Input isn't valid, an error, or not applicable. For availability replicas to be created and run on an instance of SQL Server, Always On availability groups must be enabled on the server instance. For more information, see Enable or disable Always On availability group feature. Note: The IsHadrEnabled property pertains only to Always On availability groups. Other high availability or disaster recovery features, such as database mirroring or log shipping, are unaffected by this server property.Applies to: SQL Server. Base data type: int |
IsIntegratedSecurityOnly |
Server is in integrated security mode. 1 = Integrated security (Windows Authentication) 0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.) NULL = Input isn't valid, an error, or not applicable. Base data type: int |
IsLocalDB |
Server is an instance of SQL Server Express LocalDB. NULL = Input isn't valid, an error, or not applicable. Applies to: SQL Server. Base data type: int |
IsPolyBaseInstalled |
Returns whether the server instance has the PolyBase feature installed. 0 = PolyBase isn't installed. 1 = PolyBase is installed. Applies to: SQL Server 2016 (13.x) and later versions. Base data type: int |
IsServerSuspendedForSnapshotBackup |
Server is in suspend mode and requires server level thaw. 1 = Suspended. 0 = Not suspended. Base data type: int |
IsSingleUser |
Server is in single-user mode. 1 = Single user. 0 = Not single user NULL = Input isn't valid, an error, or not applicable. Base data type: int |
IsTempDbMetadataMemoryOptimized |
Returns 1 if tempdb has been enabled to use memory-optimized tables for metadata; 0 if tempdb is using regular, disk-based tables for metadata. For more information, see tempdb Database.Applies to: SQL Server 2019 (15.x) and later versions. Base data type: int |
IsXTPSupported |
Server supports In-Memory OLTP. 1 = Server supports In-Memory OLTP. 0 = Server doesn't supports In-Memory OLTP. NULL = Input isn't valid, an error, or not applicable. Applies to: SQL Server 2014 (12.x) and later versions, and Azure SQL Database. Base data type: int |
LCID |
Windows locale identifier (LCID) of the collation. Base data type: int |
LicenseType |
Unused. License information isn't preserved or maintained by the SQL Server product. Always returns DISABLED. Base data type: nvarchar(128) |
MachineName |
Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server. NULL = Input isn't valid, an error, or not applicable. Base data type: nvarchar(128) |
NumLicenses |
Unused. License information isn't preserved or maintained by the SQL Server product. Always returns NULL .Base data type: int |
PathSeparator |
Returns \ on Windows and / on LinuxApplies to: SQL Server 2017 (14.x) and later versions. Base data type: nvarchar |
ProcessID |
Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance. NULL = Input isn't valid, an error, or not applicable. Base data type: int |
ProductBuild |
The build number. Applies to: SQL Server 2014 (12.x) and later versions. Base data type: nvarchar(128) |
ProductBuildType |
Type of build of the current build. Returns one of the following values: OD = On Demand release a specific customer. GDR = General Distribution Release released through Windows Update. NULL = Not applicable. Applies to: SQL Server. Base data type: nvarchar(128) |
ProductLevel |
Level of the version of the instance of SQL Server. Returns one of the following values: 'RTM' = Original release version 'SPn' = Service pack version 'CTPn', = Community Technology Preview version. Base data type: nvarchar(128) |
ProductMajorVersion |
The major version. Applies to: SQL Server. Base data type: nvarchar(128) |
ProductMinorVersion |
The minor version. Applies to: SQL Server. Base data type: nvarchar(128) |
ProductUpdateLevel |
Update level of the current build. CU indicates a cumulative update. Returns one of the following values: CUn = Cumulative Update NULL = Not applicable. Applies to: SQL Server and Azure SQL Managed Instance. Base data type: nvarchar(128) |
ProductUpdateReference |
KB article for that release. Applies to: SQL Server. Base data type: nvarchar(128) |
ProductUpdateType |
Update cadence the instance follows. Corresponds to the Azure SQL Managed Instance update policy. Returns one of the following values: CU = Updates are deployed via cumulative updates (CUs) for the corresponding major SQL Server release (SQL Server 2022 update policy). Continuous = New features are brought to Azure SQL Managed Instance as soon as they're available, independent of the SQL Server release cadence (Always-up-to-date update policy). Applies to: Azure SQL Managed Instance. Base data type: nvarchar(128) |
ProductVersion |
Version of the instance of SQL Server, in the form of major.minor.build.revision. Base data type: nvarchar(128) |
ResourceLastUpdateDateTime |
Returns the date and time that the Resource database was last updated. Base data type: datetime |
ResourceVersion |
Returns the version Resource database. Base data type: nvarchar(128) |
ServerName |
Both the Windows server and instance information associated with a specified instance. NULL = Input isn't valid, or an error. Base data type: nvarchar(128) |
SqlCharSet |
The SQL character set ID from the collation ID. Base data type: tinyint |
SqlCharSetName |
The SQL character set name from the collation. Base data type: nvarchar(128) |
SqlSortOrder |
The SQL sort order ID from the collation. Base data type: tinyint |
SqlSortOrderName |
The SQL sort order name from the collation. Base data type: nvarchar(128) |
SuspendedDatabaseCount |
The number of suspended databases on the server. Base data type: int |
The following table lists possible values for EditionID
and Edition
.
EditionID | Edition |
---|---|
1804890536 | Enterprise |
1872460670 | Enterprise Edition: Core-based Licensing |
610778273 | Enterprise Evaluation |
284895786 | Business Intelligence |
-2117995310 | Developer 1, or Developer Enterprise 2 |
-2509700633 | Developer Standard 2 |
-1592396055 | Express |
-133711905 | Express with Advanced Services |
-1534726760 | Standard |
1293598313 | Web |
1674378470 | SQL Database or Azure Synapse Analytics |
-1461570097 | Azure SQL Edge Developer 3 |
1994083197 | Azure SQL Edge 4 |
1 Applies to: SQL Server 2022 (16.x) and earlier versions.
2 Applies to: SQL Server 2025 (17.x) Preview and later versions.
3 Indicates the development only edition for Azure SQL Edge.
4 Indicates the paid edition for Azure SQL Edge.
Return types
sql_variant
Remarks
ServerName property
The ServerName
property of the SERVERPROPERTY
function and @@SERVERNAME return similar information. The ServerName
property provides the Windows server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.
The ServerName
property and @@SERVERNAME return the same information if the default server name, at the time of installation, hasn't been changed. The local server name can be configured by executing the following:
EXECUTE sp_dropserver 'current_server_name';
GO
EXECUTE sp_addserver 'new_server_name', 'local';
GO
If the local server name has been changed from the default server name at installation time, @@SERVERNAME returns the new name.
The ServerName
property of the SERVERPROPERTY
function returns the Windows server name as it's saved. In previous major versions, it returned uppercase. This behavior changed back to uppercase between SQL Server 2019 (15.x) CU 9 and CU 12, but starting from SQL Server 2019 (15.x) CU 13, the server name returns as it's saved.
If the Windows server name contains any lowercase characters, this change of behavior might cause differences between the ServerName
property of the SERVERPROPERTY
function, and @@SERVERNAME (uppercase versus lowercase), even if there's no name change for the server.
Consider you have a server named as server01
, with a SQL Server instance named INST1
. The following table summarizes the change of behavior between different builds of SQL Server 2019 (15.x):
SQL Server 2019 (15.x) release | SERVERPROPERTY('ServerName') | Additional information |
---|---|---|
RTM | SERVER01\INST1 |
Returns the ServerName property in uppercase |
CU 1 – CU 8 | server01\INST1 |
Returns the ServerName property as is, without changing to uppercase |
CU 9 – CU 12 | SERVER01\INST1 |
Returns the ServerName property in uppercase |
CU 13 and later versions | server01\INST1 |
Returns the ServerName property as-is, without changing to uppercase |
Version properties
The SERVERPROPERTY
function returns individual properties that relate to the version information whereas the @@VERSION function combines the output into one string. If your application requires individual property strings, you can use the SERVERPROPERTY
function to return them instead of parsing the @@VERSION results.
Permissions
All users can query the server properties.
Examples
The following example uses the SERVERPROPERTY
function in a SELECT
statement to return information about the current instance of SQL Server.
SELECT SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
GO
Related content
For a list of features supported by the editions of SQL Server on Windows, see: