June 15, 2011 at 10:23 pm
some people told when ever we are using char data type memory will be wasted
to utilise memory use varchar data type so can any one give syntax to it to identify practically
June 15, 2011 at 10:56 pm
nsaireddymca (6/15/2011)
some people told when ever we are using char data type memory will be wastedto utilise memory use varchar data type so can any one give syntax to it to identify practically
Yes,Cause whenever you are declaring variable @var char(20)
and set its value to 'AB'(set @var='AB') then you are wasting 18 Bytes.
char [ ( n ) ]
Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.
source:http://msdn.microsoft.com/en-us/library/ms176089.aspx
June 15, 2011 at 11:39 pm
nsaireddymca (6/15/2011)
some people told when ever we are using char data type memory will be wasted
Not always.
For example,
Customer_Id CHAR(9)
'030902001'
'040507002'
:
there is no wastage here as the data has fixed length of 9.
June 15, 2011 at 11:39 pm
If you have a fixed length then you should go for char if variable length then varchar. The name of the data types itself is self explanatory.
When comes to performance, the only benefit of using char I can see is page splits. If you are defining char(20) then whenever a row is inserted it will reserve 20 bytes for the field. Now if you are storing 2 characters in the field then 18 bytes are wastage in the page. This can lead to more number of page splits and will contribute to performance issues.
June 22, 2011 at 11:32 am
Totally agreed with Sujit. Use char only in case of fixed[strictly] length value else varchar [with maximum length of the value].
June 22, 2011 at 11:47 am
Also use char for very small strings. VARCHAR(1) is completely nonsensical.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2011 at 9:51 am
sujitkmishra (6/15/2011)
If you have a fixed length then you should go for char if variable length then varchar. The name of the data types itself is self explanatory.When comes to performance, the only benefit of using char I can see is page splits. If you are defining char(20) then whenever a row is inserted it will reserve 20 bytes for the field. Now if you are storing 2 characters in the field then 18 bytes are wastage in the page. This can lead to more number of page splits and will contribute to performance issues.
This is not true. Say you have a field that has max length of 20, but varies in length from say 10 to 20. What matters is the AVERAGE length of ALL data you have or expect in the column. Take it to the extreme: if you have a million rows and all but ONE of them are 20 chars in length and the other is 19 bytes, do you REALLY want the overhead of the column size value (2 bytes for every row IIRC) as well as the effort to calculate length etc for all but that one row? No, you do not. In this case the column should be defined as CHAR(20) and not VARCHAR(20).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 23, 2011 at 10:00 am
A rule of thumb , the data decides the datatype , performance and storage considerations are secondary , i.e if the data is fixed length use char else varchar if the dta is unicode use nvarchar or nchar , storage and memory usage can be compensated in other ways.
Dont let the least relevant aspect dictate the design of the column.
June 23, 2011 at 12:01 pm
Jayanth_Kurup (6/23/2011)
A rule of thumb , the data decides the datatype , performance and storage considerations are secondary , i.e if the data is fixed length use char else varchar if the dta is unicode use nvarchar or nchar , storage and memory usage can be compensated in other ways.Dont let the least relevant aspect dictate the design of the column.
1) in my example the data DID decide the datatype. The data averages 19.99999 bytes per row with a max number of characters of 20. This should absolutely, positively be a char(20).
2) you may think performance considerations are secondary - right up until you actually need good performance. It is a hell of a lot more difficult to reengineer stuff than to do it right from the get go. Fortunately for me there is a nearly infinite supply of entities out there who feel like you do and I get paid good money to fix their stuff because they did shoddy design and shoddy coding and did NOT feel that performance was a primary consideration. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 23, 2011 at 12:15 pm
Fortunately for me there is a nearly infinite supply of entities out there who feel like you do and I get paid good money to fix their stuff because they did shoddy design and shoddy coding and did NOT feel that performance was a primary consideration
I try my best 😀
However , while the example you have given works to explain your theory , I have yet to come across a database column where we have 999999 rows with char 9 and 1 rows with char 10.
Pratically its a mixed bag with average data lenghts varying between 4- 8 chars in most cases. I dont ignore query performance and storage size based on a single datatype. Trust me , I work with a lot of xml and binary datatype to know every little helps, however I would sooner have a varchar(20) column over a char(10) than have my application not scale when the input data format changes.
June 23, 2011 at 1:01 pm
Another difference between char and varchar is they way the query optimizer grants query memory when it for example needs to do a sort on a char versus varchar. Since a char column is fixed length, the query optimizer has a much better chance at granting the correct size of query memory. For varchar it's more of a wild guess (or was it half of the defined size of the varchar column?) which can often lead to tempdb spills.
This is all well explained and demonstrated in one of these webcasts[/url] by Ramesh Meyappan, a former SQL Server developer at Microsoft.
Disclaimer: I'm not saying, in any way, that char is superior to varchar or vice versa. I'm simply pointing out another difference between them.
June 23, 2011 at 1:09 pm
I usually go "kaCHIIINNNGGG" when I see XML and binary or other BLOB data types in a client's schema! 🙂
Anyway, enough of this dead horse. On to other problems!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 23, 2011 at 1:42 pm
The question was to practically demo how char uses up more space than varchar, so here's the demo.
I created two tables, VarChar_Table and Char_Table as below:
create table Char_Table (name char(100))
create table VarChar_Table (name varchar(100))
Now insert 100 rows into column 'Name' of Char_Table. Insert SAME 100 values into VarChar_Table.
Now execute the below query:
sp_spaceused 'Char_Table'
go
sp_spaceused 'VarChar_Table'
Notice in the output that for same data in both tables, the space reserved for Char_Table is 24KB and the space reserved for VarChar_Table is 16 KB.
There is the difference we are talking about. Open for corrections.:-D
June 23, 2011 at 1:49 pm
ankit.shukla1105 (6/23/2011)
The question was to practically demo how char uses up more space than varchar, so here's the demo.I created two tables, VarChar_Table and Char_Table as below:
create table Char_Table (name char(100))
create table VarChar_Table (name varchar(100))
Now insert 100 rows into column 'Name' of Char_Table. Insert SAME 100 values into VarChar_Table.
Now execute the below query:
sp_spaceused 'Char_Table'
go
sp_spaceused 'VarChar_Table'
Notice in the output that for same data in both tables, the space reserved for Char_Table is 24KB and the space reserved for VarChar_Table is 16 KB.
There is the difference we are talking about. Open for corrections.:-D
Completely depends on what data you're inserting. If I insert those 100 rows where that string contains exactly 100 characters, the Varchar will be bigger. If I insert the 100 rows with only 10 characters, the char will be bigger.
Choose appropriate data types based on the data and with thoughts as to storage and performance. Schema is hard (near-impossible) to fix later, you do not want to get it wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2011 at 11:53 pm
Definitely agree with you. I think you have misinterpreted my message. What I was trying to point is that if you have a char(20) field and uniformly storing much shorter strings then it will be an wastage and performance overhead (page splits).
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply