各种数据类型的SQL设计

BigTable . 如果您真的想使用关系数据库系统,我强烈建议您阅读 开始数据库设计

就性能而言,使用#3中所示的表可能会浪费大量的存储和RAM,因为对于每一行,您为每种类型的值分配空间,但只使用一个。如果您使用2008年新增的稀疏表功能,可能会有所帮助,但也存在其他问题:约束/规范化有点困难,因为您只希望为每行填充多个值中的一个-在两列中包含两个值将是一个错误,但设计没有反映这一点。我会把它划掉。

因此,如果是我,我会考虑选项1、2或4,决策将由以下因素驱动:我通常需要进行一次查询,返回具有 同一结果集中不同类型的值的数量?或者我几乎总是要求按项目列出行 和 按类型。我之所以这样问是因为如果值是不同的类型,这对我来说意味着源或数据使用方面的一些差异(例如,您不太可能比较字符串和实数,或者字符串和位)。这是相关的,因为每种类型有不同的表实际上可能是一个显著的性能/可伸缩性优势,如果以这种方式对数据进行分区,则查询会更快。将数据划分为更小的更密切相关的数据集可以提供性能优势。

细节:

CREATE TABLE [dbo].[items](
    [itemid] [int] IDENTITY(1,1) NOT NULL,
    [item] [varchar](100) NOT NULL,
 CONSTRAINT [PK_items] PRIMARY KEY CLUSTERED 
(
    [itemid] ASC
)
) 

/* This table has the problem of allowing two values 
in the same row, plus allocates but does not use a 
lot of space in memory and on disk (bad): */

CREATE TABLE [dbo].[vals](
    [itemid] [int] NOT NULL,
    [datestamp] [datetime] NOT NULL,
    [valueBit] [bit] NULL,
    [valueNumericA] [numeric](2, 0) NULL,
    [valueNumericB] [numeric](8, 2) NULL,
    [valueReal] [real] NULL,
    [valueString] [varchar](100) NULL,
 CONSTRAINT [PK_vals] PRIMARY KEY CLUSTERED 
(
    [itemid] ASC,
    [datestamp] ASC
)
) 

ALTER TABLE [dbo].[vals]  WITH CHECK 
ADD  CONSTRAINT [FK_vals_items] FOREIGN KEY([itemid])
REFERENCES [dbo].[items] ([itemid])
GO

ALTER TABLE [dbo].[vals] CHECK CONSTRAINT [FK_vals_items]
GO


/* This is probably better, though casting is required 
all the time. If you search with the variant as criteria, 
that could get dicey as you have to be careful with types, 
casting and indexing. Also everything is "mixed" in one 
giant set */

CREATE TABLE [dbo].[allvals](
    [itemid] [int] NOT NULL,
    [datestamp] [datetime] NOT NULL,
    [value] [sql_variant] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[allvals]  WITH CHECK 
ADD  CONSTRAINT [FK_allvals_items] FOREIGN KEY([itemid])
REFERENCES [dbo].[items] ([itemid])
GO

ALTER TABLE [dbo].[allvals] CHECK CONSTRAINT [FK_allvals_items]
GO


/* This would be an alternative, but you trade multiple 
queries and joins for the casting issue. OTOH the implied
partitioning might be an advantage */

CREATE TABLE [dbo].[valsBits](
    [itemid] [int] NOT NULL,
    [datestamp] [datetime] NOT NULL,
    [val] [bit] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[valsBits]  WITH CHECK 
ADD  CONSTRAINT [FK_valsBits_items] FOREIGN KEY([itemid])
REFERENCES [dbo].[items] ([itemid])
GO

ALTER TABLE [dbo].[valsBits] CHECK CONSTRAINT [FK_valsBits_items]
GO

CREATE TABLE [dbo].[valsNumericA](
    [itemid] [int] NOT NULL,
    [datestamp] [datetime] NOT NULL,
    [val] numeric( 2, 0 ) NOT NULL
) ON [PRIMARY]

GO

... FK constraint ...

CREATE TABLE [dbo].[valsNumericB](
    [itemid] [int] NOT NULL,
    [datestamp] [datetime] NOT NULL,
    [val] numeric ( 8, 2 ) NOT NULL
) ON [PRIMARY]

GO

... FK constraint ...

etc...

什么是使用场景?从查询示例开始,计算必要的索引。 考虑前面提到的数据划分。尽量多了解您的数据/关系。我认为决策应基于数据的商业意义/用途。