Month: January 2008

Collation is a wonderful thing. It’s what makes sure that Greek people can see their data in a Greek order, and Germans can see theirs in a German order. It helps us determine whether data should be case sensitive, or whether those little dots and circles that Swedes use really matter. But if you have a database that has different collation settings to the SQL instance it’s on, it can be a pain. Suppose you have an instance of SQL Server, and you decide to restore a database on it. It may be an upgrade, it may be part of … Continue reading The end of collation woes in SQL Server

SQL Server 2008 has spatial types which are very useful for all kinds of applications. I thought it would be nice to produce one of those charts like you get in maps, telling you the distance between cities. So I start by populating the data. This uses the geography data type, which understands about the spherical nature of the world. The data is populated using POINTS, using data which I found around the internet. create table dbo.Cities (id int identity(1,1) primary key,name char(3) not null,fullname varchar(100) not null,location geography); insert into dbo.cities values (‘ADL’,’Adelaide’, ‘POINT(-34.92 138.58)’),(‘MEL’,’Melbourne’, ‘POINT(-37.78 144.97)’),(‘SYD’,’Sydney’, ‘POINT(-34 151)’),(‘PER’,’Perth’, … Continue reading Querying distances using Spatial data types in SQL 2008