Showing posts with label differences. Show all posts
Showing posts with label differences. Show all posts

Tuesday, February 14, 2012

Column By Column Comparison

SQL Server 2000

I will to compare a normal table with a replicated audit table having
identical columns. I wish to report on the differences between the
data.

How can I loop though a "column list" in TSQL rather than explicitly
naming each column to compare ?

Thanks,
CraigDo you just want to know if there IS a difference or what sort of
difference ?

--For the Whole Table
SELECT checksum_agg(binary_checksum(*)) from SomeTable

--For just the rows
Select binary_checksum(*) from SomeTable

If you need more granualarity, then it would be more work.

HTH, Jens Suessmeyer.|||Jens,

This is not 100% reliable. If the checksum functions are
different, there must be a difference in the tables, but the
converse is not true. The checksum functions can return the
same values on different data, like any hash function.

The only sure way is to compare column by column values.

Steve Kass
Drew University

Jens wrote:
> Do you just want to know if there IS a difference or what sort of
> difference ?
> --For the Whole Table
> SELECT checksum_agg(binary_checksum(*)) from SomeTable
> --For just the rows
> Select binary_checksum(*) from SomeTable
> If you need more granualarity, then it would be more work.
> HTH, Jens Suessmeyer.|||Yes I agree with you, definitely. But as I said, to have more
granularity and more reliabilty you have to use another mechanism which
causes more coding. but as I said, I am with you.

Jens Suessmeyer.|||Thanks for the reply.

I have to interrogate each column and determine which is different and
then report on the difference. I was thinking more in the lines of
looping through the columns collection somehow without having to know
the name of each column ...

Thanks again !|||(csomberg@.dwr.com) writes:
> SQL Server 2000
> I will to compare a normal table with a replicated audit table having
> identical columns. I wish to report on the differences between the
> data.
> How can I loop though a "column list" in TSQL rather than explicitly
> naming each column to compare ?

You can get the columns for a table with

SELECT name FROM syscolumns WHERE id = object_name('tbl')

I would suggest that it is best to generate the SQL statement from client
code, since client languages are better apt for string manipulation. You
can build SQL strings in T-SQL as well, and exeute them with EXEC() or
sp_executesql, but this is bulkier.

If you want to be static, one option is to use a client-language to
generate a stored procedure.

Keep in mind that you cannot just say:

SELECT ...
FROM tbl a
JOIN audit_tbl b ON a.keycol = b.keycol
WHERE a.col1 <> b.col1
AND a.col2 <> b.col2
...

Correct is:

WHERE (a.col1 <> b.col1 OR
a.col1 IS NULL AND b.col1 IS NOT NULL OR
a.col1 IS NOT NULL AND b.col1 IS NULL)
AND

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 14 Oct 2005 21:51:02 +0000 (UTC), Erland Sommarskog wrote:

(snip)
>Keep in mind that you cannot just say:
> SELECT ...
> FROM tbl a
> JOIN audit_tbl b ON a.keycol = b.keycol
> WHERE a.col1 <> b.col1
> AND a.col2 <> b.col2
> ...
>Correct is:
> WHERE (a.col1 <> b.col1 OR
> a.col1 IS NULL AND b.col1 IS NOT NULL OR
> a.col1 IS NOT NULL AND b.col1 IS NULL)
> AND

Hi Erland,

This can be (slightly) simplified to

WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR
NULLIF (b.col1, a.col1) IS NOT NULL)
AND

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> This can be (slightly) simplified to
> WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR
> NULLIF (b.col1, a.col1) IS NOT NULL)
> AND

Simplified in the sense "few characters to type, yes". Else I'm not
sure that I find it simpler. Or it is just that it's Friday night after
a hard and long working week...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 14 Oct 2005 22:14:32 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> This can be (slightly) simplified to
>>
>> WHERE (NULLIF (a.col1, b.col1) IS NOT NULL OR
>> NULLIF (b.col1, a.col1) IS NOT NULL)
>> AND
>Simplified in the sense "few characters to type, yes". Else I'm not
>sure that I find it simpler. Or it is just that it's Friday night after
>a hard and long working week...

Hi Erland,

