SQL Server Performance

Free SQL Server Product

Discussion in 'Forum Announcements' started by Wingenious, Sep 14, 2003.

  1. Wingenious New Member

    There is a new version of a free package of T-SQL routines and documentation. This version includes several new routines and other improvements. It consists of over 3000 lines (or about 70KB) of original T-SQL code. Among the many useful routines, two stand out as solutions to common problems that are not adequately addressed by any other tool.

    There have been many forum postings asking about how to do a crosstab query. The typical response is a short and simple script that barely works for a very specific purpose. However, the initial requests are often looking for functionality to mimic the handy crosstab feature of Microsoft Access. They seek a flexible solution that can be used in a variety of situations. One of the best things about a Microsoft Access crosstab is the optional ability to specify the column headings. This limits the aggregation results to certain data values in a certain order. There is a stored procedure in this free package that provides a general purpose crosstab feature, including an option to specify the column headings. Perfect crosstabs can be constructed in moments instead of wasting time trying to decipher (then modify) SQL code written specifically for a very different situation.

    There have been many forum postings asking about how to close gaps in a sequence of identity values. The typical suggestion is to copy the data into a temporary table, truncate the original table, and copy the data back. This is a cumbersome process at best, and many times it simply would not work. The typical use for identity values is a primary key. If the table involved is a parent to another table then removing the records is not an option unless the constraints are disabled. When the data is copied back the parent records get new identity values for the primary key and child records are orphaned. The task begs for a much easier and more effective approach. There is a stored procedure in this free package that moves records within the table (in either of two ways) and adjusts the foreign keys in any child tables. The gaps in identity values are closed without hassles and without loss of data integrity.

    For more information, including a list of the routines with brief descriptions, please visit http://www.dbaction.com.

Share This Page