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 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 nobody seems to agree on the best way of doing it… Read more
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
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
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 there are now ways of simulating concurrency during the test process.… Read more
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 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
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. Alex continues his excellent series, by showing how to catch those subtle problems.
… Read more
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 from the past.… Read more
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 'try it and see...'… Read more
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
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 counts?
… Read more
Login Triggers were quietly introduced in SP2 to tighten up the security features of SQL Server to comply with the latest industry standards for security. But you can meet a lot of the security requirements even without them! … Read more
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
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
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 and Alex continue their series of articles on 'Unit Testing' database development work with some examples of unit testing stored procedures.… Read more
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 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 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