Using GETDATE in an In-Line function

Jeff Mason · March 15th, 2004, 04:44 PM

It is true that views do not allow parameters, but why do you think you need parameters on the view? A view is nothing more than a 'virtual' table, materialized when it is referred to. You don't have parameters on a table so you don't have them on views either. Instead, you have parameters on the queries which manipulate those tables.

Any parameters would be used to restrict the rows returned from the query which uses the view. For example:

Code:

SELECT col1,col2, ...
FROM yourtable
    INNER JOIN yourview
        ON yourtable.somecolumn=yourview.someothercolumn
WHERE yourtable.onecolumn=@FirstParameter
  AND yourview.anothercolumn=@SecondParameter
...etc

I doubt you'll need temp tables, since the view essentially serves that purpose and is a *lot* faster.

T-SQL isn't SQL either.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Gert · March 25th, 2004, 07:15 AM

Quote:

quote:Both HAVING and WHERE are similar, but the WHERE clause selects rows before the grouping operation, and the HAVING clause selects rows (groups actually) after the grouping operation. Since generally speaking a grouping operation is a resource intensive operation, if you can reduce the number of rows going in, the query will have less rows to group which should mean it will run faster.

Quote:

Jeff. I am reading a book by Ken Henderson. He agrees with you that the correct way to select rows is via the WHERE clause. But, he claims that if you do use the HAVING clause to select rows, SQL Server will internally translate the HAVING clause to a WHERE clause, so the performance should be the same. Just thought I would throw this into the discussion :)

Gert

Jeff Mason · March 25th, 2004, 07:42 AM

Would that be "The Guru's Guide to Transact-SQL"?

Very good book, IMO.

I see that he does say, quoting on P19, where he presents an example of a selection that should be done via a WHERE clause but instead is done in a HAVING clause:

Quote:

quote:
... SQL Server recognizes this type of HAVING misuse and translates HAVING into WHERE during query execution. Regardless of whether SQL Server catches errors like these, it's always better to write optimal code in the first place.

Note that Ken characterizes misplaced row selection in a HAVING clause as "misuse" and an "error". For what it is worth, I'd be leery of depending on my tool to cover my mistakes. I doubt there would be any guarantee that this kind of behavior would be supported forever in future versions of SQL Server, nor would there be any in any other product, should porting ever be an issue.

Do it right the first time. :)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Gert · March 25th, 2004, 08:46 AM

The book was "The Guru's Guide to SQL Server Stored Procedures..."

I totally agree with both you and mr Henderson. Just thought I should mention it..

Gert