OK, so it's Halloween time and everything, but give me a
break! Here at my organization we've been having this
happen on occasion for a quite a while (maybe for the
last year or so) - old versions of things such as DTS
packages and stored procedures come back and become the
current version of that particular object. Now, we store
our DTS packages here in the msdb database, and we
haven't always deleted old versions of those, so maybe
somehow they are floating back to the top of the version
list somehow. But stored procedures? Just today I went to
check out a stored procedure I had written a while back
so that I could show one of my co-workers the code within
it, and saw that it had mysteriously reverted back to the
way it was before I changed it months ago to add the
aforementioned code. We managed to find the correct
version of the procedure, luckily, in a backup of the
database we had lying around, or I would have had to re-
write it all over again. What is particularly disturbing
about this incident is that the former version of the
stored procedure will execute successfully and return
results that appear correct on the surface. Those results
are only slightly flawed and the damage won't come to
light until much later. Not good!
We have not done any restores that would have turned back
the database to the point before the procedure or package
change; it's just that this reverting action will occur
on its own periodically with no warning.
Has anyone else experienced this? Does anyone know why
it's happening and what can be done about it?
We're running SQL Server 2000 on Windows 2000 Advanced
Server, and the current service pack (SP3a) is applied.
Thank you,
Mark Schmidt
Database Administrator
Pennsylvania House of RepresentativesMark,
Do you at all rename stored procedures? Sp_rename does not change the proc name in create proc in
syscomments and this might be a reason...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Schmidt" <MSchmidt@.pa_housewithouttheunderscore.net> wrote in message
news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
> OK, so it's Halloween time and everything, but give me a
> break! Here at my organization we've been having this
> happen on occasion for a quite a while (maybe for the
> last year or so) - old versions of things such as DTS
> packages and stored procedures come back and become the
> current version of that particular object. Now, we store
> our DTS packages here in the msdb database, and we
> haven't always deleted old versions of those, so maybe
> somehow they are floating back to the top of the version
> list somehow. But stored procedures? Just today I went to
> check out a stored procedure I had written a while back
> so that I could show one of my co-workers the code within
> it, and saw that it had mysteriously reverted back to the
> way it was before I changed it months ago to add the
> aforementioned code. We managed to find the correct
> version of the procedure, luckily, in a backup of the
> database we had lying around, or I would have had to re-
> write it all over again. What is particularly disturbing
> about this incident is that the former version of the
> stored procedure will execute successfully and return
> results that appear correct on the surface. Those results
> are only slightly flawed and the damage won't come to
> light until much later. Not good!
> We have not done any restores that would have turned back
> the database to the point before the procedure or package
> change; it's just that this reverting action will occur
> on its own periodically with no warning.
> Has anyone else experienced this? Does anyone know why
> it's happening and what can be done about it?
> We're running SQL Server 2000 on Windows 2000 Advanced
> Server, and the current service pack (SP3a) is applied.
> Thank you,
> Mark Schmidt
> Database Administrator
> Pennsylvania House of Representatives|||Thank you for your response, Tibor.
No, we haven't renamed any procs to cause this. Actually,
it came to my attention after I posted my question that
the problem with the stored procedure that I mentioned
may very well have been somebody re-creating the
procedure with old code and not realizing it until later.
But, when it comes to DTS packages, we definitely have
had this phenomenon occur several times with no apparent
intervention on anyone's part.
Mark
>--Original Message--
>Mark,
>Do you at all rename stored procedures? Sp_rename does
not change the proc name in create proc in
>syscomments and this might be a reason...
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mark Schmidt"
<MSchmidt@.pa_housewithouttheunderscore.net> wrote in
message
>news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
>> OK, so it's Halloween time and everything, but give me
a
>> break! Here at my organization we've been having this
>> happen on occasion for a quite a while (maybe for the
>> last year or so) - old versions of things such as DTS
>> packages and stored procedures come back and become the
>> current version of that particular object. Now, we
store
>> our DTS packages here in the msdb database, and we
>> haven't always deleted old versions of those, so maybe
>> somehow they are floating back to the top of the
version
>> list somehow. But stored procedures? Just today I went
to
>> check out a stored procedure I had written a while back
>> so that I could show one of my co-workers the code
within
>> it, and saw that it had mysteriously reverted back to
the
>> way it was before I changed it months ago to add the
>> aforementioned code. We managed to find the correct
>> version of the procedure, luckily, in a backup of the
>> database we had lying around, or I would have had to
re-
>> write it all over again. What is particularly
disturbing
>> about this incident is that the former version of the
>> stored procedure will execute successfully and return
>> results that appear correct on the surface. Those
results
>> are only slightly flawed and the damage won't come to
>> light until much later. Not good!
>> We have not done any restores that would have turned
back
>> the database to the point before the procedure or
package
>> change; it's just that this reverting action will occur
>> on its own periodically with no warning.
>> Has anyone else experienced this? Does anyone know why
>> it's happening and what can be done about it?
>> We're running SQL Server 2000 on Windows 2000 Advanced
>> Server, and the current service pack (SP3a) is applied.
>> Thank you,
>> Mark Schmidt
>> Database Administrator
>> Pennsylvania House of Representatives
>
>.
>|||> But, when it comes to DTS packages, we definitely have
> had this phenomenon occur several times with no apparent
> intervention on anyone's part.
I still suspect sp_rename... ;-)
But without a repro, it if of course impossible to say.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Schmidt" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c39e52$ab954820$a001280a@.phx.gbl...
> Thank you for your response, Tibor.
> No, we haven't renamed any procs to cause this. Actually,
> it came to my attention after I posted my question that
> the problem with the stored procedure that I mentioned
> may very well have been somebody re-creating the
> procedure with old code and not realizing it until later.
> But, when it comes to DTS packages, we definitely have
> had this phenomenon occur several times with no apparent
> intervention on anyone's part.
> Mark
> >--Original Message--
> >Mark,
> >
> >Do you at all rename stored procedures? Sp_rename does
> not change the proc name in create proc in
> >syscomments and this might be a reason...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Mark Schmidt"
> <MSchmidt@.pa_housewithouttheunderscore.net> wrote in
> message
> >news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
> >> OK, so it's Halloween time and everything, but give me
> a
> >> break! Here at my organization we've been having this
> >> happen on occasion for a quite a while (maybe for the
> >> last year or so) - old versions of things such as DTS
> >> packages and stored procedures come back and become the
> >> current version of that particular object. Now, we
> store
> >> our DTS packages here in the msdb database, and we
> >> haven't always deleted old versions of those, so maybe
> >> somehow they are floating back to the top of the
> version
> >> list somehow. But stored procedures? Just today I went
> to
> >> check out a stored procedure I had written a while back
> >> so that I could show one of my co-workers the code
> within
> >> it, and saw that it had mysteriously reverted back to
> the
> >> way it was before I changed it months ago to add the
> >> aforementioned code. We managed to find the correct
> >> version of the procedure, luckily, in a backup of the
> >> database we had lying around, or I would have had to
> re-
> >> write it all over again. What is particularly
> disturbing
> >> about this incident is that the former version of the
> >> stored procedure will execute successfully and return
> >> results that appear correct on the surface. Those
> results
> >> are only slightly flawed and the damage won't come to
> >> light until much later. Not good!
> >>
> >> We have not done any restores that would have turned
> back
> >> the database to the point before the procedure or
> package
> >> change; it's just that this reverting action will occur
> >> on its own periodically with no warning.
> >>
> >> Has anyone else experienced this? Does anyone know why
> >> it's happening and what can be done about it?
> >>
> >> We're running SQL Server 2000 on Windows 2000 Advanced
> >> Server, and the current service pack (SP3a) is applied.
> >>
> >> Thank you,
> >>
> >> Mark Schmidt
> >> Database Administrator
> >> Pennsylvania House of Representatives
> >
> >
> >.
> >|||Well, my developers are not telling me that they're doing
renames, and I have to go with that for now.
Thank you very much for the help and for something to
look for, Tibor.
Mark
>--Original Message--
>> But, when it comes to DTS packages, we definitely have
>> had this phenomenon occur several times with no
apparent
>> intervention on anyone's part.
>I still suspect sp_rename... ;-)
>But without a repro, it if of course impossible to say.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mark Schmidt" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0a0f01c39e52$ab954820$a001280a@.phx.gbl...
>> Thank you for your response, Tibor.
>> No, we haven't renamed any procs to cause this.
Actually,
>> it came to my attention after I posted my question that
>> the problem with the stored procedure that I mentioned
>> may very well have been somebody re-creating the
>> procedure with old code and not realizing it until
later.
>> But, when it comes to DTS packages, we definitely have
>> had this phenomenon occur several times with no
apparent
>> intervention on anyone's part.
>> Mark
>> >--Original Message--
>> >Mark,
>> >
>> >Do you at all rename stored procedures? Sp_rename does
>> not change the proc name in create proc in
>> >syscomments and this might be a reason...
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mark Schmidt"
>> <MSchmidt@.pa_housewithouttheunderscore.net> wrote in
>> message
>> >news:00c201c39cbb$bd022e40$a501280a@.phx.gbl...
>> >> OK, so it's Halloween time and everything, but give
me
>> a
>> >> break! Here at my organization we've been having
this
>> >> happen on occasion for a quite a while (maybe for
the
>> >> last year or so) - old versions of things such as
DTS
>> >> packages and stored procedures come back and become
the
>> >> current version of that particular object. Now, we
>> store
>> >> our DTS packages here in the msdb database, and we
>> >> haven't always deleted old versions of those, so
maybe
>> >> somehow they are floating back to the top of the
>> version
>> >> list somehow. But stored procedures? Just today I
went
>> to
>> >> check out a stored procedure I had written a while
back
>> >> so that I could show one of my co-workers the code
>> within
>> >> it, and saw that it had mysteriously reverted back
to
>> the
>> >> way it was before I changed it months ago to add the
>> >> aforementioned code. We managed to find the correct
>> >> version of the procedure, luckily, in a backup of
the
>> >> database we had lying around, or I would have had to
>> re-
>> >> write it all over again. What is particularly
>> disturbing
>> >> about this incident is that the former version of
the
>> >> stored procedure will execute successfully and
return
>> >> results that appear correct on the surface. Those
>> results
>> >> are only slightly flawed and the damage won't come
to
>> >> light until much later. Not good!
>> >>
>> >> We have not done any restores that would have turned
>> back
>> >> the database to the point before the procedure or
>> package
>> >> change; it's just that this reverting action will
occur
>> >> on its own periodically with no warning.
>> >>
>> >> Has anyone else experienced this? Does anyone know
why
>> >> it's happening and what can be done about it?
>> >>
>> >> We're running SQL Server 2000 on Windows 2000
Advanced
>> >> Server, and the current service pack (SP3a) is
applied.
>> >>
>> >> Thank you,
>> >>
>> >> Mark Schmidt
>> >> Database Administrator
>> >> Pennsylvania House of Representatives
>> >
>> >
>> >.
>> >
>
>.
>sql
2012年3月11日星期日
any tips for testing a san?
we finally got our san going here on a test db server.
any tips on testing it?
old disks are raid 5, san is configured raid 5.
so far i've moved some db's data and log files to the san. no problems.
changed the backups to point to the san.
saw significant improvements in backup times.
no improvement on full dbreindex.
nearly all of my databases are small (50mb to 500mb) which sort of
prevents large scale testing. i've got one 5gb db that i'll move over
(should be able to get some good read tests from it). i've also got a
sql stress test tool that i downloaded from somewhere (forget the name
of it though) that inserts millions of records and then reads them.
any other ideas or things to watch out for?when we first implemented SAN we were told to limit size
of individual files to 40gb, since then we have made sure
to set maximum growth of data / log files to 40gb, and add
additional files as needed. I am not sure if this is the
limit of SAN or Windows operating system or sql server.
Out experience is that if you have very large files then
you may have issues restoring them.
We never had any significant issues with SAN, and I am
glad that burden of managing 30+ spindles and few disk
cages is off my shoulders.
hth.
>--Original Message--
>we finally got our san going here on a test db server.
>any tips on testing it?
>old disks are raid 5, san is configured raid 5.
>so far i've moved some db's data and log files to the
san. no problems.
>changed the backups to point to the san.
>saw significant improvements in backup times.
>no improvement on full dbreindex.
>nearly all of my databases are small (50mb to 500mb)
which sort of
>prevents large scale testing. i've got one 5gb db that
i'll move over
>(should be able to get some good read tests from it).
i've also got a
>sql stress test tool that i downloaded from somewhere
(forget the name
>of it though) that inserts millions of records and then
reads them.
>any other ideas or things to watch out for?
>
>.
>
any tips on testing it?
old disks are raid 5, san is configured raid 5.
so far i've moved some db's data and log files to the san. no problems.
changed the backups to point to the san.
saw significant improvements in backup times.
no improvement on full dbreindex.
nearly all of my databases are small (50mb to 500mb) which sort of
prevents large scale testing. i've got one 5gb db that i'll move over
(should be able to get some good read tests from it). i've also got a
sql stress test tool that i downloaded from somewhere (forget the name
of it though) that inserts millions of records and then reads them.
any other ideas or things to watch out for?when we first implemented SAN we were told to limit size
of individual files to 40gb, since then we have made sure
to set maximum growth of data / log files to 40gb, and add
additional files as needed. I am not sure if this is the
limit of SAN or Windows operating system or sql server.
Out experience is that if you have very large files then
you may have issues restoring them.
We never had any significant issues with SAN, and I am
glad that burden of managing 30+ spindles and few disk
cages is off my shoulders.
hth.
>--Original Message--
>we finally got our san going here on a test db server.
>any tips on testing it?
>old disks are raid 5, san is configured raid 5.
>so far i've moved some db's data and log files to the
san. no problems.
>changed the backups to point to the san.
>saw significant improvements in backup times.
>no improvement on full dbreindex.
>nearly all of my databases are small (50mb to 500mb)
which sort of
>prevents large scale testing. i've got one 5gb db that
i'll move over
>(should be able to get some good read tests from it).
i've also got a
>sql stress test tool that i downloaded from somewhere
(forget the name
>of it though) that inserts millions of records and then
reads them.
>any other ideas or things to watch out for?
>
>.
>
2012年2月23日星期四
any installation no-no's?
We are installing MS Win Server 2003 and my old version (7?) of MSDE is not
supported. So I will download MSDE 2000 and install it. I do not have a lot
of experience installing MSDE. I installed it a few times years ago and, with
a few exceptions, it has run just fine since then.
I vaguely remember that I used all the installation defaults with MSDE. That
led to a problem whenever the administrator, or whoever the user was when it
was installed, changed thier password, the connection to the database no
longer worked.
Can anyone suggest a source of information that might be useful to me to
read before I install MSDE 2000?
Thanks for your help, John Brown
John Brown
hi John,
John Brown wrote:
> We are installing MS Win Server 2003 and my old version (7?) of MSDE
> is not supported. So I will download MSDE 2000 and install it. I do
> not have a lot of experience installing MSDE. I installed it a few
> times years ago and, with a few exceptions, it has run just fine
> since then.
> I vaguely remember that I used all the installation defaults with
> MSDE. That led to a problem whenever the administrator, or whoever
> the user was when it was installed, changed thier password, the
> connection to the database no longer worked.
> Can anyone suggest a source of information that might be useful to me
> to read before I install MSDE 2000?
> Thanks for your help, John Brown
modifying the "sa" password was a problem for MSDE 1.0 relating to Windows
account running the SQL Server Agent, but this does not apply to MSDE
2000... and there are not other problems I'm aware of.. but, of course, you
should have a look at the readme file bundled with the installation
package...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
supported. So I will download MSDE 2000 and install it. I do not have a lot
of experience installing MSDE. I installed it a few times years ago and, with
a few exceptions, it has run just fine since then.
I vaguely remember that I used all the installation defaults with MSDE. That
led to a problem whenever the administrator, or whoever the user was when it
was installed, changed thier password, the connection to the database no
longer worked.
Can anyone suggest a source of information that might be useful to me to
read before I install MSDE 2000?
Thanks for your help, John Brown
John Brown
hi John,
John Brown wrote:
> We are installing MS Win Server 2003 and my old version (7?) of MSDE
> is not supported. So I will download MSDE 2000 and install it. I do
> not have a lot of experience installing MSDE. I installed it a few
> times years ago and, with a few exceptions, it has run just fine
> since then.
> I vaguely remember that I used all the installation defaults with
> MSDE. That led to a problem whenever the administrator, or whoever
> the user was when it was installed, changed thier password, the
> connection to the database no longer worked.
> Can anyone suggest a source of information that might be useful to me
> to read before I install MSDE 2000?
> Thanks for your help, John Brown
modifying the "sa" password was a problem for MSDE 1.0 relating to Windows
account running the SQL Server Agent, but this does not apply to MSDE
2000... and there are not other problems I'm aware of.. but, of course, you
should have a look at the readme file bundled with the installation
package...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
2012年2月9日星期四
ANSI vs SQL 92 ambiguity ?!? yeah right
manstein,
I must admit that I did not thoroughly read your posting.
However, the "supposed" ambiguity of the old outer join syntax (such as
*=) is well known and documented. Here is one posting that illustrates
the ambiguity, and highlights this by sharing with the rest of the world
how different RDBMS vendors (at the time) had implemented this syntax:
differently! The posting almost dates back to the last century.
See
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
(url may warp)
Gert-Jan
manstein wrote:
> I have uncovered some interesting techniques that I wanted to share
> with everyone. In particular, they relate to the claimed "ambiguity"
> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
> OUTER JOIN) over *= syntax by Microsoft. A little background would be
> helpful. Microsoft has stated that it will not be supporting the
> older *= syntax into the future. The claim is that it poses ambiguity
> when adding filter logic to the left outer join query. If the filter
> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
> In contrast, filter logic added to the WHERE clause actually turns a
> left outer join into an inner join. Example:
> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
> INSERT INTO #foo (taste) VALUES ('yummy!');
> INSERT INTO #foo (taste) VALUES ('bummy!');
> INSERT INTO #foo (taste) VALUES ('mummy!');
> INSERT INTO #bar (taste) VALUES ('yummy!');
> INSERT INTO #bar (taste) VALUES ('tummy!');
> INSERT INTO #bar (taste) VALUES ('mummy!');
> --This will return all records from dbo.foo with matching records
> from
> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
> will
> --produce NULLS.
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> -- This will only return records from dbo.foo that have a matching
> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
> produces
> -- an inner join styled "filter"
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> This illustration highlights the fact that filter logic in the FROM
> clause is applied before the join is made while filter logic in the
> WHERE clause is applied after the join. This is a characteristic of
> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
> different flavor. The logic in the WHERE clause also preserves our
> LEFT outer join with the addition of filters. Example:
> SELECT
> *
> FROM
> #foo f,
> #bar b
> WHERE
> f.taste *= b.taste
> AND b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> In my opinion, there is no ambiguity at all. If you are familiar with
> the peculiarities of these two competing styles, you can get the
> results you want. Certainly the query and result themselves are not
> ambiguous to the system. Now, since it is sometimes desirable in
> practice to be able to apply filter logic to a relation *after* two
> tables have been left outer joined (in particular, in WHERE clause
> subqueries), I was curious to test the limits of MS claim. What I
> found is that you can have WHERE clause filter logic with ANSI outer
> join operators but with a twist. The trick is to add an additional
> filter which includes NULL values from your joined table. Example:
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!' OR
> b.taste IS NULL
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> Here we get a different result. Tuples matching my filter value
> 'yummy!' are included as well as tuples that have no matching value
> and produce NULL. The twist is rows that do have matching values in
> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
> there is an addition drawback. If #.bar COL1 has an index defined on
> it, the system will perform an index scan as opposed to a seek.
> Nevertheless, this approach can be a significant improvement on an
> order of magnitude better if the only alternative is line - by -line
> processing.
> As of this writing, older SQL 92 syntax is unsupported in SQL Server
> 2005 level 9.0. With MS's history of dealing with competing
> technologies, I suspect there is more than meets the eye with
> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
> but I digress.
My favorite is a table (*) with 5 rows that has NULL for a column. And a query that say col IS NULL
now suddenly returns 8 rows with NULL. Where did the 3 non-existing rows come from? Or a query
saying col IS NOT NULL returning 5 rows with NULL for that column? Or the fact that these queries
doesn't return the same result running on 2000 vs 2005 (with compatibility mode 80).
(*) The table is in fact a view, but a view should behave like a table...
Run below in 2000:
USE pubs
GO
EXEC sp_dbcmptlevel pubs, 80
GO
IF OBJECT_ID('v') IS NOT NULL
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanstende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46366ED0.C1FE40F3@.toomuchspamalready.nl...[vbcol=seagreen]
> manstein,
> I must admit that I did not thoroughly read your posting.
> However, the "supposed" ambiguity of the old outer join syntax (such as
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
> See
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
> (url may warp)
> Gert-Jan
>
>
> manstein wrote:
|||"Gert-Jan Strik" wrote:
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
The argument in this post is specious. It is based entirely on the statement
that some implementations of *= are broken. That argument can be equally
applied against LEFT OUTER JOIN, obviously. The spelling mistakes and
obviously overblown statements make me dismiss it out of hand. According to
the post, *= is "simpoly dead wrong and dangerous". OK c00l d00d.
Maury
I must admit that I did not thoroughly read your posting.
However, the "supposed" ambiguity of the old outer join syntax (such as
*=) is well known and documented. Here is one posting that illustrates
the ambiguity, and highlights this by sharing with the rest of the world
how different RDBMS vendors (at the time) had implemented this syntax:
differently! The posting almost dates back to the last century.
See
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
(url may warp)
Gert-Jan
manstein wrote:
> I have uncovered some interesting techniques that I wanted to share
> with everyone. In particular, they relate to the claimed "ambiguity"
> between using the ANSI outer join operators (LEFT OUTER JOIN, RIGHT
> OUTER JOIN) over *= syntax by Microsoft. A little background would be
> helpful. Microsoft has stated that it will not be supporting the
> older *= syntax into the future. The claim is that it poses ambiguity
> when adding filter logic to the left outer join query. If the filter
> logic is added to the FROM clause, the LEFT OUTER JOIN is preserved.
> In contrast, filter logic added to the WHERE clause actually turns a
> left outer join into an inner join. Example:
> CREATE TABLE #foo (indx INT IDENTITY(1,1), taste VARCHAR(255));
> CREATE TABLE #bar (indx INT IDENTITY(1,1), taste VARCHAR(255));
> INSERT INTO #foo (taste) VALUES ('yummy!');
> INSERT INTO #foo (taste) VALUES ('bummy!');
> INSERT INTO #foo (taste) VALUES ('mummy!');
> INSERT INTO #bar (taste) VALUES ('yummy!');
> INSERT INTO #bar (taste) VALUES ('tummy!');
> INSERT INTO #bar (taste) VALUES ('mummy!');
> --This will return all records from dbo.foo with matching records
> from
> --dbo.bar filtered by b.COL1 = "yummy!" non matching dbo.bar records
> will
> --produce NULLS.
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste AND
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> -- This will only return records from dbo.foo that have a matching
> -- dbo.bar record where b.COL1 = "yummy!" In other words, this
> produces
> -- an inner join styled "filter"
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> This illustration highlights the fact that filter logic in the FROM
> clause is applied before the join is made while filter logic in the
> WHERE clause is applied after the join. This is a characteristic of
> Microsoft's preferred syntax. With the older SQL 92 syntax, we have a
> different flavor. The logic in the WHERE clause also preserves our
> LEFT outer join with the addition of filters. Example:
> SELECT
> *
> FROM
> #foo f,
> #bar b
> WHERE
> f.taste *= b.taste
> AND b.taste = 'yummy!'
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> 3 mummy! NULL NULL
> In my opinion, there is no ambiguity at all. If you are familiar with
> the peculiarities of these two competing styles, you can get the
> results you want. Certainly the query and result themselves are not
> ambiguous to the system. Now, since it is sometimes desirable in
> practice to be able to apply filter logic to a relation *after* two
> tables have been left outer joined (in particular, in WHERE clause
> subqueries), I was curious to test the limits of MS claim. What I
> found is that you can have WHERE clause filter logic with ANSI outer
> join operators but with a twist. The trick is to add an additional
> filter which includes NULL values from your joined table. Example:
> SELECT
> *
> FROM
> #foo f
> LEFT OUTER JOIN #bar b ON b.taste = f.taste
> WHERE
> b.taste = 'yummy!' OR
> b.taste IS NULL
> indx taste indx taste
> -- -- -- --
> 1 yummy! 1 yummy!
> 2 bummy! NULL NULL
> Here we get a different result. Tuples matching my filter value
> 'yummy!' are included as well as tuples that have no matching value
> and produce NULL. The twist is rows that do have matching values in
> #bar that are not filtered for (i.e. 'mummy!') are excluded. Also,
> there is an addition drawback. If #.bar COL1 has an index defined on
> it, the system will perform an index scan as opposed to a seek.
> Nevertheless, this approach can be a significant improvement on an
> order of magnitude better if the only alternative is line - by -line
> processing.
> As of this writing, older SQL 92 syntax is unsupported in SQL Server
> 2005 level 9.0. With MS's history of dealing with competing
> technologies, I suspect there is more than meets the eye with
> Microsoft's witch hunt against *= syntax beyond the claim of ambiguity
> but I digress.
My favorite is a table (*) with 5 rows that has NULL for a column. And a query that say col IS NULL
now suddenly returns 8 rows with NULL. Where did the 3 non-existing rows come from? Or a query
saying col IS NOT NULL returning 5 rows with NULL for that column? Or the fact that these queries
doesn't return the same result running on 2000 vs 2005 (with compatibility mode 80).
(*) The table is in fact a view, but a view should behave like a table...
Run below in 2000:
USE pubs
GO
EXEC sp_dbcmptlevel pubs, 80
GO
IF OBJECT_ID('v') IS NOT NULL
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanstende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46366ED0.C1FE40F3@.toomuchspamalready.nl...[vbcol=seagreen]
> manstein,
> I must admit that I did not thoroughly read your posting.
> However, the "supposed" ambiguity of the old outer join syntax (such as
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
> See
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/518d849c9b1040c0?dmode=source
> (url may warp)
> Gert-Jan
>
>
> manstein wrote:
|||"Gert-Jan Strik" wrote:
> *=) is well known and documented. Here is one posting that illustrates
> the ambiguity, and highlights this by sharing with the rest of the world
> how different RDBMS vendors (at the time) had implemented this syntax:
> differently! The posting almost dates back to the last century.
The argument in this post is specious. It is based entirely on the statement
that some implementations of *= are broken. That argument can be equally
applied against LEFT OUTER JOIN, obviously. The spelling mistakes and
obviously overblown statements make me dismiss it out of hand. According to
the post, *= is "simpoly dead wrong and dangerous". OK c00l d00d.
Maury
Ansi SQL92 question
I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?Versions 2000 SP4 and 2005 SP1|||dunleav1 wrote:
Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.
>
Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,
I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?
In SQL Server, no.
If you on the other hand have lots of code with *= int, there is
all reason to rewrite it. *= is deprecated in SQL 2005, and works
only in compatibility mode 80.
The old OUTER JOIN syntaxes are not portable, and are being deprecated
by vendors. It does not work the same way in Sybase, SQL Server,
Oracle, Informix and Centura among other products. It is also very
limited and you will probably find that you can re-write old code to
great advantage. Here is a cut&paste on the details:
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?Versions 2000 SP4 and 2005 SP1|||dunleav1 wrote:
Quote:
Originally Posted by
I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?
Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Yes, implicit join sytax.
I believe it's a portability issue for me accross database vendors.
It's easier to write the same sql accross vendors. But if development
want to mix and match join syntax accross vendors and products it's
fine with me. As long as it doesn't effect performance.
So for Mssql there is no performance impact for using an implict join
as opposed to the SQL92 standard outer join syntax?|||On Feb 1, 11:14 am, Serge Rielau <srie...@.ca.ibm.comwrote:
Quote:
Originally Posted by
dunleav1 wrote:
Quote:
Originally Posted by
I have an application that uses the old join syntax instead of the
SQL92 standards join syntax.
I need to justify changing the code to the new standard.
Is there any performance issue related to using the old syntax that
are documented?
Are there any other issues that I use to justify a code upgrade?
>
Careful.. when you refer to "old join syntax" in this group readers will
assume you mean TSQL proprietary *= syntax for outer joins and not
"implicit join syntax". For implicit inner joins please see my answer in
c.d.ibm-db2.
If you in fact do outer joins using *= or Oracle style (+) syntax things
are different. Portability would be one reason to abandon proprietary
syntax for ANSI syntax.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge,
I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?
I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).|||dunleav1 (jmd@.dunleavyenterprises.com) writes:
Quote:
Originally Posted by
I believe we are in agreement but let me give you an example about
what I referring to:
sql89: select tab1.col1 from tab1,tab2 where tab1.col1=tab2.col and
tab1.col1 >1;
sql92: select tab1.col1 from tab1 inner join tab2 on
tab1.col1=tab2.col2 where tab1.col1 >1;
Is there a performance impact using one syntax over the other?
In SQL Server, no.
And I would find it difficult to justify to go through all code and
change it to use the newer syntax. (Note that the SQL-89 syntax is
still very much valid.)
However, I tend to rewrite into the newer syntax when I work with old
code, since I find the newer syntax much easier to read and work with.
Quote:
Originally Posted by
I agree it is a good idea to not use proprietary sql extensions such
as (tab1(+) for Oracle or Mssql *=).
If you on the other hand have lots of code with *= int, there is
all reason to rewrite it. *= is deprecated in SQL 2005, and works
only in compatibility mode 80.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>I need to justify changing the code to the new standard. Is there any performance issue related to using the old syntax that are documented? <<
Performance is not the real problem ..
Quote:
Originally Posted by
Quote:
Originally Posted by
>Are there any other issues that I use to justify a code upgrade? <<
The old OUTER JOIN syntaxes are not portable, and are being deprecated
by vendors. It does not work the same way in Sybase, SQL Server,
Oracle, Informix and Centura among other products. It is also very
limited and you will probably find that you can re-write old code to
great advantage. Here is a cut&paste on the details:
Here is how OUTER JOINs work in SQL-92. Assume you are given:
Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z
and the outer join expression:
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition
We call Table1 the "preserved table" and Table2 the "unpreserved
table" in the query. What I am going to give you is a little
different, but equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.
2) If the predicate tests TRUE for that row, then you keep it. You
also remove all rows derived from it from the CROSS JOIN
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.
So let us execute this by hand:
Let @. = passed the first predicate
Let * = passed the second predicate
Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL
the final results:
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL
The basic rule is that every row in the preserved table is represented
in the results in at least one result row.
There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables
Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250
and let's do an extended equality outer join like this:
SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;
If I do the outer first, I get:
Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL
Then I apply the (qty < 200) predicate and get
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
Doing it in the opposite order
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL
Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;
... or do it in the joining:
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;
Another problem is that you cannot show the same table as preserved
and unpreserved in the extended equality version, but it is easy in
SQL-92. For example to find the students who have taken Math 101 and
might have taken Math 102:
SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;
ANSI settings on sql connection
I need to disable my ansi defaults, i could set it off on old asp, but i can't find reference or documentation anywhere about how to disable that on the sqlconnection
does anyone have a clue?Use the following statement first in the batch:
SET ANSI_DEFAULTS ON | OFF
ansi padding issues 64 bit vs 32 bit
Hi all.
I'm using the SQL 2005 partitioning schemes to keep 5 weeks worth of data in a table, swapping in a new week and getting rid of the old week. It works fantastic in a 32 bit environment, when I try it in a 64 bit Itanium cluster, I start having issues with the schema's (specifically the ANSI PADDING) being different between my partitioned table and my "Archive" table, the one that I'm switching the old data out to.
When I created the partitioned table originally, I didn't specify ansi padding at all, so my assumption would be that it would take the default of the database, which is "false". However, when you execute a script in Mgt Studio, the default setting is to have ANSI Padding ON. That's fine, so now my tables are all set to ANSI Padding On when I created them, but the database setting is Off, I can live with that. So how come when I run my SSIS Package in a 32 bit environment, which calls 2 stored procedures to do the partitions switching (inside the 2 stored procedures I have dynamic "Create Table" statements for both the New Week and Archive tables, in order to get them on the proper file groups), these Create table statements apparently create the tables with ANSI Padding set to ON as well, because I don't have a problem. But when I use the exact same SSIS Package with the exact same Stored Procedures in the 64 bit environment, I get the following error?
Error: ALTER TABLE SWITCH statement failed because column 'VendorNum' does not have the same ansi trimming semantics in tables 'ODSTJM.dbo.WeeklyActivityCumulative' and 'ODSTJM.dbo.WeeklyActivityCumulativeArchive'.(42000,50000) Procedure(usp_WACPartitionForArchive), Batch 10 Line 188
If I right click either the New or Archive tables (that are dynamically generated) in Mgt Studio and generate script, these 2 tables both Have the ANSI PADDING set to OFF? Every other table is set to ON. Again, this issue doesn't happen in 32 bit, only 64 bit. Any ideas?
Please help.
Andy
This actually ended up being an issue with DBArtisan. By default, dbArtisan has ansi padding set to off, while SQL 2005 has it set to on in Mgt Studio, which was causing the problem. Lesson learned, don't run from dbArtisan.
订阅：
博文 (Atom)