MS SQL Server Proficiency Exam
Welcome to our MS SQL Server Quiz! This quiz is designed to challenge your expertise and enhance your understanding of Microsoft SQL Server, one of the most popular relational database management systems. Whether you are a budding database administrator, a seasoned developer, or an IT professional, this quiz offers a wide array of questions that cover the foundational to advanced features of SQL Server.
You will be tested on key topics such as database architecture, indexing, querying, performance tuning, security, and transaction management. The questions are crafted to probe your knowledge of SQL Server's tools and functionalities, including the use Read moreof SQL Server Management Studio (SSMS), T-SQL programming, backup strategies, and disaster recovery planning.
By taking this quiz, you'll get a chance to validate your skills, identify areas where you may need further study, and better prepare yourself for professional challenges. Dive in to demonstrate your proficiency with MS SQL Server and gain insights into best practices and effective database management techniques. Let's see how much you really know about SQL Server!
MS SQL Server Questions and Answers
- 1.
Using the following table structure: CREATE TABLE PurchaseOrders ( [PurchaseOrderID] [int] NOT NULL REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID) , [LineNumber] [smallint] NOT NULL , [ProductID] [int] NULL REFERENCES Production.Product(ProductID) , [UnitPrice] [money] NULL , [OrderQty] [smallint] NULL , [ReceivedQty] [float] NULL , [RejectedQty] [float] NULL, [DueDate] [datetime] NULL ;) Create the SELECT statement which returns all rows, in the following column order: Unit Price, ProductID, OrderQty and Due Date, in reverse alphabetical order by ProductID.
- 2.
Select the correctly formed SQL Statement from the below choices.
- A.
INSERT ('1','Sam' INTO (ID,Name) USING People
- B.
INSERT INTO People ('1','Sam') VALUES (ID, Name)
- C.
INSERT INTO People (ID, Name) VALUES (@@IDENTITY,'Sam')
- D.
INSERT INTO People SET ID=1, Name='Sam'
Correct Answer
C. INSERT INTO People (ID, Name) VALUES (@@IDENTITY,'Sam') -
- 3.
True or False: Will this statement update the PeopleActive field? UPDATE dbo.People SET PeopleActive = 1 HAVING TermDate IS NULL
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
The statement "UPDATE dbo.People SET PeopleActive = 1 HAVING TermDate IS NULL" will not update the PeopleActive field. The HAVING clause is used with the GROUP BY clause to filter the results of a query. However, in this case, there is no GROUP BY clause present, so the HAVING clause has no effect. Therefore, the PeopleActive field will not be updated.Rate this question:
-
- 4.
Select all the correct answers: SQL Server 2008 supports the following network protocols:
- A.
Banyan VINES SSP
- B.
AppleTalk
- C.
TCP/IP
- D.
Named Pipes
- E.
NWLink IPX/SPX
- F.
Shared Memory
Correct Answer(s)
C. TCP/IP
D. Named Pipes
F. Shared MemoryExplanation
SQL Server 2008 supports multiple network protocols for communication. The given options list various protocols, and the correct answers are TCP/IP, Named Pipes, and Shared Memory. These protocols allow SQL Server to communicate with client applications over different network environments.Rate this question:
-
- 5.
Select the correct clause to finish the below SELECT statement: SELECT COUNT(ID), ItemName, LastModifiedDate FROM dbo.Inventory ....
- A.
No further clause is necessary.
- B.
WHERE LastModifiedDate IS NOT NULL
- C.
HAVING LastModifiedDate IS NOT NULL
- D.
GROUP BY LastModifiedDate, ItemName
- E.
GROUPING ItemName, LastModifiedDate
Correct Answer
D. GROUP BY LastModifiedDate, ItemNameExplanation
The correct clause to finish the SELECT statement is "GROUP BY LastModifiedDate, ItemName". This clause is used to group the results by the LastModifiedDate and ItemName columns. It allows for aggregating the COUNT(ID) function and displaying the count of IDs for each unique combination of LastModifiedDate and ItemName.Rate this question:
-
- 6.
You have written a stored procedure which uses the @@IDENTITY function to return the last inserted value. This was working correctly until last night when you found out that @@IDENTITY began to return NULL each time. What tool do you use to diagnose your problem?
- A.
Profiler utility
- B.
Osql utility
- C.
SQL Server Profiler
- D.
Query Optimizer Tool
- E.
SQLDiag utility
Correct Answer
C. SQL Server ProfilerExplanation
SQL Server Profiler is the correct tool to diagnose the problem in this scenario. SQL Server Profiler allows you to capture and analyze events that occur on a SQL Server instance. By using the Profiler, you can trace the execution of the stored procedure and identify any issues or errors that may be causing the @@IDENTITY function to return NULL. This tool provides detailed information about the queries, events, and performance metrics, which can help in troubleshooting and resolving the problem.Rate this question:
-
- 7.
Select the correct clause to return information from a table.
- A.
GET
- B.
RETURN
- C.
SELECT FROM
- D.
SELECT INTO
Correct Answer
C. SELECT FROMExplanation
The correct clause to return information from a table is "SELECT FROM". This clause is used in SQL queries to specify the columns that should be retrieved from a table. It allows you to select specific data from one or more tables based on certain conditions, such as filtering by a specific column value or joining multiple tables together. By using the "SELECT FROM" clause, you can retrieve the desired information from a table in a database.Rate this question:
-
- 8.
SQL Server 2008 uses Distributed Transaction Services (DTS) as it's primary integration tool.
- A.
True
- B.
False
Correct Answer
B. FalseExplanation
SQL Server 2008 does not use Distributed Transaction Services (DTS) as its primary integration tool. Instead, it uses SQL Server Integration Services (SSIS) as its primary integration tool. SSIS is a platform for building high-performance data integration and workflow solutions, including extraction, transformation, and loading (ETL) operations.Rate this question:
-
- 9.
Select all appropriate choices: SQL Server Integration Services connection managers are used to
- A.
Connect to different data sources
- B.
Prepare or copy data
- C.
Sequence of package flow
- D.
None of the above
Correct Answer
A. Connect to different data sourcesExplanation
SQL Server Integration Services connection managers are used to connect to different data sources. This means that they provide the necessary functionality to establish connections with various types of databases or other data sources, allowing SSIS packages to retrieve or transfer data from these sources. Connection managers play a crucial role in the overall data integration process, enabling the seamless integration of data from multiple sources into a single destination or performing data transformations and manipulations during the process.Rate this question:
-
- 10.
A pre-compiled executable object which contains one or more Transact-SQL statements is called a:
- A.
Function
- B.
Stored Procedure
- C.
Package
- D.
Binary
Correct Answer
B. Stored ProcedureExplanation
A stored procedure is a pre-compiled executable object that contains one or more Transact-SQL statements. It is used to group a set of SQL statements into a single unit, which can then be executed repeatedly. Stored procedures provide a way to improve performance and maintainability by reducing the amount of code that needs to be written and executed. They can also be used to implement complex business logic and enforce data integrity rules.Rate this question:
-
- 11.
Select the correct T-SQL statement that removes all rows from the table and resets the Identity counter.
- A.
DELETE * FROM People WITH @@IDENTITY(0,0)
- B.
TRUNCATE People WITH @@IDENTITY(0)
- C.
TRUNCATE TABLE People
- D.
DELETE * FROM People
Correct Answer
C. TRUNCATE TABLE PeopleExplanation
The correct answer is TRUNCATE TABLE People. This statement removes all rows from the table and also resets the Identity counter. The other options are incorrect because they either have syntax errors or do not reset the Identity counter. The DELETE statement without specifying any condition will remove all rows from the table but will not reset the Identity counter.Rate this question:
-
- 12.
What is the preferred error handling method in SQL Server 2008?
- A.
RAISERROR
- B.
ON ERROR
- C.
TRY/CATCH
Correct Answer
C. TRY/CATCHExplanation
The preferred error handling method in SQL Server 2008 is TRY/CATCH. This method allows for structured error handling by enclosing the code that might generate an error within a TRY block, and then catching and handling any errors that occur within a CATCH block. This approach provides more control and flexibility in handling errors, allowing for specific actions to be taken based on the type of error encountered. It also allows for the possibility of nested error handling, where multiple levels of TRY/CATCH blocks can be used to handle errors at different levels of the code.Rate this question:
-
- 13.
Choose the incorrect clause.
- A.
SELECT INTO
- B.
INSERT INTO
- C.
SELECT FROM
- D.
INSERT FROM
Correct Answer
D. INSERT FROMExplanation
The correct answer is INSERT FROM. This is because the correct syntax for inserting data into a table is "INSERT INTO" followed by the table name, not "INSERT FROM". The "SELECT INTO" and "SELECT FROM" clauses are used for retrieving data from a table, while the "INSERT INTO" clause is used for inserting data into a table.Rate this question:
-
- 14.
Microsoft SQL Server is an example of a:
- A.
Relational Database
- B.
NoSQL Database
- C.
Map/Reduce Database
- D.
Vector Database
- E.
None
- F.
All
Correct Answer
A. Relational DatabaseExplanation
Microsoft SQL Server is an example of a relational database because it is designed to store and manage data in a structured manner, using tables with rows and columns. It supports the relational model, which allows for the establishment of relationships between different tables through keys. SQL Server also supports the SQL language, which is commonly used to query and manipulate relational databases.Rate this question:
-
- 15.
Select the methods you can use to copy data from one SQL Server to another of the same version.
- A.
BCP
- B.
SELECT INTO
- C.
SQL Server Integration Services
- D.
SQL Server Import and Export Wizard
Correct Answer(s)
A. BCP
B. SELECT INTO
C. SQL Server Integration Services
D. SQL Server Import and Export WizardExplanation
The methods that can be used to copy data from one SQL Server to another of the same version are BCP (Bulk Copy Program), SELECT INTO, SQL Server Integration Services, and SQL Server Import and Export Wizard. BCP allows for high-speed data transfers between SQL Server instances. SELECT INTO creates a new table and copies data from an existing table into it. SQL Server Integration Services provides a comprehensive platform for data integration and transformation. SQL Server Import and Export Wizard is a graphical tool that simplifies the process of importing and exporting data.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
May 08, 2024Quiz Edited by
ProProfs Editorial Team
Expert Reviewed by
Samy Boulos -
Feb 06, 2013Quiz Created by
Hlasher
Back to top