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…

T-SQL: Cross-Database Logic

Please see my other Database Development articles. Executing T-SQL against different databases Often when accessing and manipulating data, requirements demand logic that crosses databases. Performing such actions do not require drastically different T-SQL, only an understanding of the syntax. In this example, I begin with two databases with identical tables and nearly identical records. For…

T-SQL: Control Flow

Please see my other Database Development articles. Implementing decision-making into logic The ability to control how logic is executed is central to all logic within the data layer. Within T-SQL, the following examples illustrates the primary constructs used for this purpose. Looping: The WHILE keyword provides the ability to execute deterministic actions for a prescribed…