SQL Server Articles


Categories : Performance | Audit | Business Intelligence | Clustering | Developer | Reporting | Windows Server | SQL Azure | Powershell | ASP.NET | Other .NET

Shopping Basket Analysis in SQL Server

A famous super market chain in USA once observed that men who are buying beer for weekend tend to buy nappies for their kids. This revelation enabled the chain in increase sales volume and revenue by placing the items in close proximity to eachother. An alternative approach would have been to move the items apart […]

Primer on Selecting Data Using Entity Framework

This question about selecting data using Entity Framework on StackOverflow got me thinking that a lot of LINQ to Entities code samples show very little consistency in how data is selected. Find(), Single(), SingleOrDefault(), First(), and FirstOrDefault() are often used interchangeably. The first issue to address is using Where() in the data selection statement. Where() […]

Monitor SQL Server Replication Jobs

The Replication infrastructure in SQL Server is implemented using SQL Server Agent to execute the various components involved in the form of a job (e.g. LogReader agent job, Distribution agent job, Merge agent job) SQL Server jobs execute a binary executable file which is basically C++ code. You can download all the scripts for this […]

Recovering a SQL Server Database from Suspect Mode

A couple of days back at I got a call from my support team informing me that one of our database located on the Production Server went into Suspect Mode. The version used was SQL Server 2005 Service Pack 3. Being a Production Database server, it was a Priority 1 incident and the expected time […]

SQL Server Performance Tuning – Index Tuning

In earlier installments of this series we looked at T-SQL Performance optimizations along with different T-SQL practices, we can now turn our attention to the second part of this series which is index Tuning In looking into Views we will break the article into two sections : Basic Index Tuning using DTA (Database Tuning Advisor) […]

Common Table Expressions (CTE) for SQL Server Developers

What is A CTE? A CTE is a temporary view that exists only in the scope of running query. A CTE is similar to a view but a CTE is embedded into the query. CTEs can be used in Stored Procedures, User Define Functions (UDFs), Triggers and Views but not in Indexed Views. Below is […]

Using Decision Trees in SQL Server

As a practical introduction to decision imagine you are assessing the risk of a PC being infected with a virus. The below data on factors influencing affecting the risk of a PC being infected with a virus is available on MSDN. Shares files Uses scanner Infected before Risk Yes Yes No High Yes No No […]

Does Transactional Replication Include Structural Changes Made to a Database

We typically use Transactional Replication for Reporting purposes. In Transactional Replication, the idea is to replicate the changes done on the Publisher Database (OLTP) onto the Subscriber Database (Reporting).  The main challenge in Replication is whether it will replicate the structural changes made on the Publisher database successfully onto the Subscriber database. For Demonstration purposes, […]

Does LogShipping Include the Structural Changes Made to the Database?

Recently a client asked me “ Does LogShipping ship the Create Table, Alter Table, Drop Table commands onto the Secondary Server?”. Obviously the answer to his question was “Yes” but I wanted to be 101% sure before committing something to him. In this article we are going to prove that LogShipping not only propogates the […]

SQL Server Hardware Optimization

An important concern in optimizing the hardware platform is hardware components that restrict performance, known as bottlenecks. Quite often, the problem isn’t correcting performance bottlenecks as much as it is identifying them in the first place. Start with obtaining a performance baseline. You monitor the server over time so that you can determine Server average […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |