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.
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]