October 11, 2018

Sreekanth B

EPAM Systems Most Frequently Asked Latest SQL Server Interview Questions Answers

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.
EPAM Systems Most Frequently Asked Latest SQL Server Interview Questions Answers
EPAM Systems Most Frequently Asked Latest SQL Server Interview Questions Answers

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.

What is Stored Procedure?

A stored procedure is a set of SQL queries which can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

List the advantages of using Stored Procedures?

Advantages of using Stored procedures are:

Stored procedure boosts application performance.
Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
Stored procedures can be reused.
Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
Stored procedures provide better security for your data.

Subscribe to get more Posts :