70-461 – spatial aggregates – geometry

Old_mapThe Exam 70-461: Querying Microsoft SQL Server 2012 syllabus has the following under the ‘Work with Data’ section:

Implement aggregate queries

New analytic functions; grouping sets; spatial aggregates; apply ranking functions

The highlighted bit it what I’m looking at here

Insert and select with geometry

So, first create a table with a column with the datatype of geography:

create table GeometricalStuff (GeometricalData geometry)
  

To insert a single point or a line you do this:

                            
insert into GeometricalStuff (GeometricalData)
values ('point (.5 .5)')

insert into GeometricalStuff (GeometricalData)
values ('linestring (1 1, 3 7)')
 

This inserts data which looks like this in the default view:

select GeometricalData from GeometricalStuff
0x00000000010C000000000000E03F000000000000E03F
0x000000000114000000000000F03F000000000000F03F00000000000008400000000000001C40

….but becomes more interesting when you select the ‘Spatial Result’ tab in SSMS
tabs

….because you get this:
Line

Drawing a triangle with sql geometry

I tried doing a triangle

insert into GeometricalStuff (GeometricalData)
values ('triangle (1 2, 3 8, 5 5)')

but ‘triangle’ isn’t a geometry keyword:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": 
System.FormatException: 24114: The label triangle (1 2, 3 8, in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).
System.FormatException: 
   at Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)
.
The statement has been terminated.

The keywords are:

Simple ones:

  • Point
  • LineString
  • CircularString
  • CompoundCurve
  • Polygon
  • CurvePolygon

Collections:

  • MultiPoint
  • MultiLineString
  • MultiPolygon
  • GeometryCollection1

So to do a triangle, you use the polygon keyword

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5, 1 1))')

which gives you:

select * from GeometricalStuff
0x00000000010404000000000000000000F03F000000000000F03F00000000000008400000000000001C4000000000000014400000000000001440000000000000F03F000000000000F03F01000000020000000001000000FFFFFFFF0000000003

which, in a prettier format is:
triangle

Closing the loop – Msg 6522

Something I found when I tried it first was that if you just specify the three points of the triangle like this:

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5))')

…it fails with:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": 
System.FormatException: 24118: The Polygon input is not valid because the exterior ring does not have enough points. Each ring of a polygon must contain at least four points.
System.FormatException: 
   at Microsoft.SqlServer.Types.GeometryValidator.ValidatePolygonRing(Int32 iRing, Int32 cPoints, Double firstX, Double firstY, Double lastX, Double lastY)
   at Microsoft.SqlServer.Types.Validator.Execute(Transition transition)
   at Microsoft.SqlServer.Types.ForwardingGeoDataSink.EndFigure()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseLineStringText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)
.
The statement has been terminated.

This is because you have to close the polygon. i.e the last point of the polygon has to be a repetition of the first one. So, this fails:

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5))')

….but this works

insert into GeometricalStuff (GeometricalData)
values ('polygon((1 1, 3 7, 5 5, 1 1))')

Geometry Aggregate Functions

So far all the above has just been mucking about with spatial datatypes – specifically geometric spatial datatypes. The aggregate function do stuff with the data (although I can’t really see that all of the functions are necessarily about aggregating things, in the normal sense of the word)

An easy-ish one is the ‘envelope’ function:

select Geometry::EnvelopeAggregate(GeometricalData) from GeometricalStuff

…which takes the triangle we prepared earlier

triangle

…and ‘envelopes‘ it in a rectangle shape:

envelope

select

Note you can’t do:

select Geometry::EnvelopeAggregate(*) from GeometricalStuff

….because you’d just get this error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
  1. Spatial Data Types Overview, URL: http://msdn.microsoft.com/en-us/library/bb964711.aspx []
Posted in sqlserver certification

70-461 notes #2 – demo-ing the date datatypes

Handbook to Salisbury Cathedral - South ViewI’ve had a play with the date and time data types:

create table demo_dates
(demo_datetime datetime,
demo_datetime2 datetime2,
demo_date date,
demo_time time,
demo_datetimeoffset datetimeoffset)

insert into demo_dates
select GETDATE(),
GETDATE(),
GETDATE(),
GETDATE(),
GETDATE()

select * from demo_dates

Gives:

2014-12-15 16:50:02.247	2014-12-15 16:50:02.2470000	2014-12-15	16:50:02.2570000	2014-12-15 16:50:02.2570000 +00:00

I presume that the datetime2 field is showing 0000 after the millisecond because that’s the extent of the precision of get_date().

Update: The presumption is justified :)

If you use sysdatetime() rather than getdate()….:

update demo_dates set demo_datetime2 = SYSDATETIME()

…then the datetime2 field gets populated down to the 7th decimal place

2014-12-15 16:50:02.247	2014-12-15 16:57:14.4219334	2014-12-15	16:50:02.2570000	2014-12-15 16:50:02.2570000 +00:00

If you try to stuff sysdatetime into an old style datetime field:

update demo_dates set demo_datetime = SYSDATETIME(), 
demo_datetime2 = SYSDATETIME()

…then sqlserver is happy enough – it just truncates it:

2014-12-15 17:02:41.983	2014-12-15 17:02:41.9836863	2014-12-15	16:50:02.2570000	2014-12-15 16:50:02.2570000 +00:00
Posted in sqlserver certification

querying microsoft sql server 2012 – 70-461 notes #1

Saint Nicholas hospital - The Salisbury GuideI’m having a go at Microsoft certification and I thought I might try to capture some of the stuff I’m learning here. What I capture is going to reflect my background and where I’m at in terms of SQL server knowledge.

In short, I’ve been working with databases since the 1990s, but for most of the time it’s only been Oracle databases. The depth of database experience is really valuable, but the downsides for this particular exam are that:

  • I instinctively think in ‘Oracle sql’ rather than ‘SQL sql’
  • it was over 20 years ago that I ‘learned SQL’. It’s changed a bit in that time!

Last Friday afternoon, I started watching this video by Bob Taylor, which I’ve found pretty useful.

Bob covers the topics that you need to know for the exam.

Here are my notes. Please don’t take anything here as gospel, there’s every chance I’ve misunderstood.

Database Objects

Notable datatypes

XML

Spatial

– Geometry is flat surface – e.g. office floor plan
– Geography – 3-dimensional or terrain e.g. journey from London to Salisbury

Date

No longer just the combined ‘datetime’, now also
– date
– time
The former caught me out recently. The ‘Date’ datatype in Oracle is actually the equivalent of the Sqlserver ‘datetime…..and I mistakenly used it assuming it was the same in both technologies. I ended up with a load of rows which I had to de-duplicate.
Also:
– datetime2, which has a greater degree of accurary
– datetimeoffset – which has timezones

See 70-461 notes #2 – demo-ing the date datatypes for more

Constraints

– check
– fk
– pk
– Unique
– default

I think all this is the same as Oracle, except I don’t think the ‘Default’ clause is implemented or considered as a constraint. I may be wrong

All follow the format: Column Definition, constraint Name, Type, Options

DML Triggers

– sets up Deleted and Inserted pseudo tables
– always part of current transaction

Views – restrictions

– 1024 columns max
– single query (don’t quite get how you’d have a multiple query)
– you can’t have a top without an order by
– there are restrictions around modifications. You can’t insert or update into multiple tables through a view. You could use an ‘instead of’ trigger

View – options

– schemabinding – seems to prevent the underlying tables changing(?)
– with encryption – to stop people looking at the view definition. Could be useful for shared hosting Database-as-a-service type scenarios
– with view_metadata – encourage some clients, including ODBC allocate memory better
– with check – stops updates which would take remove the data from the view e.g. it the view was chelsea_players and you tried to change the team from chelsea to man city, it would error

Querying Data

A niladic function is one with no arguments like get_date()
Try_convert
Parse (value as type using culture)
Format (value,format, culture)
Iif (boolean epxression, value to return if true, value to return if false)

Ranking options

Rank would be
1st= Chelsea
1st= Newcastle
3rd Man Utd

Dense rank would be
1st= Chelsea
1st= Newcastle
2nd Man Utd

