The problem

Imagine a data warehouse where you have many etl process from either a staging area or other external systems direclty.There are also many reading tasks that fill data marts for reporting purposes.

Imagine now you have to update many records because you discovered a bug and you cannot create a maintenance window since the DW has to be online 24/7.

So let us just do the update straight away, right? For that purpose we create a large table i.e. an eample fact table

CREATE TABLE dbo.demotable (
	id INT IDENTITY NOT NULL,
	val1 BIGINT NOT NULL,
	val2 BIGINT NOT NULL,
	val3 BIGINT NOT NULL,
	val4 BIGINT NOT NULL,
	val5 BIGINT NOT NULL,
	val6 BIGINT NOT NULL,
	val7 BIGINT NOT NULL,
	val8 BIGINT NOT NULL,
	val9 BIGINT NOT NULL,
	val10 BIGINT NOT NULL,
	val11 BIGINT NOT NULL,
	val12 BIGINT NOT NULL
);

➜continue reading

The Fake Logtable

This time just a short and easy one. Imagine you load FTP logs or any other kind of log file into a table.

For this purpose we introduce a fake ftp log like this:

DECLARE @LogFile TABLE (	
        id			INT IDENTITY PRIMARY KEY,
	TransactionId	INT NOT NULL,
	Operation		NVARCHAR(100) NOT NULL,
	[Filename]		NVARCHAR(250) NULL
);

INSERT INTO @LogFile 
VALUES 
	(1, 'connecting', NULL),
	(1, 'connection acqui

➜continue reading

Background information

Before SQL-92 there was no possibility to use outer joins since they simply were not in the standard. To do an inner join you basically did:

SELECT * 
FROM t1,t2 
WHERE t1.id = t2.id

So there was not even the keyword JOIN that you can use today. Therefore people used subqueries quite a lot since it was the only possibility to do things an outer join can provide.

With outer joins available today there is the recommendation to rather use joins than subqueries since the Query Optimizer of major DBMS systems can handle joins better than subqueries. If you take SQL Server for example you will realize that the query optimizer tries to rewrite any subquery as a join. Since I treated that subject in my master thesis i know that Oracle DB does the same.

So you can help the query optimizer by using joins ....

➜continue reading

this is not just another relaunch. keinerspieltmitmir.de was merely a setback.

Enough quotes. This is relaunch of my website. This is my new blog and it is all about databases, business intelligence, data warehousing and programming.

Since my job is all about databases and data warehousing there will be a focus on such topics. But i also plan to do little programs or scripts that help me in my daily job life. Don't hesitate to email me if you feel a certain topic should be mentioned or discussed.

This relaunch includes the use of #hashtags that shall help you to look old posts up. On top of this blog you will always find a list of the most used hashtags. Later on you will be able to look arbitrary hashtags up.

Old posts can be found under "Archive" but be warned they may be badly formated since i changed a few things.

Projects i ....

➜continue reading