Asked By
eabae16
10 points
N/A
Posted on - 10/24/2011
I want to know about the reading sequence of SQL server that, does a SQL server always read the data from the hard drive put it into the Ram and then work on it?For Example: you have just done a very complex join and after that you will getting data from number of tables (the material is unsorted), then you do an order by to sort it,Does all that data have to be read first in the RAM ,and then SQL does sorting?if YES then what if there are million of rows?Â
Answered By
Jackman
0 points
N/A
#128883
Sequence of reading the data of a SQL Server ?
My answer would be a yes. using the SNAPSHOT Isolation it is very easy to sort rows of data provided it is enabled any time a row of data is updated the SQL server will store the original copy of the data. In something called the tempdb. It also adds transaction sequence numbers to the row, here I mean each of the row. This is the sequence it follows:- after a new transaction is initiated it is given or assigned a transaction sequence number. The transaction engine will read a row within the transactions then it will retrieve a row version in the tempdb which is closer to the transaction number but lower than the transaction sequence number. The database engine will check to see if there is a number closer to the transaction and if it is listed. At this point the engine can read from the tempdb and it will not see the transaction after this one because there numbers are greater. The current transaction will see only the rows that were deleted after the transaction began. The effect of the SNAPSHOT Isolation is that it is able to see all the transactions that were existing at the start of the transaction. This is the ways that help the server to read millions of rows.
