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

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

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

Adding Time in HH:MM Format - Useful SQL Query

11 Feb 2012 by Jobless Creature

HH:MM Time format addition

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

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

All the jobs in SQL Server

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

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

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

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

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

Clearing Transaction Logs

24 Jan 2011 by Sunasara Imdadhusen

Clear the transaction Log if you are facing a low disk space

SQL-Server-2005

Clearing Transaction Logs

4 Jan 2011 by thatraja

By this way tooHow to Shrink SQL Server database log files?[^]

SQL-Server-2005

Clearing Transaction Logs

23 Jan 2011 by sx2008

The root cause why the transaction log file grows so big is the wrong recovery model.The recovery model is set to full by default.99 percent of all SQL Servers never make use of this model because there is no backup of the transaction log file.So the great majority of all databases should...

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

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

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

23 Feb 2012 by PIEBALDconsult

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

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

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

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

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

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

Create Linq to SQL connection with select, insert, delete, update

6 Feb 2011 by SuhasHaridas

Create Linq to SQL connection with select, insert, delete, update

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

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

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

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

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

Encrypt all Stored Procedures with PowerShell

7 Aug 2012 by kennysun

encrypt all stored procedures in SQL server with powershell

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

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

Example of gridview rowcommand on Button Click

29 Mar 2013 by Nandakishore G N

Example For Gridview RowCommand Event

SQL-Server-2005

Export data from SQL Server as XML

10 Aug 2011 by seankk

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

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

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

Generate Insert script to extract data

18 Feb 2012 by Rishikesh_Singh

Generate Insert script to extract data

SQL-Server-2005

Generate Script with Data from Database

28 Oct 2010 by Sunasara Imdadhusen

SQL Server 2005 : Generate Script with Data from Database – Database Publishing Wizard

SQL-Server-2005

Generate Script with Data from Database

13 Nov 2010 by tarun_j200

SSMS also provides the option to generate SQL script file which contains both schema and data.Right click the database and select Generate Scripts under Tasks Menu.Click Set Scripting Options on the left hand menu (in SQL Server 2008 R2) then click Advanced button, there is an option under...

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

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

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

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

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

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

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

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

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)

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

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

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

How to get the details of Open Transaction in SQL Server

2 Sep 2010 by tarun_j200

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

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

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

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

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

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

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

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

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

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

Index and Sort Order

20 Jul 2012 by Mr. Joydeep Das

What the effects of Ordering in Index

SQL-Server-2008

SQL-Server-2005

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

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

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

nth Highest Salary in SQL Server 2005

16 Feb 2011 by Mohd_Ahmad

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

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

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

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

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

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

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

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

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

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

19 Feb 2010 by Md. Marufuzzaman

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

SQL-Server-2005

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

Record Count of Tables in SQL Server

26 Aug 2010 by tarun_j200

Also use either this one:SELECT OBJECT_NAME(SYSINDEXES.id), SYSINDEXES.rowcntFROM SYSINDEXES INNER JOIN SYSOBJECTS ON SYSINDEXES.id = SYSOBJECTS.idWHERE SYSINDEXES.indid

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

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

Return Comma Separated String using SQL

30 Jan 2013 by Yogesh Gulve

Return Comma Separated String using SQL

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

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

Selective masking in SQL server 2005

5 Jan 2012 by Jobless Creature

Masking selected characters in SQL, hiding few characters

SQL-Server-2005

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

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

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

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

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

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

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

Split function in SQL

30 Jul 2013 by Gauri Chodanker

SQL function in SQL

SQL-Server-2005

SQL-server-2000

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