One of the colleagues asked me question which one is better clustered
index or non-clustered index in Sql server?
Sql Server script examples Query tuning and optimization best practices Databases, Tables, Stored procedures,functions, SSIS, SSRS Interview questions and answers explanation, Errors and solutions, Data 3G tariff recharge
Dec 28, 2014
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)
|
Subscribe to:
Posts (Atom)