@@servername - where does instance get that info

is it the 'computer name' of the SQL Server? or how/where does it get that info from?

Who is Participating?
mcv22Connect With a Mentor Commented:
Its the local server name (initialized to computer name during setup). To change the name of the server, use sp_addserver, and then restart SQL Server. For the default instance its usually the computer name. For the named instance its computername\instancename.

The servername can be changed using sp_addserver and sp_dropserver procedures.
cyberkiwiConnect With a Mentor Commented:
This looks like a definition question, the only best answer would be from the book itself...

@@servername reports from master..sysservers or sys.servers and is affected by sp_addserver, sp_dropserver


SERVERPROPERTY / SERVERNAME returns the actual network name of the server
dqmqConnect With a Mentor Commented:
It's the name that the computer running SQL Server is known by the network.  Usually the same as the name of the computer, but you can name it otherwise.  
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

sammySeltzerConnect With a Mentor Commented:
During SQL Server setup, the computer or servername is setup at that time.

To get the servername where your sql server is set up, simply run:


If for some reason you wish to change server name, you use this syntax:

 sp_addserver @server or

 sp_addserver 'servername'

You run this within sql query window

Hope this answers your question.
rajeshprasathConnect With a Mentor Commented:
while installing the SQL Server it automatically takes the host name as @@SERVERNAME, But if you change the host name of the computer after installation. It won't change automatically.

The @@SERVERNAME remains the old host name. So at that time if you want to change the @@SERVERNAME you can do it by the following syntax,

sp_addserver 'new server name'

So it defines the name of the local instance of SQL Server.

you can also refer the following url,
Norman MainaConnect With a Mentor Commented:
select @@servername  for the default instance returns the server name while for named instances, it returns the computername\instancename.
That info is stored in the master table during install of the instances.
anushahannaAuthor Commented:
Thanks for your helpful input.

@@servername is not bringing the name of the computer.

I tried sp_addserver 'new server name' syntax, and restarted the SQL Service; still it reflects the old name? what am i missing?
what do you see here:

Select hostname(), * from sys.servers

Pay attention to the local host,  id=0, which is where @@servername comes from

anushahannaAuthor Commented:
I get
'hostname' is not a recognized built-in function name.

>> id=0, which is where @@servername comes from

after giving a new name with sp_addserver and restarting sql service, what is incomplete that the change has not yet occurred?

dqmqConnect With a Mentor Commented:
sorry, should have been
Select host_name(), * from sys.servers
Norman MainaConnect With a Mentor Commented:
anushahanna:The question you asked originally did not suggest you had a problem with your server name.

A couple of questions to help understand your problem.
1)what version of sql server have you installed that you want to rename?
2)start a new quesry on ssms and type:sp_helpserver :then execute
This will show you the installed instances in your machine
What instance do you want to rename?
3)Run the below code get your server name,your instance name,hostname and port.

set nocount on

Declare @key Varchar(100), @PortNumber varchar(20)

if charindex('\',CONVERT(char(20), SERVERPROPERTY('servername')),0) <>0


set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'




set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'


EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Tcpport',@value=@PortNumber OUTPUT

SELECT CONVERT(char(20), SERVERPROPERTY('servername')) ServerName,

CONVERT(char(20), SERVERPROPERTY('InstanceName')) instancename,

CONVERT(char(20), SERVERPROPERTY('MachineName'))

as HOSTNAME, convert(varchar(10),@PortNumber) PortNumber

anushahannaAuthor Commented:
NormanMaina, thanks for your feedback on those points.

Yes, I can see I am going outside of what I asked. it is like at least 2 questions in one post;
and you had already helped me with the solution. sorry for the confusion.

I have put the follow question below:

Thanks again to all the experts.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.