at the breaking point. (in more ways than one)
-
I plead with the DB gurus out there for assistance. I have just encountered what would appear to be a severe problem in converting some old Sybase SQLAnywhere 5.5 data to SQL2000. The Sybase table had a PK on two text fields. SQL2000 now uses an identity field for the PK, and has a Unique Index to maintain the integrity of the data as it's copied over.
Here's where the "Breaking point" comes in. It would appear that Sybase didn't care diddly that two rows in my table were differentiated by a mere ' ' at the end of the second text field in the PK. NOW, SQL Server on the other hand seems to not like this configuration and insists on telling me that I am inserting "duplicate key data" when I attempt to insert into SQL2000.
Appearantly the 'breaking point' when doing string comparisons on a varchar field in SQL2000 is immediately after the last non-blank character. This is not good, since my Sybase data is bound to have countless rows (in countless tables) with this exact same problem. What can I do? Is there some way to tell SQL2000 that 'A ' is different than 'A' when maintaining the index, so I don't have to modify the incoming data?? Any help is appreciated - preferably before I break.
Thanks!
-
See online help for ANSI_PADDING
-
Played with the problem:
Adding a length column for the two PK columns.
Ok assumption no spaces in front
Create Table Testing(A varchar(10) not null,B varchar(10) not null,
C tinyint not null default 0,D tinyint not null default 0)
go
CREATE UNIQUE CLUSTERED INDEX [Testing_INDX] ON [dbo].[Testing]([A], , [C], [D]) ON [PRIMARY]
GO
insert Testing values('A','A',1,1)
insert Testing values('A','A ',1,2)
insert Testing values('A ','A',2,1)
insert Testing values('A ','A ',2,2)
Go
Select '!'+A+'!','!'+B+'!','Equal '+Case When Reverse(A)=Reverse(B) Then 'Yes' Else 'No' End From Testing
Go
-
intersting idea 5409045121009 (nice name
), but even this seems to have problems to me. You inserted the lengths because you knew them, however if you do a select len(A) from Testing, in your example, every row comes back with a len() = 1 !! that's great. even select * from testing where A = 'A' seems to ignore the blanks (for me anyways) I'm still looking into the ANSI_PADDING idea though... Why wouldn't it be defaulted to ON, if the BOL strongly suggests that it always be ON.??? -
so far, I'm not having any luck with ANSI_PADDING. I have tried setting it to on (after figuring out that you have to include an 'alter database' if you really want it to take effect
) and recreating the column in question, but the Unique Index still does not seem to accept the "duplicate" data in two rows ('613' and '613 '). does the Index care about the ANSI_PADDING setting? It doesn't seem to. -
where is Antares when you need him...?
-
BOL 2000:
LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
DATALENGTH
Returns the number of bytes used to represent any expression.
Select len('A '),DataLength('A ')
----------- -----------
1 2
-
And a voice from the heavens boomed saying...
"Huh, wha? Oh, are you talking to me."
Sorry just couldn't help myself.
5409045121009 is right on the mark. I tested myself. The question I have thou is how is the connection or the data moved between servers. And did you remember to disconnect then reconnect the connection from the SQL Server if was active before the settings change? I found that got me the first time I needed to do this as the settings are placed on the connection at the moment they connect to that DB and remain in effect until disconnect.
Edited by - antares686 on 03/04/2003 04:13:33 AM
-
antares, I suspect that you are referring to disconnecting and reconnecting after setting the ANSI_PADDING. right?
I must be screaming now. IT DOESN'T WORK! I have SET (stupid)ANSI_PADDING ON and it does not affect the behavior of my Unique Index. this example is exaclty (or close enough for hand-grenades) what I am trying to accomplish with the following 2 rows:
A B
--- ----
1, '613'
1, '613 '
create unique index UQ_test on Test (A, B).
The values are very obviously saved as indicated, one with the trailing blank, and one without. HOWEVER, I have had no success when attempting to add the Unique index. The suggestion of adding a column to my unique index that keeps the DATALENGTH of my varchar column seems like it would work, but this is much more overhead than I want to invest in this issue.
-
Alternative is a form of character stuffing
but if will depends on your app / enivronment, 2 byte extra per row, instead trigger to add one char, etc. Any rate here the story!Drop Table testing
go
Create Table Testing(A varchar(10) not null,B varchar(10) not null)
go
CREATE UNIQUE CLUSTERED INDEX [Testing_INDX] ON [dbo].[Testing]([A], ) ON [PRIMARY]
GO
insert Testing values('A','A')
insert Testing values('A','A'+Char(160))
insert Testing values('A'+Char(160),'A')
insert Testing values('A'+Char(160),'A'+Char(160))
Go
Select '!'+A+'!','!'+B+'!',
'Equal '+Case When A=B Then 'Yes' Else 'No' End
From Testing
Go
drop table Testing
go
Set ansi_padding off
go
create table Testing(A varchar(10),B varchar(10))
go
insert Testing values('A','A')
insert Testing values('A','A ')
insert Testing values('A ','A')
insert Testing values('A ','A ')
Go
Select '!'+A+'!','!'+B+'!','Equal '+Case When A=B Then 'Yes' Else 'No' End From Testing
Go
drop table Testing
go
-
wow. this is getting ugly. I think I'm just going to stick with the tried and true Data-Massage therapy I had been applying in some similar import situations, so that I don't have to deal with this problem after the data is loaded. So, does everyone agree that this is expected/only behavior for a Unique Index on a varchar field, to prohibit entry of two rows distinguised by only a trailing blank/space?
-
I tried everything I can think of with direct inserts and I cannot get my server to duplicate the issues you are having that way.
How are you moving the data, it may be the process that moves it is trimming the data in some way?
As for Unique Index it does not cause me an issue so either we are overlooking something here or again the move process is trimming it for us and thus the blanks are not there as we thought. Unfortunately my machine is acting odd so I have to reboot now. I will try to test when comes back up.
-
borrowing from 5409045121009's script, and using the DATABASEPROPERTYEX function to check my ANSI_PADDING setting, here's my test script:
select DATABASEPROPERTYEX('ISIS_PRO' , 'IsAnsiPaddingEnabled')
go
Create Table Testing(A varchar(10) not null,B varchar(10) not null)
go
CREATE UNIQUE INDEX [Testing_INDX] ON [dbo].[Testing]([A], ) ON [PRIMARY]
GO
insert Testing values('A','A')
insert Testing values('A','A ')
here's the results:
(No Column Name)
------------------------
1
(1 row(s) affected)
(1 row(s) affected)
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'Testing' with unique index 'Testing_INDX'.
The statement has been terminated.
-
Nevermind I must be smoking today as my brain just didn't pick up the index from previous. No I can now confirm, as a friend of mine would say "I Am a Frickin Idiot"
, it will not allow you to add a Unique index on a column based on ANSI PADDING differences. At least not that I can find.Sorry for sleeping while typing.
-
your confirmation is all I need. thanks. moving on...
), but even this seems to have problems to me. You inserted the lengths because you knew them, however if you do a select len(A) from Testing, in your example, every row comes back with a len() = 1 !! that's great. even select * from testing where A = 'A' seems to ignore the blanks (for me anyways) I'm still looking into the ANSI_PADDING idea though... Why wouldn't it be defaulted to ON, if the BOL strongly suggests that it always be ON.???
) and recreating the column in question, but the Unique Index still does not seem to accept the "duplicate" data in two rows ('613' and '613 '). does the Index care about the ANSI_PADDING setting? It doesn't seem to.
but if will depends on your app / enivronment, 2 byte extra per row, instead trigger to add one char, etc. Any rate here the story!Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply