Some important sql server interview questions for developers to help them secure their dream job. A new batch of interview questions to help the developers to get through their next interview.
What is the 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.
- Scalar Functions
- Table-Valued Functions
- System Function
What is the stored procedure?
Stored Procedures are pre-compiled objects which are compiled for the first time and it saves the compiled format which executes whenever we call it .
Difference between SQL function and Stored procedure?
- A function must return a value but in Stored Procedure, it is optional( Procedure can return zero or n values).
- Functions can have only input parameters for it whereas Procedures can have input/output parameters.
- We can call functions from procedures But, we cannot call the procedures from functions.
- The procedure allows SELECT as well as Insert, Update, Delete statement where Function allows only SELECT statement.
- Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
- Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
- Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
- We can handle Exception by try-catch block in a Procedure but not in functions.
- 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.
Can 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.
Were these sql server interview questions for developers helpful to you? You can provide your comments and concerns in the comment section below.