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

6 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!

    Like

  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

    Like

  3. 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?

    Like

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