Hi, I am Shashangka Shekhar, Working with Microsoft Technologies. This is my personal blog where I share some of my experiences.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?

Yes,Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem.

A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is SQL server agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA).

It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

What are types of sub-queries?

  1. Single-row subquery, where the subquery returns only one row.
  2. Multiple-row subquery, where the subquery returns multiple rows and
  3. Multiple column subquery, where the subquery returns multiple columns.

What is sub-query? Explain properties of sub-query.

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword. Read More

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Specifies a search condition for a group or an aggregate.

  1. HAVING can be used only with the SELECT statement.
  2. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
  3. Having Clause is basically used only with the GROUP BY function in a query.
  4. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
  5. HAVING criteria is applied after the the grouping of rows has occurred.

What types of Joins are possible with Sql Server?

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Difference between Function and Stored Procedure?

User-Defined Functions(UDF) can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section

where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

What is a NOLOCK?

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. Read More