Walter Meester
HoogkarspelPays-Bas
Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>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).
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement