I amd really getting into the use of UDTs as a way of packaging up SQL CLR code. I'm also looking into implementing an array type so I can do code like

declare @s dbo.SQLArray

set @s.AddKeyword('sas')

set @s.AddKeyword('dog')

Fo this to be realy useful you would want to be able to select from your array.

select *

from @s.ListValues()

Unfortunately thats not possible, you can't have a table valued function on a type. You get 1 of two errors one complaining that you have to alias the table and columns of the valued function and the other saying you can't alias the column in table valued function. On reading BOL it states that you can't do this.

So your other option is to add a static method to your type and have it take an instance of the type from which you return the list.

declare @s dbo.SQLArray

set @s = 'simon,sabin'

select *

from dbo.ListValues(@s) as List

I am still looking into the true impact of having such code in terms of performance and supportability, but I am sure it would be of use in some situations to make more readable code even if it doesn't perform great.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Table Valued Methods on Type

I read Steve Jones editorial inDatabase Weekly today and saw his statement.

"I still recommend jumping from 2000 to 2008 if you haven't already upgraded"

My personal view is that if you are looking to replace your systems then 2005 has some great features that you get out of the box and is now stable.

Some of the most important features for me are around consistency and recoverability. I've had 2 clients in as many weeks that have supsect databases. Something has caused data corruptions and the database is inaccessible.

SQL 2005 has page checksums which are a great mechanism to prevent crap from being written or read from the disk. It is also contains numerous bug fixes that SQL 2000 doesn't.

But this on top of the fact that SQL 2008 (Katmai) will not RTM for a considerable amount of time and the first service pack probably won't be in during 2008 (but what do I know). That means to get a stable SQL 2008 you have to wait at least 14 months.

Further more having native 64bit versions of SQL means that the scalability of SQL 2005 is so much more that SQL 2000 especially on standard edition.

Personally I would move to SQL 2005 now, to benefit from the high availability and scalability features.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Should you wait for Katmai.

I've just been tearing my hair out trying to get variables and locking working. It was only after one of those eureka moments that I realised that the variable names are case sensitive.

So the lesson learnt is make sure that you have a naming convention for your variables so you don't have to remember if your variables are all lower case (unreadable) , title case (my preference) or camel case.

I've also decided that I prefer to use the lockForWrite over the LockOneForWrite and even over specifying the names in as a delimited list. The reason being is that you have the better control in doing it that way.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SSIS - Variable lists for the script task are case sensitive