Which Transact-SQL query should you use?
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as
shown in the exhibit. (Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row having the CustomerId value
set to 1 in the following XML format.
Which Transact-SQL query should you use?
You need to minimize disk space usage without altering the data types in the tables of the database
You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain three tables. The structure of the three tables is as shown
in the following table. You need to minimize disk space usage without altering the data
types in the tables of the database. What should you do?
Which Transact-SQL statement should you use?
You develop a Microsoft SQL Server 2012 server database that supports an application.
The application contains a table that has the following definition:
CREATE TABLE Inventory (
ItemID int NOT NULL PRIMARY KEY,
ItemsInStore int NOT NULL,
ItemsInWarehouse int NOT NULL)
You need to create a computed column that returns the sum total of the ItemsInStore and
ItemsInWarehouse values for each row.
The new column is expected to be queried heavily, and you need to be able to index the column.
Which Transact-SQL statement should you use?
What should you recommend?
You are designing a database that will be used for reporting purposes. You need to
minimize the data storage requirements and improve the application response time. What
should you recommend?
Which code fragment should you use?
You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a table that has a column defined as a smallint data type. The
table is partitioned on the basis of the smallint column. The partition function, named
MyRangePF1, is a range-right function that has boundaries of 100 and 1,000. The table
must be altered to contain the following partitions: ·< 100 ·>= 100 and < 400 ·>= 400 and <
700 ·>= 700 and < 1000 ·>= 1000 You need to alter the partition function to provide the
required partitions. Which code fragment should you use?
Which Transact-SQL query should you use?
You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The
Customers table has the following definition:
You need to create an audit record only when either the MobileNumber or HomeNumber column is
updated.
Which Transact-SQL query should you use?
What should you implement?
You are designing a table that will store transactions for an online retailer. You anticipate
that the online retailer will have an average of 500,000 transactions per day. You need to
design a solution for data aging and archiving. The solution must minimize the amount of
time it takes to return query results for active data. What should you implement?
Which Transact-SQL statement should you use?
You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and
LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type.
You use a third table named Transactions that has columns named TransactionId AccountNumber,
Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the
records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted.
Which Transact-SQL statement should you use?
What should you recommend?
You have a database that has 20 large tables. All the tables have qualified indexes. As the
tables grow, you discover that queries that contain JOIN statements execute more slowly.
You need to recommend a solution to decrease the query response time and the I/O. The
solution must minimize hardware costs. What should you recommend?
You need to improve the performance of the reports
You develop a Microsoft SQL Server 2012 database. You create a view that performs the following
tasks:
Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports.
You need to improve the performance of the reports.
What should you do?