Find Complete and recently updated Correct Question and answers of Microsoft SQL Server. All Answers updated regularly with new questions. Upwork Microsoft SQL Server test answers of 2016.

Question:* Which of the following aggregate functions are supported by the Transact-SQL language?

Answer: • All of these are supported

Question:* In which context is the ROW_NUMBER() function valid?

Answer: • select ... ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;

Question:* Which of the following storage options can be used to store LOBs and save storage?

Answer: • Both FILESTREAM and Sparse columns

Question:* Which of the following relationships requires a reference table?

Answer: • Many to many

Question:* Which of the following is not a valid string constant or hexadecimal constant?

Answer: • 'New York"

Question:* What is the name of the database that gets created in SQL Server 2012 when you create an "Integration Services Catalog"?

Answer: • SSISDB

Question:* Which statement is allowed inside of a User Defined Function?

Answer: • DECLARE

Question:* Which of the following is NOT an aggregate function in SQL Server?

Answer: • RANK

Question:* Which Transact-SQL statement should you include at the beginning of a stored procedure to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back?

Answer: • SET XACT_ABORT ON

Question:* What does DBCC stand for?

Answer: • Database Consistency Commands

Question:* True or False? Every row in a FileTable represents a file or a directory.

Answer: • True

Question:* To Improve stored procedure performance,which statement below is used to write beginning of SP?

Answer: • SET NOCOUNT ON

Question:* What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?

Answer: • SSIS

Question:* Which transaction isolation level allows dirty reads?

Answer: • READ UNCOMMITED

Question:* Which sql function finds the length of a string field?

Answer: • LEN

Question:* When creating indexes in SQL, what two main factors must be considered?

Answer: • Performance versus disk space

Question:* What is the default isolation level in SQL Server?

Answer: • Read Commited

Question:* What is the "WITH RECOMPILE" statement used for?

Answer: • Causes SQL Server to rebuild the query plan for a stored procedure on every call

Question:* Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function

Answer: • When casting to desired data type failed it will result NULL

Question:* What is a "CROSS APPLY" used for?

Answer: • Performing joins between calculated sets

Question:* Consider the following queries: A: select * from [tbl1] union select * from [tbl2]; B: select * from [tbl1] union all select * from [tbl2]; What is the difference between these two queries?

Answer: • Query A will return both result sets with duplicates removed; Query B will return both full result sets.

Question:* Which of the following statements will remove a UDF?

Answer: • DROP FUNCTION

Question:* You can create index on views.

Answer: • True

Question:* When you are dealing with lots of NULL Values in columns,which datatype is better?

Answer: • Variable Length

Question:* Which of the following allows you to modify (update or delete) all values of the parent table?

Answer: • CASCADE

Question:* Which of the following are not default database schemas?

Answer: • pte

Question:* Which of the following is a benefit of SQL Server replication?

Answer: • All of these

Question:* a FOREIGN KEY constraint can reference a UNIQUE constraint

Answer: • True

Question:* Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?

Answer: • All of these

Question:* The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.

Answer: • SQL_VARIANT

Question:* What is the basic difference between varchar and nvarchar?

Answer: • nvarchar supports unicode data.

Question:* Differential backups do what?

Answer: • Copy only the information that has changed since the last full backup

Question:* What is order of precedence of order by group by?

Answer: • group by then order by

Question:* True or false: A stored procedure can recursively call itself.

Answer: • True

Question:* What are the 3 Transact-SQL statements that are related to authorization?

Answer: • GRANT, DENY, and REVOKE

Question:* To run Schedule Job in SQL Server, which services used?

Answer: • SQL Server Agent Service

Question:* Data Transformation Services do what?

Answer: • They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats

Question:* What clause allows you to filter the result of a column that has been aggregated?

Answer: • HAVING

Question:* True or False? A table can have more than one primary key.

Answer: • False

Question:* What is the role of a processadmin user?

Answer: • Manages processes running in an instance of SQL Server

Question:* In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.

Answer: • True

Question:* What does the "IN" clause do?

Answer: • Compares a specified value against a list of values and is true if a match is found

Question:* What does the Query Analyzer, or SQL Server Management Studio, tool do?

Answer: • All of these

Question:* In this statement, "SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ", the JOIN term is the same as:

Answer: • INNER JOIN

Question:* Which of the following acts as the Database Engine's security system?

