Walter Meester
HoogkarspelNetherlands
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>However it suprises me a bit that you would prefer this over the simple approach of using a temp table. I would not consider this alternative if it were only for the reason that it is it is less readable of what is going on (A common problem with CTE's anyways).
>
>OTOH, it is a good example of how to impress other people not familiar with CTEs and implementing a simple problem in a difficult way. (TEST: give the two solutions to any SQL developer and ask them to figure out what is going on).
>
>Walter, as a follow-up, just so you know, subsequent queries against table variables (e.g. your solution) will usually cause a table scan. Obviously, you could create an actual temp table and index it, but you know as well as I do that actual temp tables have a little more overhead.
>
>Now, I know that table scans aren't the end of the world (though from some of your posts, one would think they are), but subsequent queries against CTEs don't do table scans, and will be a little more optimized.
The @invoices table in my example, in the read world of course is a table, which I presume would be indexed adequately. The @weekendings in my solution indeed would be a table variable, Performing a tablescan is neccesary because of the nature of the query. Indexing it would not speed up anything (as far as can see anyways). The real query would be optimized on the invoice date column of the 'real' table.
So to draw any conclusions on optimizing we should compare the two queries on a real invoice table and not on a table variable (as I did for completing the example).
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only