SQL Server General DBA

Moving SQL Server System Databases

I was recently examining the configuration of one of our production database servers. This server was involved in Merge Replication. All the database files on this server were dumped on one small SAN drive. The replication cleanup job for some reason was unable to delete the historical records from the system tables present in the […]

Are You Using SSMS effectively ?

SQL Server Management Studio is an essential tool for DBAs and developers. But are you using it effectively?  In this article, I will try examine a few tips and techniques which could well boost your efficiency using SSMS. Object Filtering The below screen is probably familiar to most DBAs – it lists all tables under […]

Whether to use UNION or OR in SQL Server Queries

Recently I came across with an article on DB2 about using Union instead of OR. So I thought of carrying out a research on SQL Server on what scenarios UNION is optimal in and which scenarios OR would be best. I will analyze this with a few scenarios using samples taken  from the AdventureWorks database […]

Adding Column to a SQL Server Table

Adding a column to a table is  common task for  DBAs. You can add a column to a table which is a nullable column or which has default values. But are these two operations are similar internally and which method is optimal? Let us start this with an example. I created a database and a […]

ERROR_PROCEDURE does not return a schema name

A recent blog entry I read reminded me again that I wanted to rant about an issue in SQL Server for quite some time now… SQL Server 2005 introduced the separation between user and schema. Though schemata already existed  before SQL Server 2005, they really became usable with this version, imho. At the same time  […]

Identity Property Range Checking in SQL Server

The IDENTITY property for a column of a numerical data type is a frequently used method to achieve system-generated “uniqueness” for each row in a table. Such a column then in turn is a quite popular choice for the PRIMARY KEY constraint. Most of the times one would choose the data type int for the […]

SQL Server Email Alerts Notifications

As  DBAs, we are often required to set up alert notification emails for various tasks. For example, there might be a requirement to set up notification alerts if a particular backup job fails. A while back, I  came across the situation where I was asked to write a script which sent an email alert to […]

Create a Performance Baseline Repository

Terms used in this article Execution or SQL Execution represents a call to a Stored Procedure or an SQL Batch. General Description This process creates a Baseline repository that holds summarized data of all SQL Executions executed on an instance of SQL Server. The repository lights up your SQL Server’s activity and exposes interesting information […]

Manage Database Projects With Visual Studio 2010

This article demonstrates how to manage database projects with Visual Studio 2010.

Unicode Compression in SQL Server 2008 R2

 Introduction Data Compression was first introduced in SQL Server 2005 with Service Pack 2 when Microsoft introduced a new storage format called VARDECIMAL to store decimal and numeric data types as variable length columns. In SQL Server 2008, Microsoft enhanced Data Compression feature to compress fixed length data types such as INTEGER, CHAR and FLOAT. […]