SQL Server行大小限制和表设计 [英] Sql Server row size limit and table design

问题描述

我在SQL Server 2008上有此查询

 创建表MediaLibrary 
（
 MediaId bigint NOT空IDENTITY（1、1），
 MediaTypeId smallint非空，
 ImageNameByUser nchar（100）空，
 GeneratedName uniqueidentifier非空，
 UploadedByUserId uniqueidentifier非空，
 UploadedDate日期NOT NULL，
 ProfilePhoto位NOT NULL，
 PublicPhoto位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 
  

尝试创建表时出现此错误

或更改表 MediaLibrary失败，因为最小行大小为14273，包括包含9个字节的内部开销。这超出了8060字节的最大允许表行大小。

我知道我正在达到行大小的限制，但这是不是一张大桌子，所以我想知道这不是一个好的设计吗？

当我更改 nchar（1000）到 varChar（1000）保存该表。我担心的是，一旦实际将数据保存到表中，我将再次达到行大小限制。

解决方案

假设您不会填充所有列，您需要使用nvarchar（或仅varchar）而不是nchar（或char）。原因是 nchar（1000）需要保留2000个字节，无论您是否要使用它。

现在，如果您可能在每列中都包含1000个字符，那么无论如何它都将不起作用您使用的数据类型。原因是SQL Server中的基本存储元素是8K页。因此，不可能存储超过8K的行（存在某些页眉开销以及根据列中的数据类型可能使用的其他位）。解决方法通常是：

• varchar（max）-它可以存储不适合行外作为blob的数据，但是为此会有性能开销，并且可能会引入一些限制，例如可以执行在线重建



• 更改表结构，以便将这些URL作为单独的行存储在单独的表中。示例：

   创建表dbo.Media 
  （
   MediaID BIGINT IDENTITY（1,1）PRIMARY KEY ，
   MediaTypeID SMALLINT不为NULL，
   ImageNameByUser NVARCHAR（100）NULL，-也不应为nchar 
   GeneratedName UNIQUEIDENTIFIER不为NULL，
   UploadedByUserId UNIQUEIDENTIFIER不为NULL，
   UploadedDate日期NOT NULL，
   ProfilePhoto位NOT NULL，
   PublicPhoto位NOT NULL，
   AppointmentId bigint NULL，
   InactiveReasonId smallint NULL，
   InactiveDate datetime NULL 
   ）; 
   
  创建表dbo.URLTypes 
  （
   URLTypeID TINYINT非空主键，
  描述NVARCHAR（32）非空唯一
  ）; 
   
  插入dbo.URLTypes VALUES（1，'OriginalImage'），（2，'ThumbImage'），...; 
   
  创建表dbo.MediaURLs 
  （
   MediaID BIGINT非空外键引用dbo.Media（MediaID），
   URLTypeID TINYINT非空外键引用dbo.URLTypes （URLTypeID），
   URL VARCHAR（2048）NOT NULL 
  ）; 
    

顺便说一句，您真的要是否需要为URL支持Unicode？

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.

解决方案

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?

这篇关于SQL Server行大小限制和表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！