Before Applying job posting in any websites or recruiting agencies or sending resumes to job recruiters or any other employment website you need to improve your skills on general knowledge, Aptitude, Puzzles, logical reasoning, general english, database, programming as per your job profile. Job recruiters or mostly preferring job candidates who are having more stuff in those categories mentioned above. So improving more skills on your job profile always boost up your career and your performance. Even hiring companies and job recruiters who are looking for many job posting websites actually they are looking and filtering on these activities. In these Sql Server section you need to update more questions to perform well in front of job recruiters and companies. Sql Server Interview questions are most important to score in interview, Update all questions to score well in all rounds in job interview.

Sql Server interview questions and answers

11)
What is the purpose of Replication ?

Replication is way of keeping data synchronized in multiple databases. SQL server replication has two important aspects publisher and subacriber.

Publisher
Database server that makes data available for replication is known as Publisher.

Subscriber
Database servers that get data from the publishers is called as Subscribers.

12)
What is the difference between Stored Procedure and user defined Function?

Following are some major differences between a stored procedure and user defined functions:

The main purpose of UDF was to increase reuse in stored procedure. So you can call UDF from a stored procedure and not vice versa.
You cannot change any data using UDF while you can do everything with a stored procedure . UDF does not return output parameters while SP’s return output parameters.

13)
Can we suggest locking hints to SQL SERVER ?

We can give locking hints that helps you over ride default decision made by SQL Server. For instance, you can specify the ROWLOCK hint with your UPDATE statement to convince SQL Server to lock each row affected by that data modification. Whether it is prudent to do so is another story; what will happen if your UPDATE affects 95% of rows in the affected table? If the table contains 1000 rows, then SQL Server will have to acquire 950 individual locks, which is likely to cost a lot more in terms of memory than acquiring a single table lock. So think twice before you bombard your code with ROWLOCKS.

14)
What are the different types of triggers in SQL SERVER?

There are two types of triggers:

INSTEAD OF triggers
INSTEAD OF triggers fire in place of the triggering action. For example, if an INSTEAD OF UPDATE trigger exists on the Sales table and an UPDATE statement is executed adainst the Salestable, the UPDATE statement will not change a row in the sales table. Instead, the UPDATE statement causes the INSTEAD OF UPDATE trigger to be executed.

AFTER triggers
AFTER triggers execute following the SQL action, such as an insert, update, or delete. This is the traditional trigger which existed in SQL SERVER. INSTEAD OF triggers are executed automatically before the Primary Key and the Foreign Key constraintsaer checked, whereas the traditional AFTER triggers is executed after these constraints are checked. Unlike AFTER triggers, INSTEAD OF triggers can be created on views.

15)
If we have multiple AFTER Triggers, can we specify sequence ?

If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the stored procedure sp_settriggerorder.