Simons SQL Blog

We have already had 230 people register for SQLBits, whilst we have more room than last time we are still limited.

To avoid disappointment make sure that you register soon.

If you have registered make sure you have confirmed your registration by clicking on the link in the email you were sent.

Are you looking for a Reporting Services  or Analysis Services contract. I have details of some positions available in London at £450/day.

If you are interested use the Contact Me here 

Following a post that SQL Server 2008 is going to RTM in Q3 2008 I was pointed to a fantastic blog on Microsofts delivery ethos .

http://www.simple-talk.com/community/blogs/philfactor/archive/2008/01/27/43174.aspx

What is sad is that MS don't generally give out a specific date of release until they know and in the case of SQL Server 2008 they have generally only said Q2 2008. Which one would think was vague enough to allow for slippage.

What does it matter when the product ships as long as it's right?

If you are desperate enough to get going with the product sooner rather than later then contact MS and try and get on an early adopters program. If not then just wait and use SQL 2005.

As for the product being right. My only gripe is with the focus on new versus old. Their is lots of new stuff in SQL 2008 but there are also many features in SQL 2005 that still need improvement. Unfortunately because they are not part of a big feature they only have severe bug fixes applied to them, rather than the incremental improvements.

If you do want a feature the best thing you can do is use connect, whilst many suggestions don't get actioned as you may like some do.

Where is the STContains function for the Geography type. I don't know but its not in Sql Server 2008.

As a workaround you can use STIntersection and compare the results with the geography you are comparing with i..e

declare @g geography = 'POLYGON((0 0,0 10,0 20,0 30, 0 40, 50 40,50 30 ,50 20, 50 10, 50 0, 0 0))'

declare @l geography = geography::Parse('LINESTRING(20 0,20 10,20 20)')

select @g.STIntersection(@l).ToString(), @g.STIntersection(@l).STEquals(@l);

This returns 1 if the LINESTRING  is within the POLYGON

Venturing into the world of LINQ I'm determined to give it a go because writing 3 -10 sps per table is just a pain in the bottom.

If you come from a database background one of the things that will concern you is the SQL that LINQ generates. The good news is that you can capture all the SQL that LINQ generates by using the Log property of you data context. The following sets the PubsDataContext to log to the file c:\log.txt

TextWriter tw = new StreamWriter("c:\\log.txt");

What I noticed straight away was updates, by default when an update is performed, irrespective of what columns are changed, all the columns are specified in the WHERE clause. This is used to enforce optimistice locking. This is to  ensure that when you update data it hasn't already been updated by someone else. The downside however is that every column has to be checked.

You can change this behaviour on each column in the LINQ to SQL model that is generated by adding the UpdateCheck attribute i.e.

[Column(Storage="_col2", DbType="Int", UpdateCheck=UpdateCheck.Never)]

However its a bit of a pain to do that on each column in each table. However there is light at the end of the tunnel. If you have a timestamp on the table only this column will be check to verify the record has't changed.

You might be still asking why I want this at all. Imagine your bank account (£100 balance), you've cash a cheque £50 and you use the cash machine to withdraw £100, what if it happens that the bank pays the cheque into your account at the same time you use the cash point, both  read the balance at £100, the chequed is cashed updating it to £150, but then the cash point withdrawl updates the £100 balance with the £100 withdrawl leaving you with a balance of £0. What happened to the cheque?

If the each transaction checked that the record had not been updated then it would have realised that the data had changed and the update would have failed.

The other option is pessimistic locking, which puts a lock on a record for the duration that the record is being read until it is updated. This is not very good for concurrency as locks cause blocking and so the number of users you application will be able to support will not be very high.

Very very high up on my list has to be "String or binary data would be truncated." Error 8152.

Why is this annoying? Because you have no idea which column it is referring to.

On recently encountering this again I looked to see if anyone had asked for a niceer message on connect. Low and behold they had http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125347

It currently only has a few votes, but it does have a response saying that this will be improved in the next release after Yukon. Well I guess that means Katmai.

I've tested it on CTP5 and I still get the same error so I just hope its coming in CTP6.

If you want to make sure its changed get voting. http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125347

