String Functions – SUBSTRING()

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the SUBSTRING() function today. The SUBSTRING() function allows a developer to slice and dice a string into pieces. It takes a source string [T], a integer starting position [S], and a the number of characters [C] to return as input parameters. It returns a string of [C] characters from position [S] as output. The example below extracts one word from a famous movie title.

One thing to…

Instead of Update

Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a UPDATE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that update action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…

Instead of Delete

Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a DELETE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that delete action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…

Surrogate Keys

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 surrogate keys can be used to tie the dimension tables to the fact tables. I need to start the talk off…