SQL Server CE “Identity” Keys

SQLServerCompact4 

 SQL Server CE is a terrific desktop database.

For developers that continue to use it in the development of desktop database applications the sad news has been known for a while that Microsoft has decided to no longer support such a venerable database engine.  Hopefully it will be opened sourced or maintained at some other level so that its use can be continued.

So far that is being done with SQL Server CE 4.0 SP1, which allows for its implementation along with the .NET 4.5 Framework and Visual Studio 2013.  This latest update can be found at the following link…

http://www.microsoft.com/en-us/download/details.aspx?id=30709

In addition, Black Falcon Software’s SQL Server CE data access layer can also be freely obtained at the following link…

http://blackfalconsoftware.com/Software

Though the highest version compiled in this package is for .NET 4.0, it can be easily recompiled with .NET 4.5 without any issues.  Just make sure that you implement the SP1 setup first  so that it will compile and work with .NET 4.5 and Visual Studio 2013.

SQL Server CE is not nearly as robust as any of the standard implementations of SQL Server but for a desktop database engine it is quite powerful.  One of the features it offers is that of “Identity” keys, which can be easily set up by using the “Auto” option against a column…

SQLServerCompact4_AutoColumn

By doing this, an “Identity” key column will increment like a similar such column in any version of SQL Server.  The only exception is that in SQL Server CE, the “Identity” key column is limited to a seed and increment value of “1”.

Using “Identity” keys as primary key columns is also limited to the fact that retrieval of a last inserted “Identity” key is only accurate when an insert and the key retrieval is performed outside of a SQL Server CE transaction.  This does not seem to make any sense, given that a retrieval of an “identity” key within a transaction should reflect the next incremental number once a new record is saved to the database but this does not appear to be the case.

If an attempt is made to retrieve an “Identity” key within a transaction, a different number will be returned than that of the actual incremented number after the physical save of a record.  And if you are trying to return “Identity” key values after an insert for inserts of other records in a multi-level transaction that represents a hierarchy of related records, your key values will be incorrect making the overall record relationships invalid.

This can be quite disconcerting for those developers that need to create complex transactions using “Identity” keys as linkage values for record relationships.

To avoid this issue you may want to consider not using “Identity” keys at all under such circumstances and instead use a key table that will provide the incremental key numbers as needed within such a transaction.

In such a situation you could do the following for example.  Set up a “Key” table with all the necessary relationship key values such as shown in the example below.

SQLServerCompact4_KeyTable

Since SQL Server CE is always “single threaded” you never have to worry about transaction locking in order to create a single-threaded set of retrieval and update operations to such a table since only one user at a time will ever be accessing this data.

Prior to initiating a transaction, retrieve the key table record into a corresponding record object, which can be maintained throughout the transaction.  As you need to increment any record key merely take the appropriate value from the key record, increment it, use the incremented number as the new key or sub-key field in the record to be inserted, and the update the corresponding key record field with the incremented number.

At the end of the transaction and within it then update the all of the fields in the key record to its own table.  Remember, this last update should be performed within the transaction so if a “rollback” is necessary the key-record table is not updated inadvertently.

This methodology works quite nicely for such a database engine and affords a very clean work around for avoiding inaccurate “Identity” key values under such circumstances.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s