Answer: • Both Windows security and SQL Server security

Question:* Which is the slowest?

Answer: • TABLE scan

Question:* What is CTE

Answer: • Common Table Expresssion

Question:* To find all employees whose name ends with 'ith' complete this sql statement: "SELECT * FROM employees WHERE name " ...

Answer: • LIKE "%ith"

Question:* A sysadmin user is allowed to perform what actions?

Answer: • Any action in SQL server

Question:* What is a cursor in SQL Server?

Answer: • A special T-SQL construct used to iterate through the individual records returned by a query

Question:* Which clause would you use to perform conditional filtering on aggregate data in a query that uses "GROUP BY"?

Answer: • HAVING

Question:* What does "UDF" stand for?

Answer: • User Defined Function

Question:* Which of the following statements are related to transactions?

Answer: • All of these

Question:* "Primary Key" can only be created on a column that doesn't accept NULL

Answer: • True

Question:* Which statement adds a row to a table?

Answer: • INSERT INTO

Question:* Which of the following indices does NOT change the physical order of the rows in the table?

Answer: • Nonclustered Indices

Question:* What is the default port of Microsoft SQL Server (for TCP)?

Answer: • 1433

Question:* What is the correct syntax for a CTE?

Answer: • with myCTE ([column]) as (select ...) select [column] from myCTE;

Question:* A sysadmin password requires at least 8 characters and which of the following character types?

Answer: • All of these

Question:* Which of the following best describes the flow for the execution of a CLR stored procedure?

Answer: • Source code > Object code > Executable code > Procedure as database object

Question:* True or False? The EXEC keyword and the EXEC() function are equivalent to each other.

Answer: • False

Question:* Given a table with the following schema: create table table1 (field1 int) The table contains 1000 records. What would the following sql statement result in: update top (100) table1 set field1 = 1

Answer: • Update the field1 column to the value 1 in only 100 records in the table

Question:* "SELECT UNIQUE city FROM customers" will return what data set?

Answer: • An error.

Question:* Which of the following statements prevents users from preforming actions?

Answer: • DENY

Question:* A relationship that identifies that a column contains primary key values from another table is referred as what?

Answer: • Foreign key

Question:* Which of the following stores FileStream data?

Answer: • FileTable

Question:* The system database that holds information on SQL Server Agent Jobs is?

Answer: • msdb

Question:* True or False? You cannot execute dynamic SQL code from a function.

Answer: • True

Question:* "Unique Constraint" can be created on a column that accepts NULL

Answer: • True

Question:* You are querying a table that contains a field with the data type XML and need only rows that have a particular value in the XML data. Which of the following would not result in an error

Answer: • SELECT * FROM #table1 WHERE xml_field1.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE '%smith%'

Question:* All Database Backup/Restore Related information stored in which system database?

Answer: • MSDB

Question:* Which of the following SQL Server built-in functions is nondeterministic?

Answer: • GETDATE()

Question:* Which of the following is used to change tracking?

Answer: • Use triggers or use change data capture (CDC)

Question:* A ________ is an alphanumerical, hexadecimal, or numberic constant.

Answer: • literal value

Question:* Which of the following statements removes one or more previously granted or denied permissions?

Answer: • REVOKE

Question:* GETDATE() is a ....... function?

Answer: • Nondeterministic

Question:* True or false: You can modify data in a table using a User Defined Function.

Answer: • False

Question:* Which versions of SQL Server support table partitioning?

Answer: • Enterprise edition

Question:* The LAG analytic function is used in a ______ statement to compare values in the current row with values in a previous row.

Answer: • SELECT

Question:* Which of the following operators allows the specification of two or more expressions to be used for a query search?

Answer: • IN

Question:* In SQL Server 2012 introduces what new feature?

Answer: • Always On

Question:* True or false? Adding TOP to a select always improves performance.

Answer: • False

Question:* True or false? Intersection tables should have an autoincrement column as the primary index

Answer: • False

Question:* The command DBCC CHECKDB allows you to do what?

Answer: • Check the average page density

Question:* Which of the following is not considered a Boolean Operator?

Answer: • IN

Question:* What would be the output of following statement: Select * from tbl_Employee where Emp_Name=NULL

Answer: • Header without any data

Question:* What is a collection of zero or more continuous CircularString and/or LineString instances of either geometry or geography types called?

