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

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

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

Auto Generate Code (CRUD) for 3 Layered architecture

12 Dec 2016 by Suresh Dasari's

Auto Generate Code (CRUD) for 3 Layered architecture (Entity, Data Access & Business Layer) with Stored Procedures based on table design.

SQL-Server-2005

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

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

Pivoting on SQL Server Varchar Data Types

22 Feb 2016 by Sifiso W Ndlovu

This tip illustrates a workaround to SQL Server pivoting on variable character data types.

SQL-Server-2005

SQL-server-2000

String splitting/tokenizing using T-SQL User Defined Function

21 Jan 2016 by smirson

This is an alternative for "String splitting/tokenizing using T-SQL user defined function."

SQL-Server-2008

SQL-Server-2005

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

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

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

Dynamic SQL Connection in ASP.NET 3.5 with User Controls

19 Jun 2015 by ss9o9o9o

Transferring backup files from a remote SQL Server and Dynamic SQL Connection with Restore utility.

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

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

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

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

Auditing Failed Logins in SQL Server: Looking Beyond Post-event Analysis

23 Sep 2014 by SatyLepide

By analyzing log-in attempts on SQL server, admins can build the complete picture out of the sequence of events that led to compliance failure or unauthorized access.

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

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

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

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

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

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

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

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

Setting deadlock priority in SQL Server

19 Sep 2013 by Saral S Stalin

Explains how to run a script with higher priority to avoid it getting terminated in a deadlock situation.

SQL-Server-2008

SQL-Server-2012

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

Split function in SQL

30 Jul 2013 by Gauri Chodanker

SQL function in SQL

SQL-Server-2005

SQL-server-2000

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

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

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

SQL Server StringPadding (String function)

24 Apr 2013 by Surender Singh (CodeToastDev)

Custom StringPadding functions for SQL Server

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

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: 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: 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

Example of gridview rowcommand on Button Click

29 Mar 2013 by Nandakishore G N

Example For Gridview RowCommand Event

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

Return Comma Separated String using SQL

30 Jan 2013 by Yogesh Gulve

Return Comma Separated String using SQL

SQL-Server-2005

Generating and Splitting a Delimited String Column

17 Jan 2013 by Tharaka MTR

This article describes the easiest way to create and process delimited row using SQL XQuery.

SQL-Server-2005

SQL-server-2000

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

15 Jan 2013 by Wonde Tadesse

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

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

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

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

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

20 Dec 2012 by Wendelius

This tip describes how to delete duplicate rows from a table that doesn't have a key.

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

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

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: 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

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 : 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: 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: 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: 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

Creating SQL Joins in Simple Steps

20 Sep 2012 by Sreedhar Puligundla

Simple steps to create SQL joins by using SQL Server 2008 Management Studio

SQL-Server-2005

SQL-server-2000

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

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

Encrypt all Stored Procedures with PowerShell

7 Aug 2012 by kennysun

encrypt all stored procedures in SQL server with powershell

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

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

Index and Sort Order

20 Jul 2012 by Mr. Joydeep Das

What the effects of Ordering in Index

SQL-Server-2008

SQL-Server-2005

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

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

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

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

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

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

Simple Technique to Exclude Column from SELECT * FROM TABLENAME

22 Mar 2012 by SERokon

Exclude Column from SELECT * statement

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

Rename a Database and its MDF and LDF files in SQL Server

13 Mar 2012 by Tecfield

This tip shows how to rename a database and its MDF and LDF files in SQL Server.

SQL-Server-2008

SQL-Server-2005

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

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

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

25 Feb 2012 by Sanjay K. Gupta

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

SQL-Server-2005

SQL-Server-2008

SQL-server-2000

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

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

Generate Insert script to extract data

18 Feb 2012 by Rishikesh_Singh

Generate Insert script to extract data

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

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

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

Uploading Zip File in ASP.NET

4 Feb 2012 by chetan virkar

Uploading Zip File in ASP.NET

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

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

Selective masking in SQL server 2005

5 Jan 2012 by Jobless Creature

Masking selected characters in SQL, hiding few characters

SQL-Server-2005

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

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

30 Sep 2011 by VDmitrovsky

1) You cann't drop any object in schemas other than dbo, so you MUST use sys.schemas system view properly.2) You cann't drop tables even in dbo schema if they are referenced by other ones, not dropped so far. Drop all FKs before tables.dva

SQL-Server-2005

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

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

Record Count of Tables in SQL Server

16 Aug 2011 by thatraja

The below query can be used to get the record count of all tables in the current database.

SQL-Server-2005

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

Export data from SQL Server as XML

10 Aug 2011 by seankk

SQL-Server-2005

All the jobs in SQL Server

10 Aug 2011 by Wendelius

Or simply:exec msdb..sp_help_jobsp_help_job (Transact-SQL)[^]

SQL-Server-2005

All the jobs in SQL Server

10 Aug 2011 by seankk

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

Adding Time in HH:MM Format - Useful SQL Query

7 Jun 2011 by Shahriar Iqbal Chowdhury/Galib

Hi,Here is an alternative for your solutionSELECT CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND,0,CAST(TMP.TotalTime as DATETIME))),0) as TIME)FROM( SELECT CAST(timeduration AS TIME) TotalTime FROM mytable) TMP

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

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

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

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

Using Pivot In SQL for Reporting

28 Feb 2011 by pankajupadhyay29

Nice and fast way for reporting

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

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

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

nth Highest Salary in SQL Server 2005

16 Feb 2011 by thatraja

SELECT TOP 1 salaryFROM (SELECT DISTINCT TOP n salaryFROM employeeORDER BY salary DESC) aORDER BY salarywhere n > 1SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value[^]

SQL-Server-2005

nth Highest Salary in SQL Server 2005

16 Feb 2011 by Mohd_Ahmad

SQL-Server-2005