SQL Server Developer

Using QUOTENAME() to Protect Against SQLInjection

QUOTENAME is function which has been available since SQL Server 2005. This function returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier, such as in the below example The output for this query is as below:           String Default_QuoteName QuoteName_WithQuote Long_String SQL Server [SQL Server] […]

Introduction to SQL Server Check Constraints

Check Constraints play a very important role in SQL Server as it allows data validation rules to be imposed at the SQL Server instance level itself instead of writing rules for each application. Check Constraint enforces Domain Integrity by restricting the values that can be inserted into columns. Domain Integrity ensures that only a valid […]

Getting SPID for an SQL Server Agent Job

When troubleshooting of an incident, it is important to find out SPID attached to the SQL Server Agent job, so that you can find the wait stat and other relevant information. Also, sometimes you may need to kill the SPID. There are a DMV and a system proc  you can get the help to achieve […]

How To Remove the Identity Column in SQL Server

Setting the identity property on a table when it is created is a simple task. The below snippet will create table called SampleTable with ID column which has an identity property. CREATE TABLE SampleTable ( ID INT IDENTITY(1,1), Name VARCHAR(30) ) To set an indentity column for an alredy created table using SQL Server Management […]

Automatically Retry A SQL Server Job After A Failed Step

If you are running SQL Server jobs with numerous steps there can be failures due to network glitches or deadlocks. In such a scenario the DBA will typically restart the job from the failed step. However, there is an option in SQL Server Agent to automatically retry the job, which is under utilized by many […]

Efficient T-SQL Code – A Case of Incorrect Use of a UNION Operator

A couple of days back, I had a developer complaining me the slow performance of some T-SQL which was causing the application to throw a Timeout Expired error. During Investigation, I noticed that the developer made an incorrect use of a UNION operator which was the culprit. In this article, I will demonstrate the issue […]

Passing Parameters to An SSRS Report From The Front End

In the last article we saw how we can call the report from the front end using the report viewer component of Visual Studio. In real time scenarios we usually want to view a customized report with the custom parameters. In this article we are going look at how we can pass parameters to a […]

Data Mining Add-ins Error

When using Microsoft SQL Server 2012 Data Mining Add-ins you may encounter the below error: Error (Data mining): Session mining objects (including special data source views used to process data mining dimensions) cannot be created on this instance The reason this occurs is that the Data Mining tool needs to create session mining models in […]

Pareto Charts in SSRS

The purpose of a Pareto chart is to highlight the most important amongst a set of factors. For example, in quality control for a manufacturer, a Pareto chart can highlight the most common sources of defects and the highest occurring type of defect. The Pareto principle is also known as 80-20 rule, so for quality […]

Design A Hybrid Report in SSRS

Hybrid reports are the reports which have two types of graphs using different axes within the same graph . For example. In the above report, Product cost is on the right axis while Sales Amount Percentage is shown on the left axis, so that viewers can easily compare Product Cost vs Sales Amount Percentage. Below […]