T-SQL Programming

Calculating the Median Value within a Partitioned Set Using T-SQL

by Dwain Camps, 17 December 2013 8 comments

It is ironic that one of the most essential of statistical aggregations, the median, has been so difficult in the past to calculate efficiently in SQL. Although the recent window functions provide the solution, there isn't an obviously superior algorithm performance-wise, particularly when working across partitioned sets. Dwain Camps sets the candidates to work and identifies the winners and losers. Read more...

Window Functions in SQL

by Joe Celko, 31 October 2013 23 comments

SQL's windowing functions are surprisingly versatile, and allow us to cut out all those self-joins and explicit cursors. Joe Celko explains how they are used, and shows a few tricks such as calculating deltas in a time series, and filling in gaps. Read more...

Calculating Values within a Rolling Window in Transact SQL

by Dwain Camps, 17 October 2013 5 comments

Before the SQL Window functions were implemented, it was tricky to calculate rolling totals or moving averages efficiently in SQL Server. There are now a number of techniques, but which has the best performance? Dwain Camps gets out the metaphorical stopwatch. Read more...

Databases and Dominoes

by Joe Celko, 09 September 2013 4 comments

A Dominoes game of Texas 42 inspires Joe to explore unusual uses for check constraints and views. Sometimes, the best way of discovering useful SQL techniques is to tackle the more unusual problems. Read more...

The SQL of Gaps and Islands in Sequences

by Dwain Camps, 25 July 2013 10 comments

Some SQL problems are intriguing because, just when good methods emerge and are accepted, other alternative solutions are discovered. The fun of exploring problems such as 'Gaps and Islands' is all the greater when we have a thorough test-harness to try out the alternative solutions. Read more...

SQL Server ALTER TABLE syntax diagrams

by Phil Factor, 09 July 2013 1 comment

The words in the documentation for the ALTER TABLE syntax on MSDN are accurate with forensic precision, but the potentially-useful 'syntax diagrams' look, to the untrained eye, to be the result of someone accidentally sitting on the keyboard. The answer for ordinary mortals like us who need to understand the syntax is to have railroad diagrams as well. Read more...

Columnstore Queries in SQL Server 2012

by Roy Ernest, 08 July 2013 0 comments

On demo, the columnstore index of SQL Server 2012 gives dazzling performance, but it is optimised for data warehouse queries so it is by no means a universal route to high-performance queries. Once you understand the context in which they are best used, and the ways of ensuring that they work as intended, they can be extremely useful. Read more...

Painless management of a logging table in SQL Server

by Hugo Kornelis, 11 June 2013 27 comments

Tables that log a record of what happens in an application can get very large, easpecially if they're growing by half a billion rows a day. You'll very soon need to devise a scheduled routine to remove old records, but the DELETE statement just isn't a realistic option with that volume of data. Hugo Kornelis explains a pain-free technique for SQL Server. Read more...

SQL Server CREATE TABLE syntax diagrams

by Phil Factor, 06 June 2013 17 comments

Many of us have seen, on MSDN, the heading 'Syntax', followed by a rash of nested brackets and keywords, enough to put off the most resolute of code-cutters. However, there is a goldmine of information there, and Phil had an ambition to get at it, and share the gold. The result is this article, full of railroad diagrams Read more...

The SQL of Parts Explosions

by Joe Celko, 30 May 2013 8 comments

Parts explosions present a classic IT problem. How can one calculate such things as weight or cost of assemblies in SQL? Joe shows how it can be done using nested sets, with not an IDENTITY or GUID in sight.. Read more...

Database Deployment: The Bits - Agent Jobs and Other Server Objects

by Phil Factor, 23 May 2013 3 comments

Databases often need more than just the database objects to run. There may be certain server objects and components, and SQL Agent objects, that are required as well. For an automated deployment, these need to be identified and their build script placed in source control. They then need to be deployed via the pre, or post deployment script. Phil spells out how and why. Read more...

Getting Started Testing Databases with tSQLt

by Robert Sheldon, 08 April 2013 4 comments

There are several frameworks for assisting with the testing of SQL Server databases, but tSQLt is popular because it is written in TSQL and is simple for a database developer to set up and use. It doesn't get in the way. Rob Sheldon shows you how to get started. Read more...

TSQL Pivot Rotations using only REPLACE

by Hugh Bin-Haad, 01 April 2013 5 comments

Pivoting SQL Server tables is always awkward, even with the PIVOT and UNPIVOT operators. If you want to get the job done without GROUP BY or PIVOY, here is a way to do it using only REPLACE. Read more...

Solving Complex T-SQL Problems, Step-By-Step

by Kathi Kellenberger, 18 March 2013 35 comments

What should you do if your first, most intuitive solution to a problem ends up scanning the data more than is necessary, resulting in poor performance? Have you missed a new SQL Server feature that can remove inefficiency from your technique? Alternatively, do you need a little help, and some lateral thinking, to open the path to a different approach? Sometimes, the answer is "both". Read more...

SQL Server 2012 Window Function Basics

by Robert Sheldon, 05 March 2013 4 comments

For some time, Microsoft had a few window functions, but not the full set specified in the SQL 2003 standard. Now, in SQL Server 2012 we have the whole range, and extremely useful they are too. There's no longer an excuse to avoid them, particularly now you have Rob's gentle introduction. Read more...

UNIQUE Constraints in SQL

by Joe Celko, 10 January 2013 5 comments

Here is an in-depth look at an underused constraint, UNIQUE, that can increase the performance of queries and protect data integrity. Read more...

Row Sorting in SQL

by Joe Celko, 30 November 2012 2 comments

It should be easy to model a game of poker in SQL. The problem is, however, that you need to model a permutation from a set of elements. Joe Celko argues that using a group of columns to do this isn't necessarily a violation of 1NF, since a permutation is atomic. Then comes the second problem: how would you sort such a column-base permutation in order? Sorting columns in SQL? Read more...

Database Deployment: The Bits - Copying Data Out

by Phil Factor, 15 November 2012 2 comments

Occasionally, when deploying a database, you need to copy data out to file from all the tables in a database. Phil shows how to do it, and illustrates its use by copying an entire database from one server to another. Read more...

Database Deployment: The Bits - Getting Data In

by Phil Factor, 05 November 2012 1 comment

Quite often, the database developer or tester is faced with having to load data into a newly created database. What could be simpler? Quite a lot of things, it seems. Read more...

Matrix Math in SQL

by Joe Celko, 17 September 2012 15 comments

Relational Databases have tables as data structures, not arrays. This makes it tricky and slow to do matrix operations, but it doesn't mean it is impossible to do. Joe gives the Celko Slant on how to go about doing Matrix Math in SQL. Read more...

Fifty Shades of Gray: The SQL and PowerShell

by Phil Factor, 30 August 2012 3 comments

Phil was struck by a comment by a DBA on a Simple-Talk article that complained that the PowerShell examples weren't simple enough. The traditional "hello world" was too simple (that's actually the program), but he was suddenly struck by the literary fuss over 'Fifty Shades of Gray' to decide to do a 'Fifty Shades of Gray' Wallchart in both TSQL and PowerShell. Read more...

Test-driven Database Development – Why tSQLt?

by Greg Lucas, 21 August 2012 4 comments

Test-Driven Development (TDD) has a good track record in application development, but is less well-established in database development work. This is set to change with the arrival of test frameworks that use SQL, and a plug-in for SQL Server Management Studio. Greg Lucas explains why. Read more...

NULL-Friendly: Using Sparse Columns and Column Sets in SQL Server

by Seth Delconte, 10 July 2012 8 comments

Sparse columns and column sets in SQL Server 2012 provide a way of accomodating the less-structured data that has always proved to be tricky for the relational model. They can be used very effectively where the attributes are sparse for any given entity and very numerous across all entities. Seth Delconte shows how to use them. Read more...

Handling Constraint Violations and Errors in SQL Server

by Phil Factor, 29 June 2012 4 comments

The database developer can, of course, throw all errors back to the application developer to deal with, but this is neither kind nor necessary. How errors are dealt with is very dependent on the application, but the process itself isn't entirely obvious. Phil became gripped with a mission to explain... Read more...

SQL View: Beyond the Basics

by Joe Celko, 28 May 2012 15 comments

Following up from his popular article, SQL View Basics, Joe delves into the main uses of views, explains how the WITH CHECK OPTION works, and demonstrates how the INSTEAD OF trigger can be used in those cases where views cannot be updatable. Read more...

The TSQL of CSV: Comma-Delimited of Errors

by Phil Factor, 13 April 2012 1487 comments

Despite the neglect of the basic ODBC drivers over the years, they still afford a neat way of reading from, and writing to, CSV files; and to be able to do so in SQL as if they were tables is somewhat magical. Just to prove it is possible, Phil creates a CSV version of AdventureWorks as a linked server. Read more...

Bin Packing Problems: The SQL

by Joe Celko, 22 March 2012 9 comments

The 'bin packing' problem isn't just a fascination for computer scientists, but comes up in a whole range of real-world applications. It isn't that easy to come up with a practical, set oriented solution in SQL that gives a near-optimal result. Read more...

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

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

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

Mimicking Network Databases in SQL

by Joe Celko, 26 September 2011 8 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 for which the network model was created, but the old 'network' solutions are still being implemented by programmers, even when they are less effective. Read more...

Temporary Tables in SQL Server

by Phil Factor, 01 September 2011 29 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 things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation. Read more...

Mimicking Magnetic Tape in SQL

by Joe Celko, 17 August 2011 18 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 modern relational database systems. There is now a better way, as Joe explains. Read more...

SQL Programmer's workshop

by Phil Factor, 05 August 2011 0 comments

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

CLR Performance Testing

by Solomon Rutzky, 21 July 2011 3 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 surprising results that you can check for yourselves, and offers some good advice. Read more...

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 about developing things like that without the comfort of Visual Studio Read more...

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 members of the set, and it should be possible to validate and verify them. Celko somehow involves watches and taxi cabs to illustrate the point. Read more...

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 manipulating dates and times. Read more...

PATINDEX Workbench

by Phil Factor, 12 May 2011 14 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 use. It is intended to be pasted into SSMS and used as a basis for experiment Read more...

Performance Implications of Parameterized Queries

by David Berry, 28 April 2011 19 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 how you can measure this impact, and springs a surprise or two in the process Read more...

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

Look-up Tables in SQL

by Joe Celko, 01 February 2011 28 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...

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 more efficient and effective; and this will only be ameliorated with better tools and planning to support the techniques. Read more...

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 why you sometimes need to be very careful if you mix data types when manipulating values. Read more...

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 to populate a table with test data as part of the unit test, and check the results with what should be expected. Sebastian and Dennis, who created tSQLt, explain. Read more...

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 listed as a 'SQL Code Smell'. Joe Celko makes the case. Read more...

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

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, there are few 'best-practices' that are universally appropriate. Read more...

Modifying Contiguous Time Periods in a History Table

by Alex Kuznetsov, 25 November 2010 0 comments

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 DATE data type in SQL Server. The modification of data in this type of table isn't always entirely intuitive so Alex is on hand to give a brief explanation of how to do it. Read more...

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 were difficult to solve with constraints. Suddenly, Alex Kuznetsov came up with a good solution, and so now history tables can benefit from more effective integrity checking. Joe explains... Read more...

Consuming JSON Strings in SQL Server

by Phil Factor, 15 November 2010 21 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 around the representation of hierarchical structures, and some code to get you started. Read more...

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 book, Defensive Database Programming with SQL Server, Alex Kuznetsov offers a simple, robust approach to checking and handling errors in SQL Server, with client-side error handling used to enforce what is done on the server. Read more...

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 requires considerable subtlety to prevent bad data getting in, and if it does, locating and fixing the problem. Joe Celko shows how useful a State transition graph can be, and how essential it can become with the time aspect added. Read more...

Parameter Sniffing

by Greg Larsen, 20 September 2010 8 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 can then hit performance problems, as Greg Larsen explains. Read more...

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

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 them? Joe embarks on a new series of articles by identifying a series of bad practices based on the habit of 'splitting' that which shouldn't be split. Read more...

DMVs for Query Plan Metadata

by Louis Davidson and Tim Ford, 17 August 2010 5 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 Studio can help, it isn't long before you need an armoury of DMVs to provide you the statistics to find the culprits. Read more...

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 give you the awful warning, 'Neglect database source control at your peril'. Read more...

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

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

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 Database Developer's nightmare. In an excerpt from his acclaimed book, Alex explains why it happens, and how you can avoid such problems. Read more...

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 minces his words, and were relieved to find that Joe Celko liked it. Read more...

SQL Server APPLY Basics

by Robert Sheldon, 24 May 2010 10 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 specialist in simple explanations, so we asked him. Read more...

SQL Server CTE Basics

by Robert Sheldon, 29 April 2010 8 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 so clearly for us. Read more...

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

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 producing resilient TSQL code that works, and carries on working. Read more...

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

Procedural, Semi-Procedural and Declarative Programing Part II

by Joe Celko, 02 March 2010 0 comments

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 using triggers? Joe adds some thoughts. Read more...

Exploring your database schema with SQL

by Phil Factor, 02 March 2010 14 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...

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 to illustrate the point that ingrained procedural reflexes can often prevent us from seeing simpler set-based techniques. Read more...

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

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 so easy, it turns out, though Phil Factor is able to come up with various methods for various purposes. Read more...

Laying out SQL Code

by Phil Factor, 21 January 2010 23 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 an overall logic to the way it is done? Read more...

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 were all written before CTEs or the new WINDOW functions. Is there now a better solution? Was there one even then? We leave it to the readers to provide the answer! Read more...

13 Things You Should Know About Statistics and the Query Optimizer

by Fabiano Amorim, 07 January 2010 26 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 optimiser the same way again after reading this, but we think you'll understand them better. Read more...

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

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 relational table, aggregating it and finally pivoting the data in a variety of ways Read more...

Query Optimizer and Cartesian Products

by Fabiano Amorim, 22 October 2009 12 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...

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

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 all. Download the eBook. Read more...

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 usually the best standard way to access such information programmatically within a routine. They are ISO standard SQL and are here to stay. Rob Sheldon goes through the basics in a timely refresher course. Read more...

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 considerably. Who better to introduce this than Query Optimizer expert, Fabiano Amorim? Read more...

Celko's SQL Stumper: The Data Warehouse Problem

by Joe Celko, 25 September 2009 21 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 answer! Read more...

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 the consequences of leaping to the wrong conclusion from correlations with tales of Pop Dread. Read more...

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 laid out will effect its readability and the time taken to review and understand it. Standardisation of code layout is an important issue, but what standard should you adopt? Rob avoids a direct answer, but tells you the sort of answers you'll need to decide upon when creating a strategy for formatting SQL code. Read more...

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" table, and the tangled Stored procedure, and shows ways of getting rid of both procedural and non-procedural code by adopting a different programming mindset. Read more...

Ten Common SQL Programming Mistakes

by Plamen Ratchev, 20 August 2009 57 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...

Celko's Summer SQL Stumpers: Prime Numbers

by Joe Celko, 23 July 2009 45 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 run on Simple-Talk and SQL Server Central together. Read more...

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

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, described - up until now. Robert Sheldon presents a practical workbench to show the various modify methods Read more...

CLR Assembly RegEx Functions for SQL Server by Example

by Phil Factor, 15 April 2009 8 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 Studio expert to develop a CLR assembly for SQL Server Read more...

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 middle value of an ordered result. Easy to do in SQL? Nope. Joe Celko explains why Read more...

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

Divided We Stand: The SQL of Relational Division

by Joe Celko, 17 February 2009 10 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 Guide to Databases', meets the Database Fairy, and is inspired to explain DIVISION. Read more...

Removing Duplicates from a Table in SQL Server

by András Belokosztolszki, 11 February 2009 27 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 using CTE, ROW_NUMBER() and DELETE Read more...

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 reading or writing data between database and file Read more...

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 other factors to take into account when using temporal data such as Holidays, and discusses a few techniques using Calendar, Report Usage and History tables Read more...

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 different. He explains the mysteries of such things as time-zones, lawful time, UTC, CUT, GMT, CE, DST, and EST. Read more...

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

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

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 find something new and useful in this article. Read more...

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

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 Sen describes, along with sage advice about their use. Read more...

Concatenating Row Values in Transact-SQL

by Anith Sen, 31 July 2008 59 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' column? A simple, and intuitive way of displaying data is surprisingly difficult to achieve. Anith Sen gives a summary of different ways, and offers words of caution over the one you choose. 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 modules, services and applications, nonetheless. Read more...

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

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 test-harness you can ensure that the end-user need never sees the apparent gobbledegook of database system error messages, and that they are properly and robustly handled by the application. Read more...

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

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 nobody seems to agree on the best way of doing it Read more...

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

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 there are now ways of simulating concurrency during the test process. Read more...

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

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

TSQL String Array Workbench

by Robyn Page and Phil Factor, 16 March 2008 14 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 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. Alex continues his excellent series, by showing how to catch those subtle problems. Read more...

Numeral Systems and Numbers Conversion in SQL

by Alex Kozak, 10 December 2007 18 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 from the past. Read more...

TSQL Regular Expression Workbench

by Robyn Page and Phil Factor, 27 November 2007 28 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 'try it and see...' Read more...

Importing Text-based data: Workbench

by Robyn Page and Phil Factor, 23 October 2007 35 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 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 counts? Read more...

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

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

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 password, and then returning it to its previous unknown value. 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 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...

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

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

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 works. 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 0 comments

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 45 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 stored procedures, based on use of the FileSystem Object (FSO), that may just get you out of a tight corner… Read more...

Creating cross tab queries and pivot tables in SQL

by Keith Fletcher, 27 March 2007 79 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 changes to you SQL code. Check it out, and then take the cross tab challenge. If you can compile a cross tab report that displays the order value by customer, by quarter, using the stored procedure, you may win a much-coveted prize! 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 down the rogue SPIDs that are causing all the problems… 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 helper tables rather than the dreaded cursor. Read more...

Writing to Word from SQL Server

by Phil Factor, 06 March 2007 25 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 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...

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 procedure that uses OLE Automation to allow you full control over the formatting of your Excel report, and the ability to include sums, ranges, pivot tables and so on. Read more...

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

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

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

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

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

by Adam Machanic, 06 June 2006 30 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...

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

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

Intelligent Database Design Using Hash Keys

by Arthur Fuller, 17 February 2006 16 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? Then you will notice the impact both in terms of storage requirements and search performance. 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 at the argument. Read more...