SQL 2014 Developer, Part 9 of 13: Advanced Data Types

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.

Lesson 1:

• The Hierarchy ID Data Type
• Old Ways of Doing Hierarchies
• HierarchyID Data Type
• Indexing a Hierarchy
• Depth-First Indexing
• Breadth-First Indexing
• Manipulating Hierarchies.

Lesson 2:

• Demo: IsDescendedOf Method
• Demo: GetAncestor Method
• Demo: Graphical View of Hierarchy.

Lesson 3:

• Sparse Columns and Column Sets
• Restrictions on Sparse Columns
• Recommendations
• Demo: Sparse Columns
• Demo: Inserting Sparse Values.

Lesson 4:

• Restrictions on Column Sets
• Demo: Column Sets
• Demo: Using Column Sets.

Lesson 5:

• Using Filtered Indexes
• Demo: Using Filtered Indexes
• Demo: Filtered Index Example.

Lesson 6:

• FILESTREAM Storage
• FILESTREAM Data Type
• FILESTREAM Data Access Methods
• Gotchas and Limitations
• Demo: Enabling FILESTREAM
• Demo: Using FILESTREAM Data
• Demo: FILESTREAM Table
• Demo: FILESTREAM Folder
• Demo: Win32 Streaming APIs.

Lesson 7:

• The Sequence Object
• Demo: Sequence Object
• Demo: Other Sequence Options
• Demo: Sequences with Tables
• Demo: Inserting Into Tables.

Lesson 8:

• Introduction to Spatial Data
• Spatial Models
• Geodetic Model
• The Mothership
• Locations on Earth
• Planar Model
• Cartesian Coordinate System
• Map Projection Distortion
• Spatial Data Standards.

Lesson 9:

• Spatial Data Types
• Spatial Data Types Collections
• SQLCLR Objects
• Representing Spatial Data
• Well-Known Text
• Demo: Using Geometry
• Demo: ST Methods
• Demo: Extension Methods.

Lesson 10:

• Demo: Spatial Shapes
• Demo: STConvexHull
• Demo: STBuffer
• Demo: STLength.

Lesson 11:

• Demo: STDifference
• Demo: STIntersection
• Demo: STSymDifference
• Demo: STUnion
• Demo: Using Multiple Methods.