An Alternative to Views?

Question

Hello all,

I am just reading this article and I came across this:

Filter: Remove any functions in the WHERE clause, don't include views in your Transact-SQL code, may need additional indexes.

If I do not use views, what are the alternatives? I mean, in my situation, I want to select some data from a table and then use a few other select queries to work on the subset of data from the first select query?

How can I do this efficiently?

Thanks all

Answer 1

Do it with stored procedures and temp tables

Answer 2

Stores procedures and temp tables are a great choice in some situation, and sometimes is the best way.

But sometimes, you can just do something like this:

SELECT *
FROM (SELECT IdC, Name FROM Customer WHERE ....)

that is, you write a query inside the FROM section of the bigger query. I did not like this at first, but with time I realized that some queries are resolved effortless with this.

HTH (Hope This Helps)!

Answer 3

The article is misleading without some context.

Red Gate have better articles on Execution Plans. And a site search

To address what you listed...

• don't use functions on columns on JOINs and WHERE clauses

This is bad: WHERE DATEADD(day, a, column) = .... Moving the DATEADD to the other side is neutral.

• Views are not inherently evil.

Nested views usually are. Indexed views can be very useful.

See my answer and HLGEMs comment here

• use the DMVs and Data Tuning Advisor to look at indexes

SQL 2005+ gives you far better tuning options

Answer 4

...in my situation, I want to select some data from a table and then use a few other select queries to work on the subset of data from the first select query?

This is a little on the vague side, but it sounds like you might be looking for Common Table Expressions, with which you could construct a query such as:

WITH First_CTE AS
(
    SELECT Col1, Col2, Col3, ...
    FROM Table
    WHERE ...
    GROUP BY ...
),
Second_CTE AS
(
    SELECT Col1, Col2, Col3, ...
    FROM First_CTE
    WHERE ...
    GROUP BY ...
)
SELECT *
FROM Second_CTE
WHERE ...
GROUP BY ...
ORDER BY ...

You can chain together as many CTEs as you want. If you are creating all sorts of ad-hoc views just to make a small handful of queries easier to write, then this would be a better option. But that's a big "if" - it really depends on what you're using the views for.