Points to remember before Query optimization in SqlServer

• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-clustered index
• Non-clustered index should be created on columns of table based on query which is running
• Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
• Do not to use Views or replace views with original source table
• Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
• Remove any adhoc queries and use Stored Procedure instead
• Check if there is atleast 30% HHD is empty – it improves the performance a bit
• If possible move the logic of UDF to SP as well
• Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned. This will help to narrow the return rows else it will perform a whole table scan and waste the Sql server resources with increasing the network traffic. While scanning the whole it will lock the Table which may prevent other users to access the table.
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
• It is seen many times developers use codes like   SELECT * FROM OrderTable WHERE LOWER(UserName)='telsa'
  Instead of writing it like the below
  SELECT * FROM OrderTable WHERE UserName='telsa'
  Infact both the queries does the same work but the 2nd one is better and retrieves rows more speedly than the first query. Because Sql Server is not case sensitive
   
• While running a query, the operators used with the WHERE clause directly affect the performance. The operators shown below are in their decreasing order of their performance.
• =
• >,>=,<, <=
• LIKE
• <>  
• When we are writing queries containing NOT IN, then this is going to offer poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using EXISTS or NOT EXISTS.When there is a choice to use IN or EXIST, we should go with EXIST clause for better performance. 
• It is always best practice to use the Index seek while the columns are covered by an index, this will force the Query Optimizer to use the index while using IN or OR clauses as a part of our WHERE clause. 
• SELECT * FROM OrderTable WHERE Status = 1 AND OrderID IN (406,530,956) Takes more time than 
  SELECT * FROM OrderTable (INDEX=IX_OrderID) WHERE Status = 1 AND OrderID IN (406,530,956)
  
• While we use IN, in the sql query it better to use one or more leading characters in the clause instead of using the wildcard character at the starting.
  
  SELECT * FROM CustomerTable WHERE CustomerName LIKE 'm%'
  SELECT * FROM CustomerTable WHERE CustomerName LIKE '%m'
  In the first query the Query optimizer is having the ability to use an index to perform the query and there by reducing the load on sql server. But in the second query, no suitable index can be created while running the query.
• While there is case to use IN or BETWEEN clause in the query, it is always advisable to use BETWEEN for better result.
• Always avoid the use of SUBSTRING function in the query.
• The queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written. So certain things should be taken care of like
• Provide the least likely true expressions first in the AND. By doing this if the AND expression is false at the initial stage the clause will end immediately. So it will save execution time
• If all the parts of the AND expression are equally like being false then better to put the Complex expression first. So if the complex works are false then less works to be done.
• While the select statement contains a HAVING clause, its better to make the WHERE clause to do most of the works (removing the undesired rows) for the Query instead of letting the HAVING clause to do the works. 
• e.g. in a SELECT statement with GROUP BY and HAVING clause, things happens like first WHERE clause will select appropriate rows then GROUP BY divide them to group of rows and finally the HAVING clause have less works to perform, which will boost the performance.   
   

Type of temporary tables in sqlserver

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do.These tables are created inside tempdb database.

Different Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

• Local Temp Table
• Global Temp Table

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Creating Temporary Table in SQL Server

As I have already discussed, there are two types of temporary tables available. Here I am going to describe each of them.

Local Temporary Table

The syntax given below is used to create a local Temp table in SQL Server:

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

insert into #LocalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user.

Global Temporary Table

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table: 

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:

insert into ##NewGlobalTempTable values ( 1, 'test','India');

Now select records from that table:

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

Points to Remember Before Using Temporary Tables

• Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an additional overhead and can causes performance issues.
• Number of rows and columns need to be as minimum as needed.
• Tables need to be deleted when they are done with their work.

Alternative Approach: Table Variable

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

The below scripts are used to insert and read the records for Tablevariables:

insert into @TempTableVariable values ( 1, 'Abhijit','India');

Now select records from that tablevariable:

select * from @TempTableVariable

When to Use Table Variable Over Temp Table

Tablevariable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.

When following error comes during browsing of any website directly from iis, it means our .net framework is not registered with our installed iis. I have faced this issue while installing "Umbraco" cms.

Error Summary
HTTP Error 500.21 - Internal Server Error
Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list

Solution :Its solution is very simple. Just register your .net framework with IIS.Run Following command on your command prompt.

windows/Microsoft.NET/Framework/v4.0.30319/aspnet_regiis.exe -i

It will install framework 4.0 (choosen one) on your IIS and your targetted websites will work fine.
Thats all :)
Happy Coding !!!
 

We can easily calculate the distance between two cities if we know the longitude and latitude of those citites. This can achieved by C# code as well as through sql server's function. Following functiona can be used to calculate the distance between two cities.

