Microsoft SQL Server Notes for Professionals (GoalKicker.com) (Z-Library) (1)

📄 File Format: PDF

💾 File Size: 2.6 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

Microsoft SQL Server Notes for ProfessionalsMicrosoft® SQL Server® Notes for Professionals 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 200+ pages of professional hints and tricks

📄 Page 2

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 6 ............................................................................................ Section 1.3: UPDATE Specific Row 6 ................................................................................................................................ Section 1.4: DELETE All Rows 7 ........................................................................................................................................ 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 9 ..................................................................................................................................... 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 .....................................................................................................................................................

📄 Page 3

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 34 ............................................................................. 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 ........................................................................................................................................

📄 Page 4

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 ..................................................................................................................................

📄 Page 5

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 76 ......................................................................................................................... Section 26.1: Index on JSON properties by using computed columns 76 .................................................................. Section 26.2: Join parent and child JSON entities using CROSS APPLY OPENJSON 77 ........................................... Section 26.3: Format Query Results as JSON with FOR JSON 78 .............................................................................. Section 26.4: Parse JSON text 78 .................................................................................................................................... Section 26.5: Format one table row as a single JSON object using FOR JSON 78 .................................................. Section 26.6: Parse JSON text using OPENJSON function 79 ..................................................................................... Chapter 27: OPENJSON 80 ........................................................................................................................................... Section 27.1: Transform JSON array into set of rows 80 ............................................................................................. Section 27.2: Get key:value pairs from JSON text 80 ................................................................................................... Section 27.3: Transform nested JSON fields into set of rows 80 ................................................................................ Section 27.4: Extracting inner JSON sub-objects 81 ..................................................................................................... Section 27.5: Working with nested JSON sub-arrays 81 .............................................................................................. Chapter 28: FOR JSON 83 ............................................................................................................................................. Section 28.1: FOR JSON PATH 83 ................................................................................................................................... Section 28.2: FOR JSON PATH with column aliases 83 ................................................................................................ Section 28.3: FOR JSON clause without array wrapper (single object in output) 83 ............................................... Section 28.4: INCLUDE_NULL_VALUES 84 .................................................................................................................... Section 28.5: Wrapping results with ROOT object 84 ................................................................................................... Section 28.6: FOR JSON AUTO 84 .................................................................................................................................. Section 28.7: Creating custom nested JSON structure 85 ........................................................................................... Chapter 29: Queries with JSON data 86 ................................................................................................................ Section 29.1: Using values from JSON in query 86 ....................................................................................................... Section 29.2: Using JSON values in reports 86 ............................................................................................................. Section 29.3: Filter-out bad JSON text from query results 86 ..................................................................................... Section 29.4: Update value in JSON column 86 ............................................................................................................ Section 29.5: Append new value into JSON array 87 ................................................................................................... Section 29.6: JOIN table with inner JSON collection 87 ............................................................................................... Section 29.7: Finding rows that contain value in the JSON array 87 .......................................................................... Chapter 30: Storing JSON in SQL tables 88 ......................................................................................................... Section 30.1: JSON stored as text column 88 ................................................................................................................ Section 30.2: Ensure that JSON is properly formatted using ISJSON 88 ................................................................... Section 30.3: Expose values from JSON text as computed columns 88 .................................................................... Section 30.4: Adding index on JSON path 88 ................................................................................................................ Section 30.5: JSON stored in in-memory tables 89 ...................................................................................................... Chapter 31: Modify JSON text 90 ............................................................................................................................... Section 31.1: Modify value in JSON text on the specified path 90 ................................................................................ Section 31.2: Append a scalar value into a JSON array 90 .......................................................................................... Section 31.3: Insert new JSON Object in JSON text 90 ................................................................................................. Section 31.4: Insert new JSON array generated with FOR JSON query 91 ................................................................ Section 31.5: Insert single JSON object generated with FOR JSON clause 91 ........................................................... Chapter 32: FOR XML PATH 93 ...................................................................................................................................

📄 Page 6

