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