SQL Server Service Pack

Because service packs include fixes that often affect performance (among other things) you should upgrade your SQL Servers with the most recent service pack when it becomes available.

In some cases, service packs affect how the Query Optimizer performs. Because of this, some queries that were once slow may now be faster. On the other hand, some queries that were fast may become slower. Because of this, after installing any service pack, you need to be aware of the above possibilities, so that you can deal with them as appropriate. Specifically, keep in mind that hints used in some queries may no longer be needed once a service pack added, so keep a close eye on them after it is installed.

But, as any prudent DBA knows, you will want to thoroughly test the newest service pack before installing it on your production servers. [6.5, 7.0, 2000, 2005] Updated 11-15-20005

*****

Sometimes you need to find out which service pack version you are running on SQL Server. First, run this command in Query Analyzer or Management Studio:

SELECT @@Version

Or

SELECT SERVERPROPERTY('ProductLevel')

Once you have the version number, then match it to the list below to find which SQL Server service pack you are using.

• 2005.90.2047 SQL Server 2005 SP1
• 2005.90.1399 SQL Server 2005 RTM
• 2000.8.00.2039 SQL Server 2000 SP4
• 2000.8.00.760 SQL Server 2000 SP3
• 2000.8.00.534 SQL Server 2000 SP2
• 2000.8.00.384 SQL Server 2000 SP1
• 2000.8.00.194.0 SQL Server 2000 "gold" release, no SP
• 7.00.623 SQL Server 7.0 "gold" release, no SP
• 7.00.677 MSDE in Office 2000, Office Developer
• 7.00.689 SQL Server 7.0 SP1 Beta
• 7.00.699 SQL Server 7.0 SP1
• 7.00.835 SQL Server 7.0 SP2 Beta
• 7.00.842 SQL Server 7.0 SP2
• 7.00.961 SQL Server 7.0 SP3
• 7.00.1063 SQL Server 7.0 SP4
• 6.50.201 SQL Server 6.5 "gold" release
• 6.50.213 SQL Server 6.5 with SP1
• 6.50.240 SQL Server 6.5 with SP2
• 6.50.252 SQL Server 6.5 with "bad" SP3. Do not use.
• 6.50.258 SQL Server 6.5 with SP3
• 6.50.259 SQL Server 6.5 on SBS only
• 6.50.281 SQL Server 6.5 with SP4
• 6.50.297 SQL Server 6.5 included in Site Server 3
• 6.50.339 SQL Server 6.5 "Y2K" Hot-fix
• 6.50.415 SQL Server 6.5 with "bad" SP5. Do not use.
• 6.50.416 SQL Server 6.5 with SP5a
• 6.50.479 SQL Server 6.5 with "post SP5a update"

[6.5, 7.0, 2000, 2005] Updated 11-15-2005

*****

To find out the current level of SQL Server Analysis Services, start the Analysis Manager, right-click on the Analysis Servers node, and select "About Analysis Services."

Once you have the version number, then match it to the list below to find which SQL Server service pack you are using.

• 2000.8.0.760 Analysis Services SP 3
• 2000.8.0.532 Analysis Services SP 2
• 2000.8.0.382 Analysis Services SP1
• 2000.8.0.194 Analysis Services "gold" release, no SP

[2000] Updated 11-15-2005