Some Interview questions
Question 1. What are the different types of Indexes available in SQL Server?
Answer: The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
Question 2. What are the different types of Indexes available in SQL Server?
Answer: The simplest answer to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
Question 3. What is the difference between Clustered and Non-Clustered Index?
Answer: In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
Question 4. What is the difference between Clustered and Non-Clustered Index?
Answer: In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
More Information:
Clustered Index Structure
Nonclustered Index Structures
fillfactor of Index
Question 5. What are the High-Availability solutions in SQL Server and differentiate them briefly?
Answer: Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server.
SQL Server 2014/2016: AlwaysOn (no more Mirroring)
Question 6. What is the default Port No to which SQL Server listens?
Answer: 1433
Question 7. What are the SQL Server file types?
Answer: Primary, Secondary, Log files more…
Question 8. SQL SERVER – What is – DML, DDL, DCL and TCL
Answer: Read…
Question 9. What is Fill Factor?
Answer: The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index. Read more…
The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.
Question 10. What are the recovery models for a database?
Answer: There are 3 recovery models available for a database. Full, Bulk-Logged and Simple are the three recovery models available. Read more…
Question 11. What are the recovery models in SQL Server 2014?
Answer: Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.
Find out the current recovery mode
SELECT DATABASEPROPERTYEX(‘dataclub’, ‘Recovery’) As [Recovery Model]