CODEWITHSUNDEEP
sqlsql-server-2005types
user565869
user565869

Reputation:

Is There ANY Sense in SQL Data Type VARCHAR(1)?

I've bumped into a lot of VARCHAR(1) fields in a database I've recently had to work with. I rolled my eyes: obviously the designer didn't have a clue. But maybe I'm the one who needs to learn something. Is there any conceivable reason to use a VARCHAR(1) data type rather than CHAR(1)? I would think that the RDMS would convert the one to the other automatically.

The database is MS SQL 2K5, but evolved from Access back in the day.

Upvotes: 20

Views: 8927

Answers (3)

Martin Smith
Martin Smith

Reputation: 453328

A varchar(1) can store a zero length ("empty") string. A char(1) can't as it will get padded out to a single space. If this distinction is important to you you may favour the varchar.

Apart from that, one use case for this may be if the designer wants to allow for the possibility that a greater number of characters may be required in the future.

Altering a fixed length datatype from char(1) to char(2) means that all the table rows need to be updated and any indexes or constraints that access this column dropped first.

Making these changes to a large table in production can be an extremely time consuming operation that requires down time.

Altering a column from varchar(1) to varchar(2) is much easier as it is a metadata only change (FK constraints that reference the column will need to be dropped and recreated but no need to rebuild the indexes or update the data pages).

Moreover the 2 bytes per row saving might not always materialize anyway. If the row definition is already quite long this won't always affect the number of rows that can fit on a data page. Another case would be if using the compression feature in Enterprise Edition the way the data is stored is entirely different than that mentioned in Mitch's answer in any event. Both varchar(1) and char(1) would end up stored the same way in the short data region.

@Thomas - e.g. try this table definition. 

CREATE TABLE T2
(
Code VARCHAR(1),
Foo datetime2,
Bar int,
Filler CHAR(4000),
PRIMARY KEY CLUSTERED (Code, Foo, Bar)
)

INSERT INTO T2
SELECT TOP 100000 'A', 
                  GETDATE(), 
                  ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
                  NULL
FROM master..spt_values v1,  master..spt_values v2

CREATE NONCLUSTERED INDEX IX_T2_Foo ON T2(Foo) INCLUDE (Filler);
CREATE NONCLUSTERED INDEX IX_T2_Bar ON T2(Bar) INCLUDE (Filler);

For a varchar it is trivial to change the column definition from varchar(1) to varchar(2). This is a metadata only change.

ALTER TABLE T2 ALTER COLUMN Code VARCHAR(2) NOT NULL

If the change is from char(1) to char(2) the following steps must happen.

  1. Drop the PK from the table. This converts the table into a heap and means all non clustered indexes need to be updated with the RID rather than the clustered index key.
  2. Alter the column definition. This means all rows are updated in the table so that Code now is stored as char(2).
  3. Add back the clustered PK constraint. As well as rebuilding the CI itself this means all non clustered indexes need to be updated again with the CI key as a row pointer rather than the RID.

Upvotes: 6

Mitch Wheat
Mitch Wheat

Reputation: 300579

AFAIK, No.

a VARCHAR(1) requires 3 bytes storage (The storage size is the actual length of data entered + 2 bytes. Ref.

a CHAR(1) requires 1 byte.

From a storage perspective: A rule of thumb is, if it's less than or equal to 5 chars, consider using a fixed length char column.

A reason to avoid varchar(1) (aside from the fact they they convey poor design reasoning, IMO) is when using Linq2SQL: LINQ to SQL and varchar(1) fields

Upvotes: 11

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

Yes there is sense to it.

  • Easier for it to be definable in the language. It is consistent and easier to define varchar to allow 1-8000 than to say it needs to be 2+ or 3+ to 8000.

  • The VARying CHARacter aspect of VARCHAR(1) is exactly that. It may not be optimal for storage but conveys a specific meaning, that the data is either 1 char (classroom code) or blank (outside activity) instead of NULL (unknown/not-yet-classified).

Storage plays very little part in this - looking at a database schema for CHAR(1), you would almost expect that it must always have a 1 char value, such as credit cards must have 16 digits. That is simply not the case with some data where it can be one or optionally none.

There are also differences to using VARCHAR(1) vs CHAR(1)+NULL combination for those who say tri-state [ 1-char | 0-char | NULL ] is completely useless. It allows for SQL statements like:

select activity + '-' + classroom
from  ...

which would otherwise be more difficult if you use char(1)+NULL, which can convey the same information but has subtle differences.

Upvotes: 17

Related Questions