Export (0) Print
Expand All
Expand Minimize
7 out of 11 rated this helpful - Rate this topic

SERVERPROPERTY (Transact-SQL)

Updated: 14 April 2006

Returns property information about the server instance.

ms174396.note(en-US,SQL.90).gifImportant:
This feature has changed from earlier versions of Microsoft SQL Server. For more information, see Behavior Changes to Database Engine Features in SQL Server 2005.

Topic link icon Transact-SQL Syntax Conventions


SERVERPROPERTY ( propertyname )
propertyname

Is an expression that contains the property information to be returned for the server. propertyname can be one of these values.

Property name Values returned

BuildClrVersion

Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server 2005.

Base data type: nvarchar(128).

Collation

Name of the default collation for the server.

NULL = Input is not 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.

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.
ms174396.note(en-US,SQL.90).gifNote:

NULL = Input is not valid, or an error.

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 maximum number of CPUs, that are supported by the installed product.

Returns:

'Desktop Engine' (Not available for SQL Server 2005.)

'Developer Edition'

'Enterprise Edition'

'Enterprise Evaluation Edition'

'Personal Edition'(Not available for SQL Server 2005.)

'Standard Edition'

'Express Edition'

'Express Edition with Advanced Services'

'Workgroup Edition'

'Windows Embedded SQL'

Base data type: nvarchar(128)

EditionID

Is an identification number that represents the installed product edition of the instance of SQL Server. Use the value of this property to determine features and limits, such as maximum number of CPUs that are supported by the installed product.

-1253826760 = Desktop Edition

-1592396055 = Express Edition

-1534726760 = Standard Edition

1333529388 = Workgroup Edition

1804890536 = Enterprise Edition

-323382091 = Personal Edition

-2117995310 = Developer Edition

610778273 = Enterprise Evaluation Edition

1044790755 = Windows Embedded SQL

4161255391 = Express Edition with Advanced Services

For more information, see Features Supported by the Editions of SQL Server 2005.

Base data type: int

EngineEdition

Database Engine edition of the instance of SQL Server installed on the server.

1 = Personal or Desktop Engine (Not available for SQL Server 2005.)

2 = Standard (This is returned for Standard and Workgroup.)

3 = Enterprise (This is returned for Enterprise, Enterprise Evaluation, and Developer.)

4 = Express (This is returned for Express, Express Edition with Advanced Services, and Windows Embedded SQL.)

Base data type: int

InstanceName

Name of the instance to which the user is connected.

Returns NULL if the instance name is the default instance, if the input is not valid, or error.

Base data type: nvarchar(128)

IsClustered

Server instance is configured in a failover cluster.

1 = Clustered.

0 = Not Clustered.

NULL = Input is not valid, or an error.

Base data type: int

IsFullTextInstalled

The full-text component is installed with the current instance of SQL Server.

1 = Full-text is installed.

0 = Full-text is not installed.

NULL = Input is not valid, or an error.

Base data type: int

IsIntegratedSecurityOnly

Server is in integrated security mode.

1 = Integrated security.

0 = Not Integrated security.

NULL = Input is not valid, or an error.

Base data type: int

IsSingleUser

Server is in single-user mode.

1 = Single user.

0 = Not single user

NULL = Input is not valid, or an error.

Base data type: int

LCID

Windows locale identifier (LCID) of the collation.

Base data type: int

LicenseType

Mode of this instance of SQL Server.

PER_SEAT = Per Seat mode

PER_PROCESSOR = Per-processor mode

DISABLED = Licensing is 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 is not valid, or an error.

Base data type: nvarchar(128)

NumLicenses

Number of client licenses registered for this instance of SQL Server if in Per Seat mode.

Number of processors licensed for this instance of SQL Server if in per-processor mode.

Returns NULL when the server is none of these.

Base data type: int

ProcessID

Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.

NULL = Input is not valid or an error.

Base data type: int

ProductVersion

Version of the instance of SQL Server, in the form of 'major.minor.build'.

Base data type: nvarchar(128)

ProductLevel

Level of the version of the instance of SQL Server.

Returns one of the following:

'RTM' = Original release version

'SPn' = Service pack version

'Bn', = Beta version

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 of SQL Server.

NULL = Input is not 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).

sql_variant

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 has not been changed. The local server name can be configured by executing the following:

EXEC sp_dropserver 'current_server_name';
GO
EXEC 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 following example uses the SERVERPROPERTY function in a SELECT statement to return information about the current server. This scenario is useful when there are multiple instances of SQL Server installed on a Windows server, and the client must open another connection to the same instance used by the current connection.

SELECT CONVERT(char(20), SERVERPROPERTY('servername'));
GO

Release History

14 April 2006

New content:
  • Added the EditionID property.
  • Added Express Edition with Advanced Services values to Edition, EngineID, and EngineEdition.
Changed content:
  • Indicated Personal and Desktop editions are limited to earlier versions of SQL Server.
  • Inserted EngineEdition mappings for Workgroup Edition and Windows Embedded SQL Edition.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.