It is almost a year since SQL Server 2005 was released and the momentum of this release continues to build.  More and more sites are adopting this new version of SQL Server and are taking advantage of the new features available such as database mirroring and snapshot isolation.  This momentum is also gaining traction as a result of new releases for SQL Server 2005 appearing each month.  A new version of SQL Server 2005 Books Online has been released which can be downloaded from http://shrinkster.com/gzc and Community Technology Preview (CTP) 4 of  Visual Studio 2005 Team Edition for Database Professionals (aka Data Dude) has been released which can be downloaded from  http://shrinkster.com/gzb.  It is anticipated that the RTM of Data Dude will be available by the end of the year so watch this space for more details.

 

If you have not booked already for Tech-Ed Australia there are still places available.  This year’s Tech-Ed Australia is being held in Sydney from the 22-25th of August at and Darling Harbor.  For more details and bookings refer to http://www.microsoft.com/australia/teched2006/.  If you will be attending, drop me a line so that we can organize to catch-up during the event.

 

I will be presenting the course, 'Writing Queries for Microsoft SQL Server' on the 25th of September 2006 at Cliftons in Brisbane.  If you are looking for a course that can provide you with the technical skills and knowledge to write efficient Transact SQL including the new features in SQL Server 2005 then this course should not be missed.  For more details regarding the course, refer to the course outline.  As a special offer to readers of the WARDT IT newsletter, this course is available at the early rate of ONLY $395.00 inc. GST.  To receive this $100 discount bookings must be received prior to the 25th of August 2006.

 

Also, if you are in Melbourne on Monday the 7th of August or Canberra on Tuesday the 8th of August, I will be presenting on SQL Server Management Objects at the respective User Groups.  For more details, refer to the Australian SQL Server User Group website http://www.sqlserver.org.au/.

 

If you are looking for an opportunity to share knowledge, information and experiences with other IT professionals in South East Queensland 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 Monday the 14th of August at the Moray Café in New Farm Add this event to your Outlook Calendar.

 

 

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:

Database Mirroring

Clustered Index Scans

Top 100 PERCENT

SQL Server Down Under Code Camp

Reporting Services Excel Export

SQL Server Management Studio

Generate Inserts

Index Defragmentation Best Practices

SQL Server Tip of the Month

TSQL Tip of the Month

Reporting Services Tip of the Month

Database Mirroring

If you are think of using database mirroring as a high availability solution then the following whitepaper by Ron Talmage of Solid Quality Learning is an invaluable resource to assist with understating this new feature http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx.

 

 

Clustered Index Scans

Itzik Ben Gan (who will be attending SQL Server Down Under Code Camp) has recently published a series of three articles about clustered index scans.  The articles explain the different access methods and outlines some important side effects regarding read consistency when a particular lock hint is used or when the lowest transactional isolation level is used.  The first article in the series is available from http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html.

 

 

Top 100 PERCENT

In SQL Server 2000 TOP 100 PERCENT could be used in a view to order the data without the need to use an ORDER BY clause.  This ‘feature’ has been removed in SQL server 2005 and in the following blog post Kimberly Tripp from SQL Skills illustrates why this is a good thing

http://www.sqlskills.com/blogs/kimberly/2006/06/30/SQL2000V2005UsingTop100PercentInAView.aspx.

 

 

SQL Server Down Under Code Camp

SQL Server Down Under Code Camp is a free two day event that will focus on data access and all things SQL Server.  The SQL Down Under Code Camp will be held over the weekend of the 7th and 8th of October 2006 at Charles Sturt university in Wagga Wagga.  This event will be a great opportunity to meet some of the most knowledgeable figures in the SQL Server community in Australia.  More details can be found at http://www.sqldownunder.com/CodeCamp/tabid/53/Default.aspx.

 

 

Reporting Services Excel Export

One of the biggest complaints with the export to Excel functionality of Reporting Services is that merged cells can occur as a result of the export.  The following article shows several tips that can be followed in order to prevent merged cells from occurring when exporting to Excel http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx.

 

 

SQL Server Management Studio

One of the concerns that I continue to hear about SQL Server Management Studio from people who have not previously been introduced to the Visual Studio IDE, is how different the new IDE is.  A great way to get up to speed with the new environment is the eBook that Elizabeth Corey has published.  This eBook which points out new features, shortcuts, bugs and more can be purchased from

http://www.sqlservercentral.com/store/default.asp#ssms.

 

 

Generate Inserts

A common way to generate insert statements based on the data in a table is to use the sp_generate_inserts Stored Procedure developed by Narayana Vyas Kondreddi.  A new version of the Stored Procedure has been developed for SQL Server 2005 and can be downloaded from http://vyaskn.tripod.com/code.htm#inserts.

 

 

Index Defragmentation Best Practices

SQL Server maintains indexes to reflect updates to underlying tables and over time these indexes can become fragmented.  Depending on workload characteristics, this fragmentation can adversely affect workload performance.  This white paper provides information to help you determine whether you should defragment table indexes to benefit workload performance.  Although the white paper is for SQL Server 2000 most of the information is also relevant to SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.

 

 

 

SQL Server Tip of the Month

A little known feature of SQL Server is the ability to ‘version’ Stored Procedures by appending “;#” to the name of the Stored Procedure.  The following example illustrates creating the Stored Procedure foo and how the multiple definitions of  foo are stored against the same object.  This means that if foo is dropped, all versions of foo are dropped.  But it also means you can potentially have multiple versions that are called explicitly by their name ie. EXEC foo;2.

 

CREATE PROC foo;1 AS SELECT 1

GO

 

CREATE PROC foo;2 AS SELECT 2

GO

 

EXEC sp_helptext foo

 

Returns:

 

Text

-------------------------------

 

CREATE PROC foo;1 AS SELECT 1

 

CREATE PROC foo;2 AS SELECT 2

 

 

EXEC foo

 

Returns:

 

-----------

1

 

(1 row(s) affected)

 

 

TSQL Tip of the Month

Typically most SQL Server installations are case–insensitive, meaning that Mr is the same as MR and mr.  However, there are occasions when you need to perform a case-sensitive search.  The following example illustrates how to perform a case-sensitive search to identify all of the titles that are Mr.  The reason that the case-insensitive predicate is used is so that an index on the column can be used as the collate function precludes the use of an index.

 

CREATE TABLE Titles

    (

        Title VARCHAR(10)

    )

 

INSERT Titles SELECT 'mr'

INSERT Titles SELECT 'MR'

INSERT Titles SELECT 'Mr'

 

SELECT  *

FROM  Titles

WHERE      Title = 'Mr'

AND         Title = 'Mr' COLLATE LATIN1_General_CS_AS

 

Returns:

 

Title

----------

Mr

 

(1 row(s) affected)

 

 

Reporting Services Tip of the Month

Occasionally there is a need to add a scalable bar chart to a summary report in a Reporting Services report so that the bar graphically displays sales so that visually the best and worst performing sales can be identified.

 

ie.

 

Store      Sales      Chart

1111        $12000     -.-.-.

1234      $8000      -.-.

1235      $16000     -.-.-.-.

 

This can be achieved by the following steps:

1. Add an embedded image to the report that will be used as the bar

2. In the column where the bar is to be displayed, place an image report item in the detail row cell

3. Select the embedded image as the source for the image report item

4. Set the padding on the image report item to adjust the size and position of the bar within the cell

5. Create an expression for the right padding of the image report item that divides a field value by some maximum value and then multiplies by the total size of the bar

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