Section 32.1: Using FOR XML PATH to concatenate values 93 .................................................................................... Section 32.2: Specifying namespaces 93 ....................................................................................................................... Section 32.3: Specifying structure using XPath expressions 94 ................................................................................... Section 32.4: Hello World XML 95 ................................................................................................................................... Chapter 33: Join 96 ........................................................................................................................................................... Section 33.1: Inner Join 96 ................................................................................................................................................ Section 33.2: Outer Join 97 .............................................................................................................................................. Section 33.3: Using Join in an Update 99 ....................................................................................................................... Section 33.4: Join on a Subquery 99 .............................................................................................................................. Section 33.5: Cross Join 100 ............................................................................................................................................ Section 33.6: Self Join 101 ............................................................................................................................................... Section 33.7: Accidentally turning an outer join into an inner join 101 ....................................................................... Section 33.8: Delete using Join 102 ................................................................................................................................ Chapter 34: cross apply 104 ........................................................................................................................................ Section 34.1: Join table rows with dynamically generated rows from a cell 104 ...................................................... Section 34.2: Join table rows with JSON array stored in cell 104 ............................................................................... Section 34.3: Filter rows by array values 104 ................................................................................................................ Chapter 35: Computed Columns 106 ....................................................................................................................... Section 35.1: A column is computed from an expression 106 ...................................................................................... Section 35.2: Simple example we normally use in log tables 106 ............................................................................... Chapter 36: Common Table Expressions 107 ...................................................................................................... Section 36.1: Generate a table of dates using CTE 107 ................................................................................................ Section 36.2: Employee Hierarchy 107 ........................................................................................................................... Section 36.3: Recursive CTE 108 ..................................................................................................................................... Section 36.4: Delete duplicate rows using CTE 109 ...................................................................................................... Section 36.5: CTE with multiple AS statements 110 ...................................................................................................... Section 36.6: Find nth highest salary using CTE 110 .................................................................................................... Chapter 37: Move and copy data around tables 111 ..................................................................................... Section 37.1: Copy data from one table to another 111 ............................................................................................... Section 37.2: Copy data into a table, creating that table on the fly 111 .................................................................... Section 37.3: Move data into a table (assuming unique keys method) 111 .............................................................. Chapter 38: Limit Result Set 113 ............................................................................................................................... Section 38.1: Limiting With PERCENT 113 ....................................................................................................................... Section 38.2: Limiting with FETCH 113 ........................................................................................................................... Section 38.3: Limiting With TOP 113 ............................................................................................................................... Chapter 39: Retrieve Information about your Instance 114 ....................................................................... Section 39.1: General Information about Databases, Tables, Stored procedures and how to search them 114 ............................................................................................................................................................................. Section 39.2: Get information on current sessions and query executions 115 .......................................................... Section 39.3: Information about SQL Server version 116 ............................................................................................. Section 39.4: Retrieve Edition and Version of Instance 116 ......................................................................................... Section 39.5: Retrieve Instance Uptime in Days 116 .................................................................................................... Section 39.6: Retrieve Local and Remote Servers 116 ................................................................................................. Chapter 40: With Ties Option 117 ............................................................................................................................ Section 40.1: Test Data 117 ............................................................................................................................................. Chapter 41: String Functions 119 .............................................................................................................................. Section 41.1: Quotename 119 ........................................................................................................................................... Section 41.2: Replace 119 ................................................................................................................................................ Section 41.3: Substring 120 ..............................................................................................................................................

📄 Page 7

Section 41.4: String_Split 120 ........................................................................................................................................... Section 41.5: Left 121 ........................................................................................................................................................ Section 41.6: Right 121 ..................................................................................................................................................... Section 41.7: Soundex 122 ................................................................................................................................................ Section 41.8: Format 122 .................................................................................................................................................. Section 41.9: String_escape 124 ..................................................................................................................................... Section 41.10: ASCII 124 .................................................................................................................................................... Section 41.11: Char 125 ...................................................................................................................................................... Section 41.12: Concat 125 ................................................................................................................................................. Section 41.13: LTrim 125 ................................................................................................................................................... Section 41.14: RTrim 126 ................................................................................................................................................... Section 41.15: PatIndex 126 .............................................................................................................................................. Section 41.16: Space 126 ................................................................................................................................................... Section 41.17: Dierence 127 ........................................................................................................................................... Section 41.18: Len 127 ....................................................................................................................................................... Section 41.19: Lower 128 ................................................................................................................................................... Section 41.20: Upper 128 ................................................................................................................................................. Section 41.21: Unicode 128 ............................................................................................................................................... Section 41.22: NChar 129 ................................................................................................................................................. Section 41.23: Str 129 ........................................................................................................................................................ Section 41.24: Reverse 129 .............................................................................................................................................. Section 41.25: Replicate 129 ............................................................................................................................................ Section 41.26: CharIndex 130 ........................................................................................................................................... Chapter 42: Logical Functions 131 ........................................................................................................................... Section 42.1: CHOOSE 131 ............................................................................................................................................... Section 42.2: IIF 131 .......................................................................................................................................................... Chapter 43: Aggregate Functions 132 ................................................................................................................... Section 43.1: SUM() 132 .................................................................................................................................................... Section 43.2: AVG() 132 ................................................................................................................................................... Section 43.3: MAX() 133 ................................................................................................................................................... Section 43.4: MIN() 133 .................................................................................................................................................... Section 43.5: COUNT() 133 .............................................................................................................................................. Section 43.6: COUNT(Column_Name) with GROUP BY Column_Name 134 ............................................................. Chapter 44: String Aggregate functions in SQL Server 135 ...................................................................... Section 44.1: Using STUFF for string aggregation 135 ................................................................................................. Section 44.2: String_Agg for String Aggregation 135 .................................................................................................. Chapter 45: Ranking Functions 136 ......................................................................................................................... Section 45.1: DENSE_RANK () 136 .................................................................................................................................. Section 45.2: RANK() 136 ................................................................................................................................................. Chapter 46: Window functions 137 .......................................................................................................................... Section 46.1: Centered Moving Average 137 ................................................................................................................. Section 46.2: Find the single most recent item in a list of timestamped events 137 ................................................ Section 46.3: Moving Average of last 30 Items 137 ...................................................................................................... Chapter 47: PIVOT / UNPIVOT 138 .......................................................................................................................... Section 47.1: Dynamic PIVOT 138 ................................................................................................................................... Section 47.2: Simple PIVOT & UNPIVOT (T-SQL) 139 ................................................................................................... Section 47.3: Simple Pivot - Static Columns 141 ............................................................................................................ Chapter 48: Dynamic SQL Pivot 142 ....................................................................................................................... Section 48.1: Basic Dynamic SQL Pivot 142 ...................................................................................................................

