Summary

In this chapter, we looked at two aspects of Transact-SQL programming that go beyond the set-based DML operations of SELECT, INSERT, UPDATE, and DELETE. First, I described server cursors in detail. Cursors are important tools that help you bridge the impedance mismatch between the set-oriented world of SQL Server and traditional record-oriented programming languages. When used properly, cursors are invaluable. When overused or used inappropriately, they can cause you to have a bad experience with SQL Server. SQL Server provides fully scrollable, updateable cursors.

SQL Server has four main cursor models: static, keyset, dynamic, and forward-only. Cursors can be accessed via syntax used directly in Transact-SQL or via function calls used in the programming interfaces to SQL Server (ODBC, OLE DB, and so on). These two types of cursors are called Transact-SQL cursors and API server cursors. This chapter focused on the Transact-SQL syntax, but the key points are relevant no matter what cursor interface you use with SQL Server.

Second, we looked at the processing of data that might be too large to store in the actual table rows. SQL Server provides the three datatypes—text, ntext, and image—to support LOB (large object) data, which can store up to 2 GB worth of data. You can use three special operators with LOB data: READTEXT, WRITETEXT, and UPDATETEXT. Because no datatypes can store this very large object, most manipulation of LOB data is done using text pointers. The three LOB operators take a text pointer as an argument and can allow you to access and manipulate only a subset of the data.