Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

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.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Database Design

Database design and modeling.
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 SQLTeam.com. 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 ]

The Golden Rule of Data Manipulation

Introduction There is a very simple rule when it comes to storing (and returning) data, which I see violated all the time, making life so much more complicated for everyone involved.  In case you haven't noticed, that's a common theme I discuss here on this blog -- different ways programmers make life more difficult for themselves, instead of simply following good practices and doing things the easy way.  This is yet another example of that situation. The "Golden Rule of Data Manipulation" is a simple, but important rule that you should always follow when designing a database,  writing database code, or really...

posted @ Wednesday, June 11, 2008 11:48 AM | Feedback (9) | Filed Under [ Database Design ]

An INT primary key .... yet not an Identity?

Ah, this is not an anti-identity rant, don't worry! Though, in a round-a-bout sort of way, it is yet another argument against always blindly using them -- but not in the way you might expect. There is a simple rule I'd like to propose, let me know what you think: "If your client code, SQL code, or configuration files reference the primary key column of a table to determine any application logic, that primary key column should not be an identity." Now, I am not saying that primary key column can't be a meaningless integer.  I am just saying it should not be an...

posted @ Thursday, February 21, 2008 12:08 PM | Feedback (12) | Filed Under [ Miscellaneous Database Design ]

Implementing Table Inheritance with SQL Server

I have a new article up at SQLTeam: Implementing Table Inheritance with Sql Server It discusses the situation where you have multiple entities that are distinct, yet they have many attributes or relations in common.   There is an easy way to simplify your database design and your code if you use the concept of a "base table" for that common data, which is very similar to the concept of Inheritance in Object-Oriented programming. I recently had to do this for a client that tracks Contributions to their foundation; there is a base set of data that all Contributions have, such as who donated,...

posted @ Thursday, February 21, 2008 11:05 AM | Feedback (6) | Filed Under [ SQL Server 2005 Database Design Links ]

Interesting Database Modeling Dilemma

Let's say you have a database that contains Companies, Products and Stores. Products and Stores are unique to each company -- i.e., they are not "shared" across companies. read more...

posted @ Monday, January 07, 2008 10:52 AM | Feedback (13) | Filed Under [ Database Design ]

Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME

The next time you are working with dates and times, please remember: how would you handle things if you were working with integers and decimals? The same logic and reasoning applies. Be smart, let SQL do the work for you and use the right data types for the job, even if things don't always "look" right. read more...

posted @ Wednesday, August 29, 2007 10:04 AM | Feedback (33) | Filed Under [ T-SQL Efficiency Database Design DateTime Data ]

Composite Primary Keys

Ah … primary keys … such a topic!  When discussing what columns to define as a primary key in your data models, two large points always tend to surface: Surrogate Keys versus Natural Keys Normalization These can be very complicated and sometimes polarizing things to debate.   As I often try to do, I will attempt to approach this topic from a slightly different perspective. Let's start things off with what I feel is a good interview question: How would you define what a primary key of a table is? a.    An auto-generated numeric or GUID column in the...

posted @ Thursday, August 23, 2007 10:07 AM | Feedback (81) | Filed Under [ Techniques Database Design ]

The problem isn't the poor database model; It's that external code is bound to the schema

Dealing with poorly designed databases is a simple and common fact of life for programmers. It happens, sometimes due to lack of experience or education, or sometimes because business requirements were never analyzed properly or they changed. It's hard to avoid poor database designs, but it takes only a simple concept to make fixing those designs much easier. read more...

posted @ Wednesday, August 15, 2007 9:34 AM | Feedback (7) | Filed Under [ Database Design ]

Distinguishing data from code

What is data, and what is code? How do we define the difference, and decide what goes where? It is great to say "keep data out of your code", but what if that data is integral to the application itself? Isn't it therefore code, and not data? read more...

posted @ Tuesday, July 24, 2007 9:02 AM | Feedback (18) | Filed Under [ Miscellaneous Database Design ]

Data Types -- The Easiest Part of Database Design

I see it time and time again in forums -- "dates" that don't sort properly, "numbers" that don't add correctly, "boolean" data with 10 different values, and so on ... Since we are rarely provided any DDL to review, it often takes many posts going back and forth until we finally realize: "wait ... you aren't using a datetime data type to store these dates?!" read more...

posted @ Tuesday, July 03, 2007 10:13 AM | Feedback (9) | Filed Under [ Miscellaneous Database Design DateTime Data ]

Retrieving Identity Values When Inserting Multiple Rows

