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…

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…