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…

xSQL Data Compare

Please see my other Database Development articles. Managing Database Change While the xSQL package also comes with xSQL Object Compare, an impressive tools for managing changes between database instances, this article will focus solely on the product used for managing data changes, xSQL Data Compare. Produced by xSQL, xSQL Data Compare represents a robust application…

SQL Server: SSMS Object Explorer Filter

Please see my other Database Development articles. Leveraging the Object Explorer Filter to work more efficiently. The SQL Server Studio Manager’s primary interface to database objects is the object explorer. While this tool is very powerful, the results it displays can be quite overwhelming, especially in a production environment. To make locating objects more efficient,…

Normalization

Please see my other Database Development articles. Introduction Before I define the process of designing a database, or “normalization,” I think some background is essential. In most implementations of SQL Server databases, the system they are designed to support are generally defined in two broad categories: Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP).…

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…