SQL 2008 data types - Which ones to use?

Question

Hi

I'm using SQL Express 2008 edition. I've planned my database tables/relationships on a piece of paper and was ready to begin.

Unfortunately, when I clicked on data type for my pkID_Officer I was offered more than I expected, and therefore doubt has set in.

Can anyone point me to a place where these dates types are explained and examples are given as to what fields work better with which data types.

Examples:

• is int for an ID (primarykey) still the obvious choice or does the uniqueidentifier takes it's crown?

• Telephone numbers where the digits are separated by '.' (01.02.03.04.05)

• Email

• items that will be hyper-links

• nChar and vChar?

Any help is always appreciated.

Thanks

Mike.

Answer 1

For primary key I always prefer (as starting point) to use an auto increment int. It makes everything more usable and you don't have any "natural" relationship with the real data. Of course there can be exception to this...

Answer 2

uniqueidentifier are GUIDs: http://de.wikipedia.org/wiki/Globally_Unique_Identifier

Guids
- are worldwide unique - have in DotNet e.g. her own objects (System.Guid) - are 16 digits long

If you want such things, then use it. If you are fine with int-ids, then it's ok.

Telephone-Numbers / emails / Hyperlings are normal strings.

Answer 3

NCHAR/NVARCHR are Unicode counterpoints of CHAR/VARCHAR datatypes. I almost always use them in my apps - unless I have compelling reasons not to use them.

Answer 4

The MSDN site has a good overview of the SQL 2008 datatypes.

http://msdn.microsoft.com/en-us/library/ms187752.aspx

For the ID field use a guid if it needs to be unique across separate systems or tables or you want to be able to generate the ID outside of the database. Otherwise an int/identity value works just fine.

I store telephone numbers a character data since I won't ever be doing calculations on it. I would think email would be stored the same way.

As for hyperlinks you can basically store the hyperlink by itself as a varchar and render the link on the client or store the markup itself in the database. Really depends on the circumstances.

Use nvarchar if you ever think you'll need to support double byte languages now or in the future.

Answer 5

• is int for an ID (primarykey) still the obvious choice or does the uniqueidentifier takes it's crown?

I personally prefer INT IDENTITY over GUIDs - especially for your clustered index. GUIDs are random in nature and thus lead to a lot of index fragmentation and therefore poor performance when used as a clustered index on SQL Server. INT doesn't have this trouble, plus it's only 4 bytes vs. 16 bytes, so if you have lots of rows, and lots of non-clustered indexes (the clustered key gets added to each and every entry in each and every non-clustered index), using a GUID will unnecessarily bloat your space requirements (on disk and also in your machine's RAM)

• Telephone numbers where the digits are separated by '.' (01.02.03.04.05)
• Email
• items that will be hyper-links

I'd use string fields for all of these.

VARCHAR is fine, as long as you don't need any "foreign" language support, e.g. it's okay for English and Western European languages, but fails on Eastern European and Asian languages (Cyrillic, Chinese etc.).

NVARCHAR will handle all those extra pesky languages at a price - each character is stored in 2 bytes, e.g. a string of 100 chars will use 200 bytes of storage - ALWAYS.

Hope this helps a bit !

Marc