ASP.Net : What is webgardening

Web gardening enables multiple worker processes to run at the same time. However, you should note that all processes will have their own copy of application state, in-process session state, ASP.NET cache, static data, and all that is needed to run applications.

When the Web garden mode is enabled, the ASP.NET ISAPI launches as many worker processes as there are CPUs, each a full clone of the next (and each affinitized with the corresponding CPU). To balance the workload, incoming requests are partitioned among running processes in a round-robin manner. Worker processes get recycled as in the single processor case. Note that ASP.NET inherits any CPU usage restriction from the operating system and doesn’t include any custom semantics for doing this.

SQL Query : Getting Nth highest salary from the employee

In any interview these days , everybody would be facing this question some time in their career. Below is the easy way to get the Nth highest salary among the employee salaries

SELECT TOP 1 SALARY FROM (SELECT DISTINCT TOP N SALARY
FROM EMPLOYEE ORDER BY SALARY DESC)SAL
ORDER BY SALARY

Place the value in place of N.

SQL Server : INTERSECT

-Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand

-It almost works like INNER JOIN

-Difference is INTERSECT treats two NULL values are same whereas INNER JOIN treats two NULL values are different

SQL Server : Number of nesting levels in stored procedures

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. The nesting level increases by one when the called stored procedure or managed code reference begins execution and decreases by one when the called stored procedure or managed code reference completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling chain to fail. The current nesting level for the stored procedures in execution is stored in the Any reference to managed code from a Transact-SQL stored procedure counts as one level against the 32-levelnesting limit. Methods invoked from within managed code do not count against this limit.

When a stored procedure executes managed code by referencing a CLR routine, type, or aggregate, this reference also counts as one level of nesting. Methods invoked from within managed code do not count against this limit. The current nesting level is returned by the @@NESTLEVEL function. When a CLR stored procedure performs data access operations through the Microsoft SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL and this level is reflected in the @@NESTLEVEL function.

An error in a nested stored procedure is not necessarily fatal to the calling stored procedure. When invoking stored procedures within stored procedures, use the Transact-SQL RETURN statement to return a return code and check the return code from the calling stored procedure. In this way, you can specify the behavior of your stored procedures when errors occur. For more information about using return codes, see Returning Data Using a Return Code.

Stored procedures can even do a nested call to themselves, a technique known as recursion.Although the nesting limit is 32 levels, SQL Server has no limit on the number of stored procedures that can be invoked from a given stored procedure, provided that the subordinate stored procedures do not invoke other
subordinate stored procedures and the maximum nesting level is never exceeded.