📄 Page 8

Chapter 49: Partitioning 143 ....................................................................................................................................... Section 49.1: Retrieve Partition Boundary Values 143 .................................................................................................. Section 49.2: Switching Partitions 143 ............................................................................................................................ Section 49.3: Retrieve partition table,column, scheme, function, total and min-max boundry values using single query 143 ....................................................................................................................................................... Chapter 50: Stored Procedures 145 ........................................................................................................................ Section 50.1: Creating and executing a basic stored procedure 145 .......................................................................... Section 50.2: Stored Procedure with If...Else and Insert Into operation 146 ............................................................... Section 50.3: Dynamic SQL in stored procedure 147 ................................................................................................... Section 50.4: STORED PROCEDURE with OUT parameters 148 ................................................................................. Section 50.5: Simple Looping 149 ................................................................................................................................... Section 50.6: Simple Looping 150 ................................................................................................................................... Chapter 51: Retrieve information about the database 151 ........................................................................ Section 51.1: Retrieve a List of all Stored Procedures 151 ............................................................................................ Section 51.2: Get the list of all databases on a server 151 ........................................................................................... Section 51.3: Count the Number of Tables in a Database 152 .................................................................................... Section 51.4: Database Files 152 ..................................................................................................................................... Section 51.5: See if Enterprise-specific features are being used 153 .......................................................................... Section 51.6: Determine a Windows Login's Permission Path 153 ............................................................................... Section 51.7: Search and Return All Tables and Columns Containing a Specified Column Value 153 .................... Section 51.8: Get all schemas, tables, columns and indexes 154 ................................................................................. Section 51.9: Return a list of SQL Agent jobs, with schedule information 155 ........................................................... Section 51.10: Retrieve Tables Containing Known Column 157 ................................................................................... Section 51.11: Show Size of All Tables in Current Database 158 ................................................................................... Section 51.12: Retrieve Database Options 158 ............................................................................................................... Section 51.13: Find every mention of a field in the database 158 ................................................................................ Section 51.14: Retrieve information on backup and restore operations 158 .............................................................. Chapter 52: Split String function in SQL Server 160 ....................................................................................... Section 52.1: Split string in Sql Server 2008/2012/2014 using XML 160 ...................................................................... Section 52.2: Split a String in Sql Server 2016 160 ......................................................................................................... Section 52.3: T-SQL Table variable and XML 161 ......................................................................................................... Chapter 53: Insert 162 ..................................................................................................................................................... Section 53.1: Add a row to a table named Invoices 162 ............................................................................................... Chapter 54: Primary Keys 163 ................................................................................................................................... Section 54.1: Create table w/ identity column as primary key 163 ............................................................................. Section 54.2: Create table w/ GUID primary key 163 .................................................................................................. Section 54.3: Create table w/ natural key 163 .............................................................................................................. Section 54.4: Create table w/ composite key 163 ........................................................................................................ Section 54.5: Add primary key to existing table 163 .................................................................................................... Section 54.6: Delete primary key 164 ............................................................................................................................. Chapter 55: Foreign Keys 165 ..................................................................................................................................... Section 55.1: Foreign key relationship/constraint 165 .................................................................................................. Section 55.2: Maintaining relationship between parent/child rows 165 ..................................................................... Section 55.3: Adding foreign key relationship on existing table 166 .......................................................................... Section 55.4: Add foreign key on existing table 166 ..................................................................................................... Section 55.5: Getting information about foreign key constraints 166 ........................................................................ Chapter 56: Last Inserted Identity 167 ................................................................................................................... Section 56.1: @@IDENTITY and MAX(ID) 167 ................................................................................................................ Section 56.2: SCOPE_IDENTITY() 167 ............................................................................................................................

📄 Page 9

