Complete Microsoft SQL Server Secrets & Tips for Professionals

Microsoft SQL Server

Complete Complete Tips & Secrets for Professionals

Microsoft? SQL Server?

Tips & Secrets for Professionals

100+ pages

of professional hints and tricks

Disclaimer

This is an unocial free book created for educational purposes and is

Free Programming Books

not aliated with ocial 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 .......................................................................................................................................................... 7 Section 1.7: Select rows that match a condition ......................................................................................................... 7 Section 1.8: UPDATE All Rows ....................................................................................................................................... 8 Section 1.9: TRUNCATE TABLE ..................................................................................................................................... 8 Section 1.10: Retrieve Basic Server Information ......................................................................................................... 8 Section 1.11: Create new table and insert records from old table ............................................................................. 9 Section 1.12: Using Transactions to change data safely ........................................................................................... 9 Section 1.13: Getting Table Row Count ...................................................................................................................... 10

Chapter 2: OVER Clause .......................................................................................................................................... 10

Section 2.1: Cumulative Sum ....................................................................................................................................... 10 Section 2.2: Using Aggregation functions with OVER ............................................................................................. 11 Section 2.3: Dividing Data into equally-partitioned buckets using NTILE ............................................................. 11 Section 2.4: Using Aggregation funtions to find the most recent records ............................................................ 12

Chapter 3: PIVOT / UNPIVOT ................................................................................................................................ 13

Section 3.1: Dynamic PIVOT ........................................................................................................................................ 13 Section 3.2: Simple PIVOT & UNPIVOT (T-SQL) ....................................................................................................... 14 Section 3.3: Simple Pivot - Static Columns ................................................................................................................ 16

Chapter 4: Database Snapshots ........................................................................................................................ 16

Section 4.1: Create a database snapshot ................................................................................................................. 16 Section 4.2: Restore a database snapshot ............................................................................................................... 17 Section 4.3: DELETE Snapshot ................................................................................................................................... 17

Chapter 5: Retrieve information about the database ............................................................................ 17

Section 5.1: Retrieve a List of all Stored Procedures ................................................................................................ 17 Section 5.2: Get the list of all databases on a server .............................................................................................. 18 Section 5.3: Count the Number of Tables in a Database ........................................................................................ 18 Section 5.4: Database Files ........................................................................................................................................ 18 Section 5.5: See if Enterprise-specific features are being used ............................................................................. 19 Section 5.6: Determine a Windows Login's Permission Path .................................................................................. 19 Section 5.7: Search and Return All Tables and Columns Containing a Specified Column Value ....................... 19 Section 5.8: Get all schemas, tables, columns and indexes .................................................................................... 20 Section 5.9: Return a list of SQL Agent jobs, with schedule information ............................................................... 21 Section 5.10: Retrieve Tables Containing Known Column ....................................................................................... 23 Section 5.11: Show Size of All Tables in Current Database ...................................................................................... 24 Section 5.12: Retrieve Database Options .................................................................................................................. 24 Section 5.13: Find every mention of a field in the database ................................................................................... 24 Section 5.14: Retrieve information on backup and restore operations ................................................................. 24

Chapter 6: The STUFF Function ........................................................................................................................... 25

Section 6.1: Using FOR XML to Concatenate Values from Multiple Rows ............................................................. 25 Section 6.2: Basic Character Replacement with STUFF() ....................................................................................... 25 Section 6.3: Basic Example of STUFF() function. ..................................................................................................... 25

Section 6.4: stu for comma separated in sql server ............................................................................................. 26 Section 6.5: Obtain column names separated with comma (not a list) ................................................................ 26

Chapter 7: FOR XML PATH ..................................................................................................................................... 26

Section 7.1: Using FOR XML PATH to concatenate values ...................................................................................... 26 Section 7.2: Specifying namespaces ......................................................................................................................... 27 Section 7.3: Specifying structure using XPath expressions ..................................................................................... 27 Section 7.4: Hello World XML ..................................................................................................................................... 28

Chapter 8: Cursors ..................................................................................................................................................... 29

Section 8.1: Basic Forward Only Cursor .................................................................................................................... 29 Section 8.2: Rudimentary cursor syntax ................................................................................................................... 29

Chapter 9: Join ............................................................................................................................................................. 30

Section 9.1: Inner Join .................................................................................................................................................. 30 Section 9.2: Outer Join ................................................................................................................................................ 31 Section 9.3: Using Join in an Update ......................................................................................................................... 33 Section 9.4: Join on a Subquery ................................................................................................................................ 34 Section 9.5: Cross Join ................................................................................................................................................ 34 Section 9.6: Self Join ................................................................................................................................................... 35 Section 9.7: Accidentally turning an outer join into an inner join ........................................................................... 36 Section 9.8: Delete using Join .................................................................................................................................... 37

