Learn your way! Get started

SQL 2014 Developer, Part 09: Advanced Data Types

with expert Don Kiely

Watch trailer

SQL 2014 Developer, Part 09: Advanced Data Types Trailer

Course at a glance

Included in these subscriptions:

  • Dev & IT Pro Video
  • Dev & IT Pro Power Pack
  • Power Pack Plus

Release date Release date 6/1/2015
Level Level Advanced
Runtime Runtime 2h 37m
Platform Platform Major browsers on Windows Major browsers on Windows Major browsers on Mac OSX Major browsers on Mac OSX Mobile Devices Mobile Devices
Closed captioning Closed captioning N/A
Transcript Transcript N/A
eBooks / courseware eBooks / courseware Included
Hands-on labs Hands-on labs Included
Sample code Sample code Included
Exams Exams Included

Enterprise Solutions
Enterprise Solutions

Need reporting, custom learning tracks, or SCORM? Learn More

Course description

In this course, you’ll learn about a system CLR type, HierarchyID, for storing hierarchical data. You’ll also take a look at using filestreams, a way to store large binary objects outside of SQL Server and let SQL Server manage the storage. You’ll also learn about sparse columns, which store null data more efficiently than before, and sequence objects that generate sequences of integers. You’ll never think of data types the same way! Then you’ll learn about the spatial models used in SQL Server to model both flat, or planar, areas as well as the surface of the earth. You’ll see how the appearances of areas on the earth are distorted when projecting them onto a flat surface, such as Google or Bing Maps on a computer screen. SQL Server uses two data types, Geography and Geometry, to support spatial data, and you’ll see their support for various shapes and how to use them in T-SQL code. You’ll also learn how to work with spatial data in SQL Server, such as to manipulate shapes as well as learn how to investigate the relationships between shapes.


This course assumes that you have a basic familiarity with the concept of relational databases and a basic understanding of what SQL Server is and the high-level tools in it, as well as how to create and manage objects using Management Studio. You should also have a basic understanding of how SQL Server implements security, including its authentication and authorization schemes, and how to assign permissions on securable objects to principals. You should know the fundamentals of Transact-SQL to write queries to retrieve and update data, and how to execute scripts using the query editor in Management Studio. The course spends a lot of time deep in T-SQL code, so you should feel comfortable writing basic queries that involve joins of multiple tables, as well as using built-in T-SQL functions and other code modules. It will be very helpful, but not absolutely necessary, to have experience with .NET development using Visual Studio 2010 or 2012 for the portions of the course that deal with SQL Server Data Tools.

Prepare for certification

This course will help you prepare for:
70-461 Querying Microsoft SQL Server
70-461 Querying Microsoft SQL Server 2012
70-464 Developing Microsoft SQL Server 2012 Databases

This course will help you earn:
MCSE: Business Intelligence
MCSA: SQL Server 2012
MCSE: Data Platform

Meet the expert

Don Kiely Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant, specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

Course outline

Hierarchy and Columns

Hierarchy ID Data Type (12:30)
  • Introduction (02:11)
  • The Hierarchy ID Data Type (01:17)
  • Old Ways of Doing Hierarchies (01:45)
  • HierarchyID Data Type (01:47)
  • Indexing a Hierarchy (00:24)
  • Depth-First Indexing (02:15)
  • Breadth-First Indexing (01:26)
  • Manipulating Hierarchies (01:03)
  • Summary (00:18)
Exploring a Hierarchy (08:44)
  • Introduction (00:17)
  • Demo: IsDescendedOf Method (02:43)
  • Demo: GetAncestor Method (03:54)
  • Demo: Graphical View of Hierarchy (01:26)
  • Summary (00:23)
Sparse Columns (15:52)
  • Introduction (00:55)
  • Sparse Columns and Column Sets (01:23)
  • Restrictions on Sparse Columns (02:17)
  • Recommendations (00:45)
  • Demo: Sparse Columns (06:03)
  • Demo: Inserting Sparse Values (03:44)
  • Summary (00:42)
Column Sets (12:09)
  • Introduction (01:00)
  • Restrictions on Column Sets (01:04)
  • Demo: Column Sets (04:58)
  • Demo: Using Column Sets (04:26)
  • Summary (00:39)

Filtered, Streams, Sequences

Filtered Indexes (09:21)
  • Introduction (01:37)
  • Using Filtered Indexes (01:06)
  • Demo: Using Filtered Indexes (02:02)
  • Demo: Filtered Index Example (04:14)
  • Summary (00:21)
FILESTREAM Storage (21:36)
  • Introduction (01:24)
  • FILESTREAM Storage (01:30)
  • FILESTREAM Data Type (00:30)
  • FILESTREAM Data Access Methods (01:16)
  • Gotchas and Limitations (01:06)
  • Demo: Enabling FILESTREAM (05:18)
  • Demo: Using FILESTREAM Data (03:41)
  • Demo: FILESTREAM Table (02:43)
  • Demo: FILESTREAM Folder (01:16)
  • Demo: Win32 Streaming APIs (02:33)
  • Summary (00:15)
Adding Sequences To Queries (22:19)
  • Introduction (01:34)
  • The Sequence Object (03:07)
  • Demo: Sequence Object (05:19)
  • Demo: Other Sequence Options (02:04)
  • Demo: Sequences with Tables (04:30)
  • Demo: Inserting Into Tables (05:11)
  • Summary (00:31)

Spatial Data Types

Introduction to Spatial Data (13:13)
  • Introduction (01:00)
  • Introduction to Spatial Data (00:55)
  • Spatial Models (00:33)
  • Geodetic Model (00:29)
  • The Mothership (00:47)
  • Locations on Earth (01:19)
  • Planar Model (00:51)
  • Cartesian Coordinate System (01:05)
  • Map Projection Distortion (04:48)
  • Spatial Data Standards (00:53)
  • Summary (00:28)
Spatial Data in SQL Server (23:01)
  • Introduction (00:38)
  • Spatial Data Types (03:38)
  • Spatial Data Types Collections (02:23)
  • SQLCLR Objects (01:23)
  • Representing Spatial Data (01:05)
  • Well-Known Text (03:58)
  • Demo: Using Geometry (03:17)
  • Demo: ST Methods (02:18)
  • Demo: Extension Methods (04:00)
  • Summary (00:18)
Manipulating Spatial Shapes (09:40)
  • Introduction (00:25)
  • Demo: Spatial Shapes (04:36)
  • Demo: STConvexHull (01:10)
  • Demo: STBuffer (01:04)
  • Demo: STLength (02:05)
  • Summary (00:17)
Interactions Between Objects (08:42)
  • Introduction (00:20)
  • Demo: STDifference (02:58)
  • Demo: STIntersection (01:02)
  • Demo: STSymDifference (00:59)
  • Demo: STUnion (00:46)
  • Demo: Using Multiple Methods (02:07)
  • Summary (00:27)