Querying Microsoft SQL Server Chapter 1

Key Take-Aways – Chapter 1: Foundations of Querying. T-SQL is the main language used to manage and manipulate data in MS SQL Server, whether on premises or in the cloud. So 2 Lessons, 1) Understanding the Foundations, and 2) Understanding Logical Query Processing.

Strong mathematical foundation, yah if you hated math in school, you’re probably gonna hate coding.

T-SQL is a dialect of standard SQL.

SQL is a standard of both ISO (International Standards Organization) and ANSI (American National Standards Institute). There are many database engines out there, SQL Server, Oracle, MySQL, PosGreSQL, so it’s good they actually pay attention to and follow a Standard, and so should You! Why? Writing code in a standard format is best practice. Your code is more portable – it will run on different engines. And your knowledge is more portable, you can understand Oracle and MySQL code if you are a Microsoft guy and have a better chance to get hired for those companies running all of these engines right..

You should consider a non-standard option only when there is not an available standard option. For example CAST and CONVERT functions. CAST is standard, CONVERT is not, they both do the same thing but CONVERT has more options like stripping time easily from the datetime datatype. So takeaway is, if you are using CONVERT when CAST can do the job, use CAST – because it is the standar.
Terminate your statements with a semicolon ; yes this is standard.

And here are the principle foundational concepts you should try to understand:

Standard SQL is based on the relational model, which is a mathematical model for data management and manipulation. Originally proposed by Edgar F. Codd in 1969.

A common misconception is that the name “relational” has to do with relationships between tables (PKs / FKs). The true source is the mathematical concept relation.

A relation in the model is what SQL calls a table. But the two are not the same. A table is an attempt by SQL to represent a relation.

It is important you understand the model’s principle, that you can use SQL in a relational way. Even though SQL deviates in many ways.
Back to a relation, where SQL attempts to represent as a table: a relation has a heading and a body. The heading is a set of attributes (SQL represents as columns), each of a specified type. An attribute is identified by name and type name. The body is a set of tuples (what SQL represents as rows). Each tuple’s heading is the heading of the relation.

Most important principles to understand about T-SQL stem from the relational model’s core foundations – set theory and predicate logic.
Remember that the heading of a relation is a set of attributes, and the body a set of tuples.

So what is a set?
By a “SET” we mean any collection M into a whole of definite, distinct objects m (which are called the “elements” of M) of our perception or of our thought. – GEORGE CANTOR, BY JOSEF W. DAUBEN (PRINCETON UNIVERSITY PRESS, 1990)

There are a number of very important principles in this definition that, if you follow and understand, should have direct implications on your T-SQL coding practices. Notice the term whole. A set should be considered as a whole.

Note distinct, a set has no duplicates. If something is true, saying it is true twice will not make it any more truer.
There is no relevance to the order of elements in a set.

Predicate Logic – the other branch of mathematics that the relational model is based on. A predicate is an expression that when attributed to some object, makes a proposition either true or false. IE, a predicate your WHERE clause, something to filter on that always returns TRUE.

Good Stuff, Easy Stuff right?