Table Valued Parameters have to be one of the coolest features in SQL Server 2008. With them you are able to pass a set of data around as a single variable. What this means is that if you are saving multiple records you can do very easily in one procedure call.

You might say well I can do that already using an XML column or a delimited list, Well the difference with a Table Valued Parameter is that you get a typed rowset which you can use directly in an INSERT, SELECT, UPDATE statement, and anyother statement that can take a rowset. This means you don't have to shred your XML or have a function to split your delimited string.

create table myTable (col1 int, col2 datetime, col3 varchar(max))

go

create type myTableType as table (col1 int, col2 datetime, col3 varchar(max))

go

create procedure up_myTable_insertFromTable

  @myTableData myTableType readonly

as

  insert into myTable (col1, col2, col3 )

  select col1, col2, col3

    from @myTableData

go

declare @myTable myTableType

insert into @myTable values(1, getdate(), 'Table Valued Parameters are great')

exec up_myTable_insertFromTable @myTable

Its very easy to use.

There are a few gotchas to be aware of,

The stored procedure parameter has to be READONLY. This means that you cannot modify it within the stored procedure. You can only modify it in the scope it was created. i.e. If you created a table type variable in your procedure you can modify the data in it, however if you pass that to another procedure that other procedure cannot modify it as it wasn't create in the scope of that procedure.

A table valued parameter ALWAYS has a default of an empty table. This is not inline with all other types btu has been done for performance reasons. What this means is that in the previous code the following will work

exec up_myTable_insertFromTable

Even though you haven't passed in a value for the @myTableData parameter.

No statistics are created on a TVP. This is inline with table variables which means if you are doing selects with where clauses you have to be careful, as you may end up with table scans.

One of the biggest challenges in understanding spatial data is visualising shapes. In 2D it isn't two bad, and I have started the SpatialViewer project on codeplex (http://www.codeplex.com/SpatialViewer)

However visualising Geography types requires a globe.Virtual Earth can come to the rescue. I have had ago at visualising shapes on Virtual Earth

Due to the need to have lots of javascript the sample is hosted on the SQLBits site http://www.sqlbits.com/HowTo/Spatial/ViewingShapesOnVirtualEarth.aspx 

You should be able to draw a shape on the virtual earth map and get the points in the text box below. The points are in WKT format without the geometry shape and brackets. You can also put in a set of points and add the shape.

I suggest you go to 3D mode (if you have t installed) to see how shapes are represented in virtual earth. This is not the same as the way they are represented in Sql Server. Sql Server uses shortest distance whereas VE maps
lines on a planar projection not allowing for the curve of the earth.

Draw a line from London to New York and then switch to 3D mode. The line should loop up towards the north pole, but it doesn't. It goes straight across the atlantic.

I find I get a few javascript errors, I am trying to iron these out but you should still be able to use it.

On Friday we will be deciding on the sessions that are going to be at the next SQLBits.

If you haven't yet voted for the sessions you want then make sure you do so to avoid disappointment.

http://www.sqlbits.com/information/PublicSessions.aspx

We will be opening registrations for the conference in a few weeks so make sure you register on the site so we can let you know when registrations open.

You think this an odd title, a square is a square. Well whilst that is the case in a planar 2D world in a 3 spatial world where the square is drawn on a sphere.

This is one of the areas that catches most people out.

If you define a polygon  with corners at (0,0) (0,50) (90,50) (90, 0). So this starts on the equator at Greenwich and then moves north until the 50 longitude, then moves to a point a quarter round the world again on longitude 50 and back to the equator on latitude 90.

On first glance you would think that the line from (0,50) to (90,50) follows ring that defines longitude 50, and this is the error. The line between these two points is actually the shortest distance between, which is part of  great circle. Morten has done a great blogpost on this Straight lines on a sphere .

What is a shame is that both Mortens Shape to Shapefile to SqlServer 2008 to WPF and Virtual earth both draw the polygon as though it followed the ring of longitude. What this leads to is confusion over when shapes intersect

If you are likely to be working with Spatial data covering the earth I suggest you buy a globe to enable you to visualise the world.