Answer: • CompoundCurve

Question:* When retrieving data, which command may be used to rotate a set of column names from horizontal across the top to vertically, appearing as values among the result set

Answer: • UNPIVOT

Question:* What does the EOMONTH function do?

Answer: • Adds the specified number of months to the input start date and then returns the last day of the month for the resulting date

Question:* SQL Server will create an Unique Non Clustered index, by default, when a Primary Key is created

Answer: • False

Question:* Which of the following is not a valid data type in SQL Server 2012?

Answer: • smalldatetime2

Question:* Can you select the built in Server Roles for SQL Server from the list below?

Tuesday, December 15, 2015

Microsoft SQL Server test answers of 2016.

Question:* Which of the following aggregate functions are supported by the Transact-SQL language?

Answer: • All of these are supported

Question:* In which context is the ROW_NUMBER() function valid?

Answer: • select ... ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;

Question:* Which of the following storage options can be used to store LOBs and save storage?

Answer: • Both FILESTREAM and Sparse columns

Question:* Which of the following relationships requires a reference table?

Answer: • Many to many

Question:* Which of the following is not a valid string constant or hexadecimal constant?

Answer: • 'New York"

Question:* What is the name of the database that gets created in SQL Server 2012 when you create an "Integration Services Catalog"?

Answer: • SSISDB

Question:* Which statement is allowed inside of a User Defined Function?

Answer: • DECLARE

Question:* Which of the following is NOT an aggregate function in SQL Server?

Answer: • RANK

Question:* Which Transact-SQL statement should you include at the beginning of a stored procedure to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back?

Answer: • SET XACT_ABORT ON

Question:* What does DBCC stand for?

Answer: • Database Consistency Commands

Question:* True or False? Every row in a FileTable represents a file or a directory.

Answer: • True

Question:* To Improve stored procedure performance,which statement below is used to write beginning of SP?

Answer: • SET NOCOUNT ON

Question:* What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?

Answer: • SSIS

Question:* Which transaction isolation level allows dirty reads?

Answer: • READ UNCOMMITED

Question:* Which sql function finds the length of a string field?

Answer: • LEN

Question:* When creating indexes in SQL, what two main factors must be considered?

Answer: • Performance versus disk space

Question:* What is the default isolation level in SQL Server?

Answer: • Read Commited

Question:* What is the "WITH RECOMPILE" statement used for?

Answer: • Causes SQL Server to rebuild the query plan for a stored procedure on every call

Question:* Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function

Answer: • When casting to desired data type failed it will result NULL

Question:* What is a "CROSS APPLY" used for?

Answer: • Performing joins between calculated sets

Question:* Consider the following queries: A: select * from [tbl1] union select * from [tbl2]; B: select * from [tbl1] union all select * from [tbl2]; What is the difference between these two queries?

Answer: • Query A will return both result sets with duplicates removed; Query B will return both full result sets.

Question:* Which of the following statements will remove a UDF?

Answer: • DROP FUNCTION

Question:* You can create index on views.

Answer: • True

Question:* When you are dealing with lots of NULL Values in columns,which datatype is better?

Answer: • Variable Length

Question:* Which of the following allows you to modify (update or delete) all values of the parent table?

Answer: • CASCADE

Question:* Which of the following are not default database schemas?

Answer: • pte

Question:* Which of the following is a benefit of SQL Server replication?

Answer: • All of these

Question:* a FOREIGN KEY constraint can reference a UNIQUE constraint

Answer: • True

Question:* Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?

Answer: • All of these

Question:* The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.

Answer: • SQL_VARIANT

Question:* What is the basic difference between varchar and nvarchar?

Answer: • nvarchar supports unicode data.

Question:* Differential backups do what?

Answer: • Copy only the information that has changed since the last full backup

Question:* What is order of precedence of order by group by?

Answer: • group by then order by

Question:* True or false: A stored procedure can recursively call itself.

Answer: • True

Question:* What are the 3 Transact-SQL statements that are related to authorization?

Answer: • GRANT, DENY, and REVOKE

Question:* To run Schedule Job in SQL Server, which services used?

Answer: • SQL Server Agent Service

Question:* Data Transformation Services do what?

Answer: • They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats

Question:* What clause allows you to filter the result of a column that has been aggregated?

Answer: • HAVING

Question:* True or False? A table can have more than one primary key.

Answer: • False