Most recent hashtags:#basics #learning #queryoptimizer #locks #sqlserver

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

Locking in SQL Server 2012

The default transaction level in sql server 2012 is READ COMITTED. That transaction level uses a pessimistic approach meaning readers can block writers, writers can block writers and writers can block readers.
Is it basically the opposite approach of optimistic approaches like providing row versions i.e. snapshot isolation level.

However in this session we want to focus on how SQL Server maintains consistency in a multi-user environment. The answer is simple: locks.

But how are locks actually handled. Well there is a bunch of information about locking.

First there are locks and latches. Most locks are being held until the end of transactions.
Latches are being held until the specific operation is over. Locks are used to maintain logic consistency. Latches are used to maintain physical consistency.
We will focu ....

➜continue reading

Hi,

i as busy the last few month. I finally started my own SQL based youtube Channel.

The first videos will be for absolute beginners, meaning it won't be interesting  for you guys. But stay tuned since i plan to do the MCSE SQL Server: Data Plattforms this year. So there are many interesting topics in the pipeline.

Also think of SQL Server 2016 and new features. So 2016 will provide us with many new fancy things.

If you have a colleague that struggles hard with SQL please provide him the link to my new Youtube Channel:

https://www.facebook.com/SQL-Tutorial-Videos-1131288020229170/

https://www.youtube.com/channel/UCimI9vOt0Sm0ukWCv6Wjx-w

Stay tuned and thanks for reading my blog. ....

➜continue reading