Scenario
As usual when you don’t do something for a very long time, it is easy to forget what you knew already. I had to increase the column size of a CHAR field from 9 to 15 bytes and I totally forgot about some of the gotchas surrounding CHAR fields. There is a reason we mostly use VARCHAR and it is the “VAR” part that we depend on the most.
Gotcha
I increased a CHAR field from 9 to 15 bytes and totally forgot that when you do that a CHAR field will use all of the bytes available. CHAR fields are for fixed width data, not variable width that’s what VARCHAR is for. Therefore any data I had in my table that was 9 bytes wide was now 15 bytes wide because 6 bytes of white space was appended.
Solution
Therefore instead of changing the field from CHAR(9) to CHAR(15), I changed it to VARCHAR(15) and this solved the problem. However there is a caveat to this, if you already changed your field to CHAR(15), those spaces are saved in there, so if you do the following operation:
- CHAR(9) altered column to CHAR(15)
- CHAR(15) altered column to VARCHAR(15)
At this point it is too late, you now have contaminated your data with 6 more bytes of white space. The only way for this to be successful is if you go directly from CHAR to VARCHAR.
Proof
I had to exercise a microcosm of the same problem to fully understand what was going on. Here is that code to exercise the same issue described above.
-- For when you are finished
DROP TABLE dbo.CharToStart
-- Simple table with 9 characters (bytes)
CREATE TABLE dbo.CharToStart
(
StringCol CHAR(9) NOT NULL
)
-- I always like to insert more than one row when doing tests
INSERT INTO dbo.CharToStart
(
StringCol
)
VALUES
('123456789')
,('123456789')
,('123456789')
,('123456789')
,('123456789')
SELECT * FROM dbo.CharToStart cvv
-- This is simply to test the data. Paste the column data in between the hard quotes
'123456789'
-- Change it to CHAR 15 and test for blanks.
-- Doing this change directly will add unwanted blanks
ALTER TABLE dbo.CharToStart ALTER COLUMN StringCol CHAR(15) NOT NULL
-- Change it to VARCHAR 15 and test for blanks
-- Doing this change directly will not add unwanted blanks
ALTER TABLE dbo.CharToStart ALTER COLUMN StringCol VARCHAR(15) NOT NULL