CREATEFUNCTION CalculateDistanceInMiles(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
RETURNS float
AS 
BEGIN
-- CONSTANTS
DECLARE @EarthRadiusInMiles float;
SET @EarthRadiusInMiles = 3963.1
DECLARE @PI  float;
SET @PI = PI();
-- RADIANS conversion
DECLARE @lat1Radians float;
DECLARE @long1Radians float;
DECLARE @lat2Radians float;
DECLARE @long2Radians float;
SET @lat1Radians = @Latitude1 * @PI / 180;
SET @long1Radians = @Longitude1 * @PI / 180;
SET @lat2Radians = @Latitude2 * @PI / 180;
SET @long2Radians = @Longitude2 * @PI / 180;
RETURN Acos(
Cos(@lat1Radians) * Cos(@long1Radians) * Cos(@lat2Radians) * Cos(@long2Radians) +
Cos(@lat1Radians) * Sin(@long1Radians) * Cos(@lat2Radians) * Sin(@long2Radians) +
Sin(@lat1Radians) * Sin(@lat2Radians)
) * @EarthRadiusInMiles;
END

Export component art grid to excel

Following code can be used to export component art grid or asp.net grid to excel

 protected void Page_Load(object sender, EventArgs e)
        {
 ExportToExcel(DataTable, Response, "Excel Sheet-" + DateTime.Today.ToString("MM-dd-yyyy"));
 }

 public void ExportToExcel(DataTable dataTable, HttpResponse Response, string FileName)
        {
            Response.Clear();
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("content-disposition", "attachment; filename=" + FileName + ".xlsx");
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
            GridView gv = new GridView();
            gv.DataSource = dataTable;
            gv.DataBind();
            gv.RenderControl(hw);
            Response.Write(sw.ToString());
            Response.End();
        }

Export component art grid or normal grid to pdf

Code to export component art grid to pdf
 private void Page_Load(System.Object sender, System.EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                Grid1.DataSource = buildGrid();
                Grid1.DataBind();
            }
        }

        public System.Data.DataSet buildGrid()
        {
            string conStr = null;
            conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
            conStr += Server.MapPath("~/app_data/demo.mdb");
            System.Data.OleDb.OleDbConnection dbCon = new System.Data.OleDb.OleDbConnection(conStr);
            dbCon.Open();

            string sql = null;
            sql = "SELECT * FROM Posts ORDER BY LastPostDate DESC";
            System.Data.OleDb.OleDbDataAdapter dbAdapter = new System.Data.OleDb.OleDbDataAdapter(sql, dbCon);
            DataSet ds = new DataSet();
            dbAdapter.Fill(ds);

            return ds;
        }

        protected void  btnExport_Click(object sender, System.EventArgs e)
        {
            ExportToExcel(buildGrid(), 0, Response, "MySheet-" + DateTime.Today.ToString("MM-dd-yyyy"));
        }

        public void ExportToExcel(DataSet dSet, int TableIndex, HttpResponse Response, string FileName)
        {
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
            GridView gv = new GridView();
            gv.DataSource = dSet.Tables[TableIndex];
            gv.HeaderStyle.Font.Size = new FontUnit(4);
            gv.RowStyle.Font.Size = new FontUnit(4);
            gv.DataBind();
            for (int x = 0; x < gv.Columns.Count; x++)
            {
                gv.Columns[x].ItemStyle.Width = Unit.Pixel(100);
            }
            gv.RenderControl(hw);

            Document Doc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);

            PdfWriter.GetInstance(Doc, new FileStream(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\myPDF.pdf", FileMode.Create));
            HTMLWorker htmlparser = new HTMLWorker(Doc);
            Doc.Open();

            Chunk c = new Chunk("PDF EXPORT OF POSTS \n", FontFactory.GetFont("Verdana", 15));
            Paragraph p = new Paragraph();
            p.Alignment = Element.ALIGN_CENTER;
            p.Add(c);
            Chunk chunk1 = new Chunk("By ComponentArt - www.componentart.com\n\n\n", FontFactory.GetFont("Verdana", 8));
            Paragraph p1 = new Paragraph();
            p1.Alignment = Element.ALIGN_RIGHT;
            p1.Add(chunk1);

            Doc.Add(p);
            Doc.Add(p1);

            System.Xml.XmlTextReader xmlReader = new System.Xml.XmlTextReader(new StringReader(sw.ToString()));
            htmlparser.Parse(new StringReader(sw.ToString()));
            Doc.Close();
            string Path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\myPDF.pdf";

            ShowPdf(Path);
        }
  private void ShowPdf(string strS)
        {
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/pdf";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + strS);
            Response.TransmitFile(strS);
            Response.End();
            //Response.WriteFile(strS);
            Response.Flush();

            Response.Clear();
        }