SimonS' SQL Server Stuff

To cluster of not

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

Tony's blogged about the use of heaps http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/06/24/row-fragmentation-hopscotch-heap-v-clustered-and-io-cost.aspx. Whilst this highlights data on a page being out of sequence and thus yoyoing when you scan the data, it doesn't mean you should always have a clustered index.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/To cluster of not

ProClarity 6.3 available on MSDN downloads

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/ProClarity 6.3 available on MSDN downloads
The new RSS feed is here

I've just logged into MSDN downloads and was very shocked to see ProClarity available for download.

Both server and desktop versions are available.

You can read more on Proclarity here

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/ProClarity 6.3 available on MSDN downloads

Two Free MSDN Team Suite Subscriptions

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Two Free MSDN Team Suite Subscriptions
The new RSS feed is here

If you want to win one of two MSDN Team Suite Subscriptions worth in excess of £5,000 then its simple. Start contributing the SQL Blog sitte sqlblogcasts.com and you'll be in with a chance. Its free to join up and start blogging so whats stopping you.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Two Free MSDN Team Suite Subscriptions

Native Command Substitution is not supported

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Native Command Substitution is not supported
The new RSS feed is here

In response to a recent support incident we raised we got a response about use of detours in SQL Server.

A detour is a means by which you can change what SQL Server does. This is used by Litespeed to change native backup commands into Litespeed backup commands. Whilst I always thought this a neat idea I always felt it a bit risky. If I want to do a litespeed backup I can easily write the code or use the UI to do so.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Native Command Substitution is not supported

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

Are you allowed to be sizeist in the database world

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Are you allowed to be sizeist in the database world
The new RSS feed is here

Well even if your not, one of my real bug bears is fat tables. These aren't large tables they are fat tables, wide ones with lots of columns or large data types.

The reason this gets is is people develop databases with 10s of rows in them. Then end up with millions of rows in them in a live environment and find this things just don't perform.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Are you allowed to be sizeist in the database world

Performance of MAX trick

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

Jamie posted a comment to by post about the trick for finding a maximum value in a set of results. Stating that the CASE statement solution was significantly faster. Being a synic I didn't believe him so tried it out for myself

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Performance of MAX trick

Running Sums the sequel part x

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Running Sums the sequel part x
The new RSS feed is here

If you've been following the trillogy (Adams posts 1,2 Jamies post ) of attempts at getting a running sum out of TSQL you will appreciate that there are many ways to solve a problem. Following my previous post on partitions and aggregates I thought that might be an avenue. Well it is, and i've thrown in a use of the unpivot as well. I know this could have been written using CASE statements, but that wasn't the point.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Running Sums the sequel part x

Increase the performance of updates by 1000

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Increase the performance of updates by 1000
The new RSS feed is here

Following on from my previous post on varchar(max) for which the consensus was that one shouldn't use varchar(max) everywhere, I have found this great function that allows you to update a varchar(max) column with great performance gain.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Increase the performance of updates by 1000

Microsoft.com SQL Server architecture

I was sent a link recently on the Microsoft.com SQL Aerver Architecture, this is really interesting and follows on from a interview I read last year with Jim Gray on channel9 that talked about terraserver using a white box solution rather than clustering.

The key here is that scaling out a clustered solution is very difficult, whereas if your architecture allows for a load balanced DB farm then you can just add new servers. If one fails the rest of the servers can take the load, no server is a single point of failure,

Building such a solution is more complex than a normal architecture, and also requires more manual processes, but is much easier to scale.

I know there has been talk of a (SODA) Service Orientated Database Architecture. This would be best practice guidance for designing, building and supporting a database architecture for different types of services.

Indirect configuration files in SSIS

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Indirect configuration files in SSIS
The new RSS feed is here

Configuring packages based on configuration files is great, whats more you can have the location of configuration file obtain from an environment variable.

One word of warning is that, if you change your configuration between indirect and direct the configuration file will be overwritten and your conifguration settings lost.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Indirect configuration files in SSIS

Performance tuning with subqueries and parameters

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Performance tuning with subqueries and parameters
The new RSS feed is here

Patrick Wright has recently posted on a performance issue he was asked to look into. The issue in question involved an SP that was taking a huge amount of time, compared to what it should have been. This is one of those real life scenarios where its not a simple you need an index there are lots of factors influencing the issue.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Performance tuning with subqueries and parameters