Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 21st, 2006, 12:11 PM
SQLScott's Avatar
Wrox Author
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default Peformance question regarding Defaults

So, I had a discussion with a co-worker yesterday regarding the application of defaults on columns. Let me give you some background.

We are converting a Foxpro database to SQL Server. The front end is being re-written in .NET. The conversion of the data is such that when a character column is imported that has no data, it comes in as an empty string (''), not a NULL value.

However, when NEW records are added via the .NET front end, it obviously stores them as NULL (if no value is supplied for that column).

This is breaking reports, because it did not don on us to check for NULL values in our queries.

So, the solution my co-worker is recommending is to add a default to all character columns that inserts an empty string. That way we don't have to go back and retro fit a bunch of views and stored procs to add the IS NULL check.

However, my question to the group is the following: Can anyone think of a reason NOT to go this route, such as possible performance degredation?

The correct answer would have been to do NULL checking from the start and we realize that, but we are too far into this to go back and "fix" some things unless we really have to.


Scott Klein
Author - Professional SQL Server 2005 XML
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
Old November 24th, 2006, 09:52 PM
Friend of Wrox
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts

You're going to have to do a data cleanup at one point or another... and, it's easier to change procs/views in the database than it is to recompile front end code. AND, NULLs and blanks mean two different things... personally, I'd change the blanks to NULLs in the data and fix the abhorrent code in the database.

--Jeff Moden
Old December 1st, 2006, 04:23 PM
Wrox Author
Join Date: Dec 2006
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts

Using a default would have some impact on performance, but unless your current perf is right on the edge of some acceptable limit and the volume of transactions is at the absolute maximum capacity of the current system, then its unlikely you would ever see the tiny difference the default would add.

However as Jeff points out NULL does not equal '' (empty string) In some cases this doesnt matter to the app. If thats the case for you then the easiest change is to just add the default.

But be warned this could come back and bite you in the butt at some later point when you suddenly realise you really do need to have a NULL value :(

Steve Wort
Co Author "Professional SQL Server 2005 Administration"

Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting Defaults Before Saving Data castlegrpsf General .NET 0 February 23rd, 2008 11:13 AM
replication, identity, defaults StevieB SQL Server 2000 0 July 19th, 2005 01:10 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.