SQL SERVER – Difference Between @@Version and xp_msver – Retrieve SQL Server Information

Just a day ago, I was asked which SQL Server version I am using. I said SQL Server 2005. However, the person I was talking was looking for more information then that. He requested more detail about the version. I responded with SQL Server 2005 Service Pack 2. After the discussion was over I thought there must be some global variable which brings back this information. I took guess and typed following command in SQL Query Editor

SELECT @@Version 'SQL Version'

I was really glad when it worked and returned following result.

Resultset:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

One additional information I learned that Microsoft was established on 1988.

After searching online I also realized that there is Extended Stored Procedure xp_msver, which also returns the similar results however, it returns much more details. Let us see its example.

EXEC
xp_msver


Resultset:

Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ------------------------------------------------------------------------------------------------------------------------
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3054.00
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.3054.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark
of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 200146944 NULL
15 WindowsVersion 170393861 5.1 (2600)
16 ProcessorCount 1 1
17 ProcessorActiveMask 1 00000001
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 1023 1023 (1073111040)
20 Product ID NULL NULL

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL- @@Version

About these ads

4 thoughts on “SQL SERVER – Difference Between @@Version and xp_msver – Retrieve SQL Server Information

  1. You wrote:
    One additional information I learned that Microsoft was established on 1988.

    Nope.
    In 1988 Microsoft created SQL Server 1.0!

  2. Hello Pinal,
    I just want to send you my thanks for the notes you post here. I work in a very challenging environment and there have been notes you have posted that have helped me through tough situations.

    Thank you once again.
    Kind Regards,
    Grant

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s