January 2008 - Posts
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
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 .
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
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
@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))'
@l geography = geography::Parse('LINESTRING(20 0,20 10,20 20)')
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
PubsDataContext dc = new PubsDataContext();
TextWriter tw = new StreamWriter("c:\\log.txt");
dc.Log = tw;
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.
public System.Nullable<int> col2
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
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
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
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
create type myTableType as
create procedure up_myTable_insertFromTable
@myTableData myTableType readonly
into myTable (col1, col2, col3 )
insert into @myTable values(1, getdate(), 'Table Valued Parameters
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
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
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
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
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.
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
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
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.
More Posts Next page »