SQL - Clustered, Non-Clustered and Index on Multiple Columns

This blog will demonstrates how to improve the SQL query performance by adding index on SQL tables and will be discuss the scenarios where we should go with clustered, non-clustered or single index on multiple columns.

Clustered Index should be created on Column which uniquely identify to each row of tables and it defines the physical order of table records and then generally table’s primary key should be have clustered index. Now when you are going to create other index (Non-clustered) which generally help SQL engine to create execution plan and quickly filter the records.

If you creates non-clustered index in very smartly way, then it will be more efficient and more reusable.

SQL: INCLUDE Clause in Non-Clustered Index

Here is an example - an order table which contain company huge order records

Table: Order

Table Columns:

OrderID	PK	Primary Key
OrderDate
OrderNumber
State
City
Zip

On Order table, mostly we make search by OrderNumber and also search by location like State, City and Zip.

In consideration of uniqueness of record, you create clustered index on OrderID which is primary key of table and one more index (non-clustered) you can create on Order Number.

If you are looking search by location query, we are considering three columns (State, City and Zip)

Then mostly time we are searching order by State, City and zip combination

And sometime by state and city and sometime by zip code only.

If you are going to have single index which covers all columns (state, city and zip) then it will helps in all scenarios except search by zip; so in this case we need to have one more index on zip.

So after considering all above scenarios, table should have below indexes

Index Name	Type of Index	Columns
Inx_OrderOrderID	Clustered Index	OrderID
Inx_OrderOrderNumber	Non-Clustred Index	OrderNumber
Inx_OrderStateCityZip	Non-Clustred Index	State, City, ZIP
Inx_OrderZip	Non-Clustred Index	ZIP

System.Data.SqlClient.SqlException : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding

In this blog, we will discuss about the root cause of execution timeout reasons and how to fix the SQL Exception - Execution Timeout Expired

Recently I was dealing with huge data approximate 160M records and performing data analyzing task and on some point, I encountered SQL Exception.

System.Data.SqlClient.SqlException

  HResult=0x80131904

  Message=Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

  Source=.Net SqlClient Data Provider

  StackTrace:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Usually default command timeout is 30 seconds and if you don’t receive anything from database within 30 seconds, it will throw System.Data.SqlClient.SqlException : Execution Timeout Expired.

To resolve this problem, we need to increase the connection timeout for SQL command, if you feels SQL script /proc is already well tuned and tables are proper indexed.

Eg. :

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 60000;

You can simply set waiting time for SQLCommand

There are two timeout options available for application to access SQL Server Database :

1. Connection Timeout
2. Command Timeout

1. Connection Timeout: it is waiting time for application to establish connection with Database and if application is not able to establish connection with database, it throws SQL Exception

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified).

You can increase the waiting time for SQL connection timeout and you have to add connection timeout attribute in SQL connection string

string connectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Connection Timeout=30";

2. Command Timeout – it is waiting time for command to execute script, if within command timeout it does not complete execution, it throws SQL Exception

System.Data.SqlClient.SqlException : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding

And by default, command timeout value is 30 seconds and you can increase the waiting time for command timeout.

SqlCommand command = new SqlCommand(commandText, sqlConnection);

command.CommandTimeout = 6000; (values in millisecond)

Thanks for visiting!!

SQL database Project : A project which specifies SQL Server vNext CTP as the target platform cannot be published to SQL Server 20016

“A project which specifies SQL Server vNext CTP as the target platform cannot be published to SQL Server 20016.”

I recently encountered the above error message while publishing or generating SQL script from database project.

After getting this error, I investigated and found that database project’s Target Platform was SQL Server 2017 and Target SQL Database is SQL Server 2016 version and followed to below steps to resolve this incompatibility issue.

Step 1: Click on Advanced Button

Step 2: Advance Publish Settings à General Tab à Check “Allow incompatible platform” option under Advanced Deployment Options

Step 3: Click on Generate Script Button and your SQL script is successfully generated

Thanks for visiting !!

JQuery: Toggle show/Hide Div or any DOM Element

.toggle() method is being used to display or hide the DOM element like DIV, Button etc. and toggle method is being fired with different set of argument .

• .toggle()
• .toggle(display : Boolean)
• .toggle(duration,[])

 .toggle() method simply change the CSS display property of element, if it is displayed,it changes to ‘block’ value and if it hides, it changes to ‘none’ value to display property of element

.toggle() -  with no parameters  :

the .toggle () method simple toggles the visibility of elements . It means if element is displayed, it will be hidden and if it is hidden, it will be displayed.

$('.div').toggle();

.toggle(display : Boolean) – with display parameter :

If you pass true as display parameter, it show the element and if you pass false, it hide the element.

$('.div').toggle(true);   //// show matched elements

$('.div').toggle(false); ///// hide matched elements

.toggle(speed,easing,callback) with speed and easing parameters

This toggle () method allows to create the visual effect by using speed, easing parameter

 Parameters :

              speed – it specify the speed of the hide and show visual effect and available possible options  [ milliseconds, slow, fast]

              easing – it specify the different points of animation of show/hide effect and available options [ swing, linear]

            callback – it is a function pointer when toggle method is completed, the pointer function will be executed

        $('.div').toggle('slow', 'swing');

Thanks for visiting!!

Unity Container Register Singleton Class

In C#, there are several way to maintain the single instance of object by using singleton pattern, static classor static property with private constructor.

Recently I explored one more way to implement singleton pattern by using unity and in unity container, we can define the lifetime scope of object instance by passing Lifetime Manager option in RegisterType methods.

  UnityContainer container = new UnityContainer();

 container.RegisterType<IHTTPClientManager, HTTPClientManager>(new ContainerControlledLifetimeManager());

In above example, ContainerControlledLifetimeManager as Lifetimemanager option is being passed in registerType method for IHTTPClientManager type.

Unity will take care of instance creation of  IHTTPClientManager type and it will make sure that there will be a single instance of IHTTPClientManager type.it will not create any new instance after first resolve method call and it will share same instance.

other dependency injection and unity container  related posts : 

What is Dependency Injection

Unity Container Register Singleton Class

Dependency Injection with Entity Framework DbContext

Dependency Injection using Unity container

C# Unit Testing - Dependency Injection , Moq

ASP.NET MVC: HTML.ACTIONLINK VS URL.ACTION

@Html.ActionLink generates anchor  <a> tag or hyperlink on a view page in MVC whereas @Url.Action returns only a qualified URL. 

HTML.Actionlink() : 

HTMLHelper (@Html) object uses ActionLink() method to render the anchor HTML element with specified link text and action /controller name.

 ASP.NET MVC: anchor tag in MVC Razor View

Here is an example of HTML.ActionLink() method to generate a hyperlink with action/controller name:

@Html.ActionLink("Home", "Home", "Person") 

It Generates <a href="/Person/Home" >Home</a>

@Html.ActionLink("Product", "Detail", "Product", new { id ="5668"}, null) 

It Generates: < a href="https://www.blogger.com/product/detail/5678" > Product </a>

URL.Action ():

URL.Action method is used to generate the qualified URL by using the action and controller name with routes value.

Here is an example of URL.Action() method:

Url.Action("detail", "product", new { id = "5678" })

It Generates:  /product/detail/5678

More information about URL.Action @  MSDN : UrlHelper.Action Method

ASP.NET MVC : Anchor Tag IN MVC Razor View

In this blog we will discuss about the @HTMLHelper class and how to create the HTML Anchor by using @Html.ActionLink.

@HTMLHelper Class:

@Html is used in ASP.NET MVC to render HTML elements like TextBox, Button etc. and it binds the model object to HTML control and display the object’s value and while posting the page, it binds the control value to model object.

@Html.ActionLink Method:

HTMLHelper object has ActionLink method to render the anchor HTML element with specified link text and action name with controller.

Here is examples of Action methods:

·        @Html.ActionLink("Home", "Home", "Person") 

Generates <a href="/Person/Home" >Home</a>

·         IF you are not passing controller, by default it will take current page controller name.

    @Html.ActionLink("Home", "Home")

·         Action and Controller with parameter are given:

@Html.ActionLink("Home", "Home", "Person", new { PersonID = Model.ID}, null )

Generates <a href="../Person/Home?PersonID=1212" >Home</a>

·         Anchor with html ID

@Html.ActionLink("Home", "Home", "Person", new { PersonID = Model.ID}, new { id = "HomeLinkID"} )

Generates <a href="../Person/Home?PersonID=1212" id="HomeLinkID" >Home</a>

Thanks for visiting!!