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 →

When Optimization Breaks Software

May 14, 2014 Andrew Palczewski 1 Comment

It’s well known that most of the code we write is not what the computer actually runs. When writing a program, the compiler or runtime will take the code and perform a process called “optimization,” where various methods are applied to make the code more efficient so that it will execute faster.
Continue reading →

ASP.NET – Custom Query Interfaces

May 13, 2014 Andrew Palczewski Leave a comment

When developing web-based forms using the ASP.NET platform, one of the basic tools that is lacking from the framework is an effective search mechanism. Custom queries seem to have been neglected due to their apparent complexity, and their non-geometric fit into the ideology of most frameworks. Still, a good search tool is very useful to users for finding relevant data in a large database. As such, we have developed a few tools and methodologies to make search implementation easier in web applications.
Continue reading →

Database Design Principles – Part 5: Isolate Semantically Related Multiple Relationships

May 12, 2014 Andrew Palczewski 1 Comment

In the previous database design post, we discussed isolating independent multiple relationships. The goal was to find relationships in the database that were independent, yet incorrectly bound to the same row. This last and final database design principle helps us find data that is too flexible, and can instead be combined to help reduce database size and optimize speed.
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 →