With the arrival of year 2000, it’s appropriate to discuss the impact the Y2K problem on SQL Server apps and some ways of handling it. A lot of hysteria seems to surround the whole Year 2000 issue—on the part of technical and nontechnical people alike—so it seems worthwhile to take a moment and address the way in which the Y2K problem affects SQL Server and applications based on it.

First, due to the fact that SQL Server sports a datetime data type, many of the problems plaguing older applications and DBMSs simply don’t apply here. Dates are stored as numeric quantities rather than character strings, so no assumptions need be made regarding the century, a given datetime variable, or column references.

Second, given that even a lowly smalldatetime can store dates up to 2079, there’s no capacity issue, either. Since four bytes are reserved for the date portion of a datetime column, a quantity of up to 2,147,483,647 days (including a sign bit) can be stored, even though there are only 3,012,153 days between January 1, 1753 and December 31, 9999.

Despite all this, there are still a number of subtle ways the Y2K and other date problems can affect SQL Server applications. Most of them have to do with assumptions about date formatting in T-SQL code. Consider the following:

What date will be returned? Though it’s not obvious from the code, the date January 1, 2039 is the answer. Why? Because SQL Server has an internal century “window” that controls how two-digit years are interpreted. You can configure this with Enterprise Manager (right click your server, select Properties, then click Server Settings) or with sp_configure (via the two digit year cutoff setting). By default, two-digit years are interpreted by SQL Server as falling between 1950 and 2049. So, T-SQL code that uses the SELECT above and assumes it references 1939 may not work correctly. (Assuming 2039 for Dad’s birth year would mean that he hasn’t been born yet!)

The simplest answer, of course, is to use four-digit years. This disambiguates dates and removes the possibility that changing the two-digit year cutoff setting might break existing code. Note that I’m not recommending that you require four-digit dates in the user interfaces you build—I refer only to the T-SQL code you write. What you require of users is another matter.

Another subtle way that Y2K can affect SQL Server apps is through date-based identifiers. It’s not uncommon for older systems (and some newer ones) to use a year-in-century approach to number sequential items. For example, a purchase order system I rewrote in the eighties used the format YY-SequenceNumber to identify POs uniquely. These numbers were used as unique identifiers in a relational database system. Each time a new PO was added, a routine in the front-end application would search a table for the largest SequenceNumber and increment it by one. About five years before I became associated with the project, the company had merged with another company that had the same numbering scheme. In order to avoid duplicate keys, the programmer merging the two companies’ data simply added 10 to the year prefixes of the second company’s purchase orders. This, of course, amounted to installing a time bomb that would explode in ten years when the new keys generated for the first company’s data began to conflict with the second company’s original keys. Fortunately, we foresaw this situation and remedied it before it occurred. We remerged the two databases, this time adding to the SequenceNumber portion of the PO number, rather than its year prefix. We added a number to the second company’s sequence numbers that was sufficient to place them after all those of the first company, thus eliminating the possibility of future key conflicts.

This situation was not so much Y2K related as it was an imprudent use of date-based keys; however, consider the situation where the keys start with the year 1999. A two-digit scheme could not handle the rollover to 2000 because it could no longer retrieve the maximum sequence value from the database and increment it.

A common thread runs through all these scenarios: omitting the century portion of dates is problematic. Don’t do it unless you like problems.

Another common use of datetime fields is to build calendars and schedules. Consider the following problem: A library needs to compute the exact day a borrower must return a book in order to avoid a fine. Normally, this would be fourteen calendar days from the time the book was checked out, but since the library is closed on weekends and holidays, the problem is more complex than that. Let’s start by building a simple table listing the library’s holidays. A table with two columns, HolidayName and HolidayDate, would be sufficient. We’ll fill it with the name and date of each holiday the library is closed. Here’s some code to build the table:

Next, we’ll build a table of check-out/check-in dates for the entire year. It will consist of two columns as well, CheckOutDate and DueDate. To build the table, we’ll start by populating CheckOutDate with every date in the year and DueDate with each date plus fourteen calendar days. Stored procedures—compiled SQL programs that resemble 3GL procedures or subroutines—work nicely for this because local variables and flow-control statements (e.g., looping constructs) are right at home in them. You can use local variables and control-flow statements outside stored procedures, but they can be a bit unwieldy and you lose much of the power of the language in doing so. Here’s a procedure that builds and populates the DUEDATES table:

Now that we’ve constructed the table, we need to adjust each due date that falls on a holiday or weekend to the next valid date. The problem is greatly simplified by the fact that the table starts off with no weekend or holiday check-out dates. Since check-ins and check-outs are normally separated by fourteen calendar days, the only way to have a weekend due date occur once the table is set up initially is by changing a holiday due date to a weekend due date—that is, by introducing it ourselves.

One approach to solving the problem would be to execute three UPDATE statements: one to move due dates that fall on holidays to the next day, one to move Saturdays to Mondays, and one to move Sundays to Mondays. We would need to keep executing these three statements until they ceased to affect any rows. Here’s an example:

This technique uses a join to HOLIDAYS to adjust holiday due dates and the DATEPART() function to adjust weekend due dates. Once the procedure executes, you’re left with a table of check-out dates and corresponding due dates. Notice the use of @@ROWCOUNT in the stored procedure to determine the number of rows affected by each UPDATE statement. This allows us to determine when to end the loop—when none of the three UPDATEs registers a hit against the table. The necessity of the @keepgoing variable illustrates the need in Transact-SQL for a DO...UNTIL or REPEAT...UNTIL looping construct. If the language supported a looping syntax that checked its control condition at the end of the loop rather than at the beginning, we might be able to eliminate @keepgoing.

Given enough thought, we can usually come up with a better solution to an iterative problem like this than the first one that comes to mind, and this one is no exception. Here’s a solution to the problem that uses just one UPDATE statement.

This technique takes advantage of the fact that the table starts off with no weekend due dates and simply avoids creating any when it adjusts due dates that fall on holidays. It pulls this off via the CASE function. If the holiday due date we’re about to adjust is already on a Friday, we don’t simply add a single day to it and expect later UPDATE statements to adjust it further—we add enough days to move it to the following Monday. Of course, this doesn’t account for two holidays that occur back to back on a Thursday and Friday, so we’re forced to repeat the process.

The procedure uses an interesting technique of returning a message string to “seed” the @@ROWCOUNT automatic variable. In addition to notifying the user of what the procedure is up to, returning the string sets the initial value of @@ROWCOUNT to 1 (because it returns one “row”), permitting entrance into the loop. Once inside, the success or failure of the UPDATE statement sets @@ROWCOUNT. Taking this approach eliminates the need for a second counter variable like @@keepgoing. Again, an end-condition looping construct would be really handy here.

Just when we think we have the best solution possible, further reflection on a problem often reveals an even better way of doing things. Tuning SQL queries is an iterative process that requires lots of patience. You have to learn to balance the gains you achieve with the pain they cost. Trimming a couple of seconds from a query that runs once a day is probably not worth your time, but trimming a few from one that runs thousands of times may well be. Deciding what to tune, what not to, and how far to go is a skill that’s gradually honed over many years.

Here’s a refinement of the earlier techniques that eliminates the need for a loop altogether. It makes a couple of reasonable assumptions in order to pull this off. It assumes that no more than two holidays will occur on consecutive days (or that a single holiday will never span more than two days) and that no two holidays will be separated by less than three days. Here’s the code:

This solution takes Thursday-Friday holidays into account via its CASE statement. If it encounters a due date that falls on a Thursday holiday, it checks to see whether the following Friday is also a holiday. If so, it adjusts the due date by enough days to move it to the following Monday. If not, it adjusts the due date by a single day just as it would a holiday falling on any other day of the week.

The procedure also eliminates the subquery used by the earlier techniques. Transact-SQL supports the FROM extension to the ANSI/ISO UPDATE statement, which allows one table to be updated based on data in another. Here, we establish a simple inner join between DUEDATES and HOLIDAYS in order to limit the rows updated to those with due dates found in HOLIDAYS.

You can concatenate string fields and variables using the 1 operator, like this:

Whether you should choose to create character or variable character fields depends on your needs. If the data you’re storing is of a relatively fixed length and varies very little from row to row, fixed character fields make more sense. Each variable character field carries with it the overhead associated with storing a field’s length in addition to its data. If the length of the data it stores doesn’t vary much, a fixed-length character field will not only be more efficiently stored, it will also be faster to access. On the other hand, if the data length varies considerably from row to row, a variable-length field is more appropriate. Variable character fields can also be more efficient in terms of SQL syntax. Consider the previous example:

Because @Vocalist is a fixed character variable, the concatenation doesn’t work as we might expect. Unlike variable-length @Song, @Vocalist is right-padded with spaces to its maximum length, which produces this output:

Of course, we could use the RTRIM() function to remove those extra spaces, but it would be more efficient just to declare @Vocalist as a varchar in the first place.

One thing to watch out for with varchar concatenation is character movement. Concatenating two varchar strings can yield a third string where a key character (e.g., the last character or the first character of the second string) shifts within the new string due to blanks being trimmed. Here’s an example:

Due to character movement and because at least one of the names contains multiple spaces, there’s no easy way to extract the authors’ first and last names once they’ve been combined in this way. Since au_fname is a 20-character field, the first character of au_lname is logical character 21 in the concatenated name. However, that character has moved due to au_lname’s concatenation with a varchar (nonpadded) string. It is now in a different position for each author, making extricating the original names next to impossible. This may not be an issue—it may be what you intend—but it’s something of which you should be aware.

By default, SQL Server doesn’t trim trailing blanks and zeros from varchar or varbinary values when they’re inserted into a table. This is in accordance with the ANSI SQL-92 standard. If you want to change this, use SET ANSI_PADDING (or SET ANSI_DEFAULTS). When ANSI_PADDING is OFF, field values are trimmed as they’re inserted. This can introduce some subtle problems. Here’s an example:

Because ANSI_PADDING has been turned OFF, no rows are returned by the second query even though we’re searching for a value we just inserted. Since ANSI_PADDING was disabled when @johnbonham was inserted, its trailing blanks were removed. That’s why the listed data lengths of the inserted values differ from row to row even though the ones we supplied were all the same length. When the second query attempts to locate a record using one of the inserted values, it fails because the value it’s using hasn’t been trimmed. The salient point here is that disabling ANSI_PADDING affects only the way values are stored—it doesn’t change the way that variables and constant values are handled. Since these are often used in comparison operations with stored values, a mismatch results—one value with trailing blanks and one without. That’s where subtle problems come in. As they say, the devil is in the details. Here’s what the result set would look like with ANSI_PADDING in effect:

There are a number of SQL Server string functions. You can check the Books Online for specifics. I’ll take you through some of the more interesting ones.

Similar to a regular stored procedure, an extended procedure is accessed as though it was a compiled SQL program. In actuality, extended procedures aren’t written in Transact-SQL—they reside in DLLs (Dynamic Link Libraries) external to the server. They make use of the SQL Server ODS (Open Data Services) API using a language tool capable of producing DLLs such as C11 or Delphi.

As you might have guessed, the xp_sprintf extended stored procedure works similarly to the C sprintf() function. You can pass it a variable, a format string, and a list of arguments in order to construct a string variable. Currently, only string arguments are supported, so you can’t pass integers or other data types directly—but you can use them indirectly by converting them to strings first. Here’s an example illustrating the use of xp_sprintf:

Here’s an example showing how to cast other types of variables and fields to strings in order to use them as arguments to xp_sprintf:

Xp_sscanf is the inverse of xp_sprintf. Rather than putting variables into a string, xp_sscanf extracts values from a string and places them into user variables, similar to the C sscanf() function. Here’s an example:

Using the %s and %c sscanf() format specifiers laid out in the second string, this example parses the first string argument for the specified character strings arguments. The %s specifier extracts a string, while %c maps to a single character. As each string or character is extracted, it’s placed in the output variable corresponding to it sequentially. A maximum of 50 output variables may be passed into xp_sscanf. You can run the query above (like the other queries in this chapter, it’s also on the accompanying CD) to see how xp_sscanf works.

If you’ve used C’s sscanf() function before, you’ll be disappointed by the lack of functionality in the Transact-SQL version. Many of the format parameters normally supported by sscanf()—including width specifiers—aren’t supported, nor are data types other than strings. Nevertheless, for certain types of parsing, xp_sscanf can be very handy.

Using the PATINDEX() function, you can search string fields and variables using wildcards. Here’s an example:

As used below, PATINDEX() works very similarly to the LIKE predicate of the WHERE clause. The primary difference is that PATINDEX() is more than a simple predicate—it returns the offset of the located pattern as well—LIKE doesn’t. To see how similar PATINDEX() and LIKE are, check out these examples:

could be rewritten as

Similarly,

can be reworked to use LIKE instead:

PATINDEX() really comes in handy when you need to filter rows not only by the presence of a mask but also by its position. Here’s an example:

Here, the first query returns all the rows in the table because LIKE can’t distinguish one occurrence of the pattern from another (of course, you could work around this by enclosing the column reference within SUBSTRING() to prevent hits within its first fifteen characters). PATINDEX(), by contrast, allows us to filter the result set based on the position of the pattern, not just its presence.

The Transact-SQL EXEC() function and the sp_executesql stored procedure allow you to execute a string variable as a SQL command. This powerful ability allows you to build and execute a query based on runtime conditions within a stored procedure or Transact-SQL batch. Here’s an example of a cross-tab query that’s constructed at runtime based on the rows in the pubs..authors table:

(Result set abridged)

The cross-tab that this query builds consists of one column for the book title and one for each author. An “X” denotes each title-author intersection. Since the author list could change from time to time, there’s no way to know in advance what columns the table will have. That’s why we have to use dynamic SQL to build it.

This code illustrates several interesting techniques. First, note the shortcut the code uses to build the first rendition of the @execsql string variable:

The cross-tab that’s returned by the query is first constructed in a temporary table. @execsql is used to build and populate that table. The code builds @execsql by initializing it to a stub CREATE TABLE command, then appending a new column definition to it for each row in authors. Building @execsql in this manner is quick and avoids the use of a cursor—a mechanism for processing tables a row at a time. Compared with set-oriented commands, cursors are relatively inefficient, and you should avoid them when possible (see Chapter 13, “Cursors,” for more information). When the SELECT completes its iteration through the authors table, @execsql looks like this:

All that’s missing is a closing parenthesis, which is supplied when EXEC() is called to create the table:

Either EXEC() or sp_executesql could have been called here to execute @execsql. Generally speaking, sp_executesql is faster and more feature laden than EXEC(). When you need to execute a dynamically generated SQL string multiple times in succession (with only query parameters changing between executions), sp_executesql should be your tool of choice. This is because it easily facilitates the reuse of the execution plan generated by the query optimizer the first time the query executes. It’s more efficient than EXEC() because the query string is built only once, and each parameter is specified in its native data format, not first converted to a string, as EXEC() requires.

Sp_executesql allows you to embed parameters within its query string using standard variable names as placeholders, like so:

Here, @au_lname is a placeholder. Though the query may be executed several times in succession, the only thing that varies between executions is the value of @au_lname. This makes it highly likely that the query optimizer will be able to avoid recreating the execution plan with each query run.

Note the use of the “N” prefix to define the literal strings passed to the procedure as Unicode strings. Unicode is covered in more detail later in this chapter, but it’s important to note that sp_executesql requires Unicode strings to be passed into it. That’s why @execsql was defined using nvarchar.

In this particular case, EXEC() is a better choice than sp_executesql for two reasons: It’s not called within a loop or numerous times in succession, and it allows simple string concatenation within its parameter list; sp_executesql, like all stored procedures, doesn’t.

The second half of the procedure illustrates a more complex use of dynamic SQL. In order to mark each title-author intersection with an “X,” the query must dynamically build an INSERT statement for each title-author pair. The title becomes an inserted value, and the author becomes a column name, with “X” as its value.

Unlike the earlier example, sp_executesql is used to execute the dynamically generated INSERT statement because it’s called several times in succession and, thanks to the concatenation within the cursor definition, doesn’t need to concatenate any of its parameters.

Since sp_executesql allows parameters to be embedded in its query string, you may be wondering why we don’t use this facility to pass it the columns from authors. After all, they would seem to be fine examples of query parameters that vary between executions—why perform all the concatenation in the cursor? The reason for this is that sp_executesql limits the types of replaceable parameters it supports to true query parameters—you can’t replace portions of the query string indiscriminately. You can position replaceable parameters anywhere a regular variable could be placed if the query were run normally (outside sp_executesql), but you can’t replace keywords, object names, or column names with placeholders—sp_executesql won’t make the substitution when it executes the query.

One final point worth mentioning is the reason for the use of the global temporary table. A global temporary table is a transient table that’s prefixed with “##” instead of “#” and is visible to all connections, not just the one that created it. As with local temporary tables, it is dropped when no longer in use (when the last connection referencing it ends).

It’s necessary here because we use dynamic Transact-SQL to create the cross-tab table, and local temporary tables created dynamically are visible only to the EXEC() or sp_executesql that created them. In fact, they’re deleted as soon as the dynamic SQL that created them ends. So, we use a global temporary table instead, and it remains visible until explicitly dropped by the query or the connection closes.

The biggest disadvantage to using global temporary tables over local ones is the possibility of name collisions. Unlike their local brethren, global temporary table names aren’t unique across connections—that’s what makes them globally accessible. Regardless of how many connections reference it, ##autxtab refers to exactly the same object in tempdb. If a connection attempts to create a global temporary table that another connection has already built, the create will fail.

We accepted this limitation in order to be able to create the table dynamically, but there are a couple of other options. First, the body of the procedure could have been written and executed as one big dynamic query, making local tables created early in the query visible to the rest of it. Second, we could create the table itself in the main query, then use dynamic T-SQL to execute ALTER TABLE statements to add the columns for each author in piecemeal fashion. Here’s a variation on the earlier procedure that does just that:

Note the use of the AlterScript cursor to supply sp_executesql with ALTER TABLE queries. Since the table itself is created in the main query and since the temporary objects created in a query are visible to its dynamic queries, we’re able to get by with a local temporary table and eliminate the possibility of name collisions. Though this solution requires more code than the initial one, it’s also much safer.

Note that this object visibility doesn’t carry over to local variables. Variables defined by the calling routine are not visible to EXEC() or sp_executesql. Also, variables defined within an EXEC() or call to sp_executesql go out of scope when they return to the caller. Basically, the only way to pass variables between them is via sp_executesql’s parameter list or via concatenation within the EXEC call.

In the past, character string data was limited to characters from sets of 256 characters. Each character was composed of a single byte and a byte can store just 256 (2⁸) different characters. Prior to the adoption of the Unicode standard, all character sets were composed of single-byte characters.

Unicode expands the number of possible characters to 2¹⁶, or 65,536, by using two bytes instead of one. This increased capacity facilitates the inclusion of the alphabets and symbols found in most of the world’s languages, including all of those from the single-byte character sets used previously.

Transact-SQL’s regular string types (char, varchar, and text) are constructed of characters from a particular single-byte character set. This character set is selected during installation and can’t be changed afterward without recreating databases and reloading data. Unicode strings, by contrast, can store any character defined by the Unicode standard. Since Unicode strings take twice as much storage space as regular strings, they can be only half as long (4000 characters).

SQL Server defines special Unicode-specific data types for storing Unicode strings: nchar, nvarchar, and ntext. You can use these data types for columns that need to store characters from multiple character sets. As with regular character string fields, you should use nvarchar when a column’s data varies in length from row to row and nchar when it doesn’t. Use ntext when you need to store more than 4000 characters.

SQL Server’s Unicode string types are based on SQL-92’s National Character data types. As with SQL-92, Transact-SQL uses the prefix character N to distinguish Unicode data types and values, like so:

This query returns “16” because the uppercase N makes ‘The Firm’ a Unicode string.

The first thing you discover when doing any real floating point work with SQL Server is that Transact-SQL does not correct for floating point rounding errors. This allows the same numeric problem, stated in different ways, to return different results—heresy in the world of mathematics. Languages that don’t properly handle floating point rounding errors are particularly susceptible to errors due to differences in the ordering of terms. Here’s an example that generates a random list of floating point numbers, then arranges them in various orders and totals them:

Since the numbers being totaled are the same in all three cases, the results should be the same, but they aren’t. Increasing SQL Server’s floating point precision (via the /p server command line option) helps but doesn’t solve the problem—floating point rounding errors aren’t handled properly, regardless of the precision of the float. This causes grave problems for applications that depend on floating point accuracy and is the main reason you’ll often see the complex floating point computations in SQL Server applications residing in 3GL routines.

The one foolproof answer here is to use fixed-point rather than floating point types. The decimal and numeric data types do not suffer from floating point rounding errors because they aren’t floating point types. As such, they also can’t use the processor’s FPU, so computations will probably be slower than with real floating point types. This slowness may be compensated for in other areas, so this is not as bad as it may seem. The moral of the story is this: SQL Server doesn’t correct floating point errors, so be careful if you decide to use the float or real data types.

Here’s the query rewritten to use a fixed-point data type with a precision of 10 and a scale of 4:

Prior to release 7.0 of SQL Server, dividing a numeric quantity by zero returned a NULL result. By default, that’s no longer the case. Dividing a number by zero now results in a divide by zero exception:

You can disable this behavior via the ANSI_WARNINGS and ARITHIGNORE session settings. By default, ANSI warnings are enabled when you connect to the server using ODBC or OLEDB, and ARITHIGNORE is disabled. Here’s the query modified to return NULL when a divide by zero occurs:

(If you’re executing this query from Query Analyzer, you’ll need to disable ANSI warnings in the Current Connection Options dialog in order for this to work.)

There’s an inconsistency between the monetary types—money and smallmoney—and the other numeric data types. All numerics except for money and smallmoney implicitly convert from character strings during INSERTs and UPDATEs. Money and smallmoney, for some reason, have problems with this. For example, the following query generates an error message:

You can change c1’s data type to any other numeric type—from tinyint to float—and the query will execute as you expect. The monetary types, for some reason, are more finicky. They require an explicit cast, like so:

In addition to using CAST() and CONVERT() to format numeric data types as strings, you can use the STR() function. STR() is better than the generic CAST() and CONVERT() because it provides for right justification and allows the number of decimal places to be specified. Here are some examples:

and

As implemented by SQL Server, BLOB fields are somewhat ponderous, and you should think twice before including one in a table definition. BLOB fields are stored in a separate page chain from the row in which they reside. All that’s stored in the BLOB column itself is a sixteen-byte pointer to the first page of the column’s page chain. BLOBs aren’t stored like other data types, and you can’t treat them as though they were. You can’t, for example, declare text or image local variables. Attempting to do so generates a syntax error. You can pass a text or image value as a parameter to a stored procedure, which you can then use in a DML statement, but you can’t reassign the variable or do much else with it. Here’s a procedure that illustrates:

Here, @instext is a text parameter that the stored procedure inserts into the text column notes. Since you can’t define local text variables, @instext can’t be assigned to another text variable (though it can be assigned to a regular char or varchar variable) and can’t have a different value assigned to it. For the most part, it’s limited to being used in place of a text value in a DML (Data Management Language) command.

You also can’t refer to BLOB columns in the WHERE clause using the equal sign—the LIKE predicate, PATINDEX(), or DATALENGTH() is required instead. Here’s an example:

Even though the INSERT statement has just supplied the ‘test’ value, the SELECT can’t query for it using the traditional means of doing so. You have to do something like this instead:

The normal rules governing data types and column access simply don’t apply with BLOB columns, and you should bear that in mind if you elect to make use of them.

Unlike smaller BLOBs, it’s not practical to return large BLOB data via a simple SELECT statement. Though you can use SET TEXTSIZE to control the amount of text returned by a SELECT, your front end may not be able to deal with large amounts of BLOB data properly. Moreover, since you can’t declare local text or image variables, you can’t use SELECT to assign a large BLOB to a variable for further parsing. Instead, you should use the READTEXT command to access it in pieces. READTEXT works with image as well as text columns. It takes four parameters: the column to read, a valid pointer to its underlying text, the offset at which to begin reading, and the size of the chunk to read. Use the TEXTPTR() function to retrieve a pointer to a BLOB column’s underlying data. This pointer is a binary(16) value that references the first page of the BLOB data. You can check its validity via the TEXTVALID() function. Here’s an example illustrating the use of TEXTPTR() and READTEXT:

Notice the use of a transaction and the HOLDLOCK keyword to ensure the veracity of the text pointer from the time it’s first retrieved through its use by READTEXT. Since other users could modify the BLOB column while we’re accessing it, the pointer returned by TEXTPTR() could become invalid between its initial read and the call to READTEXT. We use a transaction to ensure that this doesn’t happen. People tend to think of transactions as being limited to data modification management, but, as you can see, they’re also useful for ensuring read repeatability.

Rather than specifying a fixed offset and read length, it’s more common to use PATINDEX() to locate a substring within a BLOB field and extricate it, like so:

Note the use of PATINDEX() to both qualify the query and set the @patindex variable. The query must subtract one from the return value of PATINDEX() because PATINDEX() is one-based, while READTEXT is zero-based. As mentioned earlier, PATINDEX() works similarly to LIKE except that it can also return the offset of the located pattern or string.

Handling larger segments requires looping through the BLOB with READTEXT, reading it a chunk at a time. Here’s an example:

(Results abridged)

The trickiest part of this query is the fact that READTEXT doesn’t allow reading past the end of the BLOB. That is, if the BLOB is 100 characters long, you can’t specify a starting point of 90 and a chunk size of 30 and expect to get the last 10 characters of the BLOB—READTEXT will return an error instead. So, the query is forced to do READTEXT’s work for it—it computes the exact size of the remainder of the BLOB and is careful not to exceed it.

This query uses the fact that SQL Server evaluates expressions left to right to keep the code as small as possible. In the initial SELECT, the @blobsize variable is used later in the SELECT list immediately after being set by the same statement. Because SQL Server evaluates the list left to right, this works. The SELECT statement within the loop employs the same technique. @chunkindex is used elsewhere within the SELECT statement that also sets its value. This behavior isn’t guaranteed to remain the same in future releases of the product, so you should use it with caution.

In the examples thus far, we’ve used HOLDLOCK to ensure that a text pointer we retrieve early in the query is still valid later—to ensure read repeatability. HOLDLOCK causes the read lock initiated by the SELECT to remain in effect until the end of the transaction. Depending on the current transaction isolation level, HOLDLOCK may not even be necessary because we’re reading the entirety of the segment we’re after and have no intention of rereading it (see Chapter 14, “Transactions,” for more information). An alternative would be to use SET TRANSACTION ISOLATION LEVEL to force the server itself to ensure repeatable reads, like so:

By telling the server to ensure the reads we perform are repeatable within the same transaction, we block other users from making changes to pr_info while we’re perusing it, which is exactly what HOLDLOCK does.

Supplying BLOB columns with text or image data that’s less than or equal to 8000 bytes in size is as straightforward as updating any other type of column. You can use INSERT, UPDATE, and DEFAULT constraints to supply these values, just as you can other types of data. Here’s an example:

Writing values larger than 8000 bytes via Transact-SQL requires the use of the UPDATETEXT or WRITETEXT command. UPDATETEXT can modify a portion of a BLOB field, while WRITETEXT rewrites its entire contents. Generally speaking, UPDATETEXT is more flexible than WRITETEXT and should be your tool of choice for writing large amounts of text or image data to a BLOB field. Here’s an example:

UPDATETEXT takes five parameters: the column to be updated, a valid text pointer to it, the offset at which the update is to occur, the number of characters to delete from the offset location, and the update text. Despite its name, UPDATETEXT deletes, then inserts the updated text. It works similarly to the Transact-SQL STUFF() function, whose purpose is to remove a segment of a string and replace it with another. Since we specified an offset and delete length of zero, the string we specified is simply inserted at the front of the text field.

As with READTEXT, valid text pointers can be acquired via the TEXTPTR() function. Transactions help ensure that a text pointer acquired via a SELECT is valid when UPDATETEXT is called. We use UPDLOCK rather than HOLDLOCK because we’re updating the data rather than merely reading it.

The real power of UPDATETEXT shows when you need to update a segment of a BLOB rather than prefix it with a new string or replace it altogether. Here’s an example:

Here, we use PATINDEX() to locate an offset within a text field, then we use UPDATETEXT to change the string at that location.

WRITETEXT works similarly to UPDATETEXT. Since it writes the entire field, it doesn’t require an offset or length parameter. Here’s an example:

Note the use of a constraint to supply a default value to the BLOB column. Since both UPDATETEXT and WRITETEXT require a valid text pointer, you can’t use them to write data to a BLOB field that’s NULL. This makes adding text to a newly inserted row more difficult than it should be. The best way to deal with this is to set up a DEFAULT constraint for the BLOB column; then, when a row is added to the table, the column will receive a valid value which you can then access via a separate TEXTPTR() query. Once you have a valid text pointer in hand, you can call UPDATETEXT or WRITETEXT to place real data into the BLOB column.