SqlDbx
You are not logged in.
My company just acquired SqlDbx (great tool) and while trying the most interesting features I think that I found a bug in the visual diff tool (version 3.28.1).
I used for testing it a basic table, with a structure like this:
CREATE TABLE TableName
(
Code char(6) NOT NULL,
Name varchar(100) NOT NULL,
CONSTRAINT PK_TableName PRIMARY KEY (Code)
)
I entered the query "select * from TableName" and connected to two test servers (one SQL Server 2000 and the other SQL Server 2005) that have the same table definition and the same data in the table, but in one server the varchar field was padded with spaces.
After running the diff, it marked the varchar column as changed (yellow) in all rows, either with Ignore Whitespace checked or not (I re-run the diff several times). So I executed a "update TableName set Name=rtrim(Name)" and after this all the differences disappeared.
If this a bug of the current version or I'm missing something?
Regards.
Last edited by alberto.martinez (2009-02-20 07:10:42)
Offline
Currently whitespace can be ignored only when comparing text and it's
not ignored when comparing data. The example you provided is exactly the
reason we implemented it this way. I agree that we should make it clear or maybe
add an option to ignore whitespace for data compare too.
Offline
Currently whitespace can be ignored only when comparing text and it's
not ignored when comparing data. The example you provided is exactly the
reason we implemented it this way. I agree that we should make it clear or maybe
add an option to ignore whitespace for data compare too.
So when making comparisons varchar columns are considered data? Or to put it in another way, ignore whitespace only affect char columns?
Thanks for the response.
Offline
The difference between CHAR and VARCHAR columns is that CHAR columns are right trimmed and VARCHAR columns not. This has nothing to do with compare, this is how data retrieved from the server.
For example if you have:
select cast ('spaces ' as CHAR(20))
you will not have spaces, but
select cast ('spaces ' as VARCHAR(20))
will have spaces. That's why when you compare VARCHAR columns with spaces on right they show
up as different.
Offline
The difference between CHAR and VARCHAR columns is that CHAR columns are right trimmed and VARCHAR columns not. This has nothing to do with compare, this is how data retrieved from the server.
Sorry, but at least in SQL Server is just the opposite, CHAR columns are always right padded up to the column length with spaces since CHAR is a fixed length type. VARCHAR columns can be right trimmed or not depending of SET ANSI_PADDING:
char and varchar: http://msdn.microsoft.com/en-us/library/ms176089.aspx
SET ANSI_PADDING: http://msdn.microsoft.com/en-us/library/ms187403.aspx
using your previous example, if you have:
select cast ('spaces ' as CHAR(20))+'.'
you get 21 characters while if you have:
select cast ('spaces ' as VARCHAR(20))+'.'
you get only 12.
Offline
We are talking about two different things here.
You refer how server handles CHAR and VARCHAR columns
for storage and in expressions. I was referring how SqlDbx
handles CHAR and VARCHAR columns on retrieval.
My point was that SqlDbx right trims all CHAR data and
does not trim VARCHAR data it receives from server
regardless of any server settings.
After thinking a little bit more about data compare I think
we probably should ignore all whitespace when comparing
data when option "ignore whitespace" is on
Offline
We are talking about two different things here.
You refer how server handles CHAR and VARCHAR columns
for storage and in expressions. I was referring how SqlDbx
handles CHAR and VARCHAR columns on retrieval.
Sorry about that, I misunderstood you. Thanks for the response (and the patience).
Offline
After thinking a little bit more about data compare I think
we probably should ignore all whitespace when comparing
data when option "ignore whitespace" is on
Many thanks, I just installed the last version and found that you change it to ignore whitespace also in varchars. I find it very useful since most of the tables we use have varchar columns.
Last edited by alberto.martinez (2009-04-20 12:24:16)
Offline