Category SQL Server

The SQL of the Game – Conway’s Game of Life

Joe Celko recently published a challenge to implement Conway’s Game of Life in SQL. At the start of this game, each cell of a 10×10 grid is either occupied or unoccupied by an imaginary creature that Celko calls a Merkle. Each turn, Merkles that have fewer than two neighbors die of loneliness while Merkles that […]

Export XML from SQL Server Using bcp

*** Update – see the comments below for a solution to an issue with unexpected CR/LF characters in the XML file generated with this method. Thanks to reader Anthony for diagnosing the issue and providing the solution. *** A recent project at work included a requirement to generate a well-formed XML file from the output […]

Extended Events – As Cool as I Thought They’d Be

Last week, a front-end developer came to me with an interesting problem. He had written some code that called a stored procedure t0 insert a row in a table that included a [ScheduledOn] column that was set to NULL by the insert and return a GUID value generated in the process, then immediately called a […]

Things That Make You Go “D’oh” – Introduction

I recently spent more time than I’d care to admit troubleshooting a T-SQL query that seemed to run fine most of the time but occasionally returned an error that, on the surface, didn’t make any sense. As I ruled out several of the more obvious possible causes, the error became one of those things that […]

Data Types for Storing Latitude and Longitude Coordinates

My last post described a trap for the unwary when using latitude and longitude values stored as the float data type in SQL Server, which left me wondering why anyone would use the float data type for this purpose. I questioned whether the float data type offers any advantages over the exact numeric types (decimal […]

Longitudes and Latitudes Stored as Approximate Numeric Types: A Trap for the Unwary

I often work with geospatial data, primarily points on the Earth’s surface represented by longitude-latitude pairs. Typically, a table will have columns for longitude and latitude stored as one of the number data types, usually decimal or numeric. Occasionally, though, I see longitude and latitude values stored in columns defined with the float data type […]