SQL Server Database Design and T-SQL Best Practices

1. SQL Server Database Design and T-SQL Best Practices

2. Speaker Background • Masters Degree in Computational Physics • 20 + years of programming • 11+ years of DBA experience

3. Database Design • Database design is often more of an art than a discrete theoretical expression. • Arie D. Jones • Luckily we can use sets of general rules to aid us in creating performant databases.

4. Why am I concerned with Database Design? Lack of pure DBAs. Excess of SADBABDs(System Administrators/Database Administrators by Default) A lot of DBAs like to make a clear distinction as to whether they are an Administrator or a Developer….. i.e. Administrator<>Developer Corporations like to think that ‘developer’ means front to back

5. What does this have to do with .NET? • Everything • Bad Database Design = Bad Application Performance • Bad Database Design = Poor Scalability • Bad Database Design = Complex Data Access Layer Bad Database Design = Application Developer Headaches!

6. What we think we know • It is what we think we know that gets us into trouble. • Beware the following • I claim • I think • I feel • Maybe • Everyone should ‘kick the tires’ of a premise before accepting it.

7. Normal Form • 3rd Normal Form or more • Why is it important? • Is it important? • Normal Form should not dictate every action within the database as a little denormalization is often needed in some circumstances.

8. Example #1

9. Business Logic in the Database • Just because something CAN be done does not necessarily mean that it SHOULD be done. • Leave business logic that can change frequently to the application layer

10. Example #2 • Business Rule #1: Brokers whom are managers can only see those individuals whom directly report to them. • Business Rule #2: Data cannot be entered for future dates. • The distinction is often a matter of preference but can have serious impact on maintainability

11. Generic Objects • Do not develop your database objects or T-SQL code in generic fashion • Objects developed to generically handle multiple tasks add unnecessary complexity and introduce a wider surface area for errors to occur. • Generic objects also are normally not very performant.

12. Example #3: What was ‘needed’ • A Simple Type 2 table in which to house hierarchy information for partners

13. Example #3 : What we ‘got’ A generic schema to handle any number of hierarchy needs.

14. Take Advantage of New Tools Example • Partitions • --Create the partition function • CREATE PARTITION FUNCTION PFN_Year2007(DATETIME) • AS RANGE RIGHT FOR VALUES (‘20070101’) • --Associate with a filegroup • CREATE PARTITION SCHEME PS_Year2007 AS PARTITION PFN_Year2007 TO (PRIMARY) • --Now create a table that uses it • CREATE TABLE PARTITIONTEST • ( • ID INT NOT NULL IDENTITY(1,1,), • PROCESSINGDATE DATETIME NOT NULL ) ON PS_YEAR2007(PROCESSINGDATE)

15. Take Advantage of New Tools • Schema Separation • Allows you to more easily drop database users • Multiple users can own a schema through group membership • You can use shared default schemas for uniform name resolution instead of using dbo

16. Take Advantage of New Tools • XML Datatypes • Allows storing of typed and untyped or typed(by adding a Schema collection) • You can store XML based upon a single schema, multiple schemas, or even XML fragments • Check out http://www.hr-xml.org

17. Miscellaneous Database Design • Naming Conventions: Create, Document, and Use • Document your Database: Oh! that is what that Description field is for. • Try not to use Text fields if possible • Design for Performance: Not Just Indexes!

18. T-SQL Best Practices • T-SQL Best Practices follow some of the basic principles of database design • Coding Conventions • Documentation • With a few more…..

19. T-SQL Best Practices • Use stored procedures whenever possible • Encapsulation • Performance • Indent your code • Avoid using temp tables(#) if possible • Avoid using cursors if possible • Keep transactions short and sweet • Know subtle differences @@Identity vsScope_Identity • And possibly the most important of all……

20. Coding Conventions • Know new coding conventions… • They are there to make queries both faster and easier for you

21. Coding Conventions • Rank & Partitions • Salespeople by Rank SELECT Salesperson, SalesYear, TotalSales, RANK() OVER (PARTITION BY SalesPerson ORDER BY TotalSales DESC, SalesYear DESC) as SalesRank FROM Sales • #1 Salespeople by Year SELECT * FROM ( SELECT Salesperson, SalesYear, TotalSales, RANK() OVER (PARTITION BY SalesPerson ORDER BY TotalSales DESC, SalesYear DESC) as SalesRank FROM Sales ) tmpSales WHERE SalesRank=1

22. Coding Conventions • CTE – Common Table Expressions and Row_Number WITH SalesOrders AS ( SELECT Salesperson, OrderDate, Row_Number() OVER (ORDER BY OrderDate) as ‘RowNumber’ FROM Sales ) SELECT * FROM SalesOrders WHERE RowNumber BETWEEN 20 AND 30

23. Coding Conventions • Exception Handling BEGIN TRY -- Divide by 0 is bad SELECT 1/0; END TRY BEGIN CATCH --Process your own error handling routine END CATCH

24. Coding Conventions • DDL Triggers • Good for tracking and/or preventing changes within the database • Good for enforcing naming conventions • Can be scoped to a database or to the Server level. • EventData() is returned as XML so you have to do a little more coding to extract particular pieces.

25. Coding Conventions • Except: Returns all distinct rows from the LEFT side of the operator that do not match the right side. SELECT * FROM SalesTest1.dbo.Sales EXCEPT SELECT * FROM SalesTest2.dbo.Sales

26. Coding Conventions • Intersect: Returns all distinct values that are returned by both the left and the right sides of the operand SELECT * FROM SalesTest1.dbo.Sales INTERSECT SELECT * FROM SalesTest2.dbo.Sales

27. Conclusion • All slides will be posted on my blog • http://www.programmersedge.com • Questions