If you are looking for an opportunity to get together in a social environment with other IT professionals in South-East Queensland  to share experiences and advice regarding the challenges that we tackle everyday, then why not attend the next WARDY IT Community Dinner?  The WARDY IT Community Dinner is held on the second Monday of each month.  The next dinner is on Tuesday the 13th of June (Monday the 12th of June is a public holiday) at the Punjabi Palace at West End.  For more details refer to http://www.wardyit.com/blog/blog/archive/2006/05/16/107.aspx.  Add this to my Outlook Calendar.

 

I will be in Sydney next month presenting at the Sydney .NET Users Group (http://www.ssw.com.au/ssw/NETUG/default.aspx) on Wednesday the 21st June at 17:45.  So if you are in Sydney and would like to learn more about SQL Server Automation then why not attend this event.  Add this to my Outlook Calendar.

 

A cumulative hotfix for SQL Server 2005 Service Pack 1 was released on the 18th of May.  This hotfix includes a fix for the  ‘SSIS service not starting’ bug (http://support.microsoft.com/kb/918644) as well as other fixes that did not make it to the shipped version of SP1.  For more details on why a hotfix was released so close to Service Pack 1 refer to the following post by Euan Garden http://blogs.msdn.com/euanga/archive/2006/05/18/600571.aspx.

 

If you are looking for additional SQL Server information between the WARDY IT newsletters then don’t forget to subscribe to the RSS feed for our SQL Server Blog http://www.wardyit.com/blog.

 

 

Should you have any suggestions or feedback on how we can improve the newsletter or content that you would like covered, please do not hesitate to email newsletter@wardyit.com.

 

Happy Reading

 

 

Peter Ward

WARDY IT Solutions Chief Technical Architect

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

In this Issue:

DB Manual

SSWUG Podcasts

ClearTrace

SQL Server Intellisence

Partitioned Tables and Indexes

SQL Server 2005 Books Online

SQL Server 2000 Links

Outer Join Mystery

SQL Server 2000 Index Defragmentation

SMO of the Month

SQL Server Tip of the Month

TSQL Tip of the Month

DB Manual

DB Manual is another documentation tool that is competing with the countless other documentation tools available for SQL Server.  These documentation tools range from free to several hundred dollars and DB Manual is very well priced at only 99 Euro for a company license.  DB Manual is not limited to just documenting SQL Server and is able to document SQL Server 2000 and 2005 as well as SQL Server Express, Oracle and Microsoft Access.  What really sets DB Manual apart though is the MSDN style layout of the documentation generated by DB Manual.  For more details regarding DB Manual refer to http://www.dbmanual.com.

 

 

SSWUG Podcasts

If you are looking to keep up to date with everything that is happening with SQL Server then The SQL Server Worldwide Users Group (SSWUG) has two regular podacsts to help fill your MP3 player of choice. Chuck Boyce hosts the ‘The WHERE Clause’ and Stephen Wynkoop hosts the ‘What's Happening Show’.  For a complete list of all of the podcasts available on SSWUG refer to http://www.sswug.org/sswugradio/.

 

 

ClearTrace

ClearTrace is a tool designed to import SQL Server trace files into SQL Server and display a summary of the statements to assist with performance monitoring.  The tool summarises statements by grouping them together so that statements that only differ by the predicate values are grouped together.  This feature makes it easy to view statements together in order to determine the statements and stored procedures which are using the most resources.  This tool is designed as the Read80Trace utility for SQL Server 2005, as a 2005 version of this tool is not being released.  For more details and to download this great tool refer to http://www.cleardata.biz/cleartrace/default.aspx.

 

 

SQL Server Intellisence

In the August 2005 Edition of the WARDY IT SQL Server Newsletter we highlighted how SQL Prompt from Atadore could be used to add Intellisence to Query Analyser and SQL Server Management Studio for SQL queries.  Well Red Gate (http://www.red-gate.com) has purchased SQL Prompt and whilst the tool undergoes further development the current version is available for free.  To take advantage of this SQL Prompt can be downloaded from http://www.red-gate.com/dynamic/redir.aspx?id=516.

 

 

Partitioned Tables and Indexes

If you are looking to improve the scalability and manageability of large tables and tables that have varying access patterns then this MSDN article by Kimberly Tripp (http://www.sqlskills.com/blogs/kimberly/default.aspx) should be the first place you look http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp.

 

 

SQL Server 2005 Books Online

Something to remember is to ensure that the version of SQL Server Books Online that you are using is the most up-to-date version available otherwise you may be looking at information that has since been modified.  Unfortunately, even if you have installed SQL Server 2005 Service Pack 1, you do not have the latest version of SQL Server Books Online.  The latest update to SQL Server 2005 Books Online is the April 2006 Update which can be downloaded from http://shrinkster.com/feo.

 

 

SQL Server 2000 Links

If the bookmark list of your favorite browser is looking a little slim then Vinod Kumar (http://blogs.sqlxml.org/vinodkumar/) has published the ultimate list of SQL Server 2000 Knowledge Base Articles.  The list of all of these references can be found at http://blogs.sqlxml.org/vinodkumar archive/2006/04/26/5264.aspx.

 

 

Outer Join Mystery

Grant Fritchey has written a great article on Outer Joins which has been published on SQL Server Central (http://www.sqlservercentral.com) causing a great deal of debate.  The article shows the different results that can be returned when using the ’old’ outer join syntax of =* and *= as opposed to the LEFT OUTER and RIGHT OUTER JOIN syntax.  This article can be found at http://www.sqlservercentral.com/columnists/gFritchey/outerjoinmystery.asp.

 

 

SQL Server 2000 Index Defragmentation

As SQL Server maintains indexes to reflect the change to the underlying tables, the indexes can become fragmented which in turn can adversely affect performance.  The following White Paper can be used to help determine whether you should defragment indexes in order to increase the performance of SQL Server and also explains the difference between the DBREINDEX AND INDEXDEFRAG DBCC commands.  For more details regarding Index Defragmentation in SQL Server SQL Server 2000 refer to the following White Paper http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.

 

 

 

SMO of the Month

One of the demonstrations that I have been performing in my recent SMO presentations around the country is how to overcome one of the limitations when scripting objects in SQL Server 2005.  In SQL Server 2005 a DROP and CREATE is unable to be generated in the same script.  Bill Graziano (http://weblogs.sqlteam.com/billg/) has released an application that allows you to easily generate a SQL script that contains a drop and create of an object(s) in the same script without using ‘copy and paste’.  This application illustrates the power of SMO and SQL Server automation and can be downloaded from http://www.sqlteam.com/publish/scriptio/.

 

 

SQL Server Tip of the Month

In last months SQL Server Tip we showed how Trace flags can be used to assist with identifying deadlocks.  In SQL Server 2005 a new trace flag (-T1222) has been introduced for deadlock trace output.  When using this Trace Flag you can take advantage of several major improvements available for deadlock detection including  XML like output for the deadlock, the log space used by the processes involved in the deadlock and the execution stack at the time of the deadlock.  An added addition is that you can also view a graphical representation of the deadlock using the SQL Server profiler tool.

 

 

TSQL Tip of the Month

A common question that is posted in the SQL Server Newsgroups is how to find the second highest/lowest value in a list.  For example how do you find the second highest salary (to find the highest is easy just look for the employee with the title of CEO)?  The following TSQL example illustrates how the second highest pay rate can be found.  The following example is based upon the SQL Server 2005 Adventure Works sample database.  For details on how to install this database refer to http://msdn2.microsoft.com/en-us/library/ms143758.aspx.

 

SELECT     MAX(rate)

FROM        HumanResources.EmployeePayHistory

WHERE      rate < (SELECT MAX(rate)

                        FROM HumanResources.EmployeePayHistory)

If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/may2006.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.

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.