Which Transact-SQL query should you use?

Your database contains a table named Purchases. The table includes a DATETIME column namedPurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on thePurchaseTime column. The business team wants a report that displays the total number of purchases made onthe current day. You need to write a […]

Read More

Which table hint should you use?

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical.You write the following Transact-SQL query:INSERT INTO OrdersHistoricalSELECT * FROM CompletedOrdersYou need to optimize transaction logging and locking for the statement.Which table hint should you use? A. HOLDLOCK B. ROWLOCK C. XLOCK D. UPDLOCK E. TABLOCK Explanation:When importing data into a heap […]

Read More

Which Transact-SQL statement should you use?

You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains aschema named Customers that has a table named Regions. A user named UserA is a member of a role namedSales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Selectpermission on the Customers […]

Read More

You need to create an object that meets the following r

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object thatmeets the following requirements:Takes an input variableReturns a table of valuesCannot be referenced within a viewWhich object should you use? A. Scalar-valued function B. Inline function C. User-defined data type D. Stored procedure Explanation:Stored procedures accept input parameters […]

Read More

Which Transact-SQL query do you use?

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The databasecontains two tables that have the following definitions: Global customers place orders from several countries. You need to view the country from which each customerhas placed the most orders.Which Transact-SQL query do you use? A. SELECT c.CustomerID, c.CustomerName, […]

Read More

You need to improve the performance of the query

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader andSalesOrderDetail. The indexes on the tables are as shown in the exhibit.(Click the Exhibit button.) You write the following Transact-SQL query: You discover that the performance of the query is slow. Analysis of the query plan shows table scans where theestimated […]

Read More

You need to implement a computed column that references

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computedcolumn that references a lookup table by using an INNER JOIN against another table.What should you do? A. Reference a user-defined function within the computed column. B. Create a BEFORE trigger that maintains the state of the computed column. […]

Read More

Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in theexhibit. (Click the Exhibit button.) You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 inthe following XML format. Which Transact-SQL query should you use? A. SELECT OrderId, OrderDate, […]

Read More

Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in theexhibit. (Click the Exhibit button.) You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 inthe following XML format. Which Transact-SQL query should you use? A. SELECT OrderId, OrderDate, […]

Read More

Which Transact- SQL statement should you use?

You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that willstore Customer data from different sources. The table will include a column that contains the CustomerID fromthe source system and a column that contains the SourceID. A sample of this data is as shown in the followingtable. […]

Read More