That sounds wrong to me. When I run this in QA
create table drop_me
(texty varchar(10))
insert into drop_me (texty) values ('one')
insert into drop_me (texty) values ('one ')
select '~' + texty + '~' from drop_me
I see that the trailing space is preserved.
BUUUUT, when I say:
select texty, count(*) as freq
from drop_me
group by texty
MSSQL comes back with a single row, w/count = 2.
And then I read this in Celko's _SQL For Smarties_:
"Equality between strings of unequal length is calculated by first
padding out the shorter string with blanks on the right-hand side until
the strings are of the same length. Then they are matched, position for
position, for identical values. If one position fails to match, the
whole equality fails."
I think that's what's going on...
-Roy
-----Original Message-----
From: Discussion of advanced .NET topics.
[mailto:[EMAIL PROTECTED] On Behalf Of Potter, Mark
S.
Sent: Tuesday, October 25, 2005 10:50 AM
To: [email protected]
Subject: Re: [ADVANCED-DOTNET] Primary Key Bug
I think in SQL key fields of type varchar are always trimmed independent
of the ANSI_PADDING option.
If ANSI_PADDING is ON (the default) non key varchar fields will allow
trailing spaces.
-----Original Message-----
From: Discussion of advanced .NET topics.
[mailto:[EMAIL PROTECTED] On Behalf Of Christopher
Reed
Sent: Tuesday, October 25, 2005 1:05 PM
To: [email protected]
Subject: Re: [ADVANCED-DOTNET] Primary Key Bug
It just dawned on me what's probably happening.
If the column length is fixed (type Char), then all data is padded to
fit the column. Thus, "key1" and "key1 " become the same.
Likewise, if the column length is variable (type VarChar), then all data
is trimmed, so "key1 " becomes "key1".
Thus, extra spaces at the end of a string becomes irrelevant.
Christopher Reed
Web Applications Supervisor
Information Technology
City of Lubbock
[EMAIL PROTECTED]
"The oxen are slow, but the earth is patient."
>>> [EMAIL PROTECTED] 11:44:46 am 10/25/2005 >>>
>From a data perspective, what makes it different? It adds nothing to
your data. For a primary key, the first four characters are equivalent.
The space is irrelevant from a data perspective. The space adds nothing
to your data. Thus, the two values are equivalent with respect to the
primary key.
I experimented in SQL Server with a table that has a column called
KeyField that's a Char(10) and set as the primary key. In the first
row, I entered "key1" while in the second row I tried to enter "key1 ".
I received a duplicate key error. So, if this is a bug, then it's in
SQL Server as well.
As a data purist, one should never have any form of ambiguity with
respect to your primary key. The values should be clearly distinct.
Then again, primary key values should never be controlled externally to
begin with, but that's a whole other esoteric discussion for academia.
Christopher Reed
Web Applications Supervisor
Information Technology
City of Lubbock
[EMAIL PROTECTED]
"The oxen are slow, but the earth is patient."
>>> [EMAIL PROTECTED] 10:17:37 am 10/25/2005 >>>
No....that's just another BS excuse. If XML doesn't recognize them as
different then XML is wrong too. The data is different and no software
tool should ever been making assumptions about the data. IT IS A
BUG!!!!!!!!!!!!!!
Message from Christopher Reed
<[EMAIL PROTECTED]>@DISCUSS.DEVELOP.COM received on 10/25/2005
09:51 AM
10/25/2005 09:51 AM
Christopher Reed <[EMAIL PROTECTED]>@DISCUSS.DEVELOP.COM
Please respond to "Discussion of advanced .NET topics."
<[email protected]>
Sent by "Discussion of advanced .NET topics."
<[email protected]>
To: [email protected]
cc:
Subject: Re: [ADVANCED-DOTNET] Primary Key Bug
Let's look at this from another perspective.
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table1>
<Key>key1</Key>
</Table1>
<Table1>
<Key>key1 </Key>
</Table1>
</NewDataSet>
The above is the XML representation of your datatable (in a dataset).
The point is that if you tried to make the Key element unique in the
above XML, it would complain because the spacing is not relevant to an
XML document. Thus, you should always view data within ADO.NET as if it
was represented by a XML file. This is not a bug. This is by design of
XML.
Hope this helps!
Christopher Reed
Web Applications Supervisor
Information Technology
City of Lubbock
[EMAIL PROTECTED]
"The oxen are slow, but the earth is patient."
>>> [EMAIL PROTECTED] 5:00:33 pm 10/24/2005 >>>
Anybody noticed that this code errors? Is this fixed in 2.0?
Module Module1
Sub Main()
Dim DT As New DataTable
DT.Columns.Add("Key", GetType(String))
DT.Rows.Add(New Object() {"key1"})
DT.Rows.Add(New Object() {"key1 "})
DT.PrimaryKey = New DataColumn() {DT.Columns("Key")}
End Sub
End Module
===================================
This list is hosted by DevelopMentor(r) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
===================================
This list is hosted by DevelopMentor(r) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com ===================================
This list is hosted by DevelopMentor(r) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com ===================================
This list is hosted by DevelopMentor(r) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
===================================
This list is hosted by DevelopMentor(r) http://www.develop.com
View archives and manage your subscription(s) at
http://discuss.develop.com
===================================
This list is hosted by DevelopMentor® http://www.develop.com
View archives and manage your subscription(s) at http://discuss.develop.com
