Default value for char / varchar in SQL column: space (1) or empty string?
I can usually find answers to my questions in SO, but I cannot find information on this topic (I may be asking the wrong question).
Background: Six years ago, a developer upgraded the Visual FoxPro database to SQL Server 2005 (table structure and data). This process introduced various bugs that I am trying to fix to improve the data structure and performance (changing TEXT to VARCHAR (MAX), some floats to DECIMAL, deleting unused columns ...)
My previous task was as follows: find all default values for all columns in all tables for which the corresponding default object did not exist in sys.default_constraints
, and fix them by assigning an empty string char
/ varchar
, 0
before Decimal
, etc. (over 30% of the columns where they are broken ... yes I know -_- wtf !. My script ran smoothly, so well.
Just for fun, I performed SP_HELP
in one of these tables to check for fixed columns, but noticed that the columns char
I hadn't changed had a default value (space((1)))
and not the empty blank row ''
I was using.
My question . Should I leave these columns intact with the default (space((1)))
? or should I change it to ''
(changing the script to do this would be very simple)?
My bet is that I have to change it (a function call on every insert cannot be free), but wanted to ask first.
PD: I'm not a DBA but a developer who needs to use the DBA hat now and then.
source to share
Often, discussion about null vs default empty string (dba.se debate here ). In this case, I believe the main problem is that any part of the application (s) is executing logic based on a single space in a column . Storage and speed issues will be minimal by comparison, perhaps even minor in anything but the largest tables and the largest TPS cases. I also assume that it would be difficult to reliably detect any significant performance difference between insert ' '
, insert ''
and insert space(1)
although, as you said, it may not be free.
Update
I just noticed that you only mentioned columns char
as having this default. In this case, I don't think it really matters that you insert a blank line or one 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 an empty string by default. One possible obstacle is having a null char column and ANSI_PADDING disabled when it was created. However, when testing various combos of ansi-nulls, char, varchar and nullibility, I can't think of a situation where it '' = ' '
is false (in SQL Server 2008 R2, anyway). Hmm ... I need to read a little.
source to share
If your application is agnostic about this, then it might be worth considering that a zero-length column varchar
will take up slightly less space than a single whitespace column.
You are storing 1 byte per row for the column value, and possibly two more in the column offset array if not followed by odd variable length columns.
I would probably use NULL
rather than any of them.
source to share