Tune stored procedures with exception handling in SQL Server 2005

Exception handling was widely thought to be one of the weakest aspects of T-SQL script writing. Fortunately, this has changed in SQL Server 2005, which supports structured error handling. This tip focuses first on the basics of the new TRY…CATCH constructs and then looks at some sample SQL Server 2000 and 2005 T-SQL that produces constraint violations using transactional code.

Source: Joe Toscano April 2006

Download & read the full article at:

Reject alpha characters in a SQL Server character column

Enforcing your data’s integrity is probably the single most important issue you face when designing a database. Validating user input is one way of keeping bad data from making its way into your analysis and reports. It only takes one piece of bad data to throw everything off. It’s serious business and there are no shortcuts — mistakes, even innocent ones, are just too easy to make. It’s just too easy to enter one too many zeros or enter “6heodore” instead of “Theodore.” Granted, you can’t stop every single mistake at the input stage, but it’s the best place to start.

Source: Susan Harkins January 2008

Download & read the full article at:

SQL Server 200: Setting Up Merge Replication

One of the best guide on Merge Replication (SQL Server 2000)

Source: Alexander Chigrik September 2001

Download & read the full article at:

Find blocking processes using recursion in SQL Server 2005

Blocking occurs in SQL Server 2005 when one process has one or more records locked, while another process tries to acquire locks on the same set (or subset) of records. This causes the second process to wait until the first process is done so it can acquire the records; this can cause other processes to be blocked if they are waiting on the resources that the second connection has acquired. These processes are ultimately dependent upon the first process to complete its work before they can complete theirs. This can create a daisy-chain of processes waiting to complete their work.

Source: Tim Chapman January 2008

Download & read the full article at:

XML data type in SQL Server 2005 vs VARCHAR (MAX)

As a database administrator, I tend to look closely at performance issues and how to make sure the use of XML does not affect SQL Server performance. In this tip, I’ll take you through an example using two tables, one inserted and queried with XML and the other with the VARCHAR (MAX) data type. Take a look at the storage, CPU and I/O results and make the best choice for your SQL Server environment.

Source: Michelle Gutzait January 2008

Download & read the full article at:

SQL Server 2005 Best Practices Analyzer (BPA)

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.This download is the January 2008 release of SQL Server 2005 Best Practices Analyzer.

Integration Services: Performance Tuning Techniques

This white paper describes common performance-tuning techniques that you can apply to your SQL Server Integration Services (SSIS) data integration solutions.

Source: Microsoft November 2005

Download file & Read the full article:

Partitioned Tables and Indexes in SQL Server 2005

Table-based partitioning features in SQL Server 2005 provide flexibility and performance to simplify the creation and maintenance of partitioned tables. Trace the progression of capabilities from logically and manually partitioning tables to the latest partitioning features, and find out why, when, and how to design, implement, and maintain partitioned tables using SQL Server 2005.

Source: Microsoft Technet Kimberly L. Tripp January 2005

Download file & Read the full article:

The Impact of Changing Collations

When a business grows internationally, its database system must support multilingual characters in tables through the use of Unicode data types. This white paper explores the amount of time that it takes to change a table column from a non-Unicode data type to a Unicode data type, along with changing the collation of the database. Use this information to calculate how long it takes to alter large tables.

Source: Microsoft SQL Server Best Practices November 2007

Download file & Read the full article:

Online Index Operations

Introduced in SQL Server 2005 Enterprise Edition, the online index feature provides a powerful way to perform maintenance operations such as rebuilding or creating indexes in a production system without sacrificing DML concurrency. This paper provides a detailed discussion of the index process and provides guidelines and best practices for implementing this feature in a production environment.

Source: Microsoft SQL Server Best Practices March 2007

Download file & Read the full article: