Today I wanted to find the version of SQL Server 2008 installed in my machine. I did a quick search and the first support article from Microsoft had the answer to it http://support.microsoft.com/kb/321185
I just modified it a little bit with column headings so that if you are using it in an SP, returning it etc., it would help. So the modified query is as below
SELECT SERVERPROPERTY('productversion') as 'Product Version', SERVERPROPERTY ('productlevel') as 'Service Pack Version', SERVERPROPERTY ('edition') as Edition
If there are better/alternate ways, please post in the comments.
Cheers !!!