learning SQL data modelling and SQL efficient programming.

Dear all,

I am about going from infrastructure side DBA to DBA developer, I want to learn data modelling on which one is good for some situtation and some good for other, then I would like to do some good T-SQL programming in order to not doing any bad programming like SQL cursor, while loop.

Any link with particle example easy to follow ? what books is good on this ?

I have few in the list, please comment which is good .
1)  SQL and Relational Theory: How to Write Accurate SQL Code by C. J. Date , https://www.amazon.com/gp/product/1449316409/ref=ox_sc_sfl_title_2?ie=UTF8&psc=1&smid=ATVPDKIKX0DER
2) Database Modeling and Design, Fifth Edition: Logical Design (The Morgan Kaufmann Series in Data Management Systems) by Toby J. Teorey   http://www.amazon.com/gp/product/0123820200/ref=ox_sc_sfl_title_3?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

3)
Database Design and Relational Theory: Normal Forms and All That Jazz (Theory in Practice) by C. J. Date
http://www.amazon.com/gp/product/1449328016/ref=ox_sc_sfl_title_6?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

4)
SQL Server 2014 Development Essentials by Basit A. Masood-Al-Farooq
http://www.amazon.com/gp/product/1782172556/ref=ox_sc_sfl_title_2?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

5)
Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014 by Kalman Toth
http://www.amazon.com/gp/product/1499321732/ref=ox_sc_sfl_title_2?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

6)
SQL Server Integration Services Design Patterns by Andy Leonard
http://www.amazon.com/gp/product/1484200837/ref=ox_sc_sfl_title_6?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

7) SQL Server 2014 Design & Programming by Kalman Toth

http://www.amazon.com/gp/product/1499529597/ref=ox_sc_sfl_title_8?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

8)

SQL Server 2014 Database Design by Kalman Toth

http://www.amazon.com/gp/product/1499367678/ref=ox_sc_sfl_title_9?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

9)
SQL Server Tuning Scripts: Performance Optimization Secrets (IT In-Focus) by Robin Schumacher
http://www.amazon.com/gp/product/0991638670/ref=ox_sc_sfl_title_10?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

10)
Pro SQL Server 2012 Relational Database Design and Implementation (Expert's Voice in SQL Server) by Louis Davidson
http://www.amazon.com/gp/product/1430236957/ref=ox_sc_sfl_title_5?ie=UTF8&psc=1&smid=ATVPDKIKX0DER


is the one from Kalman Toth, one of the best one as she already one of the famous SQL server professional in the world ?

you can see that she is focusing on T-SQL developement ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
He :)

1) is a must.
2) and 3) are optional.

Then you should go for Itzik Ben-Gans Microsoft SQL Server 2012 T-SQL Fundamentals and T-SQL Querying for basic understanding of SQL Server.
marrowyungSenior Technical architecture (Data)Author Commented:
"1) is a must."

some review in Amazon can say this book is not good but I can't find another which is better that this.

Please note that I have T-SQL experience but I am not from developer/SQL developer background.

so I want to do more SQL developer/database developer's work to understand why their code always not good and slow. This is my concern and where I want to go to.

"Then you should go for Itzik Ben-Gans Microsoft SQL Server 2012 T-SQL Fundamentals and T-SQL Querying for basic understanding of SQL Server."

but I already got a lot of T-SQL experience but not a daily T-SQL programmer, I only try to copy code from web and implement my function.I can make use of Whoisactive to troubleshoot who kill the SQL server, and I can copies with troubleshooting SQL by using the RIGHT script as I check line by line on what each part of the script/store procedure is going to do.

 do I need the basic understand of SQL?

"2) and 3) are optional."

this is for data modelling, right? what is good book for data modelling from your point of view, or what you mean is other than 2) and 3), I should buy all of them?
ste5anSenior DeveloperCommented:
Imho you need to understand the importance and impact of the relational model first. This includes modelling techniques like normalization, which is the applied relational algebra.

With this knowledge you can enhance your modelling techniques for practical skills like using different tools or graphical modelling tools. This is which can make the T-SQL development easier.

When you have a correct model, then you implement it. And also test it. For testing your model, you need already basic T-SQL knowledge. Here the fundamentals book is a very good reference.

As a DBA you need to know how querying works. This includes a lot of internals. Cause it's a DBA's job to maintain the server and it's performance. Well, you may say even as a developer you need to deliver performance. Yes, indeed. But performance is a different step. As Don Knuth said (the short version): Make it run, make it right, make it fast. He was talking about premature optimization. When I'm in a development stage, I don't care about performance. Only about a correct model and correct output. Cause with these two I can later play the DBA role and improve performance.
For example: T-SQL has user defined functions, which are under most circumstances really a performance bottleneck. But this doesn't stop me from using them while implementing my logic, when it allows a faster development process here. Of course I'm revisiting those later to optimize them. But the advantage is now: I have the entire system running, so I can identify the overall problems. So I don't waste time in optimizing queries which are only used once a week.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

marrowyungSenior Technical architecture (Data)Author Commented:
"Imho you need to understand the importance and impact of the relational model first. This includes modelling techniques like normalization, which is the applied relational algebra.

With this knowledge you can enhance your modelling techniques for practical skills like using different tools or graphical modelling tools. This is which can make the T-SQL development easier."

yeah, that's why I know I have 3x target:
1) data model to see which model, is faster for developement, like web site and which one is slower but more secure (more table to separate data but more join, so slower, e.g.)
2) after that medium and advanced T-SQL programming to provide guildline for SQL/non SQL developer on how to talk to SQL  in a good way.
3) go to the direction of data warehousing.

"When you have a correct model, then you implement it. And also test it. For testing your model, you need already basic T-SQL knowledge. Here the fundamentals book is a very good reference."

as I said I have fundemental T-SQL knowledge and I am not sure if I still need it !! buying some more but I don't read (or can find from web) only waste time/money/paper.

"As Don Knuth said (the short version): Make it run, make it right, make it fast. "

who is Don Knuth ?

"When I'm in a development stage, I don't care about performance. Only about a correct model and correct output. Cause with these two I can later play the DBA role and improve performance."

so you mean the first step is a good data model first without talking about anything else ?

"But the advantage is now: I have the entire system running, so I can identify the overall problems. So I don't waste time in optimizing queries which are only used once a week."

good thinking ! I would like to also see what query are running everyday at the time CPU 100% hight, and see if it is the same daily ! then turn these queries.

is 3) the total repeat of 1) ?
ste5anSenior DeveloperCommented:
For Target 1) There is only one relational model in the end. Faster for development or more secure are non functional aspects of development, but not of the model.

For Target 2) You don't need to know SQL to generate a model. Thus I mentioned this as extra step.

For Target 3) Here you should also read Kimball and Inmon about dimensonal modelling.

See http://en.wikipedia.org/wiki/Donald_Knuth.
marrowyungSenior Technical architecture (Data)Author Commented:
"For Target 3) Here you should also read Kimball and Inmon about dimensonal modelling."

I am sorry , who is Kimball and Inmon ?

how about target 4) to 10) ?
ste5anSenior DeveloperCommented:
..Inmon and Kimball..

I consider the rest of those books optional.
marrowyungSenior Technical architecture (Data)Author Commented:
"..Inmon and Kimball.."

tks for that but as there are a lot of books in the link, which one you suggest for me purprose ?

this one: Data Architecture: A Primer for the Data Scientist: Big Data, Data Warehouse and Data Vault
http://www.amazon.com/Data-Architecture-Primer-Scientist-Warehouse/dp/012802044X/ref=sr_1_2?s=books&ie=UTF8&qid=1429080484&sr=1-2&keywords=inmon

or ... ? this one is for Microsoft? or just theory from the ground up ?

it seems the Kimball, he is focusing on data warehouse, which one is about modelling or you are talking about data warehouse modelling.

they both focusing on MS product or in general ?
ste5anSenior DeveloperCommented:
Inmon and Kimball are those guys who have done the most research on that field. So its the theory behind. Kimball also provides books about the specific Microsoft toolset for dataware houses.
marrowyungSenior Technical architecture (Data)Author Commented:
"Kimball also provides books about the specific Microsoft toolset for dataware houses. "


ok, so those books are theory only but if we need real world solution we need other books or what book from them is a MUST to read ?
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, it seems that 1) is only about SQL code but not data modelling, are we finding the wrong one ?
marrowyungSenior Technical architecture (Data)Author Commented:
also any query tuning good books you can suggest or link you can suggest so that I can tune query asap first and then study theory behind that make it slow.
ste5anSenior DeveloperCommented:
For tuning it's T-SQL Querying.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
what ? you mean 1) is already including the best data modelling as well as the fastest T-SQL Querying ?

again any query tuning good books you can suggest or link you can suggest so that I can tune EXISTING developed query in an easy way by showing how to read execution plan to see the real bottleneck ?
marrowyungSenior Technical architecture (Data)Author Commented:
so any update for me on this:

again any query tuning good books you can suggest or link you can suggest so that I can tune EXISTING developed query in an easy way by showing how to read execution plan to see the real bottleneck ?
marrowyungSenior Technical architecture (Data)Author Commented:
tks very much but I hope you can give me some link/books to learn about T-SQL tuning.
marrowyungSenior Technical architecture (Data)Author Commented:
so all books you suggest is on a generic terms but not only focus on MS SQL Server, right?

any comment on this : http://www.amazon.com/gp/product/1430236957/ref=ox_sc_sfl_title_7?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

and any conflict between this and http://www.amazon.com/gp/product/1449316409/ref=ox_sc_act_title_4?ie=UTF8&psc=1&smid=ATVPDKIKX0DER  ?
ste5anSenior DeveloperCommented:
Not all, but the basics (relational theory) are not SQL Server specific.
marrowyungSenior Technical architecture (Data)Author Commented:
but you mean all applicable to Oracle and MS SQL ?

What if I want to focus on MS SQL only in a company ? what books you recommend ?
marrowyungSenior Technical architecture (Data)Author Commented:
ste5an,

"For Target 3) Here you should also read Kimball and Inmon about dimensonal modelling."

are you talking about this books?

http://www.amazon.com/Building-Data-Warehouse-W-Inmon/dp/0764599445/ref=sr_1_2?s=books&ie=UTF8&qid=1430973475&sr=1-2&keywords=inmon

http://www.amazon.com/gp/product/1118530802/ref=ox_sc_act_title_7?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

they seems only focus on Warehouse, right ?
ste5anSenior DeveloperCommented:
Yes. These explain the different view point you need for building dataware houses. It's about relational modelling vs. dimensional modelling. And why redundancy is not evil in a DW.
marrowyungSenior Technical architecture (Data)Author Commented:
ok, good !

but are they repeating anything with each other ? if they are and this mean I can only buy one, which one is better ?
ste5anSenior DeveloperCommented:
Start with Kimball. Inmon is slightly different. Imho more theory and a different architectural approach.
marrowyungSenior Technical architecture (Data)Author Commented:
tks. you are good !
marrowyungSenior Technical architecture (Data)Author Commented:
"Imho more theory and a different architectural approach."

Yeah, this should be the second one, this can have more theory and let me talk to developer!

those theory should work fine in practical world ?
marrowyungSenior Technical architecture (Data)Author Commented:
one thing, I heard that there are Data warehouse 2.0 concept, is it right there and is both books above copy with that?

Inmon has this one:

http://www.amazon.com/gp/product/0123743192/ref=ox_sc_sfl_title_1?ie=UTF8&psc=1&smid=ATVPDKIKX0DER

is it an second choice or I don't buy

http://www.amazon.com/Building-Data-Warehouse-W-Inmon/dp/0764599445/ref=sr_1_2?s=books&ie=UTF8&qid=1430973475&sr=1-2&keywords=inmon

but that one:

http://www.amazon.com/gp/product/0123743192/ref=ox_sc_sfl_title_1?ie=UTF8&psc=1&smid=ATVPDKIKX0DER
?

what or DW 2.0 or highter (what is the highest concept? any 3.0, 2.5 ?) books is good to start reading ?
marrowyungSenior Technical architecture (Data)Author Commented:
also the T-SQL query is about tuning query, any books about advance T-SQL Query tunning that make use of any special feature of SQL server 2008 R2 and afterwards that focus on the way to write T-SQL query, like parallel execution?
marrowyungSenior Technical architecture (Data)Author Commented:
any books/guideline on advanced data modeling  ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.