Debug School

Akanksha
Akanksha

Posted on

Top 30 MsSQL Interview Questions with Answers

1. In SQL, what is a primary key?

a) A key that uniquely identifies a record in a table
b) A key that allows NULL values
c) A key that can be duplicated
d) A key that is used for encryption
Answer: a) A key that uniquely identifies a record in a table

2. Which SQL constraint ensures that there are no duplicate values in a column?

a) PRIMARY KEY
b) UNIQUE
c) FOREIGN KEY
d) CHECK
Answer: b) UNIQUE

3. What does ACID stand for in the context of database transactions?

a) Atomicity, Consistency, Isolation, Durability
b) Access, Consistency, Indexing, Durability
c) Atomicity, Concurrent, Isolation, Durability
d) Access, Concurrency, Isolation, Durability
Answer: a) Atomicity, Consistency, Isolation, Durability

4. Which type of join returns only unmatched rows from the left table and matched rows from the right table?

a) INNER JOIN
b) LEFT JOIN
c) RIGHT JOIN
d) FULL JOIN
Answer: b) LEFT JOIN

5. In a one-to-many relationship, which side contains the foreign key?

a) The "one" side
b) The "many" side
c) Both sides equally
d) None of the above
Answer: b) The "many" side

6. What is an index in SQL Server?

a) A data structure that organizes data in a table
b) A file that stores backup copies of data
c) A set of rules for data validation
d) A trigger that fires on data changes
Answer: a) A data structure that organizes data in a table

7. Which index type in SQL Server is typically used for columns that contain a large number of repeated values?

a) Clustered index
b) Non-clustered index
c) Unique index
d) Filtered index
Answer: b) Non-clustered index

8. In SQL Server, what is a scalar function?

a) A function that returns a single value
b) A function that operates on multiple columns
c) A function that returns a table
d) A function that modifies data
Answer: a) A function that returns a single value

9. What is the purpose of the COMMIT statement in SQL?

a) To save the current transaction
b) To cancel the current transaction
c) To roll back the current transaction
d) To start a new transaction
Answer: a) To save the current transaction

10. Which SQL isolation level provides the highest level of isolation but may result in performance issues?

a) READ COMMITTED
b) REPEATABLE READ
c) SERIALIZABLE
d) READ UNCOMMITTED
Answer: c) SERIALIZABLE

11. Which statement is used to handle exceptions in SQL Server?

a) TRY
b) CATCH
c) EXCEPTION
d) HANDLE
Answer: b) CATCH

12. In SQL Server, what does RAISERROR do?

a) Raises a user-defined error message
b) Raises a system-defined error message
c) Raises a warning message
d) Raises an informational message
Answer: a) Raises a user-defined error message

13. What is a view in SQL Server?

a) A virtual table based on the result of a SELECT query
b) A physical table that contains computed columns
c) A temporary table created at runtime
d) A table with restricted access
Answer: a) A virtual table based on the result of a SELECT query

14. Which SQL object is used to automatically execute a set of SQL statements in response to certain events?

a) Procedure
b) Function
c) Trigger
d) View
Answer: c) Trigger

15. Which SQL statement is used to create a backup of a database?

a) CREATE BACKUP
b) BACKUP DATABASE
c) SAVE DATABASE
d) ARCHIVE DATABASE
Answer: b) BACKUP DATABASE

16. What is a differential backup in SQL Server?

a) A backup that includes all changes since the last full backup
b) A backup that includes only changed data since the last backup
c) A backup that includes all data in the database
d) A backup that includes only system databases
Answer: a) A backup that includes all changes since the last full backup

17. Which SQL Server authentication mode requires a username and password?

a) Windows Authentication Mode
b) Mixed Mode
c) SQL Authentication Mode
d) Integrated Security Mode
Answer: c) SQL Authentication Mode

18. What is SQL injection?

a) A technique to bypass authentication in SQL Server
b) A method to inject malicious code into SQL queries
c) A way to create secure SQL statements
d) A technique to optimize SQL queries
Answer: b) A method to inject malicious code into SQL queries

19. What is the purpose of the SQL Server Agent?

a) To manage database transactions
b) To schedule and automate tasks and jobs
c) To optimize SQL queries
d) To monitor hardware performance
Answer: b) To schedule and automate tasks and jobs

20. Which tool is used for monitoring and optimizing the performance of SQL Server?

a) SQL Profiler
b) SQL Analyzer
c) SQL Monitor
d) SQL Inspector
Answer: a) SQL Profiler

21. What is SSIS used for in SQL Server?

a) Data warehousing and ETL (Extract, Transform, Load)
b) Online transaction processing (OLTP)
c) Query optimization
d) Database design
Answer: a) Data warehousing and ETL (Extract, Transform, Load)

22. Which control flow task in SSIS is used to perform looping within a package?

a) For Loop Container
b) Foreach Loop Container
c) Sequence Container
d) Looping Container
Answer: b) Foreach Loop Container

23. What is SSRS used for in SQL Server?

a) Data analysis and visualization
b) ETL (Extract, Transform, Load)
c) Database design
d) Online transaction processing (OLTP)
Answer: a) Data analysis and visualization

24. Which tool is used to design and create reports in SSRS?

a) Report Builder
b) Report Designer
c) Report Analyzer
d) Report Creator
Answer: a) Report Builder

25. What is SSAS used for in SQL Server?

a) Data analysis and multidimensional modeling
b) ETL (Extract, Transform, Load)
c) Database design
d) Online transaction processing (OLTP)
Answer: a) Data analysis and multidimensional modeling

26. Which type of SSAS model organizes data in a hierarchical structure?

a) Multidimensional model
b) Tabular model
c) Relational model
d) Entity-Relationship model
Answer: a) Multidimensional model

27. Which cloud platform offers a managed SQL Server service?

a) Amazon Web Services (AWS)
b) Microsoft Azure
c) Google Cloud Platform (GCP)
d) IBM Cloud
Answer: b) Microsoft Azure

28. Which service in Azure provides a fully managed SQL Server database engine?

a) Azure SQL Database
b) Azure SQL Managed Instance
c) Azure SQL Data Warehouse
d) Azure Cosmos DB
Answer: a) Azure SQL Database

29. Which T-SQL statement is used to create a new table?

a) CREATE TABLE
b) ADD TABLE
c) INSERT TABLE
d) CREATE NEW TABLE
Answer: a) CREATE TABLE

30. What is the purpose of the T-SQL function CHARINDEX()?

a) Returns the first occurrence of a specified string within another string
b) Returns the number of characters in a string
c) Returns the ASCII value of a character
d) Converts a string to uppercase
Answer: a) Returns the first occurrence of a specified string within another string

31. What is the purpose of XML data type in SQL Server?

a) To store XML data in a column
b) To convert XML to JSON
c) To store JSON data in a column
d) To create XML reports
Answer: a) To store XML data in a column

32. Which T-SQL function is used to query XML data in SQL Server?

a) XMLQUERY()
b) XQUERY()
c) XMLSELECT()
d) XPATH()
Answer: d) XPATH()

33. What is the purpose of full-text search in SQL Server?

a) To perform advanced text-based searches in character data
b) To perform searches only on primary keys
c) To perform searches on numeric data
d) To perform searches on binary data
Answer: a) To perform advanced text-based searches in character data

34. Which T-SQL statement is used to create a full-text index on a table?

a) CREATE FULLTEXT INDEX
b) CREATE INDEX
c) CREATE SEARCH INDEX
d) CREATE TEXT INDEX
Answer: a) CREATE FULLTEXT INDEX

Top comments (0)