T-SQL: String Search

Returning selection portions of values in a resultset. When searching for specific values within a field, returning portions surrounding the search criteria may be very helpful for providing the user a context in which the value in being used. In this example, I will illustrate searching within a “stories” table that contain strings about someone…

T-SQL: Stored Procedure Default Values

Building flexibility into data INSERT operations. Often when designing an application that receives user input, it’s difficult to determine beforehand exactly which fields will be supplied where a user completes a form. SQL Server provides the ability to supply default values for parameters of stored procedures to allow for more flexibility when supplying values to…

T-SQL: Binary_Checksum()

Performing case-sensitive String Comparison While using other string functions or operators may be used to compare strings, the most effective method remains the system function binary_checksum. Here’s an example of how it may be used.

T-SQL: Pivot

Dynamically restructuring report data. Often when creating reports, data is received in varying formats, some of which make it difficult to create a visually compelling format. One example of such data is when the structure itself is embedded within the data. As you see below, the initial record set begins with a company’s data. The…

T-SQL: Parsing Values

Updating a field with only a portion of its original value. String functions provide powerful methods for manipulating values in recordsets. Querying and changing only a portion of a field’s original data is a common requirement from the data layer. In this example, I have a standard “pages” table that might contain items common to…

T-SQL: NTile

Ranking Functions There are several new impressive advancements in the latest version of t-sql; this article will focus on “Ranking” functions. Prior to the release of SQL Server 2005 with its t-sql enhancements, working with blocks of related data was clunky at best. T-sql authors mostly relied on “GROUP BY” statements and then had to…

T-SQL: Ranking Functions

Ranking Functions There are several new impressive advancements in the latest version of t-sql; this article will focus on “Ranking” functions. Prior to the release of SQL Server 2005 with its t-sql enhancements, working with blocks of related data was clunky at best. T-sql authors mostly relied on “GROUP BY” statements and then had to…