a very very strange sql bug (?)

I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
Felix
I often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov
|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov

a very very strange sql bug (?)

I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK
2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause
.
> Bojidar Alexandrov

A little help with a query? :-)

Hi everyone,
I'm hoping someone could spare a moment to help me with the following:
I have a table with a forename, surname, username and password column.
There are about 18000 rows in this table.
I would like to copy the value from the forename into the username column
and the surname into the password column for each row
I'm sure this can be done using TSQL or perhaps even straight SQL but I havent
a clue how to do it.
If anyone could advise I would greatly appreciate
Kindest Regards and Thanks in Advance
tce
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
CREATE TABLE Foobar
(username VARCHAR(15) NOT NULL PRIMARY KEY,
password VARCHAR(15) NOT NULL,
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
[vbcol=seagreen]
UPDATE Foobar
SET user_name = fore_name,
password = sur_name;
Now, this will blow up if two or more people have the same name.
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124738964.888566.304500@.g47g2000cwa.googlegr oups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> CREATE TABLE Foobar
> (username VARCHAR(15) NOT NULL PRIMARY KEY,
> password VARCHAR(15) NOT NULL,
> forename VARCHAR(15) NOT NULL,
> surname VARCHAR(15) NOT NULL);
>
> UPDATE Foobar
> SET user_name = fore_name,
> password = sur_name;
> Now, this will blow up if two or more people have the same name.
>

A little help with a query? :-)

Hi everyone,
I'm hoping someone could spare a moment to help me with the following:
I have a table with a forename, surname, username and password column.
There are about 18000 rows in this table.
I would like to copy the value from the forename into the username column
and the surname into the password column for each row
I'm sure this can be done using TSQL or perhaps even straight SQL but I have
nt
a clue how to do it.
If anyone could advise I would greatly appreciate
Kindest Regards and Thanks in Advance
tcePlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
CREATE TABLE Foobar
(username VARCHAR(15) NOT NULL PRIMARY KEY,
password VARCHAR(15) NOT NULL,
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
UPDATE Foobar
SET user_name = fore_name,
password = sur_name;
Now, this will blow up if two or more people have the same name.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124738964.888566.304500@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> CREATE TABLE Foobar
> (username VARCHAR(15) NOT NULL PRIMARY KEY,
> password VARCHAR(15) NOT NULL,
> forename VARCHAR(15) NOT NULL,
> surname VARCHAR(15) NOT NULL);
>
> UPDATE Foobar
> SET user_name = fore_name,
> password = sur_name;
> Now, this will blow up if two or more people have the same name.
>

A little help with a query? :-)

Hi everyone,
I'm hoping someone could spare a moment to help me with the following:
I have a table with a forename, surname, username and password column.
There are about 18000 rows in this table.
I would like to copy the value from the forename into the username column
and the surname into the password column for each row
I'm sure this can be done using TSQL or perhaps even straight SQL but I have
nt
a clue how to do it.
If anyone could advise I would greatly appreciate
Kindest Regards and Thanks in Advance
tcePlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
CREATE TABLE Foobar
(username VARCHAR(15) NOT NULL PRIMARY KEY,
password VARCHAR(15) NOT NULL,
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
[vbcol=seagreen]
UPDATE Foobar
SET user_name = fore_name,
password = sur_name;
Now, this will blow up if two or more people have the same name.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124738964.888566.304500@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> CREATE TABLE Foobar
> (username VARCHAR(15) NOT NULL PRIMARY KEY,
> password VARCHAR(15) NOT NULL,
> forename VARCHAR(15) NOT NULL,
> surname VARCHAR(15) NOT NULL);
>
> UPDATE Foobar
> SET user_name = fore_name,
> password = sur_name;
> Now, this will blow up if two or more people have the same name.
>