T-SQL: Compound Operators

Please see my other Database Development articles. Performing shorthand arithmetic While it’s possible to perform simple arithmetic operations by repeating the use of variables, shorthand notation provides a much more efficient way of writing the same operations. In the examples below, notice not once do I repeat a variable to perform an operation (increment, decrement,…

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…

T-SQL: WITH

Please see my other Database Development articles. Performing Recursive Queries Prior to SQL Server 2005, performing recursive actions on data required the use of temporary tables, cursive, or some other memory-intensive method. Now, performing such operations requires much less code and avoids the previous performance increase. For this example, we’ll begin with a generic table…

T-SQL: User Defined Functions

Please see my other Database Development articles. Creating reusable logic As with Object-Oriented Programming (OOP) where it’s important to separate code used frequently into reusable modules, in T-SQL the same need exists. In this example I demonstrate a user-defined function (UDF) that receive a string from which all pre-defined matching blocks of HTML are found…

T-SQL: Trigger – INSERT AFTER

Please see my other Database Development articles. Initiating actions in response to events One of the new features provided with SQL Server 2005 enables actions to be performed after an event occurs on an object, in this case the “people” table. To illustrate an “after” response, the following code creates a trigger that responds to…

T-SQL: Subquery – SELECT

Please see my other Database Development articles. Performing multiple, concurrent queries Often when needing a resultset, there’s a need to perform multiple queries to derive the desired data. In this example, we have multiple records of people, from which we need an aggregated report. In order to accomplish this goal, I must perform two separate…