Course: SQL 2014 Advanced Development

$329.00
$398.09 incl. vat

duration: 32 hours |

Language: English (US) |

access duration: 90 days |

Details

In this SQL 2014 you will learn all the advanced functionalities in SQL Server. So are the views extensively discussed and the course also features such as stored procedures, functions, transactions, SQL CLR, designs, localDB databases, partitions and XML. Overall, a very comprehensive course where you get all the tools to increase your knowledge in this type of field.

Result

After completing this course, you are familiar with the advanced features of SQL Server.

Prerequisites

You have basic knowledge of SQL Server, Visual Studio, Transact-SQL and .NET.

Target audience

Software Developer, Database Administrators

Content

SQL 2014 Advanced Development

32 hours

SQL 2014 Developer: Views

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.

SQL 2014 Developer: Stored Procedures

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.

SQL 2014 Developer: Functions

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.

SQL 2014 Developer: Transactions

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.

SQL 2014 Developer: Common Language Runtime

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.

SQL 2014 Developer: Advanced SQL CLR

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.

SQL 2014 Developer: Design and Deployment

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.

SQL 2014 Developer: LocalDB Databases

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.

SQL 2014 Developer: 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.

SQL 2014 Developer: Partitions

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.

SQL 2014 Developer: Complex Querying

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!

SQL 2014 Developer: Coding Techniques

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.

SQL 2014 Developer: Working with XML

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.

Course options

We offer several optional training products to enhance your learning experience. If you are planning to use our training course in preperation for an official exam then whe highly recommend using these optional training products to ensure an optimal learning experience. Sometimes there is only a practice exam or/and practice lab available.

Optional practice exam (trial exam)

To supplement this training course you may add a special practice exam. This practice exam comprises a number of trial exams which are very similar to the real exam, both in terms of form and content. This is the ultimate way to test whether you are ready for the exam. 

Optional practice lab

To supplement this training course you may add a special practice lab. You perform the tasks on real hardware and/or software applicable to your Lab. The labs are fully hosted in our cloud. The only thing you need to use our practice labs is a web browser. In the LiveLab environment you will find exercises which you can start immediatelyThe lab enviromentconsist of complete networks containing for example, clients, servers,etc. This is the ultimate way to gain extensive hands-on experience. 

WHY_ICTTRAININGEN

Via ons opleidingsconcept bespaar je tot 80% op trainingen

Start met leren wanneer je wilt. Je bepaalt zelf het gewenste tempo

Spar met medecursisten en profileer je als autoriteit in je vakgebied.

Ontvang na succesvolle afronding van je cursus het officiële certificaat van deelname van Icttrainingen.nl

Krijg inzicht in uitgebreide voortgangsinformatie van jezelf of je medewerkers

Kennis opdoen met interactieve e-learning en uitgebreide praktijkopdrachten door gecertificeerde docenten

Orderproces

Once we have processed your order and payment, we will give you access to your courses. If you still have any questions about our ordering process, please refer to the button below.

read more about the order process

What is included?

Certificate of participation Yes
Monitor Progress Yes
Mobile ready Yes
Sharing knowledge Unlimited access to our IT professionals community
Study advice Our consultants are here for you to advice about your study career and options
Study materials Certified teachers with in depth knowledge about the subject.
Service World's best service

Platform

Na bestelling van je training krijg je toegang tot ons innovatieve leerplatform. Hier vind je al je gekochte (of gevolgde) trainingen, kan je eventueel cursisten aanmaken en krijg je toegang tot uitgebreide voortgangsinformatie.

Life Long Learning

Follow multiple courses? Read more about our Life Long Learning concept

read more

Contact us

Need training advise? Contact us!


contact