Database Primary Key C# mapping - String or int

Question

In the Northwind Starters Kit, Primary keys from database are mapped to Strings in C#.

Is this good practice? And if so, why?

thx, Lieven Cardoen

ps: Sorry for the maybe wrong question...

In Northwind Starters Kit some tables have a auto-incremental primary key with datatype int and others have a non auto-incremental primary key with datatype nchar(5). Why is this? Well, apparently some primary keys are just codes (nchar(5) format). So sorry to have used your time.

I thought that a datatype int was mapped to C# string which seemed very wrong to me (but it isn't the case).

Answer 1

It all depends on the data type of the column in the database.

Good practice is to use a compatible/corresponding data type. If the database uses int, use int. If the database uses uniqueidentifier, use Guid. If the database uses nvarchar, use string.

Anything else will give problems down the line. Guaranteed.

Answer 2

For pure efficiency, using an Int as your primary key is better simply due to the support for comparison of Ints at the machine code level. Strings are compared using algorithms implemented at the database level. Unless your strings are very short, an Integer key will take up less space on the page as well (db page).

Update: Based on the other answer now on the board, I'm not sure if I've understood your question correctly. Are you asking whether it is better to use an Integer as your key compared to a string (where either could be chosen)? Or are you asking whether your C# type should match your database type? I'm assuming the former...and would be very surprised if it is the latter - whose answer I would think is obvious.

Update: Lieven has now clarified his request to say that he was, in fact, asking whether an Int or an nchar field would be better as an index so my original take on this question was correct.

To add to my answer, Lieven, it is almost always better to have an Int as your PK. The exception is when there is a natural key that can be captured as a short character string (e.g. in an accounting system where "Item" entries are char strings). The reasons are threefold.

First, Integers are represented as a native machine type (32 or 64-bit word) and manipulated via machine-native operations whereas strings are not but must be compared using a char-by-char approach. So, for example, when traversing the PK Index (usually some variant of a BTree) to locate a record, the comparison operation at each node is a single operation. Is this a huge thing? Probably not unless you are working with a truly massive database or transaction load. If you have a natural character key then, by all means, use it! However, if your "key" is the first five letters of the last name plus the first initial plus a number to make it unique, then you'd obviously be far better off with an Int field.

Second, Integers simply take up less room than almost any char key (except char(1) assuming the use of Unicode). And it isn't just the room in the main table page, remember that the index fields are represented in the Index as well. Again, is this a big deal? Not really, unless you are, again, working with a massive database.

Lastly, our choice of keys often has effects elsewhere. So, for example, if you use the primary key on one table as the foreign key on another, both of the above effects are magnified when you are inserting or updating records in the table using the foreign key.

To sum: use the key that is most natural. However, if you have a choice between Int and Char and both are essentially arbitrary, go with the Int over the Char.

Answer 3

Always use the corresponding C# datatype for the Sql datatype. As other posters have noted, to do anything else is asking for problems later on.

Have a look at this article: http://msdn.microsoft.com/en-us/library/ms131092.aspx for a full list of Sql Server / C# data type equivalents.