Click here to Skip to main content
15,868,016 members
Everything / Database Development / SQL Server / SQL Server 2005

SQL Server 2005

SQL-Server-2005

Great Reads

by Wonde Tadesse
This tip/trick helps to set a default value for blob data type such as Image, VarBinary.
by Steve Krile
Fully AJAX-enabled user control used to select names from a database in a drop-down format.
by Mubin M. Shaikh
Create time dimension with 24 hour plus values and time buckets in your data warehouse
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

by Narapareddy Shyamprasad
SQL Dynamic Pivots
by Luca Astolfi
User defined procedure for make an HTML table from T-SQL Select statment
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!
by Ebenezar John Paul
An Ideal Code Review Checklist that applies for most programming languages

All Articles

Sort by Score

SQL Server 2005 

15 Jan 2013 by Wonde Tadesse
This tip/trick helps to set a default value for blob data type such as Image, VarBinary.
5 Sep 2013 by Mubin M. Shaikh
Create time dimension with 24 hour plus values and time buckets in your data warehouse
13 Mar 2012 by Tecfield
This tip shows how to rename a database and its MDF and LDF files in SQL Server.
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.
16 Mar 2012 by Anurag Gandhi
A Query to copy an individual table data between databases
8 Jul 2010 by Md. Marufuzzaman
How easy to read a file without using of the seven extended stored procedures.
19 Feb 2010 by Md. Marufuzzaman
This is good... You can also consider this link.[^]
28 Oct 2010 by Sunasara Imdadhusen
SQL Server 2005 : Generate Script with Data from Database – Database Publishing Wizard
25 Nov 2010 by Sivaraman Dhamodharan
How do you use the Template explorer and assign parameter values.
23 Feb 2012 by Mahmud Hasan
How to use variable in SELECT TOP SQL command
20 Dec 2012 by Wendelius
This tip describes how to delete duplicate rows from a table that doesn't have a key.
17 Sep 2012 by aasim abdullah
Get all indexes list in detail, with their involved columns
18 Dec 2015 by Md. Marufuzzaman
This tip shows you an easy way to split Microsoft SQL Server table row data.
17 Feb 2011 by John Gathogo
Catastrophic results associated with unqualified use of columns belonging to tables referenced on main query in a subquery
17 Aug 2012 by ManojKumar19
Update row column with another row column in same table SQL only if it is null.
13 Dec 2012 by Paw Jershauge
MS SQL system views, mapped over version.
20 Sep 2012 by Sreedhar Puligundla
Simple steps to create SQL joins by using SQL Server 2008 Management Studio
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.
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.
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
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...
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...
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); --...
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
18 Jul 2011 by RaviRanjanKr
A tip enable you to learn how to use Column_Index instead of Column Name with Order by clause
17 Aug 2011 by bmckuhen
4 Feb 2012 by chetan virkar
Uploading Zip File in ASP.NET
27 Nov 2012 by Aadhar Joshi
Creating job in sql server which automates taking backup of all stored procedures and functions in physical drive.
19 Jun 2015 by ss9o9o9o
Transferring backup files from a remote SQL Server and Dynamic SQL Connection with Restore utility.
22 Feb 2016 by Sifiso W Ndlovu
This tip illustrates a workaround to SQL Server pivoting on variable character data types.
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
4 Jan 2011 by thatraja
By this way tooHow to Shrink SQL Server database log files?[^]
8 Feb 2011 by Irfan Baig
String splitting/tokenizing using T-SQL user defined function.
23 Feb 2011 by Kale Yogesh
SHRINKFILE and TRUNCATE Log File in SQL Server 2008
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 ...
6 Nov 2011 by Ravi Tuvar
Installing SQL Server 2005 and 2008 in the same computer.
10 Feb 2012 by Ramanujam Shankar
Find missing date periods from a table.
25 Feb 2012 by e-Lopes
How to concatenate many rows into a single text string using SQL Server 2008.
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 ??
19 Apr 2013 by aasim abdullah
Automatic query execution at every instance startup.
17 Jan 2013 by Tharaka MTR
This article describes the easiest way to create and process delimited row using SQL XQuery.
24 Apr 2013 by Surender Singh (CodeToastDev)
Custom StringPadding functions for 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.
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.
21 Jan 2016 by smirson
This is an alternative for "String splitting/tokenizing using T-SQL user defined function."
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) ) ) )
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...
26 Aug 2010 by Pranay Rana
Number of different ways to get total number of rows from tables
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...
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)...
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...
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
13 Jan 2012 by Robby Tendean
How to do SQL atomic operations on UPDATE and DELETE syntax.
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
25 Feb 2012 by Sanjay K. Gupta
How to concatenate many rows into a single text string using SQL Server 2008
20 Jul 2012 by Mr. Joydeep Das
What the effects of Ordering in Index
25 Jul 2012 by Durgesh Swarnkar
Getting the count of records in a particular column.
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
3 Jan 2013 by Levente Kupás
CLR-addon for T-SQL DATE transformation and Split-String
4 Jan 2013 by RahulAJoshi
Grouping dynamically on different columns in single query
9 Oct 2013 by Matthew Taylor
How to remove Microsoft SQL Server triggers associated with a custom schema.
8 Mar 2012 by Ramanujam Shankar
Using COALESCE function - concatenate many rows into a single text string output in SQL Server.
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:
21 May 2014 by Arvind Singh Baghel
Import CSV or txt File Into SQL Server Using Bulk Insert.
9 Apr 2016 by NightWizzard
Handling for hex expressions and the trailing '}'
11 Nov 2013 by Rajat-Indiandotnet
Find column or Text in entire stored procedure
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
16 Aug 2011 by thatraja
The below query can be used to get the record count of all tables in the current database.
8 Jun 2015 by Diluka Wittahachchige
Creating SQL Server database project use in Visual Studio 2012 and maintain versions use in TFS
12 Nov 2013 by Jobless Creature
CTE To find all the related nodes in a hierarcy
25 Aug 2016 by Gene R. Browning
Generate random dates (and times if you like) for your test data environment.
3 Dec 2014 by Atul_Kapoor
Perform SQL Server Instance health check simultaneously on more than one server using Powershell
24 Apr 2012 by mkader
SQL scripts to insert File to BLOB field and export BLOB to File
18 Nov 2014 by qadirv
Quick C# console application to encrypt all your database stored procedures
29 Mar 2013 by Nandakishore G N
Example For Gridview RowCommand Event
20 Feb 2010 by Md. Marufuzzaman
Get accurate age by date using MS SQL Server
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[^]
24 Jan 2011 by Sunasara Imdadhusen
Clear the transaction Log if you are facing a low disk space
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...
23 Dec 2015 by Md. Marufuzzaman
A simple tip/trick to get rows on n’th maximum number from a table
6 Jun 2013 by Nelson Kosta Souto
Find text in Stored Procedures, View, Trigger, and Function.
20 Sep 2016 by GhostHost
Automatic creation of non clustered indexes using system SQL entities
23 Jul 2012 by Paul M Simpson
Expanding a string by inserting alternating single spaces between characters
5 Jun 2015 by Dathuraj Pasarge
Know whether your databases are backed up successfully in the last 24 hours or not !!!
13 Jun 2012 by headshot9x
How to create ShoppingCart using Gridview in asp.net with code VB
10 Aug 2011 by Wendelius
Or simply:exec msdb..sp_help_jobsp_help_job (Transact-SQL)[^]
21 Jun 2010 by Pranay Rana
Getting a SQL Server RowCount Without doing a Table Scan
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.
15 Jan 2012 by Dhol Gaurav
Get Tables List With Its Dependent Tables Names
10 Nov 2013 by Rajat-Indiandotnet
A unique feature of SQL SERVER Loop with Go statement
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.
18 Dec 2015 by Md. Marufuzzaman
A simple example of Microsoft SQL Server Pad String
27 Mar 2012 by SA - Hibbert
Return all records between two dates that include time in SQL.
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...
11 Feb 2012 by Jobless Creature
HH:MM Time format addition
2 Oct 2012 by aasim abdullah
A simple query to get all databases size on a given instance.