Merge pull request MicrosoftDocs#19 from pmasl/master

pmasl · web-flow · commit e78415eb4929 · 2020-04-21T22:18:22.000-07:00
Adding new files
diff --git a/docs/t-sql/queries/hints-transact-sql-table.md b/docs/t-sql/queries/hints-transact-sql-table.md
@@ -1,7 +1,7 @@
 ---
 title: "Table Hints (Transact-SQL) | Microsoft Docs"
 ms.custom: ""
-ms.date: "08/31/2017"
+ms.date: "04/21/2020"
 ms.prod: sql
 ms.prod_service: "database-engine, sql-database"
 ms.reviewer: ""
@@ -40,10 +40,10 @@ ms.author: vanto
 # Hints (Transact-SQL) - Table
 [!INCLUDE[tsql-appliesto-ss2008-asdb-xxxx-xxx-md](../../includes/tsql-appliesto-ss2008-asdb-xxxx-xxx-md.md)]
 
-  Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.  
+  Table hints override the default behavior of the Query Optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.  
   
 > [!CAUTION]  
->  Because the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.  
+>  Because the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Query Optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.  
   
  **Applies to:**  
   
@@ -121,7 +121,7 @@ With some exceptions, table hints are supported in the FROM clause only when the
 > [!IMPORTANT]  
 > Omitting the WITH keyword is a deprecated feature: [!INCLUDE[ssNoteDepFutureAvoid](../../includes/ssnotedepfutureavoid-md.md)]  
   
