Thanks for that.
So if I can then I should create the table with INTEGER PRIMARY KEY.
Is it right that this won't affect the speed of any subsequent inserts or
deletes?
About the single quotes etc:
This is VB code, so I can't do:
Create table "table1"("ID" INTEGER PRIMARY KEY)
I can do:
Create table table1(ID INTEGER PRIMARY KEY)
As the table and the columns are often variables it will be something like:
strTable = "table1"
strColumn = "ID"
strSQL = "create " & strTable & "(" & strColumn & " INTEGER PRIMARY KEY)"
RBS
-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 27 March 2007 22:51
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Difference in these indices?
RB Smissaert wrote:
> Is there any difference in an index created like this:
>
> Create table 'table1'([ID] INTEGER PRIMARY KEY)
>
> with this:
>
> Create table 'table1'([ID] INTEGER)
>
> Create unique index idx_table1_ID on table1(ID)
>
> I tended to use the first form, but as that can make subsequent table
> inserts or deletes slower I am now moving to the second form.
>
>
Yes there is. The first uses the key for the btree that stores the table
to hold the id. The second uses a second comlpetely independent btree to
store an index that holds records that contain the id number and the
rowid of the corresponding record in the table. The table itself
contains a rowid as the key of the table btree and the user id field.
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER PRIMARY KEY);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER PRIMARY KEY)
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> Create table 'table1'([ID] INTEGER);
sqlite> Create unique index idx_table1_ID on table1(ID);
sqlite> select * from sqlite_master;
table|table1|table1|2|CREATE TABLE 'table1'([ID] INTEGER)
index|idx_table1_ID|table1|3|CREATE UNIQUE INDEX idx_table1_ID on table1(ID)
The first version stores on integer for each record, and stores it in
the btree key. The second stores four integers for each record, two in
the table record and two in the index record. You are making your
database much larger for no reason.
Also, you are using literal strings (delimited with a single quote) for
your table names. This is not standard SQL and will not be portable. You
are also using square brackets to quote your column names. This is also
an SQLite and MS extension to standard SQL. You should quote identifiers
such as table and column names with double quotes.
Create table 'table1'([ID] INTEGER PRIMARY KEY)
should be:
Create table "table1"("ID" INTEGER PRIMARY KEY)
HTH
Dennis Cote
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
