Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

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. create table Companies(CompanyID int identity primary key,Name varchar(100))create table Products(ProductID int identity primary key,CompanyID int references Companies(CompanyID) not null,Name varchar(100))create table Stores(StoreID int identity primary key,CompanyID int references Companies(CompanyID) not null,Name varchar(100)) OK, looks very simple and standard, right? Read more →

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

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 →

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. Read more →

Retrieving Identity Values When Inserting Multiple Rows

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 →

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! Read more →

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 →