Top 10 SQL Server Interview Questions & Answers 2020

Q1. What are the two authentication modes in SQL Server?

There are two authentication modes –

• Windows Mode
• Mixed Mode

Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page.

Q2. What Is SQL Profiler?

SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.

Q3. Can SQL servers linked to other servers?

SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.

Q4. What is sub query and its properties?

A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. 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

Q5. What are the types of sub query?

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.

Q6. What are scheduled tasks in SQL Server?

Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.

Q7. What is COALESCE in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

Example –

Select COALESCE(empno, empname, salary) from employee;

Q8. What will be the maximum number of index per table?

For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

Q9. What is the difference between COMMIT and ROLLBACK?

Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.

Q10. What is SQL injection?

SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.

Even parameters can be manipulated by the skilled and experienced attackers.