Fixed and Variable length data type char() versus varchar()

Hi Friends,

This blog is focused on Fixed and Variable length data type char() versus varchar(). During the creation of any new table, we take the decision for data types. Here we will see one interesting scenario which you have to keep in mind during the selection of data types. Let say we have a column which will store a fixed length character data type, then it is very simple, we will go for the char data type.Now think about a different scenario, i have a column that will store character length 11 or 12 so here I’ll go for the varchar data type. We have selected varchar data type to save the space because most of the values are of character length 11. But this is not 100% correct here. Let me show you.

I am going to create two tables where one will store fixed 12 character length data in char(12) and other will store 11 and 12 character length data in varchar(12).

-- Here i am using Test database which is already created on my test instance
Use Test
GO
create table xtStorageTest1
(
id int identity(1,1) Primary Key,
Code Char(12)
)
GO
create table xtStorageTest2
(
id int identity(1,1) Primary Key,
Code VarChar(12)
)
GO
Insert into xtStorageTest1 values('abcdefghijkl')
GO 10000
Insert into xtStorageTest2 values('abcdefghijkl')
GO 5000
Insert into xtStorageTest2 values('abcdefghijk')
GO 5000

Now let’s check the space used by both the tables:

   
exec sp_spaceused xtStorageTest1
GO
exec sp_spaceused xtStorageTest2
GO

Output

Now you can see, varchar storage has taken more space. This is due to the reason of variable length column storage. For varchar storage, SQL Server uses some extra bytes per record, i.e. two bytes per variable length column for providing the offset to the end of that column value.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.