Chapter 10: Common Table Expressions ......................................................................................................... 37

Section 10.1: Generate a table of dates using CTE ................................................................................................... 37 Section 10.2: Employee Hierarchy ............................................................................................................................. 37 Section 10.3: Recursive CTE ........................................................................................................................................ 38 Section 10.4: Delete duplicate rows using CTE ......................................................................................................... 39 Section 10.5: CTE with multiple AS statements ......................................................................................................... 40 Section 10.6: Find nth highest salary using CTE ....................................................................................................... 40

Chapter 11: Move and copy data around tables ......................................................................................... 41

Section 11.1: Copy data from one table to another .................................................................................................. 41 Section 11.2: Copy data into a table, creating that table on the fly ........................................................................ 41 Section 11.3: Move data into a table (assuming unique keys method) .................................................................. 41

Chapter 12: Dates ........................................................................................................................................................ 42

Section 12.1: Date & Time Formatting using CONVERT ........................................................................................... 42 Section 12.2: Date & Time Formatting using FORMAT ............................................................................................ 43 Section 12.3: DATEADD for adding and subtracting time periods ......................................................................... 44 Section 12.4: Create function to calculate a person's age on a specific date ....................................................... 45 Section 12.5: Get the current DateTime ..................................................................................................................... 45 Section 12.6: Getting the last day of a month ........................................................................................................... 46 Section 12.7: CROSS PLATFORM DATE OBJECT ...................................................................................................... 46 Section 12.8: Return just Date from a DateTime ...................................................................................................... 46 Section 12.9: DATEDIFF for calculating time period dierences ............................................................................ 46 Section 12.10: DATEPART & DATENAME .................................................................................................................... 47 Section 12.11: Date parts reference ............................................................................................................................ 48 Section 12.12: Date Format Extended ........................................................................................................................ 48

Chapter 13: Limit Result Set .................................................................................................................................. 51

Section 13.1: Limiting With PERCENT .......................................................................................................................... 51 Section 13.2: Limiting with FETCH .............................................................................................................................. 51 Section 13.3: Limiting With TOP .................................................................................................................................. 51

Chapter 14: Retrieve Information about your Instance ......................................................................... 51

Section 14.1: General Information about Databases, Tables, Stored procedures and how to search them.

................................................................................................................................................................................ 51 Section 14.2: Get information on current sessions and query executions ............................................................. 52 Section 14.3: Information about SQL Server version ............................................................................................... 53 Section 14.4: Retrieve Edition and Version of Instance ............................................................................................ 53 Section 14.5: Retrieve Instance Uptime in Days ....................................................................................................... 53 Section 14.6: Retrieve Local and Remote Servers .................................................................................................... 54

Chapter 15: With Ties Option ................................................................................................................................ 54

Section 15.1: Test Data ................................................................................................................................................. 54

Chapter 16: Variables ................................................................................................................................................ 55

Section 16.1: Declare a Table Variable ....................................................................................................................... 55 Section 16.2: Updating variables using SELECT ....................................................................................................... 56 Section 16.3: Declare multiple variables at once, with initial values ....................................................................... 56 Section 16.4: Updating a variable using SET ............................................................................................................. 56 Section 16.5: Updating variables by selecting from a table .................................................................................... 57 Section 16.6: Compound assignment operators ...................................................................................................... 57

Chapter 17: JSON in Sql Server ............................................................................................................................ 57

Section 17.1: Index on JSON properties by using computed columns ................................................................... 58 Section 17.2: Join parent and child JSON entities using CROSS APPLY OPENJSON ............................................ 58 Section 17.3: Format Query Results as JSON with FOR JSON ............................................................................... 59 Section 17.4: Parse JSON text ..................................................................................................................................... 60 Section 17.5: Format one table row as a single JSON object using FOR JSON ................................................... 60 Section 17.6: Parse JSON text using OPENJSON function ...................................................................................... 60

Chapter 18: Window functions .............................................................................................................................. 61

Section 18.1: Centered Moving Average .................................................................................................................... 61 Section 18.2: Find the single most recent item in a list of timestamped events ................................................... 61 Section 18.3: Moving Average of last 30 Items ......................................................................................................... 61

Chapter 19: Partitioning ........................................................................................................................................... 62

Section 19.1: Retrieve Partition Boundary Values ..................................................................................................... 62 Section 19.2: Switching Partitions ............................................................................................................................... 62 Section 19.3: Retrieve partition table,column, scheme, function, total and min-max boundry values using

single query ......................................................................................................................................................... 62

Chapter 20: Stored Procedures ........................................................................................................................... 63

