Hotspot Question You have an on-premises Microsoft SQL Server instance named SQLSVR1 that hosts a database named DB1. You have an Azure subscription that contains an Azure SQL database named SQLDB1…

Question

Exhibit

Answer Area

• Option:ROWS_PER_BATCH
  CHECK_CONSTRAINTSFIRE_TRIGGERSROWS_PER_BATCH
• Parameter:IGNORE_DUP_KEY
  IGNORE_DUP_KEYUNIQUEWHERE

Explanation

This question tests knowledge of specific options for BULK INSERT for efficient data loading and key parameters for CREATE INDEX to define index properties in SQL Server.

Approach. The question asks to identify an option for BULK INSERT and a parameter for CREATE INDEX based on specific requirements.

1. For BULK INSERT (Option): The requirement is to 'Add data to DB1 at regular intervals by using BULK INSERT.' When performing bulk inserts at regular intervals, managing the transaction size and resource consumption is crucial. The ROWS_PER_BATCH option in BULK INSERT specifies the approximate number of rows in the data file that form a batch. Each batch is copied to the server as one transaction, which is committed when the batch completes. This option directly relates to handling data loads in manageable chunks over intervals, making it the most appropriate choice for the given scenario.

2. For CREATE INDEX (Parameter): The requirement is to 'Configure an index in DB1 by using CREATE INDEX.' The UNIQUE parameter (or keyword) is a fundamental part of the CREATE INDEX statement used to define a unique index, ensuring that no two rows have the same index key value. This is a core property or 'parameter' that defines the very nature of the index being created, making it a primary configuration choice when 'configuring' an index.

Common mistakes.

• common_mistake. 1. For BULK INSERT:
  • CHECK_CONSTRAINTS: This option checks constraints during the bulk import. While important for data integrity, it doesn't directly address the aspect of 'adding data at regular intervals' in terms of load management or batching.
  • FIRE_TRIGGERS: This option controls whether insert triggers fire during the bulk import. Similar to CHECK_CONSTRAINTS, it's an important control but not the most direct option for handling data additions in 'regular intervals' or batches. The core concept for 'regular intervals' is breaking down the load, which ROWS_PER_BATCH achieves.

1. For CREATE INDEX:
   • IGNORE_DUP_KEY: This is an option that can be specified with a UNIQUE index (or a PRIMARY KEY or UNIQUE constraint) to control error handling when duplicate key values are inserted. It doesn't define the index as unique itself; UNIQUE does that. The question asks for a 'parameter' for CREATE INDEX, and UNIQUE is a more fundamental parameter defining the index type.
   • WHERE: This clause is used for creating filtered indexes, allowing you to index only a subset of rows that satisfy a predicate. While a valid part of CREATE INDEX, it defines a filtering condition rather than a fundamental property like uniqueness. UNIQUE is a more general and common 'parameter' for configuring an index's core behavior.

Concept tested. This question tests the candidate's understanding of Transact-SQL (T-SQL) syntax and options for BULK INSERT and CREATE INDEX statements in SQL Server, specifically focusing on their practical application for efficient data loading and index configuration and performance optimization.

Reference. https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql

No community discussion yet for this question.