Telegram GroupTop 30 SQL Server Interview Questions
SQL Server is one of most important Relational Database Management Systems. Many questions are asked from SQL in relation to questions on a database. Check Updated SQL Server Interview Questions and Answers 2022 from this page.
Top 30 SQL Server Interview Question includes questions from almost all important areas of the SQL Server. SQL Server Interview Questions and Answers will definitely help you in dealing with the questions beginners and advanced level interview.
Top 30 SQL Server Interview Questions
SQL Server Interview Questions and Answers
Q.1 Which TCP/IP port does SQL Server run on?
Answer: By default SQL Server runs on port 1433.
Q.2 What is the difference between clustered and a non-clustered index?
Answer:
- A clustered index is an index that rearranges the table in the order of index itself. Its leaf nodes contain data pages.
- A table can have only one clustered index.
- A non-clustered index is an index that does not re-arranges the table in the order of index itself. Its leaf nodes contain index rows instead of data pages.
- A table can have many non-clustered indexes.
Check Here: Interview Preparation Tips
Q.3 List the different index configurations possible for a table?
Answer:
A table can have one of the following index configurations:
- No indexes
- A clustered index
- A clustered index and many non-clustered indexes
- A non-clustered index
- Many non-clustered indexes
Q.4 What are different backups available in SQL Server?
Answer:
- Full backup
- Differential Backup
- Transactional Log Backup
- Copy Only Backup
- File and Filegroup backup
Q.5 What is OLTP?
Answer:
- OLTP means Online transaction processing which follows rules of data normalization to ensure data integrity.
- Using these rules complex information is broken down into a most simple structure.
SQL Server Interview Questions and Answers Download PDF
SQL Server DBA Interview Questions
Following SQL Server DBA Interview Questions And Answers are beneficial for Professionals and Freshers aspirants. So check below listed SQL Server DBA Interview Questions and Answers for Experienced
Q.6 What is SQL server agent?
Answer: It plays animportant role in day to day tasks of SQL server administrator (DBA) and the Server agent’s determination is to implement the tasks effortlessly with the scheduler engine which permits our jobs to run at scheduled date and time.
Read: Interview Tips For Freshers
Q.7 What are scheduled tasks in SQL Server?
Answer: Scheduled tasks are used to systematize processes that can be run on a scheduled time at a regular intermission. This scheduling of tasks assists to decrease human interference during night time and feed can be completed at a specific time. User can also mandate the tasks in which it has to be created.
Q.8 How exceptions can be handled in SQL Server Programming?
Answer: In SQL Server Programming, exceptions are handled using TRY—CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.
Check: DBMS Interview Questions & Answers
Q.9 What are the sub query and its properties?
Answer: A sub-query is a request which can be nested inside a leading query like Select, Update, Insert or Delete statements. This can be used when expression is allowed and properties of sub query can be defined as
- A sub query should not have order by clause
- A sub query should be placed in the right hand side of the comparison operator of the main query.
- A sub query should be enclosed in parenthesis because it needs to be executed first before the main query.
- More than one sub query can be included
Q.10 What are the types of sub query?
Answer: There are three types of sub query –
- Single row sub query which returns only one row
- Multiple row sub query which returns multiple rows
- Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.
Must Know: How to Prepare For an Interview
SQL Server Query Interview Questions
First of all, we’re going to create two tables namely Empdetails and Empsalary. The following SQL Server Queries Interview Questions will be based on it, take a look…
Table – Empdetails
| EmpId | FullName | ManagerId | DateOfJoining |
| 121 | John Snow Deepak Garg | 321 | 01/31/2014 |
| 321 | Abc Xyz Prince Jain | 986 | 01/30/2015 |
| 421 | KuldeepRanaHanishMalhotra | 876 | 27/11/2016 |
Table – Empsalary
| EmpId | Project | Salary |
| 121 | P1 | 8000 |
| 321 | P2 | 1000 |
| 421 | P1 | 12000 |
Q.11 Write a query to fetch the count of employees working in project ‘P1’.
Answer:SELECT COUNT(*) FROM Empsalary WHERE Project = ‘P1’;
Q.12 Write a query to fetch project wise of count of employees sorted by project’s count in descending order.
Answer: Following command is used to perform above query:
SELECT Project, count(EmpId) EmpProjectCount FROM Empsalary GROUP BY Project ORDER BY EmpProjectCount DESC;
Q.13 Write a query to fetch common records between two tables.
Answer: SELECT * FROM Empsalary INTERSECT SELECT * FROM ManagerSalary
Q.14 Write a query to fetch all the Employees from Employee Details table who joined in Year 2016.
Answer: SELECT * FROM Empsalary WHERE DateOfJoining BETWEEN ’01-01-2016′ AND date ’31-12-2016′;
Q.15 Write a query to create an empty table with same structure as some other table.
Answer: SELECT * INTO newTable FROM EmployeeDetails WHERE 1 = 0;
Read: Difference Between Resume And CV
SQL Server Developer Interview Questions
Q.16 Is it possible to rename a database? If so, how would you rename the database?
Answer: Yes, a database can be renamed and it is possible to rename a database by one of these options:
- Execute the sp_renamedb system stored procedure.
- Execute the sp_rename system stored procedure and specify ‘database’ as the parameter.
- Use Management Studio by right clicking on the database and selecting the ‘Rename’ option.
Q.17 What are the two commands to remove all of the data from a table?
Answer: By using the following two commands i.e. The DELETE command and The TRUNCATE command, you can easily remove all of the data from a table.
Q.18 What are the three ways that Dynamic SQL can be executed?
Answer: Following are the three ways that Dynamic SQL can be executed
- Writing a query with parameters.
- Using EXEC.
- Using sp_executesql.
You may also read- Computer Networking Interview Questions
Q.19 How can you delete duplicate records in a table with no primary key?
Answer: By using the Use the SET ROWCOUNT command, you can delete duplicate recordswith no primary key.
Q.20 Is it True or False that SQL Server can format the date in over 10 different patterns.
Answer: Yes, it is true. By using the CONVERT command, there are over 15 different date formats.
MS SQL Server Interview Questions
Q.21 Which TCP/IP port does SQL Server run on? How can it be changed?
Answer: SQL Server runs on port 1433 and it can be changed from the Network Utility TCP/IP properties.
Q.22 What are the different index configurations a table can have?
Answer: A table will have the following index configurations as described below:
- No indexes
- A clustered index
- A clustered index and many nonclustered indexes
- A nonclustered index
- Many nonclustered indexes
Q.23 What is the purpose of FLOOR function?
Answer: FLOOR function is used to round up a non-integer value to the previous least integer for example FLOOR (6.7) and it will returns 6.
Learn more: Computer Programming Questions
Q.24 What is a Trigger?
Answer: Triggers are used to perform a batch of SQL code when insert or update or delete commands are implemented against a table. Triggers are spontaneously triggered or accomplished when the data is altered. It can be performed automatically on insert, delete and update operations.
Q.25 What are the types of Triggers?
Answer: There are four types of triggers and they are:
- Insert
- Delete
- Update
- Instead of
SQL Server Interview Questions By Shivprasad Koirala
Following SQL Server Interview Questions and Answers for Experienced is helpful for preparing in systematic way. So, check below mentioned SQL Server Interview Questions For Experienced Professionals.
Q.26 What is Bulkcopy in SQL?
Answer: Bulkcopy is a tool used to copy big amount of data from Tables and this tool is used to load huge amount of data in SQL Server.
Q.27 What are the differences between Stored Procedure and the dynamic SQL?
Answer: The difference between Stored Procedure and the dynamic SQL as shown below, take a look…
| Stored Procedure | Dynamic SQL |
| It is a set of statements which is stored in a compiled form |
|
Q.28 What is Collation?
Answer: Collation is defined to identify the sort order in a table and there are three types of sort order –
- Case sensitive
- Case Insensitive
- Binary
Q.29 What is ISNULL() operator?
Answer: The ISNULL() function is used to check whether value given is NULL or not NULL in sql server and this function also provides to replace a value with the NULL.
Q.30 What is the use of @@SPID?
Answer: @@SPID returns the session ID of the current user process.
Must Read: MCQ – Computer Memory Questions and Answers
Top 30 SQL Server Interview Questions and Answers:
SQL Server is one of the most important Relational Database Management Systems (RDBMS) for executing functions of recovering and storing data and so, most probably, many questions are asked from this topic in relation to questions on a database.
RDBMS is one of the most commonly used databases till date, and therefore SQL skills are indispensable in most of the job roles. In SQL Interview Questions, I will introduce you to the most frequently asked questions on SQL (Structured Query Language).
You May Also Search For:
- SQL Server Interview Questions For Developers
- SQL Server Interview Questions For Freshers
- SQL Interview Questions
- SQL Server Interview Questions And Answers for 5 Years Experience PDF
- SQL Server Interview Questions PDF Download
- SQL Server Interview Questions for Experienced Professionals
- SQL Server Interview Questions Download PDF
- SQL Server Interview Questions In Hindi
- SQL Server Job Interview Questions
- SQL Server XML Interview Questions
This is all about SQL Server Interview Questions. Practice all the important SQL Server topics for better understanding and appearing for the interview confidently.