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. AdministratorltgtDeveloper
• 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 _at__at_Identity vs
  Scope_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 SalesRank1

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