使用DEFAULT CONSTRAINTs或BOUND DEFAULT？ [英] Use DEFAULT CONSTRAINTs or BOUND DEFAULTs?
问题描述
我正在对Google进行一些关于这个主题的研究，我遇到了这个主题的
：
http://groups.google.com/group/micro...c13d4ee6758966
实际上他们更有可能放弃绑定默认值的概念。
约束是执行此操作的标准方法，而且确实应该是这样的方式
无论如何都要创建默认值。
即使我读过Microsoft SQL Server简介（SQL 7书籍
第244页，但是我们''正在使用SQL Server 2000）：
"约束定义关于列中允许的值的规则，并且是
enfornte的标准机制g完整性，优先于触发器，
规则和默认值。查询优化器还使用它们来提高选择性估算，成本计算和查询中的b $ b性能。
重写。
为什么约束默认值更好？关于约束的第二句话
有更好的优化，我猜它们并不是指这个关于
默认约束，而是其他类型的约束？
因为我没看到默认约束与
性能有什么关系？不是默认只与创建新记录有关吗？
在工作中我们将所有表的列设置为具有约束默认值
为0或''' '（空格字符），以便没有任何具有
NULL值的列。因此我们有几十个包含以下语句的文件：
alter table TABLE1 add constraint TABLE1_ID_DF
DEFAULT（''''）FOR ID
go
alter table TABLE1 add constraint TABLE1_QUANTITY_DF
DEFAULT（0）FOR QUANTITY
go
首先我想创建3个SQL默认值：
DefaultZero
DefaultSpace
DefaultDate
然后将这些默认值绑定到所有表的所有列，不包括
主键。在创建表格后，我将通过
列出所有列，并根据它们的
数据类型绑定这三个默认值中的一个：
number = DefaultZero
文本类型= DefaultSpace
日期类型= DefaultDate
然后取消绑定我们特别需要指定的其他
默认值。
所以我的问题是我应该通过使用sp_binddefault或使用表格内的默认约束来粘贴
列循环代码？
谢谢
I am doing a little research on Google about this topic and I ran into
this thread:
http://groups.google.com/group/micro...c13d4ee6758966
I read SQL Server MVP Louis Davidson''s post saying:
"Actually they are more likely to drop the concept of bound defaults.
Constraints are the standard way to do this, and really should be the way
you create defaults anyhow."
Even I read in the Microsoft SQL Server Introduction (SQL 7 book
page 244, however we''re using SQL Server 2000):
"Constraints define rules regarding the values allowed in columns and are
the standard mechanism for enforcing integrity, preferred over triggers,
rules, and defaults. They are also used by the query optimizer to improve
performance in selectivity estimation, cost calculations, and query
rewriting."
Why constraint defaults are better? The second sentence about constraints
having better optimization, I am guessing they don''t mean this about
Default Constraints, rather the other type of constraints?
Because I don''t see how a Default Constraint have anything to do with
performance? Isn''t default only to do with new records being created?
At work we are setting all tables'' columns to have constraint defaults
of 0 or '' '' (space character) in order not to have any column with the
NULL value. Therefore we have dozens of files containing statements like:
alter table TABLE1 add constraint TABLE1_ID_DF
DEFAULT('' '') FOR ID
go
alter table TABLE1 add constraint TABLE1_QUANTITY_DF
DEFAULT(0) FOR QUANTITY
go
First I was thinking to create 3 SQL Defaults called:
DefaultZero
DefaultSpace
DefaultDate
and then bind these defaults to all the columns of all tables excluding
primary keys. After creating the tables I would enumerate through
all the columns and bind one of these three Defaults based on their
datatype:
number = DefaultZero
text type = DefaultSpace
date type = DefaultDate
And then unbind the ones that we specifically need to specify other
default values.
So my question is should I do this by using sp_binddefault or stick
with using Default Constraints inside a table/columns loop code?
Thank you
推荐答案
" serge" < SE **** @ nospam.ehmail.com>在消息中写道
新闻：KQ ****************** @ weber.videotron.net ...
"serge" <se****@nospam.ehmail.com> wrote in message
news:KQ******************@weber.videotron.net...
我我正在谷歌对这个话题进行一些研究，我遇到了这个帖子：
http://groups.google .com / group / micro ... c13d4ee6758966
我读过SQL Server MVP Louis Davidson的帖子说：
实际上他们更有可能放弃绑定默认值的概念。
约束是执行此操作的标准方法，并且无论如何都应该是您创建默认值的方式。
即使我读了Microsoft SQL Server简介（SQL 7本页
第244页，但我们正在使用SQL Server 2000）：
"约束定义有关co中允许的值的规则lumns和
是强制执行完整性，首选触发器，规则和默认值的标准机制。查询优化器还使用它们来提高选择性估计，成本计算和查询重写的性能。
为什么约束默认值更好？关于约束的第二句话有更好的优化，我猜它们不是指这个关于
默认约束，而是其他类型的约束？
因为我没看到怎么样默认约束与
性能有什么关系？不仅仅是默认创建新记录吗？
在工作中我们将所有表的列设置为约束默认值为0或''''（空格字符） ）为了没有任何具有
NULL值的列。因此我们有几十个包含以下语句的文件：
alter table TABLE1 add constraint TABLE1_ID_DF
DEFAULT（'''）FOR ID
go
alter table TABLE1 add约束TABLE1_QUANTITY_DF
DEFAULT（0）FOR QUANTITY
go
首先我想创建3个SQL默认值：
DefaultZero
DefaultSpace
DefaultDate
然后将这些默认值绑定到除
主键之外的所有表的所有列。在创建表格之后，我将列举所有列并根据它们的
数据类型绑定这三个默认值中的一个：
number = DefaultZero
text type = DefaultSpace
date type = DefaultDate
然后取消绑定我们特别需要指定其他
默认值的那些。
所以我的问题是我应该通过使用sp_binddefault来做到这一点或者坚持使用表/列循环代码中的默认约束？
谢谢
I am doing a little research on Google about this topic and I ran into
this thread:
http://groups.google.com/group/micro...c13d4ee6758966
I read SQL Server MVP Louis Davidson''s post saying:
"Actually they are more likely to drop the concept of bound defaults.
Constraints are the standard way to do this, and really should be the way
you create defaults anyhow."
Even I read in the Microsoft SQL Server Introduction (SQL 7 book
page 244, however we''re using SQL Server 2000):
"Constraints define rules regarding the values allowed in columns and are
the standard mechanism for enforcing integrity, preferred over triggers,
rules, and defaults. They are also used by the query optimizer to improve
performance in selectivity estimation, cost calculations, and query
rewriting."
Why constraint defaults are better? The second sentence about constraints
having better optimization, I am guessing they don''t mean this about
Default Constraints, rather the other type of constraints?
Because I don''t see how a Default Constraint have anything to do with
performance? Isn''t default only to do with new records being created?
At work we are setting all tables'' columns to have constraint defaults
of 0 or '' '' (space character) in order not to have any column with the
NULL value. Therefore we have dozens of files containing statements like:
alter table TABLE1 add constraint TABLE1_ID_DF
DEFAULT('' '') FOR ID
go
alter table TABLE1 add constraint TABLE1_QUANTITY_DF
DEFAULT(0) FOR QUANTITY
go
First I was thinking to create 3 SQL Defaults called:
DefaultZero
DefaultSpace
DefaultDate
and then bind these defaults to all the columns of all tables excluding
primary keys. After creating the tables I would enumerate through
all the columns and bind one of these three Defaults based on their
datatype:
number = DefaultZero
text type = DefaultSpace
date type = DefaultDate
And then unbind the ones that we specifically need to specify other
default values.
So my question is should I do this by using sp_binddefault or stick
with using Default Constraints inside a table/columns loop code?
Thank you
默认约束更接近标准SQL，根据我的经验，这是更传统的解决方案，大多数人使用SQL
Server更好地理解。以下是SQL Server 2005联机丛书中有关
遗产CREATE DEFAULT / sp_binddefault语法的引用：
" CREATE DEFAULT将在未来删除Microsoft SQL Server的版本。
避免在新的开发工作中使用CREATE DEFAULT，并计划修改当前使用它的
应用程序。相反，使用ALTER TABLE或CREATE TABLE的DEFAULT关键字创建创建的默认定义
。
-
David Portas
SQL Server MVP
-
Default constraints are closer to standard SQL and in my experience are the
more conventional solution and better understood by most people using SQL
Server today. Here''s a quote from SQL Server 2005 Books Online about the
legacy CREATE DEFAULT / sp_binddefault syntax:
"CREATE DEFAULT will be removed in a future version of Microsoft SQL Server.
Avoid using CREATE DEFAULT in new development work, and plan to modify
applications that currently use it. Instead, use default definitions created
using the DEFAULT keyword of ALTER TABLE or CREATE TABLE."
--
David Portas
SQL Server MVP
--
On Sun，2005年10月30日16:22:49 -0500 ，serge写道：
（snip）
On Sun, 30 Oct 2005 16:22:49 -0500, serge wrote:
(snip)
为什么约束默认值更好？关于约束的第二句话有更好的优化，我猜它们不是指这个关于
默认约束，而是其他类型的约束？
因为我没看到怎么样默认约束与
性能有什么关系？不仅仅是默认创建新记录吗？
嗨Serge，
我倾向于同意你的看法。我不知道默认值对b / b $ b值的了解如何对优化器有任何用处。
在工作中我们设置所有表''要使约束默认值为0或'''（空格字符）的列，以便不具有任何具有
NULL值的列。
虽然确实应该谨慎使用NULL，但也应该小心避免使用它们。如果您的业务性质为
，以至于您必须处理缺失的数据，那么最好使用
专门为缺失日期设计的令牌（即NULL）而不是
与占位符混淆。
你如何区分0的奖金（我们考虑过它并决定
不给奖励）奖金为0（对于任何一个
可能的原因，在这个
时刻没有关于奖金的信息）。
另外，你是否知道所有这些零和空格都会使你贬值
汇总值，并且它们会影响查询的逻辑？
最后 - 你将如何处理日期时间变量？既不是0也不是''''可以存储在
中！
因此我们有几十个包含以下语句的文件：
alter table TABLE1 add constraint TABLE1_ID_DF
DEFAULT（'''）FOR ID
go
alter table TABLE1 add constraint TABLE1_QUANTITY_DF
DEFAULT（0）FOR QUANTITY
go
不需要 - 您可以在CREATE TABLE
语句中包含默认值。
CREATE TABLE测试
（Col1 int NOT NOT DEFAULT 0，
Col2 varchar（20）NOT NULL DEFAULT''''
）
INSERT INTO测试默认值
SELECT * FROM测试
所以我的问题是我应该使用sp_binddefault还是坚持
在表/列循环代码中使用默认约束？
Why constraint defaults are better? The second sentence about constraints
having better optimization, I am guessing they don''t mean this about
Default Constraints, rather the other type of constraints?
Because I don''t see how a Default Constraint have anything to do with
performance? Isn''t default only to do with new records being created?
Hi Serge,
I''m inclined to agree with you. I don''t see how knowledge of the default
values could be of any use for the optimizer.
At work we are setting all tables'' columns to have constraint defaults
of 0 or '' '' (space character) in order not to have any column with the
NULL value.
Though it''s true that NULLs should be used with care, it''s also true
that they should be avoided with care. If the nature of your business is
such that you have to deal with missing data, then it''s better to use
the token specifically designed for missing date (i.e. NULL) instead of
mucking around with placeholders.
How will you distinguish a bonus of 0 (we thought about it and decided
not to give a bonus) from a bonus of 0 (for any of a whole lot of
possible reasons, no information is known about the bonus at this
moment).
Also, are you aware that all these zeros and spaces will muck up you
aggregate values, and that they can impact the logic of your queries?
Finally - how will you handle datetime variables? Neither 0 nor '' '' can
be stored in them!
Therefore we have dozens of files containing statements like:
alter table TABLE1 add constraint TABLE1_ID_DF
DEFAULT('' '') FOR ID
go
alter table TABLE1 add constraint TABLE1_QUANTITY_DF
DEFAULT(0) FOR QUANTITY
go
No need for that - you can include the default in the CREATE TABLE
statement.
CREATE TABLE Test
(Col1 int NOT NULL DEFAULT 0,
Col2 varchar(20) NOT NULL DEFAULT '' ''
)
INSERT INTO Test DEFAULT VALUES
SELECT * FROM Test
So my question is should I do this by using sp_binddefault or stick
with using Default Constraints inside a table/columns loop code?
使用约束。绑定默认值是专有的，不可移植的; DEFAULT
约束在ANSI标准中定义。我的猜测是绑定
默认值仅保留在SQL Server中以实现向后兼容。我希望这个功能在SQL的未来版本中被弃用。
服务器。
Best，Hugo
-
（删除_NO_和_SPAM_以获取我的电子邮件地址）
Use constraints. Bound defaults are proprietary, non-portable; DEFAULT
constraints are defined in the ANSI standard. My guess is that bound
defaults are only retained in SQL Server for backwards compatibility. I
also expect this feature to become deprecated in a future version of SQL
Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
serge（se ****@nospam.ehmail.com）写道：
serge (se****@nospam.ehmail.com) writes:
所以我的问题是我应该使用sp_binddefault还是使用在表/列循环代码中使用默认约束来实现这一点？
So my question is should I do this by using sp_binddefault or stick
with using Default Constraints inside a table/columns loop code?
绑定的默认值和规则对于用户定义的类型非常有用，并且只要很长时间
Microsoft不提供真实域，它们应该不会想想
放弃这些结构。
但是如果你正在使用单个列，没有理由
使用绑定默认值和规则。改为使用约束。
-
Erland Sommarskog，SQL Server MVP， es ** **@sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books.asp
Bound defaults and rules are great for user-defined types, and as long
Microsoft does not provide real domains, they should just not think of
dropping these constructs.
But if you are working with individual columns, there is no reason to
use bound defaults and rules. Use constraints instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于使用DEFAULT CONSTRAINTs或BOUND DEFAULT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
