Microsoft
70-467 · Question #93
70-467 Question #93: Real Exam Question with Answer & Explanation
The correct answer is E: ALTER TABLE...SWITCH. ALTER TABLE...SWITCH is the correct T-SQL statement for partition switching, allowing bulk data movement from a staging table into a partitioned fact table as a near-instantaneous metadata operation.
Question
You need to select a method of moving data from the staging tables to the factOrders table. What type of Transact-SQL (T-SQL) statement should you use?
Options
- AINSERT INTO...SELECT
- BSELECT...INTO
- CALTER PARTITION-SWITCH
- DALTER PARTITION FUNCTION
- EALTER TABLE...SWITCH
Explanation
ALTER TABLE...SWITCH is the correct T-SQL statement for partition switching, allowing bulk data movement from a staging table into a partitioned fact table as a near-instantaneous metadata operation.
Common mistakes.
- A. INSERT INTO...SELECT physically copies rows one at a time within a transaction, which is slow for large fact table loads and does not leverage partition architecture.
- B. SELECT...INTO creates a brand-new table from a query result and cannot insert data into an existing fact table.
- C. ALTER PARTITION-SWITCH is not valid T-SQL syntax; the correct statement for a partition switch is ALTER TABLE...SWITCH.
- D. ALTER PARTITION FUNCTION adds or removes a boundary point to change the number of partitions in a partition function, and does not move data between tables.
Concept tested. Partition switching for fast fact table loading
Reference. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql
Community Discussion
No community discussion yet for this question.