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.

  • How can I join on a CSV varchar?
  • How can I insert identity manually?
  • How to synchronize two (or n) replication processes for SQL Server databases?
  • SSDT Unit Test:. An error occurred while SQL Server unit testing settings were being read from the configuration file
  • Finding stored procedures having execute permission
  • Create automated installer of VB.Net program + SQL Server & script to create the DB & user
  • 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

  • Differences between Database and Schema using different databases?
  • Speeding up Between Search
  • When no 'Order by' is specified, what order does a query choose for your record set?
  • Listing only the last version of a row in a composite key
  • Add primary key column in SQL table
  • SSRS get meta data of remote report
  • 4 Solutions collect form web for “An Alternative to Views?”

    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

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

    Do it with stored procedures and temp tables

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.