Microsoft SQL Server MCQs
Microsoft SQL Server MCQs
These Microsoft SQL Server multiple-choice questions and their answers will help you strengthen your grip on the subject of Microsoft SQL Server. You can prepare for an upcoming exam or job interview with these Microsoft SQL Server MCQs.
So scroll down and start answering.
1: Which of the following aggregate functions are supported by the Transact-SQL language?
A. All of these are supported
B. MAX
C. MIN
D. AVG
2: Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function
A. When casting to desired data type failed it will result NULL
B. When casting to desired data type failed it will result Error
C. When casting to desired data type failed it will result 0
D. When casting to desired data type failed it will result Empty
3: True or false: You can modify data in a table using a User Defined Function.
A. False
B. True
4: Which of the following is not considered a Boolean Operator?
A. OR
B. AND
C. IN
D. WHERE
5: Which of the following operators allows the specification of two or more expressions to be used for a query search?
A. WHERE
B. AND
C. OR
D. IN
6: In SQL Server 2012 introduces what new feature?
A. Always On
B. Online Index Reorgs
C. Online Index Rebuilds
D. Mirroring
E. Log Shipping
7: Which of the following storage options can be used to store LOBs and save storage?
A. None of these
B. Only in FILESTREAM
C. Only in Sparce columns
D. Both FILESTREAM and Sparse columns
8: A ________ is an alphanumerical, hexadecimal, or numberic constant.
A. delimiters
B. literal value
C. identifiers
D. figurative value
9: True or false? Adding TOP to a select always improves performance.
A. False
B. True
10: 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?
A. TRY
B. SET XACT_ABORT OFF
C. SET ARITHABORT ON
D. SET XACT_ABORT ON
11: GETDATE() is a ....... function?
A. Database Level
B. Nondeterministic
C. User Defined
D. Server Level
E. Deterministic
12: 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
A. Update the field1 column to the value 1 in only 100 records in the table
B. Produce and error
C. Update the field1 column to the value 1 in all 1000 records in the table
13: Which of the following SQL Server built-in functions is nondeterministic?
A. CONVERT()
B. ISNULL()
C. GETDATE()
D. COUNT()
E. LEN()
14: What is the correct syntax for a CTE?
A. select [column] from cte myCTE ([column]) as (select ...);
B. with select [column] from myCTE ([column]) as (select ...);
C. with myCTE ([column]) as (select ...) select [column] from myCTE;
D. using cte myCTE ([column]) as (select ...) select [column] from myCTE;
15: What does DBCC stand for?
A. Database Clustering Checker
B. Database Connection Checker
C. Database Consistency Commands
D. Database Communication Checker
16: To Improve stored procedure performance,which statement below is used to write beginning of SP?
A. SET NOCOUNT ON
B. SET STATISTICS IO ON
C. SET NOCOUNT OFF
D. SET STATISTICS TIME ON
E. SET STATISTICS IO OFF
17: When you are dealing with lots of NULL Values in columns,which datatype is better?
A. Variable Length
B. Fixed Length
18: True or False? Every row in a FileTable represents a file or a directory.
A. True
B. False
19: In which context is the ROW_NUMBER() function valid?
A. select ... inner join [tbl] on ROW_NUMBER() = [tbl].[id];
B. select ... ROW_NUMBER() over (partition by [group_val] order by [id]) as [row] ...;
C. select ... ROW_NUMBER() as [row] ... group by [group_val] order by [id];
D. select ROW_NUMBER();
20: What is the default port of Microsoft SQL Server (for TCP)?
A. 1443
B. 1433
C. 1434
D. 1343
E. 1143
21: Which sql function finds the length of a string field?
A. STRLEN
B. LENGTH
C. LEN
22: What is a "CROSS APPLY" used for?
A. Updating a report file
B. Performing joins between calculated sets
C. Setting user permissions
D. Synchronizing changes between two databases
23: When creating indexes in SQL, what two main factors must be considered?
A. Memory versus disk space
B. Network bandwidth versus memory
C. Tables versus functions
D. Performance versus disk space
24: What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?
A. SSIS
B. Backup Restore
C. Analysis Services
D. Data Import & Export
25: Which of the following allows you to modify (update or delete) all values of the parent table?
A. CASCADE
B. SET NULL
C. NO ACTION
D. SET DEFAULT
26: What is the "WITH RECOMPILE" statement used for?
A. Converts a SQL statement into machine code
B. Causes the SQL engine to recompile itself from source
C. Causes SQL Server to rebuild the query plan for a stored procedure on every call
D. Binds a CLR static method to a stored procedure
27: Which of the following are not default database schemas?
A. pte
B. guest
C. sys
D. dbo
28: The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.
A. FILESTREAM
B. TIMESTAMP
C. SQL_VARIANT
D. UNIQUEIDENTIFIER
29: Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?
A. All of these
B. SMALLDATETIMEFROMPARTS
C. DATEFROMPARTS
D. TIMEFROMPARTS
E. SMALLDATETIME2FROMPARTS
30: Which transaction isolation level allows dirty reads?
A. SERIALIZABLE
B. READ UNCOMMITED
C. READ COMMITED
D. SNAPSHOT
31: What does COALESCE do?
A. The same as RIGHT JOIN.
B. Combines multiple T-SQL statements into one.
C. The same as UNION ALL.
D. Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
E. The same as LEFT JOIN.
32: You can create index on views.
A. False
B. True
33: 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?
A. Query A will return both full result sets; Query B will return both result sets with duplicates removed.
B. Query A and B return the same result set.
C. Query A will return both result sets with duplicates removed; Query B will return both full result sets.
34: Which of the following statements will remove a UDF?
A. ALTER FUNCTION
B. DROP FUNCTION
C. All of these
D. CREATE FUNCTION
35: What is the default isolation level in SQL Server?
A. Serializable
B. Read Commited
C. Snapshot
D. Repeatable Read
36: What is the basic difference between varchar and nvarchar?
A. nvarchar performs faster then varchar.
B. nvarchar supports unicode data.
C. varchar supports unicode data.
D. nvarchar supports large blob.
37: Which of the following is a benefit of SQL Server replication?
A. Redundancy
B. Offline Processing
C. Load Processing
D. All of these
38: a FOREIGN KEY constraint can reference a UNIQUE constraint
A. True
B. False
39: Data Transformation Services do what?
A. They provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server and other formats
B. They provide a window into the inner workings of the database
C. They are used to control the MSSQLServer, MSDTC, and SQLServerAgent processes
D. They are the main administrative console for SQL Server installations
40: In this statement, "SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ", the JOIN term is the same as:
A. LEFT INNER JOIN
B. LEFT OUTER JOIN
C. FULL OUTER JOIN
D. INNER JOIN
41: Differential backups do what?
A. Backs up all the information in the SQL Server
B. Backs up only the information in the selected FileTable
C. None of these
D. Copy only the information that has changed since the last full backup
42: What is the role of a processadmin user?
A. Executes the BULK INSERT statement
B. Creates and alters databases
C. Manages processes running in an instance of SQL Server
D. Manages server logins
43: What does the Query Analyzer, or SQL Server Management Studio, tool do?
A. Tests queries before implementing them in other applications
B. All of these
C. Creates/modifies stored procedures and execute administrative tasks
D. Quickly pulls information out of a database in response to a user request
44: In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.
A. True
B. False
45: What does the "IN" clause do?
A. Checks whether a table contains a reference to another table
B. Searches a file for records belonging to a table
C. Compares a specified value against a list of values and is true if a match is found
D. Used to specify which table to iterate over in a loop using T-SQL
46: What is the default port number for SQL Server?
A. 5022
B. 1433
C. 1434
D. 5023
47: Which of the following acts as the Database Engine
A. Windows security
B. SQL Server security
C. Both Windows security and SQL Server security
48: Which is the slowest?
A. TABLE scan
B. INDEX scan
49: What is a cursor in SQL Server?
A. A user interface component contained within the SQL Server Management Studio application
B. A special T-SQL construct used to iterate through the individual records returned by a query
C. A remote connection to another SQL Server
D. A collection of icons that can be applied to the mouse pointer on the screen
50: A sysadmin user is allowed to perform what actions?
A. Set user accounts
B. Edit databases
C. Any action in SQL server
D. Change security settings
List of Microsoft SQL Server M...
Available in:
Microsoft SQL Server MCQs
Тесты по Microsoft SQL Server (MCQs)
Pruebas de Microsoft SQL Server (MCQs)
Microsoft SQL ServerのMCQ問題
Domande a risposta multipla su Microsoft SQL Server
Questions à choix multiple sur Microsoft SQL Server
Questões de Múltipla Escolha sobre o Microsoft SQL Server
MCQs zu Microsoft SQL Server
Microsoft SQL Server 多项选择题
أسئلة اختيارات متعددة حول Microsoft SQL Server
Pertanyaan Pilihan Ganda Microsoft SQL Server
Microsoft SQL Server Çoktan Seçmeli Sorular
