SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The ABCs of Database Creation


The ABCs of Database Creation

Author
Message
Brandie Tarvin
Brandie Tarvin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48355 Visits: 9362
Comments posted to this topic are about the item The ABCs of Database Creation

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
samot-dwarf
samot-dwarf
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 143
Good:
- hints about the smallest datatypes
- clustering the table not by the PK but by the ususal query columns (one of the most common mistakes)

Bad:
- no normalization - make extra tables for publisher and title / serie
- I'd never try to drop a database statement before I creating on - if I want to prevent errors I could use the same but negated IF to create the DB only if missing

Neutral (feature wish list :-)):
- I think price, buy date, condition (nearly new or half rotten), remark and maybe bookshelf (extra table again) would be nice to have
- what's about duplicates
andrew_dale
andrew_dale
SSChasing Mays
SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)SSChasing Mays (650 reputation)

Group: General Forum Members
Points: 650 Visits: 721
you say

NOTE: There is a lot of available literature about pages, extents, and the affects they have on database design. Because of this, I won't go into the issue. It suffices to say we want to keep our table width below 1024 bytes when possible.


But the literature you reference https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx says 8060 bytes per row before it has to start moving stuff off page.

Where did your 1024 byte width limit come from?
cschnaars
cschnaars
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 14
Overall, this is a great back-to-basics article. Thank you!

Minor quibble: Your article says tinyint goes up to 251. It's 255.
Brandie Tarvin
Brandie Tarvin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48355 Visits: 9362
andrew_dale (10/29/2015)
you say

NOTE: There is a lot of available literature about pages, extents, and the affects they have on database design. Because of this, I won't go into the issue. It suffices to say we want to keep our table width below 1024 bytes when possible.


But the literature you reference https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx says 8060 bytes per row before it has to start moving stuff off page.

Where did your 1024 byte width limit come from?


Possibly a typo. My bad.

EDIT: A bit of dyslexia. I was looking at my resources and saw the 1024 x 8KB = 8192 (8060 really) and my fingers typed the first number instead of the 8060. I'll ask Steve to correct it.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48355 Visits: 9362
samot-dwarf (10/29/2015)

Bad:
- no normalization - make extra tables for publisher and title / serie
- I'd never try to drop a database statement before I creating on - if I want to prevent errors I could use the same but negated IF to create the DB only if missing

Neutral (feature wish list :-)):
- I think price, buy date, condition (nearly new or half rotten), remark and maybe bookshelf (extra table again) would be nice to have
- what's about duplicates


Ummm, kind of the point of the article. I wanted to point out that there weren't these things. More articles are coming out to address these issues. (Read the last paragraph of the article).

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48355 Visits: 9362
cschnaars (10/29/2015)
Overall, this is a great back-to-basics article. Thank you!

Minor quibble: Your article says tinyint goes up to 251. It's 255.


In my defense, I have fumble fingers. I'll get Steve to correct that.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
wagner crivelini
wagner crivelini
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 Visits: 282
@cschnaars, where do I click to LIKE your comment?

One thing that really annoys me in this community is that bunch of smart asses taking every opportunity to criticize without considering the real purpose of the articles.

Are there a couple of typos in this article ? YES

Do they really matter for the target audience? At this point (learning basics), they obviously don´t compromise the author´s goal.

@Brandie Tarvin, thanks for sharing.
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2636 Visits: 3708
A problem here is that Brandie has set out to describe in this article how NOT to design a database but unfortunately some people will miss the significance of that last paragraph (as I did first time around). People may think that Brandie believes he is giving some sound, good advice. That explains the criticisms already posted here and perhaps there are more to come.

The past record of featured articles on this site is not a happy one, so I'm not at all surprised if people jump to the wrong conclusions and think that this is just another article confirming their low expectations. For that reason I think it would have been more prudent to structure the article differently, i.e. say more about good practice instead of giving an example of poor practice.
William Vach
William Vach
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3477 Visits: 1767
Overall, this is a good article. One thing that I would like to see added is to set the recovery model to simple:
ALTER DATABASE [ComicBooks] SET RECOVERY SIMPLE
GO

The default for SQL Server is Full recovery. If you don't do transaction log backups, then the tran log will keep growing. For a personal use database, probably not much of an issue, but for a small database for business use, this could turn into a big problem at a later date (I've seen this happen).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search