Walter Meester
HoogkarspelPays-Bas
Information générale
Forum:
Microsoft SQL Server
Versions des environnements
SQL Server:
SQL Server 2000
O.K.
>No, no matter if PRIMARY KEY index is CLUSTERED or not in both cases I have INDEX SEEK.
>Thanks again Walter.
And if you have a regular index you have a TABLE SCAN?
If so, I can only see the difference in the optimizer knowing that at most only one matching record in the other table can exist.
This is important to the query optimizer as the number of records outputted here is exactly the same as the selected records to the left of the LEFT JOIN. The optimizer can use a LOOP LEFT JOIN in this case (a kind of SEEK() equivalent in VFP) to find 0 or 1 matching records for each record in the table on the left, whereas with a regular index the right table has to be analyzed for all matching records (0 - n matching records). Since there are no furter restrictions on the right table, a table scan is performed. If there were restrictions it might use an index and perform a HASH or MERGE JOIN afterwards, but not a LOOP JOIN
Walter,
Précédent
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