You are a database administrator for AIOTestKing.com. A software developer in the company is running the following query against a SQL Server 2005 database. SELECT Surname FROM Employees WHERE UPPER(Surname) LIKE ‘COR%’ The software developer reports that the query runs quickly on a test database that has a small number of rows. However, the query runs very slowly on the production database that has millions of rows. The Surname column stores data in mixed case, by using case-sensitive collation. But the query needs to perform a case-insensitive search. You need to improve the performance of this query. However, you do not have permission to change the collation orders. And you cannot modify the application code that adds rows to the database. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
A.
Create an index on the Surname column.
B.
Modify the query to include the following Transact-SQL statement.
SELECT Surname FROM Employees WHERE Surname LIKE UPPER(‘cor%’)
C.
Execute the following Transact-SQL statement.
ALTER TABLE Employees ADD UpperSurname AS UPPER(Surname)
D.
Execute the following Transact-SQL statement.
ALTER TABLE Employees ADD UpperSurname AS CAST(Surname AS nvarchar)
E.
Create an index on the UpperSurname column.
F.
Modify the query to include the following Transact-SQL statement.
SELECT Surname FROM Employees WHERE UpperSurname LIKE ‘COR%’
Explanation:
As the database uses case-sensitive collation, the easiest way to speed up the query is to create a new column in the database that stores the surnames in uppercase, index the column you just created and the run the SELECT T-SQL query against the new column.