Share
Embed
Slide 1
Slide 1 text
Richie Rump
Jorriss LLC
@Jorriss
www.jorriss.net
Slide 2
Slide 2 text
No content
Slide 3
Slide 3 text
No content
Slide 4
Slide 4 text
• Think of your database as your
application foundation.
• The more time you spend on your
foundation the better your application
can be.
• If you get the foundation wrong you WILL
have problems.
Slide 5
Slide 5 text
• Databases are highly complex systems.
• SQL Server has over twenty years of
development.
• Learning SQL Server is like Alice’s rabbit
hole. It keeps going down.
• Leverage your DBAs experience and
knowledge of SQL Server.
Slide 6
Slide 6 text
• Relational Databases are NOT going
away!
• Reporting from NoSQL databases is
difficult…for now.
• Right tool, right situation.
Slide 7
Slide 7 text
• ORM tools like Hibernate, ActiveRecord
and Entity Framework all can create
databases.
• Out of the box schemas can
have…opportunities.
• Agile frameworks and rapid iterations can
leave DB design an afterthought.
Slide 8
Slide 8 text
• Wrong Types
• No Indexes on Foreign Keys
• Entity Attribute Value pattern
• Guids
• Surrogate Key / No Alternate Key
Slide 9
Slide 9 text
Page Header
Row 1
1
Row 2
Row 3
Row 4
Row 5
2
3
4
5
Page Header
Row 6
6
Row 7
Row 8
Row 9
Row 10
7
8
9
10
Page Header
Row 11
11
Row 12
Row 13
Row 14
Row 15
12
13
14
15
Slide 10
Slide 10 text
• Having the wrong data types can create
long lasting issues.
• T-SQL can become challenging and
inefficient.
• Understanding the differences between
data types
• Better information, better decisions.
Slide 11
Slide 11 text
• CHAR
– Fixed length. Value will be padded with spaces.
– Size n bytes.
– 1 – 8000 characters.
• VARCHAR
– Variable length.
– The storage size is the actual length of the data
entered + 2 bytes.
– 1 – 8000 characters.
Slide 12
Slide 12 text
• Holds UNICODE data.
• NCHAR
– 1 – 4000 characters
– Storage size twice n characters.
• NVARCHAR
– 1 – 4000 character
– Storage size twice n characters + 2 bytes.
Slide 13
Slide 13 text
• MAX allows a column size of 2GB.
• Cannot create an index on MAX columns
• REPEAT: Cannot create an index on MAX
columns
• NVARCHAR(MAX) is the default for a
string when generated from Entity
Framework.
Slide 14
Slide 14 text
• DATETIME
– Accuracy rounded to .000, .003, or .007 second.
– 8 Bytes
• DATETIME2 (n)
– Accuracy to .0000001 second
– 6 bytes for precisions less than 3;
– 7 bytes for precisions 3 and 4.
– All other precisions require 8 bytes.
• Demo
Slide 15
Slide 15 text
• BIGINT
– -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807
– Size 8 bytes
• INT
– -2,147,483,648 to 2,147,483,647
– Size 4 bytes
Slide 16
Slide 16 text
• May slow queries with JOINs
• Will have a performance impact on
DELETEs
Slide 17
Slide 17 text
No content
Slide 18
Slide 18 text
Demo
Slide 19
Slide 19 text
No content
Slide 20
Slide 20 text
Entity ID Name
1 Cecil Phillip
Attribute ID Entity ID Attribute Type Value
100 1 Phone Number 305-555-9607
101 1 Age 30
102 1 Birthdate 2/9/1983
Person
Person_Attribute
Slide 21
Slide 21 text
Entity ID Name
1 Cecil Phillip
Attribute ID Entity ID Attribute Type Value
100 1 Phone Number 305-555-9607
101 1 Age 501
102 1 Birthdate Yes I have one
Person
Person_Attribute
• No Data Type Enforcement
Slide 22
Slide 22 text
Entity ID Name
1 Cecil Phillip
Attribute ID Entity ID Attribute Type Value
100 1 Phone Number NULL
101 1 Age 30
102 1 Birthdate 2/9/1983
Person
Person_Attribute
• No NOT NULL Enforcement
Slide 23
Slide 23 text
• Querying much harder
• Demo…
Slide 24
Slide 24 text
• You would think this wouldn’t be a
problem…
• Some benefits
– Portable
– Id can be generated from the app
– That’s about it.
Slide 25
Slide 25 text
The problems:
• Size
– Guids 16 bytes
– Int 4 bytes
– Disk and Memory
• Fragmentation
Slide 26
Slide 26 text
Page Header
Page Header
Id: 2
1
Id: 3
Id: 7
Id: 12
Id: 14
2
3
4
5
Id: 6
1
2
3
Slide 27
Slide 27 text
Page Header
Row 1
1
Row 2
2
Page Header
Row 3
1
Row 4
Row 5
2
3
Page Header
Row 10
1
Row 11
Row 12
2
3
Page Header
Row 8
1
Row 9
2
Page Header
Row 6
1
Row 7
2
3
Page Header
Row 13
1
Slide 28
Slide 28 text
A partial solution:
• NEWSEQUENTIALID()
– Creates a sequential GUID.
– Minimizes fragmentation
– Still have the space issue.
Slide 29
Slide 29 text
• A surrogate key is a primary key
automatically generated
• Surrogate keys are good thing.
• BUT when alternate key isn’t defined bad
things can happen…bad things man, bad
things.
• Demo…
Slide 30
Slide 30 text
• Align proper design with the
requirements.
• Learn how SQL Server works
• Work more closely with the DB team.
• Document DB design decisions.
Slide 31
Slide 31 text
• Data Model Resource Book vol 1-3
Len Silverston
• Pro SQL Server 2012 Relational
Database Design and Implementation
Louis Davidson, Jessica Moss
Slide 32
Slide 32 text
Richie Rump
@Jorriss
http://jorriss.net
http://slideshare.net/jorriss
http://dotnetmiami.com