SQL Interview Questions

You can benefit from this article's popular SQL Server DBA interview questions and answers. To learn more about SQL interview questions, I would advise you to read this post as well as the SQL Server Interview questions and answers. I made an effort to be very specific in my responses to each question in this article as well.

What is SQLOS?

The SQL Server Operating System is referred to as SQLOS. The SQL Server Database Engine's lowest layer, SQLOS, is in charge of carrying out crucial internal duties like allocating CPU threads for SQL Server transactions, memory management, deadlock detection, and IO completion activities. For SQL Server operations, it functions exactly like another little operating system.

 

What you know about tempdb?

Temporary user objects that are specifically created are stored in the tempdb system database, which is a global resource. They consist of temporary stored procedures, global or local temporary tables and indexes, table variables, tables returned in table-valued functions, and cursors.

 

What happens if TempDB is full?

The TempDB database is unique in many ways, but one intriguing feature is that when its files expand automatically when they reach capacity, this expansion is not stored and will be undone upon the subsequent restart of the SQL Server service.

Why are database statistics important?

The performance of a query depends on statistics. Without them, the optimiser can only make educated guesses about the most effective combination of data entry points. Every table access becomes equivalent to a table scan. Because of how crucial they are, SQL Server automatically builds them for ad hoc queries.

 

What's the use of File Group in DB?

The principal data file and any secondary files that aren't included in other file groups are both included in the file group. To organise data files for administrative, data allocation, and placement needs, user-defined file groups can be formed.

 

What are pages in SQL Server?

Pages, the fundamental unit of data storage in SQL Server, are conceptually separated into portions of the disc space allotted to a data file. The size of a database page is 8 KB. A series of 8 KB pages in the data file are created each time you put data into a SQL Server database.

What can the possible cause of SQL Server connection issues be?

There are many reasons why you might be unable to connect to the SQL Server instance, including:

1. Be careful you input the correct connection information, including the server name and port number.
2. Make that the computer from which you are connecting to the instance has the SQL Server port open.
3. Ensure that the TCP or named pipe protocols are enabled in the SQL Server configuration manager and that the SQL Server instance is functioning.
4. No SQL browser service is active.

What does the Always On Availability Group's Automatic Seeding mean?

In order to set up the secondary replica, a function called automatic seeding was introduced in SQL Server 2016. Prior to this, there was only one way to backup, copy, and restore databases in order to initialize the secondary replica. When establishing the always-on availability group, we make use of this functionality. Despite the fact that Microsoft has provided this functionality, it is not appropriate for very large databases or if you have numerous databases on your SQL Server instance because it is a single-threaded procedure that degrades performance and can take many hours to setup your secondary replica.

 

Can you elaborate on the Basic availability groups' restrictions?

Database mirroring functionalities are replaced with basic availability groups. It allows us to keep a single secondary copy. When compared to conventional availability groups, its key drawback is listed below.

1. There can only be 2 replicas made. There is one major replica and one secondary replica.
2. Similar to database mirroring, only one database will function as an availability database.
3. Reading transactions, database backups, and other database maintenance tasks cannot be performed on the secondary replica.
4. Distributed availability groups cannot contain basic availability groups.
5. Basic availability groups cannot be upgraded to regular availability groups. They must be removed, and new standard availability groups must be configured.

Can a secondary database in the availability group have database snapshots created?

Yes, databases with the role status of either PRIMARY or SECONDARY can have database snapshots made. The role state of RESOLVING prevents the creation of database snapshots.

 

What effect will the SQL Server Agent job stopping to run have on log shipping?

All of the operations involved in sending logs rely on agent jobs. Log shipping cannot execute backup, copy, or restore operations if SQL Server Agent is suspended, and it may go out of sync if the agent is stopped over an extended period of time.

What distinguishes MS SQL Server from MySQL?

Name	MS SQL Server	MySQL
Define	Microsoft Relational Database Management System	Wide usage of Relational Database Management system
Primary DB Model	Relational DB Management System	Relational DB Management System
Secondary DB Model	Graph DBMS, Document & Value store	Document & Key-Value store
Developer	It is developed by Microsoft	It is developed by Oracle
Server OS	It supports Windows, Linux	It supports Linux, Solar winds, Windows, OS X
Supporting Languages	Java, PHP, Ruby, C#, C++, R, etc	Python, TCL, Scheme, Haskell, JavaScript, Ruby, C, C++, C# few more
FK(Foreign Key)	Yes they support	Yes they support
API's	JDBC, ODBC, OLE DB, TDS	ODBC, ADO.NET, JDBC
License Permit	Only Commercial	Open Source (Free)

What functions do the various replication components serve?

Publisher, Distributor, and Subscriber make up the three primary parts of replication. A publication's data source is the publisher. The distribution of the database objects to one or more destinations is the distributor's responsibility. The recipient of the publisher's data's copies and replications is the subscriber.

 

What are the various replication techniques, and why are they implemented?

Snapshot, Transactional, and Merge Replication are the three main methods of replication. The kind of replication you select will depend on the conditions and/or objectives you're aiming to meet. Like a monthly summary table or a product list table, etc., Snapshot Replication is only helpful when the data inside the tables does not change frequently and the amount of data is not too vast.

When keeping a copy of a transactional table, such as sales order tables, etc., transactional replication is helpful. Merge Replication is more helpful in remote or dispersed systems where data flow can come from multiple locations, such as sales made at a retail store.

 

What is Transparent Data Encryption?

Transparent Data Encryption (TDE), which was first introduced in SQL Server 2008, is a way through which you can encrypt the SQL Server Database files to prevent unwanted access. Additionally, TDE has the ability to safeguard the instance's database backups.