Index and isolation level interview questions

1. What is clustered index?
2. What is non-clustered index?
3. What is unique index?
4. What is Hash index?  ( in memory OLTP)
5. What is filtered index?
6.  Index with included column
7. Clustered column-store index
8. Non-clustered column-store index
     The nonclustered index contains a copy of part or all of the rows and columns in the               underlying table. The index is defined as one or more columns of the table and has an           optional condition that filters the rows.
9. Difference between clustered column-store index vs non-clustered column-store index
     The difference is that a nonclustered index is a secondary index that's created on a                  rowstore table, but a clustered columnstore index is the primary storage for the entire            table.
10.What is a primary key, foreign key and unique key?
11. Composite index
12. covering index
13. Natural key
14.surrogate key
15. Online index.
16. Resumable online index? (yes, row-store index only for SQL 2017 and above version )
17.

SQLServer Interview Questions-2

1. What is the query store in SQL 2016?

2. How do you upgrade SQL server with minimum downtime?

3. Can we setup AlwaysOn availability group between Windows and linux cluster?

4.  How do you partition database?

5. What will be the best practice for selecting RANGE value(LEFT/RIGHT) when you create function?

    By default function will use LEFT RANGE value however best practice would be RIGHT range.

6. What is the Change Data Capture?

7. Can we restore database with CDC enabled from one server to another server

   Answer:-  Yes, we can restore database with "KEEP_CDC" option while restoring database on another server.  also we need to script out following job and create on server where you're restoring the database. or you can simply run below command so it will create job for you on new server.

Exec sys.sp_cdc_add_job 'capture'

go

Exec sys.sp_cdc_add_job 'cleanup'

go

 

8. What is temporal database? difference between CDC vs Temporal database

9. What is a contained database? 

     Independent to SQL server instance. we can use SQL server authentication to connect to the database
10. Estimated vs Actual query plan
11. How SQL server optimizer decides what physical join  will be used to create query plan?
12. Physical join summary:

	Nested Loops Join	Merge Join	Hash Join
Best for …	Relatively small inputs with an index on the inner table on the join key.	Medium to large inputs with indexes to provide order on the equijoin keys and/or where we require order after the join.	DW queries with medium to large inputs.  Parallel execution that scales linearly.
Concurrency	Supports large numbers of concurrent users.	Many-to-one join with indexes to provide order supports large numbers of concurrent users.	Best for small numbers of concurrent users with high throughput requirements.
Stop and go	No	No	Yes (build input only)
Equijoin required	No	Yes (except for full outer join)	Yes
Outer and semi-joins	Left joins only (full outer joins via transformation)	All join types	All join types
Uses memory	No	No (may require sorts which use memory)	Yes
Uses tempdb	No	Yes (many-to-many join only)	Yes (if join runs out of memory and spills)
Requires order	No	Yes	No
Preserves order	Yes (outer input only)	Yes	No

13. How to find out if  ColumnStore index is being used from Query plan?

14. Check AlwaysON synchronization status between replicas and find out if there's delay in synchronization

    Alwayson Dashboard

      or DMVs

    SELECT 

ar.replica_server_name, 

adc.database_name, 

ag.name AS ag_name, 

drs.is_local, 

drs.is_primary_replica, 

drs.synchronization_state_desc, 

drs.is_commit_participant, 

drs.synchronization_health_desc, 

drs.recovery_lsn, 

drs.truncation_lsn, 

drs.last_sent_lsn, 

drs.last_sent_time, 

drs.last_received_lsn, 

drs.last_received_time, 

drs.last_hardened_lsn, 

drs.last_hardened_time, 

drs.last_redone_lsn, 

drs.last_redone_time, 

drs.log_send_queue_size, 

drs.log_send_rate, 

drs.redo_queue_size, 

drs.redo_rate, 

drs.filestream_send_rate, 

drs.end_of_log_lsn, 

drs.last_commit_lsn, 

drs.last_commit_time

FROM sys.dm_hadr_database_replica_states AS drs

INNER JOIN sys.availability_databases_cluster AS adc 

ON drs.group_id = adc.group_id AND 

drs.group_database_id = adc.group_database_id

INNER JOIN sys.availability_groups AS ag

ON ag.group_id = drs.group_id

INNER JOIN sys.availability_replicas AS ar 

ON drs.group_id = ar.group_id AND 

drs.replica_id = ar.replica_id

ORDER BY 

ag.name, 

ar.replica_server_name, 

adc.database_name;

Database Mirroring interview questions

1. How to add data files to different drive of mirrored databases?

The steps below will explain the route we took to move database files with zero downtime without disturbing the database Mirroring.

For Mirror Instance:

1. Suspend mirroring on Principal Server (optional).
2. Issue Alter database statement on the mirrored server to point to the new location. 
3. Stop mirrored SQL Server services.
4. Move database files of the mirrored database to the new location and make sure that permissions on the files are preserved.
5. Start mirrored SQL Server services.
6. Resume mirroring on Principal server databases and Verify mirroring is successfully resumed.

For Principal Instance:

1. Fail over databases to Mirror Server so that Mirror server can now act as Principal server.
2.  Suspend mirroring on new Principal server (optional).
3. Issue Alter database statement on the new Mirror server to point databases to the new location.
4. Stop new mirrored SQL Server services.
5. Move database files of the new mirrored database to the new location and make sure permissions on the files are preserved.
6. Start new mirrored SQL Server services.
7. Resume mirroring on Principal server databases and verify mirroring is successfully resumed.

2. Can we configure SQL DB mirroring using local system account on both principal and mirrored server?

No, WE can't configure or run DB mirroring using local system account. Local system account is limited to local system. It won't have an access to different server. you can use domain account or named account to run database services for db mirroring.

3. How to add Data files in mirorred database?

An overview of the entire procedure can be summed up in the following four points:

- Remove the existing mirroring partner

- Create a new file or file group on the primary server

- Take transaction log file backup and restore the same on the mirror server with the help of

   WITH MOVE option

- Once done, database mirroring has to be re-established

4. How to remove witness server from mirrored database?

Using below T-SQL you can remove witness server

ALTER DATABASE DBNAME SET WITNESS OFF;

5. Types of DB mirroring :-

    - Synchronous mirroring(automatic fail-over) -- High Safety

           High Availability  ( require witness server )

           High protection (doesn't require witness server)

   - Asynchronous Mirroring

           High performance ( requires manual fail-over)

6. Is is possible to mirrored database as read only purpose?

   Yes, it is possible only using SNAPSHOT

7. How Database snapshot works?

High level tasks of snapshot involves

· When you create a snapshot a sparse file is created for each data file

· When data is modified in the source database for the first time, the old value of the modified data is copied to the sparse file

· If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot

· When you query the snapshot, it first checks if the data is available in the snapshot. If it’s there it reads if from the snapshot. If it’s not there, I reads through to the source database and gets the data from there instead, because that means the data has not yet changed since the time the snapshot was taken