Elance Microsoft SQL Server Test Answers 2015
·
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
Subscribe to:
Posts (Atom)
