If you ever lose the drive that contains your log file, your database will become suspect and will stop working. Notice that you cannot expand the database. If you do, you will get an error message. You do not need to restore the backup in such a crash...

Pivots in SQL Server 2005/2008 can convert row into column data. Pivots are frequently used in reports, and are reasonably easy to work with. However, many people have asked me how to make the column list dynamic. Normally, this list is fixed, but many...

The Database Engine Tuning Advisor (DTA) is a new tool in SQL Server 2005 that enables you to tune databases and improve the performances of your queries. DTA examines how queries are processed in the databases you specify and then it recommends how you...

Today I was asked what was the replacement for the IIF command in MS Access and how to manage nulls in SQL Server. So I decided to write about nulls, not that I like to work with them...but sometimes we don't have a choice ;) SQL Server may generate...

Effect of NULL Values on Aggregate Functions NULL values may cause aggregate functions to produce unexpected or incorrect results because these functions ignore NULL values for calculations. For example, if the Weight column of a table has 504 records...

I’m often ask how combine information on same line in a query. Let me explain, lets say that you would like to list all products from the Production.product table in the AdventureWorks database and that you would like to list ALL vendors for each product...

People are always surprised when I tell that their queries will run slower when they use local variables in their queries. Let me explain: If you use a local variable in a query predicate instead of a parameter or literal, the optimizer resorts to a guesstimate...

Today, I was asked a question on how to prevent Microsoft Excel from connecting to SQL Server. At first I wasn’t sure how to respond to this, but I finally found a way. It is a simple trick that uses the FOR LOGON option now available since the SP2 release...

When I teach my SQL administration classes, I'm often ask if SQL Server provides a mechanism to log user logins. Before SQL Server 2005 SP2, this task was possible but not reliable. In SQL Server 2005 this task is easy to implement using DDL triggers...

Today, I want to talk about two commands that are often overlooked. SET STATISTICS IO and SET STATISTICS TIME these two commands are very useful when comes time to tune a query. Tuning a query seems simple enough. In essence, we want our searches to run...

The other day I found a way to script multiple objects using the "Object Explorer Detail" tab in Management Studio. To be honest, I never thought that this tab was useful. All you have to do is select the type object you want to script in the...