StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, your <code>IDENTITY</code> (Autonumber in Access) column must be <code>INTEGER</code> (Long Integer in Access) as opposed to <code>UNIQUEIDENTIFIER</code> (Replication ID in Access), must use the increment algorithm as opposed to the random algorithm for generating new values and assuming your seed and increment values respectively are both the default value 1, then if you delete all the rows in the table and compact the database then the <code>IDENITTY</code> column should be reseeded at 1. If it doesn't then you may need to install a Jet service pack (<a href="http://support.microsoft.com/kb/287756" rel="nofollow noreferrer">http://support.microsoft.com/kb/287756</a>).</p> <p>Note that when the maximum positive value for <code>INTEGER</code> (Long Integer in Access) would be exceeded by the next auto-generated value then it will 'wrap' into the negative <code>INTEGER</code> range and will continue to cycle through the positive and negative ranges, generating duplicate values where necessary (unless the column is additionally covered by a unique constraint). Indeed, if the increment value is large enough you can guarantee that the values will alternate between being greater than and less than the previous auto-generated value e.g. (ACE/Jet ANSI-92 Query Mode syntax):</p> <pre><code>CREATE TABLE Test ( ID INTEGER IDENTITY (0, 2147483647), data_col INTEGER NOT NULL UNIQUE ) ; INSERT INTO Test (data_col) VALUES (1) ; INSERT INTO Test (data_col) VALUES (2) ; INSERT INTO Test (data_col) VALUES (3) ; INSERT INTO Test (data_col) VALUES (4) ; INSERT INTO Test (data_col) VALUES (5) ; INSERT INTO Test (data_col) VALUES (6) ; INSERT INTO Test (data_col) VALUES (7) ; INSERT INTO Test (data_col) VALUES (8) ; </code></pre> <p>The auto-generated values are 0, 2147483647, -2, 2147483645, -4, 2147483643, -6, 2147483641, etc.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload