Paras Cadd SQL Database Administrator Interview Questions and Answers

Clustered index determines physical order of data while non-clustered index is a separate structure.

• Clustered index sorts and stores data in the table based on the key values

• Non-clustered index creates a separate structure to store the index data

• Clustered index can only be created on one column while non-clustered index can be created on multiple columns

• Example: Clustered index on a customer table can be created on the...read more

Composite key is made up of multiple columns while non-composite key is made up of a single column. Primary key can also be defined using unique, not null, or candidate key.

• Composite key uses multiple columns to uniquely identify a row while non-composite key uses a single column

• Primary key can be defined using unique, not null, or candidate key instead of explicitly defining it

• Candidate key is a unique key that can be

Questions on SQL Database Administration including recursive stored procedures, optimization, recovery and backup, loading JSON files, scheduling, and cursor examples.

• For recursive stored procedures, use common table expressions (CTEs) to simplify the code

• Optimization techniques include indexing, query tuning, and partitioning

• Regular backups and testing of recovery procedures are essential for data protection

• To load JS...read more

Recovery of corrupt System Database involves restoring from backups, running repair utilities, and potentially rebuilding the database.

• Check for recent backups and restore the database from a known good backup

• Run repair utilities like DBCC CHECKDB to identify and fix corruption

• If necessary, rebuild the database by exporting data, dropping the corrupt database, and recreating it

Views are virtual tables that display data from one or more tables, while stored procedures are precompiled SQL statements that can accept parameters and perform actions.

• Views are read-only and cannot accept parameters, while stored procedures can be used to perform complex logic and can accept input parameters.

• Views are used for simplifying complex queries and providing a layer of abstraction, while stored procedures ...read more

DBCC stands for Database Console Commands. It is a set of commands used to perform maintenance tasks on a SQL Server database.

• DBCC CHECKDB - used to check the logical and physical integrity of all objects in a database

• DBCC SHRINKDATABASE - used to shrink the size of a database

• DBCC SQLPERF - used to display performance statistics for SQL Server

• DBCC TRACEON - used to enable a specific trace flag for troubleshooting purpo