Which one is better clustered index or non-clustered index in Sql server?

One of the colleagues asked me question which one is better clustered index or non-clustered index in Sql server? 

SQL Server Performance Tuning

This post keeps complete best practices of database and query performance tuning dashboard and optimization techniques tips and tricks with examples. Detail discussion of how to improve SQL server 2008, 2012 and 2014 performance

MERGE statement data synchronization sql server best practices

A very common business requirement we generally come across where we need to synchronization of two tables which includes:

1. Inserting of new data if it is not present in target table while it is in the source table

2. Updating the existing data if it already present in the source table

3. Deletion of data if present in the target table while it is not present in the source table.

Indexed view in Sql server performance

Speeding up queries by indexed views. Query tuning and optimization best practices.  

When to use derived tables, CTE and Temporary tables SQL server performance tuning

Common table expression (CTE) vs. derived table vs. temporary table vs. table variable best practice and query optimization    

Check the existence of records efficient | Fastest way Sql server

Optimized and fastest way to check the existence of records in a table

Multi-statement vs. In line Table Valued Function performance SQL server

Query tuning best practices recommendation  

There are two types of user defined table valued function.

a. Multi-statement table valued function

b. In line table valued function

Table valued function query performance Sql server

Table valued function performance, optimization and tuning

Aggregate values Group BY max sum query performance | Optimization | Tuning SQL SERVER

Query performance tuning and optimization of GROUP BY, MAX, and SUM etc. Aggregate values from same set of tables SQL Server

Dynamic conditions WHERE clause Performance SQL Server

Sometimes we need to write WHERE predicate dynamically. For example: Let assume there is business need to filter data on the basis of ntUserID when @btIsUser = 1 and on the basis of ntPostID when @btIsUser = 0.

Dynamic SQL Query Performance SQL Server

Some SQL server developers hate dynamic SQL queries. There might be below possible causes:

Stored procedure vs. Function performance SQL server

Some developers have a little confusion about when to create SQL function and when to create stored procedures? Some of the developers always create the stored procedures. Here we are going to discuss about performance impact between stored procedure and SQL function. 

How to write efficient | optimized | tuned queries in SQL Server

For a database developer it is a skill to write a most efficient tuned query.

Mixed Database Layers application code best practices

It is related to application design. There is not any direct relation with database tuning. 

Application code best practices SQL SERVER

Application program code performance tuning SQL server

Looping is most commonly used programming element over collection of data. Since it is very easy to think and implement a complex algorithm by processing one row at a

Predict about Future data during development phase In Sql server

Sometimes we get chance to work on the project which start from scratch. Initially we deal with very small data i.e. tables with 100 to 100000 records. With these data, application runs very fast.

Pagination code best practices SQL Server

Pagination Strategy

