Click here to Skip to main content

Submit your article

SQL Server 2005

SQL-Server-2005

Great Reads

How to Set Default Value or Binding for BLOB DataTypes on SQL Server

by Wonde Tadesse

This tip/trick helps to set a default value for blob data type such as Image, VarBinary.

AJAX-enabled Name Selector User Control

by Steve Krile

Fully AJAX-enabled user control used to select names from a database in a drop-down format.

Create & Populate Time Dimension with 24 Hour+ Values

by Mubin M. Shaikh

Create time dimension with 24 hour plus values and time buckets in your data warehouse

Notification of Silverlight Applications about Database State Changes

by Max Paulousky

This article presents the techniques and caveats of building Silverlight applications that can be notified about database state changes via polling duplex. WSHttp binding and CLR triggers are used to implement the solution as well.

Latest Articles

SQL - Pivot with Grand Total Column and Row

by Narapareddy Shyamprasad

SQL Dynamic Pivots

Select Statement to HTML Table

by Luca Astolfi

User defined procedure for make an HTML table from T-SQL Select statment

Use RegEx in SQL with CLR Procs

by JasonShort

Wouldn’t it be handy if you could use regular expressions (RegEx) in your SQL code? You could do some really advanced queries through the ability to specify regular expressions rather than just the simple like % matching you normally use. SQL CLR can do that!

Code Review Checklist

by Ebenezar John Paul

An Ideal Code Review Checklist that applies for most programming languages

All Articles

SQL Server 2005

Create job to export Stored Procedure daily

27 Nov 2012 by Aadhar Joshi

Creating job in sql server which automates taking backup of all stored procedures and functions in physical drive.

SQL-Server-2008

SQL-Server-2005

SQL Server: How to Get All Indexes List With Involved Columns Name

17 Sep 2012 by aasim abdullah

Get all indexes list in detail, with their involved columns

SQL-Server-2008

SQL-Server-2005

SQL Server: Keeping Log/Alert for Job Disable/Enable Status

1 Oct 2012 by aasim abdullah

How do you know, if someone disabled your job at production database server. Who is the calprit. How to send a mail alert for this change ??

SQL-Server-2008

SQL-Server-2012

SQL-Server-2005

SQL Server: Restore Failure from Enterprise to Standard Edition

19 Apr 2013 by aasim abdullah

Restore failure from Enterprise to Standard edition in SQL Server.

SQL-Server-2008

SQL-Server-2005

SQL Server: Get All Databases Size

2 Oct 2012 by aasim abdullah

A simple query to get all databases size on a given instance.

SQL-Server-2008

SQL-Server-2005

SQL Server: Automatic Query Execution at Every Instance Startup

19 Apr 2013 by aasim abdullah

Automatic query execution at every instance startup.

SQL-Server-2008

SQL-Server-2012

SQL-Server-2005

SQL Server: TOP…WITH TIES - A Beauty of TSQL

19 Apr 2013 by aasim abdullah

The TOP clause is commonly used to get the top required rows from a result set. The beauty of this clause is that it can be used with the WITH TIES clause to retrieve all similar rows to a base result set.

SQL-Server-2008

SQL-Server-2012

SQL-Server-2005

SQL Server: Changed data capture without using Triggers

23 Oct 2012 by aasim abdullah

How to use DELETED and INSERTED virtual tables to capture data change in result of any INSERT, UPDATE or DELETE statement.

SQL-Server-2008

SQL-Server-2012

SQL-Server-2005

SQL Server : How to Keep Database Restore History

24 Oct 2012 by aasim abdullah

Backup and then restore of a database is a normal job for developers and DBAs. This script will be helpfull to findout, from which path, and which backup file a database was restored.

SQL-Server-2008

SQL-Server-2012

SQL-Server-2005

SQL Server Scripts: Get All Nested Stored Procedures List (Procedures with Dependent Procedures)

1 Nov 2012 by aasim abdullah

