Say I have a table with 3 columns - firstname, middlename, lastname - in that order .. there are also no indexes, and all the fields are varchar.
If I do a full table scan to determine if a given firstname exists in the table.. Will Oracle (or MSSQL) have to read through all the other columns as well? (or is it smart enough to skip over them?).
What about if I'm searching through the 2nd column instead of the 1st one ? Will the first column have to be read? What about if the 1st column is a varchar with close to 2000bytes of data? .. will all the bytes have to be read, or will they somehow be skipped over?
For a programmer the definition of 'read' is probably 'will look at the memory address and read every byte', but for a database this definition is incorrect. In a database 'read' means 'access the disk and fetch the data in memory'. Since both Oracle and SQL Server are page oriented rowstores then yes, all the columns will be 'read'. Things get complicated once you consider off-row storage and such, and things get really muddy if you consider the buffer pool. But for that level of detail you need to express the question in more detail (what you're trying to achieve and why are you asking?) and you need to understand the storage format in great detail as well.
As a side note, both SQL Server and Oracle support columnstore format which is explicitly designed for reading in column oriented fashion (i.e. do not read columns not needed), but it is very unlikely that this is what you want and columnar storage is a very, very, special case.
All data in a data page is read unless it is not queried and stored out of bound. When varchar(max) column exceeds the 8k data it will continue in a new set of pages. These pages are not read when the field is not queried.
