Within the Northwind Starters Package, Primary secrets from database are planned to Strings in C#.

Is sound practice? And when so, why?

thx, Lieven Cardoen

ps: Sorry for that maybe wrong question...

In Northwind Starters Package some tables possess a auto-incremental primary key with datatype int yet others possess a non auto-incremental primary key with datatype nchar(5). Why? Well, apparently some primary secrets are simply codes (nchar(5) format). So sorry to possess used your time and effort.

I figured that the datatype int was planned to C# string which appeared very wrong in my experience (however it is not the situation).

Everything is dependent around the data kind of the column within the database.

Sound practice is by using a compatible/corresponding data type. When the database uses int, use int. When the database uses uniqueidentifier, use Guid. When the database uses nvarchar, use string.

Other things can give problems down the road. Guaranteed.

For pure efficiency, utilizing an Int as the primary secret is better simply because of the support to compare of Ints in the machine code level. Strings are in comparison using calculations implemented in the database level. Unless of course your strings are extremely short, an Integer key will require up less space around the page too (db page).

Update: In line with the other answer now around the board, I am unsure if I have understood your question properly. Are you currently asking whether it's easier to make use of an Integer as the key in comparison to some string (where either might be selected)? Or are you currently asking whether your C# type should suit your database type? I am presuming the previous...and could be very surprised if it's the second - whose answer I'd think is apparent.

Update: Lieven has clarified his request to express he was, actually, asking whether an Int or perhaps an nchar area could be better being an index so my original undertake this was correct.

To increase my answer, Lieven, it will always be better with an Int as the PK. The exception happens when there's a natural key that may be taken like a short character string (e.g. within an accounting system where "Item" records are char strings). The reason why are threefold.

First, Integers are symbolized like a native machine type (32 or 64-bit word) and altered via machine-native procedures whereas strings aren't but should be in comparison utilizing a char-by-char approach. So, for instance, when crossing the PK Index (usually some variant of the BTree) to discover a record, the comparison operation each and every node is really a single operation. Is an enormous factor? Most likely not unless of course you're using a truly massive database or transaction load. For those who have an all natural character key then, go ahead and, utilize it! However, in case your "key" may be the first five letters from the surname as well as the first initial along with a number to really make it unique, then you'd clearly be far best by having an Int area.

Second, Integers you need to up less room than just about any char key (except char(1) presuming using Unicode). Also it is not only the room within the primary table page, keep in mind that the index fields are symbolized within the Index too. Again, is a large deal? Not necessarily, unless of course you're, again, using a massive database.

Lastly, our selection of secrets frequently has effects elsewhere. So, for instance, if you are using the main key on a single table because the foreign key on another, both of the aforementioned effects are magnified when you're placing or upgrading records within the table while using foreign key.

To sum: make use of the key that's most basic. However, for those who have an option between Int and Char and both of them are basically arbitrary, opt for the Int within the Char.

Always employ the related C# datatype for that Sql datatype. As other posters have noted, to complete other things is requesting problems afterwards.

Take a look only at that article: http://msdn.microsoft.com/en-us/library/ms131092.aspx for any full listing of Sql Server / C# data type counterparts.