Over the years I have accumulated some seemingly weird ways to explain topics within SQL Server. Usually in a training I find that participants are able to relate to an example in real life compared to some code written on the screen. While often the example might not be a perfect fit it still conveys the important point I am trying to get across. Here are some of my most frequently used.
Explaining Dimensions and Facts
The purpose of Business Intelligence is to answer a question and when you think of it every question has two parts something I like to call the Qualifier and the Quantifier. E.g. Show me sales for Honda cars in Bangalore for 2010. In this question when it’s all said and done we are looking for a single number (the fact) which is sales. But we are not looking for sales across all cars, locations or years. We are slicing and dicing the data along three different axis (the edges of a cube) these axis we slice on are called dimensions (Qualifier).
Locking Blocking and Deadlock
Think of a bathroom stall. If a single person enters it and locks it from inside its just locking. Now while that person is inside anybody else waiting to use the stall is being blocked and should wait until the original lock is released. Now assume two people enter the stall simultaneously and one takes a seat while the other grabs the toilet paper. Neither can proceed without the other and there you have a deadlock.
Cursor vs Set based operations
RDBMS really on set based data access for performance. So it’s always better to access data in a set e.g. if I wanted to find all people with age =35 in a room I could ask each one individually like a row by row operation in a cursor or I could simply shout in the room that everybody with age =35 raise your hand like a set.
Principle of least privileges
Making sure that a person has the minimum permission to do the job is what we call principle of least privileges. Think of the server as a House obviously the admins will have the key to the front door through which they decide who gets into the house. Once within the house you have the different members of the house such as parents and children. Parents by nature behave as DBO while children can only access their room (database). However in the case where siblings share a room each has their own cupboard (table) with a key known only to them and thus they can share the room while enforcing privacy.
Wait stats
The little book in which your partner has made a list of all the things they don’t like about you in anticipation of the next fight.
Hashing and Encryption
Imagine a door with a secret password you can use to open it. In the case of hashing all that is needed is the password be the same so it doesn’t matter who says it as long as the word is the same (hash key) you will be granted access. Now imagine the same situation but this time the person on the other side also recognizes your voice. Now knowing the password isn’t enough you need to be recognized using your own voice (key) too. Therefore making your access unique in spite of the same password being used.
DBA and Developer
Developer the guy who drives the car, DBA the guy who fixes it when it’s broken. Neither can exists independent of the other. The Driver needs to know how to change a tire and the DBA needs to know how to start a car.
Type0, Type 1 and Type 2 Dimensions
Your parents are a type 0 dimension because they never change over time and will always remain your parents i.e. fixed. Your employer is a Type 1 dimension because while you may have worked for many companies in the past the only one that matters is the one you are working for now. Type 2 Dimension is best exemplified by siblings you will have siblings older than you and in future you may have new siblings arrive at a later date but I the end they all matter and just knowing the latest one (like type 1) isn’t acceptable.
There are obviously more but you get the idea. Often examples like this remain in memory far longer than anything else I have shown on the screen. If you know some nice example you have used or heard please feel free to share them.
Please Consider Subscribing