-The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, SNAPSHOT, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone. For example:  
+The following table hints are allowed with and without the `WITH` keyword: `NOLOCK`, `READUNCOMMITTED`, `UPDLOCK`, `REPEATABLEREAD`, `SERIALIZABLE`, `READCOMMITTED`, `TABLOCK`, `TABLOCKX`, `PAGLOCK`, `ROWLOCK`, `NOWAIT`, `READPAST`, `XLOCK`, `SNAPSHOT`, and `NOEXPAND`. When these table hints are specified without the WITH keyword, the hints should be specified alone. For example:  
   
 ```sql  
 FROM t (TABLOCK)  
@@ -256,7 +256,7 @@ Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED late
 > For UPDATE or DELETE statements: [!INCLUDE[ssNoteDepFutureAvoid](../../includes/ssnotedepfutureavoid-md.md)]  
   
 NOWAIT  
-Instructs the [!INCLUDE[ssDE](../../includes/ssde-md.md)] to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table. The NOWAIT hint does not work when the TABLOCK hint is also included. To terminate a query without waiting when using the TABLOCK hint, preface the query with `SETLOCK_TIMEOUT 0;` instead.  
+Instructs the [!INCLUDE[ssDE](../../includes/ssde-md.md)] to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying `SET LOCK_TIMEOUT 0` for a specific table. The NOWAIT hint does not work when the TABLOCK hint is also included. To terminate a query without waiting when using the TABLOCK hint, preface the query with `SETLOCK_TIMEOUT 0;` instead.  
   
 PAGLOCK  
 Takes page locks either where individual locks are ordinarily taken on rows or keys, or where a single table lock is ordinarily taken. By default, uses the lock mode appropriate for the operation. When specified in transactions operating at the SNAPSHOT isolation level, page locks are not taken unless PAGLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.  
@@ -334,7 +334,7 @@ This option allows for fine-tuning of query execution time by adjusting the trad
 TABLOCK  
 Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.  
   
-When importing data into a heap by using the INSERT INTO \<target_table> SELECT \<columns> FROM \<source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged. For more information, see [INSERT &#40;Transact-SQL&#41;](../../t-sql/statements/insert-transact-sql.md).  
+When importing data into a heap by using the `INSERT INTO \<target_table> SELECT \<columns> FROM \<source_table>` statement, you can enable minimal logging and optimized locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged. The TABLOCK hint also enables parallel inserts. For more information, see [INSERT &#40;Transact-SQL&#41;](../../t-sql/statements/insert-transact-sql.md).  
   
 When used with the [OPENROWSET](../../t-sql/functions/openrowset-transact-sql.md) bulk rowset provider to import data into a table, TABLOCK enables multiple clients to concurrently load data into the target table with optimized logging and locking. For more information, see [Prerequisites for Minimal Logging in Bulk Import](../../relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import.md).  
   
diff --git a/docs/t-sql/statements/insert-transact-sql.md b/docs/t-sql/statements/insert-transact-sql.md
@@ -1,7 +1,7 @@
 ---
 title: "INSERT (Transact-SQL) | Microsoft Docs"
 ms.custom: ""
-ms.date: "08/10/2017"
+ms.date: "04/21/2020"
 ms.prod: sql
 ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw"
 ms.reviewer: ""
@@ -297,37 +297,43 @@ For information specific to inserting data into SQL graph tables, see [INSERT (S
   
 ### Best Practices for Bulk Importing Data  
   
-#### Using INSERT INTO...SELECT to Bulk Import Data with Minimal Logging  
- You can use `INSERT INTO <target_table> SELECT <columns> FROM <source_table>` to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.  
-  
- Minimal logging for this statement has the following requirements:  
+#### Using INSERT INTO...SELECT to Bulk Import data with minimal logging and parallelism 
+You can use `INSERT INTO <target_table> SELECT <columns> FROM <source_table>` to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.  
   
+Minimal logging for this statement has the following requirements:  
 -   The recovery model of the database is set to simple or bulk-logged.  
-  
--   The target table is an empty or nonempty heap.  
-  
+-   The target table is an empty or non-empty heap.  
 -   The target table is not used in replication.  
-  
--   The TABLOCK hint is specified for the target table.  
+-   The `TABLOCK` hint is specified for the target table.  
   
 Rows that are inserted into a heap as the result of an insert action in a MERGE statement may also be minimally logged.  
   
- Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO...SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. This means that you cannot insert rows using parallel insert operations.  
+Unlike the `BULK INSERT` statement, which holds a less restrictive Bulk Update (BU) lock, `INSERT INTO … SELECT` with the `TABLOCK` hint holds an exclusive (X) lock on the table. This means that you cannot insert rows using multiple insert operations executing simultaneously. 
+
+However, starting with [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] and database compatibility level 130, a single `INSERT INTO … SELECT` statement can be executed in parallel when inserting into heaps or clustered columnstore indexes (CCI). Parallel inserts are possible when using the `TABLOCK` hint.  
+
+Parallelism for the statement above has the following requirements, which are similar to the requirements for minimal logging:  
+-   The target table is an empty or non-empty heap.  
+-   The target table has a clustered columnstore index (CCI) but no non-clustered indexes.  
+-   The target table cannot have an identity column specified.
+-   The `TABLOCK` hint is specified for the target table.
+
+For scenarios where requirements for minimal logging and parallel insert are met, both improvements will work together to ensure maximum throughput of your data load operations.
+
+> [!NOTE]
+> Inserts into local temporary tables (identified by the # prefix) and global temporary tables (identified by ## prefixes) are also enabled for parallelism using the TABLOCK hint.
   
-#### Using OPENROWSET and BULK to Bulk Import Data  
+#### Using OPENROWSET and BULK to Bulk Import data  
  The OPENROWSET function can accept the following table hints, which provide bulk-load optimizations with the INSERT statement:  
   
--   The TABLOCK hint can minimize the number of log records for the insert operation. The recovery model of the database must be set to simple or bulk-logged and the target table cannot be used in replication. For more information, see [Prerequisites for Minimal Logging in Bulk Import](../../relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import.md).  
-  
--   The IGNORE_CONSTRAINTS hint can temporarily disable FOREIGN KEY and CHECK constraint checking.  
-  
--   The IGNORE_TRIGGERS hint can temporarily disable trigger execution.  
+-   The `TABLOCK` hint can minimize the number of log records for the insert operation. The recovery model of the database must be set to simple or bulk-logged and the target table cannot be used in replication. For more information, see [Prerequisites for Minimal Logging in Bulk Import](../../relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import.md).  
+-   The `TABLOCK` hint can enable parallel insert operations. The target table is a heap or clustered columnstore index (CCI) with no non-clustered indexes, and the target table cannot have an identity column specified.  
+-   The `IGNORE_CONSTRAINTS` hint can temporarily disable FOREIGN KEY and CHECK constraint checking.  
+-   The `IGNORE_TRIGGERS` hint can temporarily disable trigger execution.  
+-   The `KEEPDEFAULTS` hint allows the insertion of a table column's default value, if any, instead of NULL when the data record lacks a value for the column.  
+-   The `KEEPIDENTITY` hint allows the identity values in the imported data file to be used for the identity column in the target table.  
   
--   The KEEPDEFAULTS hint allows the insertion of a table column's default value, if any, instead of NULL when the data record lacks a value for the column.  
-  
--   The KEEPIDENTITY hint allows the identity values in the imported data file to be used for the identity column in the target table.  
-  
-These optimizations are similar to those available with the BULK INSERT command. For more information, see [Table Hints &#40;Transact-SQL&#41;](../../t-sql/queries/hints-transact-sql-table.md).  
+These optimizations are similar to those available with the `BULK INSERT` command. For more information, see [Table Hints &#40;Transact-SQL&#41;](../../t-sql/queries/hints-transact-sql-table.md).  
   
 ## Data Types  
  When you insert rows, consider the following data type behavior:  
@@ -379,7 +385,7 @@ These optimizations are similar to those available with the BULK INSERT command.
  When an INSERT statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the [!INCLUDE[ssDE](../../includes/ssde-md.md)] handles these errors as if SET ARITHABORT is set to ON. The batch is stopped, and an error message is returned. During expression evaluation when SET ARITHABORT and SET ANSI_WARNINGS are OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.  
   
 ## Interoperability  
- When an INSTEAD OF trigger is defined on INSERT actions against a table or view, the trigger executes instead of the INSERT statement. For more information about INSTEAD OF triggers, see [CREATE TRIGGER &#40;Transact-SQL&#41;](../../t-sql/statements/create-trigger-transact-sql.md).  
+ When an `INSTEAD OF` trigger is defined on INSERT actions against a table or view, the trigger executes instead of the INSERT statement. For more information about `INSTEAD OF` triggers, see [CREATE TRIGGER &#40;Transact-SQL&#41;](../../t-sql/statements/create-trigger-transact-sql.md).  
   
 ## Limitations and Restrictions  
  When you insert values into remote tables and not all values for all columns are specified, you must identify the columns to which the specified values are to be inserted.  
@@ -511,7 +517,6 @@ INSERT INTO T1 DEFAULT VALUES;
 GO  
 SELECT column_1, column_2  
 FROM dbo.T1;  
-  
 ```  
   
 #### G. Inserting data into user-defined type columns  
