SQL Server Interview Questions and answers
SQL Server Interview Questions and answers for beginners and experts. List of frequently asked SQL Server Interview Questions with answers by Besant Technologies. We hope these SQL Server Interview Questions and answers are useful and will help you to get the best job in the networking industry. This SQL Server Interview Questions and answers are prepared by SQL Server Professionals based on MNC Companies expectation. Stay tuned we will update New SQL Server Interview questions with Answers Frequently.
Best SQL Server Interview Questions and answers
Besant Technologies supports the students by providing SQL Server Interview Questions and answers for the job placements and job purposes. SQL Server is the leading important course in the present situation because more job openings and the high salary pay for this SQL Server and more related jobs. We provide the SQL Server online training also for all students around the world through the Gangboard medium. These are top SQL Server Interview Questions and answers, prepared by our institute experienced trainers.
SQL Server Interview Questions and answers for the job placements
Here is the list of most frequently asked SQL Server Interview Questions and answers in technical interviews. These questions and answers are suitable for both freshers and experienced professionals at any level. The questions are for intermediate to somewhat advanced SQL Server professionals, but even if you are just a beginner or fresher you should be able to understand the answers and explanations here we give.
Full Backup, Differential Backup, Log Backup, and Filegroup Backup.
The only difference between full and full-copy is that full-copy does not break the differential chain. A copy-only backup doesn’t affect that differential base. Neither Full or Full-Copy only backup breaks the log chain as neither of them truncates the log file.
SIMPLE, FULL & BULK LOGGED
Atomicity, Consistency, Isolation, and Durability
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE
It updates the distribution of key values for an index, Table or an indexed view. Statistics can be for columns or indexes.
Trace flags :1204 & 1222
“Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events
In case of deadlock, SQL Server will pick one task/session, whichever will be least expensive to rollback and kill it. This session/task is termed as a deadlock victim.
Master, Model, TempDB & MSDB.
Its s read only database and has all system tables and views. These objects appear in the sys schema of each database.
It’s a template database, changes made to Model database are applied to all databases created afterward.
Sys.objects , sys.allocation_units , sys.columns etc
Sysadmin, server admin , setupadmin
db_owner, db_securityadmin and db_accessadmin
Example: GRANT Execute on TestTable to TestUser1
Always Encrypted, Dynamic Data Masking, Stretch database, Polybase etc
No, if the primary database is not online, Secondary database should be brought online manually.
Log shipping is at database level while replication replicates data at the individual table level.
high-safety mode and high-performance mode
No, it is an optional instance. It is needed only if automatic failover is required.
Yes, the database needs to be in FULL recovery mode.
Active – Active and Active – Passive configuration type
- SQL Server Database Emgine
- SQL Server Agent
- SQL Server analysis services
Generally, when the fragmentation level is more than 30% , it is advisable to rebuild indexes and when the fragmentation is less than 30% we can re-organize indexes.
- DBCC CHECKDB
- DBCC Show_statistics
- DBCC Opentran
- DBCC Inputbuffer
Q33) Whats I ste default port number for SQL Server instance, if it has not been chnaged during installation
- Records which have been marked for deletion (logically deleted) but have not been physically deleted are termed as Ghost records. This feature was introduced to enhanceperformance.
- SQL Server has a background thread that checks for Ghost records on a periodic basis and deletes it .
- It should have atleast two files – Data file and Log file.
Yes, Indexes can be moved to a separate data file. This will help from performance point of view as well.
- Logins are at instance level while Users are at the database level. Instance level privileges and permissions are granted to Logins and they are also known as ‘Server principals’.
- Users are at the database level and are granted database level permissions. They are also known as
- ‘database principals’. Logins are mapped to users at the database level.
- A database user (based on a login) which is not mapped to a valid login is termed as an Orphan user.
- This can occur when the login is deleted or the database is moved from one server to the other.
Q39) What is the minimum level of permission required for a login to be able to change the password of another login?
A login can change its own password, but it needs at least “Alter Any Login” permission to be able to change the password for other logins, except for logins with “sysadmin” role.
Whenever SQL Server starts, it needs a minimum of below listed three startup
- Master database data file location (-d parameter)
- Errorlog file location (-e parameter)
- Master database transaction log file location (-l parameter)
PAGEIOLATCH_SH, CXPACKET, SOS_SCHEDULER_YIELD, ASYNC_NETWORK_IO etc
This can be done using Database Mail feature
Run the following query: SELECT * FROM sys.configurations WHERE name = ‘Database Mail XPs’GO
Enterprise, Standard, Express and Developer
Q45) Transparent data encryption (TDE) is a new feature in SQL Server that is available with which all editions?
We cannot use production data in Developer edition.
Always Encrypted technology, row-level security, dynamic data masking, transparent data encryption (TDE) etc
SQL Server 2017
Backup, Copy , Restore jobs