Dec 28, 2014

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.  

Dec 27, 2014

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.

Dec 26, 2014

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.

Dec 20, 2014

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.

Dec 14, 2014

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)