Hello,

I have a table with 1 million entries and wanted to remove the ROWID to use my primary key instead.

sqlite3.dll version 3.37.0

I made the change in the software SQLite expert, through the UI (checking the box "Without ROWID").

My table looks like: CREATE TABLE DYN_joueur ([player_id] INTEGER PRIMARY KEY NOT NULL, [joueur_nom] nvarchar(40), [joueur_prenom] nvarchar(40)) WITHOUT ROWID;

There are gaps in my player_id because some have been deleted, I have: 1,2,3,4,7,8

I'm saving space as expected. But when running a: SELECT * FROM DYN_joueur with a SQLiteDataReader, some items (around the gaps) are coming twice in the loop. The problem disappears when I reactivate the ROWID.

using (SQLiteCommand command = source.CreateCommand()) { command.CommandText = "SELECT player_id, joueur_nom, joueur_prenom FROM DYN_joueur"; using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { using (SQLiteCommand commandUpdate = source.CreateCommand()) { int id = System.Convert.ToInt32(reader["player_id"]); // ID 7 appears twice in the loop } } reader.Close(); } }

I am not able to reproduce the problem in SQLite Expert or SQLite Studio, both show only one entry as expect when querying the db. If I reactivate the ROWID i see that the ROWID matches my primary key (so there is a gap in row ids as well).

What tool feature could I use to understand more what is happening behind the scene?

Thank you for your help,