char Vs varchar

char Vs varchar

Most common rule for any database systems if you have fixed length column use "char" data type else, (if you have variations) then use "varchar". but after read some forum post, noticed this is not common for less variation columns(less than 10 digits columns). I have created sample script to validate this as follows,

USE tempdb

GO

DECLARE

  @char CHAR(5) = 'ABCD',

  @nchar NCHAR(5) = N'ABCD',

  @varchar VARCHAR(10) = 'ABCD',

  @nvarchar NVARCHAR(10) = N'ABCD';

SELECT

  DATALENGTH(@char) AS CharDataLength,

  DATALENGTH(@nchar) AS NcharDataLength,

  DATALENGTH(@varchar) AS VarcharDataLength,

  DATALENGTH(@nvarchar) AS NvarcharDataLength

GO

-------------------------------------------------------------

CREATE TABLE #char(strchar char(8));

CREATE TABLE #varchar(strvarchar varchar(8));

GO

INSERT INTO #char VALUES('A');

INSERT INTO #char VALUES('AB');

INSERT INTO #char VALUES('ABC');

INSERT INTO #char VALUES('ABCD');

INSERT INTO #char VALUES('ABCDE');

INSERT INTO #char VALUES('ABCDEF');

INSERT INTO #char VALUES('ABCDEFG');

INSERT INTO #char VALUES('ABCDEFGH');

INSERT INTO #varchar VALUES('A');

INSERT INTO #varchar VALUES('AB');

INSERT INTO #varchar VALUES('ABC');

INSERT INTO #varchar VALUES('ABCD');

INSERT INTO #varchar VALUES('ABCDE');

INSERT INTO #varchar VALUES('ABCDEF');

INSERT INTO #varchar VALUES('ABCDEFG');

INSERT INTO #varchar VALUES('ABCDEFGH');

GO 10000

SET STATISTICS IO ON

SELECT * FROM #char;

PRINT '------------------------------------'

SELECT * FROM #varchar;

SET STATISTICS IO OFF

EXEC sp_spaceused '#char';

GO

EXEC sp_spaceused '#varchar';

GO

DROP TABLE #char;

DROP TABLE #varchar;

with above script statistics you will noticed less space used (and less pages) for "char" column apart from "varchar" column.