Top SQL Server Interview Questions & Answers

Ans. A Database is an organised method of data storage that allows for quick data retrieval and effective data management. It is a set of tables where the data is arranged for storage. Rows sometimes referred to as records or tuples, and columns, usually referred to as attributes, make up a table. A couple of instances of databases include university management databases and bank management databases.

Ans. A relational database is a collection of data whose relationships to one another are known. There are rows and columns in it. In a database, each row represents a record, and each column contains data properties. A primary key is a distinctive ID that may be used to identify each row in a database. Foreign keys can also be used to link rows in one table to those in other tables.

Ans. RDBMS, or Relational DataBase Management System, is the data from a relational database that can be stored, managed, queried, and retrieved using this software. RDBMS can also conduct administrative duties including maintaining data storage, gaining access to data, and evaluating database performance in addition to interacting with users and the database.

Ans. The differences between SQL and MYSQL are tabulated below, 

Ans. Table relationships explain how a row in one table in a database is related to a row or rows in another table. There are three different kinds of relationships, as follows:

• One-to-one: When a row in one table is related to exactly one row in another table, it is said to be one-to-one.
• One-to-many: When a table's rows are related to one another in more than one way
• Many-to-many: When a row in one table has several linked rows in another, and vice versa.

Ans. In master data services models, entities are mere objects that are stored. They can be distinguished from other objects because they are actual, physical objects. An entity's columns represent the master data's properties, while its rows represent its members. Entities typically classify similar types of data together. a list of a company's employees, as an illustration.

Ans. The objects that makeup master data service entities are attributes. The members of the entity are also described using attribute values. There are three different kinds of qualities, as follows:

• Flexible characteristics
• Domain-based characteristics
• File Properties

Ans. A Microsoft RDBMS is called SQL Server. SQLOS and the database engine are the two main parts. The database engine has a relation engine that supports maintaining database files, pages, indexes, etc. as well as processing queries. And a SQL Operating system is all that SQLOS is. Operating systems like memory and I/O management are offered by SQLOS. Additionally, it provides synchronisation services and handles exceptions.

Ans. A transaction is an instance of a single task. Modifications to the data will be committed and permanently recorded in a database once a transaction is complete. The data modifications will be undone right away if there is a problem with the transaction. The transactional methods are as follows:

• Transaction auto-commit
• Deals that are made openly
• Unstated transactions
• Bounded-by-batch transactions

Ans. Every transaction's accompanying database alterations are recorded in the transaction log. The beginning of a transaction, modifications made during the process, and the end COMMIT or ROLLBACK of the transaction are all precisely recorded. Since it is used to restore the database to a consistent state in the event of a system failure, the transaction log is one of the crucial elements in database management.

Ans. The various SQL Database functions are listed below.

• Aggregate Functions: They operate on a collection of values and give back a single value
• Analytic Functions: Each group that they work with can have numerous rows returned to them
• Ranking Functions: They provide the row-by-row ranking value 
• Rowset Functions: Functions that return a rowset value can be used to reference a table
• Scalar Functions: They operate on a single value and only return a single value, making them scalar functions

Ans. When a program invokes a stored procedure, the compiled code that was used to create it is run. On the other hand, whenever a compilation and execution of the code takes place it is called a function. Additionally, a return is required when calling functions, while it is unnecessary when using stored functions. Additionally, stored procedures cannot be called from functions but can only be called from other functions.

Ans. Data can be processed, stored, and secured via the database engine. It both produces and executes database objects like stored procedures, views, and triggers. It manages queries, grants controlled access, and speeds up database transactions. On a single machine, an SQL server can support up to 50 instances of database engines.

Ans. The JOIN operator is used to integrate data from many tables depending on predefined criteria, which results in the creation of additional columns. In contrast, UNION uses SELECT commands to merge data from various tables, producing new rows. In JOIN statements, the number of columns retrieved from the tables is not required to be the same, however, in UNION statements, the number of columns retrieved from the tables is required to be the same.

Ans. A table should contain a key value and is described by a clustered index, which explains how data is kept there. Recognise that a table can only have one clustered index. A table is referred to as a clustered table if it contains a clustered index. Data is stored in tables unstructured when there is no clustered index present.

Ans. Within the same scope, this method retrieves the most recent identity value placed into an identity column. In this case, the only object that falls under the scope is a module, which will include a stored procedure, trigger, batch, and function. The existence of two statements in the same batch, stored procedure, or function indicates that they are within the same scope.

