SQL Server Job Interview questions part 2

Que:- What is Index? 
Ans:- An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed 
up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance. 

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself. 

Que:- What is the difference between clustered and a non-clustered index? 
Ans:- A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. 

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. 

Que:- What are the different index configurations a table can have? 
Ans:- A table can have one of the following index configurations: 
No indexes 
A clustered index 
A clustered index and many nonclustered indexes 
A nonclustered index 
Many nonclustered indexes 

Que:- What is cursors? 
Ans:- Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. 

In order to work with a cursor we need to perform some steps in the following order: 
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row Close cursor
Deallocate cursor 

Que:- What is the use of DBCC commands? 
Ans:- DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks. 
E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked. 
DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. 
DBCC CHECKFILEGROUP - Checks all tables file group for any damage. 

Que:- What is a Linked Server? 
Ans:- Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. 
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. 

Que:- What is Collation? 
Ans:- Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width. 

Que:- What are different type of Collation Sensitivity? 
Ans:- Case sensitivity 
A and a, B and b, etc. 

Accent sensitivity 
a and á, o and ó, etc. 

Kana Sensitivity 
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. 

Width sensitivity 
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive. 

Que:- What's the difference between a primary key and a unique key? 
Ans:- Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only. 

Que:- How to implement one-to-one, one-to-many and many-to-many relationships while designing tables? 
Ans:- One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.