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
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
