Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




August 2008 Blog Posts

Why Single-Column Primary/Foreign Keys Can't Always Accurately Model/Constrain Your Data

A Simple Single-Child Model Consider the following logical data model: There are multiple Companies Each Company has many Projects Each Project has many Tasks Each Task has a Status, selected from a global list of pre-defined Statuses.   Status   Companies       \       |        \      |         \  Projects          \    |           \   |            Tasks Let us say that we decide that the primary key of Companies, Projects, Tasks, and Status are all Identity (auto-number) columns, since we would like to auto-generate primary keys for those tables. So, we have something like...

posted @ Wednesday, August 13, 2008 11:06 AM | Feedback (51) | Filed Under [ Database Design ]

SQL Server 2005 Foreign Key Constraints: SET NULL and SET DEFAULT

Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity? Read all about them in my latest article over at These features, introduced with SQL Server 2005, haven't got a lot of publicity, but they can be very useful.  I just used the SET NULL option recently for the first time (inspiring me to put together an article on it) and it works great.

posted @ Wednesday, August 13, 2008 9:56 AM | Feedback (3) | Filed Under [ Database Design Links Joins/Relations ]

Database Column Names != Report Headings

Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.   For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer.  I often see programmers struggling with writing dynamic SQL to produce output like this: CustomerID   2008 Total    2007 Total   Variance ----------   ----------    ----------   -------- ABC          $100          $50          $50 DEF          $200          $250         -$50 That is, the names of the columns vary based on the data; that is not a good way...

posted @ Wednesday, August 06, 2008 11:43 AM | Feedback (3) | Filed Under [ Techniques Efficiency Report Writing ]

Powered by:
Powered By Subtext Powered By ASP.NET