ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 97 discussion

Actual exam question from Microsoft's 70-762
Question #: 97
Topic #: 1
[All 70-762 Questions]

You run the following Transact-SQL following statement:

Customer records may be inserted individually or in bulk from an application.
You observe that the application attempts to insert duplicate records.
You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.
Which Transact-SQL statement should you run?

  • A. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = OFF)
  • B. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY = ON)
  • C. CREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY =OFF)
  • D. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)
  • E. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️
IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The
IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER
INDEX, or UPDATE. The default is OFF.
Incorrect Answers:
ONLINE = { ON | OFF } specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017
by JohnFan at March 18, 2020, 1:21 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
JohnFan
Highly Voted 5 years, 1 month ago
When IGNORE_DUP_KEY is OFF, the first duplicate encountered results in an error, and none of the new rows are inserted. When IGNORE_DUP_KEY is ON, inserted rows that would violate uniqueness are discarded. The remaining rows are successfully inserted. A warning message is emitted instead of an error: Duplicate key was ignored. https://sqlperformance.com/2019/04/sql-performance/ignore_dup_key-slower-clustered-indexes
upvoted 19 times
...
SoupDJ
Most Recent 4 years, 6 months ago
Clearly, C is the most plausible - but warning messages will be generated. Yet the requirements say "no notifications". Are notifications something different here?
upvoted 1 times
MarcusJB
4 years, 5 months ago
In C the index name consists of three parts (with spaces), which is not allowed. That makes the difference to the correct option B.
upvoted 1 times
Hoglet
4 years, 4 months ago
No, no, no. That will be a typo The important bit is IGNORE_DUP_KEY = ON vs IGNORE_DUP_KEY = OFF OFF is default. It need to be ON to meet the requirement https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-index-option-transact-sql?view=sql-server-ver15
upvoted 4 times
...
...
lh2607
4 years, 5 months ago
Don't answer if you're not sure. Answer is B.
upvoted 5 times
...
...
stm22
4 years, 9 months ago
yes B because "when IGNORE_DUP_KEY is ON inserted rows that would violate uniqueness are discarded. The remaining rows are successfully inserted". https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-index-option-transact-sql?view=sql-server-ver15: ON A warning message occurs when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint fail. OFF An error message occurs when duplicate key values are inserted into a unique index. The entire INSERT operation is rolled back. IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.
upvoted 4 times
...
gtc108
5 years, 1 month ago
"You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications." Therefore, the answer should be C NOT B.
upvoted 1 times
gtc108
5 years ago
Sorry, I'm wrong. The answer is B,
upvoted 3 times
...
...
JohnFan
5 years, 1 month ago
Different from bulk loading data into a rowstore table, you can load bulk amounts of data into a clustered columnstore index by using an INSERT...SELECT ... FROM <TableName> WITH (TABLOCK) statement.
upvoted 1 times
JohnFan
5 years, 1 month ago
if you directly bulk insert 102400 or more rows, the data goes directly into compressed rowgroups as rows are being added. To show what happens when you bulk load at least the minimum number of rows
upvoted 1 times
JohnFan
5 years, 1 month ago
Be aware that BULK INSERT operations can change a table’s metadata and cause transaction failures as a result. (This behavior does not occur when using the READ_COMMITTED_SNAPSHOT isolation level.)
upvoted 1 times
...
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago