SQL Server 70-461 Exams fundamental - Part 1 (Tips from Querying from Microsoft SQL Server Training kit by Itzik-Ben-Gan)
Hello Everybody!!! This time, it is about Microsoft
Exam.
When I decided to take up Microsoft certification (MCSA)
on Business Intelligence 2012 (I was
planning from 3 years but was never that serious to write, Lol!! But now I am
ready), I came up with training kit by Itzik-Ben-Gan. This book was very
helpful for understanding the fundamentals of T-SQL and its architectures.
However, for my preparation standpoint view, I am
summarizing the tips and points from the book which I believe, going through main
points, will help lot of them to get small tips instead of reading entire book
before day of exam. So let’s start with SQL….!!!
Standard SQL is based on the relational model, which is a mathematical model for data management
and manipulations.
The word relational
is not about 2 tables related but it is from mathematical concept of Set Relation
from “Set theory”.
Relation in the relational model is called a “Table”.
(T-SQL is based on multiset theory than on set theory).
For a table to be in Relational format, set of rules are
applicable but if not followed, those tables are not really called relational
or in other words, the so called relational table deviates from mathematical
Set theory. The examples of such deviations are below.
For a table to be in Relational format, set of rules are
applicable but if not followed, those tables are not really called relational
or in other words, the so called relational table deviates from mathematical
Set theory. The examples of such deviations are below.
Ø Set
theory say, A Set cannot have duplicates. This is not true when it comes to
Table. T-SQL on querying a set of records, by default will not remove duplicates.
Ø Adding
DISTINCT which remove duplicates will make the table relational.
Ø Set
theory doesn’t believe in order of tuples (sets like a,b,c,z,y) arranged. T-SQL
on enforcing the rules like ORDER BY clause, will make the table not relational
or deviates from relational model.
(Note: SQL Server doesn’t
track the order of records been saved into its memory. It randomly picks the
location and no guarantee that SQL Server would save the data on particular
address. The SQL Server stores the rows in a volatile fashion).
Ø According
to Relational theory, all the attributes should have names. It deviates in
T-SQL where we still can write queries like,
Select EmpID,
Firstname+ ‘ ‘ +LastName From Emp.
Where
2nd column resulting is derived column from Firstname and LastName
but have no doesn’t have any name.
This can be overcome by;
Select EmpID,
Firstname+ ‘ ‘ +LastName as Name From
Emp.
Ø Same
name of column cannot be a output if it is relational. Example ;
Select E1.ID , E2.ID from … can result in column ID from 2 tables
but this is not going to form relational table under relational model. Each
column should be identify distinctly.
Ø Set
theory stands for 2 valued- logic (True or a False) but T-SQL is a three
valued-logic system. It follows or predicates TRUE or FALSE OR UNKNOWN (NULL-
Meaning missing value).
Ex: Select * From
EMP Where Ename IS NULL.
Comments
Post a Comment