For those of you that subscribe to the Database Daily Newsletter which is published weekly by the team from SQL Server Central (http://www.sqlservercentral.com/) you may have noticed a couple of familiar articles in the latest newsletter.  The reason for this dej'vu is that two of the WARDY IT Solutions blog articles were published in the Database Daily Update for the 19th of December.   If you currently do not subscribe to the WARDY IT Solutions SQL Server blog it can be reached at http://www.wardyit.com/blog/.

 

I would like to take this opportunity to thank all our newsletter subscribers for their support and feedback throughout the year and wish you and your families the best of wishes over the festive season and for the year ahead.

 

We here at WARDY IT Solutions look forward to continuing to publish the SQL Server newsletter in 2006 and should you have any content that you would like covered in upcoming issues then do not hesitate to email newsletter@wardyit.com with your suggestions.

 

Happy Reading

 

 

Peter Ward

WARDY IT Solutions Chief Technical Architect

Phone: +61 7 3856 5411     Mobile: +61 0 403 177 761

In this Issue:

Reporting Services 2005 Sorting

SSIS Training

Pro SQL Server

SQL Server 2005 Indexed Views

RS 2005 Best Practices

TRY/CATCH to Resolve a Deadlock

SSIS Data Cleansing

Reporting Services Scripter Version 2

T-SQL of the Month

SQL Server Tip of the Month

 

Reporting Services 2005 Sorting

A common request within the implementation of enterprise reporting systems is for interactive features such as sorting to be available to the client. Reporting Services 2005 has introduced interactive sorting so that columns can be sorted in a report.  The following article by William Pearson shows how to design a Reporting Service report from scratch and how the sorting feature is implemented in Reporting Services 2005 http://www.databasejournal.com/features/mssql/article.php/10894_3570621_1.

 

 

SSIS Training

SQL Server Integration Services (SSIS) is the 'new' DTS in SQL Server 2005.  Microsoft has released a ‘hands on’ training package for SSIS which contains a series of exercises to illustrate how to create SSIS packages.  This ‘hands on’ training can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=b1145e7a-a4e3-4d14-b1e7-d1d823b6a447&DisplayLang=en.

 

 

Pro SQL Server

A new SQL Server book 'Pro SQL Server' has been released by Apress which is authored by some of the top SQL Server authors including. Ton Rizzo Adam Machanic and Joseph Sack. A sample chapter on SQL Server .Net integration is available for download from http://www.sqlservercentral.com/columnists/pressrelease/prosqlserver2005.asp.

 

 

SQL Server 2005 Indexed Views

Indexed views are a feature of all versions of SQL Server 2000 and 2005.  However only the query optimiser of the Developer and Enterprise editions of SQL Server 2000 and 2005 will use an indexed view to solve queries that structurally match the view, even if they don't refer to the view by name.  This article explains the performance benefits that can be gained through using an index view in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx.

 

 

RS 2005 Best Practices

Microsoft has released a document that consolidates guidelines, suggestions and tips for report authoring and design for SQL Server 2005 Reporting Services. Although this document was written for Reporting Services 2005 many of the design practices are also applicable to Reporting Services 2000.  This document can be downloaded from http://download.microsoft.com/download/1/3/4/134644FD-05AD-4EE8-8B5A-0AED1C18A31E/Reporting_Services_Design.doc.

 

 

TRY/CATCH to Resolve a Deadlock

A deadlock is a situation that occurs when at least two transactions are waiting for each other to complete.  To generate a Deadlock refer to http://www.wardyit.com/blog/blog/archive/2005/12/12/65.aspx.  SQL server 2005 has introduced TRY/CATCH structured exception handling and the following article is a good example of this error handling which will resolve a deadlock. http://www.sql-server-performance.com/art_deadlock_sql_2005.asp.

 

 

SSIS Data Cleansing

One of the noteworthy features of SQL Server Integration Services (SSIS) is the new Fuzzy Lookups and Fuzzy Groupings features.  These features offer a powerful data cleansing solution to assist with dedupping data.  The fuzzy features offer an easy way to assist with identifying common data inconsistencies including misspellings, inconsistent abbreviation usage, and other free-form text input anomalies.  For more information regarding these features refer to http://msdn.microsoft.com/sql/default.aspx?pull=/msdnmag/issues/05/09/sqlserver2005/default.aspx.

 

 

Reporting Services Scripter Version 2

In March's SQL Server Newsletter (http://www.wardyit.com/newsletters/mar2005.htm) we mentioned Jasper Smith's great utility Reporting Services Scripter. Well, Jasper has released an updated version that adds full support for SQL Server Reporting Services 2005 both as a script source and deployment target.  This new version also has a new ‘Transfer' mode to automatically deploy generated Reporting Services scripts to a selected target server.

 

 

 

T-SQL of the Month:

 

Something that we have noticed whilst reviewing code recently is the number of queries that return unnecessary columns through the misuse of the SELECT * FROM construct. One of the most common performance and scalability problems are queries that return too many columns or too many rows.

 

Using a SELECT * FROM construct not only returns unnecessary data but can also force clustered index scans for the query plan, regardless of the WHERE clause restrictions.  This happens because the cost of going back to the clustered index to return the remaining data from the row after using a non-clustered index to limit the result set is actually more resource-intensive than scanning the clustered index.

 

The query below and the execution plan shows the difference in query cost for a SELECT * FROM construct compared to selecting only the required column.

 

SELECT * FROM northwind.dbo.orders

Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 19.

 

SELECT orderid FROM northwind.dbo.orders

Table 'Orders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 2.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL Server Tip of the Month

 

Have you ever tried to delete all tables from a SQL Server database?  If so, you will either have ended up getting a very sore wrist from Right clicking and selecting delete in Enterprise Manager, or alternatively created a script to generate the drop statements that you could copy/paste and execute much like the example below:

 

SELECT     'drop table ' + name

FROM        sysobjects

WHERE      xtype = 'u'

 

There is however another way that this can be achieved using the undocumented Extended Procedure called xp_execresultset.  This Extended Stored Procedure is available in SQL Server 2000, however has been deprecated in SQL Server 2005.  As the name implies, this Extended Procedure executes the resultset of a SQL statement.   In order to use this XP, two parameters are required.  The first being a string with the SQL statement that generates the SQL statements and the second defines the database to execute the resultset in.  For example, the statement below will drop all tables in the Northwind Database

 

xp_execresultset N'select ''drop table '' + name from sysobjects where xtype = ''u'' ', 'northwind'

 

 

If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/dec2005.htm

WARDY IT Solutions is a highly regarded IT Solutions provider specialising in Microsoft SQL Server.  You may not be aware though that WARDY IT Solutions is also a registered Microsoft Partner and has an established relationship with numerous hardware and software vendors, enabling us to provide competitive pricing on your next hardware or software acquisition.  For more information on how WARDY IT Solutions can assist with your next acquisition email: contact@wardyit.com.  WARDY IT Solutions has also recently partnered with Web Central in order to add web hosting and DSL plans backed by guaranteed Service Levels to the extensive list of services that we already provide.

You are receiving this monthly newsletter as a valuable associate of WARDY IT Solutions. Should you wish to unsubscribe then send a blank e-mail to newsletter_remove@wardyit.com. Alternatively if you have been forwarded this newsletter and would like to subscribe than send a blank email to newsletter_add@wardyit.com.