Geography, SQL Server and Big Updates!

Ran into a situation today where I had the following problem.

A database was given to me that had places (with Geography types in it) but I needed to be able to search based on a “Region”. A region, for this case, would be something like United States, Poland, Canada, etc. I also had a table of these Regions and a place holder in my “Places” table to assign a Place to a Region.

What did I do? I took advantage of these awesome types!

My SQL queries looked like this:

declare @area geography

-- ROUGH USA Border
select @area = geography::STGeomFromText('Polygon((-126.9075399967648 47.68027463274793,
-125.5933469520973 41.87061363994702,
 -124.5566677584721 38.34492930185334,
 -121.622900826232 34.85168794468432,
 -118.3275314955308 32.43508337044441,
 -115.5103796044553 32.19705726589449,
 -113.0654003277932 31.77687371296225,
 -110.9163553600406 31.16613810081651,
 -107.8958428990167 31.21725190814499,
 -105.3582829747764 30.3390488806813,
 -103.6229070416005 28.49450717252408,
 -101.6198812206448 29.23270393932756,
 -100.7263754686541 28.00898678954991,
 -99.49318812859124 26.32947301175079,
 -97.60389066822228 25.60910944803409,
 -96.63744452913633 25.93235911342899,
 -96.66023831081218 27.52354912193902,
 -95.02257544177812 28.59771331453454,
 -93.634596213173 29.23882980485253,
 -92.19747252871773 29.18153225747653,
 -90.40642605344159 28.7293375847932,
 -88.6334266135873 28.88384249687773,
 -87.73643471760506 29.47029719641609,
 -85.87623029108073 29.53341980232942,
 -84.08101685380154 29.52982504860095,
 -83.45417798638813 29.01842045470445,
 -82.38844010677963 24.24859712043354,
 -79.93327843767648 24.20260368451352,
 -79.46386162873068 25.97644839336022,
 -79.41762247493236 27.57497473469773,
 -79.91028751745607 29.10473903971489,
 -80.72068003661178 30.76965704143833,
 -79.97159623672641 32.13755430502794,
 -78.56208210583684 33.05511984133363,
 -77.67426061440897 33.75436948960927,
-75.22615439141757 34.85613954463283,
-74.25971143052236 36.63662366903175,
-73.53523756113569 38.47662633954932,
-72.62489706944318 39.64529717376119,
-69.30794873940781 41.15751725724563,
-68.44618965381588 43.16536523423157,
-67.08150916498779 44.0404291840986,
-66.1853049222763 45.88453459280221,
-66.37599482234019 47.69293757149508,
-70.97725783051568 48.40288239949412,
-73.7478063212333 46.77463218866193,
-76.50777828662675 44.75251441663305,
-78.25661258474381 44.01667413433834,
-80.38458637693319 43.06328850460273,
-81.86159747540829 42.11343964044789,
-81.5698248277223 43.05972264831926,
-81.68301357631532 44.43194956690464,
-82.47213088304741 45.78012745632746,
-83.55860543115054 47.03329808948071,
-87.06682093928856 47.65337611719893,
-89.34057911681968 47.9782542785637,
-91.04822120347217 48.64900196116928,
-94.56095082763974 49.72287463654621,
-96.54901877261908 49.20586850215418,
-126.4638084250994 49.34574515284749,
-126.9075399967648 47.68027463274793 ))', 4326)

UPDATE Place
SET RegionKey = 123 -- USA
WHERE RegionKey IS NULL
AND
@area.STIntersects(PlaceGeography) = 1

This does the following:

  1. Sets up a new variable called @area
  2. Defines that area as a polygon that roughly covers the good ol’ US of A
  3. Then updates my “Place” table with a region code that matches the RegionKey of the Region table with items that intersect (or actually, lie within) the area I defined

Pretty easy but believe it or not I had trouble finding an example like this any where on the interwebs so I thought I’d share.

Happy geographic coding!

SQL Fragmented Index Query

I put together this query on SQL Server 2008 to help me organize the fragmented indexes and thus get me pointed in the right direction for troubleshooting.

SELECT dbps.database_id, dbps.OBJECT_ID, dbps.index_id, sysi.name,
dbps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS dbps
INNER JOIN sys.indexes AS sysi ON dbps.OBJECT_ID = sysi.OBJECT_ID
AND dbps.index_id = sysi.index_id
WHERE dbps.database_id = DB_ID()
AND dbps.index_id > 0
ORDER BY dbps.avg_fragmentation_in_percent DESC
GO

Many of our systems automatically defrag in overnight tasks, but when things stop working, this helps me get systems back up and running.

Using the SQL Server Performance Dashboard Missing Indexes Report

Using the SQL Server Performance Dashboard Missing Indexes Report – Driving The Database Engine – Site Home – MSDN Blogs.

I NEEDED this article today. We are on the hunt for missing indices and we don’t always have access to sql management studio or other tools that should be part of our workbench. The query in this article fit the bill perfectly. Thanks!!