November 8, 2018

Sreekanth B

Huawei Most Frequently Asked Latest SQL Server Interview Questions Answers

What is Trigger and types of a trigger?

The trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.

2 types of triggers that are available in the SQL Server are as follows:

DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or on the view.
DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development database environments.
Logon Triggers: These are very special triggers which fire in case of the logon event of the SQL Server. This is fired before setup of a user session in the SQL Server.

What is the Subquery?

A Subquery is subset of select statements whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

Types of Sub-query:

Single-row sub-query: where the sub-query returns only one row
Multiple-row sub-query: where the subquery returns multiple rows, and
Multiple column sub-query: where the sub-query returns multiple columns

What is SQL Server used for?

SQL Server is one of the very popular Relational Database Management Systems. This is a product from the Microsoft to store and manage the information in the database.

Which language is supported by SQL Server?

SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.

Which is the latest version of SQL Server and when it is released?

SQL Server 2017 is the latest version of SQL Server that is available in the market and Microsoft launched this on 2 October 2017 with the support of the Linux O/S.

What is TCL in SQL Server?

TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.
Huawei Most Frequently Asked Latest SQL Server Interview Questions Answers
Huawei Most Frequently Asked Latest SQL Server Interview Questions Answers

Which TCL Commands are available on the SQL Server?

  There are 3 TCL Commands in the SQL Server. These are as follows:

Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction so as to provide the convenience that the transaction can be rolled back to the point wherever required.

What are the 2 types of classifications of constraints in the SQL Server?

Constraints are classified into the following 2 types in the SQL Server:

Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. Definition of these can be given at the time of the creation of a table in the database.
Table Types Constraints: These constraints are applied on a table and these are defines after the creation of a table is completed. Alter command is used to apply the table type constraint.

How is table type constraint applied to a table?

The Table Type Constraint is applied in the following way:

Alter Table Name of the Constraint

Alter Table Constraint_1

What are the different types of Columns Types Constraints in the SQL Server?

SQL Server provides 6 types of Constraints. These are as follows:

Not Null Constraint: This puts a constraint that the value of a column cannot be null.
Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
Check Constraint
Unique Constraint
Primary Key Constraint
Foreign Key Constraint

What are the various editions of SQL Server 2017 that are available in the market?

SQL Server 2017 is available in 4 editions. These are as follows:

Enterprise: This supports in leading the high performance for the Tier 1 database along with the capability of supporting business intelligence and workloads of advanced analytics.
Standard: This supports mid-tier applications to achieve fast performance. This can be easily upgraded to enterprise edition also without having any changes in the coding part.
Express: This is available for free and supports the building of web and mobile applications up to 10 GB in the size.
Developer: This supports building, testing and demonstrating applications in a non-production.

What are functions in the SQL Server?

Functions are the sequence of the statements which accepts inputs, process the inputs to perform some specific task and then provides the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.

What is a User-Defined function in the SQL Server and what is its advantage?

User-Defined Function is a function which can be written as per the needs of the user by implementing your own logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the needs.

This returns Scalar value or a table.

Explain creation and execution of a user-defined function in the SQL Server?

  A User-Defined function can be created in the following way:

Create Function fun1(@num int)

returns table

as

return select * from employee where empid=@num

This function can be executed as follows:

select * from fun1(12)

So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.

 What are the Pre-Defined functions in the SQL Server?

These are Built-In functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.

What is SQL Server Agent?

SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.

What is the PRIMARY KEY?

Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.

What is a UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Subscribe to get more Posts :