Press "Enter" to skip to content

Author: Kevin Feasel

Using GeoJSON Data

Published 2016-01-06 by Kevin Feasel

Jovan Popovic shows how to use data in GeoJSON format.

First, building data in GeoJSON format from a spatial type:

In geometry object are placed type of the spatial data and coordinates. In “property” object can be placed various custom properties such as address line, town, postcode and other information that describe object. SQL Server stores spatial information as geometry or geography types, and also stores additional properties in standard table columns.

Since GeoJSON is JSON, it can be formatted using new FOR JSON clause in SQL Server.

In this example, we are going to format content of Person.Address table that has spatial column SpatialLocation in GeoJSON format using FOR JSON clause.

Then, converting GeoJSON to Geography types:

New OPENJSON function in SQL Server 2016 enables you to parse and load GeoJSON text into SQL Server spatial types.

In this example, I will load GeoJSON text that contains a set of bike share locations in Washington DC. GeoJSON sample is provided ESRI and it can be found in https://github.com/Esri/geojson-layer-js/blob/master/data/dc-bike-share.json

Check them out.

Comments closed

Parallel Horizontal

Published 2016-01-06 by Kevin Feasel

Erik Darling looks at operators which result in serial plans:

In the past, there were a number of things that caused entire plans, or sections of plans, to be serial. Scalar UDFs are probably the first one everyone thinks of. They’re bad. Really bad. They’re so bad that if you define a computed column with a scalar UDF, every query that hits the table will run serially even if you don’t select that column. So, like, don’t do that.

What else causes perfectly parallel plan performance plotzing?

Commenting on one of his comments, I can name at least one good reason to use a table variable.

Comments closed

VLFs

Published 2016-01-06 by Kevin Feasel

Tom Roush talks VLFs, changes in DBCC LOGINFO, and Availability Groups:

Turns out SQL 2008R2 (where the original script worked) returns different fields than 2012 and 2014 (where it didn’t).

I figured I didn’t want to find out which version of the script to use every time I needed to run it on a server, so I told the script to figure that out by itself, and then run the appropriate hunk of code (example below)

This is a good explanation of how to back out of a complex situation.

Comments closed

Change Azure SQL Database Compatibility Level

Published 2016-01-06 by Kevin Feasel

Tom LaRock shows us how to change the compatibility level of an Azure SQL Database:

You can change the compatibility level of an Azure SQL Database.

It’s true! I know!

OK, so I’m a little excited about this one. See, I’ve been giving this talk on cardinality for the past couple of years now, so this is a hidden gem to me. When I found out this was possible I took out my demo scripts to see if changing the compatibility level would have any effect.

This is interesting, especially given that Management Studio doesn’t give you that option.  Know your T-SQL, folks.

Comments closed

SQLQueryStress Source Code Now Available

Published 2016-01-05 by Kevin Feasel

Adam Machanic has made the SQLQueryStress source code publicly available:

So here’s the official word: The attached source code is hereby released to the world, copyright and royalty free. You may use it, if you like, for whatever you want. Enjoy! If you use it for a public project, I would appreciate a mention in the acknowledgements section, but even that is not required. This source code is yours, warts and all. I was tempted to do some cleanup work, but at this point it’s just not something I’m ever going to touch again. I upgraded the project from Visual Studio 2005 to Visual Studio 2013, confirmed that it builds and seems to work, and that’s that.

Adam may never have used in a production scenario, but I certainly have, and SQLQueryStress is still the best free load simulator.  There’s also a GitHub repo thanks to Erik Ejlskov Jensen, so go forth and hack at some C# code.

Comments closed

Anchor Modeling

Published 2016-01-05 by Kevin Feasel

Steph Locke has a presentation on Anchor Modeling as 6th Normal Form:

Anchor Modelling moves you beyond third normal form and into sixth normal form. What does this mean? Essentially it means that an attribute is stored independently against the key, not in a big table with other attributes. This means you can easily store metadata about that attribute and do full change tracking with ease. The historical problem with this methodology is that it makes writing queries a real pain. Anchor Modelling overcomes this by providing views that combine all the attribute data together.

Anchor Modeling is a rather different approach, so if it sounds interesting, check out the tutorial.

Comments closed

Analyzing World Running Times

Published 2016-01-05 by Kevin Feasel

Andrie de Vries looks at average speed for different mens’ running events:

However, it seems that there might be two kinks in the line:

  • The first kink occurs somewhere between the 800m distance and the mile. It seems that the sprinting distances (and the 800m is sometimes called a long sprint) has different dynamics from the events up to the marathon.

  • And then there is another kink for the ultra-marathon distances. The standard marathon is 42.2km, and distances longer than this are called ultramarathons.

The analysis is done in R, and the code is available in the post.  Check it out.

Comments closed

What Makes A Good Post?

Published 2016-01-05 by Kevin Feasel

Tony Davis tells us what makes for a compelling article:

As an author, it’s a mistake to make wild assumptions about what the reader already knows about the technology, and why it’s useful. Don’t just ‘show the rooms’, but explain why they are interesting and how they might be used.

Beyond these fundamentals, I’ve written before about the simple ‘rules’ for writing a compelling blog post, and of the need to avoid hyperbole, colloquialisms, and acronym-overload, in favour of simple, plain English that can be understood internationally.

I’ll take a moment to talk about what makes you likely to show up on Curated SQL.  My expected reader is someone who has a few minutes to kill during the day and is looking for technical content.  They might occasionally have more time to dig into interesting topics, but more frequently, I’m imagining somebody on a pre-lunch coffee break.  To make things easier for those readers, I’m looking for four things:

  1. Is the post concise?  Coffee breaks won’t last long enough to watch a webinar.  This is most flexible criterion; if it’s interesting but a bit lengthy, I’m still liable to include it.
  2. Is the post technical?  We’re geeks; we want to learn something even on coffee break.  Non-technical posts can be great, but they aren’t really in the Curated SQL purview.
  3. Is the post at least somewhat novel?  If I’ve linked to someone taking a cursory look at Querystore, I’m probably not going to link to another overview of that product unless there’s something new in there.  With that said, “novel” is a hard goal; what’s new to me might be old hat to you, so there’s a balancing act here.
  4. Is the post well-written?  Poor writing makes technical content more difficult to understand, so sometimes I’ll skip an otherwise-interesting technical post and wait for somebody else to post a better version.

With that said, I gladly accept submissions via Twitter (@curatedsql).

Comments closed

Trickle Migration

Published 2016-01-05 by Kevin Feasel

Richie Lee encountered a use case for trickle migration:

Recently I needed to apply compression data on a particularly large table. One of the main reasons for applying compression was because the database was extremely low on space, in both the data and the log files. To make matters worse, the data and log files were nowhere near big enough to accommodate compressing the entire table in one go. If the able was partitioned then I could have done one partition at a time and all my problems would go away. No such luck.

Best way to eat an elephant, etc. etc.  Read the whole thing; you might be in a similar situation someday.

Comments closed

Use Parentheses Wisely

Published 2016-01-04 by Kevin Feasel

Jen McCown plays around with the AND and OR operators:

Specifically, how is it evaluated when your where clause says “WHERE This AND That OR Something AND that”, without any clarifying parenthesis?

Let’s play around with this. The simplest test scenario is a SELECT 1. If I get a 1 back, that means my WHERE clause evaluated to true, right? Right.

Parentheses should clarify statements.  If I see an “AND” and an “OR” in a WHERE clause, I want to see parentheses, even if you’ve gotten it right.  It’s too easy to misinterpret precedence.

1 Comment