February 2006 - Posts

SSIS Performance Whitepaper

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 Performance Whitepaper
The new RSS feed is here

Came across this whitepaper by Microsoft and Unisys on SQL Server Intergration services.

If interested is can be found here

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

posted Monday, February 06, 2006 11:07 AM by SimonSabin with 1 Comments

Why use anything but varchar(max)

As you might have read I am putting together some standards for data type usage.

When I got to the text stuff varchars are king (generally due to the effort required in an app to trim data), except for small (<4 characters) when I would use char.

My quandry came about what size should a varchar be. With SQL 2005 and the varchar(max) I can't see any benefit not using this everywhere, the result is the same whether you use varchar(10), varchar(2000) or varchar(max). Isn't it.

Ok so with a fixed size datatype SQL will stop you iserting more data than is allowed, but be honest who is relying on SQL to do that validation, not me for one.

and there might be a slight difference in storage, I can't remember how the length or the column is stored but would guess that for < 256 it is 1 byte, < 32,767 its 2 bytes etc.

But using varchar(max) it is definitely future proof, and a lot easier to set standards "always use varchar(max)".

posted Tuesday, February 28, 2006 7:16 AM by SimonSabin with 9 Comments

Tiny integers less than 0

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/Tiny integers less than 0
The new RSS feed is here

It has just dawned on me, whilst updating my standards document and putting together some guidelines on datatype usage that a tinyint can't store negative values. Which explains why SSIS converts a tinyint to an unsigned single byte integer.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Tiny integers less than 0

posted Tuesday, February 28, 2006 7:08 AM by SimonSabin with 0 Comments

Download Speed from MSDN

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

If you've been to the MSDN subscriber downloads you will have seen there is a new download control (v5.0). This control includes download performance improvements, download throttler and increase of 4 concurrent downloads.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Download Speed from MSDN

posted Monday, February 27, 2006 4:19 AM by SimonSabin with 0 Comments

Most amusing post

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

As a Brit I find this amusing especially on a Monday morning ....

Taking a dump with Windows Vista

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

posted Monday, February 27, 2006 4:13 AM by SimonSabin with 0 Comments

SET and SELECT with SQL 2005 and SQL 2000

My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/SET and SELECT with SQL 2005 and SQL 2000
The new RSS feed is here

Came across an interesting bug reported on the feedback centre. Basically SQL2000 allows the following syntax

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SET and SELECT with SQL 2005 and SQL 2000

posted Sunday, February 26, 2006 2:45 PM by SimonSabin with 0 Comments

SSIS Lookup population

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 Lookup population
The new RSS feed is here

In SSIS the lookup component can only be loaded from a SQL statement this is a bit restrictive/annoying. What would be nice is to load from any source that SSIS can use, i.e. OLEBD, raw file, etc.

If you think the lookup should be bale to be populated from any source vote for Jamies suggestion here

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SSIS Lookup population

posted Sunday, February 26, 2006 2:32 PM by SimonSabin with 0 Comments

SSIS data flows and tinyint

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 data flows and tinyint
The new RSS feed is here

If you have designed your db well and are using appropriate datatypes, rather than int every where it is likely you will have used the tinyint datatype for your small sets of data. If you have subsequently tried to use these in SSIS its not happy.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SSIS data flows and tinyint

posted Friday, February 24, 2006 5:46 AM by SimonSabin with 3 Comments

Developer Day 3 announced

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

Developer Day 3 will be on the 3rd JulyJune(oops) this year. Although I couldn't make it last year I heard it was a great success and I intend on speaking this year.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/Developer Day 3 announced

posted Thursday, February 23, 2006 1:56 PM by SimonSabin with 0 Comments

Dodgy use of GO - follow up

Subramanyam Krishnamurthy has posted about the use of GO in scripts and how it can be dodgy. Many people have provided feedback on the forum I though I could add mine.

A couple of points, just to confirm that GO isn't TSQL but a terminator used by DMO and SMO to parse batches of SQL. Whats more with SQLCMD you can put a number after the GO for the preceeding TSQL batch to be repeated.

The biggest issue I have seen is the deployment of sprocs. The easiest way is to concatenate all the files for the store procs together and run that combined file. However if one file doesn't have a go at the end and all the files start with a if exists drop statement you can end up with.

If exists (select 1 ....)

    drop proc myfirstProc

go

create proc myfirstProc

    begin

    --    do some stuff

    end

 

If exists (select 1 ....)

    drop proc mysecondProc

go

create proc mysecondProc

    begin

    -- Do some stuff

    end

go

If exists (select 1 ....)

    drop proc mythirdProc

go

create proc mythirdProc

    begin

    -- Do some stuff

    end 

What you find is that this is likely to run in, however the first time someone runs myFirstProc it will drop mysecondProc :( Not good.

On final note for those running scripts on SQL 2005 its a must that you start the script with :on error exit. This will avoid the problem detailed above, where one statement fails and but all the others are still run

posted Tuesday, February 21, 2006 5:18 PM by SimonSabin with 0 Comments

SSIS: Cut and pasting tasks

Anyone that has used SQL Server Integration Services (SSIS) and tried to cut and paste tasks from different parts of the control flow will appreciate this tip.

SSIS has the concept of containers that allows tasks to be grouped, in a loop or in a sequence. If you try and past a task into one of these containers some wonderful thnig happen. Generally the task is pasted in a bizarre location resulting in your container having its size and shape changed. The task can be moved and the container resized, however if you have more than one layer of containers, i.e. a sequence inside a loop inside another loop, then reszing all the containers is a bit of a pain.

The trick to avoid this,

  1. paste your task onto the main flow area, not in a container,
  2. change the name
  3. drag the task into the container.

(The changing of the name is really only required if you want to paste the task in the same container as the original.

posted Tuesday, February 21, 2006 9:25 AM by SimonSabin with 0 Comments

SSAS Thrashing your machine

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

If you've been playing about with analysis services as I have and tried the realtime updating or you've deployed a project and then made changes. You may find your machine starts thrashing, lots of IO (very noticeable on a laptop) Analysis Services service using CPU. Well I've just had the exact same situation.

... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SSAS Thrashing your machine

posted