September 30, 2009 at 11:43 am
Just looking for some opinions/thoughts on using the ANSI compliant version of DELETE verses the TRANSACT-SQL version. We are using SQL Server 2005 and so either method will work and while I know the benefit of using a more ANSI compliant versiion I was looking for any insights from the veterans out there if there are any Pros/Cons not in the satndard documentation that would be applicable to an all SQL Server only environment that wil not be moving to any other RDBMS any time soon.
Thanks
SQL-2003 Compliant Example from BOL 2005:
-- SQL-2003 Standard subquery
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE SalesPersonID IN
(SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
GO
TRANSACT-SQL Specific Example from BOL 2005:
-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO
Kindest Regards,
Just say No to Facebook!September 30, 2009 at 11:52 am
I always use the From version. Allows for more complex logic, since In only allows comparison of one column at a time unless you get into correlation complexities.
I alias the table I'll be deleting from in the From clause, and then use the alias in the Delete clause. Makes it really clear exactly what's being deleted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2009 at 10:16 pm
Same. If you had to port an app to Oracle for example, then delete queries would be one of the easier conversions. In my experience, ANSI standards only really apply when they are the best way to do something across the board.
October 5, 2009 at 8:23 am
Thanks for the feedback guys.
Kindest Regards,
Just say No to Facebook!Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply