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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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 →
The Importance of Data Types Imagine that SQL Server only provided two data types: the MONEY data type to store numeric values, and VARCHAR to store text. If you are designing a database in this scenario and you need to store or return integer values, which data type – MONEY or VARCHAR – would you use?
Read more →
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.
Read more →
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.
Read more →
I recently submitted my Data in tables versus data in code post from a ways back to reddit, and I was surprised that there ended up being quite a bit of discussion about it.
Read more →
Database design can be very complicated, and it truly is an art as opposed to a science; sometimes there are multiple correct ways to model the same data with pros and cons to each.
Read more →
Suppose you have the following tables:
create table Customers (CustomerID int identity primary key, CustomerName varchar(100) not null)create table AddressTypes (AddressType varchar(10) primary key)create table CustomerAddress (CustomerID int references Customers(CustomerID),AddressType varchar(10) references AddressTypes(AddressType),Street varchar(100),City varchar(100),State varchar(2),ZIP varchar(20),primary key (CustomerID, AddressType)) This is a simple schema for which a Customer can have multiple addresses, one per AddressType.
Read more →
Introduction 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 which for you’d like to calculate a particular bonus.
Read more →
Introduction <o:p>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?
Read more →
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 →
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. From the intro:
Read more →
Question: How do you create a DateTime in a specific format in SQL Server? Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type.
Read more →
It's been a while, so let's open up the old mailbag! Jeremy writes:
Can you spare a few minutes to show me a possible way to restrict a column in a table using a check constraint to only allow characters in a given range of the ASCII table?
Read more →
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!
Read more →
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 →