Sql Server row size limit and table design

Question

I have this query on SQL Server 2008

CREATE TABLE MediaLibrary
(
MediaId bigint NOT NULL IDENTITY (1, 1),
MediaTypeId smallint NOT NULL,
ImageNameByUser nchar(100) NULL,
GeneratedName uniqueidentifier NOT NULL,
UploadedByUserId uniqueidentifier NOT NULL,
UploadedDate date NOT NULL,
ProfilePhoto bit NOT NULL,
PublicPhoto bit NOT NULL,
AppointmentId bigint NULL,
OriginalImage nchar(1000) NULL,
ThumbImage nchar(1000) NULL,
MediumImage nchar(1000) NULL,
LargeImage nchar(1000) NULL,
UrlThumb nchar(1000) NULL,
UrlMedium nchar(1000) NULL,
UrlLarge nchar(1000) NULL,
InactiveReasonId smallint NULL,
InactiveDate datetime NULL
)  ON [PRIMARY]
GO

When I attempt to create the table I get this error

Creating or altering table 'MediaLibrary' failed because the minimum row size would be 14273, including 9 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

I get that I am hitting the limit on row size, but this is not a big table so I am wondering if this is not a good design?

When I changed the nchar(1000) to varChar(1000) the table saved fine. My concern is that once data is actually getting saved into the table that I will hit the row size limit again.

Solution

Assuming you're not going to populate all columns, you need to use nvarchar (or just varchar) and not nchar (or char). The reason is that an nchar(1000) needs to reserve 2000 bytes, whether you're going to use it or not. This isn't true for varchar/nvarchar.

Now, if you are going to potentially have 1000 characters in each of these columns, it's not going to work no matter what data type you use. The reason is that the fundamental storage element in SQL Server is an 8K page. So it's not possible to store a row with more than ~8K (there is some page header overhead as well as other bits that may be used depending on data types in the column). The workarounds are typically to:

• varchar(max) - which can store data that doesn't fit off-row as a blob, but there is a performance overhead for this, and it can introduce some limitations, e.g. the ability to perform online rebuilds

• change the table structure, so that these URLs are stored as separate rows in a separate table. Example:

  CREATE TABLE dbo.Media
  (
    MediaID BIGINT IDENTITY(1,1) PRIMARY KEY,
    MediaTypeID SMALLINT NOT NULL,
    ImageNameByUser NVARCHAR(100) NULL, -- should also not be nchar
    GeneratedName UNIQUEIDENTIFIER NOT NULL,
    UploadedByUserId UNIQUEIDENTIFIER NOT NULL,
    UploadedDate date NOT NULL,
    ProfilePhoto bit NOT NULL,
    PublicPhoto bit NOT NULL,
    AppointmentId bigint NULL,
    InactiveReasonId smallint NULL,
    InactiveDate datetime NULL
  );
  
  CREATE TABLE dbo.URLTypes
  (
    URLTypeID TINYINT NOT NULL PRIMARY KEY,
    Description NVARCHAR(32) NOT NULL UNIQUE
  );
  
  INSERT dbo.URLTypes VALUES(1,'OriginalImage'),(2,'ThumbImage'),...;
  
  CREATE TABLE dbo.MediaURLs
  (
    MediaID BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Media(MediaID),
    URLTypeID TINYINT NOT NULL FOREIGN KEY REFERENCES dbo.URLTypes(URLTypeID),
    URL VARCHAR(2048) NOT NULL
  );
  

As an aside, are you really going to need to support Unicode for URLs?