16 April 2010 13:56 tonyrogerson

Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server

The Slides and Demo's for the SQLBits session I did today at SQL Bits in London can be found here: http://sqlblogcasts.com/blogs/tonyrogerson/UKSSUG/SQLBits20100416NormSetsKeysRI.zip.

Feel free to email me tonyrogerson@torver.net if you have any queries or questions. 

The Agenda was...

  • Thinking in Sets
  • Surrogate Keys
ú  What they areú  Comparison NEWID, NEWSEQUENTIALID, IDENTITYú  Fragmenation
  • Normalisation
ú  An introduction – what is it? Why use it?ú  Joins – Pre-filter problems, index intersectionú  Fragmentation again
  • Referential Integrity
ú  Optimiser -> Query rewriteú  Locking considerations around Foreign Keys and Declarative RI (using Triggers)

 

Filed under: , ,

Comments

# Twitter Trackbacks for Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server - Tony Rogerson's ramblings on SQL Server [sqlblogcasts.com] on Topsy.com

16 April 2010 14:20 by Twitter Trackbacks for Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server - Tony Rogerson's ramblings on SQL Server [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server - Tony Rogerson's ramblings on SQL Server         [sqlblogcasts.com]        on Topsy.com

# re: Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server

16 April 2010 16:27 by robvolk

Hi Tony-

I don't see the link for the attachments on the web site.  It came through the RSS feed though.

I also couldn't find the slides in the Nomalisation.zip file.

-Rob Volk

# re: Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server

16 April 2010 16:49 by tonyrogerson

Slides fixed- here: sqlblogcasts.com/.../sets-surrogates-normalisation-referential-integrity-the-theory-with-example-scaling-considerations-in-sql-server.aspx

.ZIP contains both deck and samples.

# re: Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server

19 April 2010 08:55 by jt1699

Hi Tony,

Thanks for talking at SQL Bit VI, I was wondering if you could elaborate a little on what the "pre-filter problem" is with a normalised database.

thx

Jag

# re: Sets, Surrogates, Normalisation, Referential Integrity - the Theory with example Scaling considerations in SQL Server

19 April 2010 09:02 by tonyrogerson

I'll go into more depth this Wed night at the user group meeting in Reading.

Basically when you get a Nested Loop join you end up reading through the top input (I call the pre-filter) and seek into the bottom input which when you have lots of tables (normalisation) can cause perf problems - its one of (my main reason) for denormalising - so you don't have to go through multiple indexes to get at your data.

Merge join is your friend there, but even still it requires IO.

Hope that helps.