Top 15+ SQL Query Interview Questions and Answers (2024)-Here you are get above idea what main question interviewer ask in term of SQL Database. So lets start.
What are differences between Stored Procedures and Functions?
- 1. Stored Procedure support deferred name resolution whereas functions do not support deffered name resolution.
2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.
3. UDF‘s cannot return Image, Text where as a StoredProcedure can return any datatype.
4. In general, User Defined Functions are used for computations whereas Stored Procedures are used for performing business logic.
5. UDF should return a value whereas Stored Procedure need not.
6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters whereas aStored Procedure can have upto 21000 input parameters.
7. Temporary Tables cannot be used in a UDF where as a StoredProcedure can use Temporary Tables.
8. UDF cannot Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL
9. User Defined Function does not support error handling whereas Stored Procedure supports error handling. RAISEERROR or @@ERROR are not allowed in UDFs.
Top 15+ SQL Query Interview Questions and Answers (2024)
What is difference between Inline Table Valued Function and Multi Statement Table Valued Function?[Most Imp Question]
1. In an Inline Table Valued function, the RETURNS clause cannot contain the structure of the table, the function returns. Whereas, with the multi-statement table valued function, we specify the structure of the table that gets returned.
2. Inline Table Valued function cannot have BEGIN and END block, whereas the multi-statement function can have.
3. Inline Table valued functions are better for performance, than multi-statement table valued functions. If the given task, can be achieved using an inline table valued function, always prefer to use them, over multi-statement table valued functions.
4. It’s possible to update the underlying table, using an inline table valued function, but not possible using multi-statement table valued function.
Top 15+ SQL Query Interview Questions and Answers (2024)
What are temporary tables ? What are different types.
Temporary tables are very similar to the permanent tables. Permanent tables get created in the database you specify, and remain in the database permanently, until you delete (drop) them. On the other hand, temporary tables get created in the TempDB and are automatically deleted, when they are no longer used.
In SQL Server, there are 2 types of Temporary tables – Local Temporary tables and Global Temporary tables.
Subcribe My Channel – Subcribe Now
Top 15+ SQL Query Interview Questions and Answers (2024)
Write SQL statement to call a Stored Procedures with Output parameter.
Declare @TotalEmployees int
Execute spGetTotalCountOfEmployees @TotalEmployees Output
Select @TotalEmployees
What is difference between Output Parameter and Return values in Stored Procedures?
It is not possible, to return more than one value using return values, whereas output parameters, can return any datatype and an SP can have more than one output parameters.
We always prefer, using output parameters, over RETURN values.
In general, RETURN values are used to indicate success or failure of stored procedure, especially when we are dealing with nested stored procedures. Return a value of 0, indicates success, and any nonzero value indicates failure.
What is difference between Cast and Convert functions?
CONVERT() function has an optional style parameter, whereas CAST() function lacks this capability.
Convert provides more flexibility than Cast. For example, it’s possible to control how you want DateTime datatypes to be converted using styles with convert function.
Cast is based on ANSI standard and Convert is specific to SQL Server. So, if portability is a concern and if you want to use the script with other database applications, use Cast().
What are deterministic and non-deterministic functions in SQL? Please list down some non deterministic functions?
Examples: Sum(), AVG(), Square(), Power() and Count()
All aggregate functions are deterministic functions.
Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
Examples: GetDate() and CURRENT_TIMESTAMP, RAND()
What id RAND() function? What if you pass it a parameter e.g. RAND(1) ?
What are functions in SQL? What are different types of functions? Explain
There are 3 different types of functions are available in SQL :
Scalar Function :
Scalar functions may or may not have parameters, but always return a single (scalar) value. The returned value can be of any data type, except text, ntext, image, cursor, and timestamp.
Inline Table Valued Function :
An Inline Table Valued function, return a table.
The function body is not enclosed between BEGIN and END block. Inline table valued function body, cannot have BEGIN and END block.
The structure of the table that gets returned, is determined by the SELECT statement with in the function.
Inline Table Valued functions can be used to achieve the functionality of parameterized views.
The table returned by the table valued function, can also be used in joins with other tables.
Multi Statement Table Valued Function :
Multi statement table valued functions are very similar to Inline Table valued functions, with a few differences.
Multi-statement table valued function, we specify the structure of the table that gets returned
Inline Table Valued function cannot have BEGIN and END block, where as the multi-statement function can have.
Write down syntax for functions ? (Note : Interviewer can ask you to write syntax for any of these UDF’s)
RETURNS Return_Datatype
AS
BEGIN
Function Body
Return Return_Datatype
END
Inline Table Valued Function :
CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType…, @ParamN DataType)
RETURNS TABLE
AS
RETURN (Select_Statement)
Multi Statement Table Valued Function :
CREATE FUNCTION Function_Name(@Param1 DataType, @Param2 DataType…, @ParamN DataType)
RETURNS @Table TABLE (Specify columns name list)
AS
BEGIN
–Insert into @Table
RETURN
END