Difference between char and char(1)

Question

Difference between char and char(1)

khushbu

SSCarpal Tunnel

Points: 4591
More actions
September 14, 2011 at 5:09 am

#257173

what is the difference between char and char(1) and which is best to use.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

It's always better to specify the length. Different types have different default lenghts which are also altered be different circumstances. Which is why I don't memorise it. I just declare correctly.

asiaindian SSCrazy Points: 2493 More actions · Answer 2

declare @chr char

declare @chr1 char(1)

select LEN('1234567890abcdefghij0987654321xyz') -- = 33

-- when declared as CHAR or VARCHAR, default length of variable is 1, as shown.

set @chr = '1234567890abcdefghij0987654321xyz'

set @chr1 = '1234567890abcdefghij0987654321xyz'

select @chr

select 'LEN(@chr)', LEN(@chr) -- by default it selects first character.

select @chr1

select 'LEN(@chr1)', LEN(@chr1) -- selects the specified length = 1

-- when CHAR or VARCHAR is used in CONVERT function, default length of variable is 30, as shown.

select 'char', CONVERT(char, '1234567890abcdefghij0987654321xyz')

-- below char(1) will select specified LENGTH of 1.

select 'char(1)', CONVERT(char(1), '1234567890abcdefghij0987654321xyz')

I hope this helps.

Vasu

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 3

Hmmm... You can try these lines to see the difference:

select CONVERT(char, 'This is a SQL Server Central Forum')

select CONVERT(char(1), 'This is a SQL Server Central Forum')

select CONVERT(char(100), 'This is a SQL Server Central Forum')

select CHAR(1)

select CHAR(100)

SkyBVI SSCoach Points: 15128 More actions · Answer 4

Sudhir Dwivedi (9/14/2011)
Hmmm... You can try these lines to see the difference:
select CONVERT(char, 'This is a SQL Server Central Forum')
select CONVERT(char(1), 'This is a SQL Server Central Forum')
select CONVERT(char(100), 'This is a SQL Server Central Forum')
select CHAR(1)
select CHAR(100)

How come the last select CHAR(100)

give output as d ??

plz explain

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

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

SKYBVI (9/14/2011)

How come the last select CHAR(100)
give output as d ??
plz explain
Regards,
Sushant

Here, it is a function.

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 6

Char is a function also, returning ascii character.

Therefore, select Char(100) returns you 'd'

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 7

Many thanks for the wonderful answers.

However I would like to know when we are trying to design a table, what should be preferred and why?

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

Always, always, always, always specify the lengths of your columns. The defaults are not consistent and most people don't know what they are.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

GilaMonster (9/15/2011)
Always, always, always, always specify the lengths of your columns. The defaults are not consistent and most people don't know what they are.

Another reason why I strongly bother to NOT remember them. It forces you to never assume anything.

Moreoever if, and it's possible, MS changes those defaults then you need to know them across all versions too.

Much simpler to just type 4 more characters.