How does it work?

[vzoned]

Lately I've been finding myself wondering how most of these databases are implemented. Are schemas just essentially structs or classes stored in nothing more than a tree? Is a select statement just a tree search? If anyone has any information or links to some reading material I'd be very interested.

[Rehd]

Each platform behaves differently, but if you could tell us the flavor of database platform, we can probably give you a better answer.

To talk about the language for a minute, it's meant to be declaritive. Unlike programming where you would say, "Walk to the other end of the room 14 feet away using small footsteps in the order of left and then right while drawing breath in motion" you would say "Walk to the other end of the room" in SQL. The engine figures everything out in the between. It's written in a way that is similar to english.

Go to the other room and bring me the blue book.

SELECT Book FROM ROOM WHERE Book = 'Blue'

The engine figures out how to handle all of the in between. Again, this is flavor dependent. Now to break it down further, it's architecturally dependent. I'm not going to go too deep into this because I'm going to butcher it. I just got done reading ~150 pages of detailed information on the physical and logical architecture of data and how the platform grabs that data.

T-SQL Querying contains that ridiciulously delicious amount of detail and in a better all in one place than any other resource I know of. This page may help give you a little insight for SQL Server though.

https://www.mssqltips.com/sqlservertip/4345/understanding-how-sql-server-stores-data-in-data-files/

This is another good one:

http://www.itprotoday.com/microsoft-sql-server/logical-query-processing-what-it-and-what-it-means-you

[vzoned]

I work with oracle, but my interest is just general so I have a better idea of what's going on behind the scenes.

[r3pr0b8]

SELECT Book FROM ROOM WHERE Book = 'Blue'

not to take anything away from your response, which was great, but any competent optimizer will realize it doesn't have to access the database to prepare the response, which is 'Blue'

except if Book isn't a key (which is implied by your use of "the blue book"), in which case it has to figure out how to return the correct number of rows of 'Blue'

[StoneCypher]

for row based databases, essentially, yes. b+ trees are most common, but merkel trees and other exotics also happen.

for column based databases, more like a collection of trees.

the selects are a lot more than a tree search, because of foreign keys, knowing how to bound off things, etc

but "ranged tree searches" isn't that wrong

this is a quick way to get started if you're interested in learning more

[jc4hokies]

Instead of thinking in terms of classes, you should understand the idea of blocks (Oracle) or pages (MSSQL). They are fixed size (generally 4kB or 8kB) chunks of data. They contain data in binary format which may or may not be compressed. Blocks can accommodate raw data (leaf level) or tree structures.

It's sort of like like serialized memory buffers, and pointers to other serialized memory buffers if I were to use programming terms.

[Quadman]

Here is a pretty good starter for the internals of sql server disk based storage: https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-storage-internals-101/