About Trailing space | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

About Trailing space

Hi, PF queries below:
select *, sales_status_cd from fund_status
where fund_status_cd = ‘c/d ‘
select *, sales_status_cd from fund_status
where fund_status_cd = ‘c/d’ Both of the queries give the same result. Does trailing space have no significance? Regards,
Ashish Ashish Johri
those 2 querries are exactly the same. Is this why they return the same result? Good day,
I was not paying attention they are not the same.
Check the BOL for SET ANSI_PADDING ON|OFF setting when creating the columns
THis is not because of ANSI_PADDING OFF. I did a small experiment as follows: 1) I created one table Foreign_Status having a column Foreign_Status_Cd with ANSI_PADDING OFF. SET ANSI_PADDING OFF
CREATE TABLE [dbo].[Foreign_Status](
Foreign_Status_Cd [char](4)
) ON [PRIMARY] I inserted record as follows; INSERT INTO Foreign_Status values(‘c/d ‘)
INSERT INTO Foreign_Status values(‘c/d’)
INSERT INTO Foreign_Status values(‘c/d ‘) Then when I queried to find out the result SELECT * FROM Foreign_Status where Foreign_Status_Cd = ‘c/d ‘
–All three rows are selected
2)The same I did for ANSI_PADDING ON and got the same result. Where is the fault? Pls suggest me. [V] SET ANSI_PADDING ON
CREATE TABLE [dbo].[Foreign_Status](
Foreign_Status_Cd [char](4)
) ON [PRIMARY] Ashish Johri
May be you are not setting it properly…. Check the following url for correct test code for this… SQL Server 2005 Books Online
http://msdn2.microsoft.com/en-us/library/ms187403.aspx Important:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Sir, Do you mean that If I have trailing spaces in the data then I should remove this? As in the next version of SQL Server ‘Ashish’, ‘Ashish ‘ and ‘Ashish ‘ won’t be considered same. Ashish Johri