Excellent article! Can someone clarify the BLOB confict? The "SQL Tips for Developers" piece by Vaughn recommends we keep BLOB out of the database because of db cache flushing and IO performance. Then Otey's piece "Using the SqlCommand Object to Import BLOBs" tells us how to store BLOBs. Does Vaughn's recommendation stand (Storing BLOBs is bad practice) or should be put BLOBs in our DB's anyway? Will SS 2005 bring more efficient BLOB handling or separate data caches to make storing BLOBs a good idea?

Olivier Muff 6/17/2004 2:14:42 AM

I am a certified DBA but haven't come across a more neat and elegant solution than this in my 3 years of experience (Refering to the aggregate multiplication). Have you published a book by any chance

shakti6/15/2004 11:22:38 AM

This is an excellent article, it combines a number of the issues that can really kill performance. I am an SQL Server DBA and I plan on sharing this info with our Development team. Thanks.

Joan Braithwaite 2/27/2004 7:15:51 AM

you can not use DSN for benifit from SQL native provider

Craig2/26/2004 5:11:26 PM

There is a bug in the script "Listing 1: Script to Refresh Views Owned by db_owner". Specifically, sysobjects.name is varchar(256), but the local variable declared in the script is varchar(30). If you try to execute this script against a database with views that have long names (> 30 characters) the script fails. To fix the problem, use these lines instead:

DECLARE @ObjectName varchar(256)
DECLARE @ObjectName_header varchar(300)

Note that @ObjectName_header really only needs to be 11 characters longer than @ObjectName, but by leaving some extra space in the string, the user can modify the header without changing the variable declaration.

Rich Holt 2/24/2004 2:08:21 PM

Listing 1 in "The Reusable Cursor" contains a bug.
sysobjects.name is varchar(256) in SQL Server 2000,
but the local variable declared in the script is varchar(30);
the script will fail if the database contains views with names longer than 30 chars.

As a rule of thumb, local variables should be declared of the same type and size as the column whose data they are intended to contain.

Rich Holt 2/20/2004 8:35:05 AM