Software Engineering: Just How Immature is it?

"Facts and Fallacies of Software Engineering" by Robert L. Glass has become a classic of Software Engineering as cherished as 'The Mythical Man-Month: Essays on Software Engineering' by Frederick P. Brooks. They seem as radical today as when first written, mainly because the software industry repeatedly fails to learn from its mistakes. Dwain Camps reviews the book.… Read more

Using the T-SQL PERCENTILE Analytic Functions in SQL Server 2000, 2005 and 2008

Percentiles give meaning to measurements by telling you the percentage of the population being measured who get higher or lower values. They are now easier to calculate in SQL, and are useful for reporting; but are the new analytic functions faster and more efficient than the older methods? Dwain Camps demonstrates, and investigates their relative performance performance.… Read more

Time Slots – An Essential Extension to Calendar Tables

After answering many forum entries from developers asking for help with dealing with SQL that involved time intervals and ranges, Dwain dreamed of a generalized tool that sets up time slots of various sorts without the need to experiment; that could do the heavy lifting, so that developers could do aggregations and reports based on time intervals without the hard graft. Here is Dwain's dream made reality. … Read more

Self-maintaining, Contiguous Effective Dates in Temporal Tables

'Temporal' tables contain facts that are valid for a period of time. When they are used for financial information they have to be very well constrained to prevent errors getting in and causing incorrect reporting. This makes them more difficult to maintain. Is it possible to have both the stringent constraints and simple CRUD operations? Well, yes. Dwain Camps patiently explains the whole process.… Read more

Archiving Hierarchical, Deleted Transactions Using XML

When you delete a business transaction from the database, there are times when you might want to keep a record of the data for posterity. This can become somewhat complicated if the transaction you need to delete is in a table that is the parent of a deeply nested hierarchy of dependent tables based on the foreign key relationships. In this article, Dwain Camps looks at a tidy means of doing just that.… Read more

Using Stored Procedures to Provide an Application’s Business-Logic Layer

When faced with a complex business application that had to be delivered with minimum staffing, on-time and within budget, Dwain's team chose to encapsulate and implement most of an application's business logic in SQL Server, using an interface made up of stored procedures. Without this approach, the team was convinced that it would not have been possible to deliver that level of business logic complexity within the timeframe. Dwain explains how it was done, and provides a template.… Read more

Filling In Missing Values Using the T-SQL Window Frame

Since SQL Server delivered the entire range of window functions, there has been far less justification for using the non-standard ex-Sybase 'Quirky Update' tricks to perform the many permutations of running totals in SQL Server. One of these related problems is the 'Data Smear'. Do window functions make this easier, and what is performance like? Dwain Camps investigates… Read more

Calculating and Verifying Check Digits in T-SQL

A lot of numbers that we use everyday such as Bank Card numbers, Identification numbers, and ISBN codes, have check digits. As part of the routine data cleansing of such codes on data entry we must check that the code is valid- but do we? Dwain Camps shows how it can be done in SQL in such a way that it could even be used in a constraint, to keep bad data out of the database.… Read more

Writing Outstanding Proposals

Oftentimes you will be forced to learn how to write proposals without a whole lot of help. You can learn, and be taught, the skill of writing an outstanding proposal, but you can't do it without a fair amount of practice. Today, Dwain explains how to write proposals that can be judged to be outstanding and what, specifically, that means.… Read more

High Performance Relational Division in SQL Server

Relational division is used in SQL to select rows that conform to a number of different criteria. It is a neglected but effective technique for a number of tasks. Although the SQL can appear daunting and complex, it can perform very well if you reduce the rows as much as possible before applying the final logic. Dwain Camps explains how, and shows the performance gains.… Read more

Calculating the Median Value within a Partitioned Set Using T-SQL

It is ironic that one of the most essential of statistical aggregations, the median, has been so difficult in the past to calculate efficiently in SQL. Although the recent window functions provide the solution, there isn't an obviously superior algorithm performance-wise, particularly when working across partitioned sets. Dwain Camps sets the candidates to work and identifies the winners and losers.… Read more

The Proposals Conundrum

When you work for a small software development (or any services) company, one of the major challenges is to make sure that you expend your limited resources on opportunities that are economically sound. You may be approached by companies that have heard about you and think they might want to do business with you, but do these leads really represent opportunities? How much of your time should be spent finding out? Dwain Camps offers some guidance.… Read more

Condensing a Delimited List of Integers in SQL Server

In real-world applications, it often makes sense to show denormalized data such as delimited lists within the application's user interface. Dwain Camps shows why, and how, the distribution business stores information about 'islands' in sequences in order to track the status of the shipping of a consignment. It makes a great SQL puzzle … Read more