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 (https://blog.sqlauthority.com), BOL- @@Version

, , ,
Previous Post
SQL SERVER – 2005 – Limitation of Online Index Rebuld Operation
Next Post
SQL SERVER – Get Permissions of My Username / Userlogin on Server / Database

Related Posts

6 Comments. Leave new

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

    Nope.
    In 1988 Microsoft created SQL Server 1.0!

    Reply
  • 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

    Reply
  • Here are some other methods to know version information on the SQL Server

    Reply
  • How to receive the MAC address of the server running a SQL using SQL Query.

    Reply
  • Juana Guerrero
    May 12, 2015 5:00 am

    Why your product id is null? I had the same result in this field, and i’m afraid about my licence status.

    Do you know how can I verify mi licence status?

    Reply
    • that value seems normal. SQL Server 2005 onwards, license type not captured as a part of installation.

      Reply

Leave a Reply

Menu