Well, okay. It's an acquired taste, I'll have to grant you that. :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||SELECT X.a, X.b, ... X.z
FROM
(SELECT a, .b, ... z
FROM AuditData
UNION ALL
SELECT a, b, ... z
FROM BaseData)
AS X (a, b, ... z)
GROUP BY X.a, X.b, ... X.z
HAVING COUNT(*) < 2;

or if we had SQL-92 set operators, you could write:

SELECT *
FROM (SELECT 'B', * FROM BaseData)
EXCEPT
(SELECT 'A' , * FROM AuditData);

or
SELECT *
FROM (SELECT 'A', * FROM AuditData)
EXCEPT
(SELECT 'B' , * FROM BaseData);|||if the table has a PK, there is no need to do tedios column-by-colmn
comparisons:

create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)

create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)

-- rows in t1 that do not have exact match in t2
select * from t1 t
-- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2

-- rows in t2 that do not have exact match in t1
select * from t2 t
-- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2

drop table t1
drop table t2|||Thank you all for the updates and I am sorry I did not get back sooner.

I need to do column bu column comparisons as the "report" has to show
which data elements have changed and from what to what ... yuk.

I do not think I can do this efficiently in SQL so I was thinking of
writing a quick little app to loop through column collections of the
recordset ...

Friday, February 10, 2012

collation question

Hello
We used MSSQL 2000 ENG - enterprise edition on a Windows 2K SP3 server ENG.
What are differences between SQL_Latin1_General_CP1_CI_AS collation
and Latin1_General_CI_AS collation ?
If I compare with COLLATIONPROPERTY function, results are equals.
Is it possible to install an instance with SQL_Latin1_General_CP1_CI_AS
collation ?
How to dow ?
Thanks.
Alain
> What are differences between SQL_Latin1_General_CP1_CI_AS collation
> and Latin1_General_CI_AS collation ?
They compare non-Unicode (varchar/char) data using different rules. The
Windows collation (Latin1_General_CI_AS) converts all non-Unicode data to
Unicode and then uses the same Unicode string comparison rules that it
uses for Unicode data. The benefit is that consistent rules are used for
both Unicode and non-Unicode string comparisons. The SQL collation
(SQL_Latin1_General_CP1_CI_AS) uses legacy SQL "sort order" rules to
compare non-Unicode data, and a different set of sorting rules for
Unicode data (the same Latin1_General rules that are used by
Latin1_General_CI_AS). The benefit of this is backwards compatibility
with older applications that may rely on the legacy SQL sort order for
non-Unicode data.

> Is it possible to install an instance with
SQL_Latin1_General_CP1_CI_AS
> collation ?
Yes. Do an advanced setup, and on the collation selection dialog click
the "SQL collations" radio button at the bottom of the form, then select
"Dictionary order, case insensitive for use with code page 1252". For
an existing instance, run REBUILDM.EXE and select the same.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: collation question
thread-index: AcUv7xA8D1swDrfPQriTPAu9nhc9mQ==
X-WBNR-Posting-Host: 213.56.154.227
From: "=?Utf-8?B?QS5HLg==?=" <AG@.discussions.microsoft.com>
Subject: collation question
Date: Wed, 23 Mar 2005 13:27:06 -0800
Lines: 17
Message-ID: <435980C5-2967-4A89-87E0-E3C6DE18F81F@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383050
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.sqlserver.server
Hello
We used MSSQL 2000 ENG - enterprise edition on a Windows 2K SP3 server
ENG.
What are differences between SQL_Latin1_General_CP1_CI_AS collation
and Latin1_General_CI_AS collation ?
If I compare with COLLATIONPROPERTY function, results are equals.
Is it possible to install an instance with SQL_Latin1_General_CP1_CI_AS
collation ?
How to dow ?
Thanks.
Alain

collation question

