char(??) vs varchar(??)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » char(??) vs varchar(??)

14 posts, Page 1 of 2

1 2 »»

char(??) vs varchar(??)

Rate Topic

Display Mode

Topic Options

Author

Message

mike 57299

Posted Monday, October 29, 2012 9:00 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 12:05 PM
Points: 110, Visits: 256

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

prettsons

Posted Monday, October 29, 2012 10:02 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:17 AM
Points: 99, Visits: 1,023

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 Smile

Post #1378557

subbareddy542

Posted Monday, October 29, 2012 10:20 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:18 PM
Points: 832, Visits: 610

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

dwain.c

Posted Tuesday, October 30, 2012 2:57 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:42 AM
Points: 2,338, Visits: 3,158

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1378618

The Dixie Flatline

Posted Tuesday, October 30, 2012 10:44 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538

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

GSquared

Posted Tuesday, October 30, 2012 10:54 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

GilaMonster

Posted Tuesday, October 30, 2012 11:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

CELKO

Posted Wednesday, October 31, 2012 5:37 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

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 Tongue

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

GilaMonster

Posted Wednesday, October 31, 2012 7:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:10 AM
Points: 37,642, Visits: 29,896

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

Jason-299789

Posted Wednesday, October 31, 2012 7:53 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803, Visits: 2,122

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions