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(),

select * from demo_dates


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



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


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.
– datetime2, which has a greater degree of accurary
– datetimeoffset – which has timezones

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


– 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()
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


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


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


– 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


Null <> null


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’


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


– 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


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


No finally as yet


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


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


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



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


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:


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

database1 backup
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

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:

     'disable trigger ' + USER_NAME(trig.schema_id) + '.' +  trig.name + 
     ' on ' + 
     USER_NAME(tab.schema_id) + '.' + OBJECT_NAME(trig.parent_object_id) +
     char(10) +
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
disable trigger dbo.tr_BeforeUpdate on dbo.BigTable

Notes and Queries - Cathedral, bell tower, stonehenge

Posted in sqlserver

Generate sqlserver table creation scripts from Powershell

NPG Ax143583; Edith Olivier by Lady Ottoline Morrell
I didn’t know you could do this – the ‘table’ object in the SQL provider has a ‘script’ method, so you can do the following:

$ServerInstance = "whatever"
$DatabaseName = "whatever"
foreach ($TableName in dir Sqlserver:\sql\$ServerInstance\databases\$DatabaseName\tables\) {

It’s a rough equivalent to doing a right click >> ‘Script table as’ >> ‘Create’ in SSMS, except you can do it for some or all of the tables on a database.

I’m looking forward to playing with the ‘script’ method for other database objects.

Pic: Edith Olivier and the Palladian Bridge in the grounds of Wilton House, by Lady Ottoline Morrell [Public domain], via Wikimedia Commons

Posted in powershell