Section 56.3: @@IDENTITY 167 ...................................................................................................................................... Section 56.4: IDENT_CURRENT('tablename') 168 ........................................................................................................ Chapter 57: SCOPE_IDENTITY() 169 ........................................................................................................................ Section 57.1: Introduction with Simple Example 169 ..................................................................................................... Chapter 58: Sequences 170 .......................................................................................................................................... Section 58.1: Create Sequence 170 ................................................................................................................................. Section 58.2: Use Sequence in Table 170 ...................................................................................................................... Section 58.3: Insert Into Table with Sequence 170 ........................................................................................................ Section 58.4: Delete From & Insert New 170 ................................................................................................................. Chapter 59: Index 171 ...................................................................................................................................................... Section 59.1: Create Clustered index 171 ....................................................................................................................... Section 59.2: Drop index 171 ........................................................................................................................................... Section 59.3: Create Non-Clustered index 171 .............................................................................................................. Section 59.4: Show index info 171 ................................................................................................................................... Section 59.5: Returns size and fragmentation indexes 171 ......................................................................................... Section 59.6: Reorganize and rebuild index 172 ........................................................................................................... Section 59.7: Rebuild or reorganize all indexes on a table 172 ................................................................................... Section 59.8: Rebuild all index database 172 ................................................................................................................ Section 59.9: Index on view 172 ...................................................................................................................................... Section 59.10: Index investigations 173 .......................................................................................................................... Chapter 60: Full-Text Indexing 174 ........................................................................................................................... Section 60.1: A. Creating a unique index, a full-text catalog, and a full-text index 174 ............................................. Section 60.2: Creating a full-text index on several table columns 174 ....................................................................... Section 60.3: Creating a full-text index with a search property list without populating it 174 ................................. Section 60.4: Full-Text Search 175 .................................................................................................................................. Chapter 61: Trigger 176 .................................................................................................................................................. Section 61.1: DML Triggers 176 ........................................................................................................................................ Section 61.2: Types and classifications of Trigger 177 ................................................................................................. Chapter 62: Cursors 178 ................................................................................................................................................. Section 62.1: Basic Forward Only Cursor 178 ................................................................................................................ Section 62.2: Rudimentary cursor syntax 178 ............................................................................................................... Chapter 63: Transaction isolation levels 180 ...................................................................................................... Section 63.1: Read Committed 180 ................................................................................................................................. Section 63.2: What are "dirty reads"? 180 ..................................................................................................................... Section 63.3: Read Uncommitted 181 ............................................................................................................................ Section 63.4: Repeatable Read 181 ................................................................................................................................ Section 63.5: Snapshot 181 .............................................................................................................................................. Section 63.6: Serializable 181 .......................................................................................................................................... Chapter 64: Advanced options 183 .......................................................................................................................... Section 64.1: Enable and show advanced options 183 ................................................................................................. Section 64.2: Enable backup compression default 183 ................................................................................................ Section 64.3: Enable cmd permission 183 ...................................................................................................................... Section 64.4: Set default fill factor percent 183 ............................................................................................................ Section 64.5: Set system recovery interval 183 ............................................................................................................ Section 64.6: Set max server memory size 183 ............................................................................................................. Section 64.7: Set number of checkpoint tasks 183 ....................................................................................................... Chapter 65: Migration 184 ............................................................................................................................................ Section 65.1: How to generate migration scripts 184 ................................................................................................... Chapter 66: Table Valued Parameters 186 ..........................................................................................................

📄 Page 10

Section 66.1: Using a table valued parameter to insert multiple rows to a table 186 ............................................... Chapter 67: DBMAIL 187 ................................................................................................................................................. Section 67.1: Send simple email 187 ............................................................................................................................... Section 67.2: Send results of a query 187 ...................................................................................................................... Section 67.3: Send HTML email 187 ................................................................................................................................ Chapter 68: In-Memory OLTP (Hekaton) 188 ...................................................................................................... Section 68.1: Declare Memory-Optimized Table Variables 188 ................................................................................... Section 68.2: Create Memory Optimized Table 188 ..................................................................................................... Section 68.3: Show created .dll files and tables for Memory Optimized Tables 189 ................................................. Section 68.4: Create Memory Optimized System-Versioned Temporal Table 190 ................................................... Section 68.5: Memory-Optimized Table Types and Temp tables 190 ........................................................................ Chapter 69: Temporal Tables 192 ............................................................................................................................ Section 69.1: CREATE Temporal Tables 192 .................................................................................................................. Section 69.2: FOR SYSTEM_TIME ALL 192 ..................................................................................................................... Section 69.3: Creating a Memory-Optimized System-Versioned Temporal Table and cleaning up the SQL Server history table 192 ........................................................................................................................................... Section 69.4: FOR SYSTEM_TIME BETWEEN <start_date_time> AND <end_date_time> 194 ............................... Section 69.5: FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time> 194 ......................................... Section 69.6: FOR SYSTEM_TIME CONTAINED IN (<start_date_time> , <end_date_time>) 194 ........................... Section 69.7: How do I query temporal data? 194 ........................................................................................................ Section 69.8: Return actual value specified point in time(FOR SYSTEM_TIME AS OF <date_time>) 195 .............. Chapter 70: Use of TEMP Table 196 ........................................................................................................................ Section 70.1: Dropping temp tables 196 ......................................................................................................................... Section 70.2: Local Temp Table 196 .............................................................................................................................. Section 70.3: Global Temp Table 196 ............................................................................................................................. Chapter 71: Scheduled Task or Job 198 ................................................................................................................. Section 71.1: Create a scheduled Job 198 ...................................................................................................................... Chapter 72: Isolation levels and locking 200 ....................................................................................................... Section 72.1: Examples of setting the isolation level 200 .............................................................................................. Chapter 73: Sorting/ordering rows 201 ................................................................................................................. Section 73.1: Basics 201 .................................................................................................................................................... Section 73.2: Order by Case 203 ..................................................................................................................................... Chapter 74: Privileges or Permissions 205 ........................................................................................................... Section 74.1: Simple rules 205 .......................................................................................................................................... Chapter 75: SQLCMD 206 ............................................................................................................................................... Section 75.1: SQLCMD.exe called from a batch file or command line 206 ................................................................. Chapter 76: Resource Governor 207 ....................................................................................................................... Section 76.1: Reading the Statistics 207 ......................................................................................................................... Chapter 77: File Group 208 ........................................................................................................................................... Section 77.1: Create filegroup in database 208 ............................................................................................................. Chapter 78: Basic DDL Operations in MS SQL Server 210 ............................................................................ Section 78.1: Getting started 210 ..................................................................................................................................... Chapter 79: Subqueries 212 ......................................................................................................................................... Section 79.1: Subqueries 212 ............................................................................................................................................ Chapter 80: Pagination 214 ......................................................................................................................................... Section 80.1: Pagination with OFFSET FETCH 214 ........................................................................................................ Section 80.2: Paginaton with inner query 214 ............................................................................................................... Section 80.3: Paging in Various Versions of SQL Server 214 .......................................................................................

