kindly give some solution

Question

kindly give some solution

nsaireddymca

SSC Journeyman

Points: 84
More actions
June 15, 2011 at 10:23 pm

#242322

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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gaurang-Patel SSCrazy Points: 2109 More actions · Answer 1

nsaireddymca (6/15/2011)
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

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

http://www.sqlstairways.com

Suresh B. SSC-Insane Points: 22986 More actions · Answer 2

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.

sujitkmishra Mr or Mrs. 500 Points: 524 More actions · Answer 3

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.

sqlnaive SSCoach Points: 17437 More actions · Answer 4

Totally agreed with Sujit. Use char only in case of fixed[strictly] length value else varchar [with maximum length of the value].

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

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.

Jayanth Kurup[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 9

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.

Jayanth Kurup[/url]

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 10

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

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

1Ankit1 Ten Centuries Points: 1253 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sujitkmishra Mr or Mrs. 500 Points: 524 More actions · Answer 14

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