What is the difference between SERVERPROPERTY('MACHINENAME') and HOST_NAME()

Is there 开发者_运维知识库a difference? I am after the name of the server, even if it's running on an instance.

HOST_NAME() is the name of the client. So if you retrieve the value from SSMS on your workstation it will be the name of your workstation, while if you run the command from SSMS on the server, it will be the name of the server.

Also if the server is clustered, do you want the virtual SQL Server name or the physical name of the currently active node? If the latter you'll want:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS');

Machine is SQL but more in depth here: http://msdn.microsoft.com/en-us/library/ms174396.aspx and the host name is here: http://msdn.microsoft.com/en-us/library/ms178598.aspx

The difference is this: HOST:

Important

The client application provides the workstation name and can provide inaccurate data. Do not rely upon HOST_NAME as a security feature.

Examples

--------------------------------------------------------------------------------

The following example creates a table that uses HOST_NAME() in a DEFAULT definition to record the workstation name of computers that insert rows into a table recording orders.

Copy


CREATE TABLE Orders
   (OrderID     int        PRIMARY KEY,
    CustomerID  nchar(5)   REFERENCES Customers(CustomerID),
    Workstation nchar(30)  NOT NULL DEFAULT HOST_NAME(),
    OrderDate   datetime   NOT NULL,
    ShipDate    datetime   NULL,
    ShipperID   int        NULL REFERENCES Shippers(ShipperID));
GO

Machine:

Copy

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO

As you can see, its more reliable to use serverprop.machine for this.