Refactor/Layout/Convert right outer joins to left
Lxocram
Posts: 25 Bronze 3
I've recently put a DDL trigger on some of my databases to forbid RIGHT OUTER JOINS on function/view/proc creation/change
Why?
Because it means some of my co-developers used a graphical tool again to edit a query with no regards for preserving embedded comments or performance (e.g. with nolock) or debugging (readability)
LEFT OUTER JOIN is just a much easier read-through especially with complex query's where the right outer join condition ('ON ...') is placed far away from the join statement
They can always temporarily disable the trigger or use other circumventions to avoid this when they absolutely NEED a right outer join.
ALTER TRIGGER trgNoRightJoins ON DATABASE
FOR ALTER_VIEW, CREATE_VIEW, ALTER_PROCEDURE, CREATE_PROCEDURE,
ALTER_FUNCTION, CREATE_FUNCTION
AS
DECLARE @data XML
SET @data = EVENTDATA()
IF @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)') LIKE '%RIGHT OUTER JOIN%'
BEGIN
DECLARE @Message VARCHAR(255)
SELECT @message = 'You are forbidden to use right outer joins in view/procedure/function definitions - It probably means you are editing query graphically with no regards for: preserving comments; performance (e.g. with nolock); future query debugging (readability) ... '
RAISERROR ( @Message, 16, 1 )
ROLLBACK ;
END
It would be supersweet if the refactor or layout tool had some option to convert this automagically to drastically improve query readibility. In fact i think this kind of feature would skyrocket sqlprompt sales
Any thoughts from the forum community :?:
Why?
Because it means some of my co-developers used a graphical tool again to edit a query with no regards for preserving embedded comments or performance (e.g. with nolock) or debugging (readability)
LEFT OUTER JOIN is just a much easier read-through especially with complex query's where the right outer join condition ('ON ...') is placed far away from the join statement
They can always temporarily disable the trigger or use other circumventions to avoid this when they absolutely NEED a right outer join.
ALTER TRIGGER trgNoRightJoins ON DATABASE
FOR ALTER_VIEW, CREATE_VIEW, ALTER_PROCEDURE, CREATE_PROCEDURE,
ALTER_FUNCTION, CREATE_FUNCTION
AS
DECLARE @data XML
SET @data = EVENTDATA()
IF @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)') LIKE '%RIGHT OUTER JOIN%'
BEGIN
DECLARE @Message VARCHAR(255)
SELECT @message = 'You are forbidden to use right outer joins in view/procedure/function definitions - It probably means you are editing query graphically with no regards for: preserving comments; performance (e.g. with nolock); future query debugging (readability) ... '
RAISERROR ( @Message, 16, 1 )
ROLLBACK ;
END
It would be supersweet if the refactor or layout tool had some option to convert this automagically to drastically improve query readibility. In fact i think this kind of feature would skyrocket sqlprompt sales
Any thoughts from the forum community :?:
Comments
I might be wrong, but the behaviour of a left out join and a right outer join is different, so switching the type of join (and I presume also the order the tables) in the query wouldn't always return the same result.
However, we have a uservoice site for SQL Prompt feature suggestions, so you might get some interest on there. Here is the link:
http://redgate.uservoice.com/forums/944 ... uggestions