Top 25+ MySQL DBA Interview Questions & Answers | Learn NOW

Last updated on 04th Jul 2020, Blog, Interview Questions

A MySQL Database Administrator (DBA) plays a crucial role in the management of MySQL database systems, overseeing various aspects of their configuration, performance, and security. A primary responsibility is the installation and configuration of MySQL databases, ensuring they are set up optimally and align with organizational requirements. DBAs actively contribute to database design, collaborating with developers to create efficient and scalable database structures.

1. What is database?

Ans:

A database is a structured collection of data that is organized in a way that allows for efficient storage, retrieval, and management of information. It serves as a centralized repository for storing and managing data in a way that facilitates data organization, retrieval, and update. In a database, data is typically organized into tables, where each table consists of rows and columns. Each row in a table represents a record, and each column represents a field or attribute of that record.

2. What is relational database?

Ans:

A relational database is a type of database that organizes data into tables, which consist of rows and columns. The relational model was introduced by E.F. Codd in 1970, and it forms the basis for the design and management of relational databases. In a relational database, data is organized in a structured manner to establish relationships between different pieces of information.

3. Differentiate between SQL and MYSQL?

Ans:

• SQL is a standardized programming language specifically designed for managing and manipulating relational databases. MySQL is a relational database management system (RDBMS) that uses SQL as its query language.
• SQL is used for defining and managing the structure of relational databases, as well as for querying and modifying the data stored in these databases. MySQL is a specific implementation of an RDBMS. It provides a database server that allows users to create, manage, and interact with relational databases using SQL.

4. What do you mean by Relationships between Tables and mention their Types?

Ans:

In a relational database, relationships between tables are established to link rows in one table to related rows in another table. These relationships help maintain data integrity, reduce redundancy. There are several types of relationships between tables:

• ONE TO ONE(1 :1 )RELATIONSHIP
• ONE TO MANY (1:N) RELATIONHIP
• MANY TO ONE (1:N) RELATIONSHIP
• MANY TO MANY (N:N) RELATIONSHIP

5. What is an entity in database?

Ans:

In the context of a database, an entity refers to a distinct and identifiable object or concept about which data can be stored. In the relational database model, entities are typically represented as tables. Each row in the table represents a specific occurrence or instance of the entity, and each column represents a property or attribute of that entity.

6. What is SQL Server, and mention its core components?

Ans:

SQL Server, developed by Microsoft Corporation, is a relational database management system (RDBMS) that provides a comprehensive and scalable platform for managing and organizing databases. It is a part of the Microsoft SQL Server product line and is widely used in various enterprise-level applications. SQL Server supports a range of transaction processing, business intelligence, and analytics workloads.

7. What do you mean by Attributes and mention their Types?

Ans:

Attributes in the context of a database refer to the properties or characteristics that describe an entity. Each entity in a database is associated with a set of attributes that define the information that can be stored about instances of that entity. Here are common types of attributes:

• A simple attribute is an atomic attribute that cannot be divided any further.
• A composite attribute is made up of multiple simple attributes.
• A derived attribute is one whose value is derived or computed from other attributes in the database.
• A multi-valued attribute can hold multiple values for a single entity.
• A key attribute is part of the primary key for an entity.
• A single-valued attribute holds a single value for each instance of an entity.

8. What is a transaction in SQL Server and mention its modes?

Ans:

In SQL Server, a transaction is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions are used to ensure the consistency and integrity of the database by providing a way to group multiple SQL statements into a single, atomic operation.

9. What is Database Normalisation and Denormalisation?

Ans:

	Aspect	Normalization	Denormalization
Definition	Standards NormalizationDefinition of Denormalizationthe method of data organization that enhances data integrity and eliminates redundancy.	the deliberate addition of redundancy with the goal of enhancing query performance.
Objective	Reduce the amount of redundant and reliant data.	Streamline data retrieval and maximize query performance.
Redundancy	Inconsistencyaims to reduce or eliminate redundancy by organizing data and dissecting tables.	Introduces redundancy by copying data between tables in order to improve performance.
Dependency	Reliance Strives to preserve data consistency and lessen reliance on data.	Could result in data dependency because data is repeated between tables.
Update Anomalies	Update Anomalies Redundancy is decreased to mitigate update anomalies.	Could cause update anomalies because of duplicate data.

10. What is a Transaction Log, and why is it important?

Ans:

Consistency and durability in a database, like SQL Server, are largely dependent on the transaction log. Among its principal functions are:

• Recovery: It is necessary to ensure data integrity and recover the database following system failures.
• Rollback Operations: In the event that a transaction fails or a user initiates a rollback, the transaction log assists in reversing the changes.
• Rollforward Operations: Throughout recovery procedures, it contributes to rollforward operations.

11. What is stored procedure?

Ans:

A stored procedure in a database is a precompiled collection of one or more SQL statements or procedural statements. It is stored in the database and can be invoked by name, providing a convenient way to encapsulate and reuse functionality in the database. Stored procedures are typically written in a procedural language specific to the database management system (DBMS) being used, such as PL/SQL for Oracle, T-SQL for Microsoft SQL Server, or PL/pgSQL for PostgreSQL.

12. What is the use of an Extended Stored Procedure?

Ans:

Extended Stored Procedures (XPs) were a feature in older versions of Microsoft SQL Server (prior to SQL Server 2005) that allowed developers to create custom functions using a language like C or C++. Extended Stored Procedures were essentially DLLs (Dynamic Link Libraries) registered with SQL Server and executed within the SQL Server process.

13. What is a recursive stored procedure?

Ans:

A recursive stored procedure is a stored procedure that calls itself during its execution. This allows the procedure to perform repetitive or iterative tasks by invoking itself with different parameters. Recursive stored procedures are often used to traverse hierarchical or tree-like structures, process nested data, or solve problems that can be naturally expressed in a recursive manner.

14. What is a JOIN, and mention its types?

Ans:

In relational databases, a JOIN is a mechanism that combines rows from two or more tables based on a related column between them. The purpose of a JOIN is to retrieve data from multiple tables in a meaningful way, allowing for the correlation of data across tables. Here are the main types of JOIN operations:

INNER JOIN

Example:

LEFT (OUTER) JOIN:

Example:

15. What is Subquery in SQL Server?

Ans:

In SQL Server, a subquery (also known as a nested query or inner query) is a query embedded within another query. A subquery is enclosed within parentheses and is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE, and HAVING clauses.

16. What are defaults in the SQL Server?

Ans:

In SQL Server, a default is a constraint that is associated with a column to specify a default value for the column. When a new record is inserted into a table and a value for the column with a default constraint is not specified, the default value is used. Defaults provide a way to ensure that a specific value is used for a column when no value is explicitly provided during an INSERT operation.

17. What are Cursors, and mention their types?

Ans:

In SQL Server, a cursor is a database object that allows for the iterative processing of a result set, typically returned by a SELECT statement. Cursors provide a way to navigate through a set of rows and perform operations on each row individually. While cursors can be useful in certain scenarios, they should be used judiciously as they can have performance implications, and set-based operations are generally preferred in SQL.

18. What are triggers, and mention their types in the SQL server?

Ans:

In SQL Server, a trigger is a database object associated with a table or view that is automatically executed (or fired) in response to certain events on the table or view. Triggers are used to enforce business rules, perform actions when data changes, or maintain data integrity.

Types of Triggers in SQL Server:

• AFTER Triggers
• INSTEAD OF Triggers
• AFTER Triggers (CLR)
• DML Triggers (Data Manipulation Language)
• DDL Triggers (Data Definition Language)

19. When can you use triggers in the SQL server?

Ans:

Triggers in SQL Server can be used in various scenarios to enforce business rules, maintain data integrity, and perform specific actions in response to database events. Here are common situations where you might use triggers:

