How do I tell what version \ edition \ service pack of SQL Server I’m working with?

If you work with several instances of SQL Server, there’s a good chance they’re not all the same edition, or service pack, or even version.  With so many different features now available (many of which require specific editions etc) there will more than likely come a time when you’ll need to know what the version, edition and what service packs have been applied to the instance you’re working with.  For instance if a client has SharePoint 2010, I immediately want to know if the client has Enterprise Edition so I can determine if PowerPivot for SharePoint is a solution option for their BI initiative. 

A real easy way to find out this information is to connect to the instance through SQL Server Management Studio(SSMS) and run the following query:



This will return the version, edition, and current service pack in the result set.  In my SQL Server 2012 development  instance it looks something like this:

“Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

Feb 10 2012 19:39:15

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)”

Pretty handy!  I use this all the time, there are also several other methods that can be found here if you looking for info on an SSAS instance perhaps.  Until next time, hope this helps.