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.

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.