Difference between Callback and Postback in asp.net?

               As with a postback, a callback does not refresh the currently viewed page.If there were two drop down boxes, the second dependant on the value of the first, when a user selects a value of a the first, rather then refreshing the page, doing some server side calculations and returning a new whole page to the client, a callback can enable you to only go fetch the required data. View State is not updated with a callback.

Tips for Writing a Good Store Procedure Part II

WHERE clauses :

• In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance. =, >, <, >=, <=, <>, !=, !>, !<.
•  Avoid necessary where clause and mulitiple "AND" and "OR" clause. For example see below example :
  
  SELECT name FROM employee WHERE LOWER(name) = 'ab'
  SELECT name FROM employee WHERE name = 'AB' OR name = 'ab'

  Avoid DISTINCT and ORDER BY :

  • If no need then avoid using distinct and orderby clause in your query cause it causes extra load on database engine.

  CAST and CONVERT :

  • Try to use CAST instead of CONVERT. CAST is ANSI-92 standard but CONVERT works in MS SQL server only. Also, Convert may be deprecated in future MS SQL releases.
  • It is better to use CONVERT only when you need to format the DATETIME datatype with the style option. CAST can not do this.

  
  

  Avoid using cursors :

  
  

  • Try to use temporary table or table variables with identity column and then iterate all the tables using WHILE loop and a looping counter, which will map with the identity column.

Tips for Writing a Good Store Procedure Part I

Dynamic Queries :

• Do not use dynamic queries as much as possible because it compile the query everytime.
• If you use the simple sql query then it compiles only once bt dynamic query recompiles everytime.

Procedure Name Fully Qualified :

• Use the fully qualified name when you are calling stored procedures. This would be the format database_name.schema_name.table_name. For example, use EXEC master.dbo.Your_Proc_name instead of EXEC Your_Proc_name. 

• Also try to use the schema name when you create a procedure. Like: CREATE PROCEDURE dbo.Your_Proc_name instead of CREATE PROCEDURE Your_Proc_name 

sp_ Prefix :

• Dont use the "sp_" prefix in store procedure or anywhere cause it is a predefined format reseverd for system procedures.
• If by mistak name of store procedure will be same then it finds this procedure in system database and also in your database.So your database procedure never will be executed.

sp_executeSQL :

• It avoids recompilation of store procedure. 
• If you use dynamic query then it is very useful to you cause it stores procedures with the variable in cache.
• When you pass parameter you will get result without recompilation of store procedure.

KEEPFIXED PLAN options :

• It also avoids recompilation of store procedure.
• Use  this keyword "KEEPFIXED PLAN" for getting result from temporary tables.
• If your query contains this hint then your store procedure will not be recompiled.

SET NOCOUNT OFF :

• This returns the message that shows number of rows affected by SQL statement. This can cause extra network traffic and can have some serious impact on performance when the procedure is called frequently. 

SELECT vs SET :

• A single SELECT statement can assign values to different variables and is much faster than multiple SET statements assigning values to multiple different variables.
  
  SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1
  instead of
  
  
  SET @Var1 = @Var1 + 1

  SET @Var2 = @Var2 - 1
   

Paging in SqlServer

Paging in SQL Server with ROW_NUMBER()


SELECT  *
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY iCagegoryID DESC)
             AS Row,sCategoryName, sCategoryType FROM CategoryMaster)
            AS LogWithRowNumbers
WHERE  Row >= 11 AND Row <= 20

See the example above. It tells that on which column you want to do paging, it stores column's index.
In above example "iCategoryID" column's Index store in variavle "Row" and everytime just user has to pass extra 2 variables minvalue and maxvalue through which we can get those much of records displayed in 1 page.

CharIndex in SQL Server

CharIndex in sql server gives you position of particular character.
See Example below :

Declare @test VARCHAR(3)
SET @test = 'abc'
SELECT CharIndex('b',@test) as 'Position'