Debian Codegraph @

www/withoutrowid.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Clustered Indexes and the WITHOUT ROWID Optimization</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Clustered Indexes and the WITHOUT ROWID Optimization
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
126 <div class="fancy-toc2"><a href="#syntax">1.1. Syntax</a></div>
127 <div class="fancy-toc2"><a href="#compatibility">1.2. Compatibility</a></div>
128 <div class="fancy-toc2"><a href="#quirks">1.3. Quirks</a></div>
129 <div class="fancy-toc1"><a href="#differences_from_ordinary_rowid_tables">2. Differences From Ordinary Rowid Tables</a></div>
130 <div class="fancy-toc1"><a href="#benefits_of_without_rowid_tables">3. Benefits Of WITHOUT ROWID Tables</a></div>
131 <div class="fancy-toc1"><a href="#when_to_use_without_rowid">4. When To Use WITHOUT ROWID</a></div>
132 <div class="fancy-toc1"><a href="#determining_if_an_existing_table_is_without_rowid">5. Determining If An Existing Table Is WITHOUT ROWID</a></div>
133 </div>
134 </details>
135 </div>
136
137
138
139
140 <h1 id="introduction"><span>1. </span>Introduction</h1>
141
142 <p>By default, every row in SQLite has a special column, usually called the
143 "<a href="lang_createtable.html#rowid">rowid</a>", that uniquely identifies that row within the table. However
144 if the phrase "WITHOUT ROWID" is added to the end of a <a href="lang_createtable.html">CREATE TABLE</a> statement,
145 then the special "rowid" column is omitted. There are sometimes
146 space and performance advantages to omitting the rowid.</p>
147
148 <p>A WITHOUT ROWID table is a table that uses a
149 <a href="https://en.wikipedia.org/wiki/Database_index#Clustered">Clustered Index</a>
150 as the primary key.</p>
151
152 <h2 id="syntax"><span>1.1. </span>Syntax</h2>
153
154 <p>To create a WITHOUT ROWID table, simply add the keywords "WITHOUT ROWID"
155 to the end of the <a href="lang_createtable.html">CREATE TABLE</a> statement. For example:</p>
156
157 <blockquote><pre>
158 CREATE TABLE IF NOT EXISTS wordcount(
159 word TEXT PRIMARY KEY,
160 cnt INTEGER
161 ) <b>WITHOUT ROWID</b>;
162 </pre></blockquote>
163
164 <p>As with all SQL syntax, the case of the keywords does not matter.
165 One can write "WITHOUT rowid" or "without rowid" or "WiThOuT rOwId" and
166 it will mean the same thing.</p>
167
168 <p>Every WITHOUT ROWID table must have a <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a>. An error is raised
169 if a CREATE TABLE statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
170
171 </p><p>In most contexts, the special "rowid" column of normal tables can
172 also be called "oid" or "_rowid_". However, only "rowid" works as
173 the keyword in the CREATE TABLE statement.</p>
174
175 <h2 id="compatibility"><span>1.2. </span>Compatibility</h2>
176
177 <p>SQLite <a href="releaselog/3_8_2.html">version 3.8.2</a> (2013-12-06) or later
178 is necessary in order to use a WITHOUT
179 ROWID table. An attempt to open a database that contains one or more WITHOUT
180 ROWID tables using an earlier version of SQLite will result in a
181 "malformed database schema" error.</p>
182
183 <h2 id="quirks"><span>1.3. </span>Quirks</h2>
184
185 <p>WITHOUT ROWID is found only in SQLite and is not compatible
186 with any other SQL database engine, as far as we know.
187 In an elegant system, all tables would behave as WITHOUT ROWID
188 tables even without the WITHOUT ROWID keyword. However, when SQLite was
189 first designed, it used only integer <a href="lang_createtable.html#rowid">rowids</a> for row keys
190 to simplify the implementation.
191 This approach worked well for many years. But as the demands on
192 SQLite grew, the need for tables in which the PRIMARY KEY really did
193 correspond to the underlying row key grew more acute. The WITHOUT ROWID
194 concept was added
195 in order to meet that need without breaking backwards
196 compatibility with the billions of SQLite databases already in use at
197 the time (circa 2013).
198
199 </p><h1 id="differences_from_ordinary_rowid_tables"><span>2. </span>Differences From Ordinary Rowid Tables</h1>
200
201 <p>The WITHOUT ROWID syntax is an optimization. It provides no new
202 capabilities. Anything that can be done using a WITHOUT ROWID table
203 can also be done in exactly the same way, and exactly the same syntax,
204 using an ordinary rowid table. The only advantage of a WITHOUT ROWID
205 table is that it can sometimes use less disk space and/or perform a little
206 faster than an ordinary rowid table.</p>
207
208 <p>For the most part, ordinary rowid tables and WITHOUT ROWID tables
209 are interchangeable. But there are some additional restrictions on
210 WITHOUT ROWID tables that do not apply to ordinary rowid tables:</p>
211
212 <ol>
213 <li><p>
214 <b>Every WITHOUT ROWID table must have a PRIMARY KEY.</b>
215 An attempt to create a WITHOUT ROWID table without a PRIMARY KEY results
216 in an error.
217
218 </p></li><li><p>
219 <b>The special behaviors associated with "<a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>" do not
220 apply on WITHOUT ROWID tables.</b>
221 In an ordinary table, "INTEGER PRIMARY KEY" means that the column is an
222 alias for the rowid. But since there is no rowid in a WITHOUT ROWID
223 table, that special meaning no longer applies. An "INTEGER PRIMARY KEY"
224 column in a WITHOUT ROWID table works
225 like an "INT PRIMARY KEY" column in an ordinary table: It is a PRIMARY KEY
226 that has integer <a href="datatype3.html#affinity">affinity</a>.
227
228 </p></li><li><p>
229 <b><a href="autoinc.html">AUTOINCREMENT</a> does not work on WITHOUT ROWID tables.</b>
230 The <a href="autoinc.html">AUTOINCREMENT</a> mechanism assumes the presence of a rowid and so it
231 does not work on a WITHOUT ROWID table. An error is raised if the
232 "AUTOINCREMENT" keyword is used in the CREATE TABLE statement for
233 a WITHOUT ROWID table.
234
235 </p></li><li><p>
236 <b>NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT
237 ROWID table.</b>
238 This is in accordance with the SQL standard. Each column of a PRIMARY KEY
239 is supposed to be individually NOT NULL. However, NOT NULL was not enforced
240 on PRIMARY KEY columns by early versions of SQLite due to a bug. By the
241 time that this bug was discovered, so many SQLite databases were already
242 in circulation that the decision was made not to fix this bug for fear of
243 breaking compatibility. So, ordinary rowid tables in SQLite violate the
244 SQL standard and allow NULL values in PRIMARY KEY fields. But WITHOUT ROWID
245 tables do follow the standard and will throw an error on any attempt to
246 insert a NULL into a PRIMARY KEY column.
247
248 </p></li><li><p>
249 <b>The <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> function
250 does not work for WITHOUT ROWID tables.</b>
251 Inserts into a WITHOUT ROWID do not change the value returned by the
252 <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a> function. The <a href="lang_corefunc.html#last_insert_rowid">last_insert_rowid()</a> SQL
253 function is also unaffected since it is just a wrapper around
254 <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a>.
255
256 </p></li><li><p>
257 <b>The <a href="c3ref/blob_open.html">incremental blob I/O</a> mechanism does not work
258 for WITHOUT ROWID tables.</b>
259 Incremental BLOB I/O uses the rowid to create an <a href="c3ref/blob.html">sqlite3_blob</a> object for
260 doing the direct I/O. However, WITHOUT ROWID tables do not have a rowid,
261 and so there is no way to create an <a href="c3ref/blob.html">sqlite3_blob</a> object for a WITHOUT
262 ROWID table.
263
264 </p></li><li><p>
265 <b>The <a href="c3ref/update_hook.html">sqlite3_update_hook()</a> interface does not fire callbacks for changes
266 to a WITHOUT ROWID table.</b>
267 Part of the callback from <a href="c3ref/update_hook.html">sqlite3_update_hook()</a> is the rowid of the table
268 row that has changed. However, WITHOUT ROWID tables do not have a rowid.
269 Hence, the update hook is not invoked when a WITHOUT ROWID table changes.
270 </p></li></ol>
271
272 <a name="bene"></a>
273
274 <h1 id="benefits_of_without_rowid_tables"><span>3. </span>Benefits Of WITHOUT ROWID Tables</h1>
275
276 <p>A WITHOUT ROWID table is an optimization that can reduce storage and
277 processing requirements.
278
279 </p><p>In an ordinary SQLite table, the PRIMARY KEY is really just a
280 <a href="lang_createtable.html#uniqueconst">UNIQUE</a> index. The key used to look up records on disk
281 is the <a href="lang_createtable.html#rowid">rowid</a>.
282 The special "<a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>" column type in ordinary SQLite tables
283 causes the column to be an alias for the rowid, and so an INTEGER PRIMARY
284 KEY is a true PRIMARY KEY. But any other kind of PRIMARY KEYs, including
285 "INT PRIMARY KEY" are just unique indexes in an ordinary rowid table.</p>
286
287 <p>Consider a table (shown below) intended to store a
288 vocabulary of words together with a count of the number of occurrences of
289 each word in some text corpus:
290
291 </p><blockquote><pre>
292 CREATE TABLE IF NOT EXISTS wordcount(
293 word TEXT PRIMARY KEY,
294 cnt INTEGER
295 );
296 </pre></blockquote>
297
298 <p>As an ordinary SQLite table, "wordcount" is implemented as two
299 separate B-Trees. The main table uses the hidden rowid value as the key
300 and stores the "word" and "cnt" columns as data. The "TEXT PRIMARY KEY"
301 phrase of the CREATE TABLE statement
302 causes the creation of an <a href="lang_createindex.html#uniqueidx">unique index</a> on the "word" column. This index is a
303 separate B-Tree that uses "word" and the "rowid" as the key and stores no
304 data at all. Note that the complete text of every "word" is stored twice:
305 once in the main table and again in the index.
306
307 </p><p>Consider querying this table to find the number of occurrences of the
308 word "xsync":
309
310 </p><blockquote><pre>
311 SELECT cnt FROM wordcount WHERE word='xsync';
312 </pre></blockquote>
313
314 <p>This query first has to search the index B-Tree looking for any entry
315 that contains the matching value for "word". When an entry is found in
316 the index, the rowid is extracted and used to search the main table.
317 Then the "cnt" value is read out of the main table and returned. Hence, two
318 separate binary searches are required to fulfill the request.
319
320 </p><p>A WITHOUT ROWID table uses a different data design for the equivalent
321 table.
322
323 </p><blockquote><pre>
324 CREATE TABLE IF NOT EXISTS wordcount(
325 word TEXT PRIMARY KEY,
326 cnt INTEGER
327 ) WITHOUT ROWID;
328 </pre></blockquote>
329
330 <p>In this latter table, there is only a single B-Tree which uses the "word"
331 column as its key and the "cnt" column as its data. (Technicality: the
332 low-level implementation actually stores both "word" and "cnt" in the "key"
333 area of the B-Tree. But unless you are looking at the low-level byte encoding
334 of the database file, that fact is unimportant.) Because there is only
335 a single B-Tree, the text of the "word" column is only stored once in the
336 database. Furthermore, querying the "cnt" value for a specific "word"
337 only involves a single binary search into the main B-Tree, since the "cnt"
338 value can be retrieved directly from the record found by that first search
339 and without the need to do a second binary search on the rowid.
340
341 </p><p>Thus, in some cases, a WITHOUT ROWID table can use about half the amount
342 of disk space and can operate nearly twice as fast. Of course, in a
343 real-world schema, there will typically be secondary indices and/or
344 UNIQUE constraints, and the situation is more complicated. But even then,
345 there can often be space and performance advantages to using WITHOUT ROWID
346 on tables that have non-integer or composite PRIMARY KEYs.
347
348 <a name="wtu"></a>
349
350 </p><h1 id="when_to_use_without_rowid"><span>4. </span>When To Use WITHOUT ROWID</h1>
351
352 <p>The WITHOUT ROWID optimization is likely to be helpful for tables
353 that have non-integer or composite (multi-column) PRIMARY KEYs and that do
354 not store large strings or BLOBs.</p>
355
356 <p>WITHOUT ROWID tables will work correctly (that is to say, they
357 provide the correct answer) for tables with a single INTEGER PRIMARY KEY.
358 However, ordinary rowid tables will run faster in that case.
359 Hence, it is good design
360 to avoid creating WITHOUT ROWID tables with single-column PRIMARY KEYs
361 of type INTEGER.
362
363 </p><p>WITHOUT ROWID tables work best when individual rows are not too large.
364 A good rule-of-thumb is that the average size of a single row in a
365 WITHOUT ROWID table should be less than about 1/20th the size of
366 a database page. That means that rows should not contain more than about
367 50 bytes each for a 1KiB page size or about 200 bytes each for 4KiB
368 page size. WITHOUT ROWID tables will work (in the sense that
369 they get the correct answer) for arbitrarily large rows - up to 2GB in size -
370 but traditional rowid tables tend to work faster for large row sizes.
371 This is because rowid tables are implemented as <a href="fileformat2.html#btree">B*-Trees</a> where
372 all content is stored in the leaves of the tree, whereas WITHOUT ROWID
373 tables are implemented using ordinary B-Trees with content stored on both
374 leaves and intermediate nodes. Storing content in
375 intermediate nodes causes each intermediate node entry to take up more
376 space on the page and thus reduces the fan-out, increasing the search cost.
377
378 </p><p>The "sqlite3_analyzer.exe" utility program, available as source code
379 in the SQLite source tree or as a precompiled binary on the
380 <a href="https://sqlite.org/download.html">SQLite Download page</a>, can be
381 used to measure the average sizes of table rows in an existing SQLite
382 database.</p>
383
384 <p>Note that except for a few corner-case differences detailed above,
385 WITHOUT ROWID tables and rowid tables work the same. They both generate
386 the same answers given the same SQL statements. So it is a simple matter
387 to run experiments on an application, late in the development cycle,
388 to test whether or not the use of WITHOUT ROWID tables will be helpful.
389 A good strategy is to simply not worry about WITHOUT ROWID until near
390 the end of product development, then go back and run tests to see
391 if adding WITHOUT ROWID to tables with non-integer PRIMARY KEYs helps
392 or hurts performance, and retaining the WITHOUT ROWID only in those cases
393 where it helps.
394
395 </p><h1 id="determining_if_an_existing_table_is_without_rowid"><span>5. </span>Determining If An Existing Table Is WITHOUT ROWID</h1>
396
397 <p>A WITHOUT ROWID table returns the same content for
398 <a href="pragma.html#pragma_table_info">PRAGMA table_info</a> and <a href="pragma.html#pragma_table_xinfo">PRAGMA table_xinfo</a> as does an ordinary
399 table. But unlike an ordinary table, a WITHOUT ROWID also
400 responds to the <a href="pragma.html#pragma_index_info">PRAGMA index_info</a> command. The <a href="pragma.html#pragma_index_info">PRAGMA index_info</a>
401 on a WITHOUT ROWID table returns information about the PRIMARY KEY
402 for the table. In this way, the <a href="pragma.html#pragma_index_info">PRAGMA index_info</a> command can be
403 used to unambiguously determine whether a particular table is a
404 WITHOUT ROWID table or an ordinary table - an ordinary table will
405 always return no rows but a WITHOUT ROWID table will always return
406 one or more rows.
407 </p><p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
408