Wednesday, March 25, 2009

New Join Types in SQL Server 2005

CROSS APPLY (equivalent to an inner join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so CROSS APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get no null ColumnName fields because this is equivalent to an inner join, so only objects with matching columns or parameters, in the case of SPROCs and functions, will be returned.

OUTER APPLY (equivalent to a left join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so OUTER APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get any type of object, even non-tables, non-SPROCs, etc, will have their ColumnName fields as null, as this is equivalent to a left join.

Note that any object with no parameter or column will not have an any records in the syscolumns table (eg, SPROCs with no parameters).

1 comment:

LGuevara said...

I used in some queries and there was an improvement of 10seg less against the original code.
Thanks