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.  If you do, you will lose everything since your last BACKUP.  All you have to do is to detach the database and reattach it without the log.  SQL Server 2005 does allow you to do this. You can reattach the ... Read Full Story

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 times the new columns are determined by the data at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:   PI... Read Full Story

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 can improve performance by suggesting the creation of indexes and statistics.  It replaces the Index Tuning Wizard from Microsoft SQL Server 2000. You can use DTA to tune a database by using workload (trace files) that were previously... Read Full Story

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 unexpected results when performing calculations on columns that have NULL values. So, to resolve this problem you need to use the ISNULL, NULLIF, and COALESCE functions of SQL Server 2005 when your columns hold NULL values. ... Read Full Story

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, of which 299 records have NULL values, the average of this column calculated by using the AVG function may return incorrect values. The following code example demonstrates this: USE AdventureWorks GO   SELECT AVG(Weight) AS 'Average We... Read Full Story

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 from the Purchasing.Vendor table on the same line.  Look at the following example:     You can see in the 3rd column that all venders are list separated by a comma.   In the AdventureWorks database the relation ... Read Full Story

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 for selectivity of the predicate. Use parameters or literals in the query instead of local variables, and the optimizer will do a better selecting a query plan. For example, execute both of these queries.  The first one uses... Read Full Story

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 of SQL Server 2005.     USE master GO   CREATE TRIGGER Prevent_Apps_logon ON ALL SERVER FOR LOGON AS BEGIN       IF APP_NAME() LIKE '%excel%' -- or any other app name!   ... Read Full Story

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 (Data Definition Language). One reason that people ask me this question, is because they need to comply with Sarbanes-Oxley.  I will be writing a series of articles on auditing for SOX in the next weeks. As a consultant, I ... Read Full Story

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 faster.  When I teach my class on “Optimizing SQL Server” I realize that many people find it difficult to accomplish this task.  While there are many reasons why query tuning is difficult, this article will concentrate on one... Read Full Story