Which of the following aggregate functions are supported by the Transact-SQL language?
MIN
AVG
MAX
All of these are
supported
True or False? Every row in a FileTable represents a file or a directory.
True
False
Which of the following is used to change tracking?
Use triggers
None of these
Use triggers or
use change data capture (CDC)
Use change data capture (CDC)
What does DBCC stand for?
Database Communication Checker
Database Clustering Checker
Database
Consistency Commands
Database Connection Checker
What is a "CROSS APPLY" used for?
Synchronizing changes between two databases
Updating a report file
Performing joins
between calculated sets
Setting user permissions
Which sql function finds the length of a string field?
STRLEN
LENGTH
LEN
a FOREIGN KEY constraint can reference a UNIQUE constraint
False
True
Which of the following statements will remove a UDF?
DROP FUNCTION
ALTER FUNCTION
CREATE FUNCTION
All of these
Which of the following is a benefit of SQL Server replication?
Load Processing
Redundancy
Offline Processing
All of these
What does COALESCE do?
Combines multiple T-SQL statements into one.
Evaluates the
arguments in order and returns the current value of the first expression that
initially does not evaluate to NULL.
The same as UNION ALL.
The same as RIGHT JOIN.
The same as LEFT JOIN.
What is the default isolation level in SQL Server?
Serializable
Repeatable Read
Snapshot
Read Commited
Which of the following are not default database schemas?
dbo
pte
guest
sys
Differential backups do what?
Backs up only the information in the selected
FileTable
Copy only the
information that has changed since the last full backup
Backs up all the information in the SQL Server
None of these
What is the basic difference between varchar and nvarchar?
nvarchar supports large blob.
varchar supports unicode data.
nvarchar
supports unicode data.
nvarchar performs faster then varchar.
True or false: A stored procedure can recursively call itself.
True
False
The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.
TIMESTAMP
FILESTREAM
SQL_VARIANT
UNIQUEIDENTIFIER
What is order of precedence of order by group by?
group by then
order by
order by then group by
True or False? A table can have more than one primary key.
True
False
What clause allows you to filter the result of a column that has been aggregated?
HAVING
WHERE
NOT IN
NOT LIKE
Data Transformation Services do what?
They provide a window into the inner workings of
the database
They are used to control the MSSQLServer, MSDTC,
and SQLServerAgent processes
They provide an
extremely flexible method for importing and exporting data between a Microsoft
SQL Server and other formats
They are the main administrative console for SQL
Server installations
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?
Query A will
return both result sets with duplicates removed; Query B will return both full
result sets.
Query A will return both full result sets; Query B
will return both result sets with duplicates removed.
Query A and B return the same result set.
Which transaction isolation level allows dirty reads?
SERIALIZABLE
READ UNCOMMITED
SNAPSHOT
READ COMMITED
You can create index on views.
False
True
What is the "WITH RECOMPILE" statement used for?
Causes the SQL engine to recompile itself from
source
Converts a SQL statement into machine code
Binds a CLR static method to a stored procedure
Causes SQL
Server to rebuild the query plan for a stored procedure on every call
What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?
Data Import & Export
SSIS
Analysis Services
Backup Restore
Which of the following allows you to modify (update or delete) all values of the parent table?
SET NULL
NO ACTION
CASCADE
SET DEFAULT
To run Schedule Job in SQL Server, which services used?
SQL Server Agent
Service
Option 1 & 2
None of the above
SQL Server Reporting Service
SQL Server Browser Service
When creating indexes in SQL, what two main factors must be considered?
Performance
versus disk space
Memory versus disk space
Tables versus functions
Network bandwidth versus memory
Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?
SMALLDATETIME2FROMPARTS
TIMEFROMPARTS
SMALLDATETIMEFROMPARTS
DATEFROMPARTS
All of these
When you are dealing with lots of NULL Values in columns,which datatype is better?
Fixed Length
Variable Length
What are the 3 Transact-SQL statements that are related to authorization?
ADMIT, REMOVE, REVOKE
ALLOW, DENY, REVOKE
ALLOW, DENY, REMOVE
GRANT, DENY, and
REVOKE
In this statement, "SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ", the JOIN term is the same as:
LEFT INNER JOIN
INNER JOIN
FULL OUTER JOIN
LEFT OUTER JOIN
What does the "IN" clause do?
Used to specify which table to iterate over in a
loop using T-SQL
Compares a
specified value against a list of values and is true if a match is found
Searches a file for records belonging to a table
Checks whether a table contains a reference to
another table
What is the role of a processadmin user?
Manages
processes running in an instance of SQL Server
Executes the BULK INSERT statement
Manages server logins
Creates and alters databases
In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.
True
False
Database Engine default Isolation Level
Read committed
Serializable
Read uncommitted
Repeatable read
What is the default port of Microsoft SQL Server (for TCP)?
1434
1343
1143
1443
1433
What does the Query Analyzer, or SQL Server Management Studio, tool do?
Quickly pulls information out of a database in
response to a user request
All of these
Tests queries before implementing them in other
applications
Creates/modifies stored procedures and execute
administrative tasks
In which context is the ROW_NUMBER() function valid?
select ROW_NUMBER();
select ... inner join [tbl] on ROW_NUMBER() =
[tbl].[id];
select ... ROW_NUMBER() as [row] ... group by
[group_val] order by [id];
select ...
ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;
Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function
When casting to desired data type failed it will
result Error
When casting to desired data type failed it will
result 0
When casting to
desired data type failed it will result NULL
When casting to desired data type failed it will
result Empty
To Improve stored procedure performance,which statement below is used to write beginning of SP?
SET NOCOUNT OFF
SET STATISTICS IO ON
SET NOCOUNT ON
SET STATISTICS IO OFF
SET STATISTICS TIME ON
Which of the following indices does NOT change the physical order of the rows in the table?
Clustered Indices
Both Nonclustered Indices and Clustered Indices
Nonclustered
Indices
Which of the following acts as the Database Engine's security system?
Both Windows
security and SQL Server security
Windows security
SQL Server security
Which is the slowest?
INDEX scan
TABLE scan
To find all employees whose name ends with 'ith' complete this sql statement: "SELECT * FROM employees WHERE name " ...
LIKE "%ith%"
LIKE "_ith"
IN ('ith')
LIKE
"%ith"
A sysadmin user is allowed to perform what actions?
Set user accounts
Edit databases
Any action in
SQL server
Change security settings
What is the default port number for SQL Server?
1433
5022
1434
5023
What is CTE
Common Table Excess
Common Table Experiences
Common Table
Expresssion
Common Table Express
What is a cursor in SQL Server?
A special T-SQL
construct used to iterate through the individual records returned by a query
A collection of icons that can be applied to the
mouse pointer on the screen
A remote connection to another SQL Server
A user interface component contained within the SQL
Server Management Studio application
Which clause would you use to perform conditional filtering on aggregate data in a query that uses "GROUP BY"?
CONTAINS
HAVING
WHERE
HAS
FILTER BY
Which of the following statements are related to transactions?
All of these
ROLLBACK [WORK]
COMMIT [WORK]
BEGIN TRANSACTION
"Primary Key" can only be created on a column that doesn't accept NULL
True
False
What does "UDF" stand for?
User Data Form
Universal Data File
User Defined
Function
USB Device File
User Data Filter
Which statement adds a row to a table?
ADD
UPDATE
RESERT
INSERT INTO
What is the correct syntax for a CTE?
with select [column] from myCTE ([column]) as
(select ...);
select [column] from cte myCTE ([column]) as
(select ...);
with myCTE
([column]) as (select ...) select [column] from myCTE;
using cte myCTE ([column]) as (select ...) select
[column] from myCTE;
A sysadmin password requires at least 8 characters and which of the following character types?
All of these
Uppercase letters
Non-alphabetic
Lowercase letters
The system database that holds information on SQL Server Agent Jobs is?
master
msdb
tempdb
model
True or False? The EXEC keyword and the EXEC() function are equivalent to each other.
True
False
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?
SET ARITHABORT ON
SET XACT_ABORT
ON
TRY
SET XACT_ABORT OFF
"SELECT name FROM t1 UNION SELECT name FROM t2 " is the same as "SELECT DISTINCT name FROM t1 UNION ALL SELECT DISTINCT name FROM t2"
True
False
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
Produce and error
Update the
field1 column to the value 1 in only 100 records in the table
Update the field1 column to the value 1 in all 1000
records in the table
GETDATE() is a ....... function?
Server Level
Database Level
Deterministic
Nondeterministic
User Defined
Which of the following SQL Server built-in functions is nondeterministic?
CONVERT()
LEN()
COUNT()
GETDATE()
ISNULL()
Which of the following is NOT an aggregate function in SQL Server?
AVG
STDEV
COUNT
SUM
RANK
"Unique Constraint" can be created on a column that accepts NULL
False
True
A ________ is an alphanumerical, hexadecimal, or numberic constant.
delimiters
figurative value
literal value
identifiers
A relationship that identifies that a column contains primary key values from another table is referred as what?
Associate key
Foreign key
Alien key
Join key
Sibling key
Which of the following statements removes one or more previously granted or denied permissions?
REVOKE
DENY and REVOKE
REVOKE and REMOVE
REMOVE
All Database Backup/Restore Related information stored in which system database?
Distribution Database
MSDB
MODEL
TEMPDB
MASTER
"SELECT UNIQUE city FROM customers" will return what data set?
Unique values of city from the customers table.
An error.
Unique values of customer from the table city.
Which of the following best describes the flow for the execution of a CLR stored procedure?
Source code >
Object code > Executable code > Procedure as database object
Source code > Object code > Procedure as
database object
Source code > Executable code > Procedure as
database object
Object code > Executable code > Procedure as
database object
TRUE OR FALSE: Is it possible to execute a Stored Procedure in a UDF.
False
TRUE
Which of the following storage options can be used to store LOBs and save storage?
None of these
Only in Sparce columns
Both FILESTREAM
and Sparse columns
Only in FILESTREAM
True or False? You cannot execute dynamic SQL code from a function.
False
True
Which versions of SQL Server support table partitioning?
Enterprise, Standard editions
Enterprise
edition
Enterprise, Standard, Express editions
Web edition
Which statement is allowed inside of a User Defined Function?
TRY....CATCH
EXECUTE
DELETE
DECLARE
INSERT
True or false? Adding TOP to a select always improves performance.
True
False
Which of the following statements prevents users from preforming actions?
REVOKE
REVOKE and REMOVE
DENY and REVOKE
DENY
True or false: You can modify data in a table using a User Defined Function.
False
True
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
SELECT * FROM
#table1 WHERE xml_field1.value('(/PageContent/Text)[1]', 'varchar(100)') LIKE
'%smith%'
Both methods would return an error
Neither method would return an error
SELECT * FROM #table1 where xml_field1 like
'%smith%'
In SQL Server 2012 introduces what new feature?
Always On
Log Shipping
Online Index Rebuilds
Online Index Reorgs
Mirroring
Which of the following stores FileStream data?
Filegroup
Directory
FileTable
None of these
Which of the following relationships requires a reference table?
Hierarchical
Many to many
One to one
One to many
The LAG analytic function is used in a ______ statement to compare values in the current row with values in a previous row.
PREVIOUS_ROW
LEAD
FIRST_VALUE
SELECT
Can a unique key with not null constraint make it a primary key?
FALSE
TRUE
True or false? Intersection tables should have an autoincrement column as the primary index
True
False
What is the name of the database that gets created in SQL Server 2012 when you create an "Integration Services Catalog"?
SSISCatalog_Temp
SSISDB
SSISTempDB
SSISCatalog
SSISDB_Temp
Which of the following operators allows the specification of two or more expressions to be used for a query search?
IN
WHERE
OR
AND
SQL Server will create an Unique Non Clustered index, by default, when a Primary Key is created
True
False
Which of the following is not a valid string constant or hexadecimal constant?
'Boston'
'02143'
"Somerville, MA 02143"
'New York"
Which of the following is not considered a Boolean Operator?
IN
WHERE
AND
OR
What would be the output of following statement: Select * from tbl_Employee where Emp_Name=NULL
Data of employees where name = null
Syntax error
1
Header without
any data
The query will return incorrect value
The command DBCC CHECKDB allows you to do what?
Locate the database
None of these
Check the amount of pages
Check the
average page density
What is a collection of zero or more continuous CircularString and/or LineString instances of either geometry or geography types called?
CompoundString
CurveString
CompoundCurve
MultiString
StringCollection
Which of the following is not a valid data type in SQL Server 2012?
datetimeoffset
smallmoney
datetime2
smalldatetime2
hierarchyid
What does the EOMONTH function do?
Adds the specified number of weeks to the input
start date and then returns the last day of the month specified
Adds the
specified number of months to the input start date and then returns the last
day of the month for the resulting date
Returns the last day of the month specified
None of these
Can you select the built in Server Roles for SQL Server from the list below?
diskadmin
backupoperator
accessadmin
datawriter
ddladmin
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
UNPIVOT
There is no specialized command for performing this
operation in SQL Server, however effect can be duplicated using other means
PIVOT
The SETCERT.EXE utility can be used to do what?
All of these
Create a security certificate for the SQL server
Specify the
certificate used by the SQL server
Cross check a security certificate with the owner
organization
By default, SQL Server treats NULL as what data type
INT
NVARCHAR
NULL does not have data type by default
BIT
UNIQUEIDENTIFIER
True or false? Foreign keys always point to other tables.
True
False
