Microsoft® SQL Server® Notes for Professionals book

Content list
About
Chapter 1: Getting started with Microsoft SQL Server
Section 1.1: INSERT / SELECT / UPDATE / DELETE: the basics of Data Manipulation Language
Section 1.2: SELECT all rows and columns from a table
Section 1.3: UPDATE Specific Row
Section 1.4: DELETE All Rows
Section 1.5: Comments in code
Section 1.6: PRINT
Section 1.7: Select rows that match a condition
Section 1.8: UPDATE All Rows
Section 1.9: TRUNCATE TABLE
Section 1.10: Retrieve Basic Server Information
Section 1.11: Create new table and insert records from old table
Section 1.12: Using Transactions to change data safely
Section 1.13: Getting Table Row Count
Chapter 2: Data Types
Section 2.1: Exact Numerics
Section 2.2: Approximate Numerics
Section 2.3: Date and Time
Section 2.4: Character Strings
Section 2.5: Unicode Character Strings
Section 2.6: Binary Strings
Section 2.7: Other Data Types
Chapter 3: Converting data types
Section 3.1: TRY PARSE
Section 3.2: TRY CONVERT
Section 3.3: TRY CAST
Section 3.4: Cast
Section 3.5: Convert
Chapter 4: User Defined Table Types
Section 4.1: creating a UDT with a single int column that is also a primary key
Section 4.2: Creating a UDT with multiple columns
Section 4.3: Creating a UDT with a unique constraint:
Section 4.4: Creating a UDT with a primary key and a column with a default value:
Chapter 5: SELECT statement
Section 5.1: Basic SELECT from table
Section 5.2: Filter rows using WHERE clause
Section 5.3: Sort results using ORDER BY
Section 5.4: Group result using GROUP BY
Section 5.5: Filter groups using HAVING clause
Section 5.6: Returning only first N rows
Section 5.7: Pagination using OFFSET FETCH
Section 5.8: SELECT without FROM (no data souce)
Chapter 6: Alias Names in SQL Server
Section 6.1: Giving alias after Derived table name
Section 6.2: Using AS
Section 6.3: Using =
Section 6.4: Without using AS
Chapter 7: NULLs
Section 7.1: COALESCE ()
Section 7.2: ANSI NULLS
Section 7.3: ISNULL()
Section 7.4: Is null / Is not null
Section 7.5: NULL comparison
Section 7.6: NULL with NOT IN SubQuery
Chapter 8: Variables
Section 8.1: Declare a Table Variable
Section 8.2: Updating variables using SELECT
Section 8.3: Declare multiple variables at once, with initial values
Section 8.4: Updating a variable using SET
Section 8.5: Updating variables by selecting from a table
Section 8.6: Compound assignment operators
Chapter 9: Dates
Section 9.1: Date & Time Formatting using CONVERT
Section 9.2: Date & Time Formatting using FORMAT
Section 9.3: DATEADD for adding and subtracting time periods
Section 9.4: Create function to calculate a person's age on a specific date
Section 9.5: Get the current DateTime
Section 9.6: Getting the last day of a month
Section 9.7: CROSS PLATFORM DATE OBJECT
Section 9.8: Return just Date from a DateTime
Section 9.9: DATEDIFF for calculating time period dierences
Section 9.10: DATEPART & DATENAME
Section 9.11: Date parts reference
Section 9.12: Date Format Extended
Chapter 10: Generating a range of dates
Section 10.1: Generating Date Range With Recursive CTE
Section 10.2: Generating a Date Range With a Tally Table
Chapter 11: Database Snapshots
Section 11.1: Create a database snapshot
Section 11.2: Restore a database snapshot
Section 11.3: DELETE Snapshot
Chapter 12: COALESCE
Section 12.1: Using COALESCE to Build Comma-Delimited String
Section 12.2: Getting the first not null from a list of column values
Section 12.3: Coalesce basic Example
Chapter 13: IF...ELSE
Section 13.1: Single IF statement
Section 13.2: Multiple IF Statements
Section 13.3: Single IF..ELSE statement
Section 13.4: Multiple IF... ELSE with final ELSE Statements
Section 13.5: Multiple IF...ELSE Statements
Chapter 14: CASE Statement
Section 14.1: Simple CASE statement
Section 14.2: Searched CASE statement
Chapter 15: INSERT INTO
Section 15.1: INSERT multiple rows of data
Section 15.2: Use OUTPUT to get the new Id
Section 15.3: INSERT from SELECT Query Results
Section 15.4: INSERT a single row of data
Section 15.5: INSERT on specific columns
Section 15.6: INSERT Hello World INTO table
Chapter 16: MERGE
Section 16.1: MERGE to Insert / Update / Delete
Section 16.2: Merge Using CTE Source
Section 16.3: Merge Example - Synchronize Source And Target Table
Section 16.4: MERGE using Derived Source Table
Section 16.5: Merge using EXCEPT
Chapter 17: CREATE VIEW
Section 17.1: CREATE Indexed VIEW
Section 17.2: CREATE VIEW
Section 17.3: CREATE VIEW With Encryption
Section 17.4: CREATE VIEW With INNER JOIN
Section 17.5: Grouped VIEWs
Section 17.6: UNION-ed VIEWs
Chapter 18: Views
Section 18.1: Create a view with schema binding
Section 18.2: Create a view
Section 18.3: Create or replace view
Chapter 19: UNION
Section 19.1: Union and union all
Chapter 20: TRY/CATCH
Section 20.1: Transaction in a TRY/CATCH
Section 20.2: Raising errors in try-catch block
Section 20.3: Raising info messages in try catch block
Section 20.4: Re-throwing exception generated by RAISERROR
Section 20.5: Throwing exception in TRY/CATCH blocks
Chapter 21: WHILE loop
Section 21.1: Using While loop
Section 21.2: While loop with min aggregate function usage
Chapter 22: OVER Clause
Section 22.1: Cumulative Sum
Section 22.2: Using Aggregation functions with OVER
Section 22.3: Dividing Data into equally-partitioned buckets using NTILE
Section 22.4: Using Aggregation funtions to find the most recent records
Chapter 23: GROUP BY
Section 23.1: Simple Grouping
Section 23.2: GROUP BY multiple columns
Section 23.3: GROUP BY with ROLLUP and CUBE
Section 23.4: Group by with multiple tables, multiple columns
Section 23.5: HAVING
Chapter 24: ORDER BY
Section 24.1: Simple ORDER BY clause
Section 24.2: ORDER BY multiple fields
Section 24.3: Custom Ordering
Section 24.4: ORDER BY with complex logic
Chapter 25: The STUFF Function
Section 25.1: Using FOR XML to Concatenate Values from Multiple Rows
Section 25.2: Basic Character Replacement with STUFF()
Section 25.3: Basic Example of STUFF() function
Section 25.4: stu for comma separated in sql server
Section 25.5: Obtain column names separated with comma (not a list)
Chapter 26: JSON in SQL Server
Section 26.1: Index on JSON properties by using computed columns
Section 26.2: Join parent and child JSON entities using CROSS APPLY OPENJSON
Section 26.3: Format Query Results as JSON with FOR JSON
Section 26.4: Parse JSON text
Section 26.5: Format one table row as a single JSON object using FOR JSON
Section 26.6: Parse JSON text using OPENJSON function
Chapter 27: OPENJSON
Section 27.1: Transform JSON array into set of rows
Section 27.2: Get key:value pairs from JSON text
Section 27.3: Transform nested JSON fields into set of rows
Section 27.4: Extracting inner JSON sub-objects
Section 27.5: Working with nested JSON sub-arrays
Chapter 28: FOR JSON
Section 28.1: FOR JSON PATH
Section 28.2: FOR JSON PATH with column aliases
Section 28.3: FOR JSON clause without array wrapper (single object in output)
Section 28.4: INCLUDE_NULL_VALUES
Section 28.5: Wrapping results with ROOT object
Section 28.6: FOR JSON AUTO
Section 28.7: Creating custom nested JSON structure
Chapter 29: Queries with JSON data
Section 29.1: Using values from JSON in query
Section 29.2: Using JSON values in reports
Section 29.3: Filter-out bad JSON text from query results
Section 29.4: Update value in JSON column
Section 29.5: Append new value into JSON array
Section 29.6: JOIN table with inner JSON collection
Section 29.7: Finding rows that contain value in the JSON array
Chapter 30: Storing JSON in SQL tables
Section 30.1: JSON stored as text column
Section 30.2: Ensure that JSON is properly formatted using ISJSON
Section 30.3: Expose values from JSON text as computed columns
Section 30.4: Adding index on JSON path
Section 30.5: JSON stored in in-memory tables
Chapter 31: Modify JSON text
Section 31.1: Modify value in JSON text on the specified path
Section 31.2: Append a scalar value into a JSON array
Section 31.3: Insert new JSON Object in JSON text
Section 31.4: Insert new JSON array generated with FOR JSON query
Section 31.5: Insert single JSON object generated with FOR JSON clause
Chapter 32: FOR XML PATH
Section 32.1: Using FOR XML PATH to concatenate values
Section 32.2: Specifying namespaces
Section 32.3: Specifying structure using XPath expressions
Section 32.4: Hello World XML
Chapter 33: Join
Section 33.1: Inner Join
Section 33.2: Outer Join
Section 33.3: Using Join in an Update
Section 33.4: Join on a Subquery
Section 33.5: Cross Join
Section 33.6: Self Join
Section 33.7: Accidentally turning an outer join into an inner join
Section 33.8: Delete using Join
Chapter 34: cross apply
Section 34.1: Join table rows with dynamically generated rows from a cell
Section 34.2: Join table rows with JSON array stored in cell
Section 34.3: Filter rows by array values
Chapter 35: Computed Columns
Section 35.1: A column is computed from an expression
Section 35.2: Simple example we normally use in log tables
Chapter 36: Common Table Expressions
Section 36.1: Generate a table of dates using CTE
Section 36.2: Employee Hierarchy
Section 36.3: Recursive CTE
Section 36.4: Delete duplicate rows using CTE
Section 36.5: CTE with multiple AS statements
Section 36.6: Find nth highest salary using CTE
Chapter 37: Move and copy data around tables
Section 37.1: Copy data from one table to another
Section 37.2: Copy data into a table, creating that table on the fly
Section 37.3: Move data into a table (assuming unique keys method)
Chapter 38: Limit Result Set
Section 38.1: Limiting With PERCENT
Section 38.2: Limiting with FETCH
Section 38.3: Limiting With TOP
Chapter 39: Retrieve Information about your Instance
Section 39.1: General Information about Databases, Tables, Stored procedures and how to search them
Section 39.2: Get information on current sessions and query executions
Section 39.3: Information about SQL Server version
Section 39.4: Retrieve Edition and Version of Instance
Section 39.5: Retrieve Instance Uptime in Days
Section 39.6: Retrieve Local and Remote Servers
Chapter 40: With Ties Option
Section 40.1: Test Data
Chapter 41: String Functions
Section 41.1: Quotename
Section 41.2: Replace
Section 41.3: Substring
Section 41.4: String_Split
Section 41.5: Left
Section 41.6: Right
Section 41.7: Soundex
Section 41.8: Format
Section 41.9: String_escape
Section 41.10: ASCII
Section 41.11: Char
Section 41.12: Concat
Section 41.13: LTrim
Section 41.14: RTrim
Section 41.15: PatIndex
Section 41.16: Space
Section 41.17: Dierence
Section 41.18: Len
Section 41.19: Lower
Section 41.20: Upper
Section 41.21: Unicode
Section 41.22: NChar
Section 41.23: Str
Section 41.24: Reverse
Section 41.25: Replicate
Section 41.26: CharIndex
Chapter 42: Logical Functions
Section 42.1: CHOOSE
Section 42.2: IIF
Chapter 43: Aggregate Functions
Section 43.1: SUM()
Section 43.2: AVG()
Section 43.3: MAX()
Section 43.4: MIN()
Section 43.5: COUNT()
Section 43.6: COUNT(Column_Name) with GROUP BY Column_Name
Chapter 44: String Aggregate functions in SQL Server
Section 44.1: Using STUFF for string aggregation
Section 44.2: String_Agg for String Aggregation
Chapter 45: Ranking Functions
Section 45.1: DENSE_RANK ()
Section 45.2: RANK()
Chapter 46: Window functions
Section 46.1: Centered Moving Average
Section 46.2: Find the single most recent item in a list of timestamped events
Section 46.3: Moving Average of last 30 Items
Chapter 47: PIVOT / UNPIVOT
Section 47.1: Dynamic PIVOT
Section 47.2: Simple PIVOT & UNPIVOT (T-SQL)
Section 47.3: Simple Pivot - Static Columns
Chapter 48: Dynamic SQL Pivot
Section 48.1: Basic Dynamic SQL Pivot
Chapter 49: Partitioning
Section 49.1: Retrieve Partition Boundary Values
Section 49.2: Switching Partitions
Section 49.3: Retrieve partition table,column, scheme, function, total and min-max boundry values using single query
Chapter 50: Stored Procedures
Section 50.1: Creating and executing a basic stored procedure
Section 50.2: Stored Procedure with If...Else and Insert Into operation
Section 50.3: Dynamic SQL in stored procedure
Section 50.4: STORED PROCEDURE with OUT parameters
Section 50.5: Simple Looping
Section 50.6: Simple Looping
Chapter 51: Retrieve information about the database
Section 51.1: Retrieve a List of all Stored Procedures
Section 51.2: Get the list of all databases on a server
Section 51.3: Count the Number of Tables in a Database
Section 51.4: Database Files
Section 51.5: See if Enterprise-specific features are being used
Section 51.6: Determine a Windows Login's Permission Path
Section 51.7: Search and Return All Tables and Columns Containing a Specified Column Value
Section 51.8: Get all schemas, tables, columns and indexes
Section 51.9: Return a list of SQL Agent jobs, with schedule information
Section 51.10: Retrieve Tables Containing Known Column
Section 51.11: Show Size of All Tables in Current Database
Section 51.12: Retrieve Database Options
Section 51.13: Find every mention of a field in the database
Section 51.14: Retrieve information on backup and restore operations
Chapter 52: Split String function in SQL Server
Section 52.1: Split string in Sql Server 2008/2012/2014 using XML
Section 52.2: Split a String in Sql Server 2016
Section 52.3: T-SQL Table variable and XML
Chapter 53: Insert
Section 53.1: Add a row to a table named Invoices
Chapter 54: Primary Keys
Section 54.1: Create table w/ identity column as primary key
Section 54.2: Create table w/ GUID primary key
Section 54.3: Create table w/ natural key
Section 54.4: Create table w/ composite key
Section 54.5: Add primary key to existing table
Section 54.6: Delete primary key
Chapter 55: Foreign Keys
Section 55.1: Foreign key relationship/constraint
Section 55.2: Maintaining relationship between parent/child rows
Section 55.3: Adding foreign key relationship on existing table
Section 55.4: Add foreign key on existing table
Section 55.5: Getting information about foreign key constraints
Chapter 56: Last Inserted Identity
Section 56.1: @@IDENTITY and MAX(ID)
Section 56.2: SCOPE_IDENTITY()
Section 56.3: @@IDENTITY
Section 56.4: IDENT_CURRENT('tablename')
Chapter 57: SCOPE_IDENTITY()
Section 57.1: Introduction with Simple Example
Chapter 58: Sequences
Section 58.1: Create Sequence
Section 58.2: Use Sequence in Table
Section 58.3: Insert Into Table with Sequence
Section 58.4: Delete From & Insert New
Chapter 59: Index
Section 59.1: Create Clustered index
Section 59.2: Drop index
Section 59.3: Create Non-Clustered index
Section 59.4: Show index info
Section 59.5: Returns size and fragmentation indexes
Section 59.6: Reorganize and rebuild index
Section 59.7: Rebuild or reorganize all indexes on a table
Section 59.8: Rebuild all index database
Section 59.9: Index on view
Section 59.10: Index investigations
Chapter 60: Full-Text Indexing
Section 60.1: A. Creating a unique index, a full-text catalog, and a full-text index
Section 60.2: Creating a full-text index on several table columns
Section 60.3: Creating a full-text index with a search property list without populating it
Section 60.4: Full-Text Search
Chapter 61: Trigger
Section 61.1: DML Triggers
Section 61.2: Types and classifications of Trigger
Chapter 62: Cursors
Section 62.1: Basic Forward Only Cursor
Section 62.2: Rudimentary cursor syntax
Chapter 63: Transaction isolation levels
Section 63.1: Read Committed
Section 63.2: What are "dirty reads"?
Section 63.3: Read Uncommitted
Section 63.4: Repeatable Read
Section 63.5: Snapshot
Section 63.6: Serializable
Chapter 64: Advanced options
Section 64.1: Enable and show advanced options
Section 64.2: Enable backup compression default
Section 64.3: Enable cmd permission
Section 64.4: Set default fill factor percent
Section 64.5: Set system recovery interval
Section 64.6: Set max server memory size
Section 64.7: Set number of checkpoint tasks
Chapter 65: Migration
Section 65.1: How to generate migration scripts
Chapter 66: Table Valued Parameters
Section 66.1: Using a table valued parameter to insert multiple rows to a table
Chapter 67: DBMAIL
Section 67.1: Send simple email
Section 67.2: Send results of a query
Section 67.3: Send HTML email
Chapter 68: In-Memory OLTP (Hekaton)
Section 68.1: Declare Memory-Optimized Table Variables
Section 68.2: Create Memory Optimized Table
Section 68.3: Show created .dll files and tables for Memory Optimized Tables
Section 68.4: Create Memory Optimized System-Versioned Temporal Table
Section 68.5: Memory-Optimized Table Types and Temp tables
Chapter 69: Temporal Tables
Section 69.1: CREATE Temporal Tables
Section 69.2: FOR SYSTEM_TIME ALL
Section 69.3: Creating a Memory-Optimized System-Versioned Temporal Table and cleaning up the SQL Server history table
Section 69.4: FOR SYSTEM_TIME BETWEEN AND
Section 69.5: FOR SYSTEM_TIME FROM TO
Section 69.6: FOR SYSTEM_TIME CONTAINED IN ( , )
Section 69.7: How do I query temporal data?
Section 69.8: Return actual value specified point in time(FOR SYSTEM_TIME AS OF )
Chapter 70: Use of TEMP Table
Section 70.1: Dropping temp tables
Section 70.2: Local Temp Table
Section 70.3: Global Temp Table
Chapter 71: Scheduled Task or Job
Section 71.1: Create a scheduled Job
Chapter 72: Isolation levels and locking
Section 72.1: Examples of setting the isolation level
Chapter 73: Sorting/ordering rows
Section 73.1: Basics
Section 73.2: Order by Case
Chapter 74: Privileges or Permissions
Section 74.1: Simple rules
Chapter 75: SQLCMD
Section 75.1: SQLCMD.exe called from a batch file or command line
Chapter 76: Resource Governor
Section 76.1: Reading the Statistics
Chapter 77: File Group
Section 77.1: Create filegroup in database
Chapter 78: Basic DDL Operations in MS SQL Server
Section 78.1: Getting started
Chapter 79: Subqueries
Section 79.1: Subqueries
Chapter 80: Pagination
Section 80.1: Pagination with OFFSET FETCH
Section 80.2: Paginaton with inner query
Section 80.3: Paging in Various Versions of SQL Server
Section 80.4: SQL Server 2012/2014 using ORDER BY OFFSET and FETCH NEXT
Section 80.5: Pagination using ROW_NUMBER with a Common Table Expression
Chapter 81: CLUSTERED COLUMNSTORE
Section 81.1: Adding clustered columnstore index on existing table
Section 81.2: Rebuild CLUSTERED COLUMNSTORE index
Section 81.3: Table with CLUSTERED COLUMNSTORE index
Chapter 82: Parsename
Section 82.1: PARSENAME
Chapter 83: Installing SQL Server on Windows
Section 83.1: Introduction
Chapter 84: Analyzing a Query
Section 84.1: Scan vs Seek
Chapter 85: Query Hints
Section 85.1: JOIN Hints
Section 85.2: GROUP BY Hints
Section 85.3: FAST rows hint
Section 85.4: UNION hints
Section 85.5: MAXDOP Option
Section 85.6: INDEX Hints
Chapter 86: Query Store
Section 86.1: Enable query store on database
Section 86.2: Get execution statistics for SQL queries/plans
Section 86.3: Remove data from query store
Section 86.4: Forcing plan for query
Chapter 87: Querying results by page
Section 87.1: Row_Number()
Chapter 88: Schemas
Section 88.1: Purpose
Section 88.2: Creating a Schema
Section 88.3: Alter Schema
Section 88.4: Dropping Schemas
Chapter 89: Backup and Restore Database
Section 89.1: Basic Backup to disk with no options
Section 89.2: Basic Restore from disk with no options
Section 89.3: RESTORE Database with REPLACE
Chapter 90: Transaction handling
Section 90.1: basic transaction skeleton with error handling
Chapter 91: Natively compiled modules (Hekaton)
Section 91.1: Natively compiled stored procedure
Section 91.2: Natively compiled scalar function
Section 91.3: Native inline table value function
Chapter 92: Spatial Data
Section 92.1: POINT
Chapter 93: Dynamic SQL
Section 93.1: Execute SQL statement provided as string
Section 93.2: Dynamic SQL executed as dierent user
Section 93.3: SQL Injection with dynamic SQL
Section 93.4: Dynamic SQL with parameters
Chapter 94: Dynamic data masking
Section 94.1: Adding default mask on the column
Section 94.2: Mask email address using Dynamic data masking
Section 94.3: Add partial mask on column
Section 94.4: Showing random value from the range using random() mask
Section 94.5: Controlling who can see unmasked data
Chapter 95: Export data in txt file by using SQLCMD
Section 95.1: By using SQLCMD on Command Prompt
Chapter 96: Common Language Runtime Integration
Section 96.1: Enable CLR on database
Section 96.2: Adding .dll that contains Sql CLR modules
Section 96.3: Create CLR Function in SQL Server
Section 96.4: Create CLR User-defined type in SQL Server
Section 96.5: Create CLR procedure in SQL Server
Chapter 97: Delimiting special characters and reserved words
Section 97.1: Basic Method
Chapter 98: DBCC
Section 98.1: DBCC statement
Section 98.2: DBCC maintenance commands
Section 98.3: DBCC validation statements
Section 98.4: DBCC informational statements
Section 98.5: DBCC Trace commands
Chapter 99: BULK Import
Section 99.1: BULK INSERT
Section 99.2: BULK INSERT with options
Section 99.3: Reading entire content of file using OPENROWSET(BULK)
Section 99.4: Read file using OPENROWSET(BULK) and format file
Section 99.5: Read json file using OPENROWSET(BULK)
Chapter 100: Service broker
Section 100.1: Basics
Section 100.2: Enable service broker on database
Section 100.3: Create basic service broker construction on database (single database communication)
Section 100.4: How to send basic communication through service broker
Section 100.5: How to receive conversation from TargetQueue automatically
Chapter 101: Permissions and Security
Section 101.1: Assign Object Permissions to a user
Chapter 102: Database permissions
Section 102.1: Changing permissions
Section 102.2: CREATE USER
Section 102.3: CREATE ROLE
Section 102.4: Changing role membership
Chapter 103: Row-level security
Section 103.1: RLS filter predicate
Section 103.2: Altering RLS security policy
Section 103.3: Preventing updated using RLS block predicate
Chapter 104: Encryption
Section 104.1: Encryption by certificate
Section 104.2: Encryption of database
Section 104.3: Encryption by symmetric key
Section 104.4: Encryption by passphrase
Chapter 105: PHANTOM read
Section 105.1: Isolation level READ UNCOMMITTED
Chapter 106: Filestream
Section 106.1: Example
Chapter 107: bcp (bulk copy program) Utility
Section 107.1: Example to Import Data without a Format File(using Native Format )
Chapter 108: SQL Server Evolution through dierent versions (2000 - 2016)
Section 108.1: SQL Server Version 2000 - 2016
Chapter 109: SQL Server Management Studio (SSMS)
Section 109.1: Refreshing the IntelliSense cache
Chapter 110: Managing Azure SQL Database
Section 110.1: Find service tier information for Azure SQL Database
Section 110.2: Change service tier of Azure SQL Database
Section 110.3: Replication of Azure SQL Database
Section 110.4: Create Azure SQL Database in Elastic pool
Chapter 111: System database - TempDb
Section 111.1: Identify TempDb usage
Section 111.2: TempDB database details
Appendix A: Microsoft SQL Server Management Studio Shortcut Keys
Section A.1: Shortcut Examples
Section A.2: Menu Activation Keyboard Shortcuts
Section A.3: Custom keyboard shortcuts
Credits
You may also like