Dwain Camps

Dwain Camps has been a project manager for many years. Because performance of applications can be a critical success factor for projects, he has been evangelizing on the need to develop highly performing SQL. By mentoring and authoring articles on SQL, he hopes to train a future generation of software engineers on the right and wrong ways to deliver SQL code. He also has a special interest in developing solutions to complex, data intensive problems using high performance SQL because the declarative nature of SQL allows development of algorithmically unique solutions that procedural languages may not be capable of.

Follow Dwain Camps via
12 June 2015
12 June 2015

Software Engineering: Just How Immature is it?

0
11
"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
0
11
18 May 2015
18 May 2015

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

0
13
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
0
13
22 April 2015
22 April 2015

Time Slots – An Essential Extension to Calendar Tables

0
30
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
0
30
10 March 2015
10 March 2015

Self-maintaining, Contiguous Effective Dates in Temporal Tables

0
9
'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
0
9
30 December 2014
30 December 2014

Archiving Hierarchical, Deleted Transactions Using XML

0
26
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
0
26
14 November 2014
14 November 2014

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

0
34
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
0
34
18 September 2014
18 September 2014

Filling In Missing Values Using the T-SQL Window Frame

0
32
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
0
32
11 July 2014
11 July 2014

Calculating and Verifying Check Digits in T-SQL

0
36
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
0
36
20 May 2014
20 May 2014

Writing Outstanding Proposals

0
4
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
0
4
24 March 2014
24 March 2014

High Performance Relational Division in SQL Server

0
21
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
0
21
17 December 2013
17 December 2013

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

0
22
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
0
22
19 September 2013
19 September 2013

The Proposals Conundrum

0
2
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
0
2
28 August 2013
28 August 2013

Condensing a Delimited List of Integers in SQL Server

0
15
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
0
15