3

I usually can find answers to my questions in SO, but I can't find information on this topic (may be I'm making the wrong question).

Background: six years ago some developer upsized a Visual FoxPro data base to SQL Server 2005 (tables structure and data). The process introduced various bugs that now I'm trying to fix in order to improve the data structure and performance (changing TEXT to VARCHAR(MAX), some Floats to DECIMAL, dropping unused columns...)

My previous task was: to find all Default Constraints assigned to all columns in all tables for which the corresponding default object didn't exist in sys.default_constraints and fix them assigning an empty string to char/varchar, 0 to Decimal, etc... (more than 30% of columns where broken... yes I know -_- wtf!. My script worked smoothly, so far so good.

Just for fun I executed SP_HELP in one of those tables to verify the fixed columns, but noticed that the char columns I didn't change had a default value of (space((1))), instead of the plain empty string '' I was using.

My question is: Should I leave those columns intact with the default value (space((1)))? or should I change it to '' (changing the script to do that would be really easy)?

My bet is that I should change it (calling a function on every insert can't be free), but wanted to ask to be sure first.

PD: I'm not a DBA, but a developer who has to use the DBA hat now and then.

Roimer
  • 1,419
  • 1
  • 19
  • 25

2 Answers2

3

Often times, the debate is about null vs a default empty string (dba.se debate here). In this case, I think the main concern is if any part of the application(s) perform logic based upon a single space in a column. Storage and speed concerns would be minimal compared to this, probably even negligible in anything but the largest table and highest TPS cases. I also imagine it would be difficult to reliably detect any meaningful performance difference between insert ' ', insert '' and insert space(1), even though as you've said it can't be free.

Update

I just noticed that you've mentioned only char columns as having this default. In this case, I don't think it really matters that you're inserting empty string or a single space as they will be stored as spaces up to the length of the column:

create table #CharTest ( Chars char(2) )
insert into #CharTest select ''
insert into #CharTest select ' '
insert into #CharTest select '  '
select distinct Chars from #CharTest
-- returns 1 row!

At this point I would like to say that you can get rid of the space and just have the empty string default. One potential caveat is if you've got a nullable char column and ANSI_PADDING was off when it was created. However while testing various combos of ansi-nulls, char, varchar, and nullibility, I can't come up with a situation where '' = ' ' is false (in SQL Server 2008 R2, anyway). Hmm...I need to go do some reading.

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • I has had the opportunity to read almost all the code of the application the last four years, and I'm sure that all trailing spaces are trimmed before any comparison (in the app logic, scripts or reports), so having spaces or empty strings is irrelevant for the current code. I was just felling uneasy for having a function where a literal would do the job. – Roimer Feb 07 '13 at 18:24
  • About your UPDATE: Yes, the `TEXT` columns also had `(space((1)))` as default, but after updating all of them to `VARCHAR(MAX)` I also changed the default to `''`; was then when I noticed the chars. Thanks, I'm editing the script: I'm going to get rid of the space, mostly to get a _consistent/uniform_ database. – Roimer Feb 07 '13 at 18:56
1

If your application is agnostic on the matter then one thing to consider is that a zero length varchar column will take up slightly less space than a single space column.

You save 1 byte per row for the column value and potentially another two in the column offset array if it is not followed by any non empty variable length columns.

I'd probably use NULL rather than either of these though.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • I would prefer to use `NULL` in many of those fields, but the app logic don't (and won't) handle nulls, so I have to do the best I can without changing the app, which is currently in production. And thanks for the tip on the size: there are many columns that are usually inserted with default values, and I my next step is to reduce the row sizes – Roimer Feb 07 '13 at 18:35