UTS Short Course

1
UTS Short Course

• SQL Server 2008 for Developers

2
Course Website

• http//www.ssw.com.au/ssw/events/2008UTSSQL/resour
  ces.aspx
• Course Timetable
• Course Materials

3
About Cameron

• Solution Architect for www.ssw.com.au
• MCP (SQL Server, VB.Net, MS Access, MS Excel,
  Networking)
• More than 15 years experience in software
  development and project management at numerous
  organizations
• Email CameronTownshend_at_ssw.com.au

4
Agenda
Session 2 T-SQL Enhancements

• New Data Types
• Inline variable assignment
• Table Value Parameters
• DDL Triggers
• Ranking Functions
• CTE (Common Table Expressions)
• TOP , XML Queries
• PIVOT/UNPIVOT
• ADO.NET

5
New Data Types

• Filestream
• Sparse Columns
• Filtered Indexes
• Spatial Data
• HierarchyID
• DATE and TIME data types

6
Filestream

• I am designing an Employee table that needs to
  cater for employee photographs. What should I do?
• You can store the image in the database (blob)
• You can store a URL to the image in the database
  (Recommended for SQL2005)
• http//www.ssw.com.au/ssw/standards/Rules/RulestoB
  etterSQLServerdatabases.aspxImageReplaceWithURL

7
Filestream

• You can store the image in the database (blob)
• Database grows really big
• Backups take longer
• Your code needs to convert the bytes back into an
  image
• Your images are in sync with your data

8
Filestream

• You can store a URL to the image in the database
  (Recommended for SQL2005)
• Database is smaller
• Easily validate or change the image (you can look
  at it on the file system)
• Data could become out of sync with the file
  system
• Need to backup the database and the file system

9
Filestream

• Filestream to the rescue
• Implemented as a special varbinary(max) where
  data is stored as a blob on the file system
• Allows you to have transactionally consistent
• Integrated backup and restore of your binary
  images
• Size limitation is the size of your hard drives
  free space

10
Problem

• Q\ Ive got an Contacts table with 200,000 rows.
  To support the latest Web 2.0 trends we want to
  also record the contacts blog address. What
  should I do?

11
Solution?

• A\ Just add a new BlogUrl column in
• Q\ Whats the problem with that?
• A\ Most of the entries in your table will be
  null, it wastes a lot of database space

12
Solution

• Use a sparse column
• These columns are new to SQL 2008
• They are optimized for storing NULL values

13
Sparse Columns
14
Sparse Columns
15
Sparse Columns
16
Filtered Indexes

• Allows you to add an index to a column with a
  where clause
• Useful for indexing columns with null values in
  them

17
Spatial Data Types

• Geometry
• Geography
• Virtual Earth Integration
• Planar vs Geodetic Algorithms
• Separate install for spatial assemblies

18
Spatial Data Types

• Geometry allows you to represent and process
  polygons

19
How do I represent an Org Chart?

• Employee with a ManagerID column (self join)
• New HierarchyID data type
• Can be indexed using
• Depth First
• Breadth First

20
Depth First Search

• A depth-first index, rows in a subtree are stored
  near each other. For example, all employees that
  report through a manager are stored near their
  managers' record.

21
Breadth First Search

• A breadth-first stores the rows each level of the
  hierarchy together. For example, the records of
  employees who directly report to the same manager
  are stored near each other.

22
Date and Time

• SQL 2008 now has the following data types to
  represent time
• DateTime
• SmallDateTime
• Date
• Time
• DateTime2 really a BigDateTime
• Min Date is 1st Jan 0000
• Max date 31st Dec 9999 Y10K BUG!!
• DateTimeOffset

23
Inline Variable Assignment

• Instead of
• DECLARE _at_myVar intSET _at_myVar 5
• You can
• DECLARE _at_myVar int 5

24
Table Value Parameters

• Pass in a table as an argument to a SPROC
• Instead of
• exec sp_MySproc 'murphy,35galen,31samuels,27col
  ton,42
• SPROC needs to then parse that string

25
Table Value Parameters

• You can do this instead
• CREATE TYPE PeepsType AS TABLE (Name varchar(20),
  Age int) DECLARE _at_myPeeps PeepsType INSERT
  _at_myPeeps SELECT 'murphy', 35 INSERT _at_myPeeps
  SELECT 'galen', 31 INSERT _at_myPeeps SELECT
  'samuels', 27 INSERT _at_myPeeps SELECT 'colton',
  42exec sp_MySproc2 _at_myPeeps

26
Table Value Parameters

• The SPROC would look like this
• CREATE PROCEDURE sp_MySproc2(_at_myPeeps PeepsType
  READONLY)

27
DDL Triggers

• Auditing, regulating schema changes, capture
  events on create_table, alter_procedure,
  drop_login etc

28
Ranking Functions

• RANK() Returns the rank of each row within the
  partition of a result set. The rank of a row is
  one plus the number of ranks that come before the
  row in question. It works a lot like ROW_NUMBER()
  except that it will not break ties, you will not
  get a unique value for ties.
• DENSE_RANK() - Returns the rank of rows within
  the partition of a result set, without any gaps
  in the ranking. The rank of a row is one plus the
  number of distinct ranks that come before the row
  in question. DENSE_RANK() works exactly like
  RANK() but will remove the skipping of numbers in
  the tie.
