Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

CHAR vs VARCHAR - Trailing spaces

May 28 2012 12:00AM by Madhivanan   

As you know both CHAR and VARCHAR datatypes are used to store characters. While VARCHAR is preferred and used in many times to have data with varying length CHAR datatype is primarily used to have data with Fixed length. Also CHAR datatype will keep trailing spaces if the length of the data is not equal to it's size.

declare @c char(10)
set @c='test'
select @c+'a' as char_data


The result is

char_data
-----------
test      a


Look at the trailing spaces after the test. Because column size is 10 and data size is 4, it keeps 6 trailing spaces. But VARCHAR datatype will not store trailing sapces as you see below

declare @c varchar(10)
set @c='test'
select @c+'a' as varchar_data


Result

varchar_data
------------
testa


But you should be aware that when values are compared in the WHERE clause trailing sapces are ignored for both of these datatyeps. Execute the following code

declare @c char(10)
set @c='test'
select @c+'a' as char_data where @c='test'

GO

declare @c varchar(10)
set @c='test'
select @c+'a' as varchar_data where @c='test'


and see the result

char_data
-----------
test      a


varchar_data
------------
testa


Although @c of type CHAR stores 6 trailing spaces, when compared with 'test', the trailing spaces are ignored The same is true for the following cases too

declare @c char(10)
set @c='test'
select @c+'a' as char_data where @c='test                   '

GO

declare @c varchar(10)
set @c='test'
select @c+'a' as varchar_data where @c='test                          '


The result is

char_data
-----------
test      a


varchar_data
------------
testa

So these trailing spaces may confuse you. You should always keep in mind that trailing spaces are always ingored at WHERE clause during comparision although a column contains trailing spaces or not.

Tags: 


Madhivanan
3 · 39% · 12441
20
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • The main issue with CHAR vs VARCHAR is not comparisons, it's wasted space and performance.

    http://www.sqlskills.com/BLOGS/PAUL/post/How-can-data-type-choice-affect-performance.aspx

    commented on May 28 2012 12:25AM
    Jeff Wharton
    51 · 4% · 1171

Your Comment


Sign Up or Login to post a comment.

"CHAR vs VARCHAR - Trailing spaces" rated 5 out of 5 by 20 readers
CHAR vs VARCHAR - Trailing spaces , 5.0 out of 5 based on 20 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]