- Why is it less efficient to query tables with large datatypes, such as nvarchar or datetime, than it is to query tables with smaller data types such as varchar or smalldatetime?
- How could queries against a table with a non-clustered index be faster than queries against a table with an identical clustered index?
The answer to these questions, amongst others, is in the way that SQL Server reads from and writes data to physical disks. All data and indexes are stored in 8KB pages, and these pages are the smallest unit that SQL Server can read from or write to disk. Consequently, even if you are reading or writing a single record, SQL Server will still perform the operation on the full 8KB page containing the record. This also means that irrespective of the number of records your query affects, if they all exist on the same page then SQL Server performs the same amount of work. So, how does this answer the above questions?
- The reason that it is less efficient to query tables with larger datatypes is that it results in less data fitting in each page, and so an increase in the number of pages that need to be retrieved to yield the same results.
- Clustered indexes are a physical ordering of the table data, and so the leaf level always contains entire records. Non-Clustered index on the other hand sit apart from the actual data pages and only contain the actual indexed and included fields, which means that each page can contain significantly more records then the equivalent clustered index. If the non-clustered index covers all the fields required by a query then SQL Server can satisfy then query based on just the index pages resulting in less read/write operations.
So as I have said many times; if you remember just one thing, remember how SQL Server stores data and the implications this has on performance.