T-SQL Programming

Home SQL .NET Exchange Opinion Blogs Forums About

SQL Home > T-SQL Programming

by Anith Sen | 16 September 2008 |

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

Concatenating Row Values in Transact-SQL

by Anith Sen | 31 July 2008 |

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

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

Missing Date Ranges- the Sequel

by Alex Kozak | 16 June 2008 |

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

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

Identity Columns

by Nigel Rivett | 12 May 2008 |

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

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

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

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

SQL String User Function Workbench: part 1

by Robyn Page and Phil Factor | 15 April 2008 |

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

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

TSQL String Array Workbench

by Robyn Page and Phil Factor | 16 March 2008 |

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

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

Close Those Loopholes: Stress-Test those Stored Procedures

by Alex Kuznetsov | 03 February 2008 |

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

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

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

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

Find Missing Date Ranges in SQL

by Alex Kozak | 11 October 2007 |

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

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

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

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

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

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

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

Crosstab Pivot-table Workbench

by Robyn Page and Phil Factor | 22 July 2007 |

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

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

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

XML Jumpstart Workbench

by Robyn Page and Phil Factor | 27 June 2007 |

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

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

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

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

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

Reading and Writing Files in SQL Server using T-SQL

by Phil Factor | 10 April 2007 |

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

Creating cross tab queries and pivot tables in SQL

by Keith Fletcher | 27 March 2007 |

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

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

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

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

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

SQL Server Excel Workbench

by Robyn Page and Phil Factor | 06 February 2007 |

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

To SP or not to SP in SQL Server: an argument for stored procedures

by Adam Machanic | 06 June 2006 |

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

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

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

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

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

To SP or not to SP in SQL Server

by Douglas Reilly | 11 April 2005 |

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