Skip to content
Advertisements

SQL interview questions part 1

What is SQL function?

SQL functions are methods which accept parameters and perform the action, such as a complex calculation and return the result of that action as a value.the return value can either be a single scalar value ,result set. We can’t use a function to Insert, Update, Delete records in the database table(s).

Available types of functions in SQL.

  1. Scalar Functions
  2. Table-Valued Functions
  3. System Function

What is the stored procedure?

Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved which executes whenever it is called.

Difference between SQL function and Stored procedure?

  1. A function must return a value but in Stored Procedure, it is optional( Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters.
  3. Functions can be called from Procedure whereas Procedures cannot be called from Function.
  4.  The procedure allows SELECT as well as Insert, Update, Delete statement where Function allows only SELECT statement.
  5. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  6. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  7. Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
  8. We can handle Exception by try-catch block in a Procedure but not in functions.
  9. We can perform Transaction Management in Procedure whereas we can’t go in Function.

Can we call the Stored procedure inside SQL function?

No, You cannot execute a stored procedure inside a function, because a function is not allowed to modify database state, and stored procedures are allowed to modify database state.

Can we call a function inside the Stored procedure?

Yes, We can call functions inside the stored procedures.

Is Row_Number generate duplicate Id?

No, ROW_NUMBER  Returns a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrarily assigned.

what is Scope identity in SQL?

Scope_Identity returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).

What is CTE in SQL?

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

 

 

 

Advertisements

Deependra Kushwah View All

Deependra is a Senior Developer with Microsoft technologies, currently working with Opteamix India business private solution. In My Free time, I write blogs and make technical youtube videos. Having the good understanding of Service-oriented architect, Designing microservices using domain driven design.

What you think

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: