Attaining reusability in SQL server: part II

This is a follow-up to a post made a few weeks ago that you can find here.

Maybe you believe me that some of the patterns I outlined last time are worth looking at, but you’re still not sold since you haven’t seen the implementation yet. I don’t blame you. I have to see a few examples of something new before I buy into it.

Today I’m going to take you through a very simple example of using UDTTs and TVFs across simple stored procedures to do some basic statistics gathering. I won’t go into specifics about performance as I think the supporting articles I listed last time have enough evidence to convince you that TVFs are performant.

Instead, I will be focusing on the details needed to fully start using TVFs and UDTTs in a reusable way. Let’s get started.

The scenario

Let’s say we have a really basic order database. We have a base table called orders (I know, I know. My naming convention is a little crazy..), that has local_orders,  domestic_orders and  international_orders indicated by an order_type  (1, 2, and 3 respectively).

These orders move along the order process via an order_status column that for simplicity has order_placed, order_fulfilled, and order_shipped (again 1, 2, and 3 respectively). We also have a timestamp for when the order is placed, fulfilled, and shipped.

Let’s also say we want to provide a dashboard with metrics per order type that are quickly accessible. We don’t want to have to run large queries whenever an administrator goes to see these metrics. In order to provide this in such a way that we don’t have to query the orders table every time, we can gather smaller snapshots of statistics and store them in another table order_stats.

The implementation

This type of scenario works really well for the objects and patterns I have talked about. To implement, we will need the following:

• A TVF for gathering statistics from the orders table
• A stored procedure for taking the values returned from the function above and store them in the order_stats table
• A TVF for gathering statistics from a set of records from the order_stats table
• A UDTT that we can pass to the function above that mimics the order_stats table.

The key component again to make some of this work is the TVF function that accepts a temporary table of the UDTT we will create. In my example, these are the tvf_gather_analytics function and the udtt_order_stats UDTT. The  tvf_gather_analytics takes in an argument named @stats which is of the udtt_order_stats type. The actual code the the function is below:

--
-- Create a TVF (table-valued user-defined function) for performing some basic 
-- statistics on the stats contained in the @stats table. The @stats table MUST
-- be of the UDTT type 'udtt_order_stats'. By passing in a table, we can peform 
-- these statistics based on any 'set' of data the function gets passed in.
--
CREATE FUNCTION [dbo].[tvf_gather_anayltics] (@stats udtt_order_stats READONLY)
RETURNS TABLE AS
RETURN (
	SELECT
		stat.order_type AS 'order_type',
		MAX(stat.new_order_count) AS 'highest_new_order_count_per_snapshot',
		AVG(CAST(stat.new_order_count AS NUMERIC(19,2))) AS 'mean_new_order_count_per_snapshot',
		SUM(stat.new_order_count) AS 'sum_new_order_count',
		MAX(stat.fulfilled_order_count) AS 'highest_fulfilled_order_count_per_snapshot',
		AVG(CAST(stat.fulfilled_order_count  AS NUMERIC(19,2))) AS 'mean_fulfilled_order_count',
		SUM(stat.fulfilled_order_count) AS 'sum_fulfilled_order_count',
		MAX(stat.shipped_order_count) AS 'highest_shipped_order_count_per_snapshot',
		AVG(CAST(stat.shipped_order_count  AS NUMERIC(19,2))) AS 'mean_shipped_order_count',
		SUM(stat.shipped_order_count) AS 'sum_shipped_order_count',
		COUNT(1) AS 'stats_present',
		MIN(stat.snapshot_start_time) AS 'stat_start_time',
		MAX(stat.snapshot_end_time) AS 'stat_end_time'
	FROM
		@stats stat
	GROUP BY 
		stat.order_type WITH ROLLUP
)
GO

Full example

The full example can be downloaded here.

The create_database_ExampleDB.sql should be the only script you need to set up the database and create all of the necessary objects. All of the specific objects are contained as well as separate scripts if you want to review them more closely. There is also an examples.sql script that should take you through inserting some data and using each of the objects discussed.

Hope this small example has been helpful. There are tons of other scenarios and use cases that this set of tools can help you address (we use it internally for rating calls!). I’m sure if you look hard enough at your database, you can find a place to put this into practice.