Tag Archives: Software Sagacity

How Correlated Subqueries Work

September 10, 2014 Andrew Palczewski Leave a comment

As the workhorse of data analytics and reporting, the SQL language enables both users and developers slice-and-dice a database for desired information. With a natural-language-based syntax, SQL is relatively easy for business analysts to learn for basic database analysis. As analysts become more comfortable with the language, however, they may require more advanced techniques, such as aggregates on subsets of data within a query. In these situations, correlated subqueries can bring new capability and virtually unlimited depth to a SQL statement.
Continue reading →

SQL Server Database Techniques – Using Arrays in TSQL

June 17, 2014 Andrew Palczewski Leave a comment

Prior to SQL Server 2008, there was no straightforward way to pass arrays in TSQL. While programming languages such as Perl, C# and Ruby adopted array map functions as the lifeblood of the language, SQL Server’s TSQL stayed behind. Fortunately, the functionality is now present in most SQL Server instances, and can be leveraged for interesting new SQL constructs.
Continue reading →

Database Design Principles – Part 4: Isolate Independent Multiple Relationships

May 9, 2014 Andrew Palczewski Leave a comment

In part 4 of this 5-part series on database design principles, we review Independent Multiple Relationships. By isolating relationships so that the database structure is fully hierarchical, the software will be more adaptable to future user needs, and require less reprogramming when the requirements will inevitably change in the future.
Continue reading →

Database Design Principles – Part 3: Eliminate Columns Not Dependent on Key

May 8, 2014 Andrew Palczewski Leave a comment

In the third of this five part database design series, we review the database design rule of removing columns not dependent on the key. Similar to removing redundant data, this rule will make the database more flexible and maintainable throughout the lifecycle of the system.
Continue reading →

Database Design Principles – Part 2: Eliminate Redundant Data

May 7, 2014 Andrew Palczewski 1 Comment

In the second of this five part database design series, we continue to explore improvements to the database schema. These improvements will help create more flexible software, reduce maintenance and support costs, and improve scalability. The second principle of database design is to eliminate redundant data.
Continue reading →

Database Design Principles – Part 1 of 5: Eliminate Repeating Groups

May 6, 2014 Andrew Palczewski 1 Comment

With the large number of software applications in the wild, from desktop, to web, to mobile, the vast majority employ some form of database. As development teams get smaller and consist of fewer seasoned professionals, the databases that run these applications are often poorly designed, inflexible, and difficult to scale. By following a few core principles of database design, however, many of these databases could be substantially improved. The first and foremost rule of good database design: eliminate repeating groups.
Continue reading →

SQL Server Development Tips – Automated, Incremental Builds

April 23, 2014 Andrew Palczewski 3 Comments

The black-box mechanics of SQL Server can make database versioning a challenge. Traditional approaches include keeping a log of all structural SQL changes, or taking backup snapshots at development milestones. The primary weaknesses of these approaches are the lack of flexibility and prototyping with the first, and deployment challenges with the latter. Herein we present a solution to the challenge using version control and automated, incremental build scripts.
Continue reading →