About a month ago, I received a copy of "Beginning Spatial with SQL Server 2008" by Alastair Aitchison. I've become acquainted with Alastair through his frequent postings on the SQL Server Spatial forum (under the login Tanoshimi), where he's always been very helpful and patient with folks starting out with spatial data concepts and SQL Server practice. Although I'd been working with SQL Server spatial for a while now, I read the book from cover to cover. It doesn't disappoint. The basic spatial concepts are explained in the first few chapters, and he goes on from there to present an encyclopediadic approach to the spatial methods and properties supported in SQL Server 2008.

I especially enjoyed that he showed examples of geocoding, visualization, and services using both Virtual Earth and Google Earth. I also liked the scenario based approach to the spatial functions, for example showing STIntersection using the Appian Way and the Pontine Marshes. Each spatial function described has an example using real-world geographic use case, and this helps in understanding. The end-to-end examples gave you a good understanding about how to start off with spatial using import, query, and visualization.

The only quibbles I had were with the introduction to SQLCLR and object orientation WRT spatial. It was confusing to describe the OGC inheritence hierarchy without contrasting this with SQL Server's actual implementation (two data types geometry and geography each supporting all of the instanciable OGC types, like point, polygon, etc). And because SQL Server's .NET UDTs don't support inheritence, individual classes for each OGC type is not the way they're exposed (or even implemented internally AFAIK). I was also surprised at the assertion that you can't initialize a geometry/geography from a WKT string in T-SQL; this works because conversion from string implicitly calls the Parse method and uses the default SRID for either type. Reported it as errata. Finally, more performance-related information might have been useful, but the book was positioned as a beginning book after all.

Starting off with spatial data is more than just learning the intricacies of a new data type or lists of methods and properties of a SQLCLR-based type; you need to acquire some domain-specific knowledge to make heads or tails of the data type.  And that's where this book is invaluable. I'd recommend it as a way to get up to speed quickly with the SQL Server 2008 spatial types.

For those of you who's blog readers don't always get updated (rather than brand new) blog entries, there's a happy ending to my last "fixed-almost" blog entry on EDM 4.0 and parameterization. I've heard that this has been fixed to generate VARCHAR(8000) parameters and also that the unparameterized version:

var x = from a in ents.authors
        where a.au_lname == "Smith"
        select new { a.au_lname, a.au_fname };

will generate ...WHERE au_lname = 'Smith' instead of ...WHERE au_lname = N'Smith'.

Note that this is a different semantic; they generate a parameter type that agrees with the column's data type rather than using the .NET string type's data type equivalent (NVARCHAR). This will be better for performance because the database and parameter types agree.

Fix is coming in next beta version of .NET 4.0. I've updated the original blog entry to note this as well.

One of the first things that I did after installing VS2010 beta this week was to check on a parameterization problem with string parameters, and Linq To SQL/ADO.NET Entity Framework generated code. In the original version, this L2S query:

var x = from a in ents.authors
        where a.au_lname == "Smith"
        select new { a.au_lname, a.au_fname };

or this EF query:

string name = "Smith";
var x = from a in ents.authors
        where a.au_lname == name
        select new { a.au_lname, a.au_fname };

Would cause plan cache pollution and possibly performance problems. These queries produced a query plan with the parameter specified as NVARCHAR(5). Each query with a different length will produce a new plan in the cache, and the use of NVARCHAR rather than VARCHAR will cause a table scan (rather than a seek) in the plan, regardless of how many rows are in the table, because there must be a conversion of au_lname from VARCHAR to NVARCHAR as part getting the rows, making the query non-sargable. The correct database data type (VARCHAR) needs to be specified as the parameter data type.

In .NET 4.0/VS2010 Beta 1, L2S handles this, producing a VARCHAR(8000) parameter, as SQL Server's autoparameterization does. Entity Framework produces NVARCHAR(4000), fixing the length problem but still using the a different data type than the table specifies.

NOTE: This will be fixed in the next beta version of .NET 4.0 to produce VARCHAR(8000). See complete post here.

In my next installment of my MSDN "Under The Table", I'll be writing about the performance implications of data access method code in detail. I do mention "fixed in the next release" WRT L2S and EF, and hope that it will be resolved before .NET 4.0 ships.

I finally got around to install .NET 4.0 CTP today on a SQL Server box to test a long-held theory. Didn't quite work out the way I'd thought.

When .NET support was introduced in SQL Server 2005, there was a lot of interest in how SQL Server and .NET Framework would approach the versioning story. The story was that SQL Server would always load "the latest version of the .NET runtime installed on the machine". SQL Server 2005 shipped with .NET 2.0.50727.42 and since then, there's been a number of Service Packs and even new releases of .NET, including 2.0 SP1, 3.0, 3.5, and 3.5 SP1. SQL Server 2008 shipped with a pre-requisite of .NET 3.5 SP1. And, up until this point, SQL Server has always loaded the latest version of the .NET runtime. That's because there is no choice.

The basic bits of .NET reside in mscorlib.dll and System.dll. Through all of the releases after SQL Server 2005, those bits are still in the "version 2.0" directory. The 3.0 and 3.5 version don't install a side-by-side version of mscorlib (or System.dll, or System.Data.dll or most of the "core" .NET framework). These are versioned by updated the 2.0 versions in-place. .NET 3.0 and 3.5 directories exist, but they only contain additional libraries, like WCF and LINQ for example. The current version of the core .NET framework at the time of this writing is 2.0.50727.3053.

Enter .NET 4.0, which includes side-by-side new versions of mscorlib.dll or the rest of the entire core. When the .NET 4.0 CTP is installed on a machine that includes SQL Server 2008, the version of .NET loaded by 2008 is 2.0.50727.3053 (sys.dm_clr_properties reports 2.0.50727), not 4.0. Specifying 4.0 in a configuration file will force SQL Server to load the 4.0 version, but then attempting to create and initialize a variable of type "geography" fails with the message "Method's type signature is not interop compatible".

It will be interesting to see how this plays out as .NET 4.0 gets closer to becoming a reality.