Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

char(??) vs varchar(??) Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 9:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 1:51 PM
Points: 138, Visits: 370
Hi all.

I have a database with over 200 tables. They all use char(xx) instead of varchar(xx). I heard that varchar is better because it saves space.

I tried to change a table from char(xx) to varchar(xx). It seems that it doubled the amount of space used. What gives?

Does it really matter?

Thanks,
Mike
Post #1378547
Posted Monday, October 29, 2012 10:02 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:16 AM
Points: 121, Visits: 1,248
Char:

1.Fixed length memory storage
2.CHAR takes up 1 byte per character
3.Use Char when the data entries in a column are expected to be the same size
5.Ex:
Declare test Char(100);
test="Test" -
Then "test" occupies 100 bytes first four bytes with values and rest with blank data.


VarChar:

1.Variable length memory storage(Changeable)
2.VARCHAR takes up 1 byte per character, + 2 bytes to hold length information
3.varchar when the data entries in a column are expected to vary considerably in size.
4.Ex:
Declare test VarChar(100);
test="Test" -
Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.

Conclusion:

1.When Using the fixed length data's in column like phone number, use Char
2.When using the variable length data's in column like address use VarChar


SQL Database Recovery Expert
Post #1378557
Posted Monday, October 29, 2012 10:20 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 9:44 PM
Points: 888, Visits: 671
pls check below code...
declare @var_chr char(50)='abc'
declare @var_varchr varchar(50)='abc'
select DATALENGTH(@var_chr) chr,DATALENGTH(@var_varchr) var

O/P:chr var
50 3
Post #1378563
Posted Tuesday, October 30, 2012 2:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Or you could construct a query against your table as follows to check total actual string lengths for both scenarios.

Assume the column (Your_Column) in your table (YourTable) is VARCHAR(100):

SELECT VARCHAR=SUM(2 + CAST(DATALENGTH(ISNULL(Your_Column,'')) AS BIGINT))
,CHAR=SUM(CAST(100 AS BIGINT))
FROM YourTable


There's probably also information on this in the sys tables but I don't recall the details.

Edit: Note that this is only an estimate as I'm not 100% sure that NULL values take up the 2 byte length specifier.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1378618
Posted Tuesday, October 30, 2012 10:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
Important to remember:


When someone says to use a certain technique because it is better/faster/more economical, make it a point to understand WHY. Blind faith doesn't take you very far in SQL


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1378888
Posted Tuesday, October 30, 2012 10:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
There are a couple of factors that can make varchar take more space than char of the same length. First is very short strings in a small column. For example, storing "AA" in char(2) takes 2 bytes, but storing it in varchar(2) takes 4 bytes (because of the length data added to variable-length columns).

The other is ANSI padding. It can do weird things to trailing spaces in varchar() columns. Check out this for details: http://msdn.microsoft.com/en-us/library/ms187403.aspx

For fixed-length data, or for very small columns (up to about 10 characters), I use char() instead of varchar(). Unless the data will much more often be very short than very long. Partially, this is due to fixed-length columns not resulting in page splits when updated to longer values. Page splits often result in extra disk allocation being needed, even if only a small percentage of the pages are actually "full", plus they can result in performance issues in busy databases.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1378894
Posted Tuesday, October 30, 2012 11:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 40,177, Visits: 36,578
mike 57299 (10/29/2012)
I tried to change a table from char(xx) to varchar(xx). It seems that it doubled the amount of space used. What gives?


Rebuild the clustered index, the table should drop back down (providing those char columns you changed weren't something like char(2))



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1378897
Posted Wednesday, October 31, 2012 5:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 1,945, Visits: 3,064
I have a database with over 200 tables. They all use char(xx) instead of varchar(xx). I heard that varchar is better because it saves space.


Yes, it matters very much. The length is a constraint on the column and ensures part of the data integrity. Each column needs to be designed separately, based on what it models. This question is like asking "should all numbers be integers less than 100 in my database?"

Look on SQL forums and you will see morons who write crud like:
zip_code VARCHAR(5)
or worse
zip_code NVARCHAR(255)

The guy that does that last one deserves the Unicode Chinese sutras I love to stick in those columns. If he cannot learn SQL, then he learn Zen

The right way is at lest
zip_code CHAR(5) NOT NULL
or better:
zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')
or best, tho this is anal even for me:
zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]'
AND zip_code
BETWEEN '01001' -- Agawam, Massachusetts
AND '99950') --Ketchikan, Alaska

Another favorite is VARCHAR(1); think about it.

I found over-use of VARCHAR(n) comes from C family programmers who think of nul terminated strings and over-use of CHAR(n) comes from COBOL, Fortran, Pascal and BASIC programmers who think of fixed length strings

And do not blindly assign VARCHAR(50) like ACCESS or VARCHAR(255) default lengths. Read your standards! For example, U.S. postal address lines are based on a VARCHAR(35) length; we used to use 10-pitch type on 3.5 inch labels with five lines per label.





Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1379256
Posted Wednesday, October 31, 2012 7:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 40,177, Visits: 36,578
CELKO (10/31/2012)

or better:
zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')


As long as whatever that is is intended for US only, now and always. I just love finding web sites and forms that insist I put a 5-digit post code (mine is 4 digits).



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1379307
Posted Wednesday, October 31, 2012 7:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
GilaMonster (10/31/2012)
CELKO (10/31/2012)

or better:
zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')


As long as whatever that is is intended for US only, now and always. I just love finding web sites and forms that insist I put a 5-digit post code (mine is 4 digits).


Then you have UK post codes which are 5-8 characters long, depending on whether you include a space or not.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1379316
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse