Click here to monitor SSC
Phil Factor

Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google. Besides the obvious Full-Text search, there are plenty of techniques for finding that pesky data that resists the normal SELECT blandishments. Phil Factor describes some alternative techniques. Read more...

Joe Celko

Database Normalization Basics

The task of Database Normalization doesn't have to be painful, especially if you follow Old Mother Celko's Normalization Heuristics. Read more...

Joe Celko

On Handling Dates in SQL

The calendar is inherently complex by the very nature of the astronomy that underlies the year, and the conflicting historical conventions. The handling of dates in TSQL is even more complex because, when SQL Server was Sybase, it was forced by the lack of prevailing standards in SQL to create its own ways of processing and formatting dates and times. Joe Celko looks forward to a future when it is possible to write standard SQL date-processing code with SQL Server. Read more...

Dwain Camps

Calculating Gaps Between Overlapping Time Intervals in SQL

There are a number of real-life reporting tasks in SQL that require a 'gaps and islands' analysis. There are a number of techniques around that work, but finding ones that scale well makes for a tougher, but interesting, challenge. Read more...

Dwain Camps

The Performance of the T-SQL Window Functions

Window Functions in SQL greatly simplify a whole range of financial and statistical aggregations on sets of data. Because there is less SQL on the page, it is easy to assume that the performance is better too: but is it? Dwain gets out the test harness to investigate. Read more...

Hugo Kornelis

Generating Test Data in TSQL

To test SQL, you need test data. There are usually many reasons why you can't use production data. Although it is usually enough to use a utility to generate test data, sometimes your requirements will compel you to resort to code to supplement this. Hugo shows how he used SQL and C# to generate large volumes of test data involving related columns and complex distributions. Read more...

Dev Nambi

Agile Database Development

Agile methodologies work well with database developments only if great care is taken to do things right. It requires good judgement and leaves little room for error. Dev Nambi, in an extract from the book Tribal SQL, argues that Agile works for smart, curious, and experienced software engineers. Read more...

Dwain Camps

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

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

Joe Celko

Window Functions in SQL

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

Dwain Camps

Calculating Values within a Rolling Window in Transact SQL

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

Joe Celko

Databases and Dominoes

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

Dwain Camps

The SQL of Gaps and Islands in Sequences

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

Phil Factor

SQL Server ALTER TABLE syntax diagrams

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

Roy Ernest

Columnstore Queries in SQL Server 2012

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

Hugo Kornelis

Painless management of a logging table in SQL Server

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

Phil Factor

SQL Server CREATE TABLE syntax diagrams

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

Joe Celko

The SQL of Parts Explosions

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

Phil Factor

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

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

Robert Sheldon

Getting Started Testing Databases with tSQLt

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

Hugh Bin-Haad

TSQL Pivot Rotations using only REPLACE

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

Kathi Kellenberger

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

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

Robert Sheldon

SQL Server 2012 Window Function Basics

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

Joe Celko

UNIQUE Constraints in SQL

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

Joe Celko

Row Sorting in SQL

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

Phil Factor

Database Deployment: The Bits - Copying Data Out

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

Phil Factor

Database Deployment: The Bits - Getting Data In

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

Joe Celko

Matrix Math in SQL

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

Phil Factor

Fifty Shades of Gray: The SQL and PowerShell

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

Greg Lucas

Test-driven Database Development – Why tSQLt?

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

Seth Delconte

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

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

Phil Factor

Handling Constraint Violations and Errors in SQL Server

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

Joe Celko

SQL View: Beyond the Basics

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

Phil Factor

The TSQL of CSV: Comma-Delimited of Errors

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

Joe Celko

Bin Packing Problems: The SQL

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

Jeremiah Peschka

SQL Server Functions: The Basics

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

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

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

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

Phil Factor

Temporary Tables in SQL Server

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

Joe Celko

Mimicking Magnetic Tape in SQL

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

Phil Factor

SQL Programmer's workshop

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

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

Phil Factor

How to develop TSQL Code

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

Joe Celko

A Tale of Identifiers

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

Joe Celko

TIME Gentlemen please! The SQL Server temporal datatypes

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

Phil Factor

PATINDEX Workbench

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

David Berry

Performance Implications of Parameterized Queries

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

Joe Celko

SQL Server CASE Law

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

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

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

Robert Sheldon

Data Conversion in SQL Server

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

Sebastian Meine and Dennis Lloyd

SQL Server Unit Testing with tSQLt

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

Joe Celko

BIT of a Problem

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

Phil Factor

The Parodist: A SQL Server Application

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

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

Alex Kuznetsov

Modifying Contiguous Time Periods in a History Table

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

Joe Celko

Contiguous Time Periods

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

Phil Factor

Consuming JSON Strings in SQL Server

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

Alex Kuznetsov

Defensive Error Handling

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

Joe Celko

State Transition Constraints

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

Greg Larsen

Parameter Sniffing

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

Auke Teeninga

Minesweeper in T-SQL

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

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

Louis Davidson and Tim Ford

DMVs for Query Plan Metadata

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

Mike Mooney

When Database Source Control Goes Bad

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

Phil Factor

SQL Server CRUD-Generation from System Views

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

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

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

Joe Celko

Book Review: Defensive Database Programming With SQL Server

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

Robert Sheldon

SQL Server APPLY Basics

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

Robert Sheldon

SQL Server CTE Basics

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

Phil Factor

Exploring SQL Server table metadata with SSMS and TSQL

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

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

Joe Celko

Celko's SQL Stumper: Eggs in one Basket

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

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

Phil Factor

Exploring your database schema with SQL

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

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

Paul Nielsen

Switching rows and columns in SQL

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

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

Phil Factor

Laying out SQL Code

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

Joe Celko

Celko's SQL Stumper: The Class Scheduling Problem

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

Fabiano Amorim

13 Things You Should Know About Statistics and the Query Optimizer

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

Phil Factor

The SQL of Scrabble and Rapping

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

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

Fabiano Amorim

Query Optimizer and Cartesian Products

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

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

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

Robert Sheldon

Using Information Schema Views

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

Fabiano Amorim

The Query Optimizer: Date Correlation Optimisation

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

Joe Celko

Celko's SQL Stumper: The Data Warehouse Problem

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

Joe Celko

Causation, Correlation and Crackpots

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

Robert Sheldon

Transact-SQL Formatting Standards (Coding Styles)

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

Joe Celko

Getting rid of SQL Code

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

Plamen Ratchev

Ten Common SQL Programming Mistakes

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

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

Joe Celko

Avoiding the EAV of Destruction

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

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

Phil Factor

CLR Assembly RegEx Functions for SQL Server by Example

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

Joe Celko

Median Workbench

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

Alex Kuznetsov

Brain Teaser for Pi Day

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

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

András Belokosztolszki

Removing Duplicates from a Table in SQL Server

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

Phil Factor

The TSQL of Text Files

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

Joe Celko

Temporal Data Techniques in SQL

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

Joe Celko

Temporal Datatypes in SQL Server

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

Joe Celko

Unique Experiences!

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

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!

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

Alex Kozak

The Bejeweled Puzzle in SQL

Alex Kozak provides another SQL puzzle to hone your SQL Skills with. Read more...

Anith Sen

Faking Arrays in Transact SQL

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

Anith Sen

Concatenating Row Values in Transact-SQL

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

William Brewer

JSON and other data serialization languages

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

Alex Kozak

Missing Date Ranges- the Sequel

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

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

Nigel Rivett

Identity Columns

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

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

Robyn Page and Phil Factor

SQL String User Function Workbench: part 2

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

Even when your code tests out perfectly in the standard test cell, you can experience errors in the real production setting where several processes are hitting the database at once, in unpredictable ways. You shouldn’t, of course, let it get that far, because there are now ways of simulating concurrency during the test process. Read more...

Robyn Page and Phil Factor

SQL String User Function Workbench: part 1

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

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

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

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

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

Alex Kozak

Numeral Systems and Numbers Conversion in SQL

Numeral systems can be fascinating. In everyday programming, we are now becoming quite insulated from the need to convert between binary numbers and their representation, so it is a novelty to try out ways of doing it in SQL, and experiment with other number systems from the past. Read more...

Robyn Page and Phil Factor

TSQL Regular Expression Workbench

Robyn and Phil start by writing a gentle introduction to using Regular expressions for validation, data cleaning and data import in TSQL, and finally end up with a routine for doing google-style searches that show the context of hits. It's all done in the spirit of 'try it and see...' Read more...

Robyn Page and Phil Factor

Importing Text-based data: Workbench

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

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

Cristian Lefter

Logon Triggers

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

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

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

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'

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

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

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'

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

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

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

Robyn Page and Phil Factor

RSS Newsfeed Workbench

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

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

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

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

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

Robyn Page and Phil Factor

SQL Server Grouping Workbench

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

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

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

Keith Fletcher

Creating cross tab queries and pivot tables in SQL

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

Pop Rivett and the Case of the Rogue SPIDs

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

Robyn Page and Phil Factor

The Helper Table Workbench

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

Phil Factor

Writing to Word from SQL Server

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

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

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

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

Eric Brown

Encryption without the Confusion

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

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

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

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

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

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

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

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

Arthur Fuller

A case for canned SQL

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

Phil Factor

Cursors and embedded SQL

The database mole turns his attention to take a look at cursors and embedded SQL Read more...

Douglas Reilly

Managing database changes

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

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

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Most Viewed

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
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... 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...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

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