SQL Server – Storage requirements – CHAR v/s VARCHAR – choose the correct data-type

I work with academia frequently, and therefore, get a chance to interact with students and experience the issues they face first hand. I recently had a very interesting experience during one of these visits. I will try to present the experience as a story.
The Problem

One of the students had developed a system which involved some database interaction. As I was reviewing the system, I noticed an issue with the table design. The design that the student had come up with was something like the following:

USE tempdb
GO
--Create the table, with fixed length columns
CREATE TABLE CityMaster (CityId      INT IDENTITY (1,1),
                         CityName    CHAR(50),
                         StateName   CHAR(50),
                         CountryName CHAR(50)
                        )
GO

(The design above is not exactly what he had, but you get the general idea.)

The problem that I had was with his choice of data types. He had chosen a fixed-length data type – CHAR.

I explained that CHAR, being a fixed-length data type, pads a string with trailing blanks when the data is stored to achieve the fixed-length. Variable length data types (e.g. VARCHAR), on the other hand, do not pad trailing blanks, and therefore, have a variable space requirement.

To explain this with an example, the simplest thing for me to do was to insert some test values in his database. Once the test data was ready, I ran the following query, which uses the DATALENGTH() and LEN() functions. For those who came in late,

    DATALENGTH = Returns the number of bytes used to represent an expression
        This can be used to estimate the storage space that SQL Server has taken up for an expression
    LEN = Returns the number of characters of the specified string expression, excluding trailing blanks

Read more: Beyond Relational
QR:

Posted via email from Jasper-net