Topics

• Database Snapshots
• Common Table Expressions
• JSON in Sql Server
• Split String function in Sql Server
• String Functions
• FOR JSON
• SELECT statement
• Queries with JSON data
• Storing JSON in SQL tables
• OPENJSON
• TRY/CATCH
• In-Memory OLTP (Hekaton)
• Foreign Keys
• cross apply
• CLUSTERED COLUMNSTORE
• Backup and Restore Database
• Natively compiled modules (Hekaton)
• Dynamic SQL
• Pagination
• Query Hints
• Modify JSON text
• Row-level security
• Dynamic data masking
• Managing Azure SQL Database
• Common Language Runtime Integration
• DBCC
• BULK Import
• Query Store

Examples

• Create a database snapshot
• Restore a database snapshot
• Format Query Results as JSON with FOR JSON
• Parse JSON text
• Generate a table of dates using CTE
• Split a String in Sql Server 2016
• String_Split
• Quotename
• Format one table row as a single JSON object using FOR JSON
• Parse JSON text using OPENJSON function
• FOR JSON PATH
• FOR JSON PATH with column aliases
• FOR JSON clause without array wrapper (single object in output)
• INCLUDE_NULL_VALUES
• Wrapping results with ROOT object
• FOR JSON AUTO
• Creating custom nested JSON structure
• Basic SELECT from table
• Filter rows using WHERE clause
• Sort results using ORDER BY
• Group result using GROUP BY
• Filter groups using HAVING clause
• Returning only first N rows
• Pagination using OFFSET FETCH
• SELECT without FROM (no data souce)
• Split string in Sql Server 2008/2012/2014 using XML
• Using values from JSON in query
• Using JSON values in reports
• Filter-out bad JSON text from query results
• Update value in JSON column
• Append new value into JSON array
• JOIN table with inner JSON collection
• Finding rows that contain value in the JSON array
• JSON stored as text column
• Ensure that JSON is properly formatted using ISJSON
• Expose values from JSON text as computed columns
• Adding index on JSON path
• JSON stored in in-memory tables
• Get key:value pairs from JSON text
• Transform JSON array into set of rows
• Transform nested JSON fields into set of rows
• Extracting inner JSON sub-objects
• Working with nested JSON sub-arrays
• Foreign key relationship/constraint
• Maintaining relationship between parent/child rows
• Adding foreign key relationship on existing table
• Add foreign key on existing table
• Getting information about foreign key constraints
• Join table rows with dynamically generated rows from a cell
• Join table rows with JSON array stored in cell
• Filter rows by array values
• Raising errors in try-catch block
• Raising info messages in try catch block
• Re-throwing exception generated by RAISERROR
• Throwing exception in TRY/CATCH blocks
• Table with CLUSTERED COLUMNSTORE index
• Adding clustered columnstore index on existing table
• Natively compiled stored procedure
• Natively compiled scalar function
• Native inline table value function
• DELETE Snapshot
• Rebuild CLUSTERED COLUMNSTORE index
• Execute SQL statement provided as string
• Dynamic SQL executed as different user
• SQL Injection with dynamic SQL
• Dynamic SQL with parameters
• Pagination using ROW_NUMBER with a Common Table Expression
• Pagination with OFFSET FETCH
• Paginaton with inner query
• JOIN Hints
• GROUP BY Hints
• FAST rows hint
• UNION hints
• MAXDOP Option
• Modify value in JSON text on the specified path
• Append a scalar value into a JSON array
• Insert new JSON Object in JSON text
• Insert new JSON array generated with FOR JSON query
• Insert single JSON object generated with FOR JSON clause
• RESTORE Database with REPLACE
• RLS filter predicate
• Altering RLS security policy
• Preventing updated using RLS block predicate
• Mask email address using Dynamic data masking
• Add partial mask on column
• Showing random value from the range using random() mask
• Adding default mask on the column
• Controlling who can see unmasked data
• Find service tier information for Azure SQL Database
• Change service tier of Azure SQL Database
• Replication of Azure SQL Database
• Create Azure SQL Database in Elastic pool
• Enable CLR on database
• Adding .dll that contains Sql CLR modules
• Create CLR Function in SQL Server
• Create CLR User-defined type in SQL Server
• Create CLR procedure in SQL Server
• DBCC maintenance commands
• DBCC validation statements
• DBCC informational statements
• DBCC Trace commands
• DBCC statement
• BULK INSERT with options
• BULK INSERT
• Reading entire content of file using OPENROWSET(BULK)
• Read file using OPENROWSET(BULK) and format file
• Read json file using OPENROWSET(BULK)
• Enable query store on database
• Get execution statistics for SQL queries/plans
• Remove data from query store
• Forcing plan for query