Sql Server row size limit and table design

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?