Microsoft SQL Server 2016 Certification (70-764) Advance

Explore the core features of SQL Server 2016, the relational database management system, including primary and foreign keys, data storage, retrieval, manipulation, and the database engine with its management tools.

Explore SQL Server editions, from enterprise with full features to standard with a feature subset, and Express for free local development, noting memory and BI limitations.

Follow a step-by-step SQL Server 2016 basic installation using the setup wizard to configure features. Choose a new standalone install, license, database engine, and Windows authentication or mixed mode.

Install SQL Server Management Studio (SSMS) for SQL 2016 by downloading SSMS from the installation center and completing the setup.

Configure and manage sql server services with configuration manager, restart services, and set client-server protocols like shared memory, dcb ip, and named pipe for reliable connections.

Learn the basics of SQL Server Management Studio, including connecting to a database engine, authenticating, navigating Object Explorer, and using design view and properties windows.

Explore the studio interface with object explorer connections, multiple databases and instances, and Intellisense to write and run queries with debugging and multiple result sets.

Launch the activity monitor to view graphs of processor time, database I/O, and running processes; customize refresh and scale, investigate bottlenecks, and avoid killing processes unless necessary.

Learn to create a SQL Server database using either the create database command or the Management Studio graphical interface, name the database, and review the default file layout.

Explore database configuration options by examining properties, adding files and file groups, enabling change tracking, managing permissions, and using query store insights for SQL Server 2016.

Learn to download and install the wide world importer sample database for sql server, and restore the backup into sql server management studio while choosing the correct edition.

Create a table in Microsoft SQL Server Management Studio by adding columns, choosing data types, and setting nullability, then save and review properties, permissions, change tracking, security, and extended properties.

Explore configuring individual table columns in SQL Server, setting primary keys and identity properties, adjusting data types, defaults, and length, noting some options require T-SQL commands.

Create a table using T-SQL by defining an employee table with columns such as employee_id int and name varchar(50), and assign it to a specific filegroup, then execute the command.

Explore how a relational database management system defines relationships between tables using primary keys and foreign keys, via the database diagram, dragging columns to create and view relationships.

Learn about in-memory, memory-optimized tables in SQL Server and how they boost read and write performance. Create memory-optimized tables with proper primary keys and non-clustered indexes and configure file groups.

Master temporal tables in SQL Server 2016 by enabling system_versioning on an inventory template, which stores history and enables time travel queries with for system_time as of a date.

Create a SQL Server view to combine city and state data, enabling city name and state name in one result set for simpler reports.

Explore advanced view options by creating schema-bound, persisted views with indexes to improve read performance, while understanding automatic updates and when to apply persistence based on data change frequency.

Create and execute a stored procedure that bundles multiple statements into a single unit of work, enforces access control, and performs error-checked queries against a view to retrieve high temperatures.

Define and test parameterized stored procedures in SQL Server, using @startdate and @enddate to filter by temperature and dates, with null checks and input validation.

Explore built-in functions in SQL Server 2016, including system and mathematical categories, and learn to compute max, min, median, and avg values with practical examples.

Create user defined scalar functions in SQL Server 2016 to return an email address from a full name, and use the function in a select statement.

Discover how XML, a human-readable text-based format, enables data exchange between systems by using tags, opening and closing tags, elements, and attributes to store information as key-value pairs.

Learn to convert a sql select from the people table into xml using for xml options, turning fullname and email into attributes or elements.

Learn how to shred XML into relational data by converting XML into columns and rows, using a built-in stored procedure to format it in memory, and query the in-memory results.

Create an axiom xml schema to define a structure, mirroring a database schema, with first name and last name as required string fields and middle name optional.

Demonstrates storing XML in SQL Server using an untyped text column versus a typed column validated against a schema, highlighting validation errors for missing elements.

Learn what JSON is: a text-based, human-readable data format used for information exchange, organized as key-value pairs, with compact syntax and available validation tools.

Convert json to tabular data in sql server 2016 by loading a json variable, using openjson in the from clause, and mapping keys to first and last name columns.

Store json data in a unicode text column, create the table, and apply a constraint verified by the isjson function to validate inserts.