How to create an automatic row numbering database in SQL Server Compact

Couple parts to this question so I will list them all as I believe they would be related in the problem to be solved. If not all related I would like help with the first question to start.

Trying to create a automatic row numbered database in SQL Server Compact. Now I can create the numbering, but is really odd how it operates. When you add a new row it numbers it in negatives (IE: -1 -2 -3 ...etc). When you save your data and re-open the database it is numbered properly and in positives.

When I delete rows, the numbering of the rows doesn't update. So if I have row 1 2 3 4 and 5 and delete row 2 and 4, save my work and re-open the database I now have 1 3 and 5. Would like it to update the numbering structure when rows are deleted.

I can click on "Add new row" several times and it does, Would like to only add a new row and no other rows can be added until the current "New Row" is populated.

I am using Visual Basic 2008 Express to build this database in SQL Server Compact. The database works very nice for me as it is but would like to figure out how to number this database in an efficient manner.

My process for creating and numbering this database:

          1. "File" and then click "New Project"
          2. Under "Project"  I click "Add New Item"
          3. I select "Local Database" in the "Add New Item" window
          4. In the "Database Explorer" I right click "Tables" and click on "Create Table"
          5. I name my "Column" and in "Data Type" I select either "bigint" or "int" and change the Default Value of "Identity" to True and "IdentityIncrement" and "IdentitySeed" to "1" .
          6. Add the rest of my data columns

That is how I create my automatically numbering database but operates in a quirky manner. Hope this isn't overly detailed. Will provide additional details if needed. Thanks in advance.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
When I delete rows, the numbering of the rows doesn't update. So if I have row 1 2 3 4 and 5 and delete row 2 and 4, save my work and re-open the database I now have 1 3 and 5. Would like it to update the numbering structure when rows are deleted.

This is the normal behaviour and that cannot be changed. Very often, this identity becomes the key and other records relate to this one using this key. It would quickly become a nightmare if you start moving all identities!
technicaltraderAuthor Commented:
OK. Now that I think about what you have pointed out that makes complete logic. Understood!

I will simplify this question then.

When I click to "AddNewItem" I can click it many times and create many new rows in the database. How can I limit this click to "AddNewItem" to create a new row, and another new row can not be created until the current row cells are populated? Possibly throws up a message of something like: "Current data is not complete" or something. Just thinking aloud now.

I like how this program is written. It is my first development. I am using this for my market trading. I have plans for other tools as well so teaching myself programming in VB. I am now just taking care of all the nuances that I don't like within this program. (Small details that makes it better)

Hope that makes sense.

Thanks again...
Negative numbers are generated for record(s) which are in buffer memory to avoid gaps in numbering. Real numbers are assigned just before saving (when no way back exists). You still have a chance to cancel new record before saving but you need some temporary values used e.g. for relations.

Identity columns are designed to ensure unique ascending values but not consecutive values. Could you imagine how long it takes to renumber millions of records after one row deletion? If you need consecutive numbers on output then you should use ROW_NUMBER() function in your selects. More info:

To avoid multiple insertions of empty rows you should add some validations. Validations on SQL Server level are called triggers. If you define insert trigger then you may control this behaviour "at the last instance". I would guess you'll need validation routine in VB. More info about SQL triggers:

For better data management at SQL Server level I would recommend to download SQL Management studio express:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
ROW_NUMBER() function is not available in Compact , nor are triggers. It is a pain...

Your only option is to use the Identity column.

Identity numbers are allocated and used at time of insert. So, if your insert fails, that number is used regardless, and you already know about deletes.

You can re-seed those numbers, but in compact, it really isn't worth it.

Where are you clicking "Addnewitem" ? if in your application, then there should be some kind of commit or insert statement. At that point, before the SQL to do the insert, test the columns to see if they contain information and disallow the update. You could flag various columns as being "mandatory". It really depends on how you have written your application.
Mark WillsTopic AdvisorCommented:
By mandatory, you can use "NOT NULL" if adding directly to the table. It will give you and error message. However that identity value is already used. That is when it is best to manage in your code.
Good point, Mark. I did not realize it is not Express but Compact (even when mentioned in question title :-).

So, thanks for the clarification and my mistake correction.

Question to the author: Do you really plan to use it on Pocket PC?
technicaltraderAuthor Commented:
Well to answer the question; "Do you really plan to use it on Pocket PC? Answer is "No" and as many red flags that were in my face, my new discovery of using SQL and Visual Basic again teaches me a new lesson. Fortunately I look to lessons such as this as "Constructive".

I wanted to use Express, not Compact. I failed to recognize the word "Compact". Being a bit naive and very new it doesn't surprise me. Now on my new quest of adding a database as express. I have become very efficient at starting a new database. Trouble is, I only see "Local Database" in "Add New Item" and comes up as "Compact". Express is loaded on this computer. Apparently I have new discoveries to unfold. If I can't figure it out, it will be time to start a new question. LOL...

Thanks to each of you as all comments taught me something!
technicaltraderAuthor Commented:
...and as I dig around about compact 3.5, it seems that this will work fine for the application as it is to be used. Believe I am on the right path at least for now. Again, you all directed me in a way with this question that I have progressed because of all of you. Again. I thank you!...
You are welcome, technicaltrader. I would recommend to download SQL Server Management Studio Express. It will allow easy database creation and SQL queries testing. If you need more features then you should buy SQL Server Developer version - cheap (not sure but around $50) and complete for development.
Sorry for duplicate recommendation of SQL MSE :-). But it is free...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.