问题描述
使用SQL Server 2005与SP4,我正在设计一个数据库表。这是表DDL
CREATE TABLE CPSync4D.ProjectProfilerOption
/ pre>
(
ProjectProfilerOptionID INT IDENTITY(1,1)CONSTRAINT PK_ProjectProfilerOption_ProjectProfilerOptionID PRIMARY KEY
,ProjectID INT CONSTRAINT FK_ProjectProfilerOption_Project_ProjectID FOREIGN KEY(ProjectID)参考CPSync4D.Project(ProjectID)ON DELETE CASCADE
,ProfilerOptionID TINYINT CONSTRAINT FK_ProjectProfilerOption_ProfilerOption_ProfilerOptionID FOREIGN KEY(ProfilerOptionID)参考CPSync4D.ProfilerOption(ProfilerOptionID)
,ProfilerOptionValue sql_variant NOT NULL
)
Go
profileroptionvalue列可以包含一个字符串,最多可以包含30个字符,整数或十进制值,例如值为ProfilerValueType,或12.52或20等(不超过两个小数和整数值小于100)
我应该使用sql_variant还是varchar(30 )...?我从来没有使用过sql_variant,不知道在数据库设计方面没有使用任何含义。
使用sql_variant ...与.net代码的任何陷阱
解决方案
我避风港' t使用
sql_variant
之前,但考虑到这些限制和性能影响,我会先看看替代方案。
关注将是我最不喜欢的解决方案
- 只需创建三个不同的列。 3不同的数据类型(应该)意味着在客户端和服务器端解释它们的3种不同的方法。
- 如果不是选项,请使用
VARCHAR
列,以便您至少使用LIKE
语句。 - 使用
sql_variant
数据类型。
修改 Cudo到ta.speot。是
using SQL Server 2005 with SP4 and I am designing a database table.
Here is the table DDL
CREATE TABLE CPSync4D.ProjectProfilerOption
(
ProjectProfilerOptionID INT IDENTITY(1,1) CONSTRAINT PK_ProjectProfilerOption_ProjectProfilerOptionID PRIMARY KEY
,ProjectID INT CONSTRAINT FK_ProjectProfilerOption_Project_ProjectID FOREIGN KEY(ProjectID) REFERENCES CPSync4D.Project(ProjectID) ON DELETE CASCADE
,ProfilerOptionID TINYINT CONSTRAINT FK_ProjectProfilerOption_ProfilerOption_ProfilerOptionID FOREIGN KEY(ProfilerOptionID) REFERENCES CPSync4D.ProfilerOption (ProfilerOptionID)
,ProfilerOptionValue sql_variant NOT NULL
)
Go
profileroptionvalue column can hold either a string upto 30 characters, integer or decimal values e.g. values are "ProfilerValueType", or 12.52 or 20 etc. (no more than than two decimals and integer values are less than 100)
Should I use sql_variant or varchar(30)...? I never used sql_variant before and not sure any implication of not using in terms of database design.
Any pitfalls of using sql_variant...with .net code
10 reasons to explicitly convert SQL Server data types
I haven't used sql_variant
before but with these restrictions and performance implications in mind, I would first look at alternatives.
Following would be my most to least prefered solution
- Simply create three different columns. 3 Different data types (should) mean 3 different ways of interpreting it both at the client side and server side.
- If that is not an option, use a
VARCHAR
column so you can at least useLIKE
statements. - Use the
sql_variant
data type.
Edit Cudo's to ta.speot.is
Variants can be part of a primary of foreign key
这篇关于我应该使用SQL_Variant数据类型吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!