A stored procedure can be called from another stored procedure as nested stored procedure. How to get a list of these stored procedures with their nested one

SQL-Server-2008

SQL-Server-2005

SQL Server: Bookmark Lookups, a Query Performance Killer

1 Nov 2012 by aasim abdullah

You have place indexes but still queries are slow... are you facing bookmark lookup problem ???

SQL-Server-2008

SQL-Server-2012

SQL-Server-2005

Adding Time in HH:MM Format - Useful SQL Query

10 Feb 2012 by abdul sami

select aday, code, convert(varchar(5), min(atime), (108))MinTime, convert(varchar(5), max(atime),(108)) MaxTime from testgroup by aday,codeorder by aday asc

SQL-Server-2005

How To Select Record Based on Row Number

20 Jan 2010 by Abhishek Sur

It is a common issue of many of the guys on how we can fetch a record based on its record number. It is really easy to get first 10 records usingSELECT TOP 10 * FROM EMPBut when we want to fetch records in such a way say I need to fetch record from 40 to 50. This comes very handy when...

SQL-Server-2005

Write error log in a text file for Custom Assembly being used in Reporting Services

19 Sep 2013 by alifaraze

writing error in a text file is very easy but writing error from a custom assembly in reporting services requires permissions

SQL-Server-2005

SQL-server-2000

Sort AlphaNumeric Data in Sql

23 Aug 2011 by Anurag Gandhi

I have gone through a lot of examples to sort alpha numeric data in sql. Here is my way:

SQL-Server-2008

SQL-Server-2005

Copy/Synchronize Table Data between databases

16 Mar 2012 by Anurag Gandhi

A Query to copy an individual table data between databases

SQL-Server-2008

SQL-Server-2005

Import CSV or txt File Into SQL Server Using Bulk Insert.

21 May 2014 by Arvind Singh Baghel

Import CSV or txt File Into SQL Server Using Bulk Insert.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

SQL Server Health Check Script with Powershell

3 Dec 2014 by Atul_Kapoor

Perform SQL Server Instance health check simultaneously on more than one server using Powershell

SQL-Server-2008

SQL-Server-2005

The Best Table Finder (The BestCube Problem)

20 Dec 2016 by BharatRamV

Finds the Best Table for a given set of columns

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

SMO 101 - Getting all DB's

17 Aug 2011 by bmckuhen

How to get a quick list of all DBs using SMO

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

Dynamic ORDER BY with multiple criteria in T-SQL

28 May 2013 by Bob Crowley

How to use a CASE statement in ORDER BY to avoid dynamic SQL

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

How to Protect from SQL Injection in PhP based website

20 Jun 2012 by chetan dudhagara

How to Protect from SQL Injection in PhP based website

SQL-Server-2005

Uploading Zip File in ASP.NET

4 Feb 2012 by chetan virkar

Uploading Zip File in ASP.NET

SQL-Server-2005

How to remove duplicate rows in SQL Server 2005 when no key is present

22 Feb 2011 by Costica U

Here is another solution:IF OBJECT_ID( 'tempdb..#TestTable' ) IS NOT NULL BEGIN DROP TABLE #TestTable ENDCREATE TABLE #TestTable ( Column1 varchar(1), Column2 int )INSERT INTO #TestTable VALUES ('A', 1);INSERT INTO #TestTable VALUES ('A', 1); --...

SQL-Server-2005

nth Highest Salary in SQL Server 2005

18 Apr 2011 by Costica U

The original solution has some issues. Check the following script:IF OBJECT_ID('tempdb..#salary') IS NOT NULL BEGIN DROP TABLE #salary ENDCREATE TABLE #salary ( Salary INT )INSERT INTO #salary(Salary) VALUES(5) -- 1INSERT INTO #salary(Salary)...

SQL-Server-2005

SQL Server Database Backup Status Report

5 Jun 2015 by Dathuraj Pasarge

Know whether your databases are backed up successfully in the last 24 hours or not !!!

SQL-Server-2005

SQL-Server-2008

SQL-Server-2008R2

SQL-server-2000

Setting up an MS Outlook Appointment C#

28 May 2010 by DecodedSolutions.co.uk

This is a simple article describing how to create an add an outlook appointment to your MS Outlook Calender.

SQL-Server-2005

Get Tables List With Its Dependent Tables Names

15 Jan 2012 by Dhol Gaurav

Get Tables List With Its Dependent Tables Names

SQL-Server-2008

SQL-Server-2005

Creating a SQL Server Database Project Use in Visual Studio 2012 and Use TFS for Version Control

8 Jun 2015 by Diluka Wittahachchige

Creating SQL Server database project use in Visual Studio 2012 and maintain versions use in TFS

SQL-Server-2008

SQL-Server-2005

Number of occurences of any record in a column

25 Jul 2012 by Durgesh Swarnkar

Getting the count of records in a particular column.

SQL-Server-2005

Concatenate many rows into a single text string using SQL Server 2008

25 Feb 2012 by e-Lopes

How to concatenate many rows into a single text string using SQL Server 2008.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

Using Pivot In SQL for Reporting

11 Mar 2011 by Feleke Feyissa

select * from(SELECT l.Prepared_Date as Prepared_Date , m.Material_Code as Material_Code ,n.Name as Name,n.Description as Description , m.Quantity as Quantity,n.Unit_Of_Measure as Unit_Of_Measure , m.Status as Status...

SQL-Server-2005

Get days of a month in SQL

3 Feb 2010 by g_p_l

@dt is a datetime valuederive the first of this month, add a month to itthen subtract 1 day to get the last day of this monthselect days_of_month=day( dateadd(d,-1, dateadd(m,1, dateadd(d,1-day(@dt), @dt) ) ) )

SQL-Server-2005

Split function in SQL

30 Jul 2013 by Gauri Chodanker

SQL function in SQL

SQL-Server-2005

SQL-server-2000

Random Date/Time Test Data Generation

25 Aug 2016 by Gene R. Browning

Generate random dates (and times if you like) for your test data environment.

SQL-Server-2005

SQL-server-2000

Record Count of Tables in SQL Server

16 Aug 2011 by Graham Cottle

Sometimes it is necessary to have the schema name included as well. I concatenate with the table name for my convenience, but it is not vital to do so.SELECT sys.sysindexes.rows, sys.schemas.name + '.' + sys.objects.nameFROM sys.objectsINNER JOIN sys.schemasON ...

SQL-Server-2005

String splitting/tokenizing using T-SQL user defined function.

14 Feb 2011 by Hajo Messner

There is a little inconsistence in this function, because the @Tokens Elements and the @Delimiter parameter are of data type NVARCHAR (=Unicode) while the @String parameter is of data type VARCHAR (=ANSI). If the @String parameter can be Unicode, it must be declared as NVARCHAR. If the @String...

SQL-Server-2008

SQL-Server-2005

Generate XML Using FOR XML

29 May 2012 by Harsh Gandhi

Generate XML Using FOR XML ; Difference between different modes (AUTO, RAW, PATH, EXPLICIT) of FOR XML to generate XML in SQL Server

SQL-Server-2008

SQL-Server-2005

ShoppingCart using Gridview in ASP.NET

13 Jun 2012 by headshot9x

How to create ShoppingCart using Gridview in asp.net with code VB

SQL-Server-2008

SQL-Server-2005

Check Digit Vertical and Horizontal

6 May 2013 by Hernán Hegykozi

This tip explains how to calculate the Check Digit Vertical (CDV) and Horizontal (CDH) by SQL Server.

SQL-Server-2008

SQL-Server-2005

Building hierarchy using Recursive CTE

5 Jan 2011 by Hiren solanki

Building hirarchy in the sense of hirarchy of manager, employee though both are an employee of an enterprise but both are related or depend on hierarchical level.

SQL-Server-2008

SQL-Server-2005

Way to Debug SQL Parameters in ASP.NET

18 Feb 2010 by Indrajeet Sutar

While using stored procedures, we need to pass SQL parameteres to those stored procedures. When there are large numbers of parameters then it is become boring task or tedious job to debug and check what values passed with. Here I have created a function that will check the values passed to...

SQL-Server-2005

String splitting/tokenizing using T-SQL user defined function.

8 Feb 2011 by Irfan Baig

String splitting/tokenizing using T-SQL user defined function.

SQL-Server-2008

SQL-Server-2005

Adding Time in HH:MM Format - Useful SQL Query

11 Feb 2012 by Jobless Creature

HH:MM Time format addition

SQL-Server-2005

Selective masking in SQL server 2005

5 Jan 2012 by Jobless Creature

Masking selected characters in SQL, hiding few characters

SQL-Server-2005

Common Table Expression to find all related nodes

12 Nov 2013 by Jobless Creature

CTE To find all the related nodes in a hierarcy

SQL-Server-2008

SQL-Server-2005

Use of columns belonging to tables referenced on main query in a subquery

17 Feb 2011 by John Gathogo

Catastrophic results associated with unqualified use of columns belonging to tables referenced on main query in a subquery

SQL-Server-2008

SQL-Server-2005

Encrypting all Stored Procedures in a Database

11 Apr 2012 by jroughgarden

This is an alternative for "Encrypting all Stored Procedures of a Database".

SQL-Server-2005

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

23 Feb 2011 by Kale Yogesh

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

SQL-Server-2005

Using Pivot In SQL for Reporting

15 Mar 2011 by Kannan K R

select ExDate, SUM(case when Dept='A' then expense else 0 end)[Store A Expense],SUM(case when Dept='B' then expense else 0 end)[Store B Expense],SUM(case when Dept='C' then expense else 0 end)[Store C Expense]from expenseTable where Dept in ('A','B','C') group by ExDate

SQL-Server-2005

Encrypt all Stored Procedures with PowerShell

7 Aug 2012 by kennysun

encrypt all stored procedures in SQL server with powershell

SQL-Server-2005

How to empty SQL Server 2005 database using cursor & sys.objects (Deleting All Tables, stored procedures, views & UDF’s)

24 Mar 2010 by kiran dangar

Understrading sys.objectsSys.objects is a system VIEW in SQL Server 2005, for each SQL database there is a separate sys.object view which gets stored within databse itself.Using Sys.objects returns list of all database objects and its types, type can be either of given below:DB OBJECT...

SQL-Server-2005

How to empty SQL Server 2005 database using cursor & sys.objects (Deleting All Tables, stored procedures, views & UDF’s)

25 Feb 2010 by kiran dangar

For Ravi LVS,The way you have suggested to Empty the database by just deleting and recreating it, is not at all smarter way when you are about to empty a remote database located on live database server, There are times when you don't hold such rights of deleting database on Live...

SQL-Server-2005

CLR-addon for DATE and Split-String

3 Jan 2013 by Levente Kupás

CLR-addon for T-SQL DATE transformation and Split-String

SQL-Server-2008

SQL-Server-2005

Select Statement to HTML Table

2 Feb 2018 by Luca Astolfi

User defined procedure for make an HTML table from T-SQL Select statment

SQL-Server-2008

SQL-Server-2008R2

SQL-Server-2005

Get Top X rows from a table

23 Feb 2012 by Mahmud Hasan

How to use variable in SELECT TOP SQL command

SQL-Server-2008

SQL-Server-2005

How to update a row with another row if column values are null in SQL

17 Aug 2012 by ManojKumar19

Update row column with another row column in same table SQL only if it is null.

SQL-Server-2005

SQL-server-2000

Drop all Triggers belonging to any schema in MS SQL Server

9 Oct 2013 by Matthew Taylor

How to remove Microsoft SQL Server triggers associated with a custom schema.

SQL-Server-2005

SQL-server-2000

Record Count of Tables in SQL Server

19 Feb 2010 by Md. Marufuzzaman

This is good... You can also consider this link.[^]

SQL-Server-2005

Get accurate age by date

20 Feb 2010 by Md. Marufuzzaman

Get accurate age by date using MS SQL Server

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

Get my database server with current database.

24 Feb 2010 by Md. Marufuzzaman

This is a very simple use of SQL Server global variables. Microsoft SQL Server provides a massive number of global variables, which are very effective to use in our regular Transact-SQL. Global variables represent a special type of variable. The server always maintain the values of these...

SQL-Server-2008

SQL-Server-2005

Read a file using transact-sql.

8 Jul 2010 by Md. Marufuzzaman

How easy to read a file without using of the seven extended stored procedures.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

How to Split Microsoft SQL Server Table Row Data

18 Dec 2015 by Md. Marufuzzaman

This tip shows you an easy way to split Microsoft SQL Server table row data.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

A Simple Example of Microsoft SQL Server Pad String

18 Dec 2015 by Md. Marufuzzaman

A simple example of Microsoft SQL Server Pad String

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

A Simple and Another Way to Find Rows on n’th Maximum Number from a Table

23 Dec 2015 by Md. Marufuzzaman

A simple tip/trick to get rows on n’th maximum number from a table

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

Insert default value[s] in a table

29 Dec 2009 by Mehul M Thakkar

I was facing the problem of inserting a default value on single column table and I have achieved via this way.insert into > default valuesAlso this could be used for many columns in a table defining with default constraints.ThanksMehul Thakkar

SQL-Server-2005

SQL Server - BLOB Import and Export

24 Apr 2012 by mkader

SQL scripts to insert File to BLOB field and export BLOB to File

SQL-Server-2008

SQL-Server-2005

nth Highest Salary in SQL Server 2005

16 Feb 2011 by Mohd_Ahmad

SQL-Server-2005

Index and Sort Order

20 Jul 2012 by Mr. Joydeep Das

What the effects of Ordering in Index

SQL-Server-2008

SQL-Server-2005

Create & Populate Time Dimension with 24 Hour+ Values

5 Sep 2013 by Mubin M. Shaikh

Create time dimension with 24 hour plus values and time buckets in your data warehouse

SQL-Server-2008

SQL-Server-2005

Example of gridview rowcommand on Button Click

29 Mar 2013 by Nandakishore G N

Example For Gridview RowCommand Event

SQL-Server-2005

Find text in Stored Procedures, View, Trigger, and Function

6 Jun 2013 by Nelson Kosta Souto

Find text in Stored Procedures, View, Trigger, and Function.

SQL-Server-2008

SQL-Server-2005

Convert RTF to Plain Text (Revised Again)

9 Apr 2016 by NightWizzard

Handling for hex expressions and the trailing '}'

SQL-Server-2005

SQL-server-2000

Reading Excel file in SQL Server

17 Jan 2011 by pankaj_254768

Shows how to read an Excel file using SQL query commands from SQL Server

SQL-Server-2005

Using Pivot In SQL for Reporting

28 Feb 2011 by pankajupadhyay29

Nice and fast way for reporting

SQL-Server-2005

T-sql over clause to aggregating without groupby

2 May 2011 by pankajupadhyay29

A nice way to get aggregated value without group by

SQL-Server-2005

T-SQL Function: Add a space between all characters

23 Jul 2012 by Paul M Simpson

Expanding a string by inserting alternating single spaces between characters

SQL-Server-2008

SQL-Server-2008R2

SQL-Server-2005

Microsoft SQL System View Version Map

13 Dec 2012 by Paw Jershauge

MS SQL system views, mapped over version.

SQL-Server-2008

SQL-Server-2005

Concatenate many rows into a single text string using SQL Server 2008

23 Feb 2012 by PIEBALDconsult

See also Stupid CTE tricks -- string concatenation[^]

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

SQL Group by With Joins

15 Feb 2010 by Pranay Rana

How to use Group By clause when joining to table Let's consider one scenario where I have two table employees (contains employee detail) and sales (contains infomation about sales done by employee).Structure of...

SQL-Server-2005

SQL-server-2000

Number of different ways to get total number of rows from tables

21 Jun 2010 by Pranay Rana

Getting a SQL Server RowCount Without doing a Table Scan

SQL-Server-2005

SQL-server-2000

Find Sp from database which is related to(using) table XXX

27 Feb 2010 by Pranay Rana

Problem : - During project development I got the requirement that I have to modified the column name of the table which is used in the no of procedure i.e i have to found all procedure related to that table and modify it For the above problem following are the solution that i used First ...

SQL-Server-2005

SQL-server-2000

Record Count of Tables in SQL Server

26 Aug 2010 by Pranay Rana

Number of different ways to get total number of rows from tables

SQL-Server-2005

Encrypting all the Stored Procedures of a Database

18 Nov 2014 by qadirv

Quick C# console application to encrypt all your database stored procedures

SQL-Server-2005

Grouping dynamically on different columns in single query

4 Jan 2013 by RahulAJoshi

Grouping dynamically on different columns in single query

SQL-Server-2008

SQL-Server-2005

A Unique Feature of SQL SERVER Loop with Go Statement

10 Nov 2013 by Rajat-Indiandotnet

A unique feature of SQL SERVER Loop with Go statement

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

How to find column or text in entire stored procedure of a database?

11 Nov 2013 by Rajat-Indiandotnet

Find column or Text in entire stored procedure

SQL-Server-2008

SQL-Server-2005

Use Union All & Select Queries Effectively

2 Aug 2010 by rajeshitpro

Union All,Select

SQL-Server-2005

Get days of a month in SQL

3 Feb 2010 by rajivlipu

select days_of_month=day(dateadd(mm,-1,'12-feb-2010'),-1) :laugh:

SQL-Server-2005

Find Missing Date Ranges in SQL Server

10 Feb 2012 by Ramanujam Shankar

Find missing date periods from a table.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

Concatenate many rows into a single text string using SQL Server 2008

8 Mar 2012 by Ramanujam Shankar

Using COALESCE function - concatenate many rows into a single text string output in SQL Server.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

Install SQL Server 2005 and 2008 on the same computer

6 Nov 2011 by Ravi Tuvar

Installing SQL Server 2005 and 2008 in the same computer.

SQL-Server-2005

Order by Clause with Column_Index instead of Column Name

18 Jul 2011 by RaviRanjanKr

A tip enable you to learn how to use Column_Index instead of Column Name with Order by clause

SQL-Server-2005

Generate Insert script to extract data

18 Feb 2012 by Rishikesh_Singh

Generate Insert script to extract data

SQL-Server-2005

SQL Atomic Operation on UPDATE and DELETE

13 Jan 2012 by Robby Tendean

How to do SQL atomic operations on UPDATE and DELETE syntax.

SQL-Server-2008

SQL-Server-2005

How to setup database versioning tool RoundhousE

2 Sep 2014 by Rohan Kishor Garud

RoundhousE (RH) is a database migrations engine that uses plain old SQL Scripts to transition a database from one version to another.

SQL-Server-2005

SQL-server-2000

How to search between two dates and get all records

27 Mar 2012 by SA - Hibbert

Return all records between two dates that include time in SQL.

SQL-Server-2008

SQL-Server-2005

SELECT from Stored Procedure in SQL Server

10 Dec 2012 by sachin bankar

How to use the result set from a Stored Procedure in a Select statement.

SQL-Server-2005

Triki Game Using SQL Triggers

25 Feb 2013 by salchichurria

Triki game created in SQL Server with triggers.

SQL-Server-2008

SQL-Server-2005

How to Search a Column Name Within All Tables of a Database and How to Search Stored Procedures Containing a Particular Text

23 Dec 2010 by Sandesh M Patil

How to search a column name within all tables of a database and how to search stored procedures containing a particular text

SQL-Server-2005