A service from Red Gate
Home SQL .NET Exchange Opinion Blogs Forums Newsletters About
Sign in  | Join  | Help
SQL Home  >  T-SQL Programming
Joe Celko Avoiding the EAV of Destruction
by Joe Celko | 18 June 2009 |  7 comments |
A forum posting, from someone who wanted a better solution to the common problem of handling global settings in a database, leads Joe Celko into a fascinating discussion of the ramifications of the various solutions. Read more...
Robert Sheldon XML Data Modification Language Workbench
by Robert Sheldon | 28 April 2009 |  7 comments |
XML Data Modification Language (XML DML) allows you to modify and update XML data. When working with SQL Server Databases, this is the most efficient way to modify elements in an XML column, yet the techniques of using XML-DML have not been well, and simply,... Read more...
Phil Factor CLR Assembly RegEx Functions for SQL Server by Example
by Phil Factor | 15 April 2009 |  3 comments |
Phil Factor presents a simple CLR Assembly to allow SQL Server users to access all the powerful RegEx library methods in .NET. In the spirit of 'get something up and running', he takes a hands-on approach and demonstrates that you needn't be a C# hotshot or Visual... Read more...
Joe Celko Median Workbench
by Joe Celko | 05 April 2009 |  11 comments |
SQL Server database engine doesn't have a MEDIAN() aggregate function. This is probably because there are several types of median, such as statistical, financial or vector medians. Calculating Medians are essentially a row-positioning task, since medians are the... Read more...
Alex Kuznetsov Brain Teaser for Pi Day
by Alex Kuznetsov | 02 March 2009 |  12 comments |
Alex has come up with a great idea for Pi Day. We should celebrate by trying to come up with a way, in SQL, of generating a an accurate value for Pi. If only Archimedes had possessed a laptop, his work would have been easier! Read more...
Joe Celko Divided We Stand: The SQL of Relational Division
by Joe Celko | 17 February 2009 |  3 comments |
Businesses often require reports that require more than the classic set operators. Surprisingly, a business requirement can often be expressed neatly in terms of the DIVISION relationship operator: How can this be done with SQL Server? Joe Celko opens up the 'Manga... Read more...
András Belokosztolszki Removing Duplicates from a Table in SQL Server
by András Belokosztolszki | 11 February 2009 |  16 comments |
Sometimes, in SQL, it is the routine operations that turn out to be the trickiest for a DBA or developer. The cleaning up, or de-duplication, of data is one of those. András runs through a whole range of methods and tricks, and ends with a a fascinating technique... Read more...
Phil Factor The TSQL of Text Files
by Phil Factor | 19 January 2009 |  8 comments |
Phil returns to the old subject of 'Getting text-based data in and out of SQL Server'. He shows various easy ways of getting a file listings of directories from the file system, shows how one can access the Shell automation Objects, and demonstrates several ways of... Read more...
Joe Celko Temporal Data Techniques in SQL
by Joe Celko | 18 January 2009 |  3 comments |
In the first part of this series on Temporal Data, Joe explained how it is that the Common Era calendar is irregular and mentioned that, although there are ANSI/ISO Standards for temporal operations in SQL, every vendor has something different. Now, he discusses... Read more...
Joe Celko Temporal Datatypes in SQL Server
by Joe Celko | 16 December 2008 |  43 comments |
In the first of a series of articles on the tricks of tackling temporal data in SQL, Joe Celko discusses SQL's temporal data types and agonizes over the fact that, although there are ANSI/ISO Standards for temporal operations in SQL, every vendor has something... Read more...
Joe Celko Unique Experiences!
by Joe Celko | 18 November 2008 |  17 comments |
You'd have thought that a unique constraint was an easy concept - Not a bit of it; it can cause a lot of subtle problems in database designs. Joe Celko goes over the ground of unique keys, primary Keys, foreign keys and constraints. Read more...
Robyn Page and Phil Factor SQL Server Matrix Workbench
by Robyn Page and Phil Factor | 15 November 2008 |  14 comments |
In this workbench, Robyn Page and Phil Factor decide to tackle the subject of Matrix handling and Matrix Mathematics in SQL. They maintain that 'One just needs a clear head and think in terms of set-based operations' Read more...
Joe Celko Constraint Yourself!
by Joe Celko | 26 October 2008 |  19 comments |
In his first article for Simple-Talk, Joe Celko demystifies the use of Constraints, and points out that they are an intrinsic part of SQL and are a great way of ensuring that a business rule is done one way, one place, one time. Almost all database programmers will... Read more...
Alex Kozak The Bejeweled Puzzle in SQL
by Alex Kozak | 09 October 2008 |  42 comments |
Alex Kozak provides another SQL puzzle to hone your SQL Skills with. Read more...
Anith Sen Faking Arrays in Transact SQL
by Anith Sen | 16 September 2008 |  28 comments |
It is a simple routine that we all need to use occasionally; parsing a delimited list of strings in TSQL. In a perfect relational world, it isn't necessary, but real-world data often comes in a form that requires one of the surprising variety of routines that Anith... Read more...
Anith Sen Concatenating Row Values in Transact-SQL
by Anith Sen | 31 July 2008 |  42 comments |
It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate'... Read more...
William Brewer JSON and other data serialization languages
by William Brewer | 18 July 2008 |  4 comments |
The easiest way to speed up an Ajax application is to take out the 'X' and use JSON rather than XML. Of course, it isn't that simple, as William Brewer explains, but JSON, and YAML, are fascinating solutions to the old problem of transferring complex data between... Read more...
Alex Kozak Missing Date Ranges- the Sequel
by Alex Kozak | 16 June 2008 |  38 comments |
Alex Kozak returns with another Date puzzle. A readers question gives Alex the inspiration to see if is possible to list unused date ranges in one Select statement. Read more...
Alex Kuznetsov Close these Loopholes - Reproduce Database Errors
by Alex Kuznetsov | 23 May 2008 |  3 comments |
This is the final part of Alex's ground-breaking series on unit-testing Transact-SQL code. Here, he shows how you can test the way that your application handles database-related errors such as constraint-violations or deadlocks. With a properly-constructed... Read more...
Nigel Rivett Identity Columns
by Nigel Rivett | 12 May 2008 |  66 comments |
When Nigel Rivett takes us on a tour of the apparently innocuous subject of Identity Columns in TSQL, even the seasoned programmer is due for one or two surprises. Read more...
William Brewer SQL Code Layout and Beautification
by William Brewer | 11 May 2008 |  30 comments |
William Brewer takes a look at the whole topic of SQL Code layout and beautification, an important aspect to SQL programming style. He concludes that once you are tired of laying SQL out by hand, you had better choose a tool with plenty of knobs to twiddle, because... Read more...
Robyn Page and Phil Factor SQL String User Function Workbench: part 2
by Robyn Page and Phil Factor | 28 April 2008 |  3 comments |
In which Robyn and Phil continue with their popular series on TSQL String User-functions. In this final episode, they pull together the themes from their TSQL String Array Workbench and String User Function workbench, to provide a simple TSQL string-handling package. Read more...
Alex Kuznetsov The Case of the Skewed Totals
by Alex Kuznetsov | 15 April 2008 |  4 comments |
Even when your code tests out perfectly in the standard test cell, you can experience errors in the real production setting where several processes are hitting the database at once, in unpredictable ways. You shouldn’t, of course, let it get that far, because... Read more...
Robyn Page and Phil Factor SQL String User Function Workbench: part 1
by Robyn Page and Phil Factor | 15 April 2008 |  26 comments |
Robyn and Phil go back to basics and hammer out some basic String-handling User Functions in TSQL, based on Python examples. Plenty of sample code, and TSQL programming tricks. Read more...
Robyn Page and Phil Factor Getting HTML Data: Workbench
by Robyn Page and Phil Factor | 27 March 2008 |  10 comments |
Robyn and Phil start their investigation into XHTML by showing how to use TSQL to parse it to extract data, and demonstrate how to turn an XHTML table into a SQL Server Table! Read more...
Robyn Page and Phil Factor TSQL String Array Workbench
by Robyn Page and Phil Factor | 16 March 2008 |  10 comments |
Robyn and Phil show how to use XML-based arrays to make string handling easier in SQL Server 2005/2008, and illustrate the techniques with some useful functions, one of which copies the PHP str_Replace function. Read more...
Alex Kozak The 'Last Seven Days' puzzle
by Alex Kozak | 12 March 2008 |  40 comments |
The best SQL puzzles come from real exeriences in the workplace. Here, Alex Kozak describes how he took on a task that looked simple for a while. Then he realised that he's stumbled over an excellent puzzle for Simple-Talk. Read more...
Alex Kuznetsov Close Those Loopholes: Stress-Test those Stored Procedures
by Alex Kuznetsov | 03 February 2008 |  7 comments |
You can write a stored procedure that tests perfectly in your regression tests. You will hand it to the tester in the smug certainty that it is perfectly bug-free. Dream on, for without stress-testing you could easily let some of the most unpleasant bugs through.... Read more...
Alex Kozak Numeral Systems and Numbers Conversion in SQL
by Alex Kozak | 10 December 2007 |  16 comments |
Numeral systems can be fascinating. In everyday programming, we are now becoming quite insulated from the need to convert between binary numbers and their representation, so it is a novelty to try out ways of doing it in SQL, and experiment with other number systems... Read more...
Robyn Page and Phil Factor TSQL Regular Expression Workbench
by Robyn Page and Phil Factor | 27 November 2007 |  22 comments |
Robyn and Phil start by writing a gentle introduction to using Regular expressions for validation, data cleaning and data import in TSQL, and finally end up with a routine for doing google-style searches that show the context of hits. It's all done in the spirit of... Read more...
Robyn Page and Phil Factor Importing Text-based data: Workbench
by Robyn Page and Phil Factor | 23 October 2007 |  29 comments |
Robyn and Phil return with some fresh ideas about how to import text files into SQL Server, without resorting to DTS or SSIS scripting. They go on to show how much can be done in TSQL Read more...
Alex Kozak Find Missing Date Ranges in SQL
by Alex Kozak | 11 October 2007 |  5 comments |
Often, the quickest way to determine whether you have missing data in a table such a ledger or journal is to see if there are gaps in the dates where one wouldn't expect them. But how do you do that in an emergency, particularly in a large table, when every minute... Read more...
William Brewer Quantifying Text differences in TSQL
by William Brewer | 20 September 2007 |  3 comments |
In TSQL there is a limit to the way you can compare text strings. They're either equal or not. Sooner or later, usually when cleaning data, something more subtle is required! Read more...
Pop Rivett Pop Rivett and the FTP directory
by Pop Rivett | 19 September 2007 |  5 comments |
Dr Pop Rivett diagnoses URL-Aphasia in an anxious and exhausted patient and divulges a technique of synchronising a local directory with a remote FTP directory, all in TSQL! Read more...
Alex Kuznetsov and Alex Styler Close These Loopholes - Testing Database Modifications
by Alex Kuznetsov and Alex Styler | 02 September 2007 |  6 comments |
In the latest in their popular series on 'Unit Testing' database development work , Alex K and Alex S give some examples of unit testing Database Modifications Read more...
Alex Kozak The Puzzle of 'Rating Decomposition'
by Alex Kozak | 29 August 2007 |  9 comments |
When reading rating information, how do you you knew how many points each separate voter gave if you only know the average rating and the number of votes? Well, you might be surprised to learn that you can figure it out using SQL Read more...
Alex Kuznetsov and Alex Styler Close those Loopholes - Testing Stored Procedures
by Alex Kuznetsov and Alex Styler | 20 August 2007 |  16 comments |
Alex and Alex continue their series of articles on 'Unit Testing' database development work with some examples of unit testing stored procedures. Read more...
Alex Kuznetsov Close These Loopholes in Your Database Testing
by Alex Kuznetsov | 31 July 2007 |  15 comments |
Alex starts of a series of articles on 'Unit Testing' your database development work. He starts off by describing five simple rules that make all the difference. Read more...
Remi Gregoire RBAR: 'Row By Agonizing Row'
by Remi Gregoire | 26 July 2007 |  14 comments |
Remi Gregoire describes the vice of RBAR Database Programming, 'Row By Agonising Row', and illustrates how the effect of RBAR can sometimes be felt only years after an application is released, when the database supporting the application grows. Read more...
Robyn Page and Phil Factor Crosstab Pivot-table Workbench
by Robyn Page and Phil Factor | 22 July 2007 |  37 comments |
Robyn and Phil turn their attention to the bedrock of management reporting, the Pivot Table. Under Phil's 'wild man' influence, they end up with some rather radical ideas. Read more...
Rodney Landrum Temporarily Changing an Unknown Password of the sa Account
by Rodney Landrum | 10 July 2007 |  21 comments |
You are asked for the sa password for a SQL Server in order to perform a software upgrade. You, the DBA, don't know the password and it's not documented. Rodney Landrum provides a way out of this dilemma, demonstrating two techniques for temporarily changing the... Read more...
Robyn Page and Phil Factor RSS Newsfeed Workbench
by Robyn Page and Phil Factor | 06 July 2007 |  4 comments |
Robyn and Phil decide to build an RSS newsfeed in TSQL, using the power of SQL Server's XML. Read more...
Robyn Page and Phil Factor XML Jumpstart Workbench
by Robyn Page and Phil Factor | 27 June 2007 |  30 comments |
In which Robyn and Phil decide that the best way of starting to learn XML is to jump in and take a ride around the block. Read more...
Robyn Page and Phil Factor Process Delegation Workbench
by Robyn Page and Phil Factor | 07 June 2007 |  16 comments |
Robyn Page and Phil Factor show a useful technique for delegating SQL Server processes to a 'Back-Office', by using 'user-defined Alerts'. Read more...
Adam Machanic A Primer on Managing Data Bitemporally
by Adam Machanic | 10 May 2007 |  6 comments |
In systems that require, for auditing purposes, advanced logging and reproducibility of reports between runs, a straightforward update, insert, or delete may be counter-productive. In such circumstances, a bitemporal model is necessary. Adam Machanic explains how it... Read more...
Robyn Page and Phil Factor SQL Server Grouping Workbench
by Robyn Page and Phil Factor | 26 April 2007 |  23 comments |
A gentle lesson about GROUP BY on the Nursery Slopes develops gradually into a wild ride off-piste amongst the pine-trees. Read more...
Eric Brown Troubleshooting with Dynamic Management Views
by Eric Brown | 12 April 2007 |
If you work with SQL Server 2000, then you know how painful it is to triage a server that has "gone astray". Eric Brown thinks that the new Dynamic Management Views in SQL 2005 are a big step forward. Read more...
Phil Factor Reading and Writing Files in SQL Server using T-SQL
by Phil Factor | 10 April 2007 |  29 comments |
SQL Server provides several "standard" techniques by which to read and write to files but, just occasionally, they aren't quite up to the task at hand – especially when dealing with large strings or relatively unstructured data. Phil Factor provides some T-SQL... Read more...
Keith Fletcher Creating cross tab queries and pivot tables in SQL
by Keith Fletcher | 27 March 2007 |  63 comments |
For those times when you absolutely, positively got to perform a cross tab query in SQL, Keith Fletcher's T-SQL stored procedure will allow you to do it "on the fly". You can add it to your database and start cross tabbing immediately, without any further setup or... Read more...
Pop Rivett Pop Rivett and the Case of the Rogue SPIDs
by Pop Rivett | 22 March 2007 |  5 comments |
A process in a complex database occasionally, and apparently randomly, manages to put table locks on vital tables. Several applications are brought to a complete halt. Armed with a T-SQL stored procedure, a violin and a keen investigative spirit, Pop Rivett tracks... Read more...
Robyn Page and Phil Factor The Helper Table Workbench
by Robyn Page and Phil Factor | 16 March 2007 |  27 comments |
Cursors and iterations are both renowned for slowing down Transact SQL code, but sometimes seem unavoidable. In this workbench, Robyn Page and Phil Factor demonstrate some set-based techniques for string manipulation and time interval-based reporting, which use... Read more...
Phil Factor Writing to Word from SQL Server
by Phil Factor | 06 March 2007 |  19 comments |
Never a man to walk away from a challenge, Phil Factor set himself the task of automating the production of Word reports from SQL Server, armed only with OLE automation and a couple of stored procedures. Read more...
Robyn Page and Phil Factor SQL Server Security Workbench Part 1
by Robyn Page and Phil Factor | 06 March 2007 |  19 comments |
Robyn Page and Phil Factor present practical T-SQL techniques for controlling access to sensitive information within the database, and preventing malicious SQL injection attacks. Read more...
Grant Fritchey SQL Server Error Handling Workbench
by Grant Fritchey | 20 February 2007 |  25 comments |
Grant Fritchey steps into the workbench arena, with an example-fuelled examination of catching and gracefully handling errors in SQL 2000 and 2005, including worked examples of the new TRY..CATCH capabilities. Read more...
Robyn Page and Phil Factor SQL Server Excel Workbench
by Robyn Page and Phil Factor | 06 February 2007 |  114 comments |
The need to produce Excel reports from SQL Server is very common. Here, Robyn Page and Phil Factor present practical techniques for creating and manipulating Excel spreadsheets from SQL Server, using linked servers and T-SQL. The pièce de résistance is a stored... Read more...
Adam Machanic To SP or not to SP in SQL Server: an argument for stored procedures
by Adam Machanic | 06 June 2006 |  27 comments |
A seemingly never-ending battle in online database forums involves the question of whether or not database application development should involve the use of stored procedures. Read more...
Arthur Fuller Intelligent Database Design Using Hash Keys
by Arthur Fuller | 17 February 2006 |  11 comments |
Your application may require an index based on a lengthy string, or even worse, a concatenation of two strings, or of a string and one or two integers. In a small table, you might not notice the impact. But suppose the table of interest contains 50 million rows?... Read more...
Arthur Fuller A case for canned SQL
by Arthur Fuller | 18 January 2006 |  6 comments |
Like a Phoenix, the dynamic SQL versus canned procedures and user functions argument has resurfaced on the SQL newsgroups. Many of the proponents of the dynamic argument are web or Access developers, or developers of some other front end. Arthur takes another look... Read more...
Phil Factor Cursors and embedded SQL
by Phil Factor | 19 December 2005 |  8 comments |
The database mole turns his attention to take a look at cursors and embedded SQL Read more...
Douglas Reilly Managing database changes
by Douglas Reilly | 11 May 2005 |  1 comment |
Stored procedures are not the be-all and end-all of managing changes in a database, but they can be very important tools in allowing changes to be made to a database whilst not breaking client applications... Read more...
Douglas Reilly To SP or not to SP in SQL Server
by Douglas Reilly | 11 April 2005 |  7 comments |
There have been a number of discussions about whether to use SPs or not, often shedding more heat than light, Doug Reilly takes a look at the debate. Read more...
  Learn SQL Server (34 articles)
  SQL Training (6 articles)

  Database Administration (45 articles)
  SQL Server 2005 (18 articles)

  T-SQL Programming (61 articles)
  Performance (12 articles)

  Backup and Recovery (17 articles)
  SQL Tools (41 articles)

  SQL Home

  Learn SQL Server (34 articles)

  SQL Training (6 articles)

  Database Administration (45 articles)

  SQL Server 2005 (18 articles)

  T-SQL Programming (61 articles)

  Performance (12 articles)

  Backup and Recovery (17 articles)

  SQL Tools (41 articles)

Phil Factor
To Boldly Ask IT for Development Work
 Phil has always been mystified by the way that, in Science-Fiction films, the crew of space-ships are able to... Read more...



 View the blog
Tony Davis The Fall and Rise of Log Shipping
 When Database Mirroring was introduced in SQL Server 2005, it seemed reasonable to assume that log shipping would...  Read more...


 View the blog
Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 Database design and implementation is the cornerstone of any data centric project (read 99.9% of... Read more...

Beginning SQL Server 2005 Reporting Services Part 2
 Continuing his in-depth tour of SQL Server 2005 Reporting Services, Steve Joubert demonstrates the most... Read more...

SQL Server Full Text Search Language Features
 SQL Full-text Search (SQL FTS) is an optional component of SQL Server 7 and later, which allows fast... Read more...

Executing SSIS Packages
 Nigel Rivett demonstrates how to execute all SSIS packages in a given folder using either an SSIS... Read more...

Over 150,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.

Join Simple Talk


Privacy policy | Terms and conditions | ©2005-2009 Red Gate Software
Site map | Become an author | Contact us