Log In to start Learning

Login via

Post By Admin Last Updated At 2020-06-15
SQL Server Interview Questions

You are right place, If you are looking for SQL Server interview questions and answers, get more confidence to crack interview by reading this questions and answers we will update more and more latest questions for you…

1. On Which TCP/IP port does SQL Server keep running on?

   Usually, SQL Server keeps running on port 1433.

2. Differentiate Clustered and a non-Clustered index?

 A Clustered index is a file that rearranges the table in the request of the record itself. Its leaf nodes contain information pages. A table can have just one grouped record.

A non-Clustered Index is a file that does not re-organizes the table in the request of the list itself. Its leaf nodes contain list pushes rather than data pages. A table can have numerous non-clustered indexes.

3. Mention the different index configurations possible for a table?

In a table, we usually have the following list configurations:

No indexes

A Clustered Index

A Clustered index with numerous non- clustered indexes

A non- clustered index

Numerous non- clustered indexes

4. What is the recovery model? Explain the types of recovery model accessible in SQL Server?

Recovery model essentially reveals to SQL Server what data should be kept in the exchange log document and for to what extent. A database can have just a single recovery model. It additionally reveals to SQL server that which back up is possible in a specific recovery model chose. There are three types of recovery model:




Get more questions and answers from onlineitguru trainers after completion of sql server certification
5. What are various Back up accessible in SQL Server?

 Various potential backup  are:

Full recovery

Differential Backup

Transactional Backup

Copy Only Backup

File and File group back up

6. What is a FULL Backup?

A full recovery is the most widely recognized kind of recovery in SQL Server. This is the complete back up of the database. It likewise contains some portion of exchange log so it can be recovered.

7. What is OLTP?

OLTP implies On-Line Transaction Processing which contains guidelines of data standardization to guarantee data integrity. Utilizing these principles complex data is separated into a most basic structure.

[ Related Article - How ETL acts as an intermediate between OLTP and OLAP? ]

8. What is RDBMS?

RDBMS or Relational Data Base Management Systems are database the board frameworks that keep up information like tables. We can make connections between the tables. An RDBMS has the capacity to recombine the information data from various records, giving amazing assets to data utilization.

9. What are the properties of the Relational tables?

Relational tables have six properties:

Values are atomic

Column values are of a similar kind.

Each column is one of a kind.

The Columns sequence is insufficient

The row sequence is insufficient.

Every column must have a special name.

10. What's the distinction between a unique key and primary key?

The distinction between the unique key and a primary key are:

The primary key is a column whose values remarkably distinguish each column in a table. Primary key values can never be reused. They make a grouped file on the column and can't be NULL.

A Unique key is a column whose values additionally interestingly distinguish each row in a table however they make a non-clustered index by default and it permits one NULL.

11. When is UPDATE_STATISTICS direction utilized?

As the name suggests UPDATE_STATISTICS direction refreshes the measurements utilized by the file to make the search simpler.

12. What is the distinction between a HAVING CLAUSE and a WHERE CLAUSE?

The difference between HAVING CLAUSE and WHERE CLAUSE is:

Both determine a search condition but having Clause is utilized uniquely with the SELECT  statement and typically used with GROUP BY Clause.

If  GROUP BY condition isn't utilized, at that point having carried on like WHERE Clause as it were.

13. What is meant by Mirroring?

Mirroring is a high accessibility arrangement. It is intended to keep up a hot backup server which is reliable with the essential server in terms of the transaction. transaction log records are sent straightforwardly from the main server to a secondary server which stays up with the latest with the principal server.

14. Mention the advantages of Stored Procedures?

Stored procedures have the following advantages:

This methodology speeds the application execution.

This strategy execution plans can be reused as they stored in SQL Server's memory which diminishes server overhead.

Stored procedure strategies can be reused.

Stored procedure can encapsulate the logic. You can change the stored procedure without influencing customers.

These systems give better security to your information.

15. What is identity in SQL?

An identity column in  SQL naturally creates numeric qualities. We can be characterized as a  start and incrementation of the identity columns. Identity columns need not be indexed.

16. What are the general performance issues in SQL Server?

Following are the regular execution issues:



Absent and unused records.

I/O bottlenecks

Poor Query plans


17. Mention the different tools accessible for Performance tuning?

There are different devices accessible for performance tuning:

Dynamic Management Views

SQL Server Profiler

Server Side Traces

Windows Performance screen.

Query Plans

Tuning adviser

18. What do you mean by the performance monitor?

The performance monitor is a tool to catch measurements for the whole server. We can utilize this tool for catching occasions of SQL server too.

Some helpful counters are – Disks, Memory, Processors, Network and so on.

19. What is the SQL server agent?

The SQL Server operator assumes a fundamental job in everyday assignments of SQL server executive (DBA). Server specialist's purpose is to execute the assignments effectively with the scheduler motor which enables our business to keep running at planned date and time.

Get Microsoft SQL certification here
20. What are the types of sub-query?

There are three types of sub-query –

Single line sub-query which returns just one row

Multiple line sub-query that  returns multiples rows

Multiple column sub-query returns various columns to the main query. With that sub-query result, the Main query will be executed.

21. Define Trigger?

Triggers are used to execute a bunch of SQL code when an addition or update or delete commands are executed against a table. Triggers are consequently activated or executed when the information is changed. It very well may be executed consequently on the insert, delete and update commands.

22. What are the types of Triggers?

There are four kinds of triggers and they are:




Instead of

23. What is Bulk copy in SQL?

A bulk copy is a tool used to copy a huge measure of data from Tables. This device is used to store huge amount of data in SQL Server.

24. Differentiate Stored Procedure and the dynamic SQL?

Stored Procedure is a set of statements that are stored in Stored procedure. Dynamic SQL is a lot of statements that powerfully developed at runtime and it won't be stored in a Database and it just execute during run time.

25. Can we hot add CPU to SQL server?

Adding CPU's can happen physically by including new equipment, legitimately by online equipment parceling, or essentially through a virtualization layer. Beginning with SQL Server 2008, SQL Server underpins hot include CPU.

It Requires equipment that supports hot include CPU.Requires the 64-bit release of Windows Server 2008 Data center or the Windows Server 2008 Enterprise Edition for Itanium-Based Systems working framework.Requires SQL Server Enterprise.26. What key gives the most grounded encryption in SQL Server DBA?

AES (256 bit)

If we pick a longer key, at that point encryption will be better, so pick longer keys for more encryption. Anyway, there is a bigger exhibition punishment for longer keys. DES is a moderately old and weaker algorithm than AES.

AES: Advanced Encryption Standard

DES: Data Encryption Standard

[contact-form-7 id="5350" title="Post insertion"]

27. Would you be able to disengage the SQL Server 2005 database and append it to a SQL Server 2008 server?

SQL Server 2005 databases are perfect with SQL Server 2008. Appending a SQL Server 2005 database to SQL Server 2008 consequently updates the SQL Server 2005 database