Which types of stored procedures should you alter?
You need to alter stored procedures to use the WITH RECOMPILE option. Which types of stored procedures should you alter? (Each correct answer represents a complete solution. Choose two.)
Which tasks should you complete?
You are creating a new table in a database. Your business requires you to store data in the table for only seven days.
You need to implement a partitioned table to meet this business requirement.
Which tasks should you complete?
Which method should you use?
You have two partitioned tables named Transaction and TransactionHistory.
You need to archive one of the partitions of the Transaction table to the TransactionHistory table.
Which method should you use?
Which data type should you use?
You currently store date information in two columns. One column contains the date in local time and one column contains the difference between local time and UTC time. You need to store this data in a single column.
Which data type should you use?
Which data type is compatible with the SPARSE option?
You have multiple tables that represent properties of the same kind of entities. The property values are comprised of text, geometry, varchar(max), and user-defined types specified as ‘bit NOT NULL’ data types.
You plan to consolidate the data from multiple tables into a single table. The table will use semi-structured storage by taking advantage of the SPARSE option.
You are tasked to identify the data types that are compatible with the SPARSE option.
Which data type is compatible with the SPARSE option?
Which collation should you select?
You plan to add a new column named SmallKey to the Sales.Product table that will be used in a
unique constraint. You are required to ensure that the following information is applied when adding the
new column:
‘a1’ and ‘A1’ are treated as different values
‘a’ and ‘A’ sort before ‘b’ and ‘B’ in an ORDER BY clause You need to select the collation that meets the requirements for the new column. Which collation should you select?
Which data type should you use?
You are creating a table that stores the GPS location of customers.
You need to ensure that the table allows you to identify customers within a specified sales boundary and to calculate the distance between a customer and the nearest store.
Which data type should you use?
Which method should you use?
You are developing a new database. The database contains two tables named SalesOrderDetail and Product.
You need to ensure that all products referenced in the SalesOrderDetail table have a corresponding record in the Product table.
Which method should you use?
Which Transact-SQL statement should you use?
You have a SQL Server 2008 database named Contoso with a table named Invoice. The primary key of the table is InvoiceId, and it is populated by using the identity property. The Invoice table is related to the InvoiceLineItem table. You remove all constraints from the Invoice table during a data load to increase load speed. You notice that while the constraints were removed, a row with InvoiceId = 10 was removed from the database. You need to re-insert the row into the Invoice table with the same InvoiceId value. Which Transact-SQL statement should you use?
Which Transact-SQL statement should you use?
You have a table named AccountsReceivable. The table has no indexes. There are 75,000 rows in the
table. You have a partition function named FG_AccountData. The AccountsReceivable table is defined in
the following Transact-SQL statement:
CREATE TABLE AccountsReceivable (
column_a INT NOT NULL,
column_b VARCHAR(20) NULL)
ON [PRIMARY];
You need to move the AccountsReceivable table from the PRIMARY file group to FG_AccountData. Which Transact-SQL statement should you use?