Ntitle – didn’t follow this. Has to do with divvying up results

Row number – like the Oracle rownum1

Joins

Inner is default. Must be a match
Outer – ‘left’ will return all the ‘lefties’ even if there’s no ‘rightie’
Full (outer) – like doing outer joins on both sides
Cross – cartesian

Apply

For joining to the result set of a function
Cross apply – like an inner join
Outer apply – like an outer join

CTE

– Because the ‘WITH’ keyword is well-used within Sqlserver, it must have a semi-colon separating it from the previous statement. Some people write it as ‘;With CTE_TABLE’ so the semi-colon doesn’t get lost
– Like an inline view
– Can be recursive, to do a ‘connect by prior’ tree-structure hierarchy type thing

Nulls

Null <> null
Case
Isnull
coalesce

Pivot

From Excel
Converts rows <-> columns
Doesn’t always come up

Rollup and Cube

Select …
… group by rollup – one pass
… group by cube – many passes

Grouping Sets

GroupId – binary representation of the combination of grouping columns, I think
Only ever does one pass

For Xml – Raw

?

For Xml – Auto

?

For Xml – Explicit

Bob says ‘it’s difficult. If you have to use it, I’m sorry’

For Xml Path

Fetch and Offset

– For applications where people are paging through the result set
– need to keep the variables on the client side
– better to build a temp table

Modifying Data

Stored Procedures

– can be encrypted
– ‘execute as’ owner, self, caller or ‘user’
– with recompile. Will recompile everyyime for the parameters passed in. Bob says ‘it can be a hand-grenade to kill a fly’

Update

– can use the OUTPUT clause to capture data into an audit table, like a trigger

Merge

– aka Upsert.
Essentially does a Full Outer Join
Components are
– target
– source
– on statement (join)
– match
– not matched
– output

Troubleshoot and optimize

Join Types

– nested loops – source and probe. small tables
– Merge joins – large tables, both in the right sort order
– hash joins – large unsorted tables

Transactions

Enforce ACID-ity – begin, commit, rollback

Isolation Levels

– read uncommitted
– read committed (default)
– repeatable read
– serializable
in the systypes table. there’s also a Chaos level

Try/catch

No finally as yet

Cursors

– don’t use them. V expensive
– the steps. Remember to de-allocate

Hints

