Understanding Catalog Views in SQL Server 2005 and 2008

Understanding Catalog Views in SQL Server 2005 and 2008
SQL Server stores meta data about its configuration options, objects, data type, constraints, etc. in system tables, which cannot be directly queried in SQL Server 2005 and 2008. To access this meta data, SQL Server offers several options. The option I have selected in this tip is the Catalog Views. These provide the most efficient and generic interface to obtain, transform and present customized forms of the persisted system meta data. In this article, I am going to discuss in details about Catalog Views and how they differ from other options like using Compatibility Views, Information Schema Views, etc click here for more details.

SQL Server 2005 and 2008 Ranking Functions - DENSE_RANK and NTILE

SQL Server 2005 and 2008 Ranking Functions - DENSE_RANK and NTILE
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With these new functions, we are no longer required to write several lines of code to get ranking data. It does not only help in simplifying the query but also improves the performance of the query. So now the questions are: what are these ranking functions, how they work and how they differ from each others? This article talks about DENSE_RANK and NTILE in detail, click here for more details.

SQL Server 2005 and 2008 Ranking Functions - Row_Number and Rank

SQL Server 2005 and 2008 Ranking Functions - Row_Number and Rank
SQL Server introduced four different ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With this inclusion we are no longer required to write several lines of code to get ranking. It does not only help in simplifying the query, but also improves the performance of the query. So now the questions are, what are these ranking functions, how do they work and how do they differ from each other? This article talks about Row_Number and Rank in detail, click here for more details.

Different strategies for removing duplicate records in SQL Server

Different strategies for removing duplicate records in SQL Server
In data warehousing applications during ETL (Extraction, Transformation and Loading) or even in OLTP (On Line Transaction Processing) applications we are often encountered with duplicate records in our table. To make the table data consistent and accurate we need to get rid of these duplicate records keeping only one of them in the table. In this article I am going to discuss different strategies which you can take for this, along with its pros and cons. Click here for more details.

Iterating through SQL Server databases and database objects without cursors

Iterating through SQL Server databases and database objects without cursors
There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works? Click here for more details.

How to upgrade SSIS 2005 Packages to SSIS 2008

How to upgrade SSIS 2005 Packages to SSIS 2008
There are several enhancements in SSIS 2008 such as enhanced lookup transformation, the development environment for Script Task and Script Component changing from VSA to VSTA, etc. If you plan to upgrade your SSIS 2005 packages to SSIS 2008 it may be a challenging task especially if it uses the Script Task or Script Component because of the development environmental changes (from VSA to VSTA) . SSIS provides several different options to upgrade SSIS packages, for more details click here.