You're INSERTing multiple rows from an un-normalized import table. Each row is assigned an identity primary key. You know you can use scope_identity() to retrieve one identity at a time, but how do you retrieve a whole set of them? Are cursors the only answer? As usual, it depends on having logical specifications and a good design. read more..

posted @ Monday, July 02, 2007 12:31 PM | Feedback (9) | Filed Under [ T-SQL Database Design Imports/Exports ]

Passing an Array or Table Parameter to a Stored Procedure

SQL is a set-based language, and often we wish to pass sets of data as a parameter to a stored procedure. For example, you might wish to pass a set of Customers to an invoice generating stored procedure, or a set of employees for which you’d like to calculate a particular bonus. SQL Server already has everything you need to do this, and you don't need CSV or XML strings. read more...

posted @ Tuesday, June 26, 2007 9:36 AM | Feedback (29) | Filed Under [ T-SQL Techniques Database Design ]

SQL Data Modeling: Entities versus Attributes

There’s a handy little rule of thumb I use when developing my database models. It comes up often in situations like this: If we are storing phone numbers in the database, should AreaCode be a “free-form” char(3) column? Or should it be a foreign key reference to an AreaCodes table? read more...

posted @ Tuesday, June 19, 2007 11:47 AM | Feedback (3) | Filed Under [ Techniques Database Design DateTime Data ]

Don't Let Output Dictate your Database Design

I often talk about "database layer" versus "presentation layer", but even the within just the database layer it is important to understand that how the data is physically stored does not always have to correlate with how the database returns results. read more...

posted @ Tuesday, June 05, 2007 8:55 AM | Feedback (8) | Filed Under [ Database Design ]

Custom Auto-Generated Sequences in SQL Server

Be sure to visit SQLTeam.com to check out my latest article, Custom Auto-Generated Sequences in SQL Server. It addresses a common question we see in the SQL Team forums.

posted @ Tuesday, April 24, 2007 8:36 AM | Feedback (3) | Filed Under [ T-SQL Database Design Links ]

How to format a Date or DateTime in SQL Server

Everything you ever wanted to know about how to use SQL Server's advanced features to format Dates into any format that you need! Here, for the first time, all in one place, is the secret that no one wants you know about how easy it is to format data in T-SQL!

read more...

posted @ Friday, April 13, 2007 4:45 PM | Feedback (56) | Filed Under [ T-SQL Techniques Efficiency Database Design DateTime Data ]

SQL Check Constraints: Like George Costanza, sometimes you need to do the Opposite!

It's been a while, so let's open up the old mailbag. Today's question is about writing a character-based check constraint. The answer: Simple ... do the *opposite* !

read more..

posted @ Thursday, April 05, 2007 2:26 PM | Feedback (16) | Filed Under [ T-SQL Database Design ]

Unique Passwords?

Today's article at WorseThanFailure.com is one that the SQL Server community might find enjoyable.  I may try to implement something similar in my next project .... or, maybe not! Are there another other great designs like this that you've come across?  Maybe even some that you designed yourself?  Come on, you can admit it, let us know! I won't tell anyone.  After all, no one ever reads the comments ... see also: Discussing VB and/or MS Access in a Programming Forum Top 10 Things I Hate About SQL Server SQLTeam Dating Advice ! ...

posted @ Tuesday, April 03, 2007 7:38 PM | Feedback (1) | Filed Under [ Humor Database Design Links ]

Dear DBA ....

Dear DBA -- Thanks so much for helping us developers out with the latest changes you've made to the stored procedures in our system. While it may have been nice if we got together first to discuss these changes, I do appreciate that you worked very hard to make things much easier for us.

read more...

posted @ Wednesday, March 28, 2007 12:36 PM | Feedback (13) | Filed Under [ Techniques Efficiency Humor Database Design ]

Data belongs in your tables -- not in your code

Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables. Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code.

read more..

posted @ Friday, February 10, 2006 10:41 AM | Feedback (37) | Filed Under [ T-SQL Techniques Efficiency Database Design ]

You've got to have an IDENTITY!

Here's kind of a funny/scary thread over at the DailyWTF:

read more...

posted @ Friday, November 04, 2005 11:18 PM | Feedback (3) | Filed Under [ Techniques Humor Database Design Links ]

Delete Duplicates And Resolve Foreign Key References in SQL

Here's a scenario many of us have encountered: You inherit a database. TableX has an identity column "ID" set up as the primary key, and contains many duplicates.

read more...

posted @ Thursday, October 07, 2004 11:15 AM | Feedback (4) | Filed Under [ T-SQL Database Design Joins/Relations ]

Powered by:
Powered By Subtext Powered By ASP.NET