📄 Page 11

Section 80.4: SQL Server 2012/2014 using ORDER BY OFFSET and FETCH NEXT 215 ............................................. Section 80.5: Pagination using ROW_NUMBER with a Common Table Expression 215 .......................................... Chapter 81: CLUSTERED COLUMNSTORE 217 ...................................................................................................... Section 81.1: Adding clustered columnstore index on existing table 217 .................................................................... Section 81.2: Rebuild CLUSTERED COLUMNSTORE index 217 .................................................................................... Section 81.3: Table with CLUSTERED COLUMNSTORE index 217 ................................................................................ Chapter 82: Parsename 218 ......................................................................................................................................... Section 82.1: PARSENAME 218 ......................................................................................................................................... Chapter 83: Installing SQL Server on Windows 219 ......................................................................................... Section 83.1: Introduction 219 .......................................................................................................................................... Chapter 84: Analyzing a Query 220 ......................................................................................................................... Section 84.1: Scan vs Seek 220 ........................................................................................................................................ Chapter 85: Query Hints 221 ....................................................................................................................................... Section 85.1: JOIN Hints 221 ............................................................................................................................................ Section 85.2: GROUP BY Hints 221 ................................................................................................................................. Section 85.3: FAST rows hint 222 .................................................................................................................................... Section 85.4: UNION hints 222 ........................................................................................................................................ Section 85.5: MAXDOP Option 222 ................................................................................................................................. Section 85.6: INDEX Hints 222 ......................................................................................................................................... Chapter 86: Query Store 224 ....................................................................................................................................... Section 86.1: Enable query store on database 224 ....................................................................................................... Section 86.2: Get execution statistics for SQL queries/plans 224 ............................................................................... Section 86.3: Remove data from query store 224 ........................................................................................................ Section 86.4: Forcing plan for query 224 ....................................................................................................................... Chapter 87: Querying results by page 226 .......................................................................................................... Section 87.1: Row_Number() 226 .................................................................................................................................... Chapter 88: Schemas 227 ............................................................................................................................................. Section 88.1: Purpose 227 ................................................................................................................................................ Section 88.2: Creating a Schema 227 ............................................................................................................................ Section 88.3: Alter Schema 227 ....................................................................................................................................... Section 88.4: Dropping Schemas 227 ............................................................................................................................. Chapter 89: Backup and Restore Database 228 ............................................................................................... Section 89.1: Basic Backup to disk with no options 228 ............................................................................................... Section 89.2: Basic Restore from disk with no options 228 ......................................................................................... Section 89.3: RESTORE Database with REPLACE 228 .................................................................................................. Chapter 90: Transaction handling 229 ................................................................................................................... Section 90.1: basic transaction skeleton with error handling 229 ............................................................................... Chapter 91: Natively compiled modules (Hekaton) 230 ................................................................................ Section 91.1: Natively compiled stored procedure 230 ................................................................................................. Section 91.2: Natively compiled scalar function 230 ..................................................................................................... Section 91.3: Native inline table value function 231 ...................................................................................................... Chapter 92: Spatial Data 233 ...................................................................................................................................... Section 92.1: POINT 233 ................................................................................................................................................... Chapter 93: Dynamic SQL 234 ..................................................................................................................................... Section 93.1: Execute SQL statement provided as string 234 ...................................................................................... Section 93.2: Dynamic SQL executed as dierent user 234 ........................................................................................ Section 93.3: SQL Injection with dynamic SQL 234 ....................................................................................................... Section 93.4: Dynamic SQL with parameters 235 .........................................................................................................

📄 Page 12

