70-461 exam

Unlike the INNER JOIN or LEFT JOIN, the cross join does not establish a relationship between the joined tables.

True

When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement.

True

SQL Server supports two kinds of DML triggers

—after and instead of

What are some different ways to create an index?

• CREATE INDEX T-SQL statement. • Using SQL Server Management Studio, by browsing the table on which you need to create an index, right click on the Indexes node and choose New Index option. • Indirectly by defining the PRIMARY KEY and the UNIQUE constraint within the CREATE TABLE or ALTER TABLE statements.

To check the status of the trace flag

use DBCC TraceStatus(1222, -1)

To determine if a deterministic column in a view with schema binding is precise,

use the IsPrecise property of the COLUMN PROPERTY function.

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each. Performs aggregations on 5 fields. The view is frequently used in several reports. You need to improve the performance of the reports. What should you do?

Convert the view into an indexed view.

Note that if a TRY. . .CATCH block captures and handles no error, as far as the caller is concerned, there was no error.

False

SQL Server supports only instead DDL triggers; it doesn't support after of DDL triggers.

False

The FETCH clause is mandatory while the OFFSET clause is optional.

False

--to locate last record

Select * From Customer where CustomerID = (Select max(CustomerID) from Customer)

You can use triggers for many purposes, including auditing, enforcing integrity rules that cannot be enforced with constraints, and enforcing policies.

TRUE

The main tool used for error handling is a construct called

TRY. . .CATCH.

views Is Updatable

True

views can be expanded out by Optimiser

True

views can have triggers

True

Page-level locks, row-level locks, or a combination of page-level and row-level locks can be disallowed for a given index.

True

SQL Server supports only after DDL triggers; it doesn't support instead of DDL triggers.

True

The SQL Server Database Engine uses a dynamic locking strategy that automatically chooses the best locking granularity for queries in most cases.

True

The UNION operation is different from using joins that combine columns from two tables.

True

Note that unlike EXEC, sp_executesql supports only _____________ character strings as the input batch of code.

Unicode

A table-valued user-defined function can also replace

stored procedures that return a single result set

COALESCEQ Function

- Returns the first NON NULL value

What are the types of subquery?

1. Correlated: In a SQL database query, a correlated subquery is a subquery that uses values from the outer query in order to complete. Because a correlated subquery requires the outer query to be executed first, the correlated subquery must run once for every row in the outer query. It is also known as a synchronized subquery. 2. Non-Correlated: A Non-correlated subquery is a subquery in which both outer query and inner query are independent to each other.

When should we update Statistics on SQL Server Database and why

1. Query execution time is slow 2. Insert Operation took place on Asc and Desc 3. keys Maintenance operation took place

Database Normalization is used for following Purpose:

1. To Eliminate the redundant or useless data 2. To Reduce the complexity of the data 3. To Ensure the relationship between tables as well as data in the tables 4. To Ensure data dependencies and data is logically stored.

What are functional Based indexes?Explain with Example

1.Function based indexes allows us to index on the functional columns so that oracle engine will take the index and improves the performance of the query. 2.As per requirements we are using lot of SQL functions to fetch the results.Function based indexs gives ability to index the computed columns. 3.Function based indexes are easy to implement and it also provides immediate value.These indexes speeds up the application without changing application code or query. Example: Syntax: Create index indexname on tablename(Function_name(column_name)); Example: Create index FI_Employee on Employee(trunc(Hire_date));

What are disadvantages of Indexes?(

1.Indexes slows down the performance of insert and update statements.So always we need follow best practice of disabling indexes before insert and update the table 2.Indexes takes additional disk space so by considering memory point indexes are costly.

What are functions of Parser?

1.Syntax Analysis: The parser checks for SQL statement syntaxs.If the syntax is incorrect then parser gives the incorrect syntax error. 2.Semantic Analysis: This checks for references of object and object attributes referenced are correct.

There are 2 functions of parser:

1.Syntax analysis 2.Semantic analysis

What is mean by Clustered index?

1.The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database. 2.Clustered indexes are created once for each table.When primary key is created then clustered index has been automatically created in the table. 3.If table is under heavy data modifications the clustered indexes are preferable to use.

What is mean by non clustered indexes?(

1.The clustered indexes are used for searching purpose as we can create clustered indexes where primary is is defined.But Non clustered indexes are indexes which will be created on the multiple joining conditions,multiple filters used in query. 2.We can create 0 to 249 non-clustered indexes on single table.Foreign keys should be non clustered. 3.When user wants to retrieve heavy data from fields other than primary key the non clustered indexes are useful.

Which code will give an error? 1. SELECT DATEDIFF_BIG(MILLISECOND, '12/01/2015', '12/30/2015') AS 'Difference in MILLISECOND' 2. SELECT DATEDIFF(MILLISECOND, '12/01/2015' '12/30/2015') AS 'Difference in MILLISECOND'

2. SELECT DATEDIFF(MILLISECOND, '12/01/2015' '12/30/2015') AS 'Difference in MILLISECOND'

Employee and Manager ID are in the same table; can you get manager names for employees?

;with empCTE as ( select e.empid, e.empname, e.managerid, CAST('' as varchar(50)) as Mname from employee e where managerid = 0 union all select e1.empid, e1.empname, e1.managerid, CAST(c.empname as varchar(50)) as Mname from employee e1 inner join empCTE as C on e1.managerid=c.empid where e1.managerid>0 ) select * from empCTE

Write query to print numbers from 1 to 100 without using loops

;with numcte AS ( SELECT 1 [SEQUENCE] UNION ALL SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] <100 ) SELECT * FROM numcte

You specify the Unicode character string holding the batch of code you want to run in the first parameter, which is called _____________. You provide a Unicode character string holding the declarations of input and output parameters in the second input parameter, which is called ___________. Then you specify the assignments of input and output parameters separated by commas.

@stmt, @params

What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table?

A Heap table is a table in which, the data rows are not stored in any particular order within each data page. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. This is due to the fact that the heap table contains no clustered index. A clustered table is a table that has a predefined clustered index on one column or multiple columns of the table that defines the storing order of the rows within the data pages and the order of the pages within the table, based on the clustered index key. The heap table can be identified by querying the sys.partitions system object that has one row per each partition with index_id value equal to 0. You can also query the sys.indexes system object also to show the heap table index details, that shows, the id of that index is 0 and the type of it is HEAP.

What are transaction and its controls?

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions. In simple word, we can say that a transaction means a group of SQL queries executed on database records. There are 4 transaction controls such as COMMIT: It is used to save all changes made through the transaction ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before SET TRANSACTION: Set the name of transaction SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence. Which approach should you use?

A user-defined function

Why is an index described as a double-edged sword?

A well-designed index will enhance the performance of your system and speed up the data retrieval process. On the other hand, a badly-designed index will cause performance degradation on your system and will cost you extra disk space and delay in the data insertion and modification operations. It is better always to test the performance of the system before and after adding the index to the development environment, before adding it to the production environment.

What is the difference between an inner and outer join?

An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared. An outer join returns rows from both tables that include the records that are unmatched from one or both the tables.

Define COMMIT?

Ans. COMMIT saves all changes made by DML statements.

Define UNION, MINUS, UNION ALL, INTERSECT ?

Ans. MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates. INTERSECT - returns all distinct rows selected by both queries.

What are user defined functions?

As the name suggests these are written by users as per their requirement. User-defined functions are the functions written to use a logic whenever required.

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: Does not return an error Closes all opened transactions Which Transact-SQL statement should you use?

BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH

Show an example of how you re-throw the error.

BEGIN TRY INSERT INTO dbo.Employees(empid, empname, mgrid) VALUES(1, 'Emp1', NULL); END TRY BEGIN CATCH IF ERROR_NUMBER() IN (2627, 547, 515, 245) EXEC dbo.ErrInsertHandler; ELSE THROW; END CATCH;

Write a code to demonstrate a case with error in the TRY block.

BEGIN TRY PRINT 10/0; PRINT 'No error'; END TRY BEGIN CATCH PRINT 'Error'; END CATCH;

Write a code to demonstrate a case with no error in the TRY block.

BEGIN TRY PRINT 10/2; PRINT 'No error'; END TRY BEGIN CATCH PRINT 'Error'; END CATCH;

You create an all server trigger for events with a server scope, such as

CREATE DATABASE

CORRECT TEXT You have a view that was created by using the following code: CREATE VIEW SALes.OrdersByTerritory AS SELECT OrderID ,OrderDate ,SalesTerritoryID ,TotalDue FROM Sales.Orders; You need to create an inline table-valued function named Sales.fn_OrdersByTerritory, which must meet the following requirements: Accept the @T integer parameter. Use one-part names to reference columns. Filter the query results by SalesTerritoryID. Return the columns in the same order as the order used in OrdersByTerritoryView. Which code segment should you use? To answer, type the correct code in the answer area.

CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID, OrderDate, SalesTerrirotyID, TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T

You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

CREATE TABLE DocumentStore ( [Id] INT NOT NULL PRIMARY KEY, [Document] VARBINARY(MAX) NULL ) GO

CORRECT TEXT You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.) OrderDetails (Table) ListPrice (Column Name) money (Data Type) Quantity (Column Name) int (Data Type) Customers (Table) CustomerID (Column Name) int (Data Type) FirstName (Column Name) varchar (Data Type) LastName (Column Name) varchar (Data Type) Orders (Table) OrderID (Column Name) int (Data Type) OrderDate (Column Name) datetime (Data Type) CustomerID (Column Name) int (Data Type) You deploy a new server that has SQL Server 2012 installed. You need to create a table named Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements: Write the results to a disk. Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row. The code must NOT use any object delimiters. The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use? To answer, type the correct code in the answer area.

CREATE TABLE Sales.OrderDetails ( ListPrice money not null, Quantity int not null, LineItemTotal as (ListPrice * Quantity) PERSISTED)

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition: CREATE TABLE [dbo] . [Customers] ( [Customerld] [bigint ] NOT NULL, [MobileNumber] [nvarchar](25) NOT NULL, [HomeNumber] [nvarchar] (25) NULL, [Name] [nvarchar](50) NOT NULL, [Country] [nvarchar](25) NOT NULL, CONSTRAINT [PK_Customers ] PRIMARY KEY CLUSTERED ( [Customerld] ASC ) ON [PRIMARY] ) ON [PRIMARY] You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records

What is Collation?

Collation is defined as a set of rules that determine how character data can be sorted as well as compared. Character data is sorted using rules that define the correct character sequence along with options for specifying case-sensitivity, character width, accent marks, kana character types.

What is cost based optimizer?

Cost Based Optimizer (CBO) uses the artificial intelligence to execute the Query. The Optimizer itself decides the execution plan based on the cost of query. The cost based method means the database must decide which query execution plan to choose. It decides the query execution plan by deciding the statistical information based on the database objects.(tables, indexes and clusters).The Oracle cost based optimizer is designed to determine the most effective way to carry out the SQL statement.

You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. CREATE VIEW vOrders WITH SCHEMABINDING AS SELECT o.ProductID, o.OrderDate, SUM(od.UnitPrice * od.OrderQty) AS Amount FROM OrderDetails As od INNER JOIN Orders AS o ON od.OrderID = o.OrderID WHERE od.SalesOrderID = o.SalesOrderID GROUP BY o.OrderDate, o.ProductID Go You need to improve the performance of the view by persisting data to disk. What should you do?

Create a clustered index on the view.

You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement: CREATE TABLE [dbo].[SalesOrderHeader]( [SalesOrderlD] [int] IDENTITY(1,1) NOT NULL, [OrderDate] [datetime] NOT NULL, [Status] [tinyint] NOT NULL, [PurchaseOrderNumber] [nvarchar](25) NULL, [AccountNumber] [nvarchar](15) NULL, [CustomerlD] [int] NOT NULL, [TotalDue] [money] NOT NULL, CONSTRAINT [PK_SalesOrderHeader] PRIMARY KEY CLUSTERED ( [SalesOrderlD] ASC ) ON [PRIMARY] You need to ensure that purchase order numbers are used only for a single order. What should you do?

Create a new UNIQUE constraint on the PurchaseOrderNumber column.

Explain steps to perform Table Partitioning and what are the best practices to do so?

Create a partition Function Create a partition scheme Create a Partition of the table

You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers. The view has the following definition: Create view apt.vwProviderli3t (Specialty, CompanylC, CompanyNumber, LastName, FxrstName, BusinessName, Email, Phone) SELECT 'Dentist' as Specialty , DentistID , DentistNumber , DentistLastName , BentistFirstName , DentistBusinessName , Email , Phone FROM apt.Dentist UNION ALL SELECT 'Psychiatrist' as Specialty , PsychiatristID , PsychiatristNumber , PsychiatristLastName , PsychiatnstFirstName , PsychiatristBusinessName , Email , Phone SELECT 'Physician' as Specialty , PhysicianID , PhysicianNumber , PhysicianLastName , PhysicianFirstName , PhysicianBusinessName , Email , Phone FROM apt.Physician GO You need to ensure that users can update only the phone numbers and email addresses by using this view. What should you do?

Create an INSTEAD OF UPDATE trigger on the view.

What are the factors to consider for creating Index on Table? Also, How to select a column for Index?

Creation of index depends on the following factors. 1. Size of table, 2. Volume of data If Table size is large and we need a smaller report, then it's better to create Index. Regarding the column to be used for Index, as per the business rule, you should use a primary key or a unique key for creating a unique index.

What is Cross-Join?

Cross join produces a result set which is the number of rows in the first table multiplied by a number of rows in the second table if no WHERE clause is used along with Cross join. This kind of result is known as Cartesian Product. If suppose, Where clause is used in cross join then the query will work like an Inner join.

In blocking

Current process must end before the new one can begin.

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

Cursor

What are the different DCL commands in SQL?

DCL commands are used to create roles, grant permission and control access to the database objects. GRANT: To provide user access DENY: To deny permissions to users REVOKE: To remove user access

A ___________or ___________ trigger also support internal use of EVENTDATA.

DDL or logon

Can you write a query to print prime numbers from 1 to 100?

DECLARE @i INT, @a INT, @count INT, @result varchar(Max) SET @i = 1 set @result='' WHILE (@i <= 100) BEGIN SET @count = 0 SET @a = 1 -- logic to check prime number WHILE (@a <= @i) BEGIN IF (@i % @a = 0) SET @count = @count + 1 SET @a = @a + 1 END IF (@count = 2) set @result = @result+cast(@i as varchar(10))+' , ' SET @i = @i + 1 END set @result = (select substring(@result, 1, (len(@result) - 1))) print(@result)

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

DECLARE @ErrorVar INT; DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0) PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO

CORRECT TEXT You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema. Which code segment should you use? To answer, type the correct code in the answer area.

DECLARE @XML1 XML(Sales.InvoiceSchema)

The following example constructs a batch of code with a query against the Sales.Orders table. The example uses an input parameter called @orderid in the query's filter.

DECLARE @sql AS NVARCHAR(100); SET @sql = N'SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderid = @orderid;'; EXEC sp_executesql @stmt = @sql, @params = N'@orderid AS INT', @orderid = 10248;

What is Denormalization?

Denormalization is a database optimization technique used to increase the performance of a database infrastructure. It involves in the process of adding redundant data to one or more tables. In a normalized database, we store data in separate logical tables and attempt to minimize redundant data.

When an event notification fires, and the specified service broker receives the results, _____________ is called.

EVENTDATA

You can not create as many user databases as you need within an instance.

False:

How do you partition a table

First we create a partition function then we associate the partition function with a create partition scheme

Locking Hints: ROWLOCK

Force locks at the row level

You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

Grant the db_owner role on the database to User1. Grant the sysadmin role on the database to User1.

derived_table

Is any valid SELECT statement that returns rows of data to be loaded into the table. The SELECT statement cannot contain a common table expression (CTE).

What is the use of NVL function?

NVL function is used to convert the null value to its actual value.

What is the difference between clustered and non-clustered indexes?

One table can have only one clustered index but multiple nonclustered indexes. Clustered indexes can be read rapidly rather than non-clustered indexes. Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row

What is Optimizer?

Optimizer is nothing but the execution of query in optimum manner.Optimizer is most efficient way of processing the query.SQL parser ,SQL Optimizer and source code generator compiles the SQL statement.

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a tablevalued expression into multiple columns in the output. Which Transact-SQL operator should you use?

PIVOT

SET SHOWPLAN_XML (Transact-SQL) causes

SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document

Index and Parameter Hints: FORCESCAN

Scans index or table

--to calculate monthly income

Select lastName, YearlyIncome/12 As Monthly_Income From Customer;

NOEXPAND

Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index.

SET NOCOUNT

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

What are the different TCL commands in SQL?

TCL commands are used to manage the changes made by DML statements. COMMIT: To write and store the changes to the database ROLLBACK: To restore the database since the last commit

Index and Parameter Hints: OPTIMIZE FOR UNKNOWN

Tells SQL Server to create a query plan where the value for the parameter will change frequently

The following are basic rules for combining the result sets of two queries by using UNION:

The number and the order of the columns must be the same in all queries. The data types must be compatible.

views can use side-effecting operator

True

Deadlocks

Two processes are each waiting for a resource held by the other process.

Specifies that multiple result sets are to be combined and returned as a single result set.

UNION

What is visible/invisible property of index?

User can make the indexes visible and invisible by altering the indexes.Following statement is used to make indexes visible and invisible.

What is the information you get from the STATSPACK Report?

We can get the following statistics from the STATSPACK report. 1. WAIT notifiers 2. Load profile 3. Instance Efficiency Hit Ratio 4. Latch Waits 5. Top SQL 6. Instance Action 7. File I/O and Segment Stats 8. Memory allocation 9. Buffer Waits

What is the "Forwarding Pointers issue" and how can we fix it?

When a data modification operation is performed on heap table data pages, Forwarding Pointers will be inserted into the heap to point to the new location of the moved data. These forwarding pointers will cause performance issues over time due to visiting the old/original location vs the new location specified by the forwarding pointers to get a specific value. Starting from SQL Server version 2008, a new method was introduced to overcome the forwarding pointers performance issue, by using the ALTER TABLE REBUILD command, that will rebuild the heap table.

What is the difference between Having and Where clause?

Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with 'GROUP BY' to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

Can you get the list of employees with same salary?

With where clause Select distinct e.empid,e.empname,e.salary from employee e, employee e1 where e.salary =e1.salary and e.empid != e1.empid

In other words, the cross join returns

a Cartesian product of rows from both tables.

where as Group by: ROLLUP generates

a result set that shows aggregates for a hierarchy of values in the selected columns

The alternative INDEX = syntax specifies .

a single index value. Only one index hint per table can be specified

A trigger is

a special kind of stored procedure that automatically executes when an event occurs in the database server.

A GROUPING SETS expression

allows you to selectively specify the set of groups that you want to create within a GROUP BY clause.

MAXRECURSION

can be used to prevent a poorly formed recursive common table expression from entering into an infinite loop

For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON

can provide a significant performance boost, because network traffic is greatly reduced.

You can define a property called _____________________ at the database level that will determine language support, case sensitivity, and sort order for character data in that database.

collation

locks prevent

concurrent use by different transactions

If the TRY block has an error,

control is passed to the corresponding CATCH block.

True or False - It is possible to correlate the Performance Monitor metrics with Profiler data in a single SQL Server native product?

o True - This functionality is possible with SQL Server Profiler.

To get the list of error numbers and messages,

query the sys.messages catalog view.

The ERROR_SEVERITY and ERROR_STATE functions

return the error severity and state.

The OUTER APPLY form, on the other hand,

returns all rows from the outer table, even if the function produces no results.

The FETCH clause

specifies the number of rows to return after the OFFSET clause has been processed.

The OFFSET clause

specifies the number of rows to skip before starting to return rows from the query.

Monitoring Performance on locks, blocking and deadlocks use

sql server profiler

SQL Server provides two ways of executing dynamic SQL:

using the EXEC (short for EXECUTE) command, and using the sp_executesql stored procedure.

The granularity of locking used on an index can be set using

the CREATE INDEX and ALTER INDEX statements.

Table hints are specified in

the FROM clause of the DML statement and affect only the table or view referenced in that clause.

Note that you must use the OFFSET and FETCH clauses with

the ORDER BY clause.

What is a FOR XML clause?

the clause you can add to the end of a query so that it will return that queries recordset as an XML stream

When SET NOCOUNT is ON,

the count (indicating the number of rows affected by a Transact-SQL statement) is not returned.

When SET NOCOUNT is OFF,

the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.

On-premises SQL Server supports the creation of DDL triggers at two scopes,

the database scope and the server scope, depending on the scope of the event.

If multiple indexes are used in a single hint list,

the duplicates are ignored and the rest of the listed indexes are used to retrieve the rows of the table.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error,

the entire transaction is terminated and rolled back.

For backwards compatibility, the sp_indexoption system stored procedure can also set

the granularity.

The lock settings apply to both

the index pages and the table pages.

The INDEX() syntax specifies

the names or IDs of one or more indexes to be used by the query optimizer when it processes the statement.

The inserted table holds

the new image of the affected rows in the case of INSERT and UPDATE actions.

When an index hint referring to multiple indexes is used on the fact table in a star join,

the optimizer ignores the index hint and returns a warning message.

SET NOCOUNT ON prevents

the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

DBCC TRACEON (1222, -1) means

the trace flag 1222 at the global level

-1 parameter indicates that the trace flag must be set at the global level. If you omit -1 parameter

the trace flag will be set only at the session level

How many modes are there in XML clause and name them

there are 4 modes o RAW o AUTO o PATH o NODE

traces flags can be set at two levels

they can be set either at the global level or at a specific session level

The sp_executesql procedure has

two input parameters and an assignments section.

To turn off the trace flag

use DBCC Traceoff(1222, -1)

The model database is

used as a template for new databases. Every new database that you create is initially created as a copy of model. So if you want certain objects (such as data types) to appear in all new databases that you create, or certain database properties to be configured in a certain way in all new databases, you need to create those objects and configure those properties in the model database. Note that changes you apply to the model database will not affect existing databases—only new databases that you create in the future.

SQL Server supports three types of routines:

user-defined functions, stored procedures, and triggers.

specifying only table-level locks on a large table that users access heavily can cause bottlenecks because

users must wait for the table-level lock to be released before accessing the table.

How do you see all the locks in a server

using Activity monitor in older versions of sql server

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met: Future modifications to the table definition will not affect the applications' ability to access data. The new object can accommodate data retrieval and data modification. You need to achieve this goal by using the minimum amount of changes to the existing applications. What should you create for each application?

views

What are the two options to turn on traces flags

we can do that either way using sql server start up parameters or by using sql server dbcc commands

Occasionally a deadlock occurs

when two concurrent operations acquire row locks on the same table and then block because they both need to lock the page. Disallowing row locks forces one of the operations to wait, avoiding the deadlock.

The tempdb database is

where SQL Server stores temporary data such as work tables, sort space, row versioning information, and so on. SQL Server allows you to create temporary tables for your own use, and the physical location of those temporary tables is tempdb. Note that this database is destroyed and recreated as a copy of the model database every time you restart the instance of SQL Server.

The msdb database is

where a service called SQL Server Agent stores its data. SQL Server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts. The SQL Server Agent is also the service in charge of replication. The msdb database also holds information related to other SQL Server features such as Database Mail, Service Broker, backups, and more.

SQL Server 2012 offers xVelocity memory-optimized columnstore indexes,

which store data for every column in separate pages and internally use an algorithm derived by the xVelocity in-memory analytics engine (VertiPaq) used by Analysis Services.

SSAS: If you cannot add indexes and statistics in SQL Server to improve query performance,

you must change the DAX query to generate a more efficient SQL query.

What is the difference between PAD_INDEX and FILLFACTOR?

• FILLFACTOR isused to set the percentage of free space that the SQL Server Engine will leave in the leaf level of each index page during index creation. The FillFactor should be an integer value from 0 to 100, with 0 or 100 is the default value, in which the pages will be filled completely during the index creation. • PAD_INDEX is used to apply the free space percentage specified by FillFactor to the index intermediate level pages during index creation.

Explain Index Depth, Density and Selectivity factors and how these factors affect index performance?

• Index depth is the number of levels from the index root node to the leaf nodes. An index that is quite deep will suffer from performance degradation problem. In contrast, an index with a large number of nodes in each level can produce a very flat index structure. An index with only 3 to 4 levels is very common. • Index density is a measure of the lack of uniqueness of the data in a table. A dense column is one that has a high number of duplicates. • Index selectivity is a measure of how many rows scanned compared to the total number of rows. An index with high selectivity means a small number of rows scanned when related to the total number of rows.

Describe the characteristics ideal Clustered index keys.

• Short: Although SQL Server allows us to add up to 16 columns to the clustered index key, with a maximum key size of 900 bytes, the typical clustered index key is much smaller than what is allowed, with as few columns as possible. A wide Clustered index key will also affect all non-clustered indexes built over that clustered index, as the clustered index key will be used as a lookup key for all the non-clustered indexes pointing to it. • Static: It is recommended to choose columns that are not changed frequently in a clustered index key. Changing the clustered index key values means that the whole row will be moved to the new proper page to keep the data values in the correct order. • Increasing: Using an increasing (aka incrementing) column, such as the IDENTITY column, as a clustered index key will help in improving the INSERT process, that will directly insert the new values at the logical end of the table. This highly recommended choice will help in reducing the amount of memory required for the page buffers, minimize the need to split the page into two pages to fit the newly inserted values and the fragmentation occurrence, that required rebuilding or reorganizing the index again. • Unique: It is recommended to declare the clustered index key column or combination of columns as unique to improve query performance. Otherwise, SQL Server will automatically add a uniqueifier column to enforce the clustered index key uniqueness. • Accessed frequently: This is due to the fact that the rows will be stored in the clustered index in a sorted order based on that index key that is used to access the data. • Used in the ORDER BY clause: In this case, there no need for the SQL Server Engine to sort the data in order to display it, as the rows are already sorted based on the index key used in the ORDER BY clause.

How can you find the missing indexes that are needed to potentially improve the performance of your queries?

• The Missing Index Details option in the query execution plan, if available. • The sys.dm_db_missing_index_details dynamic management view, that returns detailed information about missing indexes, excluding spatial indexes, • A combination of the SQL Server Profiler and the Database Engine Tuning Advisor tools.

When checking the index usage statistics information, retrieved by querying the sys.dm_db_index_usage_stats dynamic management view, explain the results of the returned number of seeks, scans, lookups and updates.

• The number of Seeks indicates the number of times the index is used to find a specific row, • the number of Scans shows the number of times the leaf pages of the index are scanned, • the number of Lookups indicates the number of times a Clustered index is used by the Non-clustered index to fetch the full row • and the number of Updates shows the number of times the index data has modified.

What are the different ways that can be used to retrieve the properties of the columns participating in a SQL Server index?

• Using SSMS, by expanding the Indexes node under a database tabl, then right-clicking on each index, and choose the Properties option. The problem with gathering the indexes information using the UI method is that you need to browse it one index at a time per each table. You can imagine the effort required to see the article: all indexes in a specific database. • The sp_helpindex system stored procedure, by providing the name of the table that you need to list its indexes. In order to gather information about all indexes in a specific database, you need to execute the sp_helpindex number of time equal to the number of tables in your database. • The sys.indexes system dynamic management view. The sys.indexes contains one row per each index in the table or view. It is recommended to join sys.indexes DMV with other systems DMVs, such as the sys.index_columns, sys.columns and sys.tables in order to return meaningful information about these indexes.

How can we get the fragmentation percentage of a database index?

• Using SSMS, from the Fragmentation tab of the index Properties window. Checking the fragmentation percentage of all indexes in a specific database, using the UI method requires a big effort, as you need to check one index at a time. • The sys.dm_db_index_physical_stats dynamic management function, that was first Introduced in SQL Server 2005. The sys.dm_db_index_physical_stats DMF can be joined with the sys.indexes DMV to return the fragmentation percentage of all indexes under the specified database.

What is Bit-map index?Explain with Example.(90 % Asked in Performance Tuning Interview Questions)

1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes. 2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index. 3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values. 4.Means If in 1 million records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only. Syntax: Create bitmap index Index_name on Table_name(Columns which have distinct values); Example: CREATE BITMAP index BM_DEPT_NAME on DEPT(Department_name);

What are advantages of Indexes?

1.It allows faster retrieval of data 2.It avoids the Full table scan so that the performance of retrieving data from the table is faster. 3.It avoids the table access alltogether 4.Indexes always speeds up the select statement. 5.Indexes used to improve the Execution plan of the database

What are different types of indexes?

1.Normal Indexes 2.Bit map indexes 3.B-tree Indexes 4.Unique Indexes 5.Function Based Indexes

What are types of SQL Optimizer?

1.Rule Based Optimizer 2.Cost Based Optimizer

What is mean by Unique Indexes?

1.To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index. 2.Especially while creating the table if we specify the primary key then unique index is automatically created on that column. 3.But for Unique key constaint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only. 4.The unique indexes are also called as clustered indexes when primary key is defined on the column. Example: Create Unique index Index_name on Table_name(Unique column name); Example: CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);

What is the main difference between a Clustered and Non-Clustered index structure?

A Non-clustered index is different from a Clustered index in that, the underlying table rows will not be stored and sorted based on the Non-clustered key, and the leaf level nodes of the Non-clustered index are made of index pages instead of data pages. The index pages of the Non-clustered index contain Non-clustered index key values with pointers to the storage location of these rows in the underlying heap table or the Clustered index.

Describe the structure of a SQL Server Index that provides faster access to the table's data?

A SQL Server index is created using the shape of B-Tree structure, that is made up of 8K pages, with each page, in that structure, called an index node. The B-Tree structure provides the SQL Server Engine with a fast way to move through the table rows based on index key, that decides to navigate left or right, to retrieve the requested values directly, without scanning all the underlying table rows. You can imagine the potential performance degradation that may occur due to scanning large database table. The B-Tree structure of the index consists of three main levels: • the Root Level, the top node that contains a single index page, form which SQL Server starts its data search, • the Leaf Level, the bottom level of nodes that contains the data pages we are looking for, with the number of leaf pages depends on the amount of data stored in the index, • and finally the Intermediate Level, one or multiple levels between the root and the leaf levels that holds the index key values and pointers to the next intermediate level pages or the leaf data pages. The number of intermediate levels depends on the amount of data stored in the index.

You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: CREATE TABLE Inventory ( ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?

ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse

You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: CREATE TABLE Inventory ( ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition CREATE TABLE Customer (CustomerlD INT NOT NULL PRIMARY KEY, CustomerName VARCHAR(2S5) NOT NULL, CustomerAddress VARCHAR(1000) NOT NULL) You are designing a new table named Orders that has the following definition: CREATE TABLE Orders (OrderlD INT NOT NULL PRIMARY KEY, CustomerlD INT NOT NULL, OrderDescnption VARCHAR (2000) ) You need to ensure that the Customerld column in the Orders table contains only values that exist in the Customerld column of the Customer table Which Transact-SQL statement should you use?

ALTER TABLE Orders ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES Customer (CustomerId)

You create a view based on the following statement: CREATE VIEW dbo.vwBatchList AS SELECT b.BatchID , b.MailItemlD , c.ContractNum , c.FirstName + ' ' + c.LastName as ContractName , a.Address1 , a.City + ', ' + a.State + ' ' + a.Zip FROH BatchLog b join Contract c on b.MailItemlD = c.ContractID join Address a on a.ContractID = c.ContractID WHERE b.ProcessDate >= dateadd(d, 1/EOMOMTH(GETDATE(),-2)); You grant the Select permission to User1 You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1 Which Transact-SQL statement should you use?

ALTER VIEW dDo. w.BatchList AS SELECT WHERE b.ProcessDate >= dateadd(d, 1,EOMONTH(GETDATE( ),-2)) and b.ProcessDate < dateadd(d, 1, EOMONTH(GETDATE( ),-1));

You are developing a database in SQL Server 2012 to store information about current employee project assignments. You are creating a view that uses data from the project assignment table. You need to ensure that the view does not become invalid if the schema of the project assignment table changes. What should you do?

Add a DDL trigger to the project assignment table to re-create the view after any schema change.

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that the root cause is a query against a frequently updated table that has a clustered index. The query returns four columns: three columns in its WHERE clause contained in a non- clustered index and one additional column. You need to optimize the statement. What should you do?

Add a FORCESEEK hint to the query.

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index. The query returns the following six columns: One column in its WHERE clause contained in a non-clustered index Four additional columns One COUNT (*) column based on a grouping of the four additional columns You need to optimize the statement. What should you do?

Add a columnstore index to cover the query.

You are maintaining a Microsoft SQL Server database. You run the following query: SELECT e. [ID] p . [Title], p.[GivenName] + ' ' + ' ' + p.[SurName], e.[JobTitle], edh.[StartDate] FROM [ActiveEmployee] e INNER JOIN [Person] p ON p.[ID] = e.[ID] INNER JOIN [History] edh ON e.[ID] = edh.[ID] WHERE edh . EndDate IS NULL You observe performance issues when you run the query. You capture the following query execution plan: You need to ensure that the query performs returns the results as quickly as possible. Which action should you perform?

Add a new index to the ID column of the Person table. Cost is 53% for the Table Scan on the Person (p) table. This table scan is on the ID column, so we should put an index on it.

Suppose a Student column has two columns, Name and Marks. How to get name and marks of the top three students.

Ans. SELECT Name, Marks FROM Student s1 where 3 <= (SELECT COUNT(*) FROM Students s2 WHERE s1.marks = s2.marks)

Dynamic SQL is useful for several purposes, including:

Automating administrative tasks For example, querying metadata and constructing and executing a BACKUP DATABASE statement for each database in an on-premises instance ■ Improving performance of certain tasks For example, constructing parameterized ad-hoc queries that can reuse previously cached execution plans (more on this later) ■ Constructing elements of the code based on querying the actual data For example, constructing a PIVOT query dynamically when you don't know ahead of time which elements should appear in the IN clause of the PIVOT operator

Creates a table called dbo.Employees in the current database. IF OBJECT_ID('dbo.Employees') IS NOT NULL DROP TABLE dbo.Employees; CREATE TABLE dbo.Employees ( empid INT NOT NULL, empname VARCHAR(25) NOT NULL, mgrid INT NULL, CONSTRAINT PK_Employees PRIMARY KEY(empid), CONSTRAINT CHK_Employees_empid CHECK(empid > 0), CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid) ); Write a code to inserts a new row into the Employees table in a TRY block, and if an error occurs, shows how to identify the error by inspecting the ERROR_NUMBER function in the CATCH block. The code uses flow control to identify and handle errors you want to deal with in the CATCH block, and re-throws the error otherwise. The code also prints the values of the other error functions simply to show what information is available to you upon error.

BEGIN TRY INSERT INTO dbo.Employees(empid, empname, mgrid) VALUES(1, 'Emp1', NULL); -- Also try with empid = 0, 'A', NULL END TRY BEGIN CATCH IF ERROR_NUMBER() = 2627 BEGIN PRINT ' Handling PK violation...'; END ELSE IF ERROR_NUMBER() = 547 BEGIN PRINT ' Handling CHECK/FK constraint violation...'; END ELSE IF ERROR_NUMBER() = 515 BEGIN PRINT ' Handling NULL violation...'; END ELSE IF ERROR_NUMBER() = 245 BEGIN PRINT ' Handling conversion error...'; END ELSE BEGIN PRINT 'Re-throwing error...'; THROW; -- SQL Server 2012 only END PRINT ' Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10)); PRINT ' Error Message : ' + ERROR_MESSAGE(); PRINT ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT ' Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10)); PRINT ' Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10)); PRINT ' Error Proc : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc'); END CATCH;

You have a view that was created by using the following code: CREATE VIEW Sales.OrdersByTerritory AS SELECT OrderlD ,OrderDate ,SalesTerritoryID ,TotalDue FROM Sales.Orders; You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the following requirements Use one-part names to reference columns. Return the columns in the same order as the order used in OrdersByTerritoryView. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. RETURNS TABLE AS RETURN (SELECT OrderlD, OrderDate,

CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID, OrderDate, SalesTerritoryID, TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID=@T )

You have a view that was created by using the following code: CREATE VIEW Sales.OrdersByTerritory AS SELECT OrderlD ,OrderDate ,SalesTerritorylD ,TotalDue FROM Sales.Orders; You need to create an inline table-valued function named Sales.fn_OrdersByTerritory that returns sales information from a specified SalesTerritorylD. Sales fn_OrdersByTerritory must meet the following requirements: .Use one-part names to reference columns. • Return all the columns in the OrdersByTerritory View The function should return the same columns as they exist in the OrdersByTerritory view and in the same order. ■ Declare the input variable as @T. ■ Use SalesTerritorylD as an integer Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code RETURNS TABLE AS RETURN (SELECT OrderlD, OrderOate, Key Words

CREATE FUNCTION Sales.fn_OrdersByTerritory (@T integer) RETURNS TABLE AS RETURN (SELECT OrderID, OrderDate, SalesTerritoryID, TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T)

DRAG DROP :You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions CREATE TABLE Employees ( empid int NCI NULL , mgrid int NULL , empname varchar(25) NOT NULL , salary money NOT NULL CONSTRAINT PX_Employees PRIMARY KEY(empid) ); CREATE TABLE Departments ( deptid INT NOT NULL PRIMARY KEY , deptname VARCHAR(25) NOT NULL , deptmgrid INT NULL REFERENCES Employees(empid) ); You need to write a Transact-SQL statement that will support the following query: SELECT D.deptid, D.deptname, D.deptmgrid , ST.empid, ST.empname, ST.mgrid FROM Departments AS D CROSS APPLY getsubtree(D.deptmgrid) AS ST; Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.) Select and Place: CREATE FUNCTION dbo.getsubtree(@empid AS INT) RETURNS @TREE TABLE ( empid INT NOT NULL ,empname VARCHAR(25) NOT NULL ,mgrid INT NULL ,lvl INT NOT NULL) AS BEGIN WITH Employees_Subtree(empid, empname , mgrid, lvl) AS (SELECT empid, empname, mgnd, 0 FROM Employees WHERE empid = @empid UNION ALL SELECT e.empid, e.empname, e.mgnd, es.Ivl+1 FROM Employees AS e JOIN EmpIoyees_Subtree AS es ON e.mgrid = es.empid) SELECT * FROM Employees_Subtree; CREATE PROCEDURE dbo.getsubtree(@empid AS INT) AS BEGIN RETURN END INSERT INTO @TREE SELECT empid, empname, mgnd, 0 FROM Employees WHERE empid ■ 3empid UNION ALL SELECT e.empid, e.empname, e.mgrid, es.lvl+l FROM Employees AS e JOIN Employees_Subtree AS es ON e.mgrid ■ es.empid

CREATE FUNCTION dbo.getsubtree(@empid AS INT) RETURNS @TREE TABLE ( empid INT NOT NULL ,empname VARCHAR(25) NOT NULL ,mgrid INT NULL ,lvl INT NOT NULL) AS BEGIN WITH Employees_Subtree(empid, empname , mgrid, lvl) AS (SELECT empid, empname, mgnd, 0 FROM Employees WHERE empid = @empid UNION ALL SELECT e.empid, e.empname, e.mgnd, es.Ivl+1 FROM Employees AS e JOIN EmpIoyees_Subtree AS es ON e.mgrid = es.empid) INSERT INTO @TREE SELECT * FROM Employees_Subtree; RETURN END

DRAG DROP You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition: CREATE TABLE Prices ( PriceId int IDENTITY(1,1) PRIMARY KEY, ActualPrice NUMERIC(16,9), PredictedPrice NUMERIC(16,9) ) You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.) Select and Place: CREATE FUNCTION udf_price_index (@actualprice FLOAT, @predictedprice FLOAT) RETURNS FLOAT ALTER TABLE Prices ADD [Pricelndex] AS dbo. udf_price_index ([ActualPrice], [PredictedPrice]) PERSISTED ALTER TABLE Prices ADD [PriceIndex] AS dbo.udf_price_index ([ActuaiPrice], [PredictedPrice]) AS BEGIN SELECT @priceindex - CASE WHEN @predictedprice - 0 THEN 0 ELSE @actualprice/@predictedprice END END GO CREATE FUNCTION udf_price_index (@actualprice NUMERIC(16, 9), @predictedprice NUMERIC(16,9)) RETURNS NUMERIC(16,9) WITH SCHEMABINDING AS BEGIN DECLARE @pricemdex NUMERIC (16, 9) SELECT @pricemdex - CASE WHEN @predictedprice = 0 THEN 0 ELSE @actualprice/@predictedprice END RETURN @priceindex END GO

CREATE FUNCTION udf_price_index (@actualprice NUMERIC(16, 9), @predictedprice NUMERIC(16,9)) RETURNS NUMERIC(16,9) WITH SCHEMABINDING AS BEGIN DECLARE @pricemdex NUMERIC (16, 9) SELECT @pricemdex - CASE WHEN @predictedprice = 0 THEN 0 ELSE @actualprice/@predictedprice END RETURN @priceindex END GO ALTER TABLE Prices ADD [Pricelndex] AS dbo. udf_price_index ([ActualPrice], [PredictedPrice]) PERSISTEDx

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

You develop a Microsoft SQL Server 2012 database. You need to create and call a stored procedure that meets the following requirements: Accepts a single input parameter for CustomerID. Returns a single integer to the calling application. Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN GO or DECLARE @CustomerRatingByCustomer INT EXECUTE dbo.GetCustomerRating @CustomerID = 1745, @CustomerRating = @CustomerRatingByCustomer OUTPUT

CORRECT TEXT You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.) Create Table OrderDetails ListPrice Money Not Null Quantity int Not Null Create Table Customers CustomerID int Not Null Pk FirstName varchar(100) Not Null LastName varchar(100) Not Null Create Table Orders OrderID int Not Null PK OrderDate datetime NotNull CustomerID int NotNull You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements: ■ NOT use object delimiters ■ Minimize sorting and counting. ■ Return only the last name of each customer in alphabetical order ■ Return only the number of rows specified by the @Count parameter - The solution must NOT use BEGIN and END statements. Which code segment should you use? To answer, type the correct code in the answer area.

CREATE PROCEDURE usp_Customers @Count int AS SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.) OrderDetails (Table) ListPrice (Column Name) money (Data Type) Quantity (Column Name) int (Data Type) Customers (Table) CustomerID (Column Name) int (Data Type) FirstName (Column Name) varchar (Data Type) LastName (Column Name) varchar (Data Type) Orders (Table) OrderID (Column Name) int (Data Type) OrderDate (Column Name) datetime (Data Type) CustomerID (Column Name) int (Data Type) You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter. The solution must NOT use BEGIN, END, or DECLARE statements. Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement CREATE PROCEDURE usp_Customers LastName FROM Customers ORDER BY LastName

CREATE PROCEDURE usp_Customers @Count int SELECT TOP(@Count) Customers.LastName FROM Customers ORDER BY Customers.LastName

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions: Always starts with 1. Starts again from 1 after it reaches 100. Which Transact-SQL statement should you use?

CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence

You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table SOURCEID: (1, 3, 3, 2) CustomerID:(234, 7345, 4402, 866) Customer Name: (John Smith, Jason Warren, Susan Burk, Micheal Allen) You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (SourceID, CustomerID));

You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements: Contain a new column named LineltemTotal that stores the product of ListPrice and Quantity for each row. The calculation for a line item total must not be run every time the table is queried. The code must NOT use any object delimiters. The solution must ensure that LineItemTotal is stored as the last column in the table. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. CREATE TABLE OrderDetails ( ListPrice money NOT NULL, Quantity int NOT NULL, )

CREATE TABLE OrderDetails ( ListPrice money NOT NULL, Quantity int NOT NULL, LineItemTotal AS (ListPrice * Quantity) PERSISTED )

CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS SET NOCOUNT ON; DECLARE @eventdata AS XML = eventdata(); INSERT INTO dbo.AuditDDLEvents( posttime, eventtype, loginname, schemaname, objectname, targetobjectname, eventdata) VALUES( @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'VARCHAR(23)'), @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'), @eventdata); GO How do you test the trigger? Write code?

CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY); ALTER TABLE dbo.T1 ADD col2 INT NULL; ALTER TABLE dbo.T1 ALTER COLUMN col2 INT NOT NULL; CREATE NONCLUSTERED INDEX idx1 ON dbo.T1(col2); (/*Next, run the following code to query the audit table.*/) SELECT * FROM dbo.AuditDDLEvents;

You create a database trigger for events with a database scope, such as

CREATE TABLE.

You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition: CREATE TABLE [dbo].[Products]( [Productld] [bigint] NOT NULL, [RetailPrice] [nvarchar](25) NOT NULL, [WholeSalePrice] [nvarchar](25) NULL, [Name] [nvarchar](50) NOT NULL, [Category] [nvarchar](25) NOT NULL, CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [Productld] ASC ) ON [PRIMARY] ) ON [PRIMARY] You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated Which Transact-SQL query should you use?

CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice) - - Create Audit Records

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions: CREATE TABLE [dbo].[Employee]( [PersonId] [bigint] NOT NULL, [EmployeeNumher] [nvarchar] (15) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [Personld] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Person]( [Id] [bigint] NOT NULL, [FirstName] [nvarchar](25) NOT NULL, [LastName] [nvarchar](25) NOT NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [Id] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO You create a view named VwEmployee as shown in the following Transact-SQL statement. CREATE VIEW [dbo].[VwEmployee] AS SELECT Employee.EmployeeNumber, Person.FirstName, Person.LastName, Person.Id FROM Employee INNER JOIN Person ON Employee.Personld = Person.Id GO Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. Which Transact-SQL statement should you use?

CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END

Partial syntax to create trigger is

CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

IF OBJECT_ID('dbo.AuditDDLEvents', 'U') IS NOT NULL DROP TABLE dbo.AuditDDLEvents; CREATE TABLE dbo.AuditDDLEvents ( audit_lsn INT NOT NULL IDENTITY, posttime DATETIME NOT NULL, eventtype sysname NOT NULL, loginname sysname NOT NULL, schemaname sysname NOT NULL, objectname sysname NOT NULL, targetobjectname sysname NULL, eventdata XML NOT NULL, CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(audit_lsn) ); Create the trg_audit_ddl_events audit trigger on the database by using the event group DDL_DATABASE_LEVEL_EVENTS , which represents all DDL events at the database level.

CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS SET NOCOUNT ON; DECLARE @eventdata AS XML = eventdata(); INSERT INTO dbo.AuditDDLEvents( posttime, eventtype, loginname, schemaname, objectname, targetobjectname, eventdata) VALUES( @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'VARCHAR(23)'), @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'), @eventdata.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'), @eventdata); GO

You need to create a view named uv_CustomerFullName to meet the following requirements: The code must NOT include object delimiters. OrderDetails (Table) ListPrice (Column Name) money (Data Type) Quantity (Column Name) int (Data Type) Customers (Table) CustomerID (Column Name) int (Data Type) FirstName (Column Name) varchar(100) (Data Type) LastName (Column Name) varchar(100) (Data Type) Orders (Table) OrderID (Column Name) int (Data Type) OrderDate (Column Name) datetime (Data Type) CustomerID (Column Name) int (Data Type) The view must be created in the Sales schema. Columns must only be referenced by using one-part names. The view must return the first name and the last name of all customers. The view must prevent the underlying structure of the customer table from being changed. The view must be able to resolve all referenced objects, regardless of the user's default schema. Which code segment should you use? To answer, type the correct code in the answer area.

CREATE VIEW Sales.uv_CustomerFullName WITH SCHEMABINDING AS SELECT FirstName, LastName FROM Sales.Customers

DRAG DROP You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements: Displays the details of only students from Canada. Allows insertion of details of only students from Canada. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.) WITH ENCRYPTION WITH CHECK OPTION WITH SCHEMABINDING WITH VIEW_METADATA CREATE VIEW dbo.CanadianStudents CREATE INDEXED VIEW dbo.CanadianStudents AS SELECT s.LastNaine, 3. FirstNaite, s.JobTitle, a.Country, e.LastQualification FROM Student s INNER JOIN NativeAddress a ON a.AddressID = a.AddreasID INNER JOIN EducationHistory e ON s.StudentID = e.StudentID WHERE a.Country = 'Canada'

CREATE VIEW dbo.CanadianStudents WITH SCHEMABINDING AS SELECT s.LastNaine, 3. FirstNaite, s.JobTitle, a.Country, e.LastQualification FROM Student s INNER JOIN NativeAddress a ON a.AddressID = a.AddreasID INNER JOIN EducationHistory e ON s.StudentID = e.StudentID WHERE a.Country = 'Canada' WITH CHECK OPTION

You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. CREATE VIEW sales.uv_CustomerFullNames AS SELECT FirstName, LastName FROM Sales.Customers

CREATE VIEW sales.uv_CustomerFullNames WITH SCHEMABINDING AS SELECT FirstName, LastName FROM Sales.Customers

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to invoke a table-valued function for each row returned by a query. Which Transact-SQL operator should you use?

CROSS APPLY

There are two forms of APPLY:

CROSS APPLY and OUTER APPLY.

The APPLY operator can take one of two forms:

CROSS APPLY or OUTER APPLY.

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. The view has the following definition: Create view apt.vwCompanyPhoneList (Source, CompanyTD, CompanyNumber, LastName, FirstName, BusinessName, Phone) as SELECT 'Customer' as Source , CustomerID , CustomerNumber , CustomerLastName , CustomerFirstName , CustomerBusinessName , Phone FROM apt.Customer UNION ALL SELECT 'Agent* as Source , AgentID , AgentNumber , AgentLastName , AgentFirstName , AgentBusinessName , Phone FROM apt.Agent UNION ALL SELECT 'Vendor' as Source , VendorlD , VendorNumber , VendorLastName , VendorFirstName , VendorBusinessName , Phone FROM apt.Vendor GO You need to ensure that users can update only the phone numbers by using this view. What should you do?

Create an INSTEAD OF UPDATE trigger on the view.

You develop a Microsoft SQL Server 2012 database You create a view from the Orders and OrderDetails tables by using the following definition CREATE VIEW vOrders WITH SCHEMABINDING AS SELECT o.ProductID, o.CrderDate, SUM(od.UmtPrice * od.OrderQty) AS Amount FROM OrderDetails AS od INNER JOIN Orders AS o ON od.CrderlD = o.CrderlD WHERE od.SaiesOrderlD = o.SalesCrderID GROUP 3Y o.OrderDate, o.ProductID GO You need to ensure that users are able to modify data by using the view What should you do?

Create an INSTEAD OF trigger on the view.

You have an XML schema collection named Sales.InvoiceSchema. You need to declare a variable of the XML type named invoice. The solution must ensure that the invoice is validated by using Sales.InvoiceSchema. The solution must ensure that the invoice variable is validated by using Sales.InvoiceSchema schema. Provide the correct code in the answer area.

DECLARE @invoice XML(Sales.InvoiceSchema)

You are developing a Microsoft SQL Server 2012 database for a company. The database contains a table that is defined by the following Transact-SQL statement: CREATE TABLE [dbo].[Employees]( [EmpNumber] [int] NOT NULL, [Surname] [varchar](40) NOT NULL, [GivenName] [varchar](20) NOT NULL, [PersonalIDNumber] [varchar](11) NOT NULL, [Gender] [varchar](1) NULL, [DateO fBirth] [date] NOT NULL) You use the following Transact-SQL script to insert new employee data into the table. Line numbers are included for reference only. 01 BEGIN TRY 02 INSERT INTO [dbo].[Employees]([EmpNumber],[Surname],[GivenName],[Gender],[DateOfBirth],[PersonalIDNumber]) 03 Values (132, 'Williams','John','M', '1/1/1990',NULL) 04 END TRY 05 BEGIN CATCH 06 07 END CATCH If an error occurs, you must report the error message and line number at which the error occurred and continue processing errors. You need to complete the Transact-SQL script. Which Transact-SQL segment should you insert at line 06?

DECLARE @message NVARCHAR(1000),@severity INT, @state INT; SELECT @message = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE(); RAISERROR (@message, @severity, @state);

What are all different types of collation sensitivity?

Different types of collation sensitivity are as follows Case Sensitivity: A and a and B and b. Kana Sensitivity: Japanese Kana characters. Width Sensitivity: Single byte character and double byte character. Accent Sensitivity.

What are the steps involved in improving the SQL performance?

Discover - First of all, find out the areas of improvement. Explore tools like Profiler, Query execution plans, SQL tuning advisor, dynamic views, and custom stored procedures. Review - Brainstorm the data available to isolate the main issues. Propose - Here is a standard approach one can adapt to boost the performance. However, you can customize it further to maximize the benefits. 1. Identify fields and create indexes. 2. Modify large queries to make use of indexes created. 3. Refresh table and views and update statistics. 4. Reset existing indexes and remove unused ones. 5. Look for dead blocks and remove them. Validate - Test the SQL performance tuning approach. Monitor the progress at a regular interval. Also, track if there is any adverse impact on other parts of the application. Publish - Now, it's time to share the working solution with everyone in the team. Let them know all the best practices so that they can use it with ease. Back to top

FOR XML AUTO

Does not include a ROOT Formats stream into Attributes Row element names are based on the table and columns in the SELECT list Nests second table inside the first table element Can group XML stream with ORDER BY

You generate a daily report according to the following query: SELECT c.CustomerName FROM Sales.Customer c WHERE Sales.ufnGetLastOrderDate(c.CustomerlD) < DATEADD(DAY, -90, GETDATE()) The Sales.ufnGetLastOrderDate user-defined function (UDF) is defined as follows: CREATE FUNCTION Sales.ufnGetLastOrderDate(gCustomerlD int) RETURNS datetime AS BEGIN DECLARE @lastOrderDate datetime SELECT @lastOrderDate = MAX(OrderDate) FROM Sales.SalesOrder WHERE CustomerlD = @CustomerlD RETURN @lastOrderDate END You need to improve the performance of the query. What should you do?

Drop the UDF and rewrite the report query as follows: WITH cte(CustomerID, LastOrderDate) AS ( SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate] FROM Sales.SalesOrder GROUP BY CustomerID ) SELECT c.CustomerName FROM cte INNER JOIN Sales.Customer c ON cte.CustomerID = c.CustomerID WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

ENCRYPTION

Within the trigger, you obtain information on the event that caused the trigger to fire by querying a function called

EVENTDATA that returns the event information as an XML value.

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use?

EXECUTE AS OWNER

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) EmployeelD int EmployeeNum char (10) lastName nvarchar(200) FirstName nvarchar(200) MiddleName nvarchar(200) DateHired date Departments int JobTitle varchar(200) ReportsToID int (EmployeelD: (a)Uniquely identifies the employee record in the table (b)Used throughout the database by all the other tables that reference the Employee table) (EmployeeNum : (a)An alphanumeric value calculated according to company requirements (b)Has to be unique within the Employee table (c)Exists only within the Employee table) (DepartmentID: (a)References another table named (b)Department that contains data for each department in the company) Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a Primary Key constraint for this table?

EmployeeID

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) (EmployeelD: (a)Uniquely identifies the employee record in the table (b)Used throughout the database by all the other tables that reference the Employee table) (EmployeeNum : (a)An alphanumeric value calculated according to company requirements (b)Has to be unique within the Employee table (c)Exists only within the Employee table) (DepartmentID: (a)References another table named (b)Department that contains data for each department in the company) (ReponsToID: (a)Contains the EmployeelD of the manager to whom an employee reports) EmployeelD int EmployeeNum char (10) lastName nvarchar(200) FirstName nvarchar(200) MiddleName nvarchar(200) DateHired date Departments int JobTitle varchar(200) ReportsToID int Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database?

EmployeeID

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.) (OrderDetails)(tablename) ListPrice(Columnname) Money Not Null Quantity(Columnname) int Customers(tablename) CustomerID(Columnname) int Not Null FirstName(Columnname) varchar(100) Not Null LastName(Columnname) varchar(100) Not Null Orders(tablename) OrderID(Columnname) int Not Null OrderDate(Columnname) Datetime Not Null CustomerID(Columnname) int Not Null You need to create a query for a report. The query must meet the following requirements: Return the last name of the customer who placed the order. Return the most recent order date for each customer. Group the results by CustomerID. Order the results by the most recent OrderDate. Use the database name and table name for any table reference. Use the first initial of the table as an alias when referencing columns in a table. The solution must support the ANSI SQL-99 standard and must NOT use object identifiers. Part of the correct T-SQL statement has been provided in the answer area. Complete the SQL statement. 1 SELECT LastName, 2 MAX(Order-Date) AS MostRecentOrderDate

Explanation: SELECT o.LastName, MAX (o.OrderData) AS MostRecentOrderData FROM Sales.Orders AS o GROUP BY o.CustomerID ORDER BY o.OrderDate DESC

Logical query processing starts with the

FROM clause, and then moves on to WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.

When table hints are specified without the WITH keyword, the hints should be specified alone. For example:

FROM t (TABLOCK)

When the hint is specified with another option, the hint must be specified with the WITH keyword:

FROM t WITH (TABLOCK, INDEX(myindex))

EXEC doesn't supports both regular and Unicode character strings as input

False

If the TRY block has Some error, the CATCH block is simply skipped.

False

MultiStatement Table valued functions can use side-effecting operator

False

Triggers in SQL Server fire per modified row and not per statement.

False

Unlike the INNER JOIN or LEFT JOIN, the cross join needs to establish a relationship between the joined tables.

False

views can Accepts Parameters

False

views can Can contain Multiple Statements

False

What do you check first if there are multiple fragments in the SYSTEM tablespace?

First of all, check if the users don't have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by verifying the DBA_USERS view.

When is the right time to rebuild an index?

First of all, select the target index and run the 'ANALYZE INDEX VALIDATE STRUCTURE' command. Every time you run it, a single row will get created in the INDEX_STATS view. But the row gets overwritten the next time you run the ANALYZE INDEX command. So better move the contents of the view to a local table. Thereafter, analyze the ratio of 'DEL_LF_ROWS' to 'LF_ROWS' and see if you need to rebuild the index.

What can you do to optimize the %XYZ% queries?

First of all, set the optimizer to scan all the entries from the index instead of the table. You can achieve it by specifying hints. Please note that crawling the smaller index takes less time than to scan the entire table.

Why it is not recommended to use GUID and CHARACTER columns as Clustered index keys?

For GUID columns, that are stored in UNIQUE IDENTIFIER columns, the main challenge that affects the clustered index key sorting performance is the nature of the GUID value that is larger than the integer data types, with 16 bytes size, and that it is generated in random manner, different from the IDENTITY integer values that are increasing continuously. For the CHARACTER columns. The main challenges include limited sorting performance of the character data types, the large size, non-increasing values, non-static values that often tend to change in the business applications and not compared as binary values during the sorting process, as the characters comparison mechanism depends on the used collation.

BEGIN TRY INSERT INTO dbo.Employees(empid, empname, mgrid) VALUES(1, 'Emp1', NULL); -- Also try with empid = 0, 'A', NULL END TRY BEGIN CATCH IF ERROR_NUMBER() = 2627 BEGIN PRINT ' Handling PK violation...'; END ELSE IF ERROR_NUMBER() = 547 BEGIN PRINT ' Handling CHECK/FK constraint violation...'; END ELSE IF ERROR_NUMBER() = 515 BEGIN PRINT ' Handling NULL violation...'; END ELSE IF ERROR_NUMBER() = 245 BEGIN PRINT ' Handling conversion error...'; END ELSE BEGIN PRINT 'Re-throwing error...'; THROW; -- SQL Server 2012 only END PRINT ' Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10)); PRINT ' Error Message : ' + ERROR_MESSAGE(); PRINT ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT ' Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10)); PRINT ' Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10)); PRINT ' Error Proc : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc'); END CATCH; When you run the same code a second time, the INSERT statement fails, control is passed to the CATCH block, and a primary key violation error is identified. You get the following output.

Handling PK violation... Error Number : 2627 Error Message : Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object 'dbo.Employees'. Error Severity: 14 Error State : 1 Error Line : 3 Error Proc : Not within proc

Can you write a query to get employee names starting and ending with a vowel?

Here you will get only one record of "empone". select empid, empname from employee where empname like '[aeiou]%[aeiou]'

You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.) 01 CREATE PROCEDURE DeleteCandidate 02 @lnputCandidatelD INT; 03 AS 04 BEGIN 05 BEGIN TRANSACTION; 06 BEGIN TRY 07 DELETE HumanResources.JobCandidate 08 WHERE JobCandidateID = @InputCandidateID; 09 INSERT INTO Audit.Log(Operation,OperationDate) 10 VALUES('Delete 1,SYSDATETIME()); 11 COMMIT TRANSACTION; 12 END TRY 13 BEGIN CATCH 14 15 COMMIT TRANSACTION 16 ELSE 17 R0LLBACK TRANSACTION; 18 END CATCH 19 END; The procedure can be called within other transactions You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit Log table fails Which code segment should you add to line 14?

IF (XACT_STATE ( ) ) = 1

How do you use If function statement to drop table

IF OBJECT_ID('Table_Name', 'U') IS NOT NULL DROP TABLE Table_Name;

Create a stored procedure that encapsulates reusable error-handling code. This way you can maintain the reusable error-handling code in one place.

IF OBJECT_ID('dbo.ErrInsertHandler', 'P') IS NOT NULL DROP PROC dbo.ErrInsertHandler; GO CREATE PROC dbo.ErrInsertHandler AS SET NOCOUNT ON; IF ERROR_NUMBER() = 2627 BEGIN PRINT 'Handling PK violation...'; END ELSE IF ERROR_NUMBER() = 547 BEGIN PRINT 'Handling CHECK/FK constraint violation...'; END ELSE IF ERROR_NUMBER() = 515 BEGIN PRINT 'Handling NULL violation...'; END ELSE IF ERROR_NUMBER() = 245 BEGIN PRINT 'Handling conversion error...'; END PRINT 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10)); PRINT 'Error Message : ' + ERROR_MESSAGE(); PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)); PRINT 'Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10)); PRINT 'Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10)); PRINT 'Error Proc : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc'); GO

If a clustered index exists,

INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek.

If no clustered index exists,

INDEX(0) forces a table scan and INDEX(1) is interpreted as an error.

To display the current locking option for a given index, use the

INDEXPROPERTY function.

What is the main difference between a Non-clustered index that is built over a Heap table and a Non-clustered index that is built over a Clustered table? What is the difference between a RID Lookup and a Key Lookup?

If a Non-Clustered index is built over a Heap table or view (read more about SQL Server indexed views, that have no Clustered indexes) the leaf level nodes of that index hold the index key values and Row ID (RID) pointers to the location of the rows in the heap table. The RID consists of the file identifier, the data page number, and the number of rows on that data page. On the other hand, if a Non-clustered index is created over a Clustered table, the leaf level nodes of that index contain Non-clustered index key values and clustering keys for the base table, that are the locations of the rows in the Clustered index data pages. A RID Lookup operation is performed to retrieve the rest of columns that are not available in the index from the heap table based on the ID of each row. A Key Lookup operation is performed to retrieve the rest of columns that are not available in the index from the Clustered index, based on the Clustered key of each row,

When would you add more Copy Latches? What are the parameters that control the Copy Latches?

If there is excessive contention for the Copy Latches, check from the "redo copy" latch hit ratio. In such a case, add more Copy Latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to double the number of CPUs available.

What happens when you run SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcusts FROM Sales.Orders GROUP BY orderyear;

If you try running this query, you get the following error. The following attempt is invalid because the GROUP BY clause refers to a column alias that was assigned in the SELECT clause, and the GROUP BY clause is logically processed prior to the SELECT clause.

You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition: CREATE TABLE dbo.Product (ProductID INT PRIMARY KEY, Name VARCHAR (50) NOT NULL, Color VARCHAR(15) NOT NULL, Size VARCHAR(5) NOT NULL, Style CHAR(2) NULL, Height DECIMAL(8,2) NULL); You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

Implement page-level compression.

What is a Latch Free Event? And when does it occur? Alos, how does the system handles it?

In Oracle, Latch Free wait event occurs when a session requires a latch, attempts to get it but fails because someone else has it. So it sleeps with a wait eying for the latch to get free, wakes up and tries again. The time duration for it was inactive is the wait time for Latch Free. Also, there is no ordered queue for the waiters on a latch, so the one who comes first gets it.

What are the bottlenecks that affect the performance of a Database?

In a web application, the database tier can prove to be a critical bottleneck in achieving the last mile of scalability. If a database has performance leakage, that can become a bottleneck and likely to cause the issue. Some of the common performance issues are as follows. 1. Abnormal CPU usage is the most obvious performance bottleneck. However, you can fix it by extending CPU units or replacing with an advanced CPU. It may look like a simple issue but abnormal CPU usage can lead to other problems. 2. Low memory is the next most common bottleneck. If the server isn't able to manage the peak load, then it poses a big question mark on the performance. For any application, memory is very critical to perform as it's way faster than the persistent memory. Also, when the RAM goes down to a specific threshold, then the OS turns to utilize the swap memory. But it makes the application to run very slow. You can resolve it by expanding the physical RAM, but it won't solve memory leaks if there is any. In such a case, you need to profile the application to identify the potential leaks within its code. 3. Too much dependency on external storage like SATA disk could also come as a bottleneck. Its impact gets visible while writing large data to the disk. If output operations are very slow, then it is a clear indication an issue becoming the bottleneck. In such cases, you need to do scaling. Replace the existing drive with a faster one. Try upgrading to an SSD hard drive or something similar.

You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected. You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer. What should you do?

Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause. Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1. You need to optimize the statement. What should you do?

Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do?

Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

UNION ALL

Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

What is the difference between index Rebuild and Index Reorganize operations?

Index fragmentation can be resolved by rebuilding and reorganizing SQL Server indexes regularly. The Index Rebuild operation removes fragmentation by dropping the index and creating it again, defragmenting all index levels, compacting the index pages using the Fill Factor values specified in rebuild command, or using the existing value if not specified and updating the index statistics using FULLSCAN of all the data. The Index Reorganize operation physically reorders leaf level pages of the index to match the logical order of the leaf nodes. The index reorganizes operation will be always performed online. Microsoft recommends fixing index fragmentation issues by rebuilding the index if the fragmentation percentage of the index exceeds 30%, where it recommends fixing the index fragmentation issue by reorganizing the index if the index fragmentation percentage exceeds 5% and less than 30%.

Use Database BEGIN TRANSACTION Using BEGIN TRANSACTION is important for what?

It is used to hold the locks

Why it is not recommended to create indexes on small tables?

It takes the SQL Server Engine less time scanning the underlying table than traversing the index when searching for specific data. In this case, the index will not be used but it will still negatively affect the performance of data modification operations, as it will be always adjusted when modifying the underlying table's data.

How could we benefits from a Filtered index in improving the performance of queries?

It uses a filter predicate to improve the performance of queries that retrieve a well-defined subset of rows from the table, by indexing the only portion of the table rows. The smaller size of the Filtered index, that consumes a small amount of the disk space compared with the full-table index size, and the more accurate filtered statistics, that cover the filtered index rows with only minimal maintenance cost, help in improving the performance of the queries by generating a more optimal execution plan.

What is a explain plan?

It's a term used in Oracle. And it is a type of SQL clause in Oracle which displays the execution plan that its optimizer plans for executing the SELECT/UPDATE/INSERT/DELETE statements.

DRAG DROP You create the following stored procedure. (Line numbers are included for reference only.) 01 CREATE PROCEDURE dbo.InsertCountryRegion 02 @CountryRegionCode nvarchar(3), 03 @Name nvarchar(5 0) 04 AS 05 BEGIN 0 6 SET NOCOUNT ON; 07 ■ ■ ■ 08 END ; You need to ensure that the stored procedure performs the following tasks: If a record exists, update the record. If no record exists, insert a new record. Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) Select and Place: UPDATE CountryRegion SET Name - @Name WHERE CountryRegionCode = @CountryRegionCode WHEN NOT MATCHED BY SOURCE THEN WHEN NOT MATCHED BY TARGET THEN WHEN MATCHED THEN UPDATE SET Name = Source.Name MERGE CountryRegion AS target USING (SELECT SCountryRegionCode, @Name) AS source (CountryRegionCode, Name) ON (target.CountryRegionCode = source.CountryRegionCode) IF (@@ROWCOUNT > 0) INSERT INTO CountryRegion (CountryRegionCode, Name) VALUES (@CountryRegionCode, @Name); INSERT (CountryRegionCode, Name) VALUES (source.CountryRegionCode, source.Name);

MERGE CountryRegion AS target USING (SELECT SCountryRegionCode, @Name) AS source (CountryRegionCode, Name) ON (target.CountryRegionCode = source.CountryRegionCode) IF (@@ROWCOUNT > 0) WHEN MATCHED THEN UPDATE SET Name = Source.Name WHEN NOT MATCHED BY TARGET THEN INSERT (CountryRegionCode, Name) VALUES (source.CountryRegionCode, source.Name);

What could most likely cause a SQL query to run as slow as 5 minutes?

Most probably, a sudden surge in the volume of data in a particular table could slow down the output of a SQL query. So collect the required stats for the target table. Also, monitor any change in the DB level or within the underlying object level.

What exactly would you do to check the performance issue of SQL queries?

Mostly the database isn't slow, but it's the worker session which drags the performance. And it's the abnormal session accesses which cause the bottlenecks. 1. Review the events that are in wait or listening mode. 2. Hunt down the locked objects in a particular session. 3. Check if the SQL query is pointing to the right index or not. 4. Launch SQL Tuning Advisor and analyze the target SQL_ID for making any performance recommendation. 5. Run the "free" command to check the RAM usage. Also, use TOP command to identify any process hogging the CPU.

What is Normalization?

Normalization is the process of table design to minimize the data redundancy.

What is Normalization? How many Normalization forms are there?

Normalization is used to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies. There are 5 forms of Normalization First Normal Form (1NF): It removes all duplicate columns from the table. Creates a table for related data and identifies unique column values First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using the primary key Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

Numeric

You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

Numeric Numeric data types that have fixed precision and scale. decimal[ (p[ , s] )] and numeric[ (p[ , s] )] where * p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18. * (scale) The number of decimal digits that will be stored to the right of the decimal point.

To skip the first 10 products and select the next 10 products, you use both

OFFSET and FETCH clauses

To get the top 10 most expensive products you use both

OFFSET and FETCH clauses:

What is the difference between OLTP and OLAP workloads and how do they affect index creation decisions?

On Online Transaction Processing (OLTP) databases, workloads are used for transactional systems, in which most of the submitted queries are data modification queries. In contrast, Online Analytical Processing (OLAP) database workloads are used for data warehousing systems, in which most of the submitted queries are data retrieval queries that filter, group, aggregate and join large data sets quickly. Creating a large number of indexes on a database table affects data modification (e.g. Updates) operation performance. When you add or modify a row in the underlying table, the row will also be adjusted appropriately in all related table indexes. Because of that, you need to avoid creating a large number of indexes on the heavily modified tables and create the minimum possible number of indexes, with the least possible number of columns on each index. For Online Analytical Processing (OLAP) workloads, in which tables have low modification requirements, you can create a large number of indexes that improve the performance of the data retrieval operations

What are some tips to improve the performance of SQL queries?

Optimizing SQL queries can bring substantial positive impact on the performance. It also depends on the level of RDBMS knowledge you have. Let's now go over some of the tips for tuning SQL queries. 1. Prefer to use views and stored procedures in spite of writing long queries. It'll also help in minimizing network load. 2. It's better to introduce constraints instead of triggers. They are more efficient than triggers and can increase performance. 3. Make use of table-level variables instead of temporary tables. 4. The UNION ALL clause responds faster than UNION. It doesn't look for duplicate rows whereas the UNION statement does that regardless of whether they exist or not. 5. Prevent the usage of DISTINCT and HAVING clauses. 6. Avoid excessive use of SQL cursors. 7. Make use of SET NOCOUNT ON clause while building stored procedures. It represents the rows affected by a T-SQL statement. It would lead to reduced network traffic. 8. It's a good practice to return the required column instead of all the columns of a table. 9. Prefer not to use complex joins and avoid disproportionate use of triggers. 10. Create indexes for tables and adhere to the standards.

What are Rule-based Optimizer and Cost-based Optimizer?

Oracle determines how to get the required data for processing a valid SQL statement. It uses one of following two methods to take this decision. Rule-based Optimizer - When a server doesn't have internal statistics supporting the objects referenced by the statement, then the RBO method gets preference. However, Oracle will deprecate this method in the future releases. Cost-based Optimizer - When there is an abundance of the internal statistics, the CBO gets the precedence. It verifies several possible execution plans and chooses the one with the lowest cost based on the system resources.

What are several SQL performance tuning enhancements in Oracle?

Oracle provides many performance enhancements, some of them are: 1. Automatic Performance Diagnostic and Tuning Features 2. Automatic Shared Memory Management - It gives Oracle control of allocating memory within the SGA. 3. Wait-model improvements - A number of views have come to boost the Wait-model. 4. Automatic Optimizer Statistics Collection - Collects optimizer statistics using a scheduled job called GATHER_STATS_JOB. 5. Dynamic Sampling - Enables the server to enhance performance. 6. CPU Costing - It's the basic cost model for the optimizer (CPU+I/O), with the cost unit as time optimizer notifies. 7. Rule Based Optimizer Obsolescence - No more used. 8. Tracing Enhancements - End to End tracing which allows a client process to be identified via the Client Identifier instead of using the typical Session ID.

the CREATE TABLE and ALTER TABLE statements can be used to set locking granularity on

PRIMARY KEY and UNIQUE constraints.

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table. PRIMARY(Filegroup) • Sales.mdf(File) XACTIONS(Filegroup) •Sales_l.ndf(File) •Sales_2.ndf(File) •Sales_3.ndf(File) ARCHIVES(Filegroup) •SalesArch_l.ndf(File) •SalesArch_2.ndf(File) You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?

Perform a file restore.

What is Proactive tuning and Reactive tuning?

Proactive tuning - The architect or the DBA determines which combination of system resources and available Oracle features fulfill the criteria during Design and Development. Reactive tuning - It is the bottom-up approach to discover and eliminate the bottlenecks. The objective is to make Oracle respond faster.

What are properties of the transaction?

Properties of transaction are known as ACID properties, such as Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone Consistency: Ensures that all changes made through successful transaction are reflected properly on database Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure

What is SQL Query Optimization?

Query Optimization is the process of writing the query in a way so that it could execute quickly. It is a significant step for any standard application.

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

READ COMMITTED SNAPSHOT

How could we benefit from the INCLUDE feature to overcome Non-Clustered index limitations?

Rather than creating a Non-clustered index with a wide key, large columns that are used to cover the query can be included to the Non-clustered index as non-key columns, up to 1023 non-key columns, using the INCLUDE clause of the CREATE INDEX T-SQL statement, that was introduced in SQL Server 2005, with a minimum of one key column. The INCLUDE feature extends the functionality of Non-clustered indexes, by allowing us to cover more queries by adding the columns as non-key columns to be stored and sorted only in the leaf level of the index, without considering that columns values in the root and intermediate levels of the Non-clustered index. In this case, the SQL Server Query Optimizer will locate all required columns from that index, without the need for any extra lookups. Using the included columns can help to avoid exceeding the Non-clustered size limit of 900 bytes and 16 columns in the index key, as the SQL Server Database Engine will not consider the columns in the Non-clustered index non-key when calculating the size and number of columns of the index key. In addition, SQL Server allows us to include the columns with data types that are not allowed in the index key, such as VARCHAR(MAX), NVARCHAR(MAX), text, ntext and image, as Non-clustered index non-key columns.

What is the main difference between Redo, Rollback, and Undo?

Redo - Log that records all changes made to data, including both uncommitted and committed changes. Rollback - Segments to store the previous state of data before the changes. Undo - Helpful in building a read consistent view of data. The data gets stored in the undo tablespace.

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. What should you do?

Reference a user-defined function within the computed column.

_________________are programmable objects that encapsulate code to calculate a result or to execute activity.

Routines

SELECT STUFF(Tagltem.lDs.value('(./text())[l]', 'varchar(max)'), 1, 2, ") AS List, FROM @tbl1 tbl1 OUTER APPLY ( SELECT ',' + [Lookup].Name FROM @tbl2 tbl2 WHERE tbl1.ID = tbl2.ID FOR XML PATH("), TYPE ) AS Tagltem(lDs) What do you need to change so that it would work?

SELECT STUFF(Tagltem.lDs.value('(./text())[l]', 'varchar(max)'), 1, 2, ") AS List, FROM @tbl1 tbl1 Cross APPLY ( SELECT ',' + [Lookup].Name FROM @tbl2 tbl2 WHERE tbl1.ID = tbl2.ID FOR XML PATH("), TYPE ) AS Tagltem(lDs)

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use?

SELECT IF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False' ) AS BadCast

You have a Microsoft SQL Server database that includes two tables named EmployeeBonus and BonusParameters. The tables are defined by using the following T ransact-SQL statements: CREATE TABLE [dbo] . [EmployeeBonus]( [EmpNumber] [INT] NOT NULL, [Quarterly] [tinyint] NULL, [HalfYearly] [tinyint] NULL [Yearly] [tinyint] NULL ) ON [PRIMARY] CREATE TABLE [dbo]. [BonusParameters]( [AvailableBonus] [money] NOT NULL, [CompanyPerformance] [tinyint] NOT NULL ) ON [PRIMARY] The tables are used to compute a bonus for each employee. The EmployeeBonus table has a non- null value in either the Quarterly, HalfYearly or Yearly column. This value indicates which type of bonus an employee receives. The Bonus Parameters table contains one row for each calendar year that stores the amount of bonus money available and a company performance indicator for that year You need to calculate a bonus for each employee at the end of a calendar year. Which Transact-SQL statement should you use?

SELECT "Bonus" = CASE EmployeeBonus WHEN Quarterly=1 THEN (Quarterly * AvailableBonus * CompanyPerformance)/40 WHEN HalfYearly=1 THEN (HalfYearly * AvailableBonus * CompanyPerformance)/20 WHEN Yearly=1 THEN (Yearly * AvailableBonus * CompanyPerformance)/10 END FROM EmployeeBonus,BonusParameters

Your database contains a table named Customer that has columns named CustomerID and Name. You want to write a query that retrieves data from the Customer table sorted by Name listing 20 rows at a time. You need to view rows 41 through 60. Which Transact-SQL query should you create?

SELECT * FROM Customer ORDER BY Name OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY

You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) Or SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)

CORRECT TEXT You have a database that contains the tables shown in the exhibit. (Click the Exhibit button). Create Table OrderDetails ListPrice Money Not Null Quantity int Not Null Create Table Customers CustomerID int Not Null Pk FirstName varchar(100) Not Null LastName varchar(100) Not Null Create Table Orders OrderID int Not Null PK OrderDate datetime NotNull CustomerID int NotNull You need to create a query for a report. The query must meet the following requirements: NOT use object delimiters. Return the most recent orders first. Use the first initial of the table as an alias. Return the most recent order date for each customer. Retrieve the last name of the person who placed the order. Return the order date in a column named MostRecentOrderDate that appears as the last column in the report. The solution must support the ANSI SQL-99 standard. Which code segment should you use? To answer, type the correct code in the answer area.

SELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID = O.CustomerID GROUP BY C.LastName ORDER BY MostRecentOrderDate DESC

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button). OrderDetails (Table) ListPrice (Column Name) money (Data Type) Quantity (Column Name) int (Data Type) Customers (Table) CustomerID (Column Name) int (Data Type) FirstName (Column Name) varchar (Data Type) LastName (Column Name) varchar (Data Type) Orders (Table) OrderID (Column Name) int (Data Type) OrderDate (Column Name) datetime (Data Type) CustomerID (Column Name) int (Data Type) You need to create a query for a report. The query must meet the following requirements: NOT use object delimiters. Use the first initial of the table as an alias. Return the most recent order date for each customer. Retrieve the last name of the person who placed the order. The solution must support the ANSI SQL-99 standard. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. SELECT LastName MAX(OrderDate) As MostRecentOrderDate

SELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID=O.CustomerID GROUP BY C.Lastname ORDER BY MAX (O.OrderDate) DESC

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM ( SELECT SalesOrderId, SalesAmount FROM DomesticSalesOrders UNION ALL SELECT SalesOrderId, SalesAmount FROM InternationalSalesOrders ) AS p

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a nonclustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

SELECT COUNT(*)FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.) Orders (SALES)(Tablename) OrderID(Column) OrderDate(Column) SalesTerritoryID(Column) TotalDue(Column) Details (SALES)(Tablename) OrdedD(Column) SttesTerritoryID(Column) ProductID(Column) UnitPrice(Column) OrderQty(Column) DiscountAmount(Column) ExtendedAmount(Column) ProductCatalog (SALES) CatID(Column) CatName (Column) ProductID(Column) ProdName(Column) UnitPrice(Column) You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements: Return rows ordered by descending values in the UnitPrice column. Use the Rank function to calculate the results based on the UnitPrice column. Return the ranking of rows in a column that uses the alias PriceRank. Use two-part names to reference tables. Display the columns in the order that they are defined in the table. The PriceRank column must appear last. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 1. SELECT CatID, CatName, Product ID, ProdMame, 2. FROM Sales.ProductCatalog 3. ORDER BY PriceRank UnitPrice,

SELECT CatID, CatName, ProductID, ProdName, UnitPrice, RANK (ORDER BY UnitPrice DESC) OVER () AS PriceRank FROM Sales.ProductCatalog ORDER BY PriceRank

You administer a Microsoft SQL Server database that supports a shopping application. You need to retrieve a list of customers who live in territories that do not have a sales person. Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

SELECT CustomerID FROM Customer WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson) or SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions: CREATE TABLE Customer (CustomerlD int NOT NULL PRIMARY KEY, CustomerName varchar(50) NOT NULL) CREATE TABLE Orders (OrderlD int NOT NULL PRIMARY KEY, CustomerlD int NOT NULL FOREIGN KEY REFERENCES Customer (CustomerlD), OrderAmount money NOT NULL, ShippmgCountry varchar(50) NOT NULL) Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders Which Transact-SQL query do you use?

SELECT CustomerID, CustomerName, ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o. ShippingCountry, RANK() OVER (PARTITION BY c. CustomerID ORDER BY o. OrderAmount DESC) AS Rnk FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1

You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements: Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. Results must include customers who have not placed any orders. Which Transact-SQL query should you use?

SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

You develop a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerId. You need to create a query that meets the following requirements: Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. Results must not include customers who have not placed any orders. Which Transact-SQL query should you use?

SELECT CustomerName, OrderDate FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)Customers(Table) CustomerID(Column) Name(Column) Country(Column) Orders(table) OrderId(Column) OrderDate(Column) Amount(Column) You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format. <Customers Name = "Customer A" Country="Australia"> <Orderld>1</0rderld> <OrderDate>2000-01-01T00:00:00</CrderDate> <Amount>3400.00</Amount> </Customers> <Customers Name = " Customer A" Country="Australia"> <Orderld>2</0rderld> <OrderDate>2001-01-01T00:00: 00</0rderDate> <Amount>4300.00</Amount> </Customers> Which Transact-SQL query should you use?

SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) Customers(Table) CustomerID(Column) Name(Column) Country(Column) Orders(table) OrderId(Column) OrderDate(Column) Amount(Column) <Customers> <Name>Customer A</Name> <Country>Australia</Country> <Orders> <Orderld>l</Orderld> <OrderDate>2000-01-01T00:00:00</OrderDate> <Amount>3400.00</Amount> </Orders> <Orders> <Orderld>2</Orderld> <OrderDate>2001-01-01T00:00:00</OrderDate> <Amount>4300.00</Amount> </Orders> </Customers> Which Transact-SQL query should you use?

SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) Customer(table) CustomerID(Column) Name(Column) Country(Column) Orders(table) OrderId(Column) OrderDate(Column) Amount(Column) You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format. <CUSTOMERS Name="Customer A" Country="Australia"> <ORDERS OrderID="1" OrderDate="2001-01-01" Amount="3400.00" /> <ORDERS OrderID="2" OrderDate="2002-01-01" Amount="4300.00" /> </CUSTOMERS> Which Transact-SQL query should you use?

SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

CORRECT TEXT You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.) Orders (SALES)(Table) OrderID (Column Name) PK OrderDate (Column Name) SalesTerritoryID (Column Name) TotalDue (Column Name) Details (SALES)(Table) OrderID (Column Name) FK to Orders.OrdersID SalesTerritoryID (Column Name) ProductID (Column Name) FK to ProductCatalog (SALES).ProductID UnitPrice (Column Name) OrderQty (Column Name) DiscountAmount (Column Name) ExtendedAmount (Column Name) ProductCatalog (SALES)(Table) CatID (Column Name) CatName (Column Name) ProductID (Column Name) PK ProdName (Column Name) UnitPrice (Column Name) You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements: Use one-part names to reference columns. Sort the order of the results from OrderId. NOT depend on the default schema of a user. Use an alias of TotalSales for the calculated ExtendedAmount. Display only the OrderId column and the calculated TotalSales column. Which code segment should you use?

SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details GROUP BY OrderID ORDER BY OrderID

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) Customer(table) CustomerID(Column) Name(Column) Country(Column) Orders(table) OrderId(Column) OrderDate(Column) Amount(Column) You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format. <Orders OrderId = "1" OrderDate = "2000-01-01T00:00:00" Amount = "3400.00"> <Customers Name = "Customer A" Country = "Australia" /> </Orders> <Orders OrderId = "2" OrderDate = "2001-01-01T00:00:00" Amount = "4300.00"> <Customers Name = "Customers A" Country = "Australian /> </Orders> Which Transact-SQL query should you use?

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) Customer(table) CustomerID(Column) Name(Column) Country(Column) Orders(table) OrderId(Column) OrderDate(Column) Amount(Column) You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format. <Orders> <OrderId>l</OrderId> <OrderDate>2000-01-01T00:00:00</OrderDate> <Amount>3400.00</Amount> <Customers> <Name>Customer A</Name> <Country>Australia</Country> </Customers> </Orders> <Orders> <Orderld>2</Orderld> <OrderDate>2001-01-01T00:00:00</OrderDate> <Amount>4300.00</Amount> <Customers> <Name>Customer A</Name> <Country>Australia</Country> </Customers> </Orders> Which Transact-SQL query should you use?

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) Customers(TableName) CustomerID(Columns) PK Name(Columns) Country(Columns) Orders(TableName) OrderID(Columns) PK CustomerID(Columns)FK OrderDate(Columns) Amount You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format: <row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer A" Country="Australia" /> <row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer A" Country="Australia" /> Which Transact-SQL query should you use?

SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW

You need to create a query that calculates the total sales of each OrderID from a table named Sales.Details. The table contains two columns named OrderID and ExtendedAmount. The solution must meet the following requirements: Use one-part names to reference columns. Order the results by OrderID with the smallest value first. NOT depend on the default schema of a user. Use an alias of TotalSales for the calculated ExtendedAmount. Display only the OrderID column and the calculated TotalSales column. Provide the correct code in the answer area. Key Words:

SELECT OrderId, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details ORDER BY OrderID ASC

You have a database that contains the tables as shown below: OrderDetails(Tablename) LisTPrice(Column Name) money (Data Type) not null Quantity(Column Name) int (Data Type) not null Customers(Tablename) CustomerID(Column Name) int (Data Type) not null PK FirstName(Column Name) varchar(100) (Data Type) not null LastName(Column Name) varchar(100)(Data Type) not null Orders(Tablename) OrderID(Column Name) int (Data Type) not null FK OrderDate(Column Name) datetime (Data Type) not null FK CustomerID(Column Name) int (Data Type) not null You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements: The code must NOT use aliases. The code must NOT use object delimiters. The objects called in Procedure1 must be able to be resolved by all users. OrderDate must be compared to Parameter1 after the data type of Parameter1 is changed to datetime. Which SELECT statement should you use? To answer, type the correct code in the answer area.

SELECT Orders.OrderID FROM Orders WHERE Orders.OrderDate>CONVERT(datetime,@Parameter1)

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.) Orders (SALES)(Table) OrderID (Column Name) PK OrderDate (Column Name) SalesTerritoryID (Column Name) TotalDue (Column Name) Details (SALES)(Table) OrderID (Column Name) FK to Orders.OrdersID SalesTerritoryID (Column Name) ProductID (Column Name) FK to ProductCatalog (SALES).ProductID UnitPrice (Column Name) OrderQty (Column Name) DiscountAmount (Column Name) ExtendedAmount (Column Name) ProductCatalog (SALES)(Table) CatID (Column Name) CatName (Column Name) ProductID (Column Name) PK ProdName (Column Name) UnitPrice (Column Name) You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements: UnitPrice must be returned in descending order. The query must use two-part names to reference the table. The query must use the RANK function to calculate the results. The query must return the ranking of rows in a column named PriceRank. The list must display the columns in the order that they are defined in the table. PriceRank must appear last. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. SELECT CatID, CatName, ProductID, ProdName, UnitPrice FROM Sales.ProductCatalog ORDER BY PriceRank

SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC

You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.) Orders(Sales)(Table_name) OrderID(Column) PK OrderDate(Column) Sales TerritoryID(Column) TotalDue(Column) Details(Sales)(Table_name) OrderID(Column) FK Sales TerritoryID(Column) ProductID(Column) UnitPrice(Column) OrderQty(Column) DiscountAmount(Column) ExtendedAmount(Column) ProductCatalog(Sales)(Table-name) CatID(Column) CatName(Column) ProductID(Column) PK ProdName(Column) UnitPrice(Column) You need to create a query that returns a list of products from Sales ProductCatalog The solution must meet the following requirements ■ UnitPrice must be returned in descending order. • The query must use two-part names to reference the table • The query must use the RANK function to calculate the results • The query must return the ranking of rows in a column named PriceRank. ■ The list must display the columns in the order that they are defined in the table ■ PriceRank must appear last Which code segment should you use? To answer., type the correct code in the answer area

SELECT ProductCatalog.CatID, ProductCatalog.CatName, ProductCatalog.ProductID, ProductCatalog.ProdName, ProductCatalog.UnitPrice, RANK() OVER (ORDER BY ProductCatalog.UnitPrice DESC) AS PriceRank FROM Sales.ProductCatalog ORDER BY ProductCatalog.UnitPrice DESC

You support a database structure shown in the exhibit. (Click the Exhibit button.) SalesPerson(TableName) SalesPersonID Pk Name city Country Sales(TableName) SalesID Pk SalesPersonID FK Amount SalesDate You need to write a query that displays the following details: Total sales made by sales people, year, city, and country Sub totals only at the city level and country level A grand total of the sales amount Which Transact-SQL query should you use?

SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.) Orders (SALES)(Table) OrderID (Column Name) PK OrderDate (Column Name) SalesTerritoryID (Column Name) TotalDue (Column Name) Details (SALES)(Table) OrderID (Column Name) FK to Orders.OrdersID SalesTerritoryID (Column Name) ProductID (Column Name) FK to ProductCatalog (SALES).ProductID UnitPrice (Column Name) OrderQty (Column Name) DiscountAmount (Column Name) ExtendedAmount (Column Name) ProductCatalog (SALES)(Table) CatID (Column Name) CatName (Column Name) ProductID (Column Name) PK ProdName (Column Name) UnitPrice (Column Name) You need to create a query that meets the following requirements: References columns by using one-part names only. Groups aggregates only by SalesTerritoryID, and then by ProductID. Orders the results in descending order by SalesTerritoryID and then by ProductID in descending order for both. Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. SELECT SalesTerritorylD, ProductlD, AVG(UnitPrice), MAX(OrderQty), MAX ( DiscountAmount) FROM Sales.Details

SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty) MAX(DiscountAmount) FROM Sales.Details GROUP BY SalesTerritoryID, ProductID ORDER BY SalesTerritoryID DESC, ProductID DESC

You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.) Orders(Sales)(Table_name) OrderID(Column) PK OrderDate(Column) Sales TerritoryID(Column) TotalDue(Column) Details(Sales)(Table_name) OrderID(Column) FK Sales TerritoryID(Column) ProductID(Column) UnitPrice(Column) OrderQty(Column) DiscountAmount(Column) ExtendedAmount(Column) ProductCatalog(Sales)(Table-name) CatID(Column) CatName(Column) ProductID(Column) PK ProdName(Column) UnitPrice(Column) You have the following query: SELECT SalesTerritorylD, ProductlD, AVG(UnitPrice) , MAX (OrderQty) , MAX(DiscountAmount) FROM Sales.Details You need to recreate the query to meet the following requirements ■ Reference columns by using one-part names only. ■ Sort aggregates by SalesTerritorylD, and then by ProductlD ■ Order the results in descending order from SalesTerritorylD to ProductlD - The solution must use the existing SELECT clause and FROM clause Which code segment should you use? To answer, type the correct code in the answer area.

SELECT SalesTerritoryID, ProductID, AVG(UnitPrice), MAX(OrderQty), MAX(DiscountAmount) FROM Sales.Details GROUP BY SalesTerritoryID , ProductID ORDER BY SalesTerritoryID DESC, ProductID DESC

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid- year marks for students. The table has marks obtained by 50 students for various subjects. You need to retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid- year marks for students. The table has marks obtained by 50 students for various subjects. You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. Which Transact-SQL query should you use?

SELECT StudentCode as Code, NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid- year marks for students. The table has marks obtained by 50 students for various subjects. You need to ensure that the following requirements are met: Students must be ranked based on their average marks. If one or more students have the same average, the same rank must be given to these students. Consecutive ranks must be skipped when the same rank is assigned. Which Transact-SQL query should you use?

SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?

SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use?

SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Territory ORDER BY Year) AS NextProfit FROM Profits

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions: CREATE TABLE Customer (CustomerlD int NOT NULL PRIMARY KEY, CustomerName varchar(50) NOT NULL) CREATE TABLE Orders (OrderlD int NOT NULL PRIMARY KEY, CustomerlD int NOT NULL FOREIGN KEY REFERENCES Customer (CustomerlD), OrderAmount money NOT NULL, ShippingCountry varchar(50) NOT NULL) Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders Which Transact-SQL query do you use?

SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1

How do you find all employees which are also manager? .

SELECT e.name, m.name FROM Employee e, Employee m WHERE e.mgr_id = m.emp_id;

If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as

SELECT result sets or the RAISERROR and PRINT statements

You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

SET XACT_ABORT ON

You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a runtime error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

SET XACT_ABORT ON

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

SET XACT_ABORT ON

Explain how the SQL Server Engine uses an Index Allocation Map (IAM)?

SQL Server Engine uses an Index Allocation Map (IAM) to keep an entry for each page to track the allocation of these available pages. The IAM is considered as the only logical connection between the data pages, that the SQL Server Engine will use to move through the heap.

In terms of security, parameters that appear in the code cannot be considered part of the code—they can only be considered operands in expressions. So, by using parameters, you can eliminate your exposure to ________________________

SQL injection.

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create?

SQL user without login

Index and Parameter Hints: FORCESEEK

Seeks at the index or table

--to display last 5 Records from Employee table

Select * From Customer where CustomerID >= (Select (max(CustomerID)-5) from Customer)

How to select all the odd number records from a table?

Select * from table where id % 2 != 0

How to select all the even number records from a table?

Select * from table where id % 2 = 0

--to locate duplicates

Select Concat(Firstname, ' ', LastName) AS FullName, EmailAddress, Count(*) As NumberOFDuplicates From Customer Group By FirstName, LastName, EmailAddress Having Count(*) > 1;

--to locate highest yearlyincome

Select CustomerID, CONCAT(FirstName, ' ', LastName) As FullName, YearlyIncome From Customer c where 1 = (Select COUNT(Distinct YearlyIncome) From Customer e Where c.YearlyIncome <= e.YearlyIncome)

You need to create a query that calculates the total sales of each OrderID from a table named Sales.Details. The table contains two columns named OrderID and ExtendedAmount. The solution must meet the following requirements: Use one-part names to reference columns. Start the order of the results from OrderID. NOT depend on the default schema of a user. Use an alias of TotalSales for the calculated ExtendedAmount. Display only the OrderID column and the calculated TotalSales column. Provide the correct code in the answer area.

Select OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details GROUP BY OrderID ORDER BY OrderID

Can you write a query to get employee names ending with a vowel?

Select empid, empname from employee where empname like '%[aeiou]'

--to locate table, schema, and Column name

Select t.name As Table_Name, Schema_name(schema_id) As Schema_Name, c.name As Column_Name From sys.tables t Inner Join sys.columns c on t.object_id = c.object_id where c.name like '%salary%' Order by Schema_Name, Table_Name;

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

Set the Merge agent on the problem subscribers to use the slow link agent profile.

How do you identify the shared memory and semaphores of a particular DB instance if there are running multiple servers?

Set the following parameters to distinguish between the in-memory resources of a DB instance. 1. SETMYPID 2. IPC 3. TRACEFILE_NAME

What are the pros and cons of using ONLINE index creation or rebuilding options?

Setting the ONLINE option to ON when you create or rebuild the index will enable other data retrieving or modification processes on the underlying table to continue, preventing the index creation process from locking the table. On the other hand, the ONLINE index creation or rebuilding process will take longer time than the offline

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements: Status information must be logged to a status table. If the status table does not exist at the beginning of the batch, it must be created. Which object should you use?

Stored procedure

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements: Takes an input variable Returns a table of values Cannot be referenced within a view Which object should you use?

Stored procedure

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical. You write the following Transact-SQL query: INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders You need to optimize transaction logging and locking for the statement. Which table hint should you use?

TABLOCK

A trigger is considered part of the transaction that includes the event that caused the trigger to fire. Issuing a ROLLBACK TRAN command within the trigger's code causes a rollback of all changes that took place in the trigger, and also of all changes that took place in the transaction associated with the trigger.

TRUE

In the case of instead of triggers, the inserted and deleted tables contain the rows that were supposed to be affected by the modification that caused the trigger to fire.

TRUE

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements: Returns a result set based on supplied parameters. Enables the returned result set to perform a join with a table. Which object should you use?

Table-valued user-defined function

What is the difference between Delete, Truncate and Drop command?

The difference between the Delete, Truncate and Drop command is Delete command is a DML command, it is used to delete rows from a table. It can be rolled back. Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cant be rolled back. Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows). All the tables' rows, indexes, and privileges will also be removed.

What is the difference between Cluster and Non-Cluster Index?

The difference between the clustered and non-clustered index in SQL is as follows: Clustered Index: It is used for easy retrieval of data from the database and it is faster. One table can only have one clustered index It alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index. Non-Clustered Index: It is slower compared to the Clustered index. One table can have multiple non clustered index It doesn't alter the way it was sorted but it creates a separate object within a table which points back to the original table rows after searching.

How do you tune a query using the explain plan?

The explain plan shows a complete output of the query costs including each subquery. The cost is directly proportional to the query execution time. The plan also depicts the problem in queries or sub-queries while fetching data from the query.

What are the tuning indicators Oracle proposes?

The following high-level tuning indicators are available to establish if a database is experiencing bottlenecks or not: 1. Buffer Cache Hit Ratio. It uses the following formula. Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads Action: Advance the DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) to improve the hit ratio. 2. Library Cache Hit Ratio. Action: Advance the SHARED_POOL_SIZE to increase the hit ratio.

What is wrong with the following query? SELECT empName FROM employee WHERE salary <> 6000

The following query will not fetch record with the salary of 6000 but also will skip the record with NULL. As per SQL Server logic, it works on 3 values in matching conditions. TRUE or FALSE and UNKNOWN. Here, NULL implies UNKNOWN. to fix this: SELECT empName FROM employee WHERE salary is NULL or salary <> 6000

What are all the different Normalization?

There are different types of Normalization forms in SQL. First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce and Codd Normal Form (BCNF)

What are all the different types of indexes?

There are three types of indexes 1. Unique Index: Unique Indexes helps maintain data integrity by ensuring that no two rows of data in a table have identical key values. A unique index can be applied automatically when a primary key is defined. It ensures that the values in the index key columns are unique. 2. Clustered Index: Clustered Index reorders the physical order of the table and search based on the key values. There will be only one clustered index per table. 3. Non-Clustered Index: Non-Clustered Index doesn't alter the physical order of the table and maintains a logical order of the data. Each table can have many non-clustered indexes.

What are all types of user-defined functions?

There are three types of user-defined function, namely • Scalar Functions • Inline Table-valued functions • Multi-statement valued functions Scalar functions return unit, variant defined the return clause. Inline Table-valued functions and Multi-statement valued functions return table as a return.

Where does the I/O statistics per table exist in Oracle?

There is a report known as UTLESTAT which displays the I/O per tablespace. But it doesn't help to find the table which has the most I/O.

What is the difference between UNIQUE and PRIMARY KEY constraints?

There should be only one PRIMARY KEY in a table whereas there can be any number of UNIQUE Keys. PRIMARY KEY doesn't allow NULL values whereas Unique key allows NULL values.

EVENTDATA.

This function returns information about server or database events.

a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view.

This means it is possible for a SELECT statement using NOLOCK to be blocked.

In SQL Server 2008, the LOCK_ESCALATION option of ALTER TABLE can disfavor table locks, and enable HoBT locks on partitioned tables.

This option is not a locking hint, but can but used to reduce lock escalation.

How doe Triggers in SQL Server fire

Triggers in SQL Server fire per statement and not per modified row.

A scalar-valued would only be able to return a single simple value, not a table.

True

A stored procedure cannot be used within a view.

True

A user-defined data type would not be able to take an input parameter.

True

Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

True

Columns of the xml data type must be equal. All columns must be either typed to an XML schema or untyped. If typed, they must be typed to the same XML schema collection.

True

EXEC accepts a character string in parentheses as input and executes the batch of code within the character string.

True

Errors trapped by a CATCH block are not returned to the calling application.

True

If you create temporary tables or declare table variables, they are created in tempdb, but you cannot connect directly to tempdb and explicitly create user objects there.

True

In SQL Database, you can connect directly only to the system database master

True

In an on-premises installation of SQL Server, you can connect directly to the system databases master, model, tempdb, and msdb.

True

In the case of instead of triggers, INSERT, UPDATE, and DELETE actions can be invoked by the INSERT, UPDATE, and DELETE statements, as well as by the MERGE statement.

True

Infrequent blocking is okay, but frequent blocks require a design check of the database or queries.

True

Inline Table valued functions Is Updatable

True

Inline Table valued functions can accepts Parameters

True

Inline Table valued functions can expanded out by Optimiser

True

MultiStatement Table valued functions can accepts Parameters - Yes

True

MultiStatement Table valued functions can contain Multiple Statements - Yes

True

The best way to efficiently reuse query execution plans is to use stored procedures with parameters. This way, even when parameter values change, the query string remains the same.

True

The definitions of the columns that are part of a UNION operation don't have to be the same, but they must be compatible through implicit conversion.

True

The sp_executesql stored procedure was introduced after the EXEC command. It is more secure and more flexible in the sense that it has an interface; that is, it supports input and output parameters.

True

To determine whether a view column is deterministic, use the IsDeterministic property of the COLUMNPROPERTY function.

True

When you use TABLESAMPLE against databases that are upgraded to SQL Server, the compatibility level of the database is set to 110 or higher, PIVOT is not allowed in a recursive common table expression (CTE) query.

True

You work with the TRY. . .CATCH construct by placing the usual T-SQL code in a TRY block (between the BEGIN TRY and END TRY keywords), and all the error-handling code in the adjacent CATCH block (between the BEGIN CATCH and END CATCH keywords).

True

if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

True

you can't refer to column aliases assigned in the SELECT clause in query clauses that are logically processed prior to the SELECT clause (for example, WHERE or GROUP BY).

True

views can be Materialized in advance

True (through indexed views)

You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns: Id bigint EntryDateTime datetime Summary nvarchar(max) Id is the Primary Key. You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime. Which Transact-SQL statement should you use?

UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID

Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. You need to increase the values in the Price column of all products in the Products table by 5 percent. You also need to log the changes to the ProductsPriceLog table. Which Transact-SQL query should you use?

UPDATE Products SET Price = Price * 1.05 OUTPUT inserted.ProductCode, deleted.Price, inserted.Price INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)

-- Uses AdventureWorks SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) XXXXX SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO What do you place in XXXXX

Union

__________________ Combines the results of two or more queries into a single result set. This set includes all the rows that belong to all queries in the __________________.

Union

Which type of indexes are used to maintain the data integrity of the columns on which it is created?

Unique Indexes, by ensuring that there are no duplicate values in the index key, and the table rows, on which that index is created.

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.) SELECT h.SalesOrderlD, h.TotalDue, d.OrderQty FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderlD = d.SalesOrderlD WHERE h.TotalDue > 100 AND (d.OrderQty > 5 CR d.LineTotal < 1000.00); You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail You need to improve the performance of the query. What should you do?

Update statistics on SalesOrderId on both tables.

You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the stored procedure with multiple event names and their dates as parameters. What should you do?

Use a user-defined table type.

You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

Use the DATETIMEOFFSET data type.

You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. What should you do?

Use the Database Engine Tuning Advisor.

You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report. You need to display dates and times in several international formats. What should you do?

Use the FORMAT function.

You develop a database application. You create four tables. Each table stores different categories of products. You create a Primary Key field on each table. You need to ensure that the following requirements are met: The fields must use the minimum amount of space. The fields must be an incrementing series of values. The values must be unique among the four tables. What should you do?

Use the UNIQUEIDENTIFIER data type along with NEWSEQUENTIALID()

You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. What should you do?

Use the VARBINARY data type.

Can you get employee details whose department id is not valid or department id not present in department table?

Using Left JOIN SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E left outer join DEPARTMENT d on E.DEPTID = D.DEPTID WHERE D.DEPTID IS NULL Using NOT IN SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E where e.deptid not in (select deptid from department) Using NOT Exists SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E where NOT EXISTS (select deptid from department where e.deptid=depart "Not In" is the least recommended, considering performance. Outer join and Not Exists are preferred. Using EXCEPT KEYWORD if you want to list Department IDs only. INTERSECT and EXCEPT keywords have rules SELECT deptid FROM EMPLOYEE EXCEPT SELECT DEPTID FROM DEPARTMENT

Can you write a query to get employee names starting with a vowel?

Using like operator and expression, Select empid, empname from employee where empname like '[aeiou]%'

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime. The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements: Remove all duplicates of the Products table based on the ProductName column. Retain only the newest Products row. Which Transact-SQL query should you use?

WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON p.ProductName = cte.ProductName AND p.CreatedDateTime < cte.CreatedDateTime

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime. The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements: Remove all duplicates of the Products table based on the ProductName column. Retain only the newest Products row. Which Transact-SQL query should you use?

WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime

What is composite index?

When 2 or more columns are related to each other in the table and the same columns are used in where condition of the query then user can create index on both columns.These indexes are known as composite indexes.

What is parser?

When SQL Statement has been written and generated then first step is parsing of that SQL Statement.Parsing is nothing but checking the syntaxes of SQL query.All the syntax of Query is correct or not is checked by SQL Parser.

Explain Rule Based Optimizer?

When we execute any SQL statement ,the optimizer uses the predefined rules which defines what indexes are present in the database and which indexes needs to be executed during the execution.Rule Based optimizer is used to specify which table is been full scanned and which tables are taking the indexes during the execution.In Earlier the only optimizer which is used by Oracle is Rule Based optimizer "Rule Based Optimizer specifies the rules for how to execute the query."

What is mean by implicit index.Explain with example.

Whenever we define unique key or primary key constraints on the table the index will automatically create on the table.These indexes are known as implicit indexes because these are created implicitly whenever the constraint has been applied to the table.These indexes are normal indexes not unique indexes.The indexes are normal because the columns already have defined as unique so uniqueness is already been applied.

You can use ____________________ expressions to extract event attributes such as post time, event type, and logon name from the XML value.

XQuery

Can you show one row twice in results from a table?

Yes. We can use union all or cross join to obtain this. select deptname from department d where d.deptname='it' union all select deptname from department d1 where d1.deptname='it' -- also cross join alias same table select d.deptname from department d, department d1 where d.deptname='it'

How do you confirm if a tablespace has disproportionate fragmentation?

You can confirm it by checking the output of SELECT against the dba_free_space table. If it points that the no. of a tablespaces extents is more than the count of its data files, then it proves excessive fragmentation.

You are developing an SQL Server database for an automobile manufacturer. The manufacturer maintains the list of vehicles sold and vehicles that have been recalled. The tables are shown below: VehiclesSold(Table) Vin(Column) Model(Column) Year(Column) State(Column) You have the following Transact-SQL code. Line numbers are included for reference only. MERGE VehiclesToRecal1 AS recall USING VehiclesSold AS sold ON recall.VIN = sold.VIN WHEN NOT MATCHED BY TARGET THEN INSERT(VIN, Model, Year, State) VALUES(sold.VIN, sold.Model, sold.Year, sold.State WHEN MATCHED THEN UPDATE SET recall.VIN = sold.VIN WHEN NOT MATCHED BY SOURCE THEN DELETE; You must update the VehiclesToRecall table with the list of vehicles that were recalled in 2014. You must maximize the performance of the operation. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

You must add the following Transact-SQL code at line 04: AND sold.Year = 2014 AND recall.Year = 2014 You must add the following Transact-SQL code at line 06: AND sold.Year = 2014

The Transact SQL CREATE TRIGGER command creates

a DML, DDL, or logon trigger.

If the collection of hinted indexes do not include all columns referenced by the query,

a fetch is performed to retrieve the remaining columns after the SQL Server Database Engine retrieves all the indexed columns.

The Resource database is

a hidden, read-only database that holds the definitions of all system objects. When you query system objects in a database, they appear to reside in the sys schema of the local database, but in actuality their definitions reside in the Resource database.

Group by: CUBE generates

a result set that shows aggregates for all combinations of values in the selected columns,

Overriding a locking level can significantly impede concurrent access to __________________________

a table or index.

DML events

are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

DDL triggers

are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

Temporal tables

are history tables which record all data changes,complete with the date and time they occurred.

Note that if a TRY. . .CATCH block captures and handles an error,

as far as the caller is concerned, there was no error.

The offset_row_count

can be a constant, variable, or parameter that is greater or equal to zero.

FOR SYSTEM_TIME

can be specified independently for each table in a query. It can be used inside common table expressions, table-valued functions and stored procedures.

SQL Server allows you to construct a batch of T-SQL code as a character string and then execute that batch. This capability is called ____________________

dynamic SQL.

You can use XQuery expressions to extract

event attributes such as post time, event type, and logon name from the XML value.

If the batch job has a large number of updates, obtaining an ____________________on the table may be the best way to ensure the batch job finishes efficiently.

exclusive lock

The CATCH block usually includes ___________________ that inspects the error number to determine what course of action to take.

flow code

What is an alternative for TOP clause in SQL?

here can be two alternatives for the top clause in SQL. #1 -- Alternative - ROWCOUNT function Set rowcount 3 Select * from employee order by empid desc Set rowcount 0 #2 -- Alternative and WITH and ROWNUMBER function -- between 1 and 2 With EMPC AS ( SELECT empid, empname,salary, ROW_NUMBER() OVER (order by empid desc) as RowNumber FROM employee ) select * from EMPC Where RowNumber Between 1 and 7

In Parallel Data Warehouse, the ORDER BY clause is invalid

in VIEWS, CREATE TABLE AS SELECT, INSERT SELECT, inline functions, derived tables, subqueries and common table expressions, unless TOP is also specified.

A multiple index hint also enforces

index ANDing, and the query optimizer applies as many conditions as possible on each index accessed.

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements: Takes an input parameter Returns a table of values Can be referenced within a view Which object should you use?

inline table-valued function

In the trigger's code, you can access tables called

inserted and deleted that contain the rows that were affected by the modification that caused the trigger to fire.

The master database holds

instance-wide metadata information, server configuration, information about all databases in the instance, and initialization information.

An instead of trigger fires

instead of the event it is associated with and can be defined on permanent tables and views.

The APPLY operator allows you to

invoke a table-valued function for each row returned by an outer table expression of a query.

A temporal table

is a new type of table that provides correct information about stored facts at any point in time.

What is Summary advisor and what type of information does it provide?

is a tool for filtering and materializing the views. It can help in elevating the SQL performance by selecting the proper set of materialized views for a given workload. And it also provides data about the Materialized view recommendations.

The setting of SET NOCOUNT

is set at execute or run time and not at parse time.

The best measure you can take against SQL injection

is to avoid concatenating user input as part of your code

The quickest way to Update Statistics on tables

is using stored procedure EXEC sp_updatestats

Why does sql server use locks

it uses it to implement isolation between transactions

The CROSS JOIN

joined every row from the first table (T1) with every row from the second table (T2).

The system databases that the setup program creates include

master, Resource, model, tempdb, and msdb

GROUPING SETS specifies

multiple groupings of data in one query. Only the specified groups are aggregated, instead of the full set of aggregations that are generated by CUBE or ROLLUP .

You can use UNIQUE constraints to make sure that

no duplicate values are entered in specific columns that do not participate in a primary key.

: Explain a SQL Server deadlock, how a deadlock can be identified, how it is a performance problem and some techniques to correct deadlocks.

o A deadlock is a situation where 2 spids have data locked and cannot release their lock until the opposing spid releases their lock. Depending on the severity of the deadlock, meaning the amount of data that is locked and the number of spids that are trying to access the same data, an entire chain of spids can have locks and cause a number of deadlocks, resulting in a performance issue. o Deadlocks can be identified by Profiler in either textual, graphical or XML format. Additional information: Capturing Deadlock Information in XML Format and How To: Graphical Deadlock Chain o Deadlocks are a performance problem because they can prevent 2 or more processes from being able to process data. A deadlock chain can occur and impact hundreds of spids based on the data access patterns, number of users, object dependencies, etc. o Deadlocks could require a database design change, T-SQL coding change to access the objects in the same order, separating reporting and OLTP applications, including NOLOCK statements in SELECT queries that can accept dirty data, etc.

: What is a query plan and what is the value from a performance tuning perspective?

o A query plan is the physical break down of the code being passed to the SQL Server optimizer. o The value from a performance tuning perspective is that each component of the query can be understood and the percentage of resource utilization can be determined at a micro level. As query tuning is being conducted, the detailed metrics can be reviewed to compare the individual coding techniques to determine the best alternative.

Why is it important to avoid functions in the WHERE clause?

o Because SQL Server will scan the index or the table as opposed to seeking the data. The scan operation is a much more costly operation than a seek. o Often a slightly different approach can be used to prevent using the function in the WHERE clause yielding a favorable query plan and high performance.

How can I/O statistics be gathered and reviewed for individual database files?

o By using the fn_virtualfilestats function to capture the metrics. o This process can be automated with a script to determine the file usage with numerous samples.

Name three different options to capture the input (code) for a query in SQL Server.

o DBCC INPUTBUFFER o fn_get_sql o sys.dm_exec_sql_text

FOR XML Options

o ELEMENTS - reformats from attributes to elements o (), ELEMENTS - NOT AVAILABLE o Names of row elements are automatically named for the table the columns orginate o ROOT - adds a ROOT to the stream making it well - formed XML o ROOT() - changes the name of the ROOT from "ROOT"

True or False - It is always beneficial to configure TempDB with an equal number of fixed sized files as the number of CPU cores.

o False - With always being the operative word in the question. o Depending on the version of SQL Server, the disk subsystem, load, queries, etc., a 1 to 1 ratio of files to cores may be necessary on high end SQL Servers with intense processing. o If you do not have that luxury, a starting point may to be have half the number of tempdb files as compared to CPU cores. o This is a configuration to load test and monitor closely depending on the type of processing, load, hardware, etc. that your SQL Server is expected to support.

True or False - A LEFT OUTER JOIN is always faster than a NOT EXISTS statement.

o False - With always being the operative word. Depending on the situation the OUTER JOIN may or may not be faster than a NOT EXISTS statement. It is necessary to test the techniques, review the query plans and tune the queries accordingly.

Name five different tools which can be used for performance tuning and their associated purpose.

o Performance Monitor\System Monitor - Tool to capture macro level performance metrics. Additional information Automate Performance Monitor Statistics Collection for SQL Server and Windows Windows Reliability and Performance Monitor to troubleshoot SQL Server o Profiler - Tool to capture micro level performance metrics based on the statements issued by a login, against a database or from host name. Additional information: Tip Category - Profiler and Trace o Server Side Trace - System objects to write the detailed statement metrics to a table or file, similar to Profiler. Additional information: SQL Server Performance Statistics Using a Server Side Trace o Dynamic Management Views and Functions - SQL Server objects with low level metrics to provide insight into a specific portion of SQL Server i.e. the database engine, query plans, Service Broker, etc. Additional information: Dynamic Management Views\Functions o Management Studio's Built-In Performance Reports - Ability to capture point in time metrics as pre-defined by Microsoft. Additional information: Built-In Performance Reports in SQL Server 2005 o Custom scripts - Custom scripts can be developed to monitor performance, determine IO usage, monitor fragmentation, etc. all in an effort to improve performance. o Third party applications - Performance monitoring and tuning applications from vendors in the SQL Server community. Additional information: SQL Server Performance Monitoring Tools

Explain three different approaches to capture a query plan.

o SHOWPLAN_TEXT o SHOWPLAN_ALL o Graphical Query Plan o sys.dm_exec_query_optimizer_info o sys.dm_exec_query_plan o sys.dm_exec_query_stats ________________________________________

Explain how the hardware running SQL Server can help or hinder performance.

o Taxed CPUs will queue requests and hinder query performance. o Insufficient memory could cause paging resulting in slow query performance. o Incorrectly configured disk drives could exacerbate IO problems. o Additional information: Hard Drive Configurations for SQL Server and Hardware 101 for DBAs

Explain the NOLOCK optimizer hint and some pros\cons of using the hint.

o The NOLOCK query hint allows SQL Server to ignore the normal locks that are placed and held for a transaction allowing the query to complete without having to wait for the first transaction to finish and therefore release the locks. o This is one short term fix to help prevent locking, blocking or deadlocks. o However, when the NOLOCK hint is used, dirty data is read which can compromise the results returned to the user.

Please explain why SQL Server does not select the same query plan every time for the same code (with different parameters) and how SQL Server can be forced to use a specific query plan.

o The query plan is chosen based on the parameters and code being issued to the SQL Server optimizer. Unfortunately, a slightly different query plan can cause the query to execute much longer and use more resources than another query with exactly the same code and only parameter differences. o The OPTIMIZE FOR hint can be used to specify what parameter value we want SQL Server to use when creating the execution plan. This is a SQL Server 2005 and beyond hint.

Explain why the NORECOMPUTE option of UPDATE STATISTICS is used.

o This command is used on a per table basis to prevent the table from having statistics automatically updated based on the 'Auto Update Statistics' database configuration. o Taking this step will prevent UPDATE STATISTICS from running during an unexpected time of the day and cause performance problems. o By setting this configuration it is necessary to manually UPDATE STATISTICS on a regular basis.

How is it possible to capture the IO and time statistics for your queries?

o Use the SET STATISTICS IO and SET STATISTICS TIME settings in your queries or enable the settings in your Management Studio session.

A user database holds

objects and data for an application.

A blocking lock occurs when

one lock causes another process to wait in a holding queue.

what are the ways in sql server tracks down the queries that are causing deadlocks

one of the options is to use the sequence of a trace flag 1222 to write the deadlock information to the sequence of an error log for that to happen we must first turn on that trace flag in sql server

CROSS APPLY returns

only rows from the outer table that produce a result set from the table-valued function.

Table hints

override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options.

The sp_executesql stored procedure can perform better than EXEC because its ___________________ aids in reusing cached execution plans.

parameterization

a database application uses a lookup table that is updated weekly in a batch process. Concurrent readers access the table with a shared (S) lock and the weekly batch update accesses the table with an exclusive (X) lock. Turning off page and row locking on the table will do what?

reduces the locking overhead throughout the week by allowing readers to concurrently access the table through shared table locks. When the batch job runs, it can complete the update efficiently because it obtains an exclusive table lock.

The ERROR_NUMBER function

returns an integer with the number of the error and is probably the most important of the error functions.

The ERROR_MESSAGE function

returns error message text.

The CROSS APPLY operator

returns rows from the primary (outer) table only if the table-value function produces a result set. ...

The ERROR_LINE function

returns the line number where the error happened.

The ERROR_PROCEDURE function

returns the name of the procedure in which the error happened and returns NULL if the error did not happen within a procedure.

SQL Server user-defined functions are

routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

--How to fetch 3rd highest salary using Rank Function

select * from (Select Dense_Rank() over ( order by YearlyIncome desc) as Rnk, a.* from Customer a) As Rank where Rnk = 2;

Can you write a query to find employees with age greater than 30?

select * from employee where datediff(year,dob, getdate()) >30

Write a query to get employees whos ID is an odd number.

select * from employee where empid %2 !=0

Write a query to get employees whos ID is even.

select * from employee where empid %2 =0

index ORing is allowed for a table with an index hint specified.

False

Locking Hints: TABLOCK

Force locks at the table level

Locks occur during

reads, insertions, updates and deletes

If the TRY block has no error, control is passed to the corresponding CATCH block.

False

Inline Table valued functions can be Materialized in advance - No

False

Inline Table valued functions can contain Multiple Statements

False

Inline Table valued functions can have triggers

False

The OFFSET clause is mandatory while the FETCH clause is optional.

True

Inline Table valued functions can use side-effecting operator - No

False

MultiStatement Table valued functions can be expanded out by Optimiser - No

False

MultiStatement Table valued functions can be materialized in advance - No

False

MultiStatement Table valued functions can have triggers

False

MultiStatement Table valued functions is Updatable - No

False

NOEXPAND applies only to

indexed views.

What is Database Black Box Testing?

Database Black Box Testing involves Data Mapping Data stored and retrieved Use of Black Box techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA)

What is Database White Box Testing?

Database White Box Testing involves Database Consistency and ACID properties Database triggers and logical views Decision Coverage, Condition Coverage, and Statement Coverage Database Tables, Data Model, and Database Schema Referential integrity rules

You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?

Decimal

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that a large amount of memory is consumed by single-use dynamic queries. You need to reduce procedure cache usage from these statements without creating any additional indexes. What should you do?

Enable the optimize for ad hoc workloads option.

Locking Hints: PAGELOCK

Force locks at the page level

What is the difference between Local Variables and Global Variables?

Local Variables: Local variables can be used or exist only inside the function. These variables are not used or referred by any other functions. These are not known to other functions. Variables can be created whenever that function is called. Global Variables: Global variables can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

What are Explicit Indexes?

The indexes which is created by user are called as explicit indexes.You can say the indexes which are created by 'Create Index' statement are called as Explicit indexes.

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met: Future modifications to the table definition will not affect the applications' ability to access data. The new object can accommodate data retrieval and data modification. You need to achieve this goal by using the minimum amount of changes to the applications. What should you create for each application?

Views

How do you analyze an explain plan?

While analyzing the explain plan, check the following areas. 1. Driving Table 2. Join Order 3. Join Method 4. Unintentional cartesian product 5. Nested loops, merge sort, and hash join 6. Full Table Scan 7. Unused indexes 8. Access paths

Each temporal table consists of two tables

actually, one for the current data and one for the historical data. The system ensures that when the data changes in the table with the current data the previous values are stored in the historical table

An after trigger fires

after the event it is associated with finishes and can only be defined on permanent tables.

OUTER APPLY returns

both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the tablevalued function.

SQL Server supports the association of triggers with two kinds of events

data manipulation events (DML triggers) such as INSERT, and data definition events (DDL triggers) such as CREATE TABLE.

To read error log what store procedure should use

execute sp_readerrorlog

By default the deadlock information is not written to the _________

error log

If the TRY block has no error,

the CATCH block is simply skipped.

To skip the first 10 products and return the rest, you use

the OFFSET clause

The deleted table holds

the old image of the affected rows in the case of DELETE and UPDATE actions.

DML triggers execute

when a user tries to modify data through a data manipulation language (DML) event.