• Enforcing Business Rules: Triggers can be used to enforce complex business rules or validation checks that involve multiple tables or conditions.
• Audit Logging: Triggers can be employed to create audit logs by automatically capturing information about changes to data, such as who made the change
• Derived Columns: Triggers can be used to maintain derived columns or computed columns that are based on other columns in the same or different tables.

20. What is Referential Integrity?

Ans:

Referential integrity is a concept in relational database management systems (RDBMS) that ensures the consistency and accuracy of relationships between tables. It is a set of rules and constraints that maintain the integrity of relationships by enforcing the validity of foreign key references.

21. What do you mean by CTE?

Ans:

CTE stands for Common Table Expression in SQL. It is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex queries more readable and maintainable by breaking them into modular, named components. A CTE is defined using the WITH clause, and it can be referenced within the query that follows its definition.

22. What is an SQL Server Agent?

Ans:

In SQL Server, a check constraint is a rule defined on a column or a combination of columns that enforces the validity of data entered into a table. Check constraints are used to ensure that the values in a specified column or columns meet certain conditions or criteria. Here’s the basic syntax for creating a check constraint:

23. What is a Sparse Column?

Ans:

In SQL Server, a sparse column is a column that is optimized for storing NULL values. Sparse columns allow you to efficiently store and query data where a significant number of the values in a column are NULL. This can be especially useful when dealing with tables that have a large number of columns with mostly NULL values, as sparse columns can reduce storage space and improve query performance.

24. What do you mean by Shared, Exclusive, and Updated locks

Ans:

In the context of a relational database management system (RDBMS) like SQL Server, locks are mechanisms used to control access to resources, such as rows or tables, to maintain data consistency and integrity in a multi-user environment. There are several types of locks, including shared locks, exclusive locks, and update locks, each serving different purposes.

• Shared Locks: Shared locks are used when a transaction wants to read a resource and indicates.
• Exclusive Locks: Exclusive locks are used when a transaction wants to modify a resource and indicates.
• Update Locks: Update locks are used to indicate an intent to update a resource.

25. What is SQL Server Profiler?

Ans:

SQL Server Profiler is a tool provided by Microsoft SQL Server that allows database administrators, developers, and other users to capture and analyze events occurring within a SQL Server instance. It provides a graphical user interface for monitoring and capturing SQL Server events, such as queries, stored procedures, and other database activities. SQL Server Profiler is part of the SQL Server Management Studio (SSMS) suite of tools.

26. What do you mean by Check Constraints?

Ans:

In SQL Server, a check constraint is a rule defined on a column or a combination of columns that enforces the validity of data entered into a table. Check constraints are used to ensure that the values in a specified column or columns meet certain conditions or criteria.

27. What is COALESCE in SQL server and mention its few properties?

Ans:

COALESCE is a function in SQL Server that returns the first non-null expression among its arguments. It is commonly used to provide a default value when working with nullable columns or expressions. The COALESCE function takes multiple parameters, and it evaluates them in order from left to right until it finds the first non-null value.

Here is the basic syntax of the COALESCE function:

28. What is the use of the SIGN function?

Ans:

In SQL Server, the SIGN function is used to determine the sign of a numeric expression. It returns an indication of the sign of the input value, which can be one of the following values:

1: Indicates a positive number, 0: Indicates zero, -1: Indicates a negative number.

Here is the basic syntax of the SIGN function:

29. What is BULK COPY in SQL server?

Ans:

Ans:

In SQL Server, BULK COPY refers to a set of technologies and techniques that allow the efficient and high-performance loading of large amounts of data into SQL Server tables. The purpose of BULK COPY operations is to streamline the process of inserting or updating massive volumes of data, typically from external data sources or files, into SQL Server tables.

30. What do you mean by Collation?

Ans:

Collation in SQL Server refers to the set of rules that govern how string comparison and sorting operations are performed in a database. It defines how characters are compared, and it influences the order of the result set when sorting data. Collation settings are essential in multilingual environments where different languages and character sets are used.

31. What is a TABLESAMPLE?

Ans:

The TABLESAMPLE clause in SQL Server is used to retrieve a random subset of rows from a table or a view. It provides a way to sample a fraction of the data from a large table, which can be useful for testing queries, performing statistical analysis, or working with large datasets where a full result set may be impractical.

The basic syntax of the TABLESAMPLE clause is as follows:

32. What is the use of the UPDATE_STATISTICS command?

Ans:

The UPDATE STATISTICS command in SQL Server is used to update the statistical information about the distribution of values in the columns of a table or indexed view. This statistical information is crucial for the SQL Server query optimizer to generate efficient execution plans for queries. The query optimizer relies on statistics to make informed decisions about the most efficient way to retrieve and process data.

33. What is a Filtered Index?

Ans:

A filtered index is a type of non-clustered index in SQL Server that includes only a subset of rows in a table based on a filter predicate. Unlike a traditional non-clustered index that includes all rows in a table, a filtered index is designed to index a specific subset of data that meets a certain condition. This can lead to more efficient use of storage and improved query performance for specific types of queries.

34. What do you mean by scheduled tasks in SQL server?

Ans:

In the context of SQL Server, “Scheduled Tasks” typically refers to the automated execution of specific operations or jobs on a predefined schedule. SQL Server provides mechanisms for scheduling and automating tasks through the use of SQL Server Agent. SQL Server Agent is a component of the SQL Server database engine that enables the creation and scheduling of jobs to automate various database-related activities.

35. What is Database Mirroring, and mention its benefits?

Ans:

Database Mirroring was a high-availability feature in Microsoft SQL Server, introduced in earlier versions of SQL Server (prior to SQL Server 2012). It provided a way to maintain a hot standby copy of a database on a separate server, ensuring data availability and disaster recovery.

Benefits:

• High Availability: Database Mirroring provides a high-availability solution by maintaining a standby copy of the database.
• Automatic Failover: In high-safety mode with a witness server, Database Mirroring supports automatic failover.
• Data Protection: Continuous data synchronization between the principal and mirror databases ensures that data loss is minimized in the event of a failure.

36. What are the various SQL Database Functions?

Ans:

SQL database functions can be categorized into several types based on their purposes. Here are some common types of SQL database functions:

• Aggregate Functions: Perform a calculation on a set of values and return a single value.
• Scalar Functions: Operate on a single value and return a single value.
• String Functions: Manipulate and analyze character string data.
• Date and Time Functions: Perform operations on date and time value.
• Conversion Functions: Convert data from one type to another.

37. What is SQL injection?

Ans:

SQL injection is a type of security vulnerability that occurs when an attacker is able to manipulate an SQL query in a way that it executes unintended commands or gains unauthorized access to a database. This is achieved by injecting malicious SQL code into input fields or parameters that are part of an SQL query. SQL injection is a common and serious threat to the security of web applications that interact with databases.

38. What is the difference between Stored Procedures and Functions?

Ans:

Stored Procedures:

• Can return zero or more values.
• Can have input and output parameters.
• Cannot be used in SELECT or WHERE clauses.
• Can have transaction management within (BEGIN TRANSACTION, COMMIT, ROLLBACK).

Functions:

• Must return a single value.
• Can only have input parameters.
• Can be used in SELECT or WHERE clauses.
• Cannot have transaction management.

39. Can you mention the different types of Queries in SQL Servers?

Ans:

• SELECT Query: Retrieves data from one or more tables.
• INSERT Query: Adds new records to a table.
• UPDATE Query: Modifies existing records in a table.
• DELETE Query: Removes records from a table.
• CREATE Query: Creates a new table, view, index, etc.
• ALTER Query: Modifies the structure of an existing database object.
• DROP Query: Deletes an existing database object.

40. How can you differentiate between UNION and UNION ALL?

Ans:

• UNION: Combines the result sets of two SELECT statements, eliminating duplicate rows.
• UNION ALL: Combines the result sets of two SELECT statements, including all rows (including duplicates).

41. What is the use of Database Engine in the SQL Server?

Ans:

The Database Engine in SQL Server is responsible for storing, processing, and securing data. It manages the physical storage of data, enforces data integrity, and supports query processing and optimization. It includes components such as the query processor, storage engine, and security subsystem.

42. What do you mean by Clustered Indexes?

Ans:

• Specifies the order in which data is physically stored on disk.
• Determines the order of data rows in a table.
• There can be only one clustered index per table.
• Typically enhances the speed of data retrieval operations.

43.How can you use the SCOPE_IDENTITY function in the SQL server?

Ans:

Returns the last identity value generated in the current scope (session). Commonly used after an INSERT operation with an identity column to retrieve the newly generated identity value.

44. What is SSMA in SQL server?

Ans:

SQL Server Migration Assistant (SSMA) is a tool to migrate databases from other database systems (e.g., Oracle, MySQL) to SQL Server. It automates the migration process and converts database objects, such as tables, views, and stored procedures, to SQL Server equivalents.

45. Brief the different types of JOINS?

Ans:

• INNER JOIN: Retrieves rows where there is a match in both tables.
• LEFT (OUTER) JOIN: Retrieves all rows from the left table and matching rows from the right table.
• RIGHT (OUTER) JOIN: Retrieves all rows from the right table and matching rows from the left table.
• FULL (OUTER) JOIN: Retrieves all rows when there is a match in either the left or right table.

46. What are the four types of physical JOIN operations?

Ans:

• Nested Loop Join
• Hash Match Join
• Merge Join
• Cross Join

47. What is the difference between JOIN and UNION in SQL server?

Ans:

• JOIN: Combines rows from two or more tables based on a related column.
• UNION: Combines result sets of two SELECT statements, and it does not involve matching columns.

48. What do you mean by Data Quality Services (DQS)?

Ans:

DQS is a SQL Server feature for data cleansing and enrichment. It helps maintain data accuracy and consistency by providing tools to create and manage knowledge bases for data quality.

49. What are SQL Server Integration Services and their functions?

Ans:

SSIS is a platform for building enterprise-level data integration and transformation solutions. It enables the creation of data workflows for tasks like ETL (Extract, Transform, Load) and data cleansing.

50. What is the use of WITH TIES?

Ans:

Used in conjunction with the ORDER BY clause in a TOP (or FETCH) clause. Retrieves additional rows that have the same values as the last row in the result set. Ensures that if there are tied rows for the last position, all tied rows are included in the result.

51. How can you distinguish between SUBSTR and CHARINDEX?

Ans:

• SUBSTR: Used in some databases (e.g., Oracle), extracts a substring from a string based on starting position and length.
• CHARINDEX: Used in SQL Server, returns the starting position of a substring within a string.

52. When to execute COMMIT and ROLLBACK commands?

Ans:

• COMMIT: Saves all the changes made during the current transaction to the database.
• ROLLBACK: Undoes all the changes made during the current transaction.

53. How can you distinguish between GETDATE and SYSDATETIME functions?

Ans:

• GETDATE: Returns the current date and time in SQL Server.
• SYSDATETIME: Returns the current date and time with fractional seconds, providing higher precision.

54. What is the FLOOR function in the SQL server?

Ans:

The FLOOR function in SQL Server rounds a numeric value down to the nearest integer that is less than or equal to the original value. For example, FLOOR(5.7) returns 5.

55. What is the use of SQL server locks, and what resources can be locked by server locks?

Ans:

SQL Server locks are mechanisms used to manage concurrent access to shared resources, preventing conflicts and maintaining data integrity. Resources that can be locked include rows, pages, tables, and databases.

56. How can Deadlocks in the SQL server be resolved?

Ans:

The resolution of deadlocks in SQL Server requires a complex strategy to handle resource contention between transactions. First off, programs such as SQL Server Profiler help locate and examine deadlock data, offering valuable information about the entangled transactions. Contention can be reduced by modifying the transaction isolation level, for example, by employing READ COMMITTED SNAPSHOT ISOLATION. Locking during reads can be avoided by using the NOLOCK hint in SELECT statements, however caution is advised because reading uncommitted data may cause problems.

57. How can you compare the Local and Global Temporary tables?

Ans:

In SQL Server, local temporary tables and global temporary tables are two different kinds of temporary storage structures made to meet various scope and accessibility needs. Local temporary tables are only visible for the duration of a session and are denoted by a single ‘#’ prefix. They are made for the purpose of temporarily storing data during a particular session, and they are deleted automatically at the end of that session.

58. How to delete a table using SQL Server Management Studio?

Ans:

• In SSMS, right-click on the table you want to delete.
• Choose “Delete” from the context menu.
• Confirm the deletion in the dialog box.

59. Compare the difference between Clustered Index and the Non-clustered Index?

Ans:

Clustered Index:

• Determines the physical order of data rows in a table.
• Only one per table.
• Typically enhances the speed of data retrieval.

Non-clustered Index:

• Does not affect the physical order of data rows.
• can have multiple per table.
• Requires an additional lookup step to locate the actual data.

60. Differentiate: Derived attribute, Derived persistent attribute, and Computed attribute.

Ans:

• Derived Attribute: Calculated from other attributes but not physically stored.
• Derived Persistent Attribute:Calculated and stored in the database.
• Computed Attribute: Similar to a derived attribute but is computed dynamically when queried.

61. What are the different levels of normalization, and explain them shortly?

Ans:

• 1NF (First Normal Form): Eliminate duplicate columns from the same table.
• 2NF (Second Normal Form): Meet 1NF and remove partial dependencies.
• 3NF (Third Normal Form): Meet 2NF and eliminate transitive dependencies.

62. What is the use of the SET NOCOUNT function?

Ans:

The SET NOCOUNT function in SQL Server is used to suppress the message indicating the number of rows affected by a T-SQL statement. It improves performance by reducing network traffic, especially in stored procedures.

63. What do you mean by Magic Tables in SQL server?

Ans:

Magic Tables in SQL Server are virtual tables automatically created and populated during trigger execution. The “inserted” table holds new values, and the “deleted” table holds old values, providing a way to reference data before and after a DML operation in triggers.

64. How can you prevent SQL injection vulnerabilities?

Ans:

To prevent SQL injection vulnerabilities, employ parameterized queries, stored procedures, and input validation. Avoid dynamic SQL concatenation with user input, ensuring that input is treated as data rather than executable code.

65. What is the difference between DELETE and TRUNCATE commands?

Ans:

• DELETE: Removes rows one by one, generates individual log entries, and can be rolled back.
• TRUNCATE: Removes all rows at once, generates minimal log entries, and cannot be rolled back.

66. Compare Local Variables and Global Variables?

Ans:

• Local Variables: Declared within a specific scope, such as a stored procedure, and exist only during the execution of that scope.
• Global Variables: Declared at the database level and persist across different scopes and sessions.

67. What is the difference between OLAP and OLTP?

Ans:

OLAP (Online Analytical Processing):

• Designed for complex queries and analysis.
• Data is typically historical and aggregated.
• Read-intensive.

OLTP (Online Transaction Processing):

• Designed for transactional processing and real-time data.
• Data is current and normalized.
• Write-intensive.

68. What do you mean by the recovery model in SQL Server and its types?

Ans:

The recovery model in SQL Server determines how transactions are logged and defines the options for data recovery after a failure. Types include SIMPLE (minimal logging), FULL (full logging), and BULK-LOGGED (optimized for bulk operations).

69. What are the different types of backups used in SQL servers?

Ans:

Different types of backups in SQL Server include Full Backup (complete database backup), Differential Backup (captures changes since the last full backup), and Transaction Log Backup (backs up transaction log entries for point-in-time recovery).

70. How can you use HAVING and WHERE clauses in a single query?

Ans:

In a single query, the WHERE clause is used to filter rows based on conditions, while the HAVING clause is applied to filter aggregated results, typically in GROUP BY queries.

71. Write a query to display employee details working in the EEE department?

Ans:

SELECT * FROM Employee WHERE Department = ‘EEE’;

The “Employee” table’s columns for workers in the Electrical and Electronic Engineering (EEE) department are all retrieved by this query. WHERE filters the results so that only records with the department set to ‘EEE’ are included.

72. Write a query to display employee details with their department names?

Ans:

Uses an inner join based on the DepartmentID to obtain employee details and the names of the corresponding departments from the Employee and Department tables.

73. Write a query to display employee details along with department_name and their age between 21 to 25?

Ans:

Adds a filter for workers who are between the ages of 21 and 25 to the preceding query.

74. Write a query of employee details whose salary is above 23000 and age is above 22 and working in the CSE department?

Ans:

Retrieves information about workers in the Computer Science and Engineering (CSE) department who are over 22 years old, have salaries over Rs. 23,000, and are specifically employed in this department using a combination query and conditional filters.

75.How important is Database Design in SQL servers?

Ans:

Database design is crucial in SQL Server for several reasons. It ensures data integrity, improves performance, supports scalability, enhances security, and facilitates maintainability. A well-designed database aligns with business requirements, reduces redundancy, and provides a solid foundation for efficient data management.

76. How can you hold the Stored Procedure scripts in the SQL server?

Ans:

Stored procedures in SQL Server are stored in the database itself. They can be created, modified, and dropped using SQL Server Management Studio (SSMS) or script files. The scripts are typically stored in the system tables within the database.

77. Can you mention the advantages of Stored Procedures over Dynamic SQL?

Ans:

• Performance: Stored procedures are precompiled, leading to better performance compared to dynamically generated SQL statements.
• Security: Stored procedures can provide a layer of security by restricting direct access to tables and allowing controlled data manipulation.
• Maintenance: Changes to database schema can be encapsulated within stored procedures, reducing the impact on application code.
• Reusability: Stored procedures can be reused across multiple parts of an application, promoting code reusability.

78. What are the advantages of using stored procedures in an SQL server?

Ans:

• Improved performance through precompilation.
• Enhanced security by restricting direct access to tables.
• Simplified maintenance and changes to database schema.
• Code reusability across different parts of an application.
• Centralized management of business logic.

79. How can you hide an instance of the SQL Server database engine?

Ans:

To hide an instance, configure SQL Server to use a non-default port. Use firewall rules to control access to the SQL Server instance. Minimize the services and protocols enabled for the instance. Implement security best practices to protect the instance from unauthorized access.

80. How can you alter a table schema in an SQL server?

Ans:

Use the ALTER TABLE statement to modify the table schema. Add or drop columns, change data types, or set constraints. Be cautious about the potential impact on existing data and applications.

81. What do you mean by SSRS in SQL Server?

Ans:

SQL Server Reporting Services (SSRS) is a server-based reporting platform. It enables the creation, management, and deployment of reports. SSRS supports various data sources and provides a web-based interface for report access.

82. What are the different third-party tools used in SQL servers?

Ans:

• SQL Server Management Studio (SSMS).
• Redgate SQL Toolbelt.
• Quest TOAD for SQL Server.
• IDERA SQL Diagnostic Manager.
• SentryOne SQL Sentry.
• ApexSQL tools.
• ERwin Data Modeler.

83. What do you mean by in-memory OLTP database?

Ans:

• In-Memory OLTP is a feature in SQL Server for high-performance transaction processing.
• It utilizes memory-optimized tables and natively compiled stored procedures.
• Improves concurrency and reduces contention for heavily accessed tables.

84. What are the benefits of Partially Contained Databases?

Ans:

• Simplifies database management by reducing dependencies on server-level configurations.
• Allows users to connect to the database without a login on the server.
• Facilitates database portability across different SQL Server instances.

85. What is the use of isolation level in SQL transactions?

Ans:

Defines the extent to which one transaction is isolated from the effects of other concurrently executing transactions. Common isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Balances data consistency and concurrency.

86. What is the Columnstore Index, and why should you use it?

Ans:

A columnstore index is an index optimized for analytical queries and data warehousing. Stores and manages data in columnar format, improving compression and query performance for large datasets.

87. Compare Triggers with Event Notifications?

Ans:

• Triggers: Execute in response to a specific event (e.g., INSERT, UPDATE, DELETE) on a table. Implicitly part of the transaction.
• Event Notifications: Notify external processes about specific events. Asynchronous and decoupled from the transaction.

88. How to improve query performance in SQL server?

Ans:

• Use proper indexing strategies.
• Update statistics regularly.
• Consider partitioning for large tables.
• Optimize queries and avoid unnecessary joins.
• Monitor and analyze execution plans.

89. What is FILESTREAM, and when can you use it?

Ans:

FILESTREAM is a feature in SQL Server for storing and managing large binary data, such as images and documents, outside the database. Combines the advantages of file system storage with the capabilities of relational databases.

90. What do you mean by Graph Database?

Ans:

A graph database is a type of NoSQL database that uses graph structures with nodes, edges, and properties to represent and store data. Nodes represent entities, edges represent relationships, and properties provide additional information about nodes and edges.

91. How can we improve the performance of poor-performing SQL queries?

Ans:

• Identify and resolve blocking issues.
• Tune queries using tools like SQL Server Profiler and Database Engine Tuning Advisor.
• Consider index optimization and database schema redesign.
• Monitor server resources and optimize hardware configuration.
• Use query hints and optimizations based on specific scenarios.

92. How to use Service Broker in SQL server?

Ans:

• Service Broker is a messaging framework in SQL Server for building scalable and reliable queuing applications.
• Steps to use Service Broker include creating a message type, contract, queue, and service, then sending and receiving messages using the SEND and RECEIVE statements.

93. How does intelligent query improve the performance of SQL Server workloads?

Ans:

It is a set of features in SQL Server that enhances query performance. Includes features like batch mode execution, adaptive joins, and table variable deferred compilation. Optimizes query plans dynamically based on runtime statistics.

94. What is SQL Latch Contention, and how can it be avoided?

Ans:

SQL Latch Contention occurs when multiple processes contend for access to a latch, a synchronization mechanism in SQL Server. It can be avoided by optimizing queries, reducing contention for shared resources, and ensuring that indexes and queries are properly tuned.

95. What is the use of views in SQL server, and mention its types?

Ans:

Views are virtual tables based on the result of a SELECT query, allowing users to simplify complex queries and control access to specific columns.

Types of views include:

• Simple Views: Based on a single table.
• Complex Views: Based on multiple tables and may involve JOIN operations.
• Indexed Views: (Materialized Views): Physical storage of the view result for improved performance.

96. What are the two execution modes in the Database Engine in the SQL server?

Ans:

• Batch Execution Mode: Used for processing multiple rows at once, optimized for set-based operations.
• Row Execution Mode: Processes rows one at a time, suitable for row-by-row operations.

97. What is Spinlock contention, and how to resolve it?

Ans:

Spinlock Contention happens when multiple threads compete for access to a spinlock, a lightweight synchronization mechanism. Resolving spinlock contention involves optimizing code, reducing contention points, and ensuring efficient resource utilization.

98. Mention a few subquery restrictions in the SQL server?

Ans:

Subqueries that return multiple rows or columns are not allowed in certain contexts, such as a SELECT statement that expects a single value. The ORDER BY clause is not allowed in subqueries used in certain parts of a query.

99. What are Data Cleansing and Data Matching in SQL server?

Ans:

• Data Cleansing: Involves identifying and correcting errors or inconsistencies in data to improve its quality. Techniques include standardization, validation, and enrichment.
• Data Matching: Involves identifying and linking similar or duplicate records in a dataset. It helps maintain data accuracy and consistency.

100. Explain the Pattern Matching in the SQL server?

Ans:

Pattern Matching in SQL Server involves using the LIKE operator with wildcard characters (% and _), regular expressions, or the CONTAINS predicate for full-text search. It is used to search for patterns or specific values within strings or text data.