Section 20.1: Creating and executing a basic stored procedure ............................................................................ 63 Section 20.2: Stored Procedure with If...Else and Insert Into operation ................................................................. 64 Section 20.3: Dynamic SQL in stored procedure ..................................................................................................... 65 Section 20.4: STORED PROCEDURE with OUT parameters ................................................................................... 66 Section 20.5: Simple Looping ..................................................................................................................................... 67 Section 20.6: Simple Looping ..................................................................................................................................... 68

Chapter 21: GROUP BY .............................................................................................................................................. 68

Section 21.1: Simple Grouping ..................................................................................................................................... 68 Section 21.2: GROUP BY multiple columns ................................................................................................................ 69 Section 21.3: GROUP BY with ROLLUP and CUBE .................................................................................................... 69 Section 21.4: Group by with multiple tables, multiple columns ............................................................................... 70 Section 21.5: HAVING ................................................................................................................................................... 71

Chapter 22: Generating a range of dates ...................................................................................................... 72

Section 22.1: Generating Date Range With Recursive CTE ..................................................................................... 72 Section 22.2: Generating a Date Range With a Tally Table ................................................................................... 73

Chapter 23: COALESCE ............................................................................................................................................. 73

Section 23.1: Using COALESCE to Build Comma-Delimited String ......................................................................... 73

Section 23.2: Getting the first not null from a list of column values ...................................................................... 74 Section 23.3: Coalesce basic Example ...................................................................................................................... 74

Chapter 24: Split String function in Sql Server ........................................................................................... 74

Section 24.1: Split string in Sql Server 2008/2012/2014 using XML ........................................................................ 74 Section 24.2: Split a String in Sql Server 2016 ........................................................................................................... 74 Section 24.3: T-SQL Table variable and XML ........................................................................................................... 75

Chapter 25: INSERT INTO ........................................................................................................................................ 76

Section 25.1: INSERT multiple rows of data .............................................................................................................. 76 Section 25.2: Use OUTPUT to get the new Id ........................................................................................................... 76 Section 25.3: INSERT from SELECT Query Results .................................................................................................. 77 Section 25.4: INSERT a single row of data ............................................................................................................... 77 Section 25.5: INSERT on specific columns ................................................................................................................ 77 Section 25.6: INSERT Hello World INTO table .......................................................................................................... 77

Chapter 26: CREATE VIEW ...................................................................................................................................... 78

Section 26.1: CREATE Indexed VIEW .......................................................................................................................... 78 Section 26.2: CREATE VIEW ........................................................................................................................................ 78 Section 26.3: CREATE VIEW With Encryption ........................................................................................................... 79 Section 26.4: CREATE VIEW With INNER JOIN ......................................................................................................... 79 Section 26.5: Grouped VIEWs ..................................................................................................................................... 79 Section 26.6: UNION-ed VIEWs .................................................................................................................................. 80

Chapter 27: String Functions ................................................................................................................................ 80

Section 27.1: Quotename ............................................................................................................................................ 80 Section 27.2: Replace .................................................................................................................................................. 81 Section 27.3: Substring ................................................................................................................................................ 81 Section 27.4: String_Split ............................................................................................................................................ 81 Section 27.5: Left ......................................................................................................................................................... 82 Section 27.6: Right ....................................................................................................................................................... 82 Section 27.7: Soundex ................................................................................................................................................. 83 Section 27.8: Format ................................................................................................................................................... 83 Section 27.9: String_escape ....................................................................................................................................... 85 Section 27.10: ASCII ...................................................................................................................................................... 85 Section 27.11: Char ....................................................................................................................................................... 86 Section 27.12: Concat .................................................................................................................................................. 86 Section 27.13: LTrim ..................................................................................................................................................... 86 Section 27.14: RTrim .................................................................................................................................................... 87 Section 27.15: PatIndex ................................................................................................................................................ 87 Section 27.16: Space .................................................................................................................................................... 87 Section 27.17: Dierence ............................................................................................................................................. 87 Section 27.18: Len ........................................................................................................................................................ 88 Section 27.19: Lower .................................................................................................................................................... 89 Section 27.20: Upper ................................................................................................................................................... 89 Section 27.21: Unicode ................................................................................................................................................ 89 Section 27.22: NChar ................................................................................................................................................... 89 Section 27.23: Str ......................................................................................................................................................... 90 Section 27.24: Reverse ................................................................................................................................................ 90 Section 27.25: Replicate .............................................................................................................................................. 90 Section 27.26: CharIndex ............................................................................................................................................ 91

Chapter 28: Resource Governor ......................................................................................................................... 91

Section 28.1: Reading the Statistics ........................................................................................................................... 91

Chapter 29: ORDER BY ............................................................................................................................................. 91

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download