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

Posted on 2009-06-28
Last Modified: 2013-11-27
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.

Question by:technicaltrader
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 25 total points
ID: 24732287
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!

Author Comment

ID: 24732590
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...
LVL 42

Accepted Solution

pcelba earned 125 total points
ID: 24732603
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:
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
ID: 24741393
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.
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 100 total points
ID: 24741409
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.
LVL 42

Assisted Solution

pcelba earned 125 total points
ID: 24743061
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?

Author Closing Comment

ID: 31597700
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!

Author Comment

ID: 24746432
...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!...
LVL 42

Expert Comment

ID: 24749029
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.
LVL 42

Expert Comment

ID: 24749151
Sorry for duplicate recommendation of SQL MSE :-). But it is free...

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question