Chapter 94: Dynamic data masking 236 ............................................................................................................... Section 94.1: Adding default mask on the column 236 ................................................................................................. Section 94.2: Mask email address using Dynamic data masking 236 ........................................................................ Section 94.3: Add partial mask on column 236 ............................................................................................................. Section 94.4: Showing random value from the range using random() mask 236 .................................................... Section 94.5: Controlling who can see unmasked data 237 ........................................................................................ Chapter 95: Export data in txt file by using SQLCMD 238 ............................................................................ Section 95.1: By using SQLCMD on Command Prompt 238 ......................................................................................... Chapter 96: Common Language Runtime Integration 239 .......................................................................... Section 96.1: Enable CLR on database 239 .................................................................................................................... Section 96.2: Adding .dll that contains Sql CLR modules 239 ...................................................................................... Section 96.3: Create CLR Function in SQL Server 239 .................................................................................................. Section 96.4: Create CLR User-defined type in SQL Server 240 .................................................................................. Section 96.5: Create CLR procedure in SQL Server 240 ............................................................................................... Chapter 97: Delimiting special characters and reserved words 241 ...................................................... Section 97.1: Basic Method 241 ....................................................................................................................................... Chapter 98: DBCC 242 ..................................................................................................................................................... Section 98.1: DBCC statement 242 .................................................................................................................................. Section 98.2: DBCC maintenance commands 242 ....................................................................................................... Section 98.3: DBCC validation statements 243 ............................................................................................................. Section 98.4: DBCC informational statements 243 ....................................................................................................... Section 98.5: DBCC Trace commands 243 .................................................................................................................... Chapter 99: BULK Import 245 ...................................................................................................................................... Section 99.1: BULK INSERT 245 ....................................................................................................................................... Section 99.2: BULK INSERT with options 245 ................................................................................................................ Section 99.3: Reading entire content of file using OPENROWSET(BULK) 245 .......................................................... Section 99.4: Read file using OPENROWSET(BULK) and format file 245 .................................................................. Section 99.5: Read json file using OPENROWSET(BULK) 246 ..................................................................................... Chapter 100: Service broker 247 ............................................................................................................................... Section 100.1: Basics 247 .................................................................................................................................................. Section 100.2: Enable service broker on database 247 ................................................................................................ Section 100.3: Create basic service broker construction on database (single database communication) 247 ............................................................................................................................................................................. Section 100.4: How to send basic communication through service broker 248 ........................................................ Section 100.5: How to receive conversation from TargetQueue automatically 248 ................................................. Chapter 101: Permissions and Security 250 .......................................................................................................... Section 101.1: Assign Object Permissions to a user 250 ................................................................................................ Chapter 102: Database permissions 251 ............................................................................................................... Section 102.1: Changing permissions 251 ....................................................................................................................... Section 102.2: CREATE USER 251 .................................................................................................................................... Section 102.3: CREATE ROLE 251 .................................................................................................................................... Section 102.4: Changing role membership 251 ............................................................................................................. Chapter 103: Row-level security 252 ........................................................................................................................ Section 103.1: RLS filter predicate 252 ............................................................................................................................ Section 103.2: Altering RLS security policy 252 ............................................................................................................. Section 103.3: Preventing updated using RLS block predicate 253 ............................................................................. Chapter 104: Encryption 254 ....................................................................................................................................... Section 104.1: Encryption by certificate 254 ................................................................................................................... Section 104.2: Encryption of database 254 ....................................................................................................................

📄 Page 13

Section 104.3: Encryption by symmetric key 254 .......................................................................................................... Section 104.4: Encryption by passphrase 255 ............................................................................................................... Chapter 105: PHANTOM read 256 .............................................................................................................................. Section 105.1: Isolation level READ UNCOMMITTED 256 .............................................................................................. Chapter 106: Filestream 257 ........................................................................................................................................ Section 106.1: Example 257 .............................................................................................................................................. Chapter 107: bcp (bulk copy program) Utility 258 ........................................................................................... Section 107.1: Example to Import Data without a Format File(using Native Format ) 258 ....................................... Chapter 108: SQL Server Evolution through dierent versions (2000 - 2016) 259 .......................... Section 108.1: SQL Server Version 2000 - 2016 259 ....................................................................................................... Chapter 109: SQL Server Management Studio (SSMS) 262 .......................................................................... Section 109.1: Refreshing the IntelliSense cache 262 .................................................................................................... Chapter 110: Managing Azure SQL Database 263 ............................................................................................. Section 110.1: Find service tier information for Azure SQL Database 263 ................................................................... Section 110.2: Change service tier of Azure SQL Database 263 .................................................................................. Section 110.3: Replication of Azure SQL Database 263 ................................................................................................ Section 110.4: Create Azure SQL Database in Elastic pool 264 .................................................................................... Chapter 111: System database - TempDb 265 .................................................................................................... Section 111.1: Identify TempDb usage 265 ...................................................................................................................... Section 111.2: TempDB database details 265 ................................................................................................................. Appendix A: Microsoft SQL Server Management Studio Shortcut Keys 266 ...................................... Section A.1: Shortcut Examples 266 ................................................................................................................................ Section A.2: Menu Activation Keyboard Shortcuts 266 ................................................................................................ Section A.3: Custom keyboard shortcuts 266 ............................................................................................................... Credits 269 ............................................................................................................................................................................ You may also like 273 ......................................................................................................................................................

📄 Page 14

GoalKicker.com – Microsoft® SQL Server® Notes for Professionals 1 About Please feel free to share this PDF with anyone for free, latest version of this book can be downloaded from: https://goalkicker.com/MicrosoftSQLServerBook This Microsoft® SQL Server® Notes for Professionals book is compiled from Stack Overflow Documentation, the content is written by the beautiful people at Stack Overflow. Text content is released under Creative Commons BY-SA, see credits at the end of this book whom contributed to the various chapters. Images may be copyright of their respective owners unless otherwise specified This is an unofficial free book created for educational purposes and is not affiliated with official Microsoft® SQL Server® group(s) or company(s) nor Stack Overflow. All trademarks and registered trademarks are the property of their respective company owners The information presented in this book is not guaranteed to be correct nor accurate, use at your own risk Please send feedback and corrections to web@petercv.com

