If i execute a join that uses ordered phyical records, it will be more efficient than a join that does not have the physycal records ordered? References are welcome.
Most database platforms have different algorithms when joining tables. How tables (or streams of data, really) are joined depends on a number of factors, including the number of rows, the ordering of the rows and the type of join (for instance, if it's a so-called "equi-join" or not).
Assuming the following example:
SELECT a.col1, a.col2, a.col3, b.col4, b.col5, b.col6 FROM table_a AS a INNER JOIN table_b AS b ON a.x=b.x AND a.y=b.y;
table_b are indexed on the columns
(x, y), a highly optimal strategy would be to join those two could be to merge those two datastreams (in SQL Server and other platforms known as a Merge Join). This is particularly true if you add
ORDER BY a.x, a.y;, in which case you would want to maintain the ordering from the underlying table.
As a general answer: imagine if your query was two decks of cards in your hands. How would you join them? Would you lay them out in order and merge them (a merge join), perhaps go through one card at a time from one deck and look for matching cards in the other deck (a nested loop join), or maybe subdivide all the cards into 10-15 smaller heaps and join those (more like a hash join)? How would that strategy change if one or both of your decks was ordered or not?
Physical order of records is defined by the clustered index, if it exists. Whatever you requested in a SQL query - it will be returned to you by the server, it is just matter of how many pages in files the server will have to look through in order to find what you requested. There is no single answer on your question, because the answer might be different depending on which other indexes are there, etc. I guess you are asking about "most typical case", then you can compare two examples below.
When you have a regular index on ZipCode:
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, ZipCope VARCHAR(6)) CREATE NONCLUSTERED INDEX IDX_CUSTOMERS_ZIPCODE ON Customers (ZipCope)
and when you have a clustered index on ZipCode:
CREATE TABLE Customers (CustomerID INT, ZipCope VARCHAR(6)) CREATE CLUSTERED INDEX IDX_CUSTOMERS_ZIPCODE ON Customers (ZipCope) CREATE NONCLUSTERED INDEX IDX_CUSTOMERS_ID ON Customers (CustomerID)
Assuming if you have millions of records, and smooth enough distribution of customers across many zipcodes, then it is more likely that if you search for a single zipcode - the server will need to search through less pages, and it will be faster if you had a clustered index based on ZipCode. Keep in mind that it may slow down queries based on other columns, such as CustomerID.