Done with Session 01 of the 24 PASS Webinar. This was by far the largest live meeting I have been a part of with over 450 users. I will not have time to write a complete review of all the webinars, however, I will add updates in this blog posts comments as I go along!
For the first session the sound did seem to cut out a bit, and some of the slides were a bit behind, but overall a good start to the 24 hour event. Taking notes the best I could as the webinar went along, these were the 10 Big Ideas In Database Design from Paul Nielsen and Louis Davidson:
1. Denormalization is for wimps: For an OLTP database the extra code it takes to get the data out of the database can outweigh the effort it takes to normalize the database. However, in reality, there are situations where normalization and denormalization work within the same database.
2. Use Keys: make sure to use primary and surrogate keys. These days it seems that there is a trend within application development to not use any keys at all…sometimes Guids are overused.
3. Generalize: Leveraging a data driven design and fewer tables can make the database easier to extend. Find a middle ground between over simplifying the database design and over complicating the design. Generalization can keep things from getting over complicated.
4. Class <> Table: Just because it is a class does not mean it needs to be a table.
5. Data Drives Design: You can put business logic at the application level, push it down into procedures, put it in schemas, or put it all the way down into the data. The further down you are, the more extensible it is. Don't hide the logic in a nasty query or stored procedure.
6. Sets Good, Cursors Bad: Be cognizant of when you are using cursors versus a set based approach (it is okay to use cursors for certain types of iterations), however, SQL Server is very set based. Understand which approach you are using, and why.
7. Use Proper Data Types: Put data in the right sized object. The speakers used an analogy of inviting people over for tea and making them all drink out of a trough. If you don't truly need a varchar(max), then don't use one!!
8. Abstract/Encapsulate --> Extensibility: Data is much more permanent then which development technique is "hot" this week, year, etc…stored procedures last as long as your data! T-SQL has been around for awhile and will continue to be around longer. The analogy is that a plug in the wall hides how the power gets to you, but anything that plugs into the plug will work, it can use the power!
9. Spaghetti is food, not code: Sometimes a trigger can kick off a procedure, the procedure can then call a trigger, back to procedure, and so on and so on…this hides the code from the programmers / developers. The data is not easy to follow, it is very hard to trace. Unraveling all of the stored procedures can not only make the code easier to understand, but it can greatly improve performance. Sometimes operations need to be complex, most of the time they can be simplified.
10. NOLOCK = NO Consistency: Don't forego table locks to try and gain performance.
A big argument for a lot of these points is like the answer to most questions in consulting…it depends! Truthfully for a lot of these points, it truly does depend. What do you think about these points, do you agree with most of them? Please let me know if you have any additional details for each point.
The next session I am attending in a couple hours is "Session 04 (BI) - Delivering Good Performance Consistently with SSIS (John Welch)". Due to my extensive experience with SSIS I am really looking forward to this one!