Oracle vs SQL server syntax

If you don't know your NVL's from you ISNULLS! This is the best site I've found so far for finding equivalent functions.

http://www.sql-server-helper.com/faq/oracle-functions-p01.aspx

Curiously there seems to be no SQL server equivalent for 'MINUS' - they have 'EXCEPT' instead but generally the function calls are close in syntax.

Preserving surrogate keys

Something I've seen in Oracle before is a way of generating surrogate keys for the Primary Keys of a table using dense_rank, and then storing the surrogates in a seperate key table.

Why you may ask? Of course in SQL server you can use identity columns but if you need to rebuild the table from scratch then you have no guarantee that the same surrogate keys will be created for a given 'natural' key.

An alternative is to create a 'key' table that contains only a limited number of field, namely the primary (surrogate) key alongside the natural key of the table. This way, you can always create the table when necessary and are guaranteed to get the same surrogate keys.

In sql server you may need to disable any identity columns before the rebuild but can swicth it back on afterwards.