Skip to content

SQL Server MCQ Questions

0
Answered
0
Correct
0%
Accuracy
Q1 Easy

Analyze the following code snippet for creating a stored procedure in SQL Server 2008:CREATE PROCEDURE Person.GetEmployees@LastName nvarchar(50),@FirstName nvarchar(50)ASSET NOCOUNT ONSELECT FirstName, LastName, JobTitle, DepartmentFROM Person.EmployeeDepartmentWHERE FirstName = @FirstName AND LastName = @LastNameGOWhich of the following is a valid code to execute the GetEmployees stored procedure?

EXECUTE Person.GetEmployees N'John', N'Miller'
EXEC Person.GetEmployees @LastName = N'Miller', @FirstName = N'John'
EXECUTE Person.GetEmployees @FirstName = N'John', @LastName = N'Miller'
All of the above
Q2 Easy

Which of the following data types are supported by SQL Server 2008?

date
datetime
datetime1
datetime2
Q3 Easy

The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type

varchar
boolean
int
tinyint
Q4 Easy

What is the return type of the value returned by @@DATEFIRST in SQL Server 2008?

datetime
date
int
tinyint
Q5 Easy

Which of the following commands is used to run a Transact-SQL script file by using sqlcmd?

sqlcmd -S myServer\instanceName -i C:\newScript.sql
sqlcmd -S instanceName -o C:\newScript.sql
sqlcmd -S myServer\instanceName -r C:\newScript.sql
sqlcmd -S instanceName -i C:\newScript.sql
Q6 Easy

What is the default value of the SORT_IN_TEMPDB clause of the relational_index_option specified at the time of creation of an index on a table in SQL Server 2008?

ON
OFF
TRUE
FALSE
Q7 Easy

Which of the following namespaces is used to manage the audit configuration programmatically in SQL Server 2008?

Microsoft.SqlServer.Management.Smo
Microsoft.SqlServer.Server
Microsoft.sqlserver.management.common
Microsoft.sqlserver.connectioninfo
Q8 Easy

An identifier denoting a global temporary object in SQL Server 2008 starts with a ______ sign.

