Top 50 SQL Interview Questions and Answers for Experienced & Freshers (2022 Update)

SQL Server Interview Questions and Answers | SQL Interview Questions

16. What are all the different types of indexes?

There are three types of indexes -.

This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.

This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

Can we configure Availability groups between two SQL Server failover cluster instances?

Yes, the availability group can be configured between SQL Server cluster instances, but automatic failover cannot be configured between replicas running on SQL Server cluster instances.

Explain some details about distributed availability group?

Distributed Availability Group was added in SQL Server 2016. It spans multiple available groups. Distributed availability group is created on top of existing 2 or more separate availability groups. Availability groups participating in distributed availability groups can be hosted on multiple locations, it can be in the cloud or on-prem, in the same domain or different domains, etc. Each availability group has its own WSFC and nodes participating in that availability group will be part of only that Windows Server Failover Cluster group.

You have upgraded SQL Server databases to their newer version. Your application users have started complaining about slow performance. How do you address this issue?

We need to investigate the below area if users have started complaining about performance issues post upgrading the database to a newer version.

  • Change compatibility level of the database to the latest
  • If the compatibility level is already set to the latest, then you should review the query plan and if there is any query regression found then we should use a legacy cardinality estimator as a workaround. Parallelly, enable query store to identify query regression and fix them by choosing a good plan
  • Parameter sniffing could be one of the reasons behind the slow performance. Recompile query plan to fix the issue
  • Find missing indexes and create them
  • Run update stats on identified objects
  • 37. Advantages and Disadvantages of Stored Procedure?

    Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

    Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

    47. How to fetch alternate records from a table?

    Records can be fetched for both Odd and Even row numbers -.

    from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]

    Related Posts

    Leave a Reply

    Your email address will not be published. Required fields are marked *