Ans. In a SQL server, a deadlock happens when two processes lock the same resource at the same time and wait for the other process to unlock it. In most cases, the SQL engine recognises this kind of incident and voluntarily terminates one of the processes, which aids in unlocking the lock. As a result, the SQL engine permits one process to finish successfully while halting another.

Ans. When connected to a SQL instance, local temporary tables are only visible to the table creators. Once the user disconnects from the SQL instance, the tables will be destroyed. Global temporary tables, however, are accessible to all users. Only when a user who has been using these tables disconnects from the SQL instance are these tables erased.

Ans. The Microsoft SQL Server Migration Assistant is also known as SSMA. The migration from Microsoft Access Database to SQL Server, Azure SQL Database, or Azure SQL Database Managed Instance is aided by this automated tool. Additionally, SQL Server migration for DB2, MySQL, Oracle, and SAP ASE is supported.

Ans. Data quality tasks like data correction, enrichment, standardisation, and de-duplication are supported by the knowledge-driven data quality platform known as DQS. Additionally, DQS uses profiling that is coupled with data-quality tasks to examine the consistency of the data. A Data Quality Server and a Data Quality Client are the two parts of DQS.

Ans. The correct data population in tables, strong relationships between tables, and the removal of data redundancy are all variables that affect how well SQL servers run. Therefore, the design must be properly developed while creating a new system or changing an old system in order to produce consistent performance.

Ans. A stored procedure is quicker than dynamic SQL since it is cached in the server's memory.

'Business logic' and 'database logic' are kept apart via stored procedures. In other words, you only need to alter the application scripts if there is a problem with the business logic. Similar to this, you only need to alter the stored procedures if there is a problem with the database logic.

Dynamic SQL generates considerable network traffic, compared to the stored procedure's modest network traffic.

While stored procedures using dynamic SQL are unable to discover faults prior to running, those using static SQL are able to do so.

Ans. A server table called Sys.SQL_Modules can be used to contain the stored procedure scripts. The names of stored procedures are also saved in the Sys. procedures table.

Ans. The advantages of using SQL server are as follows,

• Due to the fact that they are compiled and saved in executable form, stored procedures offer faster performance.
• A stored procedure is simply some code that may be used repeatedly to boost productivity.
• Instead of being kept on a client workstation, it might be kept in a database server. It accelerates the execution of queries as a result.
• Following a successful compilation, it can be used in a variety of applications.

Ans. The following are the various third-party tools used in SQL servers:

• Red Gate SQL Compare
• SSW SQL Deploy
• SSW SQL Server Auditor
•  Instant SQL formatter
• Apex SQL Doc
• LECCO SQL Expert
• Speed Ferret
• XPSMTP

Ans. The SQL Server configuration manager allows you to conceal the SQL Server database engine. It may be done in two easy stages as listed below:

• Right-click protocols after expanding the SQL server network setup and choosing properties.
• Select "YES" in the HideInstance box on the Flags tab, then click OK to clear the dialogue box

Ans. SQL Server Reporting Services (SSRS) is a collection of on-premises tools and services. Mobile and paginated reports can be created, deployed, and managed using SSRS.

• Paginated Reports: With the aid of new features and updated tools, they produce up-to-date reports.
• Mobile Reports: This category has a format that is mobile-friendly.
• Web portal: The web portal allows you to organise and examine paginated reports, excel workbooks, KPIs, and mobile reports. Any contemporary browser can be used to access the web portal.

Ans. Numerous aspects of the intelligent query processing (IQP) family enhance workload performance with minimal implementation effort. The numerous IQP features in this case include approximation QP, batch mode on rowstore, and adaptive joins. By activating database compatibility levels, IQP may be applied automatically to all server workloads.

Ans. A database technology known as in-memory OLTP (Online Transaction Processing) assists in improving the efficiency of numerous activities, including transaction processing, data intake, transitory data scenarios, and data load. Low latency, better throughput, and quicker response times are all features it offers.

Ans. Isolation is a characteristic of SQL transactions that is used to separate a SQL transaction from other transactions. As a result, it protects data integrity in the transactions. In order to prevent access by other transactions, the isolation feature assists in locking a row during a transaction. In order to ensure data concurrency in databases, SQL transactions enable five different levels of isolation.