Pagination is one of the very common need to design a web page (Using C#, PHP, JQuery etc). While writing the code for pagination, some developers think, it is a bad idea to call database multiple times to load data for each web pages instead of that 

Application Coding: Best practices for database tuning

There are many programming language to develop software like C#, Java, ASP.net, PHP, Python. How to write efficient and optimized code? 

Best Team To Develop An Application

Who Am I?

I want to share an interesting thing about a small project team working on a very small module.  Team size of the project was only 6 including one QA.

Query Execution and process Life Cycle in SQL SERVER

Do you know how SQL queries are processed in SQL Server? What are the execution order steps? What are the possible scopes where queries can be tuned and what are the limitations? Here we are going to discuss about query life cycle in detail.

Common causes database performance bottleneck in SQL SERVER

An interesting survey

There are many survey has been conducted regarding the causes of SQL server performance bottlenecks which they found   while tuning the queries in a database. Below table illustrates the aggregates:

Root Cause	Response (%)
Database Design	10
Query Writing	30
Index and Statistics Strategy	22
Application Code	18
Server Configuration and Settings	20

If we will observe the responses, application code and query writing is almost 50% responsible for degradation of database performance!! Here we will discuss about all the root causes of query bottleneck in detail.

Query Optimization In Sql Server

Introduction: SQL Server Query Optimization

Database tuning is one of the high business need. A market research says just one second delay in page load can cause 7% loss in customer conversion.

Sql server convert string to date

Sql server cast or convert to string is only possible for validate date format.

Conversion table for string to only date (Year in 4 digits)

Format	Example	Convert Script
MM/DD/YYYY	12/21/2014	CONVERT(DATETIME,'12/21/2014',101)
YYYY/MM/DD	2014/12/21	CONVERT(DATETIME,'2014/12/21',111)
DD/MM/YYYY	21/12/2014	CONVERT(DATETIME,'21/12/2014',103)
YYYY.MM.DD	2014.12.21	CONVERT(DATETIME,'2014.12.21',102)
DD.MM.YYYY	21.12.2014	CONVERT(DATETIME,'21.12.2014',104)
MM.DD.YYYY	12.21.2014	CONVERT(DATETIME,'12.21.2014',101)
MON DD YYYY	DEC 21 2014	CONVERT(DATETIME,'DEC 21 2014',0)
DD MON YYYY	21 DEC 2014	CONVERT(DATETIME,'21 DEC 2014',106)
YYYY MON DD	2014 DEC 21	CONVERT(DATETIME,'2014 DEC 21',102)
MON DD,YYYY	DEC 21,2014	CONVERT(DATETIME,'DEC 21,2014',107)
DD-MM-YYYY	21-12-2014	CONVERT(DATETIME,'21-12-2014',105)
MM-DD-YYYY	12-21-2014	CONVERT(DATETIME,'12-21-2014',110)
YYYY-MM-DD	2014-12-21	CONVERT(DATETIME,'2014-12-21',102)
YYYYMMDD	20141221	CONVERT(DATETIME,'20141221',112)

Conversion table for string to only date (Year in 2 digits)

Format	Example	Convert Script
MM/DD/YY	12/21/14	CONVERT(DATETIME,'12/21/14',1)
YY/MM/DD	14/12/21	CONVERT(DATETIME,'14/12/21',11)
DD/MM/YY	21/12/14	CONVERT(DATETIME,'21/12/14',3)
YY.MM.DD	14.12.21	CONVERT(DATETIME,'14.12.21',2)
DD.MM.YY	21.12.14	CONVERT(DATETIME,'21.12.14',4)
MON DD YY	DEC 21 14	CONVERT(DATETIME,'DEC 21 14',0)
DD NON YY	21 DEC 14	CONVERT(DATETIME,'21 DEC 14',6)
MON DD,YY	DEC 21,14	CONVERT(DATETIME,'DEC 21,14',7)
DD-MM-YY	21-12-14	CONVERT(DATETIME,'21-12-14',5)
MM-DD-YY	12-21-14	CONVERT(DATETIME,'12-21-14',10)
YYMMDD	141221	CONVERT(DATETIME,'141221',12)

Conversion table for string to date with time

Example	Convert Script
21/12/2014 05:30:45:555PM	CONVERT(DATETIME,'21/12/2014 05:30:45:555PM',131)
2014/12/21 05:30:45:555PM	CONVERT(DATETIME,'2014/12/21 05:30:45:555PM',111)
12/21/2014 05:30:45:555PM	CONVERT(DATETIME,'12/21/2014 05:30:45:555PM',101)
DEC 21 2014 02:30PM	CONVERT(DATETIME,'DEC 21 2014 02:30PM',0)
DEC 21 2014 05:30:45:555PM	CONVERT(DATETIME,'DEC 21 2014 05:30:45:555PM',109
DEC 21 14 05:30:45:555PM	CONVERT(DATETIME,'DEC 21 14 05:30:45:555PM',9)
21 DEC 2014 17:30:45:555	CONVERT(DATETIME,'21 DEC 2014 17:30:45:555',113)
21 DEC 14 17:30:45:555	CONVERT(DATETIME,'21 DEC 14 17:30:45:555',13)
2014 DEC 21 05:30:45:555PM	CONVERT(DATETIME,'2014 DEC 21 05:30:45:555PM',102)
DEC 21,2014 05:30:45:555PM	CONVERT(DATETIME,'DEC 21,2014 05:30:45:555PM',107)
2014-12-21 17:30:45	CONVERT(DATETIME,'2014-12-21 17:30:45',120)
2014-12-21 17:30:45	CONVERT(DATETIME,'2014-12-21 17:30:45',20)
2014-12-21 17:30:45.555	CONVERT(DATETIME,'2014-12-21 17:30:45.555',121)
2014-12-21 17:30:45:555	CONVERT(DATETIME,'2014-12-21 17:30:45:555',21)
2014-12-21T17:30:45.555	CONVERT(DATETIME,'2014-12-21T17:30:45.555',126)
2014-12-21T17:30:45.555Z	CONVERT(DATETIME,'2014-12-21T17:30:45.555Z',127)
12-21-2014 05:30:45:555PM	CONVERT(DATETIME,'12-21-2014 05:30:45:555PM',101)
21-12-2014 05:30:45:555PM	CONVERT(DATETIME,'21-12-2014 05:30:45:555PM',103)