best practice question: JOIN criteria vs. WHERE criteria

For example, consider the following queries:

DECLARE @.SomeParam INT
SET @.SomeParam = 44
SELECT *
 FROM TableA A
 JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
 WHERE B.SomeParamColumn = @.SomeParam
SELECT *
 FROM TableA A
 JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @.SomeParam

Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?

Thanks.I checked the estimated execution plan and they were identical when i used a hard coded value instead of a parameter (@.someparam)

however,

when I used a parameter there was suddenly an execution difference.

The second query stored data in a temporary table. Thats something you'd definately want to avoid if you could. It also mis-guessed at the estimated row count.|||What tool did you use to check the execution plans? I checked it out with MS SQL Query Analyzer and saw no differences in execution plan when using a variable. Do you have example sql?

Thanks for the help!|||Always check the plan. And remember the plan *will* change as the stats for the table change. So make sure you've got a representative set of data.

Best approach to sending field names dynamically

Hi,

I have a C# web app that searches my database table using the
following search parameters

Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?

Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.

If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.

Thank you
Maz.(maflatoun@.gmail.com) writes:

Quote:

Originally Posted by

I have a C# web app that searches my database table using the
following search parameters
>
Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?
>
Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.
>
If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.

Yes, these sort of searches are not very easy to do with static SQL.
I have an article on my web site that discusses this topic in detail:
http://www.sommarskog.se/dyn-search.html.

--
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

Before Insert Trigger cancels insert

Is it possible for me to write a before insert trigger which will prevent insert if the data doesn't match specific criteria? What would the syntax look like?SQL Server triggers are always AFTER triggers, but you can rollback the insert.

If some condition causes you to want to fail the insert, you can just rollback the transaction

From Books Online:

USE pubs
IF EXISTS (SELECT name FROM sysobjects
 WHERE name = 'employee_insupd' AND type = 'TR')
 DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @.min_lvl tinyint,
 @.max_lvl tinyint,
 @.emp_lvl tinyint,
 @.job_id smallint
SELECT @.min_lvl = min_lvl,
 @.max_lvl = max_lvl,
 @.emp_lvl = i.job_lvl,
 @.job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
 JOIN jobs j ON j.job_id = i.job_id
IF (@.job_id = 1) and (@.emp_lvl <> 10)
BEGIN
 RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
 ROLLBACK TRANSACTION
END
ELSE
IF NOT (@.emp_lvl BETWEEN @.min_lvl AND @.max_lvl)
BEGIN
 RAISERROR ('The level for job_id:%d should be between %d and %d.',
 16, 1, @.job_id, @.min_lvl, @.max_lvl)
 ROLLBACK TRANSACTION
END

|||I just want to add that SQL Server 2000 introduced "INSTEAD OF" triggers. These triggers fire BEFORE the action. Well, more correctly they fire instead of the updating action.

Terri|||True. The name has put me off, and so I have not really ever used them, but in effect they can be used exactly like a before trigger. Cool.

From a design standpoint I would have preferred a true BEFORE UPDATE (INSERT/DELETE) trigger to save me some work in the trigger if I want the transaction to go through, but this is certainly close.

Thanks again.|||does SQL Server not have a "Create or Replace" clause? i notice in the sample code that there is logic to do just that.

if i write an INSTEAD OF trigger, how do i insert into the same table without causing recursion? or is SQL Server smart enough to avoid that? i haven't seen any examples in the help files for what i want to do.