– ‘Hints’ is a misnomer – they are directives
– Option (optimize for (@city = ‘Salisbury’, @Postcode UNKNOWN)
– Be careful with hints at upgrade time

Resources

Register: www.prometric.com
MS Official Courseware: 10774A – Querying SQL Server 2002
MS Press: Training kit and microsoft SQL Server 2012 T-SQL Fundamentals

http://channel9.msdn.com/Events/TechEd

www.microsoft.com/learning
microsoft.com/technet
microsoft.com/msdn

The video

  1. A pedantic note – Bob said it was like rowid. I think that’s wrong.

    Rownum is the number of the row in the order it is returned, and would be 1,2,3,4. Rowid is the physical address of the row 0 it’s an encoded representation of the file and block address – looks like this: AAAAECAABAAAAgiAAA. I think from Bob’s description Row number is like the former []

Posted in sqlserver certification

php comments not working in wordpress

Automobile Association at Stonehenge Easter 1899This was a bit of a ‘Duh!’ moment.

I use the following to ‘comment out’ chunks of WordPress pages that I’m still working on

<?php /*
Some old rubbish that I have'nt finished working on yet
*/?>

This seemed to have ‘stopped working’.

The reason was my ‘Exec-PHP’ plugin was de-activated. I don’t remember de-activating it. I guess it’s possible that an automat(t)ic update might have done so, but I don’t know.

Posted in Uncategorized

sqlserver sketchnotes: oct-dec 2014

Slideshow

This slideshow requires JavaScript.

Powershell v5 and Powershell with Solarwinds

powershell v5 and Powershell with Solarwinds Sketchnote

powershell v5 and Powershell with Solarwinds Sketchnote

Webpage for the webinar is here: What’s New in PowerShell v5 – Petri. I think they said the talk would be available soon to watch – I’ll update the link.

Professional Networking Toolbox – Craig Purnell

the professional networking toolbox

Networking Toolbox

The Sqlpass page is: Archive Display
The video is: ▶ The Professional Networking Toolbox by Craig Purnell – YouTube
Craig’s website is: Craig Purnell | SQL Server, Databases, Technology, User Groups, Professional Development

Sqlserver – the Bug Talk

the bug talk sketchnote

the bug talk sketchnote

The Sqlpass page is: Database Administration Virtual Chapter > Home
The talk is on Youtube here: ▶ The “Bug talk” — sponsored by Dell Software – YouTube

Sqlserver Log Shipping

Sqlserver Log Shipping Sketchnote

Sqlserver Log Shipping Sketchnote

Excel tips

Excel Tips Sketchnote

Excel Tips Sketchnote

SQL Virtualization

SQL Virtualization Sketchnote

SQL Virtualization Sketchnote

T-Sql Habits

T-sql habits sketchnote

T-sql habits sketchnote

SQL Azure backups

sql azure backups sketchnote

sql azure backups sketchnote

Posted in sqlserver

Server hangs on restart – Powershell restart-computer does the trick

Poultry Cross - The Salisbury GuideRecently I attempted to re-start a server that I’d rdp-ed to.

Unfortunately, it seemed to hang at some stage during the shutdown process. I could still ping it and run some WMI stuff but I couldn’t rdp back onto it.

On a virtual server I’d have powered it off an back on again, but obviously that wasn’t an option, and I don’t have server room access.

This relatively significant – it was a production server and I was in danger of not being able to get it back up.

Anyway the fix was to use the PowerShell restart-computer with -force, as follows

restart-computer -computername server33 -force

Posted in powershell

sql server equivalent of Oracle ‘set feedback on’

Old George HotelI haven’t been able to find a T-sql equivalent of the Oracle ‘set feedback on’, which outputs a line telling you how many rows have been effected by the last sql statement.

The closest thing, I think, is:


select 'Rows updated: ' + CAST(@@ROWCOUNT as varchar(10))

Posted in sqlserver

Sketchnotes from 24 Hours of Pass

These are my notes from the ’24 Hours of Pass’ event of September 2014.

’24 Hours of Pass’ is an online event where 24 presentations about SQL Server are delivered by SQL experts within 24 hours. It’s very good indeed.

Don’t put too much reliance on the contents of the Sketchnotes. I may have misunderstood, or written things down wrong, or otherwise mucked things up.

The slides from each of the Presentations is currently on-line on the pages for each presentation here:

24 Hours of PASS: Summit 2014 Preview > Schedule

Better still, the presentations themselves will be online shortly.

Brent Ozar on ‘Developers: Who Needs a DBA?’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

01 Who Needs a DBA

Brian Knight on ‘Performance Tuning SQL Server Integration Services (SSIS)’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

02 Tuning SSIS

Allan Hirt on ‘Availability Groups vs. Failover Cluster Instances: What’s the Difference?’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

03 Availabilty Groups and Failover Cluster Indexes

Erin Stellato, Jonathan Kehayias on ‘Everything You Never Wanted to Know about Extended Events’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

04 Extended Events

Gail Shaw on ‘Guessing Games: Statistics, Heuristics, and Row Estimations’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

06 Estimations

Tim Chapman, Denzil Ribeiro on ‘Troubleshoot Customer Performance Problems Like a Microsoft Engineer’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

07 Troubleshooting

Argenis Fernandez on ‘Secure Your SQL Server Instance without Changing Any Code’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

08 - Securing sql

Joe Webb on ‘Hiring the Right People: Interviewing and Selecting the Right Team’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

09 - Hiring

Robert Cain, Bradley Ball, Jason Strate on ‘Zero to Hero with PowerShell and SQL Server’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

10 Posh and Sql

Chris Shaw, John Morehouse on ‘Real World SQL 2014 Migration Path Decisions’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

12 - Migration

Davide Mauri on ‘Agile Data Warehousing: Start to Finish’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

14 Agile DWH

Julie Koesmarno on ‘”I Want It NOW!” Data Visualization with Power View’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

15 - PowerView

Jen Stirrup on ‘Business Intelligence Toolkit Overview: Microsoft Power BI and R’

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

16 Power BI

Ryan Adams on ‘SQL Server AlwaysOn Quickstart

Abstract and slide deck: 24 Hours of PASS: Summit 2014 Preview > Sessions > Session Details

18 - always on

Posted in sqlserver

Filtering SSRS subscriptions from Sqlserver Jobs in Powershell

James Easton - The Salisbury Guide - Poultry CrossIf a Sqlserver instance hosts a Reporting Services database, then users’ report subscriptions are set up as Sql Server Agent jobs.

They are named like this:

E422ED2F-C8AD-445B-BF85-E4FD35EE8FAD
E668784B-0AA9-4DEC-8C3B-C9EB6CBC7ADE
E72E1822-408E-4008-B2D4-6065843A9C86
E8658059-BEF6-493A-908F-928B9B3F2065
EA5D435F-1095-4578-9325-EC3AB14327AC

If you query Sqlserver jobs from Powershell (or in SSMS) these can tend to ‘swamp’ the ‘normal’ jobs like so:

CAEC2E40-7AF5-4CB1-B7D2-66343A79471B
CDAEDD6F-07C6-4849-A8AA-5643F771B39B
CDF9A96D-747F-4439-855C-CE916E261646
CEEC38CC-3FCA-439E-8C32-33719F9291D2
database1 backup
DB64DBA4-D51E-43A0-BA17-A4B247075F70
DB834C0D-6206-4A22-87E6-821414EF95E4
DEF15AC7-8142-4909-A4A2-9D40B8CAC3AD
DF4E92E3-E689-44EF-B6C6-8065C18734F0
DF9471A5-4829-4C3E-ACF9-B0D912E26AD2
E05EC820-D572-4168-B14A-24A9102AB1EB
E13A783B-DAED-4F51-87AE-058B10AFCF60
E1E6746A-8F87-4DF3-BD30-3D88285F5305
E422ED2F-C8AD-445B-BF85-E4FD35EE8FAD
F441C4C6-342A-476B-B9B4-9B1FA7AFC545
F46BC346-24A6-41A8-8965-CC91E8F8BE26
F6EC8655-0487-435C-A390-622B692B63DE
F9174393-7A59-4E71-A74B-0E27A9A0118B
F9BB8EEA-BD93-4580-A5F8-DA754EF7DC32
F9C775B1-F724-4146-A4CD-64D0735ECE15
FC0FECED-BCC6-46AF-B7C9-E0551DA01E15
FDC6412F-C153-449E-B7E5-FD03281B69E4
FF2A1611-1763-4790-B7C9-64ABD83B710C
FF4A61A4-2580-4E94-AAEA-66FB7C7ABD9D
Warehouse Load Monday & Wednesday
Warehouse Load Sunday
Warehouse Load Thursday
Warehouse Load Tuesday & Friday

You can filter the SSRS stuff out in Powershell by filtering on the ‘Category’ property as follows:

dir Sqlserver:\sql\$ServerInstance\Jobserver\Jobs | where {$_.Category -ne 'Report Server'}

This gives you a more useful list

Name
----
db1 backup
Warehouse Load Monday & Wednesday
Warehouse Load Sunday
Warehouse Load Thursday
Warehouse Load Tuesday & Friday

Posted in powershell

Sql server: Generate sql to disable triggers

This seems to work:

SELECT 
     'disable trigger ' + USER_NAME(trig.schema_id) + '.' +  trig.name + 
     ' on ' + 
     USER_NAME(tab.schema_id) + '.' + OBJECT_NAME(trig.parent_object_id) +
     char(10) +
     'go'
FROM sys.objects trig
INNER JOIN sys.objects tab
    ON trig.parent_object_id = tab.object_id 
WHERE trig.type = 'TR'

It generates sql like this:

disable trigger dbo.tr_AfterUpdate on dbo.BigTable
go
disable trigger dbo.tr_BeforeUpdate on dbo.BigTable
go

Notes and Queries - Cathedral, bell tower, stonehenge

Posted in sqlserver