MicrosoftSQLServerNotesForProfessionals.pdf

(2372 KB) Pobierz
Microsoft
SQL Server
Microsoft SQL Server
Notes for Professionals
®
®
Notes for Professionals
of professional hints and tricks
200+ pages
GoalKicker.com
Free Programming Books
Disclaimer
This is an unocial free book created for educational purposes and is
not aliated with ocial Microsoft
®
SQL Server
®
group(s) or company(s).
All trademarks and registered trademarks are
the property of their respective owners
Contents
About
................................................................................................................................................................................... 1
Chapter 1: Getting started with Microsoft SQL Server
.............................................................................. 2
Section 1.1: INSERT / SELECT / UPDATE / DELETE: the basics of Data Manipulation Language
......................... 2
Section 1.2: SELECT all rows and columns from a table
............................................................................................ 5
Section 1.3: UPDATE Specific Row
................................................................................................................................ 6
Section 1.4: DELETE All Rows
........................................................................................................................................ 6
Section 1.5: Comments in code
.................................................................................................................................... 7
Section 1.6: PRINT
.......................................................................................................................................................... 8
Section 1.7: Select rows that match a condition
......................................................................................................... 8
Section 1.8: UPDATE All Rows
....................................................................................................................................... 8
Section 1.9: TRUNCATE TABLE
..................................................................................................................................... 8
Section 1.10: Retrieve Basic Server Information
......................................................................................................... 9
Section 1.11: Create new table and insert records from old table
............................................................................. 9
Section 1.12: Using Transactions to change data safely
......................................................................................... 10
Section 1.13: Getting Table Row Count
...................................................................................................................... 11
Chapter 2: Data Types
............................................................................................................................................. 12
Section 2.1: Exact Numerics
........................................................................................................................................ 12
Section 2.2: Approximate Numerics
.......................................................................................................................... 13
Section 2.3: Date and Time
........................................................................................................................................ 13
Section 2.4: Character Strings
.................................................................................................................................... 14
Section 2.5: Unicode Character Strings
.................................................................................................................... 14
Section 2.6: Binary Strings
.......................................................................................................................................... 14
Section 2.7: Other Data Types
................................................................................................................................... 14
Chapter 3: Converting data types
..................................................................................................................... 15
Section 3.1: TRY PARSE
............................................................................................................................................... 15
Section 3.2: TRY CONVERT
......................................................................................................................................... 15
Section 3.3: TRY CAST
................................................................................................................................................. 16
Section 3.4: Cast
.......................................................................................................................................................... 16
Section 3.5: Convert
.................................................................................................................................................... 16
Chapter 4: User Defined Table Types
............................................................................................................. 18
Section 4.1: creating a UDT with a single int column that is also a primary key
.................................................. 18
Section 4.2: Creating a UDT with multiple columns
................................................................................................. 18
Section 4.3: Creating a UDT with a unique constraint:
............................................................................................ 18
Section 4.4: Creating a UDT with a primary key and a column with a default value:
......................................... 18
Chapter 5: SELECT statement
.............................................................................................................................. 19
Section 5.1: Basic SELECT from table
........................................................................................................................ 19
Section 5.2: Filter rows using WHERE clause
........................................................................................................... 19
Section 5.3: Sort results using ORDER BY
................................................................................................................. 19
Section 5.4: Group result using GROUP BY
............................................................................................................... 19
Section 5.5: Filter groups using HAVING clause
....................................................................................................... 20
Section 5.6: Returning only first N rows
.................................................................................................................... 20
Section 5.7: Pagination using OFFSET FETCH
.......................................................................................................... 20
Section 5.8: SELECT without FROM (no data souce)
............................................................................................... 20
Chapter 6: Alias Names in SQL Server
............................................................................................................. 21
Section 6.1: Giving alias after Derived table name
.................................................................................................. 21
Section 6.2: Using AS
................................................................................................................................................... 21
Section 6.3: Using =
..................................................................................................................................................... 21
Section 6.4: Without using AS
..................................................................................................................................... 21
Chapter 7: NULLs
........................................................................................................................................................ 22
Section 7.1: COALESCE ()
............................................................................................................................................ 22
Section 7.2: ANSI NULLS
............................................................................................................................................. 22
Section 7.3: ISNULL()
................................................................................................................................................... 23
Section 7.4: Is null / Is not null
.................................................................................................................................... 23
Section 7.5: NULL comparison
................................................................................................................................... 23
Section 7.6: NULL with NOT IN SubQuery
................................................................................................................. 24
Chapter 8: Variables
................................................................................................................................................. 26
Section 8.1: Declare a Table Variable
........................................................................................................................ 26
Section 8.2: Updating variables using SELECT
......................................................................................................... 26
Section 8.3: Declare multiple variables at once, with initial values
........................................................................ 27
Section 8.4: Updating a variable using SET
.............................................................................................................. 27
Section 8.5: Updating variables by selecting from a table
..................................................................................... 28
Section 8.6: Compound assignment operators
........................................................................................................ 28
Chapter 9: Dates
......................................................................................................................................................... 29
Section 9.1: Date & Time Formatting using CONVERT
............................................................................................ 29
Section 9.2: Date & Time Formatting using FORMAT
............................................................................................. 30
Section 9.3: DATEADD for adding and subtracting time periods
........................................................................... 31
Section 9.4: Create function to calculate a person's age on a specific date
........................................................ 32
Section 9.5: Get the current DateTime
...................................................................................................................... 32
Section 9.6: Getting the last day of a month
............................................................................................................ 33
Section 9.7: CROSS PLATFORM DATE OBJECT
....................................................................................................... 33
Section 9.8: Return just Date from a DateTime
....................................................................................................... 33
Section 9.9: DATEDIFF for calculating time period dierences
............................................................................. 33
Section 9.10: DATEPART & DATENAME
..................................................................................................................... 34
Section 9.11: Date parts reference
............................................................................................................................. 35
Section 9.12: Date Format Extended
......................................................................................................................... 35
Chapter 10: Generating a range of dates
...................................................................................................... 39
Section 10.1: Generating Date Range With Recursive CTE
...................................................................................... 39
Section 10.2: Generating a Date Range With a Tally Table
.................................................................................... 39
Chapter 11: Database Snapshots
........................................................................................................................ 40
Section 11.1: Create a database snapshot
................................................................................................................. 40
Section 11.2: Restore a database snapshot
.............................................................................................................. 40
Section 11.3: DELETE Snapshot
................................................................................................................................... 40
Chapter 12: COALESCE
.............................................................................................................................................. 41
Section 12.1: Using COALESCE to Build Comma-Delimited String
.......................................................................... 41
Section 12.2: Getting the first not null from a list of column values
....................................................................... 41
Section 12.3: Coalesce basic Example
....................................................................................................................... 41
Chapter 13: IF...ELSE
................................................................................................................................................... 43
Section 13.1: Single IF statement
................................................................................................................................ 43
Section 13.2: Multiple IF Statements
.......................................................................................................................... 43
Section 13.3: Single IF..ELSE statement
...................................................................................................................... 43
Section 13.4: Multiple IF... ELSE with final ELSE Statements
..................................................................................... 44
Section 13.5: Multiple IF...ELSE Statements
................................................................................................................ 44
Chapter 14: CASE Statement
................................................................................................................................ 45
Section 14.1: Simple CASE statement
......................................................................................................................... 45
Section 14.2: Searched CASE statement
................................................................................................................... 45
Chapter 15: INSERT INTO
........................................................................................................................................ 46
Section 15.1: INSERT multiple rows of data
............................................................................................................... 46
Section 15.2: Use OUTPUT to get the new Id
............................................................................................................ 46
Section 15.3: INSERT from SELECT Query Results
................................................................................................... 47
Section 15.4: INSERT a single row of data
................................................................................................................ 47
Section 15.5: INSERT on specific columns
................................................................................................................. 47
Section 15.6: INSERT Hello World INTO table
........................................................................................................... 47
Chapter 16: MERGE
..................................................................................................................................................... 48
Section 16.1: MERGE to Insert / Update / Delete
...................................................................................................... 48
Section 16.2: Merge Using CTE Source
...................................................................................................................... 49
Section 16.3: Merge Example - Synchronize Source And Target Table
................................................................. 49
Section 16.4: MERGE using Derived Source Table
.................................................................................................... 50
Section 16.5: Merge using EXCEPT
............................................................................................................................. 50
Chapter 17: CREATE VIEW
....................................................................................................................................... 52
Section 17.1: CREATE Indexed VIEW
........................................................................................................................... 52
Section 17.2: CREATE VIEW
......................................................................................................................................... 52
Section 17.3: CREATE VIEW With Encryption
............................................................................................................ 53
Section 17.4: CREATE VIEW With INNER JOIN
.......................................................................................................... 53
Section 17.5: Grouped VIEWs
...................................................................................................................................... 53
Section 17.6: UNION-ed VIEWs
................................................................................................................................... 54
Chapter 18: Views
........................................................................................................................................................ 55
Section 18.1: Create a view with schema binding
..................................................................................................... 55
Section 18.2: Create a view
......................................................................................................................................... 55
Section 18.3: Create or replace view
.......................................................................................................................... 55
Chapter 19: UNION
...................................................................................................................................................... 56
Section 19.1: Union and union all
................................................................................................................................ 56
Chapter 20: TRY/CATCH
......................................................................................................................................... 59
Section 20.1: Transaction in a TRY/CATCH
.............................................................................................................. 59
Section 20.2: Raising errors in try-catch block
........................................................................................................ 59
Section 20.3: Raising info messages in try catch block
.......................................................................................... 60
Section 20.4: Re-throwing exception generated by RAISERROR
........................................................................... 60
Section 20.5: Throwing exception in TRY/CATCH blocks
....................................................................................... 60
Chapter 21: WHILE loop
............................................................................................................................................ 62
Section 21.1: Using While loop
.................................................................................................................................... 62
Section 21.2: While loop with min aggregate function usage
................................................................................. 62
Chapter 22: OVER Clause
........................................................................................................................................ 63
Section 22.1: Cumulative Sum
.................................................................................................................................... 63
Section 22.2: Using Aggregation functions with OVER
........................................................................................... 63
Section 22.3: Dividing Data into equally-partitioned buckets using NTILE
........................................................... 64
Section 22.4: Using Aggregation funtions to find the most recent records
.......................................................... 64
Chapter 23: GROUP BY
............................................................................................................................................. 66
Section 23.1: Simple Grouping
.................................................................................................................................... 66
Section 23.2: GROUP BY multiple columns
............................................................................................................... 66
Section 23.3: GROUP BY with ROLLUP and CUBE
.................................................................................................... 67
Section 23.4: Group by with multiple tables, multiple columns
.............................................................................. 68
Section 23.5: HAVING
.................................................................................................................................................. 69
Chapter 24: ORDER BY
............................................................................................................................................ 71
Section 24.1: Simple ORDER BY clause
...................................................................................................................... 71
Section 24.2: ORDER BY multiple fields
.................................................................................................................... 71
Section 24.3: Custom Ordering
.................................................................................................................................. 71
Section 24.4: ORDER BY with complex logic
............................................................................................................ 72
Chapter 25: The STUFF Function
........................................................................................................................ 73
Section 25.1: Using FOR XML to Concatenate Values from Multiple Rows
........................................................... 73
Section 25.2: Basic Character Replacement with STUFF()
..................................................................................... 73
Section 25.3: Basic Example of STUFF() function
.................................................................................................... 74
Section 25.4: stu for comma separated in sql server
........................................................................................... 74
Section 25.5: Obtain column names separated with comma (not a list)
.............................................................. 74
Chapter 26: JSON in SQL Server
......................................................................................................................... 75
Section 26.1: Index on JSON properties by using computed columns
.................................................................. 75
Section 26.2: Join parent and child JSON entities using CROSS APPLY OPENJSON
........................................... 76
Section 26.3: Format Query Results as JSON with FOR JSON
.............................................................................. 77
Section 26.4: Parse JSON text
.................................................................................................................................... 77
Section 26.5: Format one table row as a single JSON object using FOR JSON
.................................................. 77
Section 26.6: Parse JSON text using OPENJSON function
..................................................................................... 78
Chapter 27: OPENJSON
........................................................................................................................................... 79
Section 27.1: Transform JSON array into set of rows
............................................................................................. 79
Section 27.2: Get key:value pairs from JSON text
................................................................................................... 79
Section 27.3: Transform nested JSON fields into set of rows
................................................................................ 79
Section 27.4: Extracting inner JSON sub-objects
..................................................................................................... 80
Section 27.5: Working with nested JSON sub-arrays
.............................................................................................. 80
Chapter 28: FOR JSON
............................................................................................................................................. 82
Section 28.1: FOR JSON PATH
................................................................................................................................... 82
Section 28.2: FOR JSON PATH with column aliases
................................................................................................ 82
Section 28.3: FOR JSON clause without array wrapper (single object in output)
............................................... 82
Section 28.4: INCLUDE_NULL_VALUES
.................................................................................................................... 83
Section 28.5: Wrapping results with ROOT object
................................................................................................... 83
Section 28.6: FOR JSON AUTO
.................................................................................................................................. 83
Section 28.7: Creating custom nested JSON structure
........................................................................................... 84
Chapter 29: Queries with JSON data
................................................................................................................ 85
Section 29.1: Using values from JSON in query
....................................................................................................... 85
Section 29.2: Using JSON values in reports
............................................................................................................. 85
Section 29.3: Filter-out bad JSON text from query results
..................................................................................... 85
Section 29.4: Update value in JSON column
............................................................................................................ 85
Section 29.5: Append new value into JSON array
................................................................................................... 86
Section 29.6: JOIN table with inner JSON collection
............................................................................................... 86
Section 29.7: Finding rows that contain value in the JSON array
.......................................................................... 86
Chapter 30: Storing JSON in SQL tables
......................................................................................................... 87
Section 30.1: JSON stored as text column
................................................................................................................ 87
Section 30.2: Ensure that JSON is properly formatted using ISJSON
................................................................... 87
Section 30.3: Expose values from JSON text as computed columns
.................................................................... 87
Section 30.4: Adding index on JSON path
................................................................................................................ 87
Section 30.5: JSON stored in in-memory tables
...................................................................................................... 88
Chapter 31: Modify JSON text
............................................................................................................................... 89
Section 31.1: Modify value in JSON text on the specified path
................................................................................ 89
Section 31.2: Append a scalar value into a JSON array
.......................................................................................... 89
Section 31.3: Insert new JSON Object in JSON text
................................................................................................. 89
Section 31.4: Insert new JSON array generated with FOR JSON query
................................................................ 90
Section 31.5: Insert single JSON object generated with FOR JSON clause
........................................................... 90
Chapter 32: FOR XML PATH
................................................................................................................................... 92

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin