Slide 1
Slide 1 text
Tim Allen Becky Sweger
Slide 2
Slide 2 text
No content
Slide 3
Slide 3 text
Joshua
Jana
John-David
Jill
Jessa
Jinger
Josiah
Joy-Anna
Jedidiah
Jeremiah
Jason
James
Justin
Jackson
Johannah
Jennifer
Slide 4
Slide 4 text
Pick one and enforce it: camelCase, PascalCase,
under_scores
Why?
Cleaner code
Logical joins
Sanity of future developers and future you
Explicitly name constraints
Avoid keywords as column names
WRDS Fail: group, client, school, subscriber, and
institution are all used for the same data entity
Slide 5
Slide 5 text
No content
Slide 6
Slide 6 text
The key, the whole key,
and nothing but the key.
So help me Codd.
Slide 7
Slide 7 text
http://en.wikipedia.org/wiki/File:Insertion_anomaly.svg
Slide 8
Slide 8 text
Avoid data duplication
Let end users make their own
changes
Avoid data anomalies
Third-party tools rely on normalized
data
Slide 9
Slide 9 text
Find a book in Van Pelt without a card catalog…
Slide 10
Slide 10 text
No content
Slide 11
Slide 11 text
Indexes in SQL Server are organized as B-trees
member name clustered index (image from Clustered Indexes vs. Nonclustered Indexes in SQL Server:http://tr.im/AeU5)
Slide 12
Slide 12 text
member id non-clustered index (image from Clustered Indexes vs. Nonclustered Indexes in SQL Server:http://tr.im/AeU5)
Slide 13
Slide 13 text
Unique
Full-text
Included columns
Indexed views
XML
Filtered (new for 2008)
Spatial (new for 2008)
http://msdn.microsoft.com/en-us/library/ms175049.aspx
Slide 14
Slide 14 text
Primary key = unique index (clustered or non-
clustered)
Slide 15
Slide 15 text
Clustered index on every table
member name non-clustered index (image from Clustered Indexes vs. Nonclustered Indexes in SQL Server:http://tr.im/AeU5)
Slide 16
Slide 16 text
Integer primary key on every table
/* take checkpoint, clear buffers & cache */
SELECT s.term, s.section_id, COUNT(penn_id)
FROM flat_section s JOIN flat_enrollment e
ON s.section_id = e.section_id
AND s.term = e.term
GROUP BY s.term, s.section_id
Slide 17
Slide 17 text
Results
Slide 18
Slide 18 text
No content
Slide 19
Slide 19 text
Columns you join on: indexed integers
are your friend!
How are the columns used in queries?
Cardinality: 1:1, 1:many, many:many
Data type
Indexing multiple columns: moderation
Goal 1: performance!
Goal 2: smallest index file possible.
Slide 20
Slide 20 text
No content
Slide 21
Slide 21 text
TINY: 8 bits (0 – 255): 01010101
SMALL INTEGER: 16 bits (0 – 65536): 0101010101010101
INTEGER: 32 bits (0 – 16777215):
01010101010101010101010101010101
VARCHAR ‘philadelphia’: 104 bits, at least (encoding UTF-8):
01010101010101010101010101010101010101010101010101010101
010101010101010101010101010101010101010101010101
Consider joining 4 tables on ‘philadelphia’: 4 initial lookups on
indexes, 8 times as bulky and less cacheable as a small integer.
Index on VARCHARs only when needed
Keeps index files smaller and less chance of fragmentation;
fragmented index files make Matt Frew’s life hellish (no, that is
NOT a positive!)
Consider purpose: don’t index for a one time script or report
Slide 22
Slide 22 text
No content
Slide 23
Slide 23 text
Large table: millions of rows
A record for each WRDS query
VARCHAR columns that should be INTEGERS
Report requests for subscribers asking number
of queries for a date range group by library and
file
Before indexing, full table scan: 30 secs per
query
Index added: subscriber, query date, library, file
After indexing, without table scan: 0.02 secs
per query
Slide 24
Slide 24 text
From the Query menu:
SET STATISTICS TIME, SET STATISTICS IO
Include Actual Execution Plan
Slide 25
Slide 25 text
Dynamic Management Views & Functions:
http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
Slide 26
Slide 26 text
Analyzes workloads
Slide 27
Slide 27 text
Blame the SQL Admins!
Slide 28
Slide 28 text
Don’t skimp on column length:
Yes/No? Maybe. Open/Closed? Under
construction. Black/White? Grey.
Know required level of precision,
and leave yourself room to grow.
Accuracy to the day, minute,
millisecond?
Slide 29
Slide 29 text
Logical deletes
vs.
physical deletes
Slide 30
Slide 30 text
Foreign Keys
Unique indexes
Check constraints
Default constraints
Triggers
image courtesy of My New Filing Technique is Unstoppable by David Rees: http://www.mnftiu.cc/2002/11/26/filing-009/(nsfw)
Slide 31
Slide 31 text
No content
Slide 32
Slide 32 text
“Temporary”
projects
Balance between
today’s
pragmatism and
tomorrow’s pain
Code review
sooner
Slide 33
Slide 33 text
Experiment in SQL Server Management Studio
to improve your times and execution plans
Slide 34
Slide 34 text
Solicit feedback on database design
before coding starts, not after.
Ask for opinions, and share your
opinions!
More eyes = better database design
More ideas = better database design
Anyone have any tips… or questions?
Slide 35
Slide 35 text
1. SQL Server Books Online
http://msdn.microsoft.com/en-us/library/ms130214.aspx
2. SQL Server 2008 Query Performance Turning Distilled by Grant Fritchey and
Sajal Dam
3. Comparing Tables Organized with Clustered Indexes versus Heaps
http://technet.microsoft.com/en-us/library/cc917672.aspx
4. MS Index Design Guidelines
http://msdn.microsoft.com/en-us/library/ms191195.aspx
5. Clustered Indexes vs. Nonclustered Indexes in SQL Server
http://digcode.com/default.aspx?page=ed51cde3-d979-4daf-afae-fa6192562ea9&article=443e9774-7d26-422d-a2f1-
dbcafbb1e1fc&pc=5
6. SQL Server Execution Plans (free e-book, registration required)
http://www.sqlservercentral.com/articles/books/65831/
7. Uncover Hidden Data to Optimize Application Performance
http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
8. My New Filing Technique is Unstoppable (NSFW)
http://www.mnftiu.cc/2002/11/26/filing-001/
9. SQL in the Wild
http://sqlinthewild.co.za/
10. Journey to SQL Authority With Pinal Dave
http://blog.sqlauthority.com/