Developing SQL Databases, Part 2

Under den här kursen fördjupar du dina kunskaper om databasutveckling med SQL Server. Du lär dig bland annat att hantera avancerad tabelldesign och indexering, In-Memory-tabeller, att arbeta med XML samt använda geospatial data.

Denna kurs är lämplig oavsett om man har SQL Server On-Premise eller som en molntjänst (såsom Azure SQL Database eller Azure SQL Managed Instance).

Kursmaterialet är på engelska, med följande innehåll:

Module 3: Advanced Table Designs

Designing a physical database implementation includes planning the filegroups, how to use partitioning to manage large tables, and using compression to improve storage and performance. Temporal tables are a new feature in SQL Server and offer a straightforward solution to collecting changes to your data..

• Partitioning data
• Compressing Data
• Temporal Tables

Module 7: Columnstore Indexes

This module highlights the benefits of using columnstore indexes on large datasets and the considerations needed to use columnstore indexes effectively in your solutions.

• Introduction to Columnstore indexes
• Creating Columnstore indexes
• Working Columnstore indexes

Module 12: Using In-Memory Tables

Memory-optimized tables are primarily stored in memory, which provides the improved performance by reducing hard disk access. Natively compiled stored procedures further improve performance over traditional interpreted Transact-SQL. This module covers the creation of in-memory tables and native stored procedures.

• In-Memory tables
• Native Stored Procedures

Module 14: Storing and Querying XML Data in SQL Server

This module covers the XML data type, schema collections, typed and un-typed columns and appropriate use cases for XML in SQL Server.

• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type
• Using the T-SQL FOR XML Statement
• Getting Started with xQuery

Module 15: Storing and Querying Spatial Data in SQL Server

This module describes spatial data and how this data can be implemented within SQL Server.

• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

Module 16: Storing and Querying Blobs and Text Documents in SQL Server

Traditionally, databases have been used to store information in the form of simple values—such as integers, dates, and strings—that contrast with more complex data formats, such as documents, spreadsheets, image files, and video files. As the systems that databases support have become more complex, administrators have found it necessary to integrate this more complex file data with the structured data in database tables. SQL Server provides several ways to integrate these files—that are often known as Binary Large Objects (BLOBs)—and enable their content to be indexed and included in search results. In this module, you will learn how to design and optimize a database that includes BLOBs.

• Considerations for BLOB Data
• Working with FileStream
• Using Full-Text Search

Module 18: Performance and Monitoring

This module looks at how to measure and monitor the performance of your SQL Server databases.

• Extended Events
• Live Query Statistics
• Optimize Database File Configuration
• Metrics

Målgrupp och förkunskaper

Kursen riktar sig utvecklare och tekniker som vill lära sig att skapa databaslösningar med SQL Server.

Du bör behärska grundläggande databasutveckling med SQL Server 2012 eller senare. Till detta rekommenderar vi vår kurs Developing SQL Databases Part 1.

För att alltid hålla en hög kvalitet på våra teknikkurser använder vi både engelsk- och svensktalande experter som kursledare.

Detaljerad information

Advanced Table Designs

The physical design of a database can have a significant impact on the ability of the database to meet the storage and performance requirements set out by the stakeholders. Designing a physical database implementation includes planning the file groups, how to use partitioning to manage large tables, and using compression to improve storage and performance. Temporal tables are a new feature in SQL Server and offer a straightforward solution to collecting changes to your data.

• Partitioning Data
• Compressing Data
• Temporal Tables

Columnstore Indexes

Introduced in Microsoft SQL Server 2012, columnstore indexes are used in large data warehouse solutions by many organizations. This module highlights the benefits of using these indexes on large datasets; the improvements made to columnstore indexes in SQL Server 2016; and the considerations needed to use columnstore indexes effectively in your solutions.

• Introduction to Columnstore Indexes
• Creating Columnstore Indexes
• Working with Columnstore Indexes

Using In-Memory Tables

Microsoft SQL Server 2014 data management software introduced in-memory online transaction processing (OLTP) functionality features to improve the performance of OLTP workloads. SQL Server adds several enhancements, such as the ability to alter a memory-optimized table without recreating it. Memory-optimized tables are primarily stored in memory, which provides the improved performance by reducing hard disk access.

Natively compiled stored procedures further improve performance over traditional interpreted Transact-SQL.

• Memory-Optimized Tables
• Natively Compiled Stored Procedures

Storing and Querying XML Data in SQL Server

XML provides rules for encoding documents in a machine-readable form. It has become a widely adopted standard for representing data structures, rather than sending unstructured documents. Servers that are running Microsoft SQL Server data management software often need to use XML to interchange data with other systems; many SQL Server tools provide an XML-based interface. SQL Server offers extensive handling of XML, both for storage and querying. This module introduces XML, shows how to store XML data within SQL Server, and shows how to query the XML data. The ability to query XML data directly avoids the need to extract data into a relational format before executing Structured Query Language (SQL) queries. To effectively process XML, you need to be able to query XML data in several ways: returning existing relational data as XML, and querying data that is already XML.

• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type
• Using the Transact-SQL FOR XML Statement
• Getting Started with XQuery
• Shredding XML

Storing and Querying Spatial Data in SQL Server

This module describes spatial data and how this data can be implemented within SQL Server.

• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

Storing and Querying BLOBs and Text Documents in SQL Server

Traditionally, databases have been used to store information in the form of simple values—such as integers, dates, and strings—that contrast with more complex data formats, such as documents, spreadsheets, image files, and video files. As the systems that databases support have become more complex, administrators have found it necessary to integrate this more complex file data with the structured data in database tables. For example, in a product database, it can be helpful to associate a product record with the service manual or instructional videos for that product. SQL Server provides several ways to integrate these files—that are often known as Binary Large Objects (BLOBs)—and enable their content to be indexed and included in search results. In this module, you will learn how to design and optimize a database that includes BLOBs.

• Considerations for BLOB Data
• Working with FILESTREAM
• Using Full-Text Search

Performance and Monitoring

This module explains how to name, declare, assign values to, and use variables. It also describes how to store data in an array. This module looks at how to measure and monitor the performance of your SQL Server databases. The first two lessons look at SQL Server Extended Events, a flexible, lightweight event-handling system built into the Microsoft SQL Server Database Engine. These lessons focus on the architectural concepts, troubleshooting strategies and usage scenarios.

• Extended Events
• Working with extended Events
• Live Query Statistics
• Optimize Database File Configuration
• Metrics