Mssql Interview Questions | Freshers & Experienced

MS SQL (Microsoft SQL) is a relational database management system developed by Microsoft released in the year 1989. Written in C & C++.

The MS SQL is primarily used for storing and retrieving the data that are requested by software applications. These software applications can run on the same or different computers across the network to access data from the MS SQL. This is proprietary software that is platform dependant to create and maintain databases. It has support for both the GUI and command-based interface.

It has support for two types of components such as workstation and server. There are different editions of MS SQL available that are maintained and supported by Microsoft for different kinds of users.

The trigger in the database is basically a type of stored procedure that is automatically executed. These are executed when the DDL or DML command statement related to the trigger is executed. They are mainly used to assess or evaluate data before or after the data modification using the DDL or DML statements. Triggers are also used to preserve the integrity of the data to control server operations and to audit a server.

Types of triggers are divided into four types.

1. DDL triggers
2. DML triggers
3. CLR triggers
4. logon triggers

• DDL triggers are used to observe and control actions performed on the server by triggering the DDL statements.
• DML triggers are used to trigger the DML statements.
• CLR statements are based on the Common Language Runtime in the .net framework.
• Logon triggers are triggered when a LOGON event of the SQL server is raised.

Blocking occurs when two process wants to access the same data and second is waiting for the first one to release. When a lock to the resource is held for a long time, they cause blocking. Blocking can be resolved as soon as the first process releases the resource.

In a deadlock, each process is waiting for other processes to release the resource. Deadlock can be ignored and assumed that it never occurs. Other steps to handle deadlocks are to detect and prevent it.

DML (Data Manipulation Language) is a type of programming language that is used to add, delete, and modify the data in the database. It is a subset of the SQL that comprises of data change statements to modify the data present in the database. It does not modify the structure of the database. The statements used in the DML are select, insert, update, and delete to modify the data in the database.

The join statement in the SQL is used to combine two or more rows from two or more databases. It combines the database based on the related column between the database.

Different types of join statements in the SQL are inner join, left join, right join, and full join.

• The inner join is used to combine the row that has matching values in both tables.
• The left join combines all the matched records from both the table with the left table.
• The right join combines all the matched records from both the table with the right table.
• The full join combines all the records from both the tables if there is a match on either side.

DCL (Data Control Language) is a type of programming language that is used for access control of the data in the database. It has commands GRANT and revokes to provide access control and authorization to the data present in the database.

• The GRANT command is used to give privileges to the database and
• The revokes command is used to withdraw the user privileges that are given by the GRANT command.

1. Insert statement syntax

INSERT INTO table_name (columns) VALUES (values)

The table_name is the table where you want to insert the values into the columns

2. Update statement syntax

UPDATE table_name SET column1 = value1, column2 = value2, … WHERE filter_expression

This updates the values of the column based on the filter expression that you mention.

3. Delete statement syntax

DELETE FROM table_name WHERE filter_expression

This deletes the value from the table based on the filter expression that you mention.

In PL/SQL, triggers are stored programs that are invoked when a specific condition is met. These programs are executed when some event occurs. It is executed when events like DML statements, DDL statements, and other database operations are executed. Using trigger, you can derive column values, enforce integrity, log or store information, impose security and prevent invalid transactions.

Normalization is used to organize the data in the database to a structured normal form. It is done to avoid the redundancy and different types of anomalies present in the database. Normalization organizes the rows and columns of a database to ensure their dependencies are properly enforced by the database integrity constraints. There are four types of normalization available. They are,

• First normal form - This ensures that an attribute can only hold an atomic value and not multiple values.
• Second normal form - It ensures the table follows the first normal form and that no non-prime attribute is dependant on the proper subset of any candidate key of the table.
• Third normal form - It ensures that the table follows the second normal form and the transitive functional dependency of a non-prime attribute on the super key is removed.
• Fourth normal form - It ensures the tables follow the third normal form and there are no non-trivial multivalued dependencies other than a candidate key.

The where clause is used to filter the rows before grouping and having clause is used to filter the records after grouping.

They differ based on how they use the GROUP BY clause. The other main difference is that where clause can be used without using the GROUP BY clause whereas the Having clause is used only if the grouping is performed by the GROUP BY clause.

The Properties of Sub-Query are as follows :

• A sub-query must be enclosed in the parenthesis.
• A sub-query cannot contain an ORDER-BY clause.
• A query can contain more than one sub-query.
• A sub-query must be put in the right hand of the comparison operator.

The different types of Sub-Query are as follows:

• Single-row sub-query -the sub-query returns only one row.
• Multiple-row sub-query - the sub-query returns multiple rows.
• Multiple column sub-query - the sub-query returns multiple columns.

SQL Profiler is one of the prominent methods of SQL analyser. There should be a system to trace the functions of SQL, and it has to be analysed. Such an order to determine the activities of SQL is called Profiler.

While using the profiler, the activities can be captured as a picture and used for future reference and analysis.

DROP - It removes a table from the database and it cannot be rolled back once dropped.

DELETE - It removes rows one at a time and records an entry in the transaction log for each deleted row.

TRUNCATE TABLE - It removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

The primary key is used to uniquely identify a record in the table so it cannot hold a null value. By default, a primary key is an index in the database table. A table can have only one primary key.

A foreign key is a field in one table that acts as a primary key in another table. You should manually create a foreign key and a table can have more than one foreign key.

The different types of Constraints used on Table Columns in SQL are as follows:

• DEFAULT Constraint − Provides a default value for a column when none is specified. 
• UNIQUE Constraint − Ensures that all values in a column are different.
• PRIMARY Key − Uniquely identifies each row/record in a database table.
• FOREIGN Key − Uniquely identifies a row/record in any of the given database tables.

The OOP concepts are basically the main ideas behind Object-Oriented Programming. It is consists of abstraction, encapsulation, inheritance, and polymorphism. Whereas Basic SQL concept can be defined as the database management concept which is used to store and manage data and information.

The main difference between the clustered index and primary key is, the clustered index helps to sort the data in the system by means of physical sorting. Whereas in the primary key, the data is assorted by means of programs.

The primary key can also be used to create a non clustered index.

The two authentication modes in SQL Server are as follows:

• Windows Authentication mode - It enables Windows Authentication and disables SQL Server Authentication.
• Mixed-mode - It enables both Windows Authentication and SQL Server Authentication.

The difference between Clustered and Non-Clustered Index can be stated as a Clustered index is a type of index in which table records are physically reordered to match the index. Whereas a Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on the disk.