SQL Server Performance Forum – Threads Archive

About Trailing space

Hi, PF queries below:
1)
select *, sales_status_cd from fund_status
where fund_status_cd = ‘c/d ‘
2)
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,
Bulent

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
GO
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
GO
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
SET ANSI_PADDING
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.
MohammedU.
Moderator
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

]]>