[RESOLVED] Beginner trying to learn SQLite database

[Sitten Spynne]

I just read up on SQLite and this odd RowId that is exposed to users.

https://www.sqlite.org/autoinc.html

Yikes - this is just horrible. Forget it exists! You won't find it elsewhere (like in MS SQL) and the re-use and assignment algorithms scare the heck out of me!

Same. SQLite is very much like Access: compared to a "real" database like MSSQL there are quite a few backwards things. It's "good enough" for local client databases that don't want to support multiple concurrent users. Get outside that case, and it starts getting foolish to use SQLite. No one sane would back an e-commerce or banking site with it. But a phone application's data? It's a good fit.

jumper77: be careful reading into implementation details. SQLite is documented as it is because it's fully open, and they want everyone to understand how it's supposed to work. But many, many features are intended only for advanced use cases, such as the above linked page's discussion of re-use and other concepts. The takeaway is supposed to be: "Don't try to guess what rowID will be assigned, instead create a row and ask via query."

Storytime: MSSQL is no saint either. At my last job, we drank the TFS kool-aid. It stores lots of things in MSSQL, among them build errors/warnings as part of continuous integration. It turns out that 300 people submitting builds all day, with failed builds producing many thousands of errors/warnings very quickly overflowed the auto-increment ID column used to store those. Apparently that thought never crossed Microsoft's mind. What happened in response?

The entire TFS system broke. No one could check out code, no one could check in code. MS's solution? Basically a "DROP TABLE; CREATE TABLE..." script. It took four days to complete, and failed twice, so we lost two weeks of development effort.

So do make sure, when choosing auto-increment as an ID column, that you're reasonably certain having several billion rows won't be a common case. If it is, you need a better ID scheme. GUIDs come to mind as a good one.