📄 Page 15

GoalKicker.com – Microsoft® SQL Server® Notes for Professionals 2 Chapter 1: Getting started with Microsoft SQL Server Version Release Date SQL Server 2017 2017-10-01 SQL Server 2016 2016-06-01 SQL Server 2014 2014-03-18 SQL Server 2012 2011-10-11 SQL Server 2008 R2 2010-04-01 SQL Server 2008 2008-08-06 SQL Server 2005 2005-11-01 SQL Server 2000 2000-11-01 Section 1.1: INSERT / SELECT / UPDATE / DELETE: the basics of Data Manipulation Language Data Manipulation Language (DML for short) includes operations such as INSERT, UPDATE and DELETE: -- Create a table HelloWorld CREATE TABLE HelloWorld ( Id INT IDENTITY, Description VARCHAR(1000) ) -- DML Operation INSERT, inserting a row into the table INSERT INTO HelloWorld (Description) VALUES ('Hello World') -- DML Operation SELECT, displaying the table SELECT * FROM HelloWorld -- Select a specific column from table SELECT Description FROM HelloWorld -- Display number of records in the table SELECT Count(*) FROM HelloWorld -- DML Operation UPDATE, updating a specific row in the table UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1 -- Selecting rows from the table (see how the Description has changed after the update?) SELECT * FROM HelloWorld -- DML Operation - DELETE, deleting a row from the table DELETE FROM HelloWorld WHERE Id = 1 -- Selecting the table. See table content after DELETE operation

📄 Page 16

GoalKicker.com – Microsoft® SQL Server® Notes for Professionals 3 SELECT * FROM HelloWorld In this script we're creating a table to demonstrate some basic queries. The following examples are showing how to query tables: USE Northwind; GO SELECT TOP 10 * FROM Customers ORDER BY CompanyName will select the first 10 records of the Customer table, ordered by the column CompanyName from the database Northwind (which is one of Microsoft's sample databases, it can be downloaded from here): Note that Use Northwind; changes the default database for all subsequent queries. You can still reference the database by using the fully qualified syntax in the form of [Database].[Schema].[Table]: SELECT TOP 10 * FROM Northwind.dbo.Customers ORDER BY CompanyName SELECT TOP 10 * FROM Pubs.dbo.Authors ORDER BY City This is useful if you're querying data from different databases. Note that dbo, specified "in between" is called a schema and needs to be specified while using the fully qualified syntax. You can think of it as a folder within your database. dbo is the default schema. The default schema may be omitted. All other user defined schemas need to be specified. If the database table contains columns which are named like reserved words, e.g. Date, you need to enclose the column name in brackets, like this: -- descending order SELECT TOP 10 [Date] FROM dbo.MyLogTable ORDER BY [Date] DESC The same applies if the column name contains spaces in its name (which is not recommended). An alternative syntax is to use double quotes instead of square brackets, e.g.: -- descending order SELECT top 10 "Date" from dbo.MyLogTable order by "Date" desc is equivalent but not so commonly used. Notice the difference between double quotes and single quotes: Single quotes are used for strings, i.e. -- descending order SELECT top 10 "Date" from dbo.MyLogTable where UserId='johndoe'

📄 Page 17

GoalKicker.com – Microsoft® SQL Server® Notes for Professionals 4 order by "Date" desc is a valid syntax. Notice that T-SQL has a N prefix for NChar and NVarchar data types, e.g. SELECT TOP 10 * FROM Northwind.dbo.Customers WHERE CompanyName LIKE N'AL%' ORDER BY CompanyName returns all companies having a company name starting with AL (% is a wild card, use it as you would use the asterisk in a DOS command line, e.g. DIR AL*). For LIKE, there are a couple of wildcards available, look here to find out more details. Joins Joins are useful if you want to query fields which don't exist in one single table, but in multiple tables. For example: You want to query all columns from the Region table in the Northwind database. But you notice that you require also the RegionDescription, which is stored in a different table, Region. However, there is a common key, RgionID which you can use to combine this information in a single query as follows (Top 5 just returns the first 5 rows, omit it to get all rows): SELECT TOP 5 Territories.*, Regions.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID=Region.RegionID ORDER BY TerritoryDescription will show all columns from Territories plus the RegionDescription column from Region. The result is ordered by TerritoryDescription. Table Aliases When your query requires a reference to two or more tables, you may find it useful to use a Table Alias. Table aliases are shorthand references to tables that can be used in place of a full table name, and can reduce typing and editing. The syntax for using an alias is: <TableName> [as] <alias> Where as is an optional keyword. For example, the previous query can be rewritten as: SELECT TOP 5 t.*, r.RegionDescription FROM Territories t INNER JOIN Region r ON t.RegionID = r.RegionID ORDER BY TerritoryDescription Aliases must be unique for all tables in a query, even if you use the same table twice. For example, if your Employee table included a SupervisorId field, you can use this query to return an employee and his supervisor's name: SELECT e.*, s.Name as SupervisorName -- Rename the field for output FROM Employee e INNER JOIN Employee s ON e.SupervisorId = s.EmployeeId

📄 Page 18

