at the breaking point. (in more ways than one)

Question

Post reply

at the breaking point. (in more ways than one)

Viewing 6 posts - 16 through 20 (of 20 total)

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

DavidWalker Mr or Mrs. 500 Points: 508 More actions · Answer 1

This may do what you need.

Create Table Testing(A nvarchar(10) not null,B nvarchar(10) not null,

C int identity)

go

CREATE UNIQUE CLUSTERED INDEX [Testing_INDX] ON [dbo].[Testing]([A],, [C]) ON [PRIMARY]

GO

insert Testing (A,B)values('A','A')

insert Testing (A,B)values('A','A ')

insert Testing (A,B)values('A ','A')

insert Testing (A,B)values('A ','A ')

go

'pling Mr or Mrs. 500 Points: 530 More actions · Answer 2

In DTS you could do an ActiveX transformation of the problem columns along the lines of:

'**********************************************************************

' Visual Basic Transformation Script

' Copy each source column to the

' destination column

'************************************************************************

Function Main()

DTSDestination("INITIALS") = Replace(DTSSource("INITIALS") ," " , "-")

Main = DTSTransformStat_OK

End Function

************************************

I tested this on a column of INITIALS which I knew was plagued by spaces and it tested fine. It might need to be a bit more sophisticated if you have spaces within the string which you don't want changed. You can obviously substitute any other character to the dash used in this example.

sql_servant Old Hand Points: 315 More actions · Answer 3

I'm too lazy to check BOL, but off the top of my head, I believe the rule is that when SQL Server compares two strings (char/varchar) the shorter of the two is padded with spaces to the length of the longer of the two. So you may have a comparison issue as well as a storage issue.

That said, chars and varchars live pretty happily as binarys and varbinarys, and they don't know nothing from spaces and padding. So, if you store them as binarys or cast them as binarys somehow in your load process, SQL should know the difference between 'A' and 'A '.

Good luck.

Chris Hofland

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 4

hey folks, thank you for your various responses. As I had aluded to earlier, the problem here turned out to be with the difference in the way Sybase and SQL Server treat varchar fields when maintaining Unique indexes.

Since SQL Server is bound and determined to treat 'A' and 'A ' as the same value when maintaining a Unique Index, irregardless of the ANSI_PADDING setting, it became necessary (as I had dreaded) to check for duplicates on every entry and modify the data where necessary. DTS was doing it's job just fine. I have it all working now.

Thanks again.

P.S. for those of you following this avidly reading this thread, I thought I'd mention that there were reasons I did not use the last two suggestions that were given..

1. the idea of using a Replace was clever, but could not guarantee that there was not already another record in the data source that had the value that resulted from the Replace(ment) function.

2. I was about to jump on the A, B, C (identity) band-wagon, until I realized that when an identity field is included in a Unique Index, all the other fields are meaningless. Thus, I would lose the reason for my unique index - maintaining data integrity according to our business rules. In this case I could have two records where A=A, B=B, & C<>C, but I don't ever want A and B to be equal.

that's all. Thanks again.

lambje SSCrazy Points: 2055 More actions · Answer 5

Well, hadn't seen the end not until I had played with this awhile. Came across something but it would need a little more work.

Here is my test table where I created the Unique Index off two computed fields:

SET QUOTED_IDENTIFIER ON

go

CREATE TABLE t_test (

id NUMERIC IDENTITY,

col1 VARCHAR(10) NOT NULL,

col2 VARCHAR(10) NOT NULL,

col1c AS REPLACE(IsNull(col1, ''), ' ', '_'),

col2c AS REPLACE(IsNull(col2, ''), ' ', '_'),

CONSTRAINT xak1_t_test UNIQUE CLUSTERED (col1c, col2c),

CONSTRAINT xpk_t_test PRIMARY KEY (id))

go

There is an issue, though, that I didn't take the time to see if there is a workaround. Everytime you insert rows you seem to need to set QUOTED_IDENTIFIER to ON. Perhaps there is a way to change the computed columns to get around this.

SET QUOTED_IDENTIFIER ON

INSERT INTO t_test VALUES ('A', 'A')

INSERT INTO t_test VALUES ('A ', 'A')

INSERT INTO t_test VALUES ('A', 'A ')

go

For what it is worth at this point.

DavidWalker Mr or Mrs. 500 Points: 508 More actions · Answer 6

joshcsmith13, "could have two records where A=A, B=B, & C<>C, but I don't ever want A and B to be equal."

An update/insert trigger could reject these.

lambje :

"col1c AS REPLACE(IsNull(col1, ''), ' ', '_'),

col2c AS REPLACE(IsNull(col2, ''), ' ', '_'),

CONSTRAINT xak1_t_test UNIQUE CLUSTERED (col1c, col2c),

CONSTRAINT xpk_t_test PRIMARY KEY (id))"

I didn't realize that SQL could use computed columns in indices and constraints. Version 7 can't but I guess version 2000 can. I think adding a delimiting character to the end would work as well and you wouldn't have to worry about the replace character being used in the actual data.

Eg.

col1c AS IsNull(col1, '') + '^'),

col2c AS IsNull(col2, '') + '^'),

Probably better to just use the col1c and col2c as the unique constraint. Col1 and Col2 plus identity column if required for the clustered index to ensure index is used for data retrieval.