Hello
We used MSSQL 2000 ENG - enterprise edition on a Windows 2K SP3 server ENG.
What are differences between SQL_Latin1_General_CP1_CI_AS collation
and Latin1_General_CI_AS collation ?
If I compare with COLLATIONPROPERTY function, results are equals.
Is it possible to install an instance with SQL_Latin1_General_CP1_CI_AS
collation ?
How to dow ?
Thanks.
Alain> What are differences between SQL_Latin1_General_CP1_CI_AS collation
> and Latin1_General_CI_AS collation ?
They compare non-Unicode (varchar/char) data using different rules. The
Windows collation (Latin1_General_CI_AS) converts all non-Unicode data to
Unicode and then uses the same Unicode string comparison rules that it
uses for Unicode data. The benefit is that consistent rules are used for
both Unicode and non-Unicode string comparisons. The SQL collation
(SQL_Latin1_General_CP1_CI_AS) uses legacy SQL "sort order" rules to
compare non-Unicode data, and a different set of sorting rules for
Unicode data (the same Latin1_General rules that are used by
Latin1_General_CI_AS). The benefit of this is backwards compatibility
with older applications that may rely on the legacy SQL sort order for
non-Unicode data.
> Is it possible to install an instance with
SQL_Latin1_General_CP1_CI_AS
> collation ?
Yes. Do an advanced setup, and on the collation selection dialog click
the "SQL collations" radio button at the bottom of the form, then select
"Dictionary order, case insensitive for use with code page 1252". For
an existing instance, run REBUILDM.EXE and select the same.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: collation question
thread-index: AcUv7xA8D1swDrfPQriTPAu9nhc9mQ==X-WBNR-Posting-Host: 213.56.154.227
From: "=?Utf-8?B?QS5HLg==?=" <AG@.discussions.microsoft.com>
Subject: collation question
Date: Wed, 23 Mar 2005 13:27:06 -0800
Lines: 17
Message-ID: <435980C5-2967-4A89-87E0-E3C6DE18F81F@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383050
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.sqlserver.server
Hello
We used MSSQL 2000 ENG - enterprise edition on a Windows 2K SP3 server
ENG.
What are differences between SQL_Latin1_General_CP1_CI_AS collation
and Latin1_General_CI_AS collation ?
If I compare with COLLATIONPROPERTY function, results are equals.
Is it possible to install an instance with SQL_Latin1_General_CP1_CI_AS
collation ?
How to dow ?
Thanks.
Alain

collation question

Hello
We used MSSQL 2000 ENG - enterprise edition on a Windows 2K SP3 server ENG.
What are differences between SQL_Latin1_General_CP1_CI_AS collation
and Latin1_General_CI_AS collation ?
If I compare with COLLATIONPROPERTY function, results are equals.
Is it possible to install an instance with SQL_Latin1_General_CP1_CI_AS
collation ?
How to dow ?
Thanks.
Alain> What are differences between SQL_Latin1_General_CP1_CI_AS collation
> and Latin1_General_CI_AS collation ?
They compare non-Unicode (varchar/char) data using different rules. The
Windows collation (Latin1_General_CI_AS) converts all non-Unicode data to
Unicode and then uses the same Unicode string comparison rules that it
uses for Unicode data. The benefit is that consistent rules are used for
both Unicode and non-Unicode string comparisons. The SQL collation
(SQL_Latin1_General_CP1_CI_AS) uses legacy SQL "sort order" rules to
compare non-Unicode data, and a different set of sorting rules for
Unicode data (the same Latin1_General rules that are used by
Latin1_General_CI_AS). The benefit of this is backwards compatibility
with older applications that may rely on the legacy SQL sort order for
non-Unicode data.

> Is it possible to install an instance with
SQL_Latin1_General_CP1_CI_ASeen">
> collation ?
Yes. Do an advanced setup, and on the collation selection dialog click
the "SQL collations" radio button at the bottom of the form, then select
"Dictionary order, case insensitive for use with code page 1252". For
an existing instance, run REBUILDM.EXE and select the same.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: collation question
thread-index: AcUv7xA8D1swDrfPQriTPAu9nhc9mQ==
X-WBNR-Posting-Host: 213.56.154.227
From: "examnotes" <AG@.discussions.microsoft.com>
Subject: collation question
Date: Wed, 23 Mar 2005 13:27:06 -0800
Lines: 17
Message-ID: <435980C5-2967-4A89-87E0-E3C6DE18F81F@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.server:383050
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
X-Tomcat-NG: microsoft.public.sqlserver.server
Hello
We used MSSQL 2000 ENG - enterprise edition on a Windows 2K SP3 server
ENG.
What are differences between SQL_Latin1_General_CP1_CI_AS collation
and Latin1_General_CI_AS collation ?
If I compare with COLLATIONPROPERTY function, results are equals.
Is it possible to install an instance with SQL_Latin1_General_CP1_CI_AS
collation ?
How to dow ?
Thanks.
Alain