SQL Server Service Broker - An Introduction

SQL Server Service Broker (SSBS or SSB) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008) which allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself. This integration of queuing and message based infrastructure into the database provides several advantages from other message/queuing based applications out of the database. In this introductory article on SSBS, I discuss SSBS on a very basic level, how it differs from MSMQ, SSBS architecture and its different components. For more details, click here.

SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc.

SQL Server provides several system meta data functions which allow users to obtain property values of different SQL Server objects and securables, for example

• SERVERPROPERTY
• DATABASEPROPERTY
• FILEGROUPPROPERTY
• FILEPROPERTY
• OBJECTPROPERTY
• COLUMNPROPERTY
• INDEXPROPERTY
• CONNECTIONPROPERTY
• SESSIONPROPERTY

Although you can also use the SQL Server catalog views or Dynamic Management Views to obtain much of this information, in some circumstances the system meta data functions simplify the process. In this tip I am going to demonstrate some of the available system meta data functions and their usage in different scenarios, for more details click here.

Understanding Dynamic Management Views and Functions in SQL Server 2008

Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system. Though these system tables are still available in SQL Server 2005 and in later versions but SQL Server 2005 introduced whole new set of Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) to monitor the health of a SQL Server instance, diagnose and tune the performance issues. For details, click here.

SQL Server CROSS APPLY and OUTER APPLY

SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications, for more details click here.

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.