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.

[toc]

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

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: http://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
http://www.microsoft.com/learning
microsoft.com/technet
microsoft.com/msdn

The video

Advertisements