Comparison of Dates in SQL

Dealing with dates has always been a problem with SQL Server queries. Small things lead to critical errors, which are very difficult to detect. One of the common usages of dates is in 'where' clause of the query to filter out the records based on one or more datetime fields. Keeping few things in mind while writing such SQL queries will help you to write an effective query which does not miss any desired records. This article gives you an idea about writing such queries involving date comparisons.

Auto generate change scripts in SQL Server Management Studio (SSMS) for tables

As a part of my best practices, I always save the T-SQL scripts used for creation and modification of objects in SQL Server. When creating and modifying tables using SQL Server Management Studio designer it is easy to right click in the designer and select "Generate Change Script...", but is there a way to automatically script the creation and/or modification of tables made through of SQL Server Management Studio (SSMS) designer?  This tip shows you an option that exists within SSMS to automatically generate these scripts for all table changes when using the table designer.

Creating a merged (slipstreamed) drop containing SQL Server 2008 RTM + Service Pack 1

Today, I am going to show you how to create new source media that will slipstream the original source media and SQL Server 2008 Service Pack 1. Once you have created this drop, you can install SQL Server 2008 SP1 in a single step! These instructions are included with the Service Pack 1 release but there are some issues with the documentation that will be addressed in the next revision of the on-line documentation. There is not a lot of user interface that indicates you are slipstreaming, but there are a few clues, see at the bottom for screen shots.

Microsoft SQL Server 2008 Resource Governor primer

Managing workloads and consumption with Resource Governor

Resource Governor is a new feature and one of the most anticipated management features in SQL Server 2008. It bolsters SQL Server performance by allowing DBAs to establish resource limits and priorities on processor and memory-related workloads and resource pools. By defining resource limits on resource consumption, it is possible to prioritize multiple workloads in order to ensure consistent performance and maximize the experience of the end users and the efficiency of the applications without degradation.

It is important to realize from the start that Resource Governor will only throttle processes if SQL Server is experiencing CPU or memory pressure, and it will only throttle incoming requests. For example, a process that consumes high CPU will not be throttled, but the next process may be throttled. Resource Governor is unable to throttle high I/O consumers (processes that read and write excessively from disk) in the SQL Server 2008 release, but this is planned for a future release. 

Collecting performance counters and using SQL Server to analyze the data

The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved.

Database level permissions for SQL Server 2005 and 2008

SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the database.

These are the list of database-level permissions.

Exploring SQL Server 2008’s Filtered Indexes

One of the most interesting features in SQL Server 2008 is filtered indexes. Let’s start with a quick explanation and then dig into the details and results.

A filtered index is simply an index with a WHERE clause. For example, if you have a table where a date is NULL in 90% of the records but you need to be able to find only records with a non-NULL date, a filtered index will have only the 10% that are not NULL.

It can be very useful for saving space, saving index maintenance costs, and making queries run faster. However, it is important to understand under whatcircumstances it will make your query faster.

Handling workloads on SQL Server 2008 with Resource Governor

Handling workloads has been quite difficult until SQL Server 2005. For example consider a scenario where one SQL instance is serving two applications i.e. an OLTP application and a reporting/data warehousing application. Since reporting applications are normally resource intensive, it may consume all the SQL Server available resources and may hamper the OLTP application which ideally should have more preference over the reporting application.

To handle this scenario, in earlier version of SQL Server there was one option to create multiple instances for these application (segregating by running one application on each instance) and setting CPU affinity mask for these instances appropriately. But the problems with this approach are, first it works for CPU only and second the dedicated CPUs cannot be shared by other SQL Server instances. For example, if there are two SQL Server instances and instance one has been assigned CPU 1 and 2 and instance two has been assigned CPU 3 and 4 on a four processor machine, even if instance one is idle and instance two is in need of additional resources, it can only use CPU 3 and 4.  So what does SQL 2008 offer to solve this issue?

Deleting duplicate Records using SQLCMD

This article presents an approach to showing and deleting duplicate records that is concise, generic and efficient. It uses some features that were introduced in SQL Server 2005.