“How do I contribute to dbatools?” with Drew Furgiuele

Published on: 2018-02-06

This weekend I caught up with Drew Furgiuele at SQL Saturday Cleveland and learned how to get involved with the open-source dbatools PowerShell module.

If you don’t use dbatools yet, what are you waiting for?  It’s an amazing community project that will help you automate your SQL Server work with over 350 ready-to-use commands.

After you start using dbatools, you might think of new commands you want to add or other features you want to improve – and the best part is that you can!

Even if writing PowerShell commands isn’t one of your strengths, there are many ways you can contribute to this excellent community project as Drew mentions in the video above.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

The Fastest Way To Locate Errors In Your SQL Query

Published on: 2018-01-30

Photo by N. on Unsplash

In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again.

Coming from a .NET background, I’m used to receiving relatively helpful error messages that point me to the precise location of the error in my code:

Pinpoint error finding

SQL Server Management Studio does a decent job too – except when it doesn’t.  Different versions of SSMS provide error line numbers differently.

Watch this week’s episode on YouTube!

In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query.  This means if you have a window full of T-SQL batch statements, your error message will be relative to the last “GO” in the editor before the syntax error:

In SSMS 2014, batch start locations aren’t considered:

Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:

While all of these error messages point me in the right direction, I’m too lazy to try and remember how each version of SSMS refers to the error location.  This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

You can double click on the error message to be taken directly to the line number referenced!

I know this sounds simple, but it’s one of those small things I never thought of trying until I saw someone else do it.  Little did I know what I was missing out on – especially in those giant hundreds/thousands of line long scripts.  but now that I know about it it’s the only way I locate my query errors.

This functionality also works if you have multiple errors listed in the Messages window.

But what about if your SQL query is all on one giant line (like if it was copied from somewhere or generated dynamically)?  You can use a regular expression to format your query first and then click on the error message to quickly navigate to the correct lines.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Inverted Polygons? How to Troubleshoot SQL Server’s Left Hand Rule

Published on: 2018-01-23

Last week we looked at how easy it is to import GeoJSON data into SQL Server’s geography datatype.

Sometimes your source data won’t be perfectly formatted for SQL Server’s spatial datatypes though.

Today we’ll examine what to do when our geographical polygon is showing us inverted results.

Watch this week’s vlog on my YouTube channel.

Colorado Is A Rectangle

If you look at the state of Colorado on a map, you’ll notice its border is pretty much a rectangle.

Roughly marking the lat/long coordinates of the state’s four corners will give you a polygon comprised of the following points:

Or in GeoJSON format (set equal to a SQL variable) you might represent this data like so:

DECLARE @Colorado nvarchar(max) = N'
{
	"type": "FeatureCollection",
	"features": [{
		"type": "Feature",
		"properties": {},
		"geometry": {
			"type": "Polygon",
			"coordinates": [
				[
					[-109.05005693435669,
						41.0006946252774
					],
					[-102.05157816410065,
						41.002362600596015
					],
					[-102.0421314239502,
						36.993139985820925
					],
					[-109.04520750045776,
						36.99898824162522
					],

					[-109.05005693435669,
						41.0006946252774
					]
				]
			]
		}
	}]
}
'

Note: four points + one extra point that is a repeat of our first point – this last repeated point let’s us know that we have a closed polygon since it ends at the same point where it began.

Viewing Our Colorado Polygon

Converting this array of points to the SQL Server geography datatype is pretty straight forward:

SELECT
	geography::STPolyFromText(
		'POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))'
		,4326) AS StateBoundary
FROM
	(
	SELECT 
		Long,
		Lat
	FROM
		OPENJSON(@Colorado, '$.features[0].geometry.coordinates[0]')
		WITH
			(
				Long varchar(100) '$[0]',
				Lat varchar(100) '$[1]'
			)
)d

We can then take a look at SQL Server Management Studio’s Spatial Results tab and see our polygon of Colorado drawn on a map.  You might notice something looks a little funny with this picture though:

WHY IS MY POLYGON AREA INVERTED?!!??!

Discerning eyes might notice that SQL Server didn’t shade in the area inside of the polygon – it instead shaded in everything in the world EXCEPT for the interior of our polygon.

If this is the first time you’ve encountered this behavior then you’re probably confused by this behavior – I know I was.

The Left-Hand/Right-Hand Rules

There is a logical explanation though for why SQL Server is seemingly shading in the wrong part of our polygon.

SQL Server’s geography datatype follows the “left-hand rule” when determining which side of the polygon should be shaded.  On the contrary, the GeoJSON specification specifies objects should be formed following the “right-hand rule.”

The left hand rule works like this: imagine you are walking the path of polygon – whatever is to the left of the line you are walking is what is considered the “interior” of that polygon.

So if we draw arrows that point in the direction that the coordinates are listed in our GeoJSON, you’ll notice we are making our polygon in a clockwise direction:

If you imagine yourself walking along this line in the direction specified, you’ll quickly see why SQL Server shades the “outside” of the polygon: following the left-hand rule, everything except for the state of Colorado is considered the interior of our polygon shape.

Reversing Polygon Direction

So the problem here is that our polygon data was encoded in a different direction than the SQL Server geography datatype expects.

One way to fix this is to correct our source data by reordering the points so that the polygon is drawn in a counter-clockwise direction:

-- Note: The middle three sets of points have been included in reverse order while the first/last point have stayed the same
DECLARE @ColoradoReversed nvarchar(max) = N'
{
	"type": "FeatureCollection",
	"features": [{
		"type": "Feature",
		"properties": {},
		"geometry": {
			"type": "Polygon",
			"coordinates": [
				[
					[-109.05005693435669,
						41.0006946252774
					],
					[-109.04520750045776,
						36.99898824162522
					],
					[-102.0421314239502,
						36.993139985820925
					],

					[-102.05157816410065,
						41.002362600596015
					],

					[-109.05005693435669,
						41.0006946252774
					]
				]
			]
		}
	}]
}
'

This is pretty easy to do with a polygon that only has five points, but this would be a huge pain for a polygon with hundreds or thousands of points.

So how do we solve this in a more efficient manner?

Easy, use SQL Server’s ReorientObject() function.

SELECT
	geography::STPolyFromText(
		'POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))'
		,4326).ReorientObject() AS StateBoundary
FROM
	(
	SELECT 
		Long,
		Lat
	FROM
		OPENJSON(@Colorado, '$.features[0].geometry.coordinates[0]')
		WITH
			(
				Long varchar(100) '$[0]',
				Lat varchar(100) '$[1]'
			)
)d

ReorientObject() does what we did manually above – it manipulates the order of our polygon’s points so that it changes the direction in which the polygon is drawn.

Note: SQL uses a different order when reversing the points using ReorientObject() than the way we reversed them above.  The end result ends up being the same however.

Regardless of which method you choose to use, the results are the same: our polygon of Colorado is now drawn in the correct direction and the Spatial Results tab visually confirms this for us:

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!