deciding between char(n) or varchar(n) for benefit

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

deciding between char(n) or varchar(n) for benefit

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-02-17 : 08:07:00

I have two fields one is nullable another is non nullable
max size of both field is 1 character long.
Now which type should be better to use
May I use char(1) or varchar(1) in both of my fields
I have actually million of records to insert so i am thinking about preformance and storage point of view

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 08:12:59

Well from the docs:


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.

varchar [ ( n | max ) ] 
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

For the column that isn't nullable and If you absolutely only need to store 1 character and that character is a standard ANSCII tileset then use CHAR(1)

VARCHAR(1) is 3 bytes, CHAR(1) is 1 byte

Fort he column that is nullable I'm not sure what happens.


If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-02-17 : 08:19:07

hmmm.From your suggestion i might go for char(1)
Also just suggest if I have a field which can have from 1 to 10 characters in it then char(10) is better or i should use varchar(10)

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-02-17 : 08:22:14

if the data is fixed length, use CHAR. if it's variable length, use VARCHAR

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-02-17 : 08:56:00

Hmmm,
From Russel point of view i might have to go with varchar(n)
One more thing is that will empty string "" is allowed in a char(1)

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 09:04:17

no you can't put the empty string into a CHAR(1)

Example


DECLARE @foo CHAR(1)

SET @foo = ''

SELECT 'a' + @foo + 'b'



DECLARE @bar VARCHAR(1)

SET @bar = ''

SELECT 'a' + @bar + 'b'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 09:07:17

"For the column that is nullable I'm not sure what happens."

There is a single bit, in a bitmap, that defines if the column is NULL, or not.

So for NULL VARCHAR is using its standard "Length" bytes (to indicate a zero length value) but no additional "data value" bytes,
whereas CHAR is using one byte (fixed length). Plus both use a BIT for the Null-ness.

The only real problem with CHAR(1) is if the user can store a space, or an empty string. There is no way to tell the difference.
Whereas a VARCHAR will contain either a Single Space (length=1) or an empty string (length = 0)

Same with CHAR(10). It will always pad with spaces, so you cannot tell if a user stored data with trailing spaces, or not.
Whereas using VARCHAR you get the trailing spaces if the user saved any.

Having said that, I think Web Browsers trim leading and trailing spaced from fields (or is it just from <TEXTAREA>?
Or is it that we have some JavaScript in our APP that trims them before they get anywhere near the DB?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources