T-SQL: String Search

Please see my other Database Development articles. 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…

T-SQL: Stored Procedure Default Values

Please see my other Database Development articles. 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…

T-SQL: Binary_Checksum()

Please see my other Database Development articles. 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

Please see my other Database Development articles. 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…

T-SQL: Parsing Values

Please see my other Database Development articles. 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”…

T-SQL: NTile

Please see my other Database Development articles. 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…

T-SQL: Ranking Functions

Please see my other Database Development articles. 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…

T-SQL: Divide

Please see my other Database Development articles. Performing division on integer values. Often, recordsets contain values that naturally reveal metrics that provide helpful insight into the data. In this example, I list records about people. Each record contains information about a person’s age, gender and name. First, I list each person’s age, the sum of…

T-SQL: DELETE FROM

Please see my other Database Development articles. Performing DELETE using a JOIN One of the most common actions necessary when deleting records is to delete some using relationships. One example of deleting via a relationship is utilizing a JOIN to isolate a recordset targeted for deletion. In this example, I’ll first create two temporary tables…

T-SQL: Cursor

Please see my other Database Development articles. Performing iterative actions. Update: SQL Server 2008 introduced a new feature to T-SQL, Common Table Expressions (CTEs) which is meant to replace the use of cursors. For more information read my WITH article. Sometimes business requirements demands that complex actions must be performed on recordsets in such a…