Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 301
SQL Server 300
Administration 251
DBA 240
T-SQL 231
#TSQL 229
Development 224
Tips 213
Guidance 146
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

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

Jan 2 2012 12:00AM by Nakul Vachhrajani   

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
USE tempdb
GO                        
--Insert test data
INSERT INTO CityMaster (CityName, StateName, CountryName)
VALUES ('Ahmedabad','Gujarat',    'India'),
       ('Mumbai'   ,'Maharashtra','India'),
       ('New Delhi','Delhi'      ,'India'),
       ('Bengaluru','Karnataka'  ,'India')
GO

USE tempdb
GO
--View the length and the data length for the data in the table
SELECT CityMaster.CityId,
       CityMaster.CityName,    LEN(CityMaster.CityName)    AS CityNameLength,    DATALENGTH(CityMaster.CityName)  AS CityNameDataLength,
       CityMaster.StateName,   LEN(CityMaster.StateName)   AS StateNameLength,   DATALENGTH(CityMaster.StateName) AS StateNameDataLength,
       CityMaster.CountryName, LEN(CityMaster.CountryName) AS CountryNameLength, DATALENGTH(CityMaster.CountryName) AS CountryNameDataLength
FROM CityMaster
GO

image

Using the output of the above query, it became easy for me to explain that because CHAR consumes the full 50 characters, the SQL Server consumes a space of 50 + 50 + 50 = 150 bytes irrespective of the amount of data actually being requested for storage.

The solution

As a solution, I recreated the table with the following design:

USE tempdb
GO
--Create the table, with variable length columns
CREATE TABLE CityMaster_v2 (CityId      INT IDENTITY (1,1),
                            CityName    VARCHAR(50),
                            StateName   VARCHAR(50),
                            CountryName VARCHAR(50)
                           )
GO

Again, I inserted the test data and ran the length determination queries.

USE tempdb
GO                          
--Insert test data
INSERT INTO CityMaster_v2 (CityName, StateName, CountryName)
VALUES ('Ahmedabad','Gujarat',    'India'),
       ('Mumbai'   ,'Maharashtra','India'),
       ('New Delhi','Delhi'      ,'India'),
       ('Bengaluru','Karnataka'  ,'India')
GO

USE tempdb
GO
--View the length and the data length for the data in the table
SELECT CityMaster_v2.CityId,
       CityMaster_v2.CityName,    LEN(CityMaster_v2.CityName)    AS CityNameLength,    DATALENGTH(CityMaster_v2.CityName)  AS CityNameDataLength,
       CityMaster_v2.StateName,   LEN(CityMaster_v2.StateName)   AS StateNameLength,   DATALENGTH(CityMaster_v2.StateName) AS StateNameDataLength,
       CityMaster_v2.CountryName, LEN(CityMaster_v2.CountryName) AS CountryNameLength, DATALENGTH(CityMaster_v2.CountryName) AS CountryNameDataLength
FROM CityMaster_v2
GO

The results spoke for themselves, and he quickly grasped the concept that I was trying to put forward, i.e. variable length datatypes have varying storage requirements, making them more efficient from a storage perspective.

image

ALTER TABLE…ALTER COLUMN will not help in releasing space from fixed-length data-types

Because the concept was clear, he proceeded with converting his existing table designs to use variable-length data types using the ALTER TABLE…ALTER COLUMN statement.

USE tempdb
GO
--Changing CHAR to VARCHAR will NOT release space back once the allocations have been done
ALTER TABLE CityMaster
    ALTER COLUMN CityName VARCHAR(50)

ALTER TABLE CityMaster
    ALTER COLUMN StateName VARCHAR(50)
    
ALTER TABLE CityMaster
    ALTER COLUMN CountryName VARCHAR(50)
GO

However, he found that the ALTER TABLE…ALTER COLUMN statement did not make a difference to the storage requirements.

image

The reason, I explained, was that once SQL Server has added trailing spaces, it is no longer able to distinguish between an intentionally padded and an automatically padded version of the same string (e.g. ‘BeyondRelational.com ‘ (3 spaces) from ‘BeyondRelational.com’ (no spaces)) and therefore, SQL Server will not be able to release the space allocated to the trailing spaces back.

The only option for him was to drop-and-recreate the tables and then re-insert all his test data.

Now that we were all set with the concept, I pointed him to one of Vinod Kumar’s (blog|twitter) posts: http://blogs.extremeexperts.com/2003/10/10/using-varchars/

Conclusion

There are not one, but 2 very good reasons why I shared this experience with the community.

  1. The prime reason being that this post might be helpful to students trying to understand and explore databases, and inculcate in them a habit of choosing the correct data-types
  2. I have seen production code with such issues, such as the incorrect choice of data-types. For us in the industry, this is a capital mistake that should not be made, ever

Reference

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql

Tags: Development, Administration, T-SQL, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, Guidance, DBA, Community,


Nakul Vachhrajani
4 · 36% · 11531
8
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • There is a way SQL Server can distinguish those trailing blanks, with the functions RTRIM and LTRIM.

    So, after altering the column to varchar, you just need to update the values on it:

    UPDATE CityMaster SET CityName = LTRIM(RTRIM(CityName))

    There is no need for dropping and re-creating tables and re-inserting data.

    commented on Jan 20 2012 3:11AM
    Antonio Olmedo
    947 · 0% · 29

Your Comment


Sign Up or Login to post a comment.

"SQL Server – Storage requirements – CHAR v/s VARCHAR – choose the correct data-type" rated 5 out of 5 by 8 readers
SQL Server – Storage requirements – CHAR v/s VARCHAR – choose the correct data-type , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]