Sql Server行大小限制和表设计

I have this query on SQL Server 2008

我在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.

创建或修改表“MediaLibrary”失败，因为最小行大小为14273，包括9字节的内部开销。这超过了最大允许的8060字节的表行大小。

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.

当我将nchar(1000)更改为varChar(1000)时，表保存得很好。我担心的是，一旦数据被保存到表中，我将再次达到行大小限制。

1 个解决方案

#1

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.

假设您不打算填充所有列，那么需要使用nvarchar(或varchar)而不是nchar(或char)。原因是nchar(1000)需要保留2000字节，不管您是否要使用它。这不是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:

现在，如果每个列中都有1000个字符，无论使用哪种数据类型，它都不会工作。原因是SQL Server中的基本存储元素是一个8K页面。因此，不可能存储大于8K的行(存在一些页眉开销以及根据列中的数据类型可能使用的其他位)。解决方案通常是:

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
varchar(max)——它可以存储不适合作为blob的离线数据，但是存在性能开销，并且会引入一些限制，例如执行在线重新构建的能力

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

更改表结构，以便将这些url存储为单独表中的单独行。例子:

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?

顺便说一句，您真的需要支持url的Unicode吗?

#1

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
varchar(max)——它可以存储不适合作为blob的离线数据，但是存在性能开销，并且会引入一些限制，例如执行在线重新构建的能力