At sign(@)
Double number(##)
Underscore(_)
None of the above
Q9 Easy

Which of the following commands is used to save the output to a text file after you run a Transact-SQL script using sqlcmd?

sqlcmd -S instanceName -o C:\new.txt
sqlcmd -S instanceName -i C:\myScript.sql -o C:\new.txt
sqlcmd -S myServer\instanceName -o C:\new.txt
sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\new.txt
Q10 Easy

Which of the following default passwords is used by sqlcmd if -P option is used at the end of the command prompt without a password?

0000
NULL
1234
None of the above
Q11 Easy

Which of the following is a valid code to change the index created on a column of a table to page compression?

ALTER INDEX Index_ProductON Table1WITH ( DATA_COMPRESSION = PAGE )GO
ALTER INDEX Index_ProductON Table1SET ( DATA_COMPRESSION = PAGE )GO
ALTER INDEX Index_ProductON Table1REBUILD WITH ( DATA_COMPRESSION = PAGE )GO
ALTER INDEX Index_ProductON Table1REBUILD SET ( DATA_COMPRESSION = PAGE )GO
Q12 Easy

Analyze the following code snippet:DECLARE @myDate DATETIME2SELECT @myDate = "2009/10/08 12:35:29.2348 +12:15"SELECT @myDate AS "@myDate"What is the output of the above code?

2009-10-08 12:35:29
2009/10/08 12:35:29.2348000
2009-10-08 12:35:29.2348000
2009/10/08 12:35:29
Q13 Easy

Which of the following data types can be specified as SPARSE?

image
text
nchar
geography
Q14 Easy

What range is supported by the TIME data type in SQL Server 2008?

00:00:00.0000000 through 23:59:59.9999999
00:00:00.0000000 through 24:00:00.0000000
00:00:00.0000000 through 23:59:00.0000000
00:00:00.0000000 through 23:59:59.0000000
Q15 Easy

How many audit action groups are available for a server in SQL Server 2008 to perform different actions on audit data?

25
30
35
40
Q16 Easy

Analyze the following code which uses the TOP clause of SQL Server 2008:USE PersonGODECLARE @p AS intSELECT @p=10SELECT TOP(@p)FROM EmployeeGOWhat Will be the output of the above code?

No output is displayed.
A list of first 10 employees is displayed from the Person database.
An incorrect syntax error is displayed.
None of the above.
Q17 Easy

Which of the following is a valid syntax of the ALTER SERVER AUDIT statement used to enable a server audit?

ALTER SERVER AUDIT <audit_name> WITH (ENABLE=TRUE)
ALTER SERVER AUDIT <audit_name> SET (ENABLE=TRUE)
ALTER SERVER AUDIT <audit_name> WITH (STATE=ON)
ALTER SERVER AUDIT <audit_name> SET (STATE=ON)
Q18 Easy

Which of the following permissions are required to create a view in SQL Server 2008?

CREATE VIEW permission in database
ALTER permission on schema
CONTROL permission on object
Q19 Easy

Multiple CHECK constraints can be applied to a single column and a single CHECK constraint can be applied to multiple columns in SQL Server 2008.

True
False
Q20 Easy

What is the default timeout for login to sqlcmd while you try to connect to a server?

5 seconds
8 seconds
10 seconds
15 seconds
Q21 Easy

Which of the following compression features have been introduced in SQL Server 2008 that were NOT supported by SQL Server 2005?

Row-level compression
Page-level compression
Data file level compression
Backup level compression
Q22 Easy

Analyze the following code used to update a Common Table Expression:USE demodbGODECLARE @a TABLE (ID int, Value int);DECLARE @b TABLE (ID int, Value int);INSERT @a VALUES (1, 10), (2, 20);INSERT @b VALUES (1, 100),(2, 200);WITH cte AS (SELECT * FROM @a)UPDATE cteSET Value = b.ValueFROM cte AS aINNER JOIN @b AS b ON b.ID = a.IDSELECT * FROM @aGOWhat Will be the output of the above code?

ID    Value-----    -----1    1002    200
ID    Value-----    -----1    102    100
ID    Value-----    -----1    102    200
ID    Value-----    -----1    1002    100
Q23 Easy

What is the default maximum precision of decimal data type in SQL Server 2008?

16
18
38
48
Q24 Easy

All MDX, DMX and XML/A errors are displayed in the Error List window of the SQL Server 2008. Management Studio.

True
False
Q25 Easy

Which of the following is used to comment a portion of an SQL Server 2008 script?

Double asterisk (**)
Double forward slash (//)
Double dash (--)
Double back slash (\\)
Q26 Easy

Suppose you create a stored procedure and save it in the database with "sp_" prefix. Which of the following statements is true regarding the execution of the stored procedure?

The prefix will speed up the execution of the stored procedure.
The prefix will slow down the execution of the stored procedure.
The performance of the stored procedure is not affected
Q27 Easy

Which of the following is used by SQL Server 2008 Management Studio to execute scripts in regular mode in the Query Editor?

sqlcmd
Microsoft .NET Framework SqlClient
OLE DB provider
None of the above
Q28 Easy

Which of the following options are available for the DATA_COMPRESSION clause specified for a table in SQL Server 2008?

Page
Table
Row
Column
Q29 Easy

Which of the following features is used by SQL Server Audit to create and manage audits in SQL Server 2008?

System stored procedures
Triggers
SQL DDL syntax
None of the above
Q30 Easy

What is the data type of the parameter "Audit_file_offset" of function "fn_get_audit_file()" in SQL Server 2008?

varchar
nvarchar
int
bigint
Q31 Easy

Windows Security log can be specified as a target for Audit in SQL Server 2008.

True
False
Q32 Easy

User defined data type columns can be designated with ROWGUIDCOL.

True
False
Q33 Easy

Which function/functions are performed by the following statement? DBCC CHECKIDENT ("table_name", RESEED, new_reseed_value)

It checks the current iDentity fielD value of the specifieD table.
It sets the identity field value to the new reseed value.
It sets the identity field value to 1.
All of the above.
Q34 Easy

Which of the following stored procedures is used to delete data from the management data warehouse of a Data Collector?

core.sp_delete_data
core.sp_purge_data
core.sp_remove_data
None of the above
Q35 Easy

What is the function of DBCC CHECKDB command in SQL Server 2008?

It checks the allocation of all the objects in the specified database.
It checks the structural integrity of all the objects in the specified database.
It checks for consistency in and between system tables in the specified database.
It checks the consistency of disk space allocation structures of a specified database.
Q36 Easy

Which of the following Audit objects primarily describes the destination where the audit data of SQL Server 2008 gets stored?

Server Audit object
Server Audit Specification object
Database Audit Specification object
Q37 Easy

Which of the following commands is used to connect to a named instance of SQL Server using the sqlcmd utility of SQL Server?

sqlcmd -C myServer\instanceName
sqlcmd -S instanceName
sqlcmd -C instanceName
sqlcmd -S myServer\instanceName
Q38 Easy

PRIMARY KEY constraint defaults to __________ index.

Clustered
Nonclustered
None of the above
Q39 Easy

Starting from the lowest, arrange the following operators in the order in which they produce results in an SQL WHERE query. 1. >, >=, <, <= 2. <> 3. = 4. LIKE

1,4,3,2
4,1,2,3
3,2,1,4
2,4,1,3
Q40 Easy

Which of the following queries is used to rename a database in SQL Server 2008?

sp_renamedb oldname,newname
sp_renamedb newname,oldname
sp_dbrename oldname,newname
sp_rename oldname,newname
Q41 Easy

Analyze the following code snippet: DECLARE @myTime TIME(4) = "02:02:02.234567 +01:01" SELECT @myTime AS "@myTime" What is the output of the above code?

02:02:02.2345
02:02:02
02:02:02.2346
An error is displayed
Q42 Easy

Which of the following is the correct precedence order of the data types in SQL Server 2008? 1.xml 2.text 3.bigint 4.image 5.float 6.datetime

1,2,3,4,5,6
6,5,4,3,2,1
1,6,5,3,2,4
1,4,2,3,6,5
Q43 Easy

Which of the following commands is used to start the Resource Governor in SQL Server 2008?

ALTER Resource Governor Enable
ALTER Resource Governor Reconfigure
ALTER Resource Governor Set
None of the above
Q44 Easy

Which of the following options is used to change the value of the password variable and exit using sqlcmd?

-p
-P
-z
-Z
Q45 Easy

Which of the following spatial data types are used in SQL Server 2008?

Geography
Longitude
Altitude
Geometry
Q46 Easy

Which of the following formats are supported by the language_term argument while altering a full-text stoplist in SQL Server 2008?

Integer
Binary
Decimal
Hexadecimal
Q47 Easy

What is the maximum value for the sum of the values of MIN_CPU_PERCENT and MIN_MEMORY_PERCENT for all the resource pools of SQL Server?

50
75
100
150

About these SQL Server questions

Questions on Microsoft SQL Server 2008 and Transact-SQL: data types, constraints and indexes, the sqlcmd utility, data compression, SQL Server Audit, encryption, the Resource Governor and DBCC commands.

Topics: Data types, Tables, constraints and indexes, T-SQL queries, sqlcmd, Administration and security, and Maintenance.

What the SQL Server test covers and how to prepare

Teachers: using these SQL Server questions with a class? Tell us about your class.

Report a mistake

What's wrong?