Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mharder5
Starting Member
1 Post |
 Posted - 2004-10-25 : 17:09:10
|
| I have a development team that wants to represent all fields with no values with a '' (blank) instead of using the database standard of NULL. The team is coding in C#. I am told it is easier for them to code around blanks than NULL's.Aside from taking up more space in the database than is necessary, what are the drawbacks of this practice? I am concerned with all those blanks messing with the statistics and query plans. There would certainly be more maintenance since each of the blanks would also be stored in the indexes, etc.My other concern is that someone not familiar with this coding practice would be querying the database and not getting the results they require.I haven't seen this as an industry standard. Is this more of a common practice than I am aware of for SQL Server and C#?Mark HarderDBA, eBusiness Solution CentreISM Canada - IBM Global Serviceswww.ismcanada.com |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-25 : 17:33:06
|
| Actually for character data this practice is not so bad.NULLS have their own set of problems.Make sure that the columns don't allow NULL, if you get both kind of logic into the db then it will get messy. ( NULLS and '' ).The question is what to do with numeric data and datetime data, it might not be a good idea to just put 0 in them to represent missing information.The same issue here, if they have to code for '' logic for character data and NULLS for numeric they are no better off anyway.I always try to avoid NULLS as far as possible and only allow it for columns that really should allow missing information.For character data I often put a default on them, such as 'Not registered' or 'Unknown' instead of allowing NULL values.Also, the issue here shouldn't be a coding issue for the C# team, but a database design issue.rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-10-25 : 18:07:05
|
| A first post about NULL and empty string.. Where's that smoke coming from?Caveat: I am on the empty string side.I am sure both sides of the party would agree that it is easier to code when NULL is not around. As you probably know there are special classes/methods to handle NULL in C# and SQL and that should be enough of a warning sign to try and avoid them.I am yet to see this magically space saving ability of NULL over Empty Strings except in the use of TEXT data types and even then there is an option to minimise the "waste". A recent blog...[url]http://sqljunkies.com/WebLog/amachanic/archive/2004/10/22/4743.aspx[/url]Query plans and statistics cope just fine with empty strings. Actually you think it would be the other way around!The most important thing when dealing with missing info is to define a standard way to handle it and then ridigly enforce it.DavidM"Always pre-heat the oven" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-25 : 22:09:34
|
| >> The team is coding in C#. I am told it is easier for them to code around blanks than NULL's.What's that got to do with it? Shouldn't matter what the client needs as this can always be transformed in the SPs. You should decide the best structure for the database and best representation of the data. Null has a meaning and and an empty string has a meaning - if you decide what these represent and stick to it then you won't have a problem (as long as it fits the business needs).This is a reason for not allowing the application to access tables - application programmers are used to 2 valued logic.I would go for nulls to represent unknown andnot applicable otherwise you end up with dummy values in a lot of tables.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-26 : 01:01:05
|
| I'm in the NULLs camp! This is because it can be represented for Dates and Numbers too.We have Triggers on our tables that convert '' [blank string] varchar columns to NULL.Kristen |
|
|
|
|
|
|
blanks ('') instead of NULLS in SQL 2000 tables