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.
-
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!
-
As a Brit I find this amusing especially on a Monday morning ....
Taking a dump with Windows Vista
-
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
-
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
-
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)
-
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.
-
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
-
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,
- paste your task onto the main flow area, not in a container,
- change the name
- 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.
-
More Posts
Next page »