You have a database named WideWorldImporters that contains the following tables: TableA and TableB. Both tables are legacy tables. TableA contains a column that uses the ntext data type. TableB contains a column that uses the nvarchar(max) data type

HOTSPOT

You have a database named WideWorldImporters that contains the following tables: TableA and TableB. Both tables are legacy tables. TableA contains a column that uses the ntext data type. TableB contains a column that uses the nvarchar(max) data type.

The indexes in each table have become highly fragmented. You must reduce the fragmentation in both tables. You must permit concurrent user queries during defragmentation, even if the queries take longer to complete than usual.

When possible, the REBUILD option is preferred. Table statistics must be updated at the end of this maintenance.

In the table below, identify the strategy that must be used for each table.

NOTE: Make only one selection in each column. Each correct selection is worth one point.

View Answer

Answer:

Explanation:

TableA: REBUILD … WITH (ONLINE =OFF) The data types image, ntext, and text can be rebuild offline only.

TableB: REBUILD … WITH (ONLINE =ON) You CAN do online index rebuilds of varchar(max), nvarchar(max), and varbinary(max) data types.

Note: When an index is created or rebuilt in SQL Server, statistics are created or updated by scanning all the rows in the table.

However, starting with SQL Server 2012 (11.x), statistics are not created or updated by scanning all the rows in the table when a partitioned index is created or rebuilt. Instead, the query optimizer uses the default sampling algorithm to generate these statistics. To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes