Querying Microsoft SQL Server 2012 (70-461) – Exam Preparation Questions – Part 2

What will be the default output of following T-SQL statement?

SELECT CEILING(16.45);

What will be the correct output of following T-SQL statement?

CREATE TABLE tbl_Test(Col1 int IDENTITY(1,1) PRIMARY KEY,Col2 varchar(20));

INSERT tbl_Test VALUES (‘Football’),(‘Cricket’),(‘Rugby’);

SELECT @@IDENTITY;

DROP TABLE tbl_Test;

Which of the below statements is true about the SQL Server 2012 Index?

You need to insert all the records from Table called “Sales” to unindexed backup table called “BK_Sales” using INSERT INTO BK_Sales SELECT * FROM Sales statement . You need to optimize transaction logging and locking for the insert statement. Which table hint should you use to achieve this goal?

Which of the below statements is incorrect about the ROW or PAGE compression in SQL Server 2012?

What will be the correct output of following T-SQL statement?

DECLARE @value1 int = 10, @value2 int = 9;

SELECT IIF ( @value1 > @value2,’TRUE’,’FALSE’);

What will be the correct output of following T-SQL statement?

DECLARE @value1 int;

SET @value1 = 100;

SET @value1 -= 5;

SELECT @value1;

How do you select all the columns from a table named “Employees” where the “First_Name” starts with an “t” or the “Last_Name” ends with an “g”?

Which statement concerning the Tables in SQL Server 2012 below is INCORRECT?

You’re planning to modify an existing table called “Manager” in your current database. Which of the following syntax can be used to rebuilds a primary key index called “PK_Manager_ID” on the Manager table?

Which of the following function would you consider using to get the line number at which an error occurred that caused the CATCH block of a TRY CATCH construct?

Which date and time function can be used to return an integer that represents the year of the specified date?

What will be the correct output of following T-SQL statement?

DECLARE @expression nvarchar(100) =’ Querying SQL Server 2012′;

SELECT LTRIM(@expression);

Which of the following is the correct output for the following T-SQL statement?

DECLARE @w int = 10, @x int = 20, @y int = 30, @z int = null;

SELECT IIF ( @w <= @x OR @y >= @z, ‘TRUE’, ‘FALSE’ );

Which of the below statements is true about the T-SQL SET STATISTICS XML ON Statement ?