SQL Server for C# Programmers [closed]

Question

I'm a pretty good C# programmer who needs to learn SQL Server. What's the best way for me to learn SQL Server/Database development?

Note: I'm a total newb when it comes to DB's and SQL.

Answer 1

SQL is about set theory, or more correctly, relational algebra. Read a brief primer on that. And learn to think in sets, not in procedures.

On the practical side, there are four fundamental operations,

• selects, which show some projection of a table(s) data
• deletes, which remove some subset of a table's rows,
• inserts, which add rows to a table,
• updates, which (possibly) change data in a table

(By subset, I mean any subset, including the empty set, and not necessarily a proper subset.)

Anywhere I can write a column name in DDL (except as the target of an update), I can write an expression that uses column names, functions, or constants.

select 1, 2, 3 from table will return the resultset "1 2 3", once for each row in the table. If the column named create_date is of type date, and the function month returns a month number given a date, select month( create_date) from table will show me the month number for each create_date.

A where clause is a predicate that restricts rows selected, or deleted, or updated to those rows for which the predicate is true. A where cause can be composed of an arbitrary number of predicates connected by the logical operators and or and not. Just like the column list in a select, I can use column names, functions, and constants in my where clause. What result set do you think is returned from select * from table where 1 = 1;?

In a query, tables are related by joins, in which some datum or key in one is related by an operator to a datum or key in another table. The relational operator is often equality, but can in fact be any binary operator or even a function.

Tables are related, as I mentioned above, by keys; a row in a table may relate to zero, one, or many rows in another table; this is referred to as the cardinality of the relation. Relations may be one-to-one, one-to-many, many-to-many. There are standard ways of representing each relation. Before you look up the standard ways to do this, think about how you'd represent each one, what the minimum requirements of each kind is. You'll see that a many-to-many relation can in fact also model one-to-many and one-to-one; ask yourself why, given that, all relations are not many-to-many.

EF Codd, among other, pioneered the idea of normal form in relational databases. There are commonly held to be five or six normal forms, but the most important summary of normal form is simple: every entity that your database models should be represented by one row and one row only, every attribute should depend on the row's key, and every row should model an entity or a relationship. Read a primer on normal form, and understand why you can get data inconsistencies if a your database isn't normalized.

In all this, try to understand why I like to say "if you lie to the database, it will lie to you". By this I don't mean bad data, I mean bad design. E.g., if you model a one-to-many relation as many-to-many, what "lies" can be recorded? What "lies" can happen if your tables aren't normalized?

A view, in practical terms, is a select query given a name and stored in the database. If I often join table student to table major through the many-to-many relation student_major, maybe I can write a view that selects the columns of interest from that join, and use the view instead of alway rewriting the join.

Practical tips: first, write a view. whatever you're doing, it'll be simpler and clearer if you write a view for every calculation or sub-calculation you do. Write a view that encapsulates each join, write a view that encapsulates each transformation. Almost anything you want to do can be done in a view.

Decomposing a query into views serves the same ends as functional decomposition serves in procedural code: it allows you to concentrate on doing one thing well, makes it more easily tested, and allows you to compose more complex functionality out of simpler operations. Here's an example where I use views to transform a table into forms that more easily allow me to apply successive transformations, in order to get to a goal.

Don't conflate data. Each table ought to unambiguously model one thing (one kind of entity) and only one thing; each column should express one and only one attribute of that thing. Different kinds of entities belong in different tables.

Metadata is your friend. Your database platform will provide some metadata; what it doesn't provide you should add. Since metadata is data, all the rules for modeling data apply. You can get, for example, the names of all objects in your database from the sytem table sysobjects; syscolumns contains all the columns. To find all the columns in one table, you'd join sysobjects and syscolumns on id, and add a where clause restricting the resultset to a particular table name: where sysobjects.name = 'mytable'.

Experiment. Sit down at a database and ask yourself, "How can I represent people with hair colors and professions and residences? What tables and relations are implied in modeling that?" Then model that, as tables.

Then ask yourself, "How can I show all blonde doctors who reside in Atlanta", and write the query that does that. Piece it together by writing views that show you all blondes, all doctors, and all people who reside in Atlanta.

You'll find that in asking "how can I find that", you'll expose deficiencies in your model, and you'll find that you want or even need to change the way your model works. Make the changes, see how they make your queries easier or harder to write.

Answer 2

I love Joe Celko books from novice to advanced. I also think virtual labs are great.