GoalKicker.com – Microsoft® SQL Server® Notes for Professionals 5 WHERE e.EmployeeId = 111 Unions As we have seen before, a Join adds columns from different table sources. But what if you want to combine rows from different sources? In this case you can use a UNION. Suppose you're planning a party and want to invite not only employees but also the customers. Then you could run this query to do it: SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees UNION SELECT ContactName, Address, City FROM Customers It will return names, addresses and cities from the employees and customers in one single table. Note that duplicate rows (if there should be any) are automatically eliminated (if you don't want this, use a UNION ALL instead). The column number, column names, order and data type must match across all the select statements that are part of the union - this is why the first SELECT combines FirstName and LastName from Employee into ContactName. Table Variables It can be useful, if you need to deal with temporary data (especially in a stored procedure), to use table variables: The difference between a "real" table and a table variable is that it just exists in memory for temporary processing. Example: DECLARE @Region TABLE ( RegionID int, RegionDescription NChar(50) ) creates a table in memory. In this case the @ prefix is mandatory because it is a variable. You can perform all DML operations mentioned above to insert, delete and select rows, e.g. INSERT INTO @Region values(3,'Northern') INSERT INTO @Region values(4,'Southern') But normally, you would populate it based on a real table like INSERT INTO @Region SELECT * FROM dbo.Region WHERE RegionID>2; which would read the filtered values from the real table dbo.Region and insert it into the memory table @Region - where it can be used for further processing. For example, you could use it in a join like SELECT * FROM Territories t JOIN @Region r on t.RegionID=r.RegionID which would in this case return all Northern and Southern territories. More detailed information can be found here. Temporary tables are discussed here, if you are interested to read more about that topic. NOTE: Microsoft only recommends the use of table variables if the number of rows of data in the table variable are less than 100. If you will be working with larger amounts of data, use a temporary table, or temp table, instead.

📄 Page 19

GoalKicker.com – Microsoft® SQL Server® Notes for Professionals 6 Section 1.2: SELECT all rows and columns from a table Syntax: SELECT * FROM table_name Using the asterisk operator * serves as a shortcut for selecting all the columns in the table. All rows will also be selected because this SELECT statement does not have a WHERE clause, to specify any filtering criteria. This would also work the same way if you added an alias to the table, for instance e in this case: SELECT * FROM Employees AS e Or if you wanted to select all from a specific table you can use the alias + " .* ": SELECT e.*, d.DepartmentName FROM Employees AS e INNER JOIN Department AS d ON e.DepartmentID = d.DepartmentID Database objects may also be accessed using fully qualified names: SELECT * FROM [server_name].[database_name].[schema_name].[table_name] This is not necessarily recommended, as changing the server and/or database names would cause the queries using fully-qualified names to no longer execute due to invalid object names. Note that the fields before table_name can be omitted in many cases if the queries are executed on a single server, database and schema, respectively. However, it is common for a database to have multiple schema, and in these cases the schema name should not be omitted when possible. Warning: Using SELECT * in production code or stored procedures can lead to problems later on (as new columns are added to the table, or if columns are rearranged in the table), especially if your code makes simple assumptions about the order of columns, or number of columns returned. So it's safer to always explicitly specify column names in SELECT statements for production code. SELECT col1, col2, col3 FROM table_name Section 1.3: UPDATE Specific Row UPDATE HelloWorlds SET HelloWorld = 'HELLO WORLD!!!' WHERE Id = 5 The above code updates the value of the field "HelloWorld" with "HELLO WORLD!!!" for the record where "Id = 5" in HelloWorlds table. Note: In an update statement, It is advised to use a "where" clause to avoid updating the whole table unless and until your requirement is different.

📄 Page 20

GoalKicker.com – Microsoft® SQL Server® Notes for Professionals 7 Section 1.4: DELETE All Rows DELETE FROM Helloworlds This will delete all the data from the table. The table will contain no rows after you run this code. Unlike DROP TABLE, this preserves the table itself and its structure and you can continue to insert new rows into that table. Another way to delete all rows in table is truncate it, as follow: TRUNCATE TABLE HelloWords Difference with DELETE operation are several: Truncate operation doesn't store in transaction log file1. If exists IDENTITY field, this will be reset2. TRUNCATE can be applied on whole table and no on part of it (instead with DELETE command you can3. associate a WHERE clause) Restrictions Of TRUNCATE Cannot TRUNCATE a table if there is a FOREIGN KEY reference1. If the table is participated in an INDEXED VIEW2. If the table is published by using TRANSACTIONAL REPLICATION or MERGE REPLICATION3. It will not fire any TRIGGER defined in the table4. [sic] Section 1.5: Comments in code Transact-SQL supports two forms of comment writing. Comments are ignored by the database engine, and are meant for people to read. Comments are preceded by -- and are ignored until a new line is encountered: -- This is a comment SELECT * FROM MyTable -- This is another comment WHERE Id = 1; Slash star comments begin with /* and end with */. All text between those delimiters is considered as a comment block. /* This is a multi-line comment block. */ SELECT Id = 1, [Message] = 'First row' UNION ALL SELECT 2, 'Second row' /* This is a one liner */ SELECT 'More'; Slash star comments have the advantage of keeping the comment usable if the SQL Statement loses new line characters. This can happen when SQL is captured during troubleshooting.

高宏飞

2025-12-07 12:17