Do you remember when we were talking about strict tables? It wasn't that long ago. We were talking about strict tables. I told you that strict was a table modifier and that there were more table modifiers. And I am here to tell you there's one that has to do with row ids.

And you can create a table without row ids and that would be using the without row id table modifier. Let's talk about why in a second. 1st, let me show you how. We're gonna create a table here called kv. So we're gonna create a key value store right here in SQLite.

It can do so many interesting things. Key is going to be text and we'll say that it is the primary key and value is gonna be any because we're gonna make this a strict table by adding that table modifier. Now we can add another modifier without row ID. Every without row ID must declare its own primary key because there's no secret row ID hanging out in the back there. And you also can't use the word auto increment on a without row ID table.

Let's create this. Select star from kv. Now, if we were to insert into kv, let's insert key and the value is just gonna be 1. So we will insert that and if I could ever remember, we could insert that correctly. Select star from kv.

So there is our key and our value. And now if we were to select row ID star from kv, not gonna work. Row ID doesn't exist. The actual primary key is the key column. The way that the data is arranged on disk is the key column.

The clustered index is the key column. You guessed it. Now why would without row ID be a performance optimization? Well, that goes back to the quirk about SQLite's primary keys really just being a secondary key with a unique constraint on it. Because if it were an actual primary key that's how the data would be arranged on disk.

But on a table that has a row ID, when you look up by primary key you go and you find the primary key in the index, and then you have to go over and look up the data based on the row ID. So you have 2 index lookups. When you create a without row ID table, your data is actually arranged on disk by the primary key that you give SQLite. So when you look up by primary key, you do one index lookup because you look it up in the primary key index and the data is down there, so you avoid a second index lookup. So this can be a performance optimization.

When should you use it? You should use it, potentially, if you have string primary keys. If you have an integer primary key, that that primary key is an alias for row ID already, so it's kind of moot. But if you have string or compound primary keys, this could be an optimization. We'll test some of this later, but it's important that you understand that it exists and what it does now and we'll look at the more detailed performance impacts here in a little bit.

We might need to redo this video with graphics in mind and redo the row ID video with graphics in mind.