June 16, 2011 at 5:45 am
hi to all
this is sai reddy i was workin as a developer cum faculty on satya technology.
i wold like to know how can we say by using char data type we were unable to save memory?
we can save the memory by usin varchar datatype
to show the proof in oracle on usage of memory syntax is there like in the same way i woulk like to show the difference between char and varchar datatype.
June 16, 2011 at 5:54 am
Hope this helps you. Note the table size. Char table 200KB, varchar table only 16KB.
create table dbo.tbChar
(
idint identity(1, 1),
Datachar(7000),
);
create table dbo.tbVarChar
(
idint identity(1, 1),
Datavarchar(7000),
);
-- Run the following two lines 20 times
insert into dbo.tbChar(Data) values('A');
insert into dbo.tbVarChar(Data) values('A');
sp_spaceused tbChar
tbChar20 200 KB160 KB8 KB32 KB
sp_spaceused tbVarChar
tbVarChar20 16 KB8 KB8 KB0 KB
June 16, 2011 at 6:40 pm
I recall figuring out that somewhere around 20 characters is the breakeven point. Less than that and varchar is using more space keeping track of the offset that it takes to store the extra blanks in the char type. Also keep in mind that the offset information is kept at the end of the row so a little more work is required to read a varchar than a char.
June 16, 2011 at 10:04 pm
andersg98 (6/16/2011)
I recall figuring out that somewhere around 20 characters is the breakeven point. Less than that and varchar is using more space keeping track of the offset that it takes to store the extra blanks in the char type. Also keep in mind that the offset information is kept at the end of the row so a little more work is required to read a varchar than a char.
In SQL Server and not including VARCHAR(MAX) and not counting the NULL bit, it only takes two bytes + the actual length of data to store a VARCHAR, so there's no breakeven point based purely on the overhead because, semantically speaking, the data length can never be equal to the data length +2. I will agree, however, that there's a breakeven point based on how much the length of the data in each row of the column varies and how often it varies.
For example, if the data in a column never varies in length, then it would be foolish to even think of using a VARCHAR because there's nothing to gain from it even if the column is 100 or more characters wide. Same goes if the length is variable but decreases from the max only for a couple of rows.
On the flip side of that, if you have a column that normally contains only 10 characters with the occasional max of 100 characters, you can save a huge amount by using VARCHAR.
I like what Suresh did in his example... he did it both ways and measured. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2011 at 7:15 am
The other big advantage of varchar over char fields is that there is a lot more work on the front end of apps when all the fields are char fields. You end having to rtrim(field) as field for every column. Doesn't really slow anything but adds a LOT of extra key strokes when using the data in a GUI.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply