My Presentations

T-SQL Tips & Tricks

Abstract

Do you ever wonder how you can make T-SQL do what you really want? Do you want to know how to make it jump through loops, perform amazing twists and turns, and execute other wondrous performances? In this session, you’ll learn about various features such as CTE, ROW_NUMBER, and other great features that will enable you to make your T-SQL queries and stored procedures more efficient and give you the results you really want.

Presented at:

• MichianaPASS, South Bend, IN on May 29, 2017
• SQL Saturday #514, Houston, TX on May 14, 2016
• SQL Saturday #357, Cleveland, OH on Feb 7, 2015
• SQL Saturday #304, Indianapolis, IN on Aug 9, 2014 (Part 2)
• SQL Saturday #190, Denver, CO on Sep 28, 2013 (Part 1)
• SQL Saturday #246, Salt Lake City, UT on Sep 7, 2013 (Part 2)
• SQL Saturday #242, Indianapolis, IN on Aug 10, 2013 (Part 1)
• SQL Saturday #153, Salt Lake City, UT on Oct 20, 2012 (Part 1)
• IndyPASS, Indianapolis, IN on Jan 17, 2012 (Part 1)

The Lost Science of Set Theory

Abstract

Many database developers today do not have a Computer Science degree and may also have begun their career developing software using procedural programming languages (Java, C#, etc.). As a result, they may not have the background needed to conceptualize solutions in terms of sets of data and therefore may not be able to develop efficient set-based queries. In this session, we will review the basics of set theory, and relational algebra and how they apply to database development to turn cursors, loops, and other procedural programming practices into efficient set-based operations that make the most use of a relational database engine.

Presented at:

• Greater Lansing SQL Server (GLASS) & SQL PASS in Detroit (SPID), Michigan (Detroit & Lansing) on Jun 16, 2020 (Virtual)
• SQL Saturday #887, Salt Lake City, UT on Aug 2, 2019
• The Church of Jesus Christ of Latter-day Saints, Riverton, UT on Jun 14, 2019
• Utah County SQL Server Users Group, American Fork, UT on Feb 21, 2019
• SQL Saturday #600, Chicago, IL on Mar 11, 2017
• SQL Saturday #572, Portland, OR on Oct 22, 2016
• SQL Saturday #557, Minneapolis, MN on Oct 1, 2016
• SQL Saturday #531, Louisville, KY on Aug 6, 2016
• SQL Saturday #527, Columbus, OH on Jul 16, 2016
• SQL Saturday #521, Atlanta, GA on May 21, 2016
• SQL Saturday #473, Cleveland, OH on Feb 6, 2016
• SQL Saturday #436, Las Vegas, NV on Sep 12, 2015
• SQL Saturday #349, Salt Lake City, UT on Oct 25, 2014
• SQL Saturday #331, Denver, CO on Sep 20, 2014
• IndyPASS, Indianapolis, IN on Sep 16, 2014
• SQL Saturday #304, Indianapolis, IN on Aug 9, 2014
• SQL Saturday #308, Houston, TX on May 10, 2014

Battle Scars: Porting a SQL Server Application to Netezza

Abstract

Battle scars are proof of the struggles endured and won. Find out about one particular battle porting a SQL Server application to Netezza. In this session, we’ll review Big Data options and their differences. We’ll specifically see how Netezza and other Massively Parallel Processor (MPP) systems differ from Symmetric Multi-Processor (SMP) systems like SQL Server and witness the lessons learned in the struggle.

Presented at:

• SQL Saturday #643, Louisville, KY on Aug 5, 2017
• SQL Saturday #557, Minneapolis, MN on Oct 1, 2016
• Salt Lake City SQL Server Users Group and Big Mountain Data Utah Users Group on Sep 9, 2015 (video)
• SQL Saturday #402, Indianapolis, IN on Aug 8, 2015

XML Xtreme!

Abstract

We’ve all had to work with XML at some point, and you may have even searched XML documents in a text editor. However, did you know you can query XML documents within SQL Server? In this session, I’ll show you how you can query XML documents as a data source alongside SQL Server tables and how to optimize your queries for better performance. You’ll learn how to check conditions that determine if the data you need exists and how to deal with incomplete or missing data.

Presented at:

• Greater Lansing SQL Server (GLASS) & SQL PASS in Detroit (SPID), Michigan (Detroit & Lansing) on Oct 25, 2023 (virtual)(video)
• Salt Lake City SQL Server Users Group on Oct 17, 2016
• SQL Saturday #530, Indianapolis, IN on Aug 13, 2016

How to Give a Technical Presentation

Abstract

Most people don’t like speaking in public, and technical folks are no exception. Yet, you’ve got some ideas for a great presentation but don’t know how to put it all together. On top of that, how do you give the presentation itself? This session will cover how to pull your material together in an organized fashion and present it such that your audience can follow along and get the message you want to relay. We’ll also discuss how to deal with issues that cause presenters stress to help you make the process enjoyable and rewarding for all.

Presented at:

• SQL Saturday #1060, Baton Rouge, LA on Jul 29, 2023
• Data Saturday #8, Southwest (Albuquerque & Santa Fe) on May 15, 2021 (Virtual)
• Big Mountain Data & Dev, Salt Lake City, UT on Oct 22, 2020 (virtual)(video)
• SQL Saturday #951, Tampa, FL on Feb 29, 2020
• SQL Saturday #653, Columbus, OH on Jul 22, 2017
• SQL Saturday #595, Cleveland, OH on Feb 4, 2017
• IndyPASS, Indianapolis, IN on Dec 20, 2016

“There Be Whales Here!” — Big Data in SQL Server

Abstract

Due to varying definitions of Big Data, which confuse data volume and organization, the common perception is that Big Data requires using Hadoop, MongoDB, or another NoSQL database. SQL Server can easily store and manipulate large volumes of data and perform very well — provided the database is set up and tuned correctly. In this session, we’ll cover some of the definitions and misconceptions of Big Data, look at some Big Data solutions that went wrong, and review a case where Big Data was successfully implemented in SQL Server. Finally, we’ll look under the covers to see how SQL Server best handles Big Data through proper data models, table designs, performance tuning, and T-SQL techniques.

Presented at:

• Utah Data Engineers Meetup, Draper, UT on Oct 18, 2023
• SQL Saturday #946, Ft. Lauderdale, FL on Feb 22, 2020
• SQL Saturday #733, Atlanta, GA on May 19, 2018
• Saturday Night SQL, Virtual Chapter on March 10, 2018
• PASS Summit 2017, Seattle, WA on November 3, 2017 (video)
• CincyPASS, Cincinnati, OH on July 18, 2017
• Ohio North SQL User’s Group, Cleveland, OH on May 2, 2017

Introduction to Relational Database Design Patterns

Abstract

Using design patterns has been helpful for object-oriented development to identify best practices for various situations. Unfortunately, this practice has not reached the world of relational database architecture, where they are just as applicable. In this session, we’ll discuss some techniques and best practices that help make database design more effective and efficient in operation, design, and administration. Using some example scenarios, you’ll walk away with a better understanding of design patterns that you can apply immediately!

Presented at:

• Pinellas SQL Server User Group, Clearwater, FL on May 19, 2020 (virtual)
• SQL Saturday #629, Indianapolis, IN on Aug 12, 2017
• SQL Saturday #652, Atlanta, GA on Jul 15, 2017

Discover the Hidden Mysteries of Common Table Expressions

Abstract

Although it has been around for some time, the Common Table Expression (CTE) has been a complete mystery to many. As a result, there are many misunderstood myths and assumptions surrounding them. This session will explore CTEs, what they are, what they are not, and how they work. We’ll look at situations where they can help improve code clarity and performance, especially when compared side-by-side to usual methods. We’ll also identify scenarios where CTEs are incorrectly used, causing hidden performance problems.

Presented at:

• SQL Saturday #745, Indianapolis, IN on Aug 11, 2018

Tally Ho! — Explore the Varied Uses of Tally Tables

Abstract

Because SQL Server is designed to operate with set-based queries efficiently, it does not perform as well with loops and cursors. However, there are situations where you need some type of a loop. Tally tables give you the best of both worlds by allowing you to effectively “loop” through records in a set-based manner. We’ll explore what tally tables are, how they work, and, more importantly, review how to use them to turn your loops and cursors into efficient set-based operations.

Presented at:

• SQL Saturday #1021, Orlando, FL on Oct 30, 2021
• IndyData (formerly IndyPASS), Indianapolis, IN on Jan 19, 2021 (virtual)
• SQL Saturday #951, Tampa, FL on Feb 29, 2020
• SQL Saturday #908, Denver, CO on Oct 12, 2019
• Salt Lake City SQL Server User’s Group, Murray, UT on May 8, 2019 (video)
• SQL Saturday #801, Orlando, FL on Oct 6, 2018

Defining What’s Normal — The Basics of Database Normalization

Abstract

Determining how to design database structures efficiently for a given application has always relied on the same old answer: “It depends!” So, how do you determine what level of normalization your data requires? This session will review the principles of data normalization, show the impact that normalization can have on performance and data integrity, and, just as importantly, show you when it’s OK to break the rules and denormalize. We will cover some cases where it’s better not to be normal!

Presented at:

• Big Mountain Data & Dev Conference 2023, Salt Lake City, UT on Nov 10, 2023
• Big Mountain Data & Dev Conference 2022, Salt Lake City, UT on Nov 10, 2022
• Data Geeks Saturday 2021, South Florida on Aug 7, 2021 (virtual)
• SQL Saturday #920, Portland, OR on Nov 2, 2019

A Practical Guide to Set-Based Queries

Abstract

T-SQL queries, stored procedures, and poorly written functions are the main contributors to performance degradation in SQL Server. SQL Server is optimized for set-based processing over procedural processing. The sad reality is that too much code is written from a procedural point of view because many database developers started as procedural programmers. Also, many applications use SQL Server as data storage and do not take advantage of its incredible capabilities. This session will examine what set-based queries are, how to identify inefficient procedural processes, and how they can be rewritten as efficient set-based queries.

Presented at:

• PASS Data Community Summit 2024, Seattle, WA on Nov 6, 2024
• Greater Lansing SQL Server (GLASS) & SQL PASS in Detroit (SPID), Michigan (Detroit & Lansing) on Jun 10, 2021 (virtual)

SQL Team Six: How to Build Effective Teams

Abstract

Teamwork is essential in all areas of our work life, including how we interact with other teams daily. Lessons learned from highly effective teams, such as SEAL Team Six, show six significant impacts on effectiveness:

• Chain of Command
• Team Cohesion
• Standard Operating Procedures
• Training
• Mission Objectives
• After-Action Analysis

Failure in any of these areas can impact team effectiveness. This session explores these six areas to understand what can build or destroy team effectiveness. The exercises and discussions teach several techniques that will enable you to become better leaders, build better teams, and be more effective team members.

Presented at:

• Postgres Conference 2024, Seattle, WA on Nov 7, 2024
• SQL Saturday #1082, Oregon (Vancouver, WA) on Nov 3, 2024
• SQL Saturday #1057, Oregon (Vancouver, WA) on Nov 11, 2023
• SQL Saturday #1047, Orlando, FL on Oct 7, 2023
• Silicon Valley Postgres Conference 2023, San Jose, CA on Apr 21, 2023
• Big Mountain Data & Dev Conference 2022, Salt Lake City, UT on Nov 10, 2022
• Salt Lake City SQL Server Users Group and Utah County Users Group on Jun 16, 2022 (virtual)
• PASS Data Community Summit, Nov 10-12, 2021 (On-Demand) (video)

Discover the Power of Window Functions

Abstract

Window functions have nothing to do with the Windows operating system but provide a “window” to a result set when used in SQL queries. They are powerful, set-based methods to view data in unexpected ways. Window functions provide easy, set-based solutions to solve some common problems:

• Using running totals
• Performing operations in intervals
• Identifying data gaps and islands
• Performing aggregates without losing detail

Many people are confused by window functions and do not know how to use them properly. This session will explore window functions, how they work, and how to use them. Demonstrations with several examples will solidify how window functions can simplify queries and make them more powerful.

Presented at:

• PASS Data Community Summit, Seattle, WA on Nov 17, 2023
• SQL Saturday #1060, Baton Rouge, LA on Jul 29, 2023
• Columbus SQL Saturday 2023, Westerville, OH on Jul 15, 2023
• Silicon Valley Postgres Conference 2023, San Jose, CA on Apr 21, 2023

Data Governance Begins with Data Architecture

Abstract

Most organizations who successfully recognize a need for a data governance program do so after the fact – after all critical source systems are in operation. They may feel they are done once they have a data catalog in place and their data governance goals have been met. However, proper data governance is not just cataloging data assets. Having the catalog on the virtual bookshelf does not do anyone any good, regardless of how much attention is given to it. Proper data governance is proactive and guides data projects before, during, and after implementation. In most industries, it is important, whereas in healthcare, it is critical! It all begins with data architecture before systems are designed or purchased. This session will address many of the questions you have:

• Who Should Be Involved?
• What Is Data Governance?
• When Should Data Governance Begin?
• Where Is Data Governance Needed Most?
• Why Begin with Data Architecture?
• How Do I Get Started?

Presented at:

• Database Trends and Applications Data Summit, Boston, MA on May 8-9, 2024
• Enterprise Data World 2023, Anaheim, CA on Sep 19, 2023

How to Change SQL from Procedural to Set-Based

Abstract

Most relational databases (like SQL Server, Postgres, etc.) are designed around processing data in sets. This is evident with the declarative nature of Structured Query Language(SQL). Yet, many developers need help transitioning from the procedural programming methodologies they learned in school or while working in procedural languages. Therefore, how can we turn database stored procedures and functions that were written procedurally into more efficient set-based processing? This session will review those issues and help identify helpful tips while working through a real-life example in SQL Server.

Presented at:

• • SQL Saturday #1079 Richmond, VA on May 18, 2024
  • SQL Saturday #1059, Salt Lake City, UT on April 5, 2024

An Enterprise Data Architecture Framework

Abstract

One of the challenges facing enterprise architecture is maintaining consistency across the enterprise. This is challenging because data comes from disparate sources and systems representing different purposes, focuses, and objectives. On top of that, there needs to be more clarity regarding the terms Data Lake, Data Warehouse, Operational Data Store, etc. To overcome these challenges at an enterprise level, a framework is necessary to apply data uniformly, consistently, and meaningfully. The framework will transform data from multiple, disparate data sources into an operational data store, a consistent, homogeneous environment, as the single dependable source of truth for all reporting and analytics. This session will present that framework and discuss the following:

• The challenges facing data analytics and reporting due to multiple disparate data sources
• An introduction to the various terms used today for data storage
• The purpose of an Operational Data Store and how it improves data usability
• The data zones for Raw, Refined, and Published data
• How data standards simplify and enhance data usability
• A method to provide primary and foreign keys across disparate data

Presented at:

• Future Data Driven Summit 2024 (virtual) on September 25, 2024