My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didn’t understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, it’s other uses evaded me for a while. Luckily, [...]
Archive of posts filed under the SQL Server 2008 category.
Introduction to Change Data Capture (CDC)
Change Data Capture (CDC) is a powerful feature included in SQL Server 2008. Using change data capture, it is possible to determine which values have been added, changed, or deleted within a table. Setting up CDC is done by specifying tables to monitor. Under the hood, CDC is written using the same repl logreader function [...]
Configure Change Data Capture Settings
After setting up change data capture (CDC), there are a few configuration options that allow for optimal performance based upon applications load. Using the default values, the most number of transactions that can be grabbed every 5 seconds is 5000. Remember, a transaction is not the same as a database row. One transaction can update [...]
Activity Monitor
The Activity Monitor is a new feature in SQL Server 2008 that provides high-level and drill down information giving good insight into the performance of SQL Server allowing DBA’s to quickly identify the source of slowdowns. To launch the activity monitor, right click on the instance name in SSMS and select ‘Activity Monitor’. This launches [...]
The Merge Statement
Examples, best practices, uses, and benefits of the SQL Server 2008 MERGE statement.
Find All Queries Run Against a Table
Dynamic management view to find all queries that have been run against a table since the last reboot.
SQL Server 2005 vs SQL Server 2008
Outlines some new features of SQL Server 2008 and provides the reasons to upgrade instead of 2005.
MetaData Change Data Capture Solution (CDC)
Change Data Capture, CDC is a feature that is revolutionizing SQL Server in respects to data flow. Previous to change data capture, triggers or high water marks needed to be used in order to perform delta transfers of data to data warehouses or disparate systems. This example shows the use of a custom CDC solution [...]
SQL Server 2008 Minimally Logged Inserts
SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if all the [...]
Custom Pagination with Dynamic ORDER BY
SQL Server Denali has a new feature allowing pagination using the order by clause. A common solution needed for the front end is to paginate records prior to sending them to the webserver. More frequently now, we are seeing demormalized data sets being stored in the WebServer’s or a middle tiers cache mechanism. Those solutions [...]
