Response.Redirect vs Server.Transfer

Response.Redirect vs Server.Transfer 

1.     ‘Response. Redirect’ sends message to the browser saying it to move to some different page, while ‘Server. Transfer’ does not send any message to the browser but rather it redirects the user directly from the server itself. So in case of ‘Server. Transfer’ there is no round trip while Response. Redirect has a round trip and hence puts extra load on server. 

2.     Using ‘Server. Transfer’ we cannot redirect to external websites or website pages. E.g. if your website is www.webcodeexpert.com then you cannot use ‘Server. Transfer’ to move to www.google.com but yes, you can move to internal pages  www.webcodeexpert.com/asp.net, i.e. within the websites. Cross server redirection is possible only by using ‘Response.Redirect’ i.e. it allows redirection to internal as well as external websites and website pages.

3.     With ‘Response. Redirect’ we can redirect the user to the both type of pages .html or .aspx e.g. Response. Redirect (“OtherPage.html”) OR Response. Redirect (“OtherPage.aspx”) But in case of ‘Server. Transfer’ we can redirect user to .asp or .aspx pages only e.g. Server. Transfer (“OtherPage.asp”) OR Server. Transfer (“OtherPage.aspx”) not to Server. Transfer (“OtherPage.html”).

4.     In ‘Server. Transfer’ URL doesn’t change but in case of ‘Response. Redirect’ URL changes.

5.     When we want to allow our website’s URL can be copied then ‘Response. Redirect’ is better but for security reasons ‘Server. Transfer’ is better because URL cannot be copied.

6.     ‘Response. Redirect’ has a round trip but ‘Server.Transfer’ has no round trip. (Roundtrip is the combination of a request being sent to the server and response being sent back to browser.)

7.     ‘Server. Transfer’ is a server process whereas ‘Response. Redirect’ is a client process.

8.     ‘Server.Transfer’ preserves Query String and Form Variables (optionally). ‘Response. Redirect’ doesn’t preserve Query String and Form Variables from the original request.

9.     ‘Server. Transfer’ is faster since there is one less round trip as compared to ‘Response. Redirect’. Transferring to another page using ’Server. Transfer’ conserves server resources. Instead of telling the browser to redirect, it simply changes the focus on the Web server and transfers the request. This means you don't get quite as many HTTP requests coming through, which therefore eases the pressure on your Web server and makes your applications run faster.

10. ‘Server. Transfer’ allow us to directly access the values, controls and properties of the previous page which we can’t do with ‘Response. Redirect’. The ’Server. Transfer’ method also has a second parameter—"preserveForm". If you set this to True, using a statement such as Server. Transfer ("OtherPage.aspx", True), the existing query string and any form variables will still be available to the page you are transferring to. For example, if your CurrentPage.aspx has a TextBox control called TextBox1 and you transferred to OtherPage.aspx with the preserveForm parameter set to True, you'd be able to retrieve the value of the original page’s TextBox control by referencing Request. Form ("TextBox1").

11. ‘Response. Redirect’ involves a roundtrip to the server whereas ‘Server. Transfer’ conserves server resources by avoiding the roundtrip. It just changes the focus of the web server to a different page and transfers the page processing to a different page. Roundtrip means in case of ‘Response. Redirect’ it first sends the request for the new page to the browser then browser sends the request for the new page to the web server only then a your page changes But in case of ‘Server. Transfer’ it directly communicate with the server to change the page hence it saves a roundtrip in the whole process.

Difference between Stored procedure and Function

Stored Procedure:

A stored procedure is a pre-compiled group of Transact-SQL statements .We can say a stored procedure is a prepared SQL code that we save so that we can reuse the code over and over again.  If a repetitive T-SQL task has to be executed within an application, then the best way for it is to create stored procedure.

               It is always recommended to create Stored Procedure instead of writing Inline queries so that we can just call the Stored Procedures whenever required instead of writing Inline queries again and again each time.

                You can also pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed to it.

Function:

 Function in Sql Server is a Transact-SQL or common language runtime (CLR) routine that takes parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.

1.       Function can return only 1 value whereas Stored Procedure can return many values(maximum 1024)

2.       Functions can have only input parameters for it whereas Stored Procedures can have input/output parameters.

3.       Function takes one input parameter which is mandatory but Stored Procedure may take Zero to n input parameters.

4.       Functions can be used in a select statement where as Stored Procedures cannot.

5.       Functions can be called from Stored Procedure whereas Stored Procedures cannot be called from Function.

6.       Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.

7.       Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

8.       Stored Procedure can be used to read and modify data but function can only read data.

9.       Stored Procedure allows SELECT as well as DML (Data Manipulation Language) statements like INSERT/UPDATE/DELETE in it whereas Function allows only SELECT statement in it.

10.   Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

11.   Stored Procedures cannot be used as an inline with a select statement while Functions can. 

12.   Stored procedures are compiled for first time and compiled format is saved and executes compiled code whenever it is called. But Function is compiled and executed every time it is called. 

13.   Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

14.   Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

15.   Stored Procedure allows Transaction Management whereas Function doesn’t.

16.   Stored procedures can be used to change server configuration settings (in terms of security-e.g. setting granular permissions of user rights) whereas function can't be used for this

17.   The Stored Procedures can perform certain tasks in the database by using insert, delete, update and create commands but in Function you can’t perform use these commands. 

18.   Normally the Stored procedures are used to process certain task but the Functions are used to compute the values i.e. we can pass some value as input and then it perform some task on the passed value and return output. 

19.   Stored Procedures can be executed using Execute or Exec command where as Functions can run as an executable file. 

20.   Functions can be used as user defined data types in create table but procedures cannot.

Stored Procedure Optimization Tips – Best Practices

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.

• Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.

CREATE PROC dbo.ProcNameAS
SET NOCOUNT ON;--Procedure code hereSELECT column1 FROM dbo.TblTable1-- Reset SET NOCOUNT to OFFSET NOCOUNT OFF;GO

• Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like

SELECT * FROM dbo.MyTable -- Preferred method
-- Instead ofSELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:EXEC dbo.MyProc -- Preferred method
--Instead ofEXEC MyProc -- Avoid this method

• Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

• Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:

IF EXISTS (SELECT 1 FROM sysobjectsWHERE name = 'MyTable' AND type = 'U')

• Use the sp_executesql stored procedure instead of the EXECUTE statement.
  The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)DECLARE @Age INT
SET @Age = 25SET @Query ='SELECT * FROM dbo.tblPerson WHERE Age = ' +CONVERT(VARCHAR(3),@Age)EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)SET @Query =N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'EXECUTE sp_executesql @Query, N'@Age int', @Age =25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

• Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
• Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
• Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY--Your t-sql code goes hereEND TRYBEGIN CATCH--Your error handling code goes hereEND CATCH

Stored Procedures Advantages and Best Advantage

SP are compiled and their execution plan is cached and used again to when the same SP is executed again

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines

Sql Interview Question and Answer.

What is Connection Pooling and why it is Used?

To minimize the cost of opening and closing connections, ADO.NET uses an optimization technique called connection pooling.

The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

What are the Properties and Different Types of Sub-Queries?

Properties of a Sub-Query

• A sub-query must be enclosed in the parenthesis.
• A sub-query must be put on the right hand of the comparison operator, and
• A sub-query cannot contain an ORDER BY clause, however sub-query can use ORDER BY when used with TOP clause. Read Comment by David Bridge
• A query can contain more than one sub-query.

Types of Sub-query

• Single-row sub-query, where the sub-query returns only one row.
• Multiple-row sub-query, where the sub-query returns multiple rows, and
• Multiple column sub-query, where the sub-query returns multiple columns

What is an SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing very slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What are the Authentication Modes in SQL Server? How can it be Changed?

There are two authentication modes in SQL Server.

• Windows Mode
• Mixed Mode – SQL and Windows

To change authentication mode in SQL Server, go to Start -> Programs- > Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server; then from the Tools menu, select SQL Server Configuration Properties and choose the Security page.

Garbage Collection

Concept of Garbage Collection

When Microsoft planned to go for a new generation platform called .NET with the new generation language called C#, their first intention is to make a language which is developer friendly to learn and use it with having rich set of APIs to support end users as well. So they put a great thought in Garbage Collection and come out with this model of automatic garbage collection in .NET.

They implemented garbage collector as a separate thread. This thread will be running always at the back end. Some of us may think, running a separate thread will make extra overhead. Yes. It is right. That is why the garbage collector thread is given the lowest priority. But when system finds there is no space in the managed heap (managed heap is nothing but a bunch of memory allocated for the program at run time), then garbage collector thread will be given REALTIME priority (REALTIME priority is the highest priority in Windows) and collect all the un wanted objects.


What is Garbage Collection...

The .NET Framework's garbage collector manages the allocation and release of memory for your application. Each time you create a new object, the common language runtime allocates memory for the object from the managed heap. As long as address space is available in the managed heap, the runtime continues to allocate space for new objects. However, memory is not infinite. Eventually the garbage collector must perform a collection in order to free some memory. The garbage collector's optimizing engine determines the best time to perform a collection, based upon the allocations being made. When the garbage collector performs a collection, it checks for objects in the managed heap that are no longer being used by the application and performs the necessary operations to reclaim their memory.

class First
{
~First()
{
System.Diagnostics.Trace.WriteLine("First's destructor is called.");
}
}

class Second : First
{
~Second()
{
System.Diagnostics.Trace.WriteLine("Second's destructor is called.");
}
}

class Third : Second
{
~Third()
{
System.Diagnostics.Trace.WriteLine("Third's destructor is called.");
}
}

class TestDestructors
{
static void Main()
{
Third t = new Third();
}

}
/* Output (to VS Output Window):
Third's destructor is called.
Second's destructor is called.
First's destructor is called.
*/

class mind
{
~mind()
{

}

public static void Main()
{

}

}
//The destructor implicitly calls Finalize on the base class of the object. Therefore, the previous destructor code is implicitly translated to the following code:

Copy
protected override void Finalize()
{
try
{
// Cleanup statements...
}
finally
{
base.Finalize();
}
}