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

VARCHAR vs. CHAR Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 12:57 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Hi,
Is it best to use varchar or char for small text columns, max 5 to 10 chars?
An example is currency where the code can be 3 to 5 chars and it has a pk, fk and clustered index.

Thanks,
Pedro




If you need to work better, try working less...
Post #1376635
Posted Wednesday, October 24, 2012 1:08 PM
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: Yesterday @ 3:59 PM
Points: 3,135, Visits: 11,473
Small text columns over two characters in length should almost aways be varchar instead of char.

The only exception would be when you are absolutely sure that every value will be the same number of characters.



Post #1376637
Posted Wednesday, October 24, 2012 1:21 PM


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 @ 6:30 AM
Points: 42,764, Visits: 35,859
A char(2) takes 2 bytes, a varchar(2) takes 2-4 bytes, A char(3) takes 3 bytes, a varchar(3) takes 2-5 bytes, etc. I'll usually switch around 4-6 characters, unless the column will always have a certain number of characters, in which case it's char.


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 #1376642
Posted Wednesday, October 24, 2012 2:29 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Thanks,
About joins do they have the same performance, since char(2) is always 2 bytes but varchar(2) can have a "dynamic" size?

Pedro




If you need to work better, try working less...
Post #1376672
Posted Wednesday, October 24, 2012 2:36 PM


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 @ 6:30 AM
Points: 42,764, Visits: 35,859
Test and see?

I would guess pretty much comparable.



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 #1376676
Posted Wednesday, October 24, 2012 2:46 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Thanks... right, test...
Just asked since i'm on my tablet and cant test it right away...
First thing to do in the morning.... varchar(5) with different size values in 10.000 rows table joining with a 1.000. See how it does compared to char(5).

Thanks,
Pedro...

PS: dont give a fish to the hungry... teach him how to fish.




If you need to work better, try working less...
Post #1376683
Posted Wednesday, October 24, 2012 3:09 PM


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 @ 6:30 AM
Points: 42,764, Visits: 35,859
Try 10 000 000 and 100 000. Don't test on small numbers of rows, the results will be lost in the measuring error.


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 #1376691
Posted Thursday, October 25, 2012 4:06 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Hi,

Created the main tables with 91.000 rows and the reference tables with 10.000.000 rows.
Same data on both pair of tables.
Ran the queries and the time and IO individually were the same.

But when I ran together the queries:
PRINT '---------------------------------------------'
SELECT s.col1fk from stable_c s inner join ptable_cc p on s.col1fk = p.col1 where p.col1 like 'RAB%'
PRINT '---------------------------------------------'
SELECT s.col1fk from stable_vc s inner join ptable_vc p on s.col1fk = p.col1 where p.col1 like 'RAB%'
PRINT '---------------------------------------------'

sometimes I got:
SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.
---------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(114 row(s) affected)
Table 'stable_c'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ptable_cc'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
---------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(114 row(s) affected)
Table 'stable_vc'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ptable_vc'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 66 ms.
---------------------------------------------
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

The 66ms for the elapsed time on the second query wasn't always there.. most the time it also was 0ms or 1ms...
Is that time given by the WAITs, probably IO, the 2nd query has?

Thanks,
Pedro




If you need to work better, try working less...
Post #1376880
Posted Thursday, October 25, 2012 4:32 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 @ 6:30 AM
Points: 42,764, Visits: 35,859
Probably waits, yes.

3 reads doesn't look like there was much data...



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 #1376894
Posted Thursday, October 25, 2012 5:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
GilaMonster (10/25/2012)
Probably waits, yes.

3 reads doesn't look like there was much data...


That query only returns 114 rows...
Changed the query to return 286k rows and the IO is quite different (on the "detail" table), since CHAR always has 5 bytes....
Table 'stable_c'. Scan count 1, logical reads 1037, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ptable_cc'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'stable_vc'. Scan count 1, logical reads 639, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ptable_vc'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Same CPU time...
I made the column 5 bytes long, but I inserted data from 1 to 5 bytes... that's why char has more reads than varchar...
The execution plan states that the char has a cost of 47% and varchar of 53%... The merge join on char is 37% and on varchar is 44%.

Thanks,
Pedro






If you need to work better, try working less...
Post #1376920
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse