Ensurepass

QUESTION 11

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?

 A. geometry B. geography C. nvarchar(max) D. varbinary(max) FILESTREAM

Explanation:

The GEOGRAPHY data type is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and polygons on the earth’s surface. For example, GPS data that represents the lay of the land is one example of data that can be stored in the GEOGRAPHY data type.

QUESTION 12

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?

 A. Latin1_General_BIN B. SQL_Latin1_General_CP1_CI_AI C. SQL_Latin1_General_CP1_CI_AS D. SQL_Latin1_General_CP1_CS_AS

Explanation:

SQL Server Collation Name consists of several parts, one of them is responsible for CaseSensitivity – CI specifies case-insensitive, CS specifies case-sensitive.

BIN specifies the binary sort order to be used.

So, because we want case-sensitive location, B and C are not suitable. Latin1_General_BIN use binary sort order, but we want linguistical sort order (according to rules of language), not based on the code point values of characters.

QUESTION 13

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?

 A. text B. geometry C. varchar(max) D. A user-defined type defined as ‘bit NOT NULL’

Explanation:

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.

The following data types cannot be specified as SPARSE:

QUESTION 14

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?

 A. time B. datetime2 C. datetime2(5) D. datetimeoffset

Explanation:

datetimeoffset defines a date that is combined with a time of a day that has time zone awareness.

QUESTION 15

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?

 A. ALTER TABLE … SWITCH … B. INSERT … SELECT …; TRUNCATE TABLE C. ALTER PARTITION FUNCTION … MERGE … D. ALTER PARTITION FUNCTION … SPLIT …

QUESTION 16

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?

 A. Create the partition function Create the partition scheme Create the table B. Create the partition function Create the table Create a filtered index C. Add a secondary file to the primary filegroups Create the table Create the distributed partitioned view D. Create the partition function Create the partition scheme Create the distributed partitioned view

QUESTION 17

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

 A. Stored procedures implemented from CLR assemblies. B. Stored procedures that require the FOR REPLICATION option. C. Stored procedures that require the WITH ENCRYPTION option. D. Stored procedures that contain queries that use the OPTION (RECOMPILE) hint.

Explanation:

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes.

SQL Server provides three ways to force a stored procedure to recompile:

The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run.

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed.

You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure.

A.CREATE and ALTER PROCEDURE syntax for CLR Stored Procedure does not have RECOMPILE option.

B.The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

C.ENCRYPTION option and RECOMPILE option can go together.

QUESTION 18

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?

 A. Marketing.GetSalesSummary should be created by using the EXECUTE AS ‘SalesUser’ clause. John should be granted EXECUTE permission on Marketing.GetSalesSummary. B. Marketing.GetSalesSummary should be created by using the EXECUTE AS OWNER clause. John should be granted EXECUTE WITH GRANT OPTION on Marketing.GetSalesSummary. C. Marketing.GetSalesSummary should be created by using the EXECUTE AS CALLER clause. John should be granted IMPERSONATE permission for the user named SalesUser. D. Marketing.GetSalesSummary should be created without an EXECUTE AS clause. John should be granted SELECT permission on the Sales.Orders table.

Explanation:

1. When the module is executed, the Database Engine first verifies that the user executing the module has EXECUTE permission on the module. So John should be granted EXECUTE permission on Marketing.

GetSalesSummary stored procedure.

2. Additional permissions checks on objects that are accessed by the module are performed against the user account specified in the EXECUTE AS clause. The user executing the module is, in effect, impersonating the specified user. Because John is not granted a SELECT permission on the Sales.Orders table which is referenced by the stored procedure, EXECUTE AS CALLER is not suitable. (CALLER specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.) Because the user named SalesUser DOES have SELECT permission on the Sales.Orders table, he can be specified in EXECUTE AS clause. It means that Marketing.

GetSalesSummary stored procedure should be created by using the EXECUTE AS ‘SalesUser’ clause.

QUESTION 19

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

Explanation:

To create and use table-valued parameters, follow these steps:

1. Create a table type and define the table structure.

/* Create a table type. */

CREATE TYPE LocationTableType AS TABLE

( LocationName VARCHAR(50)

, CostRate INT );

GO

2. Declare a routine that has a parameter of the table type.

/* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE usp_InsertProductionLocation

AS

SET NOCOUNT ON

([Name]

,[CostRate]

,[Availability]

,[ModifiedDate])

SELECT *, 0, GETDATE()

FROM @TVP;

GO

3. Declare a variable of the table type, and reference the table type.

/* Declare a variable that references the type. */ DECLARE @LocationTVP

AS LocationTableType;

4. Fill the table variable by using an INSERT statement.

/* Add data to the table variable. */

INSERT INTO @LocationTVP (LocationName, CostRate)

SELECT [Name], 0.00

FROM

5. After the table variable is created and filled, you can pass the variable to a routine.

/* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP;

Restrictions

Table-valued parameters must be passed as input READONLY parameters to Transact- SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

QUESTION 20

You have a computed column that is implemented with a user-defined function. The user- defined 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 user-defined function. Which combination should you use?

 A. IsDeterministic = True IsSystemVerified = True UserDataAccess = False SystemDataAccess = False B. IsDeterministic = True IsSystemVerified = True IsPrecise = True IsTableFunction = True C. IsDeterministic = False IsSystemVerified = True UserDataAccess = False SystemDataAccess = False D. IsDeterministic = False IsSystemVerified = True IsPrecise = True SystemDataAccess = False