CODEWITHSUNDEEP
sql-servert-sql
Abs
Abs

Reputation: 57916

An Alternative to Views?

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

Upvotes: 3

Views: 7353

Answers (4)

Aaronaught
Aaronaught

Reputation: 122624

...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.

Upvotes: 2

gbn
gbn

Reputation: 432210

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

Upvotes: 2

Daniel Dolz
Daniel Dolz

Reputation: 2421

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)!

Upvotes: 5

CaffGeek
CaffGeek

Reputation: 22054

Do it with stored procedures and temp tables

Upvotes: 0

Related Questions