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?

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?

You have multiple tables that represent properties of the same kind of entities. The property values
are comprised of text, geometry, varchar(max), and userdefined
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?

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?

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?

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?

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.)

You have a SQL Server database. The database contains two schemas named Marketing and Sales.
The Marketing schema is owned by a user named MarketingManager. The Sales schema is owned by
a user named SalesManager.
A user named John must be able to access the Sales.Orders table by using a stored procedure named
Marketing.GetSalesSummary. John is not granted a SELECT permission on the Sales.Orders table. A
user named SalesUser does have SELECT permission on the Sales.Orders table. You need to
implement
appropriate permissions for John and the stored procedure Marketing.GetSalesSummary.
What should you do?

You need to create a stored procedure that accepts a tablevalued
parameter named @Customers.
Which code segment should you use?

You have a computed column that is implemented with a userdefined
function. The userdefined
function returns a formatted account number. The column must be indexed to provide adequate
search
performance.
You plan to create an index on the computed column. You need to identify the valid combination of
ObjectPropertyEX values for the userdefined
function.
Which combination should you use?