Click here to monitor SSC
A service from Red Gate
Home SQL .NET SysAdmin Opinion Blogs Forums Books
Sign in  | Join  | Help
SQL Home  >  T-SQL Programming
Jeremiah Peschka SQL Server Functions: The Basics
by Jeremiah Peschka | 10 November 2011 |  11 comments |
SQL Server's functions are a valuable addition to TSQL when used wisely. Jeremiah provides a complete and comprehensive guide to scalar functions and table-valued functions, and shows how and where they are best used. Read more...
William Brewer Database Source Control - The Cribsheet
by William Brewer | 08 November 2011 |  3 comments |
As part of our long-running Cribsheet series, we asked William to come up with a brief summary of what was involved in bringing database development work under source control. What are the advantages it brings, and are there disadvantages? Read more...
Joe Celko Voting Paradoxes: a SQL Stumper
by Joe Celko | 28 October 2011 |  9 comments |
Voting systems can become very complex, and some of them are easy to manipulate by tactical voting. Joe takes a couple of voting systems and wonders how you would implement them in SQL. He's even more curious as to how you, the reader, would do so. Read more...
Joe Celko Mimicking Network Databases in SQL
by Joe Celko | 26 September 2011 |  7 comments |
Unlike the hierarchical database model, which created a tree structure in which to store data, the network model formed a generalized 'graph' structure that describes the relationships between the nodes. Nowadays, the relational model is used to solve the problems... Read more...
Phil Factor Temporary Tables in SQL Server
by Phil Factor | 01 September 2011 |  30 comments |
Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if... Read more...
Joe Celko Mimicking Magnetic Tape in SQL
by Joe Celko | 17 August 2011 |  21 comments |
The sequential nature of early data storage devices such as punched card and magnetic tape once forced programmers to devise algorithms that made the best of sequential access. These ways of doing data-processing have become so entrenched that they are still used in... Read more...
Phil Factor SQL Programmer's workshop
by Phil Factor | 05 August 2011 |
Phil Factor records, as closely as possible, the twists and turns of creating a SQL Server T-SQL stored procedure, describing the methods that work for him. Read more...
Solomon Rutzky CLR Performance Testing
by Solomon Rutzky | 21 July 2011 |  2 comments |
Are Common Language Runtime routines in SQL Server faster or slower than the equivalent TSQL code? How would you go about testing the relative performance objectively? Solomon Rutzky creates a test framework to try to answer the question and comes up with some... Read more...
Phil Factor How to develop TSQL Code
by Phil Factor | 24 June 2011 |  8 comments |
The basic texts for developing SQL code tend to leave unsaid the basic techniques for building routines such as stored procedures in T-SQL. Phil is well-known for his more lengthy and complex stored procedures, so we asked him to explain in more detail how he goes... Read more...
Joe Celko A Tale of Identifiers
by Joe Celko | 09 June 2011 |  22 comments |
Identifiers aren't locators, and they aren't pointers or links either. They are a logical concept in a relational database, and, unlike the more traditional methods of accessing data, don't derive from the way that data gets stored. Identifiers uniquely identify... Read more...
Joe Celko TIME Gentlemen please! The SQL Server temporal datatypes
by Joe Celko | 12 May 2011 |  12 comments |
If you are still using the old Sybase DateTime datatype, it is a good idea to move your code to the more standard datatypes that were introduced in SQL Server 2008. Joe Celko explains why, and walks through some of the history of the TSQL way of storing and... Read more...
Phil Factor PATINDEX Workbench
by Phil Factor | 12 May 2011 |  11 comments |
The PATINDEX function of SQL Server packs powerful magic, but it is easy to get it wrong. Phil Factor returns to the Workbench format to give a tutorial of examples, samples and cookbook ideas to demonstrate the ways that this underrated function can be of practical... Read more...
David Berry Performance Implications of Parameterized Queries
by David Berry | 28 April 2011 |  16 comments |
Why don't we emphasize the huge advantages of parameterized queries over ad-hoc queries in SQL Server? There is a severe impact on resources and performance from repeatedly using similar ad-hoc queries, instead of reusing the existing query plans. David Berry shows... Read more...
Joe Celko SQL Server CASE Law
by Joe Celko | 03 March 2011 |  11 comments |
SQLs CASE expressions can be powerful magic, but can trap the unwary who are used to the more familiar CASE statements of procedural languages. Read more...
Joe Celko Look-up Tables in SQL
by Joe Celko | 01 February 2011 |  27 comments |
Lookup tables can be a force for good in a relational database. Whereas the 'One True Lookup Table' remains a classic of bad database design, an auxiliary table that holds static data, and is used to lookup values, still has powerful magic. Joe Celko explains.... Read more...
Nick Harrison Database Refactoring
by Nick Harrison | 01 February 2011 |  6 comments |
Although the methodology of refactoring code has been adopted enthusiastically, the same has not really been the case with databases. Nick argues that the reason could lie in the extent of the task of unpicking complex databases systems sufficiently to make them... Read more...
Robert Sheldon Data Conversion in SQL Server
by Robert Sheldon | 06 January 2011 |  9 comments |
Most of the time, you do not have to worry about implicit conversion in SQL expressions, or when assigning a value to a column. Just occasionally, though, you'll find that data gets truncated, queries run slowly, or comparisons just seem plain wrong. Robert explains... Read more...
Sebastian Meine and Dennis Lloyd SQL Server Unit Testing with tSQLt
by Sebastian Meine and Dennis Lloyd | 06 January 2011 |  4 comments |
When one considers the amount of time and effort that Unit Testing consumes for the Database Developer, is surprising how few good SQL Server Test frameworks are around. tSQLt , which is open source and free to use, is one of the frameworks that provide a simple way... Read more...
Joe Celko BIT of a Problem
by Joe Celko | 04 January 2011 |  13 comments |
The BIT data type is an awkward fit for a SQL database. It doesn't have just two values, and it can do unexpected things in expressions. What is worse, it is a flag rather than a predicate, and so its overuse, along with bit masks, is a prime candidate for being... Read more...
Phil Factor The Parodist: A SQL Server Application
by Phil Factor | 20 December 2010 |  6 comments |
Every year, we ask Phil Factor to celebrate the holiday season with an article on SQL Server Programming that is fun. This year, he responded with 'The Parodist'. This is a SQL Server application, the like of which I doubt if you've seen before. Read more...
Alex Kuznetsov Tuning SQL Queries with the Help of Constraints
by Alex Kuznetsov | 07 December 2010 |  5 comments |
The use of constraints is a valuable way of improving query performance as well as maintaining the integrity of the data, but this is, inevitably, a trade-off: The data uses up more storage, and the modifications are slower and more difficult. In SQL Programming,... Read more...
Alex Kuznetsov Modifying Contiguous Time Periods in a History Table
by Alex Kuznetsov | 25 November 2010 |
Alex Kuznetsov is credited with a clever technique for creating a history table for SQL that is designed to store contiguous time periods and check that these time periods really are contiguous, using nothing but constraints. This is now increasingly useful with the... Read more...
Joe Celko Contiguous Time Periods
by Joe Celko | 22 November 2010 |  8 comments |
It is always better, and more efficient, to maintain referential integrity by using constraints rather than triggers. Sometimes it is not at all obvious how to do this, and the history table, and other temporal data tables, presented problems for checking data that... Read more...
Phil Factor Consuming JSON Strings in SQL Server
by Phil Factor | 15 November 2010 |  17 comments |
It has always seemed strange to Phil that SQL Server has such complete support for XML, yet is completely devoid of any support for JSON. In the end, he was forced, by a website project, into doing something about it. The result is this article, an iconoclastic romp... Read more...
Alex Kuznetsov Defensive Error Handling
by Alex Kuznetsov | 28 October 2010 |  1 comment |
TRY…CATCH error handling in SQL Server has certain limitations and inconsistencies that will trap the unwary developer, used to the more feature-rich error handling of client-side languages such as C# and Java. In this article, abstracted from his excellent new... Read more...
Joe Celko State Transition Constraints
by Joe Celko | 08 October 2010 |  8 comments |
Data Validation in a database is a lot more complex than seeing if a string parameter really is an integer. A commercial world is full of complex rules for sequences of procedures, of fixed or variable lifespans, Warranties, commercial offers and bids. All this... Read more...
Greg Larsen Parameter Sniffing
by Greg Larsen | 20 September 2010 |  2 comments |
If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you... Read more...
Auke Teeninga Minesweeper in T-SQL
by Auke Teeninga | 02 September 2010 |  41 comments |
Whatever happened to the idea that programming in TSQL can be fun? A Simple-Talk reader contributes an article to remind us all that there is more to TSQL than wrestling with DMVs and pumelling recalcitrant correlated subqueries. Read more...
Joe Celko The DIS-Information Principle: A Splitting Headache
by Joe Celko | 17 August 2010 |  15 comments |
You can easily re-factor bad DML code, but if a database design is wrong, you can do little to rescue the problem, even with expert queries. So what constitutes 'wrong RDBMS design? What are these errors that continually crop up? How can you recognise them and fix... Read more...
Louis Davidson and Tim Ford DMVs for Query Plan Metadata
by Louis Davidson and Tim Ford | 17 August 2010 |  3 comments |
Before you can tackle any performance issues with a working database, you need to know which queries to work on first: The ones that are taking the most time in total, and which are the most expensive in terms of cache, CPU and disk. Although SQL Server Management... Read more...
Mike Mooney When Database Source Control Goes Bad
by Mike Mooney | 05 August 2010 |  18 comments |
It is a question every development manager dreads; “So how does your company handle database changes?”. The reply usually masks a multitude of sins against all the canons of version control. Mike has seen most of these sins and, like the ancient mariner, is keen to... Read more...
Phil Factor SQL Server CRUD-Generation from System Views
by Phil Factor | 09 July 2010 |  10 comments |
If you are not keen on repetitive typing, you can still rapidly produce production-quality documented code by planning ahead and using Extended properties, and system views. Phil Factor explains, with some Scary SQL Read more...
Joe Celko Binary Trees in SQL
by Joe Celko | 22 June 2010 |  7 comments |
A number of hierarchies and networks are most convenently modelled as binary trees. So what is the best way of representing them in SQL? Joe discards the Nested Set solution in favour of surprisingly efficient solution based on the Binary Heap. Read more...
Alex Kuznetsov Developing Modifications that Survive Concurrency
by Alex Kuznetsov | 22 June 2010 |  9 comments |
You can create a database under the assumption that SQL looks after all the problems of concurrency. It will probably work fine under test conditions: Then, in the production environment, it starts losing data in subtle ways that defy repetition. It is every... Read more...
Joe Celko Book Review: Defensive Database Programming With SQL Server
by Joe Celko | 10 June 2010 |  1 comment |
It distils a great deal of practical experience; the writing of it was a considerable task; It packs in a great deal of information. Alex's book shows how to write robust database applications, and we can all learn from it. We took the book to a critic who never... Read more...
Robert Sheldon SQL Server APPLY Basics
by Robert Sheldon | 24 May 2010 |  9 comments |
One of the most interesting additions to SQL Server syntax in SQL Server 2005 was the APPLY operator. It allows several queries that were previously impossible. It is surprisingly difficult to find a simple explanation of what APPLY actually does. Rob Sheldon is the... Read more...
Robert Sheldon SQL Server CTE Basics
by Robert Sheldon | 29 April 2010 |  7 comments |
The CTE was introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table just wasn't suitable. For some reason, it can be difficult to grasp the techniques of using it. Well, that's before Rob Sheldon explained it all... Read more...
Phil Factor Exploring SQL Server table metadata with SSMS and TSQL
by Phil Factor | 29 April 2010 |  5 comments |
Phil shows how to start squeezing powerful magic from SSMS for doing a detailed exploration of the metadata of your routines and tables, in this third part to his series on exploring your database schema with SQL. Read more...
Alex Kuznetsov Basic Defensive Database Programming Techniques
by Alex Kuznetsov | 31 March 2010 |  2 comments |
We can all recognise good-quality database code: It doesn't break with every change in the server's configuration, or on upgrade. It isn't affected by concurrent usage, or high workload. In an extract from his forthcoming book, Alex explains just how to go about... Read more...
Joe Celko Celko's SQL Stumper: Eggs in one Basket
by Joe Celko | 29 March 2010 |  36 comments |
Joe Celko reveals the winner of his Easter Stumper: the puzzle of designing an apparently simple database to deal with the process of packing eggs into cartons. It wasn't quite as easy as it looked. Read more...
Joe Celko Procedural, Semi-Procedural and Declarative Programing Part II
by Joe Celko | 02 March 2010 |
SQL Server accommodates a whole range of programming styles and will even allow you to create code that is wholly procedural. Is a declarative approach inevitably better? Can it be difficult to maintain? Can you avoid the performance problems of procedural code by... Read more...
Phil Factor Exploring your database schema with SQL
by Phil Factor | 02 March 2010 |  13 comments |
In the second part of Phil's series of articles on finding stuff (such as objects, scripts, entities, metadata) in SQL Server, he offers some scripts that should be handy for the developer faced with tracking down problem areas and potential weaknesses in a database. Read more...
Joe Celko Procedural, Semi-Procedural and Declarative Programming in SQL
by Joe Celko | 15 February 2010 |  1 comment |
A lot of the time, the key to making SQL databases perform well is to take a break from the keyboard and rethink the way of approaching the problem; and rethinking in terms of a set-based declarative approach. Joe takes a simple discussion abut a problem with a UDF... Read more...
Paul Nielsen Switching rows and columns in SQL
by Paul Nielsen | 04 February 2010 |  9 comments |
When they use SQL Server, one the commoner questions that Ms Access programmers ask is 'Where's the TRANSFORM/PIVOT command? So how do you swap colums and rows in an aggregate table? Do you really need to use a CLR routine for this? Read more...
Phil Factor Finding Stuff in SQL Server Database DDL
by Phil Factor | 04 February 2010 |  8 comments |
You'd have thought that nothing would be easier than using SQL Server Management Studio (SSMS) for searching through the DDL for both the names and definitions of the structural metadata of your databases, for the occurrence of a particular string of letters. Not... Read more...
Phil Factor Laying out SQL Code
by Phil Factor | 21 January 2010 |  22 comments |
It is important to ensure that SQL code is laid out the best way for the team that has to use and maintain it. Before you work out how to enforce a standard, one has to work out what that standard should be for the application. So do you dive into detail or create... Read more...
Joe Celko Celko's SQL Stumper: The Class Scheduling Problem
by Joe Celko | 19 January 2010 |  19 comments |
What can we use in SQL instead of E. F. Codd's T theta operators for best-fit? Joe Celko returns with another puzzle that isn't new, in fact it already features “Swedish”, “Croatian” and “Colombian” solutions in chapter 17 of Joe's 'SQL for Smarties' book. These... Read more...
Fabiano Amorim 13 Things You Should Know About Statistics and the Query Optimizer
by Fabiano Amorim | 07 January 2010 |  22 comments |
Fabiano launches into a sound technical explanation of the way that the query optimiser works in SQL Server with a mention of Brazilian Soccer stars and young ladies on Copacabana beach. You'll never quite think of statistics, execution plans, and the query... Read more...
Phil Factor The SQL of Scrabble and Rapping
by Phil Factor | 25 December 2009 |  20 comments |
In which Phil decides to use a table consisting of all the common words in English to explore ways of cheating at Scrabble and writing doggerel using SQL Server. He then issues a SQL challenge. Read more...
Phil Factor Pivoting, Un-pivoting and Aggregating: A Quick Spin Around the Block
by Phil Factor | 12 November 2009 |  8 comments |
In which Phil is asked to write a nice simple quick-start guide about aggregation, pivoting and un-pivoting techniques. To do so, he takes us all the way from getting the data from a published source, transferring it to SQL Server, un-pivoting it, storing it in a... Read more...
Fabiano Amorim Query Optimizer and Cartesian Products
by Fabiano Amorim | 22 October 2009 |  10 comments |
In his continuing quest to bring a deeper understanding of Query Optimizer to the world at large, Fabiano takes a moment to point out a potential pitfall you may encounter. A light read, but one worth perusing. Read more...
Fabiano Amorim Data Correlation Optimization Internals
by Fabiano Amorim | 14 October 2009 |  4 comments |
Having adroitly introduced us, in his previous article, to the Date Correlation ability of the Query Optimizer, Fabiano discusses the inner workings of this little-known feature in order to explain exactly how Date Correlation works. Read more...
Jacob Sebastian The Art of XSD - eBook Download
by Jacob Sebastian | 07 October 2009 |  1 comment |
When information is exchanged in XML format, you need an agreement between the sender and receiver about the structure and content of the XML document. This "agreement" takes the form of an XSD (XML Schema Definition Language) Schema. Jacob Sebastian's book explains... Read more...
Robert Sheldon Using Information Schema Views
by Robert Sheldon | 01 October 2009 |  5 comments |
Many seasoned database developers tuck away all the commonly-used INFORMATION_SCHEMA queries as templates. They're an indispensable supplement to sp_help and sp_helpText to get handy information about your database objects, and, even if you use SQL Prompt, they're ... Read more...
Fabiano Amorim The Query Optimizer: Date Correlation Optimisation
by Fabiano Amorim | 01 October 2009 |  11 comments |
In SQL Server 2005, a feature was introduced that was hardly noticed, but which might make a great difference to anyone doing queries involving temporal data. For anyone doing Data Warehousing, timetabling, or time-based pricing, this could speed up your queries... Read more...
Joe Celko Celko's SQL Stumper: The Data Warehouse Problem
by Joe Celko | 25 September 2009 |  19 comments |
Joe Celko comes back with a puzzle that isn't new, but one where the answer he originally gave now seems archaic: It is a deceptively simple problem, but is it true that the new features of SQL have simplified the solution? We leave it to the readers to provide the... Read more...
Joe Celko Causation, Correlation and Crackpots
by Joe Celko | 15 September 2009 |  8 comments |
Joe Celko explores the dangers of muddling correlation and causation, emphasises the importance of determining how likely it is that a correlation has occurred by chance, and gets stuck into calculating correlation coefficients in SQL. Along the way, Joe illustrates... Read more...
Robert Sheldon Transact-SQL Formatting Standards (Coding Styles)
by Robert Sheldon | 25 August 2009 |  54 comments |
How should SQL code be formatted? What sort of indentation should you use? Should keywords be in upper case? How should lists be lined up? SQL is one of those languages that will execute anyway however you treat whitespace and capitalization. However, the way SQL is... Read more...
Joe Celko Getting rid of SQL Code
by Joe Celko | 20 August 2009 |  32 comments |
Joe becomes intrigued by the way that experts make errors in any area of technology, and suggests that the problem is more that of mindsets than lack of knowledge. He illustrates the point with SQL Development by means of the "Britney Spears, Automobiles and Squids"... Read more...
Plamen Ratchev Ten Common SQL Programming Mistakes
by Plamen Ratchev | 20 August 2009 |  55 comments |
It is not always easy to spot "antipatterns" in your SQL, especially in more complex queries. In this article, Plamen demonstrates some of the most common SQL coding errors that he encounters, explains their root cause, and illustrates potential solutions. Read more...
Joe Celko Celko's Summer SQL Stumpers: Prime Numbers
by Joe Celko | 23 July 2009 |  43 comments |
Joe Celko kicks off our series of Summer SQL Stumpers with a challenge to improve on his solution to calculating the prime numbers between 1 and 10000. Once the various solutions have been contributed and judged, the winner will be announced. The competition will be... Read more...
Joe Celko Avoiding the EAV of Destruction
by Joe Celko | 18 June 2009 |  8 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 |  6 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 |  12 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 |  7 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 |  25 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 |  11 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 |  7 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 |  44 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 |  15 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 |  58 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 |  31 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 |  4 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 |  11 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 |  11 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 |  9 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 |  17 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 |  24 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 |  34 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 |  7 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...
Cristian Lefter Logon Triggers
by Cristian Lefter | 10 October 2007 |  7 comments |
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...
William Brewer Quantifying Text differences in TSQL
by William Brewer | 20 September 2007 |  4 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 |  38 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 |  23 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 |  31 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...
Robyn Page and Phil Factor SQL Server 2005 DDL Trigger Workbench
by Robyn Page and Phil Factor | 25 May 2007 |  32 comments |
Robun and Phil's latest workbench shows you how to track and log all database changes, including changes to tables, logins, users and queues, using SQL 2005 DDL triggers. Read more...
Adam Machanic A Primer on Managing Data Bitemporally
by Adam Machanic | 10 May 2007 |  7 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 |  41 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 |  76 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 |  23 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 |  28 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 |  131 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...
Eric Brown Encryption without the Confusion
by Eric Brown | 29 November 2006 |  9 comments |
Eric Brown demonstrates some practical encryption techniques in SQL Server 2005, to protect both your objects and your data. Read more...
Tony Davis SQL Server, PostgresSQL and Fish Curry
by Tony Davis | 16 August 2006 |  10 comments |
An interview with Adam Machanic, discussing hot new features of SQL 2005, stored procedures, fish curry and more Read more...
Sanchan Sahai Saxena Using and Monitoring SQL 2005 Query Notification
by Sanchan Sahai Saxena | 11 August 2006 |  50 comments |
Query notification allows your applications to take advantage of caching, safe in the knowledge that the cache will be refreshed whenever any critical data in the underlying database is updated. Find out how it all works... Read more...
Nigel Rivett SQL Server 2005 Common Table Expressions
by Nigel Rivett | 02 August 2006 |  49 comments |
Common Table Expressions (CTEs) are one of the most exciting features to be introduced with SQL Server 2005. Nigel Rivett explains what they are and how they can be used. Read more...
Adam Machanic To SP or not to SP in SQL Server: an argument for stored procedures
by Adam Machanic | 06 June 2006 |  28 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...
Julian Skinner Practical SQL Server 2005 CLR Assemblies
by Julian Skinner | 28 February 2006 |  6 comments |
One advantage of CLR assemblies is the ability to consume web services from within the database. This wouldn’t be easy with T-SQL, and would also require a lot of work in an unmanaged extended stored procedure. With .NET, it’s almost as simple as accessing a local DLL. Read more...
Srinivas Sampath Beginning SQL Server 2005 XML Programming
by Srinivas Sampath | 21 February 2006 |  33 comments |
XML has been used to represent semi-structured (as well as unstructured) data such as documents and emails. If information in these models has to be queried, then XML is probably the simplest way to represent such information. Read more...
Arthur Fuller Intelligent Database Design Using Hash Keys
by Arthur Fuller | 17 February 2006 |  13 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 (80 articles)
  SQL Training (9 articles)

  Database Administration (115 articles)
  T-SQL Programming (130 articles)

  Performance (34 articles)
  Backup and Recovery (25 articles)

  SQL Tools (72 articles)
  SSIS (19 articles)

  Reporting Services (20 articles)

  SQL Home

  Learn SQL Server (80 articles)

  SQL Training (9 articles)

  Database Administration (115 articles)

  T-SQL Programming (130 articles)

  Performance (34 articles)

  Backup and Recovery (25 articles)

  SQL Tools (72 articles)

  SSIS (19 articles)

  Reporting Services (20 articles)

Phil Factor
Automated Script-generation with Powershell and SMO
 In the first of a series of articles on automating the process of building, modifying and copying SQL Server... Read more...



 View the blog
Tony Davis To Not CI to Eye
 Many developers, including Troy Hunt, here on Simple-Talk, have argued persuasively that each database developer in...  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...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... 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...

Creating CSV Files Using BCP and Stored Procedures
 Nigel Rivett demonstrates some core techniques for extracting SQL Server data into CSV files, focussing... Read more...

Over 400,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-2012 Red Gate Software
About | Site map | Become an author | Newsletters | Contact us