April 26, 2004 at 7:56 pm
I have a table with many columns whose values should exist in the various look up tables. Example...
These are some columns of the "TEST" table...CategoryType, EmployeeType, BillingType, etc.
There are CategoryType, EmployeeType, BillingType tables that hold the values.
I want to enforce values that come from the various look up tables. These tables may contain up to 30 entries each but they will hardly ever change.
How is the best way to approach this? Will triggers have a negative performance hit because there are many look up tables to check?
Thanx Eric
April 27, 2004 at 12:42 am
Why use a trigger?
If I understand you correct create relationships between the various tables.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 28, 2004 at 12:48 am
Eric,
I agree with Frank. I don't understand why you would need a trigger.
Create a Primary Key on your tables with a corresponding Foriegn Key in the other tables. Have a look at Table Relationships in BOL (SQL Server Books Online)
Cheers,
Angela
April 28, 2004 at 7:31 am
Is there any logical limit on the amount of joins you should perform on a query. In my case, there are many lookup tables which i would have to reference (maybe about 8). Would using 8 joins cause any problems with the optimizer?
April 28, 2004 at 7:43 am
As per BOL the max number of table in a SELECT statement is 256.
Here is something from Inside SQL Server 2000
Multiple-Table Joins
According to some folklore, SQL Server "does not optimize" joins of more than four tables. There was some truth to this in earlier versions of SQL Server, but in SQL Server 2000 optimization doesn't happen in the same way at all. There are so many different possible permutations of join strategies and index usage that the query optimizer goes through multiple optimization passes, each considering a larger set of the possible plans. On each pass, it keeps track of the best plan so far and uses a cost estimate to reduce the search in succeeding passes. If your query contains many tables, the query optimizer is likely to interrupt the last pass before completion because the last pass is quite exhaustive. The cheapest plan found will then be used.
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply