SQL Server: Two Misconceptions about CHAR Data type

Today I would like to share two very simple but interesting interview (DBA & Developer) questions, for which I found, most of the candidates confused. These questions are

• If I have a table with a column named ColA of data type CHAR(10), and each cell of this column contains same value that is ‘ABC’, what will the output of

SELECT MAX(LEN(ColA)) FROM mytable

• If above mentioned table have 10 rows, How many rows I will get as output of following query

                        SELECT * FROM mytable

                  WHERE ColA+'1' = 'ABC1'

First question is very simple but I found few candidates confused as they think that CHAR and NCHAR data type columns  takes same space, and length of actual data doesn’t matter (which is true). So if it takes same space, LEN() function will also return column length i.e. 10 in our case (which is false).

First query will return 3 as output. Because LEN() function returns length of actual data.

For second question I hardly get the correct answer and almost every candidate said it will return all ten rows, because ColA have same value for every row i.e. ‘ABC’ and concatenating it with ‘1’, result will be ‘ABC1’ and that is equal to our given condition in WHERE clause (Which is false).

 Second query will return 0 rows. Because when we concatenate a column which has datatype CHAR or NCHAR, it comes with all of its length (i.e. length of CHAR or NCHAR column). In our case it was CHAR(10) and every column contains same value i.e. ‘ABC’ so when it will be concatenated with ‘1’ it will become ‘ABC       1’ i.e. ABC with remaining 7 spaces (3+7=10) and then ‘1’.

Use following code for verifications.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'TestTable' AND type = 'U')

CREATE TABLE [dbo].TestTable(

      [ColA] [CHAR] (10))

GO

--- Insert records     

INSERT INTO dbo.TestTable

SELECT 'ABC'

GO 10

--First Query

SELECT MAX(LEN(ColA)) AS CharColumnLength FROM dbo.TestTable

GO

--Second Query

SELECT * FROM dbo.TestTable

      WHERE ColA+'1' = 'ABC1'

GO

DROP TABLE dbo.TestTable