Monday, April 11, 2011

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

1 comment:

  1. "First query will return 3 as output. Because LEN() function returns length of actual data." That is not entirely correct.

    Len() Returns the number of characters of the specified string expression, excluding trailing blanks. (http://msdn.microsoft.com/en-us/library/ms190329.aspx)

    You can take also a look at DataLength()
    http://msdn.microsoft.com/en-us/library/ms173486.aspx

    ReplyDelete

All suggestions are welcome