Thousand seperators

Slightly bizarre.. after years of running queries through Oracle basic editors and being able to provide primitive formatting I found SQL server unable to do something (easily) that I thought would be trivial. If I return a count(*) from a table and want to send that information to someone I'd expect to be able to simply add thousand seperators and set the decimal places etc.

In Oracle I'd simply type:

SELECT TO_CHAR (COUNT ( * ), '999,999,999') AS row_count

FROM MyTable;

but in SQL server I find myself jumping through hoops and end up with:

select convert(varchar, CAST(COUNT(*) as money),1) as row_count

from MyTable;

Converting to money?? This seems to be recommended technique on the SQL forums. You still need another step to strip out the decimal places that money will give you as well.

Guess you could argue that you would likely format at the Excel or SSRS stage but should really be able to do this more simply in SSMS.

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.

New Blog Spot

New to the world of blogging I intend to use this as a techie site with my thoughts on both Data Warehouse and Business Intelligence systems design.