SimonS' SQL Server Stuff

July books online available for download and its free

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/July books online available for download and its free
The new RSS feed is here

I really like this new constant update of documentation coming from the SQL team

The BOL can be found here "SQL Server 2005 July Books Online"

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/July books online available for download and its free

What the F***

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/What the F***
The new RSS feed is here

If you appreciate programming then this is a must read. http://thedailywtf.com/default.aspx

Most of us will be able to chortle with amusement due to havnig come across many of these in our working life (if not having written them ourselves :)  )

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/What the F***

SSIS in the Real World - Parsing Unstructured Data

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/SSIS in the Real World - Parsing Unstructured Data
The new RSS feed is here

For those of you that were at the first UK BI usergroup meeting you will have been my whirl wind example of processing an unstructured data file using ssis. The packages are now available.

The presentation is here http://sqlblogcasts.com/files/7/ssispresentations/entry902.aspx

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SSIS in the Real World - Parsing Unstructured Data

Ping and Tracert explained for dummies

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Ping and Tracert explained for dummies
The new RSS feed is here

I recently looked into how ping and tracert worked only know that they use something called ICMP. Its fairly straight forward (well the basics) so thought I would provide a dummies explanation so don't use this a a foundation of a PHD on ICMP.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Ping and Tracert explained for dummies

Is IDENT_CURRENT the next @@identity

I've just finished watching the webcast on building a wareshouse according to Kimball principles. In it they generate an audit record by inserting a record and then using IDENT_CURRENT to get the identity value.

A few months ago some one on the forums was screaming because the IDENT_CURRENT was not working on a 64 bit server it was returning NULL.

My point is they both wanted the last identity generated for a table, but both missed the point that IDENT_CURRENT is not specific to a session but is server wide. This means you could be getting the identity value generated for an insert in another session. Pretty much like calling select MAX(identityCol) from table (but without the locking).

So just as people with triggers got burnt with the use of @@identity so are people with IDENT_CURRENT() whats more it probably won't show up as a real problem i.e. a failure, you will just have records associated with the wrong parent, your database will slowly become corrupt.

In both of these situations the user should be using SCOPE_IDENTITY to get the value generated by their last insert statement.

Cross posted from http://sqlblogcasts.com/blogs

Double your performance, don't use unicode in your database

I was listening to a web cast by one of the infamous gurus of SQL and heard again that you should use nvarchar and nchar for your database columns and not varchar and char.

The reasons put forward were that

1. your client will be in unicode and so if you don't there will always be conversion going on from non-unicode to unicode.
2. you will be able to support languages such as Chinese etc that require unicode.

Again I was wondering how much should I take head and start using unicode because

1. I work on central european systems
2. the space required for unicode is double that of non-unicode

The only compelling reason I see would be if the performance of the conversion on the client out ways the size required to store the double bytes.

So I did a test. Very cruedly created 2 tables, populated them with 10,000 rows and read them from a .net client app and timed them.

What was the outcome, well on my laptop (so no network latency) the time to process the unicode data was twice that of the non-unicode data. That does make sense as SQL has to return twice the amount of data, that means reading twice the number of pages, send twice the amount of data down the stack and use up twice the amount of your valuable cache. 

So it seems that the overhead of processing the double the amount of data far out ways any conversion that goes on.

When ever you design a database you will realise that the size of text based columns will dwarfs the size of your integer key columns, if you double the size of those columns the dwarfing becomes a real David v Goliath. imagine a address table with 2  integer key columns and 5x50 character address fields. thats 250 bytes of text and 8 bytes of integers, with unicode that becomes 500 bytes of text data, thats 16 rows per page. Your 1 million row table now takes up .5 Gb of your cache.

Whilst I know anyone that has used the CLR will be shouting, "but SQLCLR only support unicode", true but it doens't mean you have to use unicode for your columns.

So if you your system isn't likely to need unicode, (if you don't know or are not sure then it probably won't), don't use unicode for your columns. Save your cache, bandwidth and disk space.

Cross posted from http://sqlblogcasts.com/blogs

Compressed backups with Litespeed for free

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Compressed backups with Litespeed for free
The new RSS feed is here

In case you had missed it, SQLServerCentral have arranged a deal with Quest to get Litespeed for free.

You can read the review of litespeed here, which highlights the benefits of the compression that litespeed gives.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Compressed backups with Litespeed for free