I recently worked on a project I which I redesigned sales data warehouse as a STAR schema, using daily file partitions, with a automatic sliding window, and applying data compression at the page level. I ended up reducing a 5 terabyte database to less than 750 GB. I will be writing several articles on the lessons that I learned during the process. Today, I want to talk about how to reseed a identity column if a data load for a dimension fails. Most dimension tables use a surrogate key to…
Tag: TRUNCATE TABLE
Searching Stored SQL – Part 4
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Earlier today, I created a pattern searching tool for FUNCTIONS. I am going to clone and modify the code so that it will work with VIEWS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Searching Stored SQL – Part 3
There are four main database objects that contain stored SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for TRIGGERS. Today, I am going to clone and modify the code so that it will work with FUNCTIONS. Again, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that it will work standalone. The current code…
Searching Stored SQL – Part 2
There are four main database objects that contain stored (compiled) SQL: VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Yesterday, I created a pattern searching tool for SQL code in STORED PROCEDURES. Today, I am going to clone and modify the code so that it will allow pattern searching on TRIGGERS. First, I want to examine the dynamic SELECT statement that retrieves the user defined stored procedure names from a given database. This is the key element of the whole program. I took the liberty to modify the original code so that…
Searching Stored SQL – Part 1
Today, I am going to start building on some the articles I did in the past. We are going to create tools to pattern search stored SQL statements. Four main objects that contain stored SQL are VIEWS, TRIGGERS, FUNCTIONS and STORED PROCEDURES. Recently at work, I was asked to find all SQL code that had a certain business rule. The task is to return all objects that reference to a certain temperature process named T1. One solution to this problem is to buy a tool like SQL Search from Red…
Import & Export Data – Part 1
There are not many real life databases that do not import data from external sources or do not export data for delivery to external targets. I am going to devote a series of talks on different ways to perform these functions. The INSERT statement is the easiest way to load small sets of static information into a database. We will be working again with the Boy Scouts of America (BSA) hypothetical database. The first example loads the rank table with a data. We can see the auto-increment surrogote key is…