• NTILE(ltexpressiongt) - Distributes the rows in an
  ordered partition into a specified number of
  groups. The groups are numbered, starting at one.
  For each row, NTILE returns the number of the
  group to which the row belongs.

29
PIVOT
30
CTE (Common Table Expression) Before
31
CTE (Common Table Expressions) After
32
More features

• ROW NUMBER see example
• TRY/Catch in queries see example
• Top also insert update delete, select top with
  tie feature( if top 10 and there are 15 that
  match number 10 will bring back all 15)
• Output see example

33
Working with XML - RAW

• SELECT TOP 3 Person.FirstName, Person.LastName,
  PersonPhone.PhoneNumber
• FROM AdventureWorks.Person.Person
• INNER JOIN AdventureWorks.Person.PersonPhone ON
• PersonPhone.BusinessEntityID
  Person.BusinessEntityID
• FOR XML RAW

34
Working with XML - RAW

• ltrow FirstName"Ken" LastName"Sánchez"
  PhoneNumber"697-555-0142"/gt
• ltrow FirstName"Terri" LastName"Duffy"
  PhoneNumber"819-555-0175"/gt
• ltrow FirstName"Roberto" LastName"Tamburello"
  PhoneNumber"212-555-0187"/gt

35
Working with XML - RAW

• What happened to our relationships?
• RAW doesnt show our table relationships but
  gives us a flat XML hierarchy

36
Working with XML - Auto

• SELECT TOP 3 Person.FirstName, Person.LastName,
  PersonPhone.PhoneNumber
• FROM AdventureWorks.Person.Person
• INNER JOIN AdventureWorks.Person.PersonPhone ON
• PersonPhone.BusinessEntityID
  Person.BusinessEntityID
• FOR XML AUTO

37
Working with XML - Auto

• ltAdventureWorks.Person.Person FirstName"Ken"
  LastName"Sánchez"gt
• ltAdventureWorks.Person.PersonPhone
  PhoneNumber"697-555-0142"/gt
• lt/AdventureWorks.Person.Persongt

38
Working with XML - Auto

• Great, but what if I needed to format the XML to
  output into a certain schema

39
Working with XML - Explicit

• SELECT TOP 3
• 1 AS TAG,
• NULL AS PARENT,
• BusinessEntityID AS Person!1!BusinessEntityID,
• FirstName AS Person!1!FirstName!ELEMENT
• FROM AdventureWorks.Person.Person
• FOR XML EXPLICIT

40
Working with XML - Explicit

• ltPerson BusinessEntityID"285gt
• ltFirstNamegtSyedlt/FirstNamegt
• lt/Persongt
• ltPerson BusinessEntityID"293"gt
• ltFirstNamegtCatherinelt/FirstNamegt
• lt/Persongt
• ltPerson BusinessEntityID"295"gt
• ltFirstNamegtKimlt/FirstNamegt
• lt/Persongt

41
Working with XML - Explicit

• Can control how the XML gets output
• Ugly query
• Is there a better way?

42
Working with XML - PATH

• SELECT TOP 3
• BusinessEntityID "Person/_at_BusinessEntityID",
• FirstName "Person/FirstName"
• FROM AdventureWorks.Person.Person
• FOR XML PATH ('')

43
Working with XML - XQuery

• XQuery is a query language for XML Data

44
XQuery Declaring our XML string

• DECLARE _at_x XML
• SET _at_x 'ltchristmaslistgtltperson name "betty"
  gift "camera"/gtltperson name "zach" gift
  "elmo doll"/gtltperson name "brad" gift
  "socks"/gtlt/christmaslistgt'

45
XQuery - Querying

• SELECT _at_x.exist('/christmaslist/person_at_gift"sock
  s"')
• SELECT _at_x.exist('/christmaslist/person_at_gift"lump
  of coal"')
• SELECT _at_x.exist('/christmaslist/person_at_gift"Sock
  s")
• SELECT _at_x.value('/christmaslist1/person1/_at_name
  ', 'VARCHAR(20))
• SELECT _at_x.query('/christmaslist/person')

46
XQuery - Querying

• query()
• value()
• exist()
• nodes()
• modify()

47
XQuery - Resources

• http//msdn.microsoft.com/en-us/library/ms345117.a
  spx

48
ADO.NET

• ADO.NET gives you full control over how you
  access and retrieve data from the data source
• Strongly typed data sets
• Work in disconnected mode

49
ADO.NET

• SQLConnection
• Manages the connection to the database
• SQLCommand
• Defines the data to be read, updated etc.
• SQLDataAdapter
• Runs the SQLCommand against the database
• DataSet
• A complete in-memory copy of the data (tables,
  relationships, data types)
• Search, filter, navigate your data without even
  being connected to the database!

50
Session 2 Lab

• T-SQL Enhancements
• Download from Course Materials Site (to
  copy/paste scripts) or type manually
• http//www.ssw.com.au/ssw/events/2008UTSSQL/resour
  ces.aspx

51
Where Else Can I Get Help?
Where else can I get help?

• Free chats and webcasts
• List of newsgroups
• Microsoft community sites
• Community events and columns

www.microsoft.com/technet/community
52
Two things

• justingking_at_ssw.com.au

53

• Thank You!
• Gateway Court Suite 10 81 - 91 Military Road
  Neutral Bay, Sydney NSW 2089 AUSTRALIA
• ABN 21 069 371 900
• Phone [phone] Fax [phone]
• info_at_ssw.com.auwww.ssw.com.au