SQL Interview Question with Answers


 SQL Interview Question with Answers


31. What are the three different types of Control Flow statements?

  • WHILE
  • IF-ELSE
  • CASE

32. What is Table Variable? Explain its advantages and disadvantages.?

  • If we want to store tabular data in the form of rows and columns into a variable then we use a table variable.
  • It is able to store and display 2D data (rows and columns).
  • We cannot perform DDL (CREATE, ALTER, DROP)

Advantages:

  • Table variables can be faster than permanent tables.
  • Table variables need less locking and logging resources.

Disadvantages:

  • Scope of Table variables is batch bound.
  • Table variables cannot have constraints.
  • Table variables cannot have indexes.
  • Table variables do not generate statistics.
  • Cannot ALTER once declared (Again, no DDL statements).

33. What are the differences between Temporary Table and Table Variable?

Temporary Table:

  • It can perform both DML and DDL Statement. Session bound Scope
  • Syntax CREATE TABLE #temp
  • Have indexes

Table Variable:

  • Can perform only DML, but not DDL Batch bound scope
  • DECLARE @var TABLE(...)
  • Cannot have indexes

34. What is Stored Procedure (SP)?

  • It is one of the permanent DB objects that is precompiled set of TSQL statements that can accept and return multiple variables.
  • It is used to implement the complex business process/logic. In other words, it encapsulates your entire business process.
  • Compiler breaks query into Tokens. And passed on to query optimizer. Where execution plan is generated the very 1st time when we execute a stored procedure after creating/altering it and same execution plan is utilized for subsequent executions.
  • Database engine runs the machine language query and execute the code in 0's and 1's.
  • When a SP is created all Tsql statements that are the part of SP are pre-compiled and execution plan is stored in DB which is referred for following executions.
  • Explicit DDL requires recompilation of SP's.

35. What are the four types of SP?

  • System Stored Procedures (SP_****): built-in stored procedures that were created by Microsoft.
  • User Defined Stored Procedures: stored procedures that are created by users. Common naming convention (usp_****)
  • CLR (Common Language Runtime): stored procedures that are implemented as public static methods on a class in a Microsoft .NET Framework assembly.
  • Extended Stored Procedures (XP_****): stored procedures that can be used in other platforms such as Java or C++.

36. Explain the Types of SP..? 

  • SP with no parameters:
  • SP with a single input parameter:
  • SP with multiple parameters:
  • SP with RETURN statement (the return value is always single and integer value)
  • SP with output parameters: Extracting data from a stored procedure based on an input parameter and outputting them using output variables.

37. What are the characteristics of SP? 

  • SP can have any kind of DML and DDL statements.
  • SP can have error handling (TRY ... CATCH).
  • SP can use all types of table.
  • SP can take any input except a table variable.
  • SP can set default inputs.
  • SP can use DSQL.
  • SP can have nested SPs.
  • SP cannot output 2D data (cannot return and output table variables).
  • SP cannot be called from a SELECT statement. It can be executed using only a EXEC/EXECUTE statement
  • SP can output multiple integer values using OUT parameters, but can return only one scalar INT value. 

38. What are the advantages of SP?

  • Precompiled code hence faster.
  • They allow modular programming, which means it allows you to break down a big chunk of code into smaller pieces of codes. This way the code will be more readable and more easier to manage. Reusability. 
  • Can enhance security of your application. Users can be granted permission to execute SP without having to have direct permissions on the objects referenced in the procedure.
  • Can reduce network traffic. An operation of hundreds of lines of code can be performed through single statement that executes the code in procedure rather than by sending hundreds of lines of code over the network.
  • SPs are pre-compiled, which means it has to have an Execution Plan so every time it gets executed after creating a new Execution Plan, it will save up to 70% of execution time. Without it, the SPs are just like any regular TSQL statements.

39. What is User Defined Functions (UDF)?

  • The return value can either be single scalar value or result set-2D data. 
  • UDFs are also precompiled and their execution plan is saved.
  • UDFs are a database object and a precompiled set of TSQL statements that can accept parameters, perform complex business calculation, and return of the action as a value.
  • PASSING INPUT PARAMETER(S) IS/ARE OPTIONAL, BUT MUST HAVE A RETURN STATEMENT.

40. What is the difference between Stored Procedure and UDF? 

Stored Procedure: 

may or may not return any value. When it does, it must be scalar INT. 
Can create temporary tables. Can have robust error handling in SP (TRY/CATCH, transactions). Can include any DDL and DML statements.

UDF: 

must return something, which can be either scalar/table valued. Cannot access to temporary tables. 
No robust error handling available in UDF like TRY/ CATCH and transactions. Cannot have any DDL and can do DML only with table variables. 

No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services