Transact-SQL Cookbook

Chapter 3. Data Structures

Data structures are often considered the domain of other programming languages, and not of SQL. In this chapter, we show that data structures can also be very useful for modeling problems and implementing algorithms in SQL. To solve a real-world problem, it is often helpful to have a number of abstract data models available. If you can adapt the problem to be solved to an abstract model, the implementation of a solution is usually much easier.

Abstract data structures help you solve problems that otherwise seem complex. In this chapter, we show you how to perform operations on linear structures such as lists, stacks, and queues in SQL. We also show several recipes for working with multidimensional data structures, such as matrices and arrays. Please note that by “multidimensional,” we are not referring to OLAP data structures, but to arrays and matrices as programmatic data structures.

Types of Data Structures

In this chapter, we plan to discuss the following three major types of data structures:

• Lists

• Stacks and queues

• Arrays and matrices

Lists, stacks, and queues are all linear data structures; the term linear referring to the fact that, conceptually, you’re dealing with a set of elements arranged in the form of a line. The use of SQL is well-suited to such structures because they map easily onto the relational table paradigm. Arrays and matrices, on the other hand, are multidimensional data structures. You can use SQL to work with arrays and matrices, ...