What should you do?
You have a table named Person that contains a nvarchar column named Surname. The Person table currently has a clustered index on PersonID. The Surname column contains Russian and Japanese characters.
The following code segment will be used to search by Surname.
IF @lang =’Russian’
SELECT PersonID, Surname
FROM Person WHERE Surname = @SearchName COLLATE Cyrillic_General_CI_AS
if @lang = ‘Japanese’
SELECT PersonID, Surname FROM Person WHERE Surname = @SearchName COLLATE Japanese_CI_AS_KS
You need to enable SQL Server to perform an index seek for these queries. What should you do?
Which collation will be assigned?
You are working with a SQL Server 2008 instance that is configured to use the Latin1_General_CS_AS collation. You create a database by using the following statements.
CREATE DATABASE TestDB COLLATE Estonian_CS_AS;
GO
USE TestDB;
GO
CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar );
You implement a temporary table named #TestTempTab that uses the following code.
use TestDB;
GO
CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar );
INSERT INTO #TestTempTab
SELECT * FROM TestPermTab;
You need to identify which collation will be assigned to #TestTempTab. Which collation will be assigned?
What query hint should you use?
You have been tasked to write a query to select one million rows.
You need to optimize the query to return the first 50 rows as quickly as possible.
What query hint should you use?
Which query hint should you use?
You notice that for a particular set of parameter values the following query sometimes executes
quickly and other times executes slowly. You also notice that 90 percent of the rows in the Address table contain the same value for the city.
SELECT AddressId, AddressLine1, City, PostalCode
FROM Person.Address
WHERE City = @city_name
AND PostalCode = @postal_code
You need to use a query hint that, for the particular set of parameter values, will result in a more consistent query execution time. Which query hint should you use?
Which option should you use?
You have a database server that has four quad-core processors. This database server executes complex queries that are used to generate reports.
You need to force a query to use only one processor core without affecting other queries.
Which option should you use?
Which Transact-SQL clause should you insert?
You work for an international charity organization. You are writing a query to list the highest 100 different amounts that were donated. You have written the following code segment (Line numbers are included for reference only):
01 SELECT *
02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven,
03 ………………..
04 FROM Customer
05 JOIN SalesOrder
06 ON Customer.CustomerID = SalesOrder.CustomerID
07 GROUP BY Customer.CustomerID) AS DonationsToFilter
08 WHERE FilterCriteria <= 100
You need to insert a Transact-SQL clause in line 03 to complete the query. Which Transact-SQL clause should you insert?
Which Transact-SQL code segments should you use?
You have a table named ProductCounts that contains 1000 products as well as the number of units that have been sold for each product. You need to write a query that displays the top 5% of products that have been sold most frequently.
Which Transact-SQL code segments should you use?
Which Transact-SQL statement should you use?
You need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Which Transact-SQL statement should you use?
Which Transact-SQL statement should you use?
You need to write a query that allows you to rank total sales for each salesperson into four groups, where the top 25 percent of results are in group 1, the next 25 percent are in group 2, the next 25 percent are in group 3, and the lowest 25 percent are in group 4.
Which Transact-SQL statement should you use?
Which feature should you use?
You have two views named Sales.SalesSummaryOverall and Sales.CustomerAndSalesSummary.
They are defined as follows:
CREATE VIEW Sales.SalesSummaryOverall
AS
SELECT CustomerId, SUM(SalesTotal) AS OverallTotal
FROM Sales.SalesOrder
GROUP BY CustomerId
GO
CREATE VIEW Sales.CustomerAndSalesSummary
AS
SELECT Customer.Name, SalesSummaryOverall.OverallTotal, (SELECT AVG(OverallTotal)
FROM Sales.SalesSummaryOverall
WHERE SalesSummaryOverall.CustomerId = Customer.CustomerId) AS avgOverallTotal,
(SELECT MAX(OverallTotal) FROM Sales.SalesSummaryOverall
WHERE SalesSummaryOverall.CustomerId =Customer.CustomerId) AS maxOverallTotal,
FROM Sales.Customer
LEFT OUTER JOIN Sales. Sales.SalesSummaryOverall
ON SalesSummaryByYear.CustomerId = Customer.CustomerId
GO
You have been tasked to modify the Sales.CustomerAndSalesSummary view to remove references to other views.
You need to identify a feature to use in the modified version of the Sales.CustomerAndSalesSummary object to achieve the task.
Which feature should you use?