SQL 2012 Developer, Part 09 of 13: Advanced Data Types

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 appearance 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.

Each LearnNowOnline training course is made up of Modules (typically an hour in length). Within each module there are Topics (typically 15-30 minutes each) and Subtopics (typically 2-5 minutes each). There is a Post Exam for each Module that must be passed with a score of 70% or higher to successfully and fully complete the course.

Prerequisites

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.

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.

Video Runtime

157 Minutes

Time to complete

610 Minutes

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)