Sql server 2005 sp2
1 / 16

SQL Server 2005 SP2 - PowerPoint PPT Presentation

  • Uploaded on

SQL Server 2005 SP2. Israeli SQL Server User Group March 2005 Ami Levin amilevin@gmail.com. Redistribution of bugs. http://support.microsoft.com/default.aspx?scid=kb;en-us;921896 About 140 confirmed bugs fixed Most of them are concerned with AS.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'SQL Server 2005 SP2' - wang-rosa

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql server 2005 sp2

SQL Server 2005 SP2

Israeli SQL Server User Group

March 2005

Ami Levin


Redistribution of bugs
Redistribution of bugs

  • http://support.microsoft.com/default.aspx?scid=kb;en-us;921896

  • About 140 confirmed bugs fixed

    • Most of them are concerned with AS

Some interesting ones you might have suffered from and didn t even know it
Some interesting ones you might have suffered from and didn’t even know it…

  • 917905

    • FIX: SQL Server 2005 performance may be slower than SQL Server 2000 performance when you use an API server cursor

  • 918276

    • FIX: You notice additional random trailing character in values when you retrieve the values from a fixed-size character column or a fixed-size binary column of a table in SQL Server 2005

  • 918882

    • FIX: A query plan is not cached in SQL Server 2005 when the text of the hint is a large object

  • 919636

    • FIX: Memory usage of the compiled query plan may unexpectedly increase in SQL Server 2005

Sql server 2005 sp2

  • 919775 didn’t even know it…

    • FIX: The BULK INSERT statement may not return any errors when you try to import data from a text file to a table by using the BULK INSERT statement in Microsoft SQL Server 2005

  • 919905

    • FIX: A query may take longer to run in SQL Server 2005 SP1 than it takes to run in the original release version of SQL Server 2005 or in SQL Server 2000

  • 920206

    • FIX: System performance may be slow when an application submits many queries against a SQL Server 2005 database that uses simple parameterization

  • 920346

    • FIX: SQL Server 2005 may overestimate the cardinality of the JOIN operator when a SQL Server 2005 query contains a join predicate that is a multicolumn predicate

  • 920347

    • FIX: The SQL Server 2005 query optimizer may incorrectly estimate the cardinality for a query that has a predicate that contains an index union alternative

  • 922063

    • FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1

Sql server 2005 sp2

  • 922438 didn’t even know it…

    • FIX: A query may take a long time to compile when the query contains several JOIN clauses against a SQL Server 2005 database

  • 923605

    • FIX: A deadlock occurs and a query never finishes when you run the query on a computer that is running SQL Server 2005 and has multiple processors

  • 923849

    • FIX: When you run a query that references a partitioned table in SQL Server 2005, query performance may decrease

  • 924344

    • FIX: You may receive incorrect results when you use the bulk copy program command-line utility to import data in SQL Server 2005

  • 924601

    • FIX: A partitioned table may generate an inefficient query plan when you run a Transact-SQL query that uses the Top n clause on a partitioned table in SQL Server 2005

  • 924807

    • FIX: The restore operation may take a long time to finish when you restore a database in SQL Server 2005

Sql server 2005 sp2

  • 925153 didn’t even know it…

    • FIX: You may receive different date values for each row when you use the GETDATE() function within a case statement in SQL Server 2005

  • 926612

    • FIX: SQL Server Agent does not send an alert quickly or does not send an alert when you use an alert of the SQL Server event alert type in SQL Server 2005

  • 927643

    • FIX: Some search results are missing when you perform a full-text search operation on a Windows SharePoint Services 2.0 site after you upgrade to SQL Server 2005

  • 928537

    • FIX: The full-text index population for the indexed view is very slow in SQL Server 2005

Find and submit bugs and suggestions
Find and submit bugs and suggestions didn’t even know it…

  • http://connect.microsoft.com/

Additional functionality
Additional functionality didn’t even know it…

  • Maintenance plans

    • SSIS no longer required for using M.P.

    • Support for multiple schedules

    • Support for multi-server environments

    • Logging to remote servers

    • Clean up task in the wizard

      * Important note!

    • Must update pre SP2 cleanup tasks

      • Minimal interval was in days, SP2 adds hours

    • http://blogs.msdn.com/sqlrem/for details

Vardecimal storage option
VARDECIMAL storage option didn’t even know it…

  • Only in Enterprise Edition (Developer/Evaluation)

  • Implemented at table level

    • Physical structural change in table and indexes.

    • Will lock the table exclusively for the duration of the change.

  • Run sp_db_vardecimal_storage_format to enable the database, and then sp_tableoption, to enable for the appropriate tables.

  • Cannot be used for system databases

  • http://msdn2.microsoft.com/en-us/library/bb326755.aspx

Logon triggers
Logon Triggers didn’t even know it…

USE master;

CREATE LOGIN login_test

WITH PASSWORD = '3KHJ6dhx(0xVYsdf'


CREATE TRIGGER connection_limit_trigger




IF ORIGINAL_LOGIN()= 'login_test'

AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 ROLLBACK;


  • http://msdn2.microsoft.com/en-us/library/bb326598.aspx

Additional functionality1
Additional functionality didn’t even know it…

  • Common criteria certification

    • http://msdn2.microsoft.com/en-us/library/bb153837.aspx

  • Sqllogship application

    • http://msdn2.microsoft.com/en-us/library/bb283327.aspx

  • SMO enhancements

    • Table.CheckIdentityValue()

    • Column.AddDefaultConstraint()

  • Replication enhancements

    • In Enterprise Edition, you can now initialize subscriptions to snapshot and transactional publications by using a database snapshot.

    • Merge replication now provides a stored procedure that regenerates the triggers, stored procedures, and views that are used to track data changes.

      • See sp_vupgrade_mergeobjects.

Compatibility issues
Compatibility issues didn’t even know it…

  • Windows Vista

  • Office 2007

  • SharePoint Services 3.0

  • Oracle data sources that run on version or later for reporting services

  • Hyperion System 9.3 BI+ Enterprise Analytics data source

Tools enhancements
Tools enhancements didn’t even know it…

  • Management studio custom reports

  • Scripting

    • Generate script wizard

    • More control on scripting options

      • Tools -> Options -> Scripting tab

  • Graphical show plan improved spacing

  • Linked server dialog box “Test Connection”

  • For a full list see

    • http://msdn2.microsoft.com/en-us/library/bb283536.aspx

Separate features downloads
Separate features downloads didn’t even know it…

  • SQL Server express SP2

    • http://go.microsoft.com/fwlink/?linkid=64064

  • Updated Books On Line

    • http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

  • SQL Server 2005 feature pack Feb. 2007

    • http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

  • Updated samples and sample databases

    • http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

    • AdventureWorksLT Diet version

Best practices analyzer ctp
Best practices analyzer CTP didn’t even know it…

  • Analyzes SQL Server databases and server configurations with a set of predefined “best practices” rules and generates recommendations.

  • http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

Performance dashboard
Performance dashboard didn’t even know it…

  • You are not supposed to see this

    • Official announcement will be in a few weeks so don’t tell anyone I showed it to you…

  • Quest, Symantec etc. beware… 