T-SQL: Row_Number

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: 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: 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: Complex Queries

Please see my other Database Development articles. Advanced Concepts Apart from some of the latest advancements in t-sql, Ranking Functions for example, the t-sql query language provides the ability to perform some rather complex operations with data being returned from a query. TOP WITH TIES A fairly routine task most db developers are requested to…

T-SQL: Checksum

Please see my other Database Development articles. Constructing a hashtable with recordsets Often times when building complex queries, it becomes difficult linking different sets of data using relationships due to the relative differences which may exists in the data. For example, let’s pretend we have two different sources of data which only share a “first…

T-SQL: Bitwise Arithmetic

Please see my other Database Development articles. Using bits as flags. Recording user settings, often as “categories” represents significant persistence within many applications. Storing all these values within separate variables would waste valuable memory as well violate the Don’t Repeat Yourself (DRY) principle, producing hard-to-maintain code as well as tedious to understand. We’ll begin our…