Refactor/Layout/Convert right outer joins to left

LxocramLxocram Posts: 25 Bronze 3
edited December 5, 2011 10:23AM in SQL Prompt Previous Versions
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 :p

Any thoughts from the forum community :?:

Comments

Sign In or Register to comment.