Course: SQL 2014 Advanced Development

You can use a SQL SELECT statement to read data from one or more tables in a relational database. This is such a common operation that SQL provides a way to save a SELECT statement as a persistent database object, called a view. In this course you’ll learn how to make use of these views. We’ll start by exploring how to create views, reviewing the syntax and the various options you can use. Then we’ll look at the Management Studio tools that help create views. Next you’ll learn how to create a view with a select statement. A computed column in SQL Server is a column that is computed using an expression that uses other fields in the table, a function, or constant values. You’ll learn how to use these columns virtually store data without duplicating it. Next you’ll learn how to use these special indexes to significantly enhance the performance of queries, whether a query uses the underlying view or not.

In this course we’ll start by looking at stored procedure features and the benefits of using them. Then you’ll learn about the syntax of the CREATE PROCEDURE statement, and some of the options for using stored procedures, including input and output parameters, return values, and results sets. You’ll also learn how to debug these code modules, as well as shape the results when executing a stored procedure. Then you’ll learn about a special kind of stored procedure, a trigger. A trigger runs automatically in response to changes to your data, letting you enforce complex business rules and perform other actions. You’ll learn about the different kinds of triggers as well as some risks in their use, and then we’ll get into the details of creating and using triggers effectively.

In this course you’ll learn about the three kinds of UDFs available in SQL Server and will see several examples of functions and how to use them. We’ll start by looking at scalar functions that return a single value, and then will explore the two types of table-valued functions. These latter functions let you return a table data type that acts like an in-memory table. Once you understand how to create functions, you’ll realize that now you have almost too many options for writing code: views, stored procedures, the different kinds of functions, and others. So we’ll discuss how to decide which kind of code module to use in different situations, as well as some potential performance issues with using functions.

In this course you’ll learn about transaction concepts and the ACID test that SQL Server uses to protect the integrity of data. You’ll also learn about locking and blocking, and how to avoid these inevitable effects of heavily used relational databases. You’ll see how to use transactions in applications, as well as how to handle compile and run time errors caused by your code. Then you’ll learn about how to take control of transactions and defined them explicitly in your code. Then we’ll wrap with a look at a relatively new feature of T-SQL, structured exception handling that has long been a feature of general purpose programming languages.

In this course you’ll learn how to write various kinds of code modules using C# code. You’ll learn the details of creating SQLCLR code manually as well as with Visual Studio. You’ll learn about the Sql data types and how using them can avoid data conversion issues. Most of the code you write will use basic ADO.NET for data access, but there is a special context connection you need to make to the database where the code is executing. I’ll take a close look at creating triggers and user-defined functions, then will explore the security issues of running SQLCLR code in the SQL Server process, and how to make it work. The course wraps up with some recommendations about how to decide whether to use T-SQL or SQLCLR code to create different kinds of code modules.

In this course you will explore two kinds of code modules that can be implemented with only .NET code. Those are Aggregates and User Defined Types. These module types are not support by T-SQL You will also see how to improve performance with ordered tabled valued functions that return data that is already sorted by defining them correctly in the database.

In this course you will learn about a number of useful features in Data Tools, including its use for both online and offline database development. In online mode you can use the new SQL Server Object Explorer that provides many of the same features as Object Explorer in SQL Server’s Management Studio. You’ll see how to create databases and other objects as well as change existing objects. You’ll also see how to compare database schemas, a very useful tool used internally by the data tools that you can also use directly. In offline data base development you can create a database project using a Visual Studio template. You can ether import an existing database schema or create objects from scratch. You will see how both these modes work and see how you can publish changes to an existing database.

In this course, you’ll learn about some advanced T-SQL statements and operators that can dramatically simplify individual statements to perform complex operations and queries. The APPLY operator lets you feed each row from the result set of one query to a second query. The MERGE statement lets you perform multiple actions with a single statement. You can use grouping sets to group data multiple ways in a single group by clause. You’ll learn about all these advanced query techniques, as well as how to safely execute dynamic SQL statements, and more. Then you’ll learn about how SQL server executes queries, taking a behind the scenes look what it takes to compile your beautiful T-SQL code in to a structure that SQL server can execute. You’ll learn about the stages of query compilation and explore the mysteries world of statics that are so critical to well performing queries. Then you’ll learn about execution plans and how to analyze them as well as what some of the most common operators do.

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.

In this course we’ll explore partitioning, and specifically how it’s implemented using SQL Server. Partitioning is a way to manage large amounts of data, and while certainly not a new concept, SQL Server has made improvements over the years that make your job as a database architect and developer easier. We’ll start off by discussing the evolution of partitioning in SQL Server. You’ll learn about partition keys, schemas and functions and how they affect tables, indexes, and the organization of your data. Once you have an understanding of the basics, we’ll move on to discuss methods for querying partitions. As time goes on, it may be necessary to make adjustments to your partitions. Next you’ll learn how to merge or split partitions, switch out partitions, and make modifications to partition schemes. The course then concludes with some tips on how performance tuning.

We’ll start off by discussing the specifics of a topic you might not expect—handling NULLs! I know it sounds surprising, but it’s important to really understand how to work with NULL values—especially if you plan to integrate with the Common Language Runtime. Next, we’ll address another common and seemingly straightforward task—the ranking of data. We’ll explore the specifics of how to rank grouped data using some T-SQL functions including ROW_NUMBER, RANK, DENSE_RANK, and NTILE. Next we’ll take a look at how to identify correlated subqueries and how you can possibly replace them with more efficient constructs. Finally, we’ll look at an alternative to subqueries, derived tables, and temporary tables—known as Common Table Expressions, or CTEs, they are similar to derived tables but they are self-referencing, can be used numerous times within the same query, and can be called recursively!

As business systems become more intricate, so does the T-SQL development needed to get at that data. In this course we’ll discuss some of the challenges facing developers including complex data types and structures; the “art” of writing efficient queries; and how to tackle and simplify multi-step processes. First we’ll discuss data types, implicit and explicit conversion, and precedence. Next, we’ll move on to discuss working with multinational data and understanding collation—which are SQL Server’s rules for sorting and comparing strings. We’ll discuss working with and storing hierarchical data using the hierarchyid data type. Next you’ll learn that even little things make a difference. We’ll explore how to boost data operations by using JOINs rather than subqueries, and how to check for inclusion using EXISTS. We’ll finish up by discussing temporary tables, table variables, user-defined table-valued functions, CTEs, worktables, and user-defined table type parameters. I’ll show you how using implicit transactions can help in the debugging process and why keeping things simple usually makes the most sense.

In this course we’ll explore SQL Server’s support for XML and how it’s improved since being introduced in SQL Server 2000. We’ll discuss SQL Server’s native XML data type—how to declare it as a variable or table column, how it differs from other data types, and how to load data into it using SELECT…FOR XML and the OPENROWSET function. Next you’ll learn the importance of indexing XML columns and that SQL Server supports multiple types of XML indexes including primary, secondary, and the Selective XML index. Then we’ll talk about XML Schemas—how they’re used to validate XML documents, SQL Server’s support for Lax Validation, how to register XML schemas and associate them with XML columns for validation. You’ll learn how to use the XML data type’s native XQuery methods as well as an introduction to FLWOR (flower) expressions. Finally, we’ll wrap up with some best practices and points to consider when you’re deciding if storing data as XML is an appropriate solution for your database application.