Interesting Query

I have four lookup tables that have identical structure. I have to
write a query to check if a particlaur string (code) exists in any of
the four lookups. What is the best way of dealing with this please?
1. Write one query with four corelated subqueries (one for each
lookup).
2. Write 4 separate queries and execute them one after the other.
If there is better way to store the lookups to make writing queries
like the one I have mentioned, easy, then I can change the lookup
tables.
ThanksIf all you have to test is existence, rather than return any value,
you could try something like:
SELECT CASE WHEN EXISTS(<query table 1> ) THEN 1
WHEN EXISTS(<query table 2> ) THEN 2
WHEN EXISTS(<query table 3> ) THEN 3
WHEN EXISTS(<query table 4> ) THEN 4
ELSE 0
END as Matched
Roy Harvey
Beacon Falls, CT
On 21 Apr 2006 03:43:14 -0700, "S Chapman" <s_chapman47@.hotmail.co.uk>
wrote:

>
>I have four lookup tables that have identical structure. I have to
>write a query to check if a particlaur string (code) exists in any of
>the four lookups. What is the best way of dealing with this please?
>1. Write one query with four corelated subqueries (one for each
>lookup).
>2. Write 4 separate queries and execute them one after the other.
>If there is better way to store the lookups to make writing queries
>like the one I have mentioned, easy, then I can change the lookup
>tables.
>Thanks|||Hi,
I'd rather create four left joins. Usually you need to return a value.
Tomasz B.
"Roy Harvey" wrote:

> If all you have to test is existence, rather than return any value,
> you could try something like:
> SELECT CASE WHEN EXISTS(<query table 1> ) THEN 1
> WHEN EXISTS(<query table 2> ) THEN 2
> WHEN EXISTS(<query table 3> ) THEN 3
> WHEN EXISTS(<query table 4> ) THEN 4
> ELSE 0
> END as Matched
> Roy Harvey
> Beacon Falls, CT
>
> On 21 Apr 2006 03:43:14 -0700, "S Chapman" <s_chapman47@.hotmail.co.uk>
> wrote:
>
>|||>> I have four lookup tables that have identical structure. I have to write
a query to check if a particular string (code) exists in any of the four loo
kups. What is the best way of dealing with this please? <<
The best way is not to split the encoding over four tables. Do you
also keep a personnel table for each employee's weight class?
This is a common design flaw called attribute splitting. You can build
a UNION-ed view and use it.
That view will also help show you that you have the same code with
different definitions in your data model (do you know the Patent Office
story?). Think you don't have this problem? Just wait. Or get the
extra overhead of prevetning it with triggers or other procedural,
proprietary code.
You will also have redundant data (look up a series of articles by Tom
Johnston on non-normal form redundancies).

Intent exclusive lock problem

Hi,
We have an application that works with SQL Server 2000
and has 20 users. All the users reach 4-5 tables most of
the time.They do select/update/insert very frequently to
those 4-5 tables.
The problem is, 2 or 3 times in a day, all the user
applications stuck with showing hourglasses. They all
stuck at the same time.They cannot go normal processing
until we reset the server machine.When the application
hangs,I control the locks in the SQL Server by calling
sp_lock stored procedure. I saw locks on the heavily
used 4-5 tables in the type TAB and MODE IX. No
transaction in the application needs to update or insert
so many rows to these tables at a time (and it actually
does not insert/update that many rows).
I guess the problem is those ıntent exclusive locks.
Since at the time of blocking all the frequently used
tables are IX locked, no one can select them.So the
application stucks there.
But how those IX locks happens? And why they just go
away from the lock tables after a small amount of time?
Thanks in advance...The table IX locks are normal and are required so that pages or rows can be
exclusively locked (X mode). Your problem sounds like a deadlock in your
application code. If you contact PSS they will be able to help you
(http://support.microsoft.com)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Caglar Okat" <gypsybregovic@.yahoo.com> wrote in message
news:337801c3fd5b$ecb7b6e0$a001280a@.phx.gbl...
> Hi,
> We have an application that works with SQL Server 2000
> and has 20 users. All the users reach 4-5 tables most of
> the time.They do select/update/insert very frequently to
> those 4-5 tables.
> The problem is, 2 or 3 times in a day, all the user
> applications stuck with showing hourglasses. They all
> stuck at the same time.They cannot go normal processing
> until we reset the server machine.When the application
> hangs,I control the locks in the SQL Server by calling
> sp_lock stored procedure. I saw locks on the heavily
> used 4-5 tables in the type TAB and MODE IX. No
> transaction in the application needs to update or insert
> so many rows to these tables at a time (and it actually
> does not insert/update that many rows).
> I guess the problem is those ıntent exclusive locks.
> Since at the time of blocking all the frequently used
> tables are IX locked, no one can select them.So the
> application stucks there.
> But how those IX locks happens? And why they just go
> away from the lock tables after a small amount of time?
> Thanks in advance...
>|||Paul S Randal [MS] wrote:
> The table IX locks are normal and are required so that pages or rows can b
e
> exclusively locked (X mode). Your problem sounds like a deadlock in your
> application code. If you contact PSS they will be able to help you
> (http://support.microsoft.com)
> Regards.
One day microsoft customers will recognize, that bothering about locks
is no longer needed in other SQL databases due to MVCC, MVTO, MVRC
mechanisms (Firebird, PostgreSQL, LogicSQL, Informix).
Microsoft solves problems, i do not have without microsoft.
regards, Guido Stepken