Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

New SQL Server Database Development Tutorial


By:   |   Last Updated: 2012-09-27   |   Comments (1)   |   Related Tips: More > T-SQL

SQL Server Database Development Tutorial

Read the tutorial!

In this tutorial we look at the different steps that one should perform or at least be familiar with when developing a database, such as:

  • Logical Design
  • Defining Data Types and Sizes
  • Defining a Primary Key
  • Defining a Clustered Index
  • Defining Constraints
  • Choosing Non-Clustered Indexes
  • Prepared Queries and Stored Procedures
  • Querying in Sets
  • Preventing Index Nullification
  • Temporary Tables and Table Variables
  • Working With Triggers

Read the tutorial!



Last Updated: 2012-09-27


get scripts

next tip button



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 02, 2012 - 11:36:41 PM - Graham Back To Top

"Another common option is to use a natural key as your primary key. For instance, a natural primary key could be a combination of LastName, FirstName, and MiddleName. I'll admit I'm biased against this approach for a couple of reasons. First, all the columns should be NOT NULL (known values) for this to be effective. Second, any child tables would need to have this same group of columns defined to establish a foreign key relationship. Moreover, what if any of the columns in the natural primary key require a change? This would necessitate cascading through and updating all child entries."


Yes I have came across this when using a database system that only allowed a limited fixed length primary key. The way to manage this is generate a "natural key" for the primary key based on the columns you have chosen. If some columns are NULL you need a system to generate some kind of data there. Note that the role of the primary key is not to provide a decoding ability. Its role is to link table rows.

So therefore if the data in the column which generated the natural key changes then do not allow that to trigger maintenance on the key. Leave the original natural key in place. 

The naturally generated primary key should not been seen as a source of information about the data which generated it. Yes it is possible to decode the key but that is not the point of the key.

 


Learn more about SQL Server tools