The end of collation woes in SQL Server

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

Querying distances using Spatial data types in SQL 2008

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