The following presentations were given at events sponsored by SQL PASS:

Mar 2013 (RI BI USERS GROUP)

Topic:

Effective Data Warehouse Storage Patterns

Abstract:

Many companies start off with a simple data mart for reporting. As the company grows, users become dependent on the data mart for monitoring and making decisions on Key Performance Indicators (KPI).

Unexpected information growth in your data mart may lead to a performance impacted reporting system. In short, your users will be lining up at your cube for their daily reports.

How do you reduce the size of your data mart and speed up data retrieval?

This presentation will review the following techniques to fix your woes.

Coverage:

1 – What is horizontal partitioning?
2 – Database sharding for daily information.
3 – Working with files and file groups.
3 – Partitioned views for performance.
4 – Table and Index partitions.
5 – Row Data Compression.
6 – Page Data Compression.
7 – Programming a sliding window.
8 – What are Federations in Azure SQL?

presentation bundle

Sep 2011 (SNESSUG) / Mar 2013 (SAT 184) / Apr 2013 (SAT 203)

Topic:

How to audit and prevent unwanted user actions.

Abstract:

Many corporations are composed of small divisions located in countries throughout the world. While you might be the lead DBA for your corporation, there are several other employees who have the keys to the kingdom. This presentation will review techniques on how to prevent and/or audit data and schema changes.

Coverage:

1 – Granting correct user access is vital.
2 – DML triggers to keep a DATA audit trail.
3 – DDL triggers to keep a SCHEMA audit trail.
4 – Preventing unwanted DATA modifications.
5 – Preventing unwanted SCHEMA changes.
6 – Preventing table TRUNCATIONS.
7 – LOGIN triggers to control who, what, and when.
8 – Change data capture (CDC) an alternative to triggers.

Details:

presentation bundle

Dec 2012 (SNESSUG)

Topic:

Don’t lose your integrity

Abstract:

This is a lightning talk covering database integrity.

I will be covering these various T-SQL techniques to enforce database integrity. A toy databases called AUTOS will be implemented two ways: one without integrity and one with integrity. The issues with the first design will be examined and corrected with the second design.

Coverage:

1 – Primary Keys.
2 – Foreign Keys.
3 – Unique constraints.
4 – Check constraints.
5 – Default constraints.
6 – Data type constraints.
7 – DML Triggers.

Details:

snessug bundle

Oct 2012 (SNESSUG) / Oct 2012 (SAT 146)

Topic:

Effective use of temporary tables

Abstract:

Every developer eventually comes against business logic that can’t be handled with a single simple or complex query. TSQL provides the developer with several constructs that can store temporary result sets that are passed to the next query in the script.

I will be covering these various TSQL techniques with examples using the [AdventureWorks] database as well as a toy database called AUTOS. The pros and cons of each construct will be examined. Advanced options like enabling trace flag 1118 will be explored.

Coverage:

1 – Derived tables.
2 – Local temporary tables.
3 – Global temporary tables.
4 – Table variables.
5 – Common Table Expressions (CTE).
6 – User databases in [tempdb].
7 – Trace Flag 1118.

Details:

presentation bundle

Jun 2012 (SNESSUG) / Aug 2012 (SAT 158) / Sep 2012 (SAT 156)

Topic:

Full Text Indexing Basics

Abstract:

Today’s large data fields (LDF) are full of unstructured information stored in varchar, text, varbinary or xml data types. How do you write an application to search the column for patterns? Traditional SQL techniques using a column INDEX and LIKE operator result in a query plan that contains a full table scan.

I will be introducing the brother’s grimm database that has the full text of each fairy tale. I will create a full text catalog / index, select a change tracking strategy, define optional stop list / thesaurus file, and then populate the index. I will use CONTAINS and FREETEXT operators in SELECT queries to leverage the FTI. This resulting query plan performs index seek.

Coverage:

1 – Creating a database from scratch.
2 – Creating a table with LOB field.
3 – Loading files via BULK INSERT.
4 – Performance via traditional techniques.
5 – Creating a full text index.
6 – Performance with the full text index.

Details:

presentation bundle

August 2011 (SNESSUG)

Topic:

Working with bit patterns.

Abstract:

In today’s manufacturing environment, production lines are automated with robotics and sensors. Many of these low end microprocessors and/or integrated circuits are designed for specific tasks such as temperature and pressure control. This presentation will review how to store the memory buffer in a table and use a view to interpret the results.

Coverage:

1 – Using the VARBINARY data type to store the bit pattern.
2 – Storing a version number just in-case the decode changes.
3 – Breaking the pattern into registers and nibbles.
4 – Left shifting of the bits.
5 – Right shifting of the bits.
6 – Combining bits from two registers.
7 – Putting it all together with a view.

Details:

presentation
microprocessor layout example
script

May 2011 (SNESSUG)

Topic:

Designing Custom Maintenance Plans with TSQL.

Abstract:

Have you ever received a call from a client asking for help when their SQL Server database is corrupted? You suggest that they restore the database from the last good backup. There is a long pause on the phone as the client states that the only backups/tapes they have are bad. Do not let this situation happen to you!

We will review how to build a custom maintenance plans from the ground up using TSQL commands. What are the best practices for daily, weekly and monthly tasks? This presentation includes tape rotation schemes and restoring those backups to make sure they really work.

Coverage:

1 – Verify integrity of databases.
2 – Backup databases (full versus differential).
3 – Backup logs
4 – Maintain database indexes.
5 – Maintain index/column statistics.
6 – Remove older data from [msdb].
7 – Remove older backups from file system.

Details:

presentation
scripts

March 2011 (SNESSUG)

Topic:

Leveraging Transaction SQL 2008 to solve business problems (Part I)

Abstract:

This presentation will review how to build a database solution from the ground up using SQL commands. What if you were a SQL Server developer at a local consulting company tasked with creating a database for a local Boy Scout of America (BSA) Troop, where would you begin? The following topics will be reviewed to make sure you have a successful data storage solution.

Database Crafting:

1 – Defining file groups for the database,
2 – Creating schemas to separate tables by functionality,
3 – Creating normalized tables to hold the BSA data
4 – Adding data integrity to the design with constraints/triggers
5 – Adding referential integrity to the design with primary/foreign keys
6 – Adding logins/users and granting rights

Data Loading:

1 – Loading data into staging area with BULK INSERT, OPENROWSET or BCP
2 – TSQL statements for TRANSACTIONS, CONTROL FLOW, and ERROR HANDLING
3 – Writing Stored Procedures to move staging data to production
4 – Schedule Procedures using SQL Server Agent

Granting Access:

1 – Using stored procedures to lock down CRUD (create, read, update, and delete) operations
2 – Using functions and views to look at the data differently.

Details:

To be posted.