February 2006 - Posts

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)".

-

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.

So the following will fail

select cast(-23 as tinyint)

Its amazing how obvious this was but just went straight over my head.

-
Posted by simonsabin | with no comments

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.

I've just achieved a download rate of 2MB/s, thats not megabits but megabytes. Wow!

-
Posted by simonsabin | with no comments

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

Taking a dump with Windows Vista

-
Posted by simonsabin | with no comments

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

SET SELECT @p=1

where as SQL 2005 doesn't.

There are quite a few of these types of bugs/design feature that have popped up, this is generally where the design teams have corrected a bug in SQL 2000, the bug was ignored in SQL 2000 but when you try and do it in SQL 2005 with the bug fixed your problem comes to light.

Another is conflicting table hints in SQL 2000 this was valid

UPDATE mytable (ROWLOCK)

SET myCol = newvalue

FROM mytable (NOLOCK)

JOIN myothertable (NOLOCK) on mytable.col2 = myothertable.col1

In SQL 2000 the table hint in the FROM clause on mytable is ignored (in this scenario) but in SQL 2005 it isn't thus the conflicting table hints.

There has been lots of "sorting the ship out" and tighting up. Especially around security and impersonation. This is a good thing in my view

-
Posted by simonsabin | with no comments

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

-
Posted by simonsabin | with no comments

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.

I've come across this before and not found the answer so when I can up with the same issue again recently I thought I would dig a bit more.

Douglas posted a while back in response to a forum post with details about an update to BOL. This update is now in BOL (http://msdn2.microsoft.com/en-us/library/ms345165.aspx) and mentions the mapping between SSIS types and .Net manager types. It doesn't discuss mapping to SQL types. It does mention mapping files used for the import export wizard which are located at file://C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles, but it does state that these are only used by the import export wizard. However I thought I would look at them any way.

I cam across one file SqlClientToSSIS.xml which has the mappings it looks like I want.

<dtm:DataTypeMapping>
  <dtm:SourceDataType>
    <dtm:DataTypeName>tinyint</dtm:DataTypeName>
  </dtm:SourceDataType>
  <dtm:DestinationDataType>
    <dtm:CharacterStringType>
      <dtm:DataTypeName>DT_UI1</dtm:DataTypeName>
      <dtm:Length>3</dtm:Length>
    </dtm:CharacterStringType>
  </dtm:DestinationDataType>
</dtm:DataTypeMapping>

So unlike the other sql integer types the tinyint gets converted to an un-signed single byte integer. 

So there you have it, if using tinyints you need to use the SSIS type DT_UI1 (single byte un-signed integer)

-
Posted by simonsabin | 1 comment(s)
Filed under:

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.

You can't register yet but keep posted.

-
Posted by simonsabin | with no comments

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 by simonsabin | with no comments

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 by simonsabin | with no comments
Filed under:
More Posts Next page »