精华内容
下载资源
问答
  • 员工辞职,马上也会有相应的制约措施,也即将推进人设的信用体系建设。那些频繁跳槽的员工,就业信用一定就有问题,就会影响个人征信。 互联网飞速发展的这些年,不可否认的是,跳槽率确实大有提升。说实话,企业...

    征信的重要性,想必大家都知道,买过房贷过款或者转过户口的人都少不了用到征信报告。征信报告,不说一毛钱,哪怕一分钱的违约记录都会被记录在案,如果被是用在贷款上面的话,利率就是几个点的上浮。没事的话,千万别做违背征信的事,一旦记录,这辈子都消不掉。

    对我们一生来说,征信这么重要的东西,现在却有可能被用在制约我们工作的工作选择上面。前两天,一条名为「浙江拟用征信约束频繁跳槽」的微博话题,备受争议。

    我看了一下相关视频,事情原委大概是这样的。在人力社保专家与企业人事的一场座谈会上,企业方面代表诉苦。企业人事的大意是招工难,留住人才更难,企业辞退员工,需要提前一个通知,并且赔偿一个月薪资,违法辞退更是需要赔偿两个月。而员工主动辞职,企业一点办法都没有。

    接下来就厉害了,人社厅的回应可谓是在网络上一石激起千层浪。员工辞职,马上也会有相应的制约措施,也即将推进人设的信用体系建设。那些频繁跳槽的员工,就业信用一定就有问题,就会影响个人征信。

    互联网飞速发展的这些年,不可否认的是,跳槽率确实大有提升。说实话,企业留住人才很难,但是我想,这不应该都是员工的问题吧。

    借用马云的一句话,员工离职的原因很多,但其实也就两点:1,钱,没给到位;2,心,受委屈了。那些离职率高于行业水准的企业,不应该好好从这两方面好好反思一下自己的原因吗?

    这里我当然不是鼓励企业无谓的向员工低头,要钱给钱,要权给权,但是你想,员工留着一家公司,不能只是为了所谓的理想,说不好听点,就是「大饼」吧。如果员工没有拿到市场和行业基本的平均薪资待遇,又不能在你这学到将来立身处世的技术本领,青春总共就这几年,荒废了,未来谁来买单?举个身边的例子,一个前同事,被中间人介绍给一个创业公司的老板。两个前后好几次见面,彼此都了解的差不多了,到了最后谈工资的问题,垮了。那个老板不愿意给钱,还希望同事降薪工作,然后各种开始画大饼,开始天上地上地描述公司的前景。甚至说到自己作为老板,每个月也就从公司那一万块钱作为生活开支,和员工一样。

    最后我那个同事在薪资方面妥协了,希望拿到一点股权,哪怕零点几的股权都行。老板还是不愿意,当然也妥协了,拿期权说事,承诺给到几十万期权。期权这个东西,对于创业公司来说,基本遥遥无期。最后不垮,才怪呢。

    这个例子说的有点多了,我想表达的,企业与员工都应该互相理解一点。有理想是一件好事,但是生活是实实在在的,生活从来不画大饼。你敢和跟房东说,这个月房租下个月再付吗?

    说回征信约束跳槽这件事,我肯定是不支持的。征信背负的东西太多了,如果再加上个人职业选择,年轻人的压力真的太大了。那个杭州逆行小伙歇斯底里的样子,再也不想看见了。

    推荐阅读

    集体参观看守所!论反腐,就服京东!集体参观看守所!论反腐,就服京东!算法之道!
    学习与调试 Framework

    站在程序员的角度,陪你唠唠 2019 年的嗑儿

    展开全文
  • 员工满意度企业的重要性

    千次阅读 2019-04-23 18:59:11
    企业的管理要受员工满意度的约束,管理成败的关键就在于员工是否在同样的价值观的熏陶下认同企业的管理理念境。成功的企业都十分重视员工满意度的分析;反之,忽视员工满意度的调查,企业必然陷入困境。 1、我认为...

    员工满意度调查是企业管理活动的基础性工作之一。企业的管理要受员工满意度的约束,管理成败的关键就在于员工是否在同样的价值观的熏陶下认同企业的管理理念境。成功的企业都十分重视员工满意度的分析;反之,忽视员工满意度的调查,企业必然陷入困境。

    1、我认为企业的第一要务就是为客户创造价值。有些创业型企业,刚刚开始条件很艰苦,而员工一直保持不离不弃,同甘共苦让企业节约大量的成本。管理者只能通过日常管理活动对人的需要施加影响和引导,而不能凭主观臆想加以创造。所以我们要进行员工工作满意度调查。因为员工的满意是企业管理的起点也是归宿。

    2、企业的客户分为内部客户和外部客户,外部客户的价值实现是靠内部客户实施的。企业在同时创造两方面的价值,从时间和空间的角度来说企业首先为内部客户——员工创造价值。彼得•德鲁克(Peter F•Drucker)说:客户不是在购买某一种“产品”,而是购买需求的满意度。要满足客户的需求一定要通过员工的劳动,无论是制造产品还是提供服务。员工满意度决定着提供的产品还是服务的好坏。所以从说整个管理的提升来讲员工满意度调研是管理的基础。

    3、企业的利润不是来源于产品而是来源于人,也就是您的员工,没有他们的劳动就不会有企业,留住优秀的员工是企业人力资源管理的重要内容,而员工满意度的调查就是留住、培养他们的基础。员工满意度的调查是人力资源决策的基础和前提。它可以帮助企业对人力资源状况做出客观的判断,对其自身条件做出正确的分析,明确自身的优势和劣势,使企业的内部条件、管理目标与市场环境实现动态的平衡,为提高企业竞争力效果创造有利的条件。

    4、任何企业都要有目标无论是一个还是多个,是盈利还是满足客户,这个目标是由员工来实现的,他们是执行者,他们是否满意是企业达到目标的重要因素,企业的职能就是帮助许多人实现自己的目标,其中最为重要的就是企业里的员工,员工的思想脉络是我们实现企业目标的前提。是形成优秀企业文化的基石。

    5、企业核心竞争力,应该由员工来形成的,在企业中某一个人的能力是无法体现出企业的核心竞争力,而由员工组成的团队或组织才会形成企业的核心竞争力。组织的目的是使平凡的人做出不平凡的事。组织不能依赖于天才。因为天才稀少如凤毛麟角。而员工满意度是企业内部无形组织的关键,满意度的提升可以是潜在能力爆发出来有利于企业的成长和员工的进步,而对企业的满意度降低会直接影响这种潜在组织。

    6、员工工作满意度调查相对于其他管理方法要简单、专注。对于我们来说我们有专业的人员、强大的后台支持、多年的经验、同业的对比数据,同时我们会客观公正的处理数据和提交报告的。另外我们提交的报告中将会明确改进措施,这些改进措施是可实施的并且会给您带来价值的。

    7、员工满意度调研的价值还可以从三方面来体现,首先从研发角度,员工的满意带来很多益处,作为员工来讲一旦认可企业文化,对企业抱有感恩的姿态,那么就会有许多技术改进措施得以提出。实践证明在员工满意度高的企业,员工工作的热情要高。

    8、员工工作满意度调查是将复杂的东西简单化,简单的东西量化,量化的东西流程化,流程化的东西执行化。

    展开全文
  • 约束理论

    千次阅读 2017-12-12 15:49:34
    约束理论概述  约束理论(Theory of Constraints, TOC)是以色列物理学家、企业管理顾问戈德拉特博士(Dr.Eliyahu M.Goldratt)在他开创的优化生产技术(Optimized Production Technology,OPT)基础上发展...

    约束理论概述

      约束理论(Theory of Constraints, TOC)是以色列物理学家、企业管理顾问戈德拉特博士(Dr.Eliyahu M.Goldratt)在他开创的优化生产技术(Optimized Production Technology,OPT)基础上发展起来的管理哲理,该理论提出了在制造业经营生产活动中定义和消除制约因素的一些规范化方法,以支持连续改进(Continuous Improvement)。同时TOC也是对MRPIIJIT在观念和方法上的发展。

      戈德拉特创立约束理论的目的是想找出各种条件下生产的内在规律,寻求一种分析经营生产问题的科学逻辑思维方式和解决问题的有效方法。可用一句话来表达TOC,即找出妨碍实现系统目标的约束条件,并对它进行消除的系统改善方法。

      TOC强调必须把企业看成是一个系统,从整体效益出发来考虑和处理问题,TOC的基本要点如下:

      1.企业是一个系统,其目标应当十分明确,那就是在当前和今后为企业获得更多的利润

      2.一切妨碍企业实现整体目标的因素都是约束

      按照意大利经济学家帕拉图的原理,对系统有重大影响的往往是少数几个约束,为数不多,但至少有一个。约束有各种类型,不仅有物质型的,如市场、物料、能力、资金等,而且还有非物质型的,如后勤及质量保证体系企业文化管理体制、规章制度、员工行为规范工作态度等等,以上这些,也可称为策略性约束。

      3.为了衡量实现目标的业绩和效果,TOC打破传统的会计成本概念,提出了三项主要衡量指标,即有效产出、库存和运行费用。TOC认为只能从企业的整体来评价改进的效果,而不能只看局部。库存投资和运行费用虽然可以降低,但是不能降到零以下,只有有效产出才有可能不断增长(见下表)。

      约束理论(Theory of Constraints,TOC) 图例1

      4.鼓-缓冲-绳法(Drum-Buffer-Rope ApproachDBR法)和缓冲管理法(Buffer Management)

      TOC把主生产计划(MPS)比喻成"鼓",根据瓶颈资源能力约束资源(Capacity Constraint Resources,CCR)的可用能力来确定企业的最大物流量,作为约束全局的"鼓点",鼓点相当于指挥生产的节拍;在所有瓶颈和总装工序前要保留物料储备缓冲,以保证充分利用瓶颈资源,实现最大的有效产出。必须按照瓶颈工序的物流量来控制瓶颈工序前道工序的物料投放量。换句话说,头道工序和其它需要控制的工作中心如同用一根传递信息的绳子牵住的队伍,按同一节拍,控制在制品流量,以保持在均衡的物料流动条件下进行生产。瓶颈工序前的非制约工序可以用倒排计划,瓶颈工序用顺排计划,后续工序按瓶颈工序的节拍组织生产。

      5.定义和处理约束的决策方法

      TOC强调了三种方法,统称为思维过程(Thinking Processes,TP),见下表:

      约束理论(Theory of Constraints,TOC) 图例2

      应用TOC获得成功的企业很多,如美国得克萨斯食品公司深感缩短提前期在竞争上的必要性,从1992年就开始进行了TOC改善活动。它先以福特公司的电子事业部为样板引进了TOC,结果省去了为增产所需的数亿美元的投资。半导体乌耶哈工厂也引进了TOC,提前期在1年半内减少了75%,生产能力在同样的设备条件下提高了25%。

    [ 编辑]

    约束理论产生的背景

      约束理论(Theory of Constraints,TOC)是以色列物理学家戈德拉特博士(Dr。 Eliyahu M。 Goldratt)在他的优化生产技术(Optimized Production Technology,OPT)的基础上发展起来的。

      OPT是Goldratt博士和其他三个以色列籍合作者创立的,他们在1979年下半年在美国成立了CreativeOutput公司。接下去的七年中,OPT有关软件得到发展,同时OPT管理理念和规则开始成熟起来。CreativeOutput公司的发展几起几落,后关闭。OPT的软件所有权转让给一家名为SchedulingTechnologyGroup的英国公司。

      TOC首先是作为一种制造管理理念出现。《The Goal》、《The Race》这两本最初介绍TOC的书引起了读者的广泛兴趣和实施这套理念的热情。TOC最初被人们理解为对制造业进行管理、解决瓶颈问题的方法,后来几经改进,发展出以"产销率、库存、经营成本"为基础的指标体系,逐渐形成为一种面向增加产销率而不是传统的面向减少成本的管理理论和工具,并最终覆盖到企业管理的所有职能方面。

      1984年,Goldratt博士在他出版的第一本以小说体写成的TOC专著《目标》中,描述了一位厂长应用约束理论使工厂在短时间内转亏为盈的故事。Goldratt博士把一个企业比喻作一条链子。链子连结在一起象征一个完整的系统,能够产生巨大的力量,就像企业内部各个部门、科室互相配合、亲密合作,为股东带来巨额利润一般。Goldratt博士认为任何一种体制至少都会有一个约束因素,从而阻碍它充分发挥潜能。以企业为例,它经常为各种不确定的因素所阻碍,无法实现利润最大化。这个系统就如同我们的链条比喻一样,约束因素使它无法承受重荷而很容易断裂。这个简单而形象的比喻深入人心,加上书中描述的问题在很多企业普遍存在,使人读起来有亲切感。一时间,该书在全球畅销,销售200多万册。1986年后半年,Goldratt博士和RobertE。Fox共同创立Goldratt研究机构。1991年,当更多的人开始知道和了解TOC的时候,TOC又发展出用来逻辑化、系统化解决问题的"思维过程"(ThinkingProcess,即TP)。TOC理论就这样经过不断地发展而逐渐成熟。

      约束理论在美国企业界得到很多应用,在20世纪90年代逐渐形成完善的管理体系。美国生产及库存管理协会(American Product and Inventory Control Society, APICS)非常关注TOC,称其为“约束管理(Constraint Management)”,并专门成立了约束管理研究小组。该小组认为:TOC是一套管理理念与管理工具的结合。“约束”即企业在实现其目标的过程中现存的或潜伏的制约因素。约束管理是通过逐个识别和消除这些约束,使得企业的改进方向和改进策略明确化,从而达到帮助企业更有效地实现其目标的目的。

      总结起来,TOC就是关于进行改进和如何最好地实施这些改进的一套管理理念和管理原则,可以帮助企业识别出在实现目标的过程中存在着哪些制约因素,并进一步指出如何实施必要的改进以消除这些约束,从而更有效地实现企业目标

    [ 编辑]

    TOC的九条原则

    TOC的九条生产作业计划制定原则:

    1. 不要平衡生产能力,而要平衡物流
    2. 非瓶颈资源的利用水平不是由自身潜力所决定,而是由系统的约束来决定
    3. 资源的利用与活力不是一码事
    4. 瓶颈损失1小时,相当于整个系统损失1小时
    5. 非瓶颈上节约开1小时,无实际意义
    6. 瓶颈制约了系统的产销率和库存
    7. 转运批量可以不等于1,而且在大多数情况下不应该等于加工批量
    8. 加工批量不是固定的,应该是随时间而变化
    9. 优先权只能根据系统的约束来设定,提前期是作业计划的结果(不是预先设定的)
    [ 编辑]

    TOC的五大核心步骤

      TOC有一套思考的方法和持续改善的程序,称为五大核心步骤(Five Focusing Steps),这五大核心步骤是:

      第一步,找出系统中存在哪些约束。

      第二步,寻找突破(Exploit)这些约束的办法。

      第三步,使企业的所有其他活动服从于第二步中提出的各种措施。

      第四步,具体实施第二步中提出的措施,使第一步中找出的约束环节不再是企业的约束。

      第五步,回到步骤1,别让惰性成为约束,持续不断地改善。

    [ 编辑]

    MRPⅡ、JIT和TOC的对比分析[1]

      MRPⅡ起源于美国,根植于批量生产方式。其优势在于长期计划能力,缺点集中于:(1)提前期、批量等参数预先静态设定;(2)能力约束考虑不足;(3)计划与控制相分离;(4)底层功能较弱,造成计划的抗扰动能力差。适用于有一定批量、提前期相对稳定、能力需求波动不大的生产类型

      JIT起源于日本,根植于重复性生产方式。其缺点在于:(1)整体计划性弱,生产控制只是被动跟随;(2)追求零库存,但未考虑到库存对系统的产销率、物流平衡等方面的正面影响;(3)对设备、人员及供应链要求很高。JIT最适用的是按订单装配(ATC))且物流发达的生产类型。

      TOC起源于以色列,由犹太人物理学家Eliyahu M,Goldratt博士率先提出,根植于离散型生产方式。TOC的优势在于:(1)正视瓶颈的存在并充分利用瓶颈把瓶颈计划调度和非瓶颈的计划调度区别对待;(2)TOC不需要预先设定提前期,提前期是编制计划的结果;(3)综合了推拉两种方式的优点;(4)TOC承认能力不平衡的绝对性,保证生产物流的平衡和生产节奏的同步;(5)TOC是集计划与控制于一体的方法,实现了生产计划与控制的和谐与统一。TOC主要适用于离散生产、机群型布置多品种批量生产和有多种产品搭配组合的订货生产及订货组装生产。

    [ 编辑]

    约束理论的核心及关键技术[1]

      1.约束理论的核心思想

      约束理论认为:企业的最终目标就是在现在和将来赚取更多的利润,只有当企业能够持续盈利的时候,才能够在竞争中求得生存。采用TP会计理论(through put accounting)作为衡量企业能否获利的标准,与传统的财务指标体系(产销率T、库存I、运行费OE)的关系如图1所示。

    Image:图1 作业指标、财务指标与制造周期的关系.jpg

      从图1中可以看出,产销率的增加、库存和运行费用的降低可以提高净利润,增大投资收益率和增加现金流量。但是,要想通过减少库存和运行费用来实现多赚钱的目标是有限度的,因为在极限的情况下也只能把库存和运行费减少到零,而通过产销率来增加利润却有着无限的可能。

      那么,如何增加系统的产销率呢?约束理论认为,系统的产销率是由系统中的一个或者少数的几个约束环节(通常又称“瓶颈”)所决定的。所以,增加系统的产销率最有效的办法就是充分利用瓶颈的活力。因为,“瓶颈上损失一小时等于整个系统损失一小时”,“非瓶颈的利用程度不由其本身决定,而由系统的瓶颈决定”。因此,约束理论的核心就在于充分利用瓶颈资源,不断突破系统约束,如此往复,有针对性、有重点地对系统进行改进。

      2.约束理论在生产计划中应用的关键技术

      在制造系统中应用约束理论制定生产计划,其所涉及到的关键技术和步骤包含以下三个方面:

      (1)瓶颈资源识别。对于制造系统,瓶颈资源就是指那些生产任务量大于其生产能力的设备/制造单元。因为在任务不断变化的单件小批量生产环境下生产能力不平衡是必然的、不可避免的;生产能力不平衡说明必然存在能力上的薄弱环节,即瓶颈环节;企业计划与控制的重点应是企业的瓶颈环节。

      (2)瓶颈资源排产。瓶颈资源的确定将企业的整个生产网络划分为关键网络和非关键网络,将需要生产的零部件划分为关键件一般件。为保证瓶颈资源的充分利用,需按照生产订单的重要、紧急程度对瓶颈资源上的生产任务按照一定规则、合理的批量进行排序。

      (3)DBR系统排产。安排好瓶颈资源上的生产任务后,需要在整个生产系统恰当的位置设置合理的缓冲,选取合适的批量,瓶颈资源之前的工序按照“拉动”方式进行,瓶颈资源之后的工序按照“推动”方式进行,完成计划期内所有任务的排产。

    [ 编辑]

    瓶颈的时间性及识别方法

      1.瓶颈资源的定义

      一般来说,瓶颈可以是三种类型:资源、市场和法规。对于一个生产型企业,假定瓶颈资源就是生产系统能力最薄弱的环节,即瓶颈设备。从实现计划目标的角度出发,把凡是设备负荷率达到或接近100%(如97%以上)的设备定为瓶颈资源。因此,对于瓶颈资源可以做出如下定义:

      对于系统中的 件资源X1X2...Xn,实际产出能力为C1C2...Cn,系统的外部需求量为MR1MR2...MRn。某些资源之间存在互为输入和输出的关联关系R。假设与资源Xi相关联的资源组成的集合为S,即:

      S= {j|j \ne i \land \exists R(X_i,X_j)}

      那么,当且仅当 C_i \le  min(MR_i, min(\forall C_j, j \in S))时,资源Xi为瓶颈资源。

      2.瓶颈的时间性及识别方法

      在实际生产过程中,系统的瓶颈随生产任务的更改和人员、设备等外界因素的变动而动态变化。所以,同一资源在计划期内负荷率还与时间有关系,称为瓶颈的时间性。因此识别瓶颈资源就需要根据一定的规则对计划期进行时间段划分,分别计算每台设备在相应时间段内的任务负荷率。时间段的划分有两个步骤:(1)各零件工序交货期的计算;(2)按照一定的规则进行时间段的划分。下面由一个例子说明任务计划期内,基于时间段的瓶颈资源的确定方法。

      假设已知:A、B、C、D、E、F六种零件的生产工艺、工时定额和零件的交货期,如表1所示(在这里假设生产批量已知)。假设零件A、B、c、D、E、F的各工序之间的运输和检验时间如表2所示。

    表1 各零件的工时定额及交货期
    零件名称 生产批量 序名称及工时定额(单位:分钟) 零件交货期
    1 2 3 4 5
    A 400 车0.06 钻0.02 镗0.06 磨0.04 第十周末
    B 20o 车0.03 车0.05 钻0.04 镗0.07 铣0.05 第九周末
    C 20o 铣0.08 钻0.04 镗0.08 磨0.05 第九周末
    D 400 车0.06 铣0.08 车0.04 镗0.08 磨0.06 第十周末
    E 600 车0.05 铣0.05 钻0.02 车0.04 镗0.08 第十周末
    F 400 铣0.06 铣0.08 钻0.04 磨0.06 第十周末
    表2 运输和检验时间   单位:小时
    零件 1—2 2—3 3—4 4—5
    A 1 3 2
    B 0 1 3 1
    C 1 3 2
    D 1 1 2 2
    E 1 1 1 2
    F 0 1 1

      上述六种零件均在一个成组单元内生产。该生产单元有车床2台,铣床、镗床、钻床、磨床各1台。假设每周按6个班,每班工作8小时计算,第十周末为厂历的第480小时;计划期为第四周末到第十周末,即从厂历的第192小时开始到第480小时结束;上个计划期内遗留下来的任务有:车192小时、铣96小时、钻72小时、镗150小时、磨88小时。

      第一步,计算各工序的工序交货期。

      各零件的工序最迟完工时间可按下式计算:

    T_ij = T_j - \sum_{t+1}^n Q_j \times t_{ij} - \sum_{t+1}^{n-1} t_{ch}    (1)

      其中,Tij为j零件i工序的最迟交货时间;Tj为j零件的最迟交货时间;\sum_{t+1}^n Q_j \times t_{ij}为一批j零件自i+1至n工序的加工时间,批量为Qj\sum_{i+1}^{n-1} t_{ch}为某工件由i+1工序到n-1工序的工序间运输和检验时间之和。

      为了研究的方便,假定在各工序零件的生产批量和零件的需求批量相等;运输和检验可以当成是零件的一道加工工序,计算出开始时间和结束时间。由(1)式计算得到各机床上各工序的开始时间和完工时间(如表3所示)。

    Image:表3 零件各工序交货时间计算表.jpg

      第二步,划分设备的时间段。因为瓶颈具有时间性,在计算各设备负荷率之前必须进行时间段的划分。本文提出在划分设备的时间段时应遵循以下几条规则。

      规则一:每个时间段内必须都包含有完整的任务。这样可以避免部分任务被掩盖,真实反映设备的实际负荷状况。

      规则二:合并原则。对某一台机床而言,若一个时间段内只有一个单独的任务,则应与在该机床上加工的上一个时间段相结合,形成一个新的时间段。因为,如果在某个时间段内只含有一道任务的话,则该时间段内的机床的负荷率一定为1,根据TOC中对瓶颈资源的定义,负荷率大于或等于1的资源即为瓶颈资源,这样就会造成假瓶颈的出现。如果该任务是本计划期内该机床所承担的第一项加工任务(上期遗留任务不在此列),则不受该规则的限制。

      规则三:所划分的两个时间段之间不允许出现间断。将划分的时间段按照时间大小进行排序,如果上一时间段的结束时间不是下一时间段的开始时间,则需要将下一个时间段的开始时间调整为上一个时间段的结束时间。因为在这里计算出的时间均是各工序的最迟完工时间,不能推迟,只能提前。

      规则四:第一个时间段的开始时间应为计划期的开始时间。

      在对设备进行时间段划分和负荷率计算时,上述四条规则的优先级别从高到低,应按顺序执行。现以镗床为例,说明在时间段划分中运用上述原则的具体过程:(1)统计所有在镗床上加工的负荷及其开始和结束时间,并按照开始时间的大小进行排序;(2)根据上述的四种规则进行时间段的合并,并计算镗床的负荷率。过程如图2所示。镗床的时间段划分结果如图3所示。

    Image:约束理论的瓶颈识别研究.jpg

      由图3可知,采用改进的时间段划分方法,可以更准确地反映设备的实际负荷情况,由此计算出的负荷率更能用于指导实际生产。有效的瓶颈资源确定方法为后续进行瓶颈资源排产,实施DBR方法,突破系统约束奠定了基础。

    [ 编辑]

    TOC的主要技术工具

      一、  思维流程分析法

      TOC理论最终就是要寻求顾客需求企业能力的最佳配合,对约束环节进行有效的控制,其余的环节相继地与这一环节同步。

      一种管理思想总是需要相应的管理技术的支持。思维流程(Thinking Process,即TP)是TOC主要的工作方法之一,思维流程有以下主要的技术工具:

      现实树(Reality Tree)

      现实树工具用来帮助人们认清企业现实存在的状况。

      现实树是因果图,分为当前现实树(Current Reality Tree, CRT)和未来现实树(Future Reality Tree, FRT)。现实树的建立要严格遵循若干条逻辑规则,从“树根”开始,向“树干”和“树枝”发展,一直到“树叶”。“树根”是根本性的原因,“树干”和“树枝”是中间结果,“树叶”是最终结果。对于当前现实树来讲,“树叶”是一些人们不满意的现象,“树根”是造成这些现象的根本原因或核心问题所在。而在未来现实树当中,它的“树根”是解决核心问题的方案,“树叶”是最终人们想看到的结果。

      当前现实树(Current Reality Tree, CRT)

      描绘当前现实树(Current Reality Tree, CRT)时,即要回答“改进什么”时,我们往往是从可以得到的例证开始着手,即系统中明显地存在着的那些不尽人意的地方,如发货拖延、库存超标等,总称为“不良效果”(Undesirable Effects,简称UDE)。值得注意的是,UDE并不是真正的问题所在,它们只是一些表面现象。通过绘出将这些不良效果联系在一起的逻辑关系图,可以大大有助于找到真正的问题症结所在,它们就显示在这个逻辑图的最低部。

      消雾法(Evaporating Cloud,EC)

      消雾法用来以双赢(Win-Win)的方式解决企业中的冲突。此法的得名是由于企业中的冲突像一团团的云雾一样,人们往往不能很清楚地说出究竟是哪些原因造成了这些冲突。消雾法就是要驱散那些弥漫在冲突周围的混淆和含糊,找到解决问题的突破点,也就是去伪存真,去粗存精,由外及内,由表及里,称之为“注入”。

      未来现实树(Future Reality Tree, FRT)

      用“消雾法”看清问题和冲突,找到“注入”。但这还不是一个充分完整的方案。要回答“改成什么样子”,需要利用未来现实树。在CRT图上,把一个个“注入”插入到它要进行突破的环节。然后,重绘逻辑连接,在CRT的基础上生成FRT。这时,我们就要看“不良效果”是否转变为“满意效果”(Desirable Effects, DE)。

      负效应枝条(Negative Effect Branches)

      当做完CRT、EC、FRT的一系列工作以后,就要找一些与改进后果相关程度最大的人来参与,以保证改进的成功实施。思维流程法是一项需要高度开放、广泛参与的活动。如果不和这些利益相关者进行全面和充分的沟通的话,就很难消除这部分员工的抵制改革的情绪。思维流程法认为,正是这些受改进影响最大的人,才对那些意料之外的负面效应(即“负效应枝条”)了解得最清楚。所以,思维流程法要求主动寻求这些人的参与,并与他们一道找出避免这些“负效应枝条”长出的办法,以避免实施的失败。此过程可以形象地描述为“剪去负效应枝条”(Trimming the Negative Branches)。

      必备树(Prerequisite Tree)

      要注意,问题“怎样使改进真正得以实现?”不同于问题“怎样对事物进行改变?”。前者是在改进前后的状态都已知晓的情况下,也就是当前现实树和未来现实树都已经描绘出来的情况下,重点强调如何导致这一改进的实际发生。鉴于人们对自己参与设计的改进方案一般抵触较少,所以回答问题“怎样使改进真正得以实现?”的关键就是,让那些将与这些转变直接相关的人来制定实施转变所需的行动方案。这个工作是思维流程法中最有力的一环,也是TOC与其他那些追求持续改进的思维流程方法相比最显著的一个特色。用来显示克服障碍路径的逻辑图就称为“必备树”。

      转变树(Transition Tree)

      TOC思想的应用成功需要集思广益,找到配合实施最初“注入”的其他“注入”。把所有这些实现成功实施所需的活动集中在一起,并给出它们之间的关系,弄清楚活动的先后顺序应该怎样,也就是“转变树”。

      通过上述步骤,改革者可以找出约束因素,并对这些约束因素进行改进。

      二、 物流分析法

      企业内部存在着人员流资金流信息流、物流。物流是企业流程中的重要组成部分,它历经采购原材料、制作毛坯、加工半成品、组装部件、总装产品及该过程中的物料传输、存储等活动环节。企业的生产管理者可以根据这个活动链中各环节的高度相关的内在关系,制定出一个详尽而周密的生产作业计划,规定出每一种毛坯、零件、部件和产品的投入、出产时间和数量。但在实际中,常常会出现各种问题,如机器损坏、原料不足、半成品和产成品因质量问题而返工等等,这些大量存在的意外的随机事件常常会打乱原本计划好的活动程序。我们如何在这些纷乱的头绪中找出干扰企业的约束瓶颈呢?解决的手段之一就是从“物流”着手。

      通过对企业中“物流”的分类,我们可以根据不同类型“物流”的特点,认识他们各自的薄弱点,或“瓶颈”所在,从而有针对地进行计划与控制。

      一般将从原材料到成品这一“产品物流”分为:“V”、“A”和“T”三种类型。其中,“V型物流”是由一种原材料加工或转变成许多种不同的最终产品;“A型物流”是由许多种原材料加工或转变成的一种最终产品;而“T型物流”则是“A型物流”的一个变形,其最终产品有多种。其流程示意关系如下图。

      实际上,一个企业的“产品物流”往往不只一种类型。我们可以根据占主要地位的“产品物流”来相应地划分企业。如果一个企业其物流形态主要是“V型物流”,那么我们就称这个企业为“V”型企业,其余的以此类推。

      “V型物流”企业

      典型的“V型物流”企业如炼油厂、钢铁厂等。其特点有:

      (1)最终产品的种类较原材料的种类多得多;

      (2)所有的最终产品,其基本的加工过程相同;

      (3)企业一般是资金密集型且高度专业化的。

      V型企业的工艺流程一般来说比较清楚且设计简单,物流路径清晰,通过对物流的分析,比较容易识别、控制与协调企业的瓶颈。对这类企业的约束分析主要集中在对内部流转环节进行衔接性的匹配,要消除上道工序与下道工序之间的拖延、无效返工等状况。

      “A型物流”企业

      对于“A”型企业,如造船厂、大型机械装配厂。其特点是:

      (1)由许多制成的零部件装配成相对较少数目的成品,原材料种类比零部件种类多;

      (2)一些零部件对特殊的成品来说是唯一的;

      (3)对某一成品来说,其零部件的加工过程往往是不相同的;

      “T型物流”企业

      而对于“T”型企业,如制锁厂,汽车制造厂等。其特点主要包括:

      (1)由一些共同的零部件装配成相对数目较多的成品;

      (2)许多成品的零部件是相同的,即存在标准件和通用件;

      (3)零部件的加工过程通常是不相同的。

      “A型物流”企业及“T型物流”企业与“V型物流”企业的最大不同点在于,前两者的物流管理难度大。它们存在着物料清单BOM),工艺流程较复杂,企业的在制品库存较高,生产提前期较长,约束环节不易识别,计划以及工序间的协调工作也非常困难。

       要消除“A 型物流”企业和“T型物流”企业的约束环节,主要工作要集中在外部供应链的优化上面。要协调好企业与外部供应商经销商之间的关系,保证企业在满足市场多变需求的同时,均衡地安排内部的采购、生产、储存等活动。

      三、 三步诊断法

      目前,全球的许多企业陷入了地区性或者行业性的生产过剩的困境。由于在供大于求的市场状况下,众多企业出于利润驱动,迅速扩张生产规模,而导致了市场上的过剩状态的出现。如何处理企业包括生产、存储、人员等各方面的过剩能力?一些企业采取了最直接的削减分支机构裁员、减薪等措施,虽然短期内解决了问题,但是经济复苏时,企业却忙于招聘新人,往往难以招聘到合适的员工,而且要为新员工的培训支付相当大笔的费用。另外,由于部门的削减,也使企业丧失了在一些新的有潜力的领域中的领先地位。

      简单的收缩策略、成本削减策略不但没有增强企业自身在市场上的竞争地位,而实际上恰恰放弃了这种优势。

      那么,当企业面对剩余的生产能力时该怎么办呢?答案是要把市场看成约束因素,努力去扩展有效的市场需求。可以利用TOC理论,着眼于企业整体功能和长远战略,通过三步诊断过程来消除市场约束因素。

      明确以市场为导向的观念

      当我们在仓库里面对堆积如山的商品,需要解决产品产销矛盾时,仅仅想到用减少生产的方式去减少库存,是不够的。应该积极采取措施通过生产市场需要的产品,去扩大市场的需求。企业应该树立这样的观念:如果我们的企业存在生产能力过剩的现象,那么市场就是约束因素。顾客是我们利益实现系统中不可缺少的重要环节,事实上也是最重要的环节,因为它最终决定产品是否能够转化为利润。所以,企业的生产应该坚决以市场为导向。

      改善内部环节

      市场是影响企业效益的重要约束因素,要想充分地利用它,把企业的产品和服务带进市场,从而创造最大企业利润,就必须改善企业内部的各个环节,包括企业的文化氛围。要使员工意识到企业是一个由许多相互制约的子系统组成的有机体,必须追求企业的整体优化,而不是单个环节、或者单个部门的局部优化。因此,需要企业内部各个方面的相互合作、相互协调,才能有效地利用约束因素。

      改善外部环境

      第三步是改善约束因素。例如市场是约束因素,我们就能有多种途径改善它。最明显的办法莫过于更有效的广告宣传,或者简单地进行更多的广告宣传。更好的方法是细分市场,寻找合适的顾客群体,使企业的产品恰好能满足该消费群体的需要。事实上,这才是有效利用市场的核心部分。

      这三步诊断法是一个循环往复的过程。通过不断改进,才能实现企业的利润和运作水平的进一步提高。

      通过采用削减成本以实现利润最大化的方法往往导致失败,或者只是能够获得短期利润。事实上,削减成本的方法通常会削弱企业在市场上的竞争力,而它为企业带来的效益却是非常有限的。事实上,我们可以根据约束理论所提供的三步运作程序不断改善企业的各个环节,这样我们就能实现均衡的长远的利润最大化。

    [ 编辑]

    约束理论在物流企业中的应用[2]

      1.企业供应链模型及现状

      实践证明,TOC作为一种先进的管理思想,除了可以应用到生产管理领域,也可以应用到分销和供应链管理领域。什么是供应链?它是指产品生产和流通过程中所涉及的原材料供应商、生产商批发商零售商以及最终消费者组成的供需网络。该供应链模型由供应商的供应开始,经生产商的制造、加工、装配和仓储,到分销、零售直至最终需求的客户。

      近几年,我国的制造业总体上已经实现了从卖方市场买方市场的转变,企业为了应对激烈的市场竞争,对物流成本越来越重视, 日本人提出的JIT因为提倡零库存,最大限度地减少库存费用,不少企业因此将其奉为上策。有些企业在实施JIT之后,完全实现了零库存。然而客观地讲,JIT所追求的消灭浪费的理念,在我国目前的市场环境中是难以全面实现的。一是企业内部的物流组织结构不尽合理,无法正常地衔接,影响整体的效率;二是因为我国市场具有相当的波动性,企业处于供应链的下游,由于牛鞭效应,应急处理往往具有滞后性,导致了安全库存沿供应链向上游增大而积累了大量超过市场需要的库存;三是供应链企业之间缺乏真正密切协作的关系,上游供应商对企业零部件、原材料的供应难以真正做到准时、有效。

      我国一些企业的零库存并不等于没有库存,只不过他们自己不设立仓库,而由上游的供应商在这些企业周围建立仓库,将零部件存储在里面,并由仓库按照制造企业的用料需求为他们随时送货。实际上这是加重了供应商的负担。此外我国的许多企业在采用JIT供应体系时,实行所谓的“多渠道供货制”,即两家或者两家以上的供应商供应同一零部件,几家供应商为了争夺订单往往会运用价格竞争的手段。此举对于制造企业无疑是有利的,但是对于供应商来说,极有可能吃不消。为了应付因需求方生产波动所带来的订单变动,供应商必然要采取加大库存的方法。而实际上由于往往只有一家供应商得到订单,或者每一家供应商得到一部分订单,大部分的库存可能会形成积压,特别是那些专用性强的零部件需求量本来就少,最后损失只能由供应商承担。

      2.DBR分析

      如果我们换一种思路,运用TOC的DBR观点来处理库存问题,或许是可以避免上述问题的。在对待库存上,TOC的观点很简单,库存唯一的目的是在一段时间内支持产销量,使企业不至于因市场的波动而产生缺货。为此,企业在约束环节之前,就应合理地设置库存“缓冲”,以防止因随机波动使约束环节出现等待任务等情况,同时通过“绳子” 力求达到物流的同步流动。一味追求做不到的事情对企业是没有必要的,也是不经济的。企业应该接受市场需求波动及其引起的相关问题的现实,并在这种前提下追求物流平衡,即各个子系统与“约束”子系统同步,以求生产周期最短,在制品最少。由于顾客的需求变化,因此市场的波动是绝对的,生产能力的稳定是相对的。此时,市场成为约束资源,即“鼓点”,制约着产品的库存量。完全消灭库存并不可取,特别是在我国制造业企业目前的生产管理水平下,还是应该保留适当的库存。我国制造业企业应重点考虑通过提高产销量的方法来增加收入,这就是要求企业能加大科学技术力量的投人,提高产品的技术含量,使产品具有差异性,从而拥有更强的竞争力。同时,企业还应该时时注重员工的培训,以科学的发展观来武装思想,为企业创造更大的财富。相反,只通过采用削减成本以实现利润的方法往往只能够获得短期的利润,未免有些鼠目寸光。事实上,一味削减成本的方法有时还会削弱企业在市场上的竞争力,为企业带来的效益是非常有限的。

      3.DBR评价

      约束理论独特的管理思想和方法在物流管理中的应用,能迅速提高物流管理的有效性,也能给企业带来一些独特的优势。

      其一,反应能力增强,应变力变快。约束理论的DBR思想帮助企业领导抓住主要流程或环节进行管理,使其从一大堆烦琐的物流管理工作中解脱出来,能更好地进行宏观调控,增强企业的市场反应能力。有效的约束管理从职能导向转移到以鼓点为导向,通过及时、准确的瓶颈优化或转移,能以最快的速度满足市场需求。其二,工作有序化和透明化。约束理论关注企业各活动环节的具体运作情况及其内在联系,并在必要时建立起新的关系网络,这有利于各部门环节的有序化和透明化。其三,企业管理通过DBR管理方法在物流管理中的有效应用,实现企业的高效。企业以鼓点为重点,不断地发现、分析和解决问题,能逐步实现企业的有序管理。

      在科技迅猛发展、竞争空前激烈的今天,国际上的企业界和学术界已关注到约束管理在企业中的重要作用。但在我国国内,由于各种原因,企业对约束理论还存在着一些顾虑,约束理论管理思想并未得到很好的运用,学术界也未对此进行深入的研究。本文就在学习和研究约束理论管理思想的基础上,借鉴国外企业的成功经验,对我国物流企业的现有状况进行了一次研究分析,并根据分析所得出的结论,将约束理论应用于物流企业的库存管理运输管理中,给我国企业的管理提供思想上的指导和方法上的辅助。

    展开全文
  • 数据库约束查找的约束 Constraints exist as a way to enforce or document rules within the database. How do they affect performance, and what are the benefits of using them? 约束是在数据库中强制执行...

    数据库约束查找的约束

    Constraints exist as a way to enforce or document rules within the database. How do they affect performance, and what are the benefits of using them?

    约束是在数据库中强制执行或记录规则的一种方式。 它们如何影响性能?使用它们有什么好处?

    介绍 (Introduction)

    There are a variety of constraints that can be implemented in a SQL Server database that can provide a variety of new functionality. Some are so familiar that we forget their presence, such as NULL constraints, while others may not be used as often, such as check constraints or default constraints.

    在SQL Server数据库中可以实现各种约束,这些约束可以提供各种新功能。 有些非常熟悉,以至于我们忘记了它们的存在,例如NULL约束,而有些则可能不那么常用,例如检查约束或默认约束。

    This is an opportunity to dive into each type of constraint, and after introducing their purpose and usage, dive into their storage, performance, and notes about their optimal use, as well as pitfalls. Please note that all demos were created and tested in SQL Server 2016 RC1.

    这是深入研究每种约束类型的机会,并在介绍了它们的用途和用途之后,深入研究了它们的存储,性能以及关于它们的最佳使用以及陷阱的注意事项。 请注意,所有演示均已在SQL Server 2016 RC1中创建和测试。

    目的 (Purpose)

    Constraints may be used for a variety of purposes. The following is a short list of what I consider the most common reasons that constraints are created:

    约束可以用于多种目的。 以下是我认为创建约束的最常见原因的简短列表:

    • Prevent bad data from being entered into tables of interest.

      防止将不良数据输入到感兴趣的表中。
    • Enforce business logic at the database-level.

      在数据库级别实施业务逻辑。
    • Documentation of important database rules.

      重要数据库规则的文档。
    • Enforce relational integrity between any number of tables.

      增强任意数量表之间的关系完整性。
    • Improve database performance.

      提高数据库性能。
    • Enforce uniqueness.

      增强唯一性。

    There are others, of course, but the reasons for using constraints are quite varied, and can be a helpful tool for database administrators and developers alike. Typically, it is recommended to use constraints over more complex, procedural logic-enforcement, such as triggers, rules, stored procedures, or jobs.

    当然,还有其他原因,但是使用约束的原因千差万别,对于数据库管理员和开发人员而言,它都是有用的工具。 通常,建议对更复杂的过程逻辑执行使用约束,例如触发器,规则,存储过程或作业。

    Let’s review one-at-a-time each type of constraint and some examples of their usage.

    让我们一次查看每种约束类型及其用法的一些示例。

    非空约束 (NOT NULL constraints)

    This is so common that we may forget that a column that does not allow NULLs is technically in possession of a NOT NULL constraint. Its function is straightforward: a column with this constraint will not allow a NULL to be entered, and will throw an error when anyone attempts to enter a NULL into a column that doesn’t allow NULLs.

    这是如此常见,以至于我们可能忘记了不允许NULL的列在技术上拥有NOT NULL约束。 它的功能很简单:具有此约束的列将不允许输入NULL,并且当任何人试图在不允许NULL的列中输入NULL时都会抛出错误。

    NULL may be defined at the time a table is defined, such as like this:

    可以在定义表时定义NULL,例如:

     
    CREATE TABLE dbo.NullDemo
    (	Null_Id INT NOT NULL,
    	Null_Data VARCHAR(50) NULL,
    	Not_Null_Data VARCHAR(50) NOT NULL);
     
    

    The syntax is simple, and in this table, Null_Id and Not_Null_Data will not allow any NULLs to be entered into either. Null_Data, on the other hand, will allow NULL, in addition to standard string values. When creating a table, you can choose to not specify NULL or NOT NULL, and if you do, the column will default to being NULLable. Since NOT NULL is an explicit constraint, the default setting for any column is to allow NULLs.

    语法很简单,在此表中, Null_IdNot_Null_Data不允许将任何NULL输入其中一个。 另一方面, Null_Data除了标准字符串值外,还将允许NULL。 创建表时,可以选择不指定NULL或NOT NULL,如果这样做,则该列将默认为NULLable。 由于NOT NULL是显式约束,因此任何列的默认设置是允许NULL。

    Using this table, let’s insert a few rows into it:

    使用此表,让我们在其中插入几行:

     
    INSERT INTO dbo.NullDemo
    	(Null_Id, Null_Data, Not_Null_Data)
    SELECT
    	1, 'Stegosaurus', 'Spiky';
    INSERT INTO dbo.NullDemo
    	(Null_Id, Null_Data, Not_Null_Data)
    SELECT
    	2, 'Tyrannosaurus', 'Huge';
     
    

    Both of these inserts succeed without incident, adding two dinosaurs into our demo table.

    这两个插入都成功了,没有发生意外,将两个恐龙添加到了我们的演示表中。

     
    INSERT INTO dbo.NullDemo
    	(Null_Id, Null_Data, Not_Null_Data)
    SELECT
    	3, NULL, 'Long Neck';
     
    

    This also works without a problem. Inserting a NULL is OK since we explicitly defined Null_Data to allow NULLs.

    这也没有问题。 插入NULL是可以的,因为我们明确定义了Null_Data以允许NULL。

     
    INSERT INTO dbo.NullDemo
    	(Null_Id, Null_Data, Not_Null_Data)
    SELECT
    	4, 'Pterodactylus ', NULL;
     
    

    Our poor dino-flyer never makes it into the database as we tried to insert a NULL into the last column, Not_Null_Data, which we applied the NOT NULL constraint to. The result is the expected error:

    当我们试图在最后一列Not_Null_Data中插入NULL时,我们可怜的dino-flyer从未进入数据库,因为我们将NOT NULL约束应用于了该列。 结果是预期的错误:

    Msg 515, Level 16, State 2, Line 27
    Cannot insert the value NULL into column ‘Not_Null_Data’, table ‘AdventureWorks2014.dbo.NullDemo’; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    信息515,第16级,州2,第27行
    无法将值NULL插入表'AdventureWorks2014.dbo.NullDemo'的'Not_Null_Data'列中; 列不允许为空。 插入失败。
    该语句已终止。

    NOT NULL is exceptionally useful when we have a table with columns that are critical to the definition of that data structure. For example, an account without a name would be potentially meaningless, whereas an employee with no start date would cause us to scratch our heads and ask why no one entered it in the first place.

    当我们有一个表的列对于该数据结构的定义至关重要时,NOT NULL异常有用。 例如,没有名称的帐户可能毫无意义,而没有开始日期的员工会导致我们挠头,问为什么没有人首先输入。

    NOT NULL corrects mistakes by the user as well as mistakes by a software or database developer. If a column is required, then we want to ensure that it is always entered, no matter what. The application should tell a user that they need to enter a required field, returning a friendly message if they forget. In the event that the application somehow allows a NULL where one does not belong, then an error would be returned that the developers could easily troubleshoot and fix. This scenario is preferable to allowing data to be created with critical missing elements.

    NOT NULL纠正用户的错误以及软件或数据库开发人员的错误。 如果需要一列,那么我们要确保始终输入该列,无论如何。 应用程序应告知用户他们需要输入必填字段,如果忘记了,则会返回友好的消息。 如果应用程序以某种方式允许一个不属于其中的NULL,则将返回错误,开发人员可以轻松地进行故障排除和修复。 此方案比允许使用严重缺失的元素创建数据更好。

    There are potentially dangerous or inappropriate uses for NOT NULL. While we tend to prefer not having NULLs to deal with in our code, forcing arbitrary values into our data can be dangerous. A NOT NULL string that defaults to a blank is relatively harmless, but what about a NOT NULL date or a NOT NULL integer? How many times have we run into a date column that was peppered with “1/1/1900” within its data? Or the integer that uses “-1” to fill in the blanks? This data can become confusing as the columns appear to have valid values, when in fact they contain dummy data. Special application or database code needs to check for these values and act appropriately when they exist. In addition, what happen if a user deliberately enters a value that matches a dummy value? Will the application disallow this? Will the database consider their entry irrelevant and the same as taking some default instead? What if your great grandfather was actually born on 1/1/1900? As of the writing of this article, there are people older than 116 years old and have birthdays that fall around this oft-used dummy date. Improbable — sure, but if I was 116 years old and got an error while signing up for Snapchat, I’d be pretty pissed off 🙂

    NOT NULL有潜在的危险或不当使用。 尽管我们倾向于不使用NULL来处理代码,但是将任意值强加到数据中可能会很危险。 默认为空白的NOT NULL字符串相对无害,但是NOT NULL日期或NOT NULL整数呢? 我们有多少次遇到了一个日期列,该日期列的数据中带有“ 1/1/1900”? 还是使用“ -1”填充空格的整数? 由于这些列似乎包含有效值,而实际上它们包含伪数据,因此这些数据可能会造成混乱。 特殊的应用程序或数据库代码需要检查这些值,并在它们存在时采取适当措施。 此外,如果用户故意输入与虚拟值匹配的值会怎样? 应用程序会不允许这样做吗? 数据库是否会认为它们的条目无关紧要,而与默认值相同? 如果您的曾祖父实际上是在1900年1月1日出生的,该怎么办? 截至本文撰写时,有些人的年龄超过116岁,生日通常在该虚拟日期附近。 不太可能-当然,但是如果我116岁,注册Snapchat时遇到错误,我会非常生气🙂

    We could avoid this problem in a variety of ways:

    我们可以通过多种方式避免此问题:

    • Allow NULL in those columns

      在这些列中允许NULL
    • Create a BIT column that signifies the special case of when data doesn’t exist, such as if a foreign national doesn’t have a US driver’s license, or if a newborn doesn’t have a social security number (yet).

      创建一个BIT列,以表示不存在数据的特殊情况,例如,如果外国人没有美国驾驶执照,或者新生儿没有社会保险号(尚未)。
    • Create a separate table to store this data when it exists, thereby eliminating the need for odd/NULL data in the interim. This is a potentially good solution when a particular column is very sparse, ie: it is very often not entered or rarely required or present.

      创建一个单独的表来存储此数据(如果存在),从而在此期间不需要奇数/ NULL数据。 当特定的列非常稀疏时,这是一个潜在的好解决方案,即:它通常不输入或很少需要或不存在。
    • Concatenate the oft-missing data with another column. For example, address lines 1, 2, and 3 could be combined in order to avoid the need for the additional address lines that may or may not have valid data in them.

      将经常丢失的数据与另一列连接。 例如,可以将地址线1、2和3组合在一起,以避免需要在其中可能有或没有有效数据的其他地址线。

    A column can be changed to NULL or NOT NULL at any time in the future using an ALTER TABLE ALTER COLUMN statement. If we attempt to change a column to NOT NULL that contains any NULLs, then that TSQL will fail:

    将来可以随时使用ALTER TABLE ALTER COLUMN语句将列更改为NULL或NOT NULL。 如果我们尝试将包含任何NULL的列更改为NOT NULL,则该TSQL将失败:

     
    ALTER TABLE dbo.NullDemo ALTER COLUMN Null_Data VARCHAR(50) NOT NULL;
     
    

    We previously entered a single row into this table with a NULL in this column. When we try to change it to NOT NULL, the error we receive is similar to what we saw earlier:

    我们之前在此表中输入了一行,此列中为NULL。 当我们尝试将其更改为NOT NULL时,我们收到的错误类似于我们之前看到的错误:

    Msg 515, Level 16, State 2, Line 32
    Cannot insert the value NULL into column ‘Null_Data’, table ‘AdventureWorks2014.dbo.NullDemo’; column does not allow nulls. UPDATE fails.
    The statement has been terminated.

    消息515,第16级,州2,第32行
    无法将值NULL插入表'AdventureWorks2014.dbo.NullDemo'的列'Null_Data'中; 列不允许为空。 更新失败。
    该语句已终止。

    The schema change of the column to NOT NULL is seen as an update to all values within, which fails due to a single row containing a NULL.

    将列更改为NOT NULL的模式更改被视为对其中所有值的更新,由于包含NULL的单行而失败。

    NULL is interesting because it is not stored in the same manner as the rest of your data. Since NULL is not a value, but an indicator of non-existence, it is stored in a separate part of each row in a structure called the NULL bitmap. This structure allows SQL Server to determine the NULLability of a column without the need to read the data itself in order to figure it out. The NULL bitmap exists for all columns in a table, NULLable or not, and therefore a table full of NOT NULL columns will not use less disk space via the absence of the NULL bitmap. The exception to this are sparse columns—if a table is defined entirely by sparse columns, then a NULL bitmap will not be present. We’ll call that an edge case, but there is no harm in being thorough here.

    NULL很有趣,因为它的存储方式与其余数据不同。 由于NULL不是值,而是不存在的指示符,因此它存储在每行的单独部分中,称为NULL位图。 这种结构使SQL Server无需确定数据本身就可以确定列的可空性。 NULL位图对于表中的所有列都存在,是否可以为NULL,因此,如果没有NULL位图,则充满NOT NULL列的表将不使用较少的磁盘空间。 稀疏列是个例外,如果表完全由稀疏列定义,则将不存在NULL位图。 我们称这种情况为“边缘情况”,但在这里进行深入介绍没有任何害处。

    The performance benefits of NOT NULL are subtle, but do exist. The most straight-forward benefit is if you query a NOT NULL column for NULL:

    NOT NULL的性能优势是微妙的,但确实存在。 最直接的好处是,如果您在NOT NULL列中查询NULL:

     
    SELECT
    	ProductID,
    	Name
    FROM Production.Product
    WHERE ReorderPoint IS NULL;
     
    

    It turns out that ReorderPoint is a NOT NULL column, and since SQL Server knows this via the table metadata, it can bypass the need to read any actual data at all and return an empty result set very efficiently. When run, this query returns no results (no surprise there), but can do so with zero logical reads and practically no query cost:

    事实证明ReorderPoint是一个NOT NULL列,并且由于SQL Server通过表元数据知道这一点,因此它可以完全不需要读取任何实际数据并非常有效地返回空结果集。 在运行时,此查询不返回任何结果(在此不出意外),但是可以在逻辑读取为零且几乎没有查询成本的情况下返回结果:

    The constant scan operator in the execution plan indicates that no tables were accessed, and scanning internal metadata about Production.Product was all that was needed to complete the query. The query cost is tiny, as is usage of CPU and memory when performing what is a very simple task.

    执行计划中的常量扫描运算符指示没有表被访问,而扫描有关Production.Product的内部元数据是完成查询所需的全部。 查询成本很小,执行非常简单的任务时占用的CPU和内存也很少。

    Otherwise, reads on NULL or NOT NULL columns will be similar. If we were to create two versions of a column, one that allows NULLs and another that does not and query against each one, the results would be virtually the same. Some experiments I ran with a variety of queries against NULL and NOT NULL columns produced a very small amount of variation, but I was able to ultimately chalk it up to index fragmentation.

    否则,对NULL或NOT NULL列的读取将是相似的。 如果我们要创建列的两个版本,一个版本允许NULL,而另一个版本不允许,并针对每个版本进行查询,则结果实际上是相同的。 我对NULL和NOT NULL列进行各种查询的一些实验产生的变化很小,但最终我可以将其归纳为索引碎片。

    独特的约束 (Unique constraints)

    A unique constraint allows us to take any column or set of columns and enforce uniqueness on their contents. This allows us to apply business or common-sense rules to our data, for example:

    唯一性约束使我们可以采用任何一列或一组列并对其内容实施唯一性。 这使我们可以将业务或常识规则应用于我们的数据,例如:

    • Ensure that no two people have the same social security number.

      确保没有两个人具有相同的社会保险号。
    • Prevent any accounts from sharing a name.

      防止任何帐户共享名称。
    • Verify that a set of data elements are unique, such as credit card type, credit card number, and expiration date.

      验证一组数据元素是否唯一,例如信用卡类型,信用卡号和有效期。
    • Make sure that a relationship between two entities is never repeated.

      确保两个实体之间的关系不会重复。

    Unique constraints are physically implemented as unique indexes, and provide the performance benefit of an index on the unique columns, while also enforcing uniqueness. There are a number of ways to define a unique index. If we were looking to add a unique index on Null_Id in our previous example, we could do so with the following syntaxes:

    唯一约束在物理上被实现为唯一索引,并在唯一列上提供索引的性能优势,同时还强制执行唯一性。 定义唯一索引的方法有很多。 如果在上一个示例中希望在Null_Id上添加唯一索引,则可以使用以下语法:

    ALTER TABLE dbo.NullDemo ADD CONSTRAINT UX_NullDemo_Null_Id UNIQUE (Null_Id);
     
    ALTER TABLE dbo.NullDemo ADD CONSTRAINT UX_NullDemo_Null_Id UNIQUE NONCLUSTERED (Null_Id);<
     
    ALTER TABLE dbo.NullDemo ADD CONSTRAINT UX_NullDemo_Null_Id UNIQUE CLUSTERED (Null_Id);
     
    CREATE UNIQUE NONCLUSTERED INDEX UX_NullDemo_Null_Id ON dbo.NullDemo (Null_Id);
     
    CREATE UNIQUE CLUSTERED INDEX UX_NullDemo_Null_Id ON dbo.NullDemo (Null_Id);
    

    These all accomplish similar goals, but there are differences. The 1st and 2nd options will create unique constraints and do so as non-clustered indexes. The 3rd will do the same, but as a clustered index. Remember that you can only have a single clustered index per table, so if one already exists, creating another will throw an error. The 4th and 5th code snippets also creates unique indexes, but there is an interesting, subtle difference in how these constraints are represented in SQL Server. After executing the 1st code snippet, Management Studio shows the following objects within the NullDemo table:

    这些都实现相似的目标,但是存在差异。 第1 和第2 的选项将创造独特的约束和非聚集索引这样做。 第三名将执行相同的操作,但将其作为聚集索引。 请记住,每个表只能有一个聚集索引,因此,如果一个聚集索引已经存在,则创建另一个聚集索引将引发错误。 4和 5个的代码片段还创建唯一索引,但有一个有趣的,微妙的差异在这些限制是如何在SQL Server中表示。 执行完第一个代码段后,Management Studio在NullDemo表中显示以下对象:

    Note that a non-clustered index was created, in addition to a unique key. Now, let’s drop this index using the standard syntax:

    请注意,除了唯一键之外,还创建了非聚集索引。 现在,让我们使用标准语法删除该索引:

     
    DROP INDEX UX_NullDemo_Null_Id ON dbo.NulLDemo; 
     
    

    The result is an error:

    结果是一个错误:

    Msg 3723, Level 16, State 5, Line 38
    An explicit DROP INDEX is not allowed on index ‘dbo.NulLDemo.UX_NullDemo_Null_Id’. It is being used for UNIQUE KEY constraint enforcement.

    Msg 3723,第16级,状态5,第38行
    索引'dbo.NulLDemo.UX_NullDemo_Null_Id'上不允许使用显式DROP INDEX。 它用于UNIQUE KEY约束实施。

    Hmmm, my attempt to drop a unique index failed as it’s considered a constraint. Let’s try the drop constraint syntax:

    嗯,我尝试删除唯一索引的尝试失败了,因为它被视为约束。 让我们尝试放置约束语法:

     
    ALTER TABLE dbo.NullDemo DROP CONSTRAINT UX_NullDemo_Null_Id;
     
    

    This time, the constraint drops as expected:

    这次,约束按预期下降:

    Command(s) completed successfully.

    命令已成功完成。

    Strange, but we’ll move on for now and come back to this in a bit. Let’s create the index using the 4th snippet and examine the contents of the table via SQL Server Management Studio:

    奇怪,但是我们现在继续,再回到这一点。 让我们使用第4 代码段创建索引,并通过SQL Server Management Studio检查表的内容:

    After refreshing the tree above, we notice that there is nothing under the “Keys” section! We were being lead to believe that a unique index and unique constraint are the same, but clearly there are differences. Let’s drop the constraint as we did before:

    刷新上面的树后,我们注意到“键”部分下没有任何内容! 我们被认为唯一索引和唯一约束是相同的,但是显然存在差异。 让我们像以前一样删除约束:

     
    ALTER TABLE dbo.NullDemo DROP CONSTRAINT UX_NullDemo_Null_Id;
     
    

    Running this yields an error (again!?):

    运行此命令将产生错误(再次!?):

    Msg 3728, Level 16, State 1, Line 41
    ‘UX_NullDemo_Null_Id’ is not a constraint.
    Msg 3727, Level 16, State 0, Line 41
    Could not drop constraint. See previous errors.

    消息3728,第16层,状态1,第41行
    'UX_NullDemo_Null_Id'不是约束。
    消息3727,级别16,状态0,第41行
    无法删除约束。 请参阅先前的错误。

    SQL Server can’t seem to make up its mind! Let’s use the alternate DROP INDEX syntax:

    SQL Server似乎无法下定决心! 让我们使用替代的DROP INDEX语法:

     
    DROP INDEX UX_NullDemo_Null_Id ON dbo.NulLDemo;
     
    

    Once again, we have success:

    我们再次取得成功:

    Command(s) completed successfully.

    命令已成功完成。

    Let’s check out entries in SQL Server’s catalog views for constraints and indexes to verify how these objects are perceived there:

    让我们在SQL Server的目录视图中检出约束和索引条目,以验证在那里如何看待这些对象:

     
    SELECT
    	*
    FROM sys.key_constraints
    WHERE name = 'UX_NullDemo_Null_Id';
    SELECT
    	*
    FROM sys.indexes
    WHERE indexes.name = 'UX_NullDemo_Null_Id';
     
    

    For the first constraint creation, using the ALTER TABLE…ADD CONSTRAINT syntax, a row is returned from each view:

    对于第一个约束创建,使用ALTER TABLE…ADD CONSTRAINT语法,从每个视图返回一行:

    That’s as expected, now let’s run the exact same queries after we use the CREATE UNIQUE INDEX syntax:

    符合预期,现在让我们在使用CREATE UNIQUE INDEX语法之后运行完全相同的查询:

    Sys.key_constraints returns nothing, implying that SQL Server does delineate between a unique index and a unique constraint, despite the fact that their functionality appears identical.

    Sys.key_constraints不返回任何内容,这意味着SQL Server确实在唯一索引和唯一约束之间进行了描述,尽管它们的功能看起来相同。

    The only difference (aside from the various syntax above) that I have arrived at between unique constraints and unique indexes is that unique constraints fit the ANSI standard for unique constraints, whereas a unique index technically does not. Performance between these alternatives is identical in all tests I could dream up, and the behavior when attempting to violate uniqueness is the same. To test this, let’s create a unique constraint and try to insert a duplicate value into our table:

    我在唯一约束和唯一索引之间得出的唯一区别(除了上述各种语法)是唯一约束适合唯一约束的ANSI标准,而唯一索引在技术上不适合。 在我可以梦想的所有测试中,这些替代方案之间的性能是相同的,并且尝试违反唯一性时的行为是相同的。 为了测试这一点,让我们创建一个唯一约束,然后尝试在表中插入重复的值:

     
    ALTER TABLE dbo.NullDemo ADD CONSTRAINT UX_NullDemo_Null_Id UNIQUE (Null_Id);
     
    INSERT INTO dbo.NullDemo
    	(Null_Id, Null_Data, Not_Null_Data)
    SELECT
    	3, 'Wooly Mammoth ', 'Furry!';
     
    

    The result is the expected error:

    结果是预期的错误:

    Msg 2627, Level 14, State 1, Line 80 Violation of UNIQUE KEY constraint ‘UX_NullDemo_Null_Id’. Cannot insert duplicate key in object ‘dbo.NullDemo’. The duplicate key value is (3). The statement has been terminated.

    消息2627,级别14,状态1,第80行违反UNIQUE KEY约束'UX_NullDemo_Null_Id'。 无法在对象'dbo.NullDemo'中插入重复的密钥。 重复键值为(3)。 该语句已终止。

    Let’s repeat the experiment with a unique index:

    让我们用唯一索引重复该实验:

     
    ALTER TABLE dbo.NullDemo DROP CONSTRAINT UX_NullDemo_Null_Id;
     
    CREATE UNIQUE NONCLUSTERED INDEX UX_NullDemo_Null_Id ON dbo.NullDemo (Null_Id);
     
    INSERT INTO dbo.NullDemo
    	(Null_Id, Null_Data, Not_Null_Data)
    SELECT
    	3, 'Wooly Mammoth ', 'Furry!';
     
    

    Again, we get an error, but the text is slightly different, further differentiating between a unique index and a unqiue constraint:

    再次,我们得到一个错误,但是文本略有不同,从而进一步区分了唯一索引和非限制约束:

    Msg 2601, Level 14, State 1, Line 91 Cannot insert duplicate key row in object ‘dbo.NullDemo’ with unique index ‘UX_NullDemo_Null_Id’. The duplicate key value is (3). The statement has been terminated.

    消息2601,级别14,状态1,第91行无法在具有唯一索引“ UX_NullDemo_Null_Id”的对象“ dbo.NullDemo”中插入重复的键行。 重复键值为(3)。 该语句已终止。

    To summarize everything above, a unique constraint is a constraint and an index, whereas a unique index is only an index. Despite the differences, the behavior of each is identical. Given the choice, I prefer to stick to standards and increased documentation, and would lean towards implementing unique constraints instead of unique indexes. Neither is incorrect and Microsoft makes no formal declaration for or against either, but the differing visibility of each is meaningful and inclusion in two sets of useful metadata can be handy when managing schema and metadata in the future.

    综上所述,唯一约束是约束和索引,而唯一索引仅是索引。 尽管存在差异,但是每个的行为都是相同的。 如果有选择,我宁愿坚持标准和增加文档编制,并且倾向于实现唯一约束而不是唯一索引。 两者都不是不正确的,并且Microsoft都没有正式声明支持或反对任何声明,但是每种视图的不同可见性都是有意义的,将来在管理架构和元数据时,将它们包含在两组有用的元数据中可能会很方便。

    As an aside, creating a unique clustered index serves similar functionality to a clustered primary key, whereas a unique non-clustered index is very similar to a non-clustered primary key. Keep this in mind as we move forward.

    顺便说一句,创建唯一的聚集索引的功能与聚集的主键相似,而唯一的非聚集索引与非聚集主键非常相似。 在前进的过程中,请牢记这一点。

    Unique constraints can also be created in-line, such as in this example:

    唯一约束也可以在线创建,例如以下示例:

     
    CREATE TABLE dbo.NullDemo2
    (	Null_Id2 INT NOT NULL CONSTRAINT UX_NullDemo2_Null_Id2 UNIQUE,
    	Null_Data2 VARCHAR(50) NULL,
    	Not_Null_Data2 VARCHAR(50) NOT NULL);
     
    

    In this similar table, we define a unique (non-clustered) constraint on Null_Id2 within the table definition. If you know that a unique constraint will be needed up-front when the table is defined, you can take advantage of this simplified syntax and include it, instead of defining the constraint later on in your script.

    在这个类似的表中,我们在表定义内的Null_Id2上定义了唯一的(非集群)约束。 如果您知道在定义表时首先需要一个唯一的约束,则可以利用此简化语法并将其包括在内,而不是稍后在脚本中定义约束。

    How do unique constraints affect performance? The most significant way is in how they affect cardinality. For a column with a unique constraint, we know that there can never be more than one of any given value. Statistics will often provide the necessary information in order to determine this, though, but in the event that statistics are unavailable or incomplete, this little bit of information provided by the unique index can assist in building a more efficient execution plan.

    唯一约束如何影响性能? 最重要的方式是它们如何影响基数。 对于具有唯一约束的列,我们知道任何给定值都不能超过一个。 统计信息通常会提供必要的信息来确定这一点,但是,如果统计信息不可用或不完整,则唯一索引提供的少量信息可以帮助构建更有效的执行计划。

    The work of a unique index occurs on an insert or update, when it is necessary to check and see if a value already exists prior to making the change. What does this cost? To investigate, we’ll look at the unique index AK_SalesOrderDetail_rowguid on Sales.SalesOrderDetail. Let’s run a simple update on the table:

    唯一索引的工作发生在插入或更新上,需要在更改之前检查并查看值是否已存在。 这要花多少钱? 为了进行调查,我们将查看Sales.SalesOrderDetail上的唯一索引AK_SalesOrderDetail_rowguid 。 让我们在表上运行一个简单的更新:

     
    UPDATE Sales.SalesOrderDetail
    	SET rowguid = 'A207C96D-D9E6-402B-8470-2CC176C42283'
    WHERE rowguid = 'B207C96D-D9E6-402B-8470-2CC176C42283';
     
    

    After executed, we can review the STATISTICS IO output and execution plan in order to see the work that was performed:

    执行后,我们可以查看STATISTICS IO的输出和执行计划,以查看已执行的工作:

    Table ‘SalesOrderDetail’. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    表“ SalesOrderDetail”。 扫描计数为0,逻辑读取为22,物理读取为0,预读为0,lob逻辑读取为0,lob物理读取为0,lob提前读取为0。

    We can see that the entire index had to be checked (via an index seek) in order to verify that the value I am assigning does not already exist. Of course, the index had to be read anyway in order to locate the row that needed updating. Let’s replace the unique index with a standard non-clustered index:

    我们可以看到必须检查整个索引(通过索引查找)以验证我分配的值不存在。 当然,无论如何都必须读取索引,以便找到需要更新的行。 让我们用标准的非聚集索引替换唯一索引:

     
    ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT AK_SalesOrderDetail_rowguid;
    CREATE NONCLUSTERED INDEX AK_SalesOrderDetail_rowguid ON Sales.SalesOrderDetail
    (	rowguid ASC	);
     
    

    Now let’s run a similar update to above:

    现在,让我们运行与上面类似的更新:

     
    UPDATE Sales.SalesOrderDetail
    	SET rowguid = 'B207C96D-D9E6-402B-8470-2CC176C42283'
    WHERE rowguid = 'A207C96D-D9E6-402B-8470-2CC176C42283';
     
    

    The results are similar to before:

    结果与之前类似:

    Table ‘SalesOrderDetail’. Scan count 1, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    表“ SalesOrderDetail”。 扫描计数1,逻辑读25,物理读0,预读0,lob逻辑读0,lob物理读0,lob预读0。

    It turns out that the effort required to enforce uniqueness is the same effort needed to locate the rows for update anyway. Let’s perform the same experiment with an INSERT, which will not require us to locate any rows prior to the operation:

    事实证明,强制唯一性所需的工作与定位行以进行更新所需的工作相同。 让我们使用INSERT执行相同的实验,它不需要我们在操作之前定位任何行:

     
    DELETE FROM Sales.SalesOrderDetail WHERE rowguid = 'B207C96D-D9E6-402B-8470-2CC176C42283';
    INSERT INTO Sales.SalesOrderDetail
    	(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
    SELECT
    	43659,
    	'4911-403C-98',
    	1,
    	776,
    	1,
    	2024.994,
    	0.00,
    	'B207C96D-D9E6-402B-8470-2CC176C42283',
    	'2011-05-31 00:00:00.000'
     
    

    With the unique constraint in place, the performance is as follows:

    有了唯一的约束,性能如下:

    Table ‘SalesOrderDetail’. Scan count 0, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    表“ SalesOrderDetail”。 扫描计数0,逻辑读9,物理读0,预读0,lob逻辑读0,lob物理读0,lob预读0。

    Now we’ll replace the unique constraint with a non-unique non-clustered index and repeat the experiment:

    现在,我们将非唯一约束替换为非唯一非聚集索引,然后重复实验:

     
    ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT AK_SalesOrderDetail_rowguid;
    CREATE NONCLUSTERED INDEX AK_SalesOrderDetail_rowguid ON Sales.SalesOrderDetail
    (	rowguid ASC	)
    DELETE FROM Sales.SalesOrderDetail WHERE rowguid = 'B207C96D-D9E6-402B-8470-2CC176C42283';
    INSERT INTO Sales.SalesOrderDetail
    	(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
    SELECT
    	43659,
    	'4911-403C-98',
    	1,
    	776,
    	1,
    	2024.994,
    	0.00,
    	'B207C96D-D9E6-402B-8470-2CC176C42283',
    	'2011-05-31 00:00:00.000'
     
    

    The results end up being exactly the same as before. The presence of an index is all that is required in order to validate uniqueness, and since a unique constraint always creates an underlying index, this requirement is fulfilled automatically.

    结果最终与以前完全相同。 索引的存在是验证唯一性所需要的全部,并且由于唯一约束始终会创建基础索引,因此该要求会自动满足。

    While we may have thought that the need to validate uniqueness would be expensive or a burden to our write operations, these experiments (as well as any more we can dream up) show that the performance difference of writes with a unique constraint versus a non-clustered index are insignificant. Of course, the addition of a unique constraint to a column that previously had no index at all would benefit from the addition of the index in the same way that any indexed column will speed up reads at the expense of the write operations needed to maintain it.

    尽管我们可能认为验证唯一性的需求会很昂贵,或者会给我们的写操作带来负担,但这些实验(以及我们梦dream以求的其他事情)表明,具有唯一约束的写性能与非唯一写条件的写性能差异聚簇索引无关紧要。 当然,向以前根本没有索引的列添加唯一约束将受益于索引的添加,其方式与任何索引列都将加快读取速度相同,而维护该列需要进行写操作。

    主键约束 (Primary Key constraints)

    A primary key is THE uniquely identifying metadata for a table. This is a critical part of database design and it is generally important to define a primary key on all tables within a database. Primary keys serve as a unique constraint, a unique index, and as the ANSI standard that defines uniqueness in a table. You may only define a single primary key per table, regardless of whether it is clustered or non-clustered. A primary key may be created in-line with table creation or via an ALTER TABLE statement.

    主键是一个表唯一标识的元数据。 这是数据库设计的关键部分,在数据库中的所有表上定义主键通常很重要。 主键用作唯一约束,唯一索引以及定义表中唯一性的ANSI标准。 您只能为每个表定义一个主键,而不管它是集群的还是非集群的。 主键可以与表创建内联或通过ALTER TABLE语句创建。

    Primary keys carry special significance in database design. Anyone that is looking at a table for the first time will be checking to see what the primary key is defined as for the table. Is it an integer ID? Is it an account name? Is it a combination of several important data elements? Does it auto-number as an IDENTITY, or are values inserted somehow via a programmatic or user-driven process?

    主键在数据库设计中具有特殊的意义。 第一次查看表的任何人都将检查以查看为表定义了什么主键。 它是整数ID吗? 是账户名吗? 它是几个重要数据元素的组合吗? 它会自动编号为IDENTITY,还是通过编程或用户驱动的过程以某种方式插入值?

    A primary key by definition is a NOT NULL column. Attempts to insert NULL into a primary key column will result in an error. Similarly, adding a primary key on a column with duplicate values or NULLs will also generate an error.

    根据定义,主键是NOT NULL列。 尝试将NULL插入主键列将导致错误。 同样,在具有重复值或NULL的列上添加主键也会产生错误。

    In our previous example, we could have created the table with a primary key on Null_Id:

    在前面的示例中,我们可以在Null_Id上创建具有主键的表:

     
    CREATE TABLE dbo.NullDemo
    (	Null_Id INT NOT NULL CONSTRAINT PK_NullDemo PRIMARY KEY CLUSTERED,
    	Null_Data VARCHAR(50) NULL,
    	Not_Null_Data VARCHAR(50) NOT NULL);
     
    

    This table definition declares, up-front, that Null_Id is the logical unique identifier for any row within the NulLDemo table. Any other tables that reference NullDemo via a foreign key will need to reference this primary key. It is also likely that most joins to NullDemo will do so via Null_Id as it guarantees a predictable cardinality (one row per Null_Id).

    该表定义预先声明Null_IdNulLDemo表中任何行的逻辑唯一标识符。 通过外键引用NullDemo的任何其他表都需要引用此主键。 大多数加入NullDemo的连接也有可能会通过Null_Id这样做,因为它保证了可预测的基数(每个Null_Id一行)。

    A primary key can also be added to a table later on, assuming that the contents of the column fit the prerequisites of a primary key: Uniqueness and no NULLs:

    假设列的内容符合主键的前提:唯一性且没有NULL,则以后也可以将主键添加到表中。

     
    ALTER TABLE dbo.NullDemo DROP CONSTRAINT PK_NullDemo;
     
    ALTER TABLE dbo.NullDemo ADD CONSTRAINT PK_NullDemo PRIMARY KEY CLUSTERED (Null_Id);
     
    

    Within SQL Server Management Studio, a primary key will be given a special notation, separate from all other indexes, keys, and constraints:

    在SQL Server Management Studio中,主键将被赋予特殊的表示法,与所有其他索引,键和约束分开:

    The yellow key signifies a primary key, and as discussed earlier, there can only be one per table. As with a unique index, a primary key will require an index to be maintained in order to enforce uniqueness. A primary key may be clustered or non-clustered, depending on whether this column is the most common or efficient organizing column for the table.

    黄色键表示一个主键,如前所述,每个表只能有一个。 与唯一索引一样,主键将要求维护索引以强制唯一性。 主键可以是群集的,也可以是非群集的,这取决于此列是表的最常见还是最有效的组织列。

    Typically, the clustered index on a table is also the primary key, though there is no requirement or enforcement of this convention. Special cases may arise in which it makes sense to define a primary key separately from a clustered index. A plausible scenario for this would be when the primary key on a table is an auto-numbering integer identity, but the clustered index is on a pair of critical identifiers. When designing a table, consider carefully whether it makes sense for the clustered index to be separate from the primary key, or if having them be one and the same is simpler and more efficient.

    通常,表上的聚集索引也是主键,尽管没有要求或强制执行此约定。 可能出现特殊情况,其中有必要与聚簇索引分开定义主键。 可能的情况是,表上的主键是自动编号的整数标识,而聚集索引位于一对关键标识符上。 设计表时,请仔细考虑将聚簇索引与主键分开是否有意义,或者使它们成为一个主键是否更简单,更有效。

    Note that in order for a primary key to be dropped, any foreign keys on other tables that reference it must also be dropped first. In addition, if the primary key is also the clustered index, then all other non-clustered index will need to be dropped before SQL Server will allow you to drop the clustered primary key. This is because non-clustered indexes reference the clustered index and you are not given the liberty of pulling the rug out from under a table’s logical storage.

    请注意,为了删除主键,引用它的其他表上的所有外键也必须先删除。 此外,如果主键也是聚集索引,则在SQL Server允许您删除聚集主键之前,需要删除所有其他非聚集索引。 这是因为非聚簇索引引用了聚簇索引,并且您没有从表的逻辑存储空间中拉出地毯的自由。

    The performance of primary keys is based on the performance of a handful of components:

    主键的性能基于少数组件的性能:

    • The unique index created for use by the primary key.

      创建供主键使用的唯一索引。
    • Any foreign keys that can benefit from referencing the primary ley.

      任何可以从引用主ley中受益的外键。
    • If the primary key is also the clustered index, then all benefits of a clustered index will also be realized.

      如果主键也是聚簇索引,那么聚簇索引的所有好处也将实现。
    • The NOT NULL constraint that is required by a primary key.

      主键要求的NOT NULL约束。

    A primary key in of itself does not provide any performance magic, but the building blocks that are created for and alongside a primary key each provide their own benefits. In the same way that all tables should have a primary key, tables should also have a carefully chosen clustered index. Reading and writing to tables without a clustered index will be expensive and incur a variety of penalties due to the inefficient nature of heaps.

    主键本身并不能提供任何性能魔咒,但是为主键创建的并与主键一起创建的构建块各自具有各自的优势。 与所有表都应具有主键的方式相同,表还应具有精心选择的聚集索引。 读取和写入不具有聚集索引的表将很昂贵,并且由于堆的低效率而导致各种罚款。

    When chosen wisely, a clustered primary key can provide the benefits of a variety of constraints: NOT NULL, uniqueness, clustered index, and foreign keys. If you’re omitting a primary key (clustered or otherwise) from a table and believe it to be the right choice, be sure to thoroughly test and verify that such a hypothesis is indeed true. I’ve heard many arguments over the years as to why certain types of tables benefit from the omission of a primary key or clustered index, but rarely have those debates been based on fact. If you can prove that either does not help performance, and never will in the future, then you may consider omitting them, though I suspect that to be a very rare scenario!

    如果明智地选择,集群主键可以提供各种约束的好处:NOT NULL,唯一性,集群索引和外键。 如果您从表中省略主键(集群键或其他键),并认为它是正确的选择,请确保彻底测试并验证这种假设的确是正确的。 多年来,我曾听到许多争论,为什么某些类型的表会因省略主键或聚集索引而受益,但是这些争论很少基于事实。 如果您可以证明其中之一对性能没有帮助,将来也无济于事,那么您可以考虑省略它们,尽管我怀疑这是非常罕见的情况!

    默认约束 (Default Constraints)

    A default constraint allows you to automatically populate a predetermined value for a column when none is provided upon insert. This is handy when a column is optional, but in the absence of a user-defined value, another is used instead. Some common uses of default constraints include:

    默认约束允许您在插入时未提供列的预定值时自动为其填充该值。 当列为可选列时,这很方便,但是在没有用户定义的值的情况下,将使用另一个。 默认约束的一些常见用法包括:

    • An active bit for accounts that always defaults to 1 for new rows.

      帐户的活动位,新行始终默认为1。
    • A notes column is populated with an empty string when no notes are entered.

      没有输入注释时,注释列中会填充一个空字符串。
    • The current date and time are entered into a last updated date/time column.

      当前日期和时间输入到最近更新的日期/时间列中。
    • The user performing an action is logged whenever a row is inserted.

      每当插入行时,都会记录执行操作的用户。
    • To differentiate between data that is user-provided and that which is system generated.

      区分用户提供的数据和系统生成的数据。

    Default constraints allow you to maintain a NOT NULL constraint on a column, even when a user or application may not provide a value for it. Be careful not to use default constraints to eliminate NULLable columns when they make the most sense for your data model. For example, a default of “1/1/1900” for a date column may seem nonsensical enough to avoid confusion, but could result in application complexity in the future if any developers forget about this default, or if they attempt to perform date math on the column without checking for the default dummy value.

    默认约束允许您在列上维护NOT NULL约束,即使用户或应用程序可能不为其提供值。 当对数据模型最有意义时,请注意不要使用默认约束来消除NULLable列。 例如,日期列的默认值“ 1/1/1900”看起来似乎很荒谬,可以避免混淆,但是如果任何开发人员忘记了该默认值,或者他们尝试执行日期数学运算,则将来可能导致应用程序复杂化而不检查默认哑元值。

    In order to demonstrate default constraints, we’ll create a new table to experiment on:

    为了演示默认约束,我们将创建一个新表进行实验:

     
    CREATE TABLE dbo.Accounts
    (	Account_Id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Accounts PRIMARY KEY CLUSTERED,
    	Account_Name VARCHAR(100) NOT NULL,
    	Account_Notes VARCHAR(1000) NOT NULL CONSTRAINT DF_Accounts_Account_Notes DEFAULT (''),
    	Is_Active BIT NOT NULL CONSTRAINT DF_Accounts_Is_Active DEFAULT (1),
    	Create_Datetime DATETIME NOT NULL CONSTRAINT DF_Accounts_Create_Datetime DEFAULT (CURRENT_TIMESTAMP),
    	Created_By_User VARCHAR(50) NULL CONSTRAINT DF_Accounts_Created_By_User DEFAULT (ORIGINAL_LOGIN())	);
     
    

    I’ve included plenty of default constraints here, using a variety of data types. Note that for these and all other constraints created in this article, I’ve consistently included constraint names. It is a good practice to ALWAYS name any objects that you create. If you create one without a name, SQL Server will automatically generate a name that will be neither elegant or helpful. For example, if I left the name off of the default on Created_By_User, it automatically gets named DF__Accounts__Create__6521F869. If I drop and recreate the table, it is named DF__Accounts__Create__6ADAD1BF. These names are not very meaningful, nor are they consistent, so always name everything, your life will be easier that way 🙂

    我在这里使用了多种数据类型,其中包括许多默认约束。 请注意,对于本文中创建的所有这些约束以及所有其他约束,我一直都包含约束名称。 始终命名您创建的任何对象都是一个好习惯。 如果您创建一个没有名称的名称,SQL Server将自动生成一个既不优雅也不有用的名称。 例如,如果我将名称保留为Created_By_User上的默认名称,它将自动命名为DF__Accounts__Create__6521F869 。 如果删除并重新创建表,该表将命名为DF__Accounts__Create__6ADAD1BF 。 这些名字不是很有意义,也不是一致的,所以总是给所有的名字起名字,这样会使你的生活更轻松🙂

    Let’s say I insert a row into the table using this TSQL:

    假设我使用此TSQL在表中插入一行:

     
    INSERT INTO dbo.Accounts
    	(Account_Name, Account_Notes)
    SELECT
    	'Dinosaur Corp.',
    	'The leader in dinosaur personal care products.';
     
    

    While I only provided the account name and notes, the remaining columns were populated for me using the defaults that we expect and would want. This is a great way to audit data as it is created and simplify INSERT statements.

    虽然我只提供了帐户名称和注释,但其余的列是使用我们期望和想要的默认值为我填充的。 这是审核数据创建和简化INSERT语句的好方法。

    Default constraints will automatically fill-in-the-blanks when you do not provide a value, but will not override an explicit NULL. If you insert a row and intentionally attempt to put a NULL into a NOT NULL column with a default, then you’ll get an error.

    当您不提供值时,默认约束将自动填充空白,但不会覆盖显式NULL。 如果您插入一行并有意尝试将NULL设置为具有默认值的NOT NULL列,那么您将得到一个错误。

     
    INSERT INTO dbo.Accounts
    	(Account_Name, Account_Notes, Is_Active)
    SELECT
    	'Pterodactyls Anonymous',
    	'The world''s trusted support group for our flying friends.',
    	NULL
     
    

    The TSQL above will throw the following error:

    上面的TSQL将引发以下错误:

    Msg 515, Level 16, State 2, Line 152 Cannot insert the value NULL into column ‘Is_Active’, table ‘AdventureWorks2014.dbo.Accounts’; column does not allow nulls. INSERT fails.

    消息515,级别16,状态2,第152行无法将值NULL插入表'AdventureWorks2014.dbo.Accounts'的'Is_Active'列中; 列不允许为空。 插入失败。

    Default constraints do not override explicit values, even if they are NULL. Similarly, an attempt to insert a NULL into a column that allows NULLs will do so and ignore the default:

    默认约束不会覆盖显式值,即使它们为NULL。 同样,尝试将NULL插入允许NULL的列中也会这样做,并且会忽略默认值:

     
    INSERT INTO dbo.Accounts
    	(Account_Name, Account_Notes, Created_By_User)
    SELECT
    	'Pterodactyls Anonymous',
    	'The world''s trusted support group for our flying friends.',
    	NULL
     
    

    This TSQL executes successfully, resulting in the following data:

    该TSQL成功执行,得到以下数据:

    Note that the NULL in the INSERT statement overrides the default constraint on that column.

    请注意,INSERT语句中的NULL会覆盖该列上的默认约束。

    Any constant may be used in a default constraint so long as its value is deterministic as of the time it is assigned. The current date or time will default to the date/time at the moment the row is created, whereas the login of the user will default to the user at that same moment, and will not change later. Defaults cannot reference other tables, contain subqueries, reference other columns in the same table, or otherwise require any information beyond a static constant. Computed columns are a nice way to gain some of those benefits, if there is no need for a combination of user/system generated values.

    任何常量都可以在默认约束中使用,只要其值在分配时就具有确定性即可。 当前日期或时间将默认为创建行时的日期/时间,而用户的登录名将同时为该用户的默认名,并且以后将不会更改。 默认值不能引用其他表,包含子查询,引用同一表中的其他列或以其他方式要求除静态常量之外的任何信息。 如果不需要用户/系统生成的值的组合,则计算列是获得其中一些好处的好方法。

    The performance impact of default constraints will only be felt if a new column is added and there is a need to backfill all existing rows with a default constraint for that new column. Otherwise, you will not see any increased IO as a result of the use of default constraints. The cost to insert a value yourself vs. the cost to have the default constraint do so are identical. The cost to insert an explicit value into a column with a default constraint is the same as the cost to let the constraint do the work for you.

    仅当添加新列并且需要用该新列的默认约束回填所有现有行时,才会感觉到默认约束的性能影响。 否则,由于使用默认约束,您不会看到任何增加的IO。 自己插入值的成本与具有默认约束的成本是相同的。 在具有默认约束的列中插入显式值的成本与让约束为您完成工作的成本相同。

    检查约束 (Check Constraints)

    Sometimes we want to enforce specific logic on our data, but doing so through stored procedures or application logic would be extremely complex or risky. Check constraints allow us to validate data whenever it is written, thus allowing us to check for specific data or compare different columns in a table. These constraints can verify the value of a single column or they can compare any number of columns to ensure data integrity.

    有时我们想对数据执行特定的逻辑,但是通过存储过程或应用程序逻辑来执行则非常复杂或冒险。 检查约束使我们能够在每次写入数据时对其进行验证,从而使我们可以检查特定数据或比较表中的不同列。 这些约束可以验证单个列的值,也可以比较任何数量的列以确保数据完整性。

    For example, here is a table with some check constraints defined on it:

    例如,这是一个表,上面定义了一些检查约束:

     
    CREATE TABLE dbo.Dinosaur
    (	Dinosaur_Id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Dinosaur PRIMARY KEY CLUSTERED,
    	Dinosaur_Name VARCHAR(100) NOT NULL,
    	Dinosaur_Type VARCHAR(25) NOT NULL CONSTRAINT CK_Dinosaur_Dinosaur_Type CHECK (Dinosaur_Type IN ('Theropod', 'Sauropod', 'Thyreophora', 'Cerapod')),
    	Is_Herbivore BIT NOT NULL	);
     
    ALTER TABLE dbo.Dinosaur ADD CONSTRAINT CK_Dinosaur_Is_Herbivore CHECK ((Dinosaur_Type = 'Theropod' AND Is_Herbivore = 0) OR (Dinosaur_Type <> 'Theropod' AND Is_Herbivore = 1));
     
    

    Here, we define a check constraint on a single column in the Dinosaur table, and then create an additional one afterwards that references multiple columns. If we insert a row into the table and it meets both conditions, then we’ll have no problem:

    在这里,我们在Dinosaur表中的单个列上定义一个检查约束,然后再创建一个引用多个列的约束。 如果我们在表中插入一行并且满足两个条件,那么我们将没有问题:

     
    INSERT INTO dbo.Dinosaur
    	(Dinosaur_Name, Dinosaur_Type, Is_Herbivore)
    SELECT
    	'Micropachycephalosaurus',
    	'Cerapod',
    	1;
     
    

    This dinosaur meets the criteria we imposed on the table, so it is inserted normally:

    该恐龙符合我们强加在桌子上的条件,因此可以正常插入:

    How cute! Let’s say we tried to add a row for T-Rex and accidentally set him as an herbivore…

    多么可爱! 假设我们尝试为T-Rex添加一行,并将其偶然设置为草食动物……

     
    INSERT INTO dbo.Dinosaur
    	(Dinosaur_Name, Dinosaur_Type, Is_Herbivore)
    SELECT
    	'Tyrannosaurus Rex',
    	'Theropod',
    	1;
     
    

    When you violate a check constraint, the result is an error that tells you specifically which constraint was the cause of the error, as well as where it is located:

    当您违反检查约束时,结果是一个错误,该错误会具体告诉您是哪个约束导致了错误,以及错误的位置:

    Msg 547, Level 16, State 0, Line 196 The INSERT statement conflicted with the CHECK constraint “CK_Dinosaur_Is_Herbivore”. The conflict occurred in database “AdventureWorks2014”, table “dbo.Dinosaur”.

    消息547,级别16,状态0,行196 INSERT语句与CHECK约束“ CK_Dinosaur_Is_Herbivore”相冲突。 冲突发生在数据库“ AdventureWorks2014”的表“ dbo.Dinosaur”中。

    We immediately know that the error was caused by CK_Dinosaur_Is_Herbivore and can quickly look at the definition of that constraint and verify if my insert statement was no good or if the check constraint was erroneous. This raises an important point with regards to the maintenance of check constraints in that they require it. Any check constraint that references specific data that could change will require review in the same way that application or database code is reviewed. If a new dinosaur is discovered tomorrow that comprises a type not shown here, we would need to update the check constraint to include it. Alternatively, if a new variety of herbivorous cryolophosaurus is discovered, we’d need to adjust our rules to account for it. This can result in check constraints becoming very complex, hard to maintain, and error-prone. For scenarios where lots of metadata is involved or it changes often, consider creating a table to store valid types, rather than trying to squeeze all that logic into a check constraint.

    我们立即知道该错误是由CK_Dinosaur_Is_Herbivore引起的,可以快速查看该约束的定义并验证我的insert语句是否不好,或者检查约束是否错误。 这在维护检查约束方面提出了一个重要的观点,因为他们需要它。 引用可能更改的特定数据的任何检查约束都将需要以与检查应用程序或数据库代码相同的方式进行检查。 如果明天发现一种新的恐龙,其类型未在此处显示,则我们需要更新检查约束以将其包括在内。 另外,如果发现了一种新的草食性低温龙,我们需要调整规则以解决这一问题。 这可能导致检查约束变得非常复杂,难以维护并且容易出错。 对于涉及大量元数据或它经常变化的场景,请考虑创建一个表来存储有效类型,而不是尝试将所有逻辑都挤压到检查约束中。

    One additional note on check constraints: NULL is not a value in the same way that a number or string is. As a result, if a check constraint exists on a NULLable column, and the constraint doesn’t explicitly validate NULL in some fashion, then NULL will not fail. For example, if our Dinosaur_Type column from above were NULLable, then an operation that puts NULL into that column will not violate the check constraint. Remember that any comparison that operates against a NULL evaluates to unknown, which is not false. This is a similar effect to when you use an equality in a WHERE clause against a column with NULLs in it. If a check constraint exists on a column that allows NULL, be sure to account for it by either including a NULL check in the constraint, or by changing the column to NOT NULL.

    关于检查约束的另一条注释:NULL不是与数字或字符串相同的值。 结果,如果在NULLable列上存在检查约束,并且该约束没有以某种方式显式验证NULL,则NULL不会失败。 例如,如果上面的Dinosaur_Type列可为NULL,则将NULL放入该列的操作不会违反检查约束。 请记住,任何针对NULL进行的比较都会得出未知值,这不是错误的。 这与在WHERE子句中对其中包含NULL的列使用等于时的效果类似。 如果在允许NULL的列上存在检查约束,请确保通过在约束中包括NULL检查或将列更改为NOT NULL来解决它。

    There are other reasons to be cautious with check constraints. While they are an effective way to bolster data integrity, it is easy to go a bit wild and apply excessive amounts of business logic directly to tables within the database. This can result in lazy development and failure to vigorously check and sanitize inputs before those values reach the database. A well-written application will validate data thoroughly in code and then in the database for critical scenarios. We cannot check for every possible bit of bad data, so it is up to us to determine the most important scenarios and protect against those, if needed. Sometimes excessive use of check constraints is indicative of design flaws elsewhere that we are trying to patch over after-the-fact, rather than fixing the underlying design.

    还有其他原因要注意检查约束。 尽管它们是提高数据完整性的有效方法,但很容易变得疯狂,直接将过多的业务逻辑直接应用于数据库中的表。 这可能导致延迟开发,并且无法在这些值到达数据库之前大力检查和清理输入。 编写良好的应用程序将针对关键场景在代码中然后在数据库中彻底验证数据。 我们无法检查所有可能的不良数据,因此我们需要确定最重要的方案并在需要时采取措施。 有时过度使用检查约束表示其他地方的设计缺陷,我们试图在事后修补这些缺陷,而不是修复基础设计。

    In no way am I discouraging the use of check constraints, but be mindful of the best way to enforce data integrity without creating technical debt. Definitely encourage the use of dinosaurs in your data as needed, though!

    我绝不鼓励使用检查约束,但要牢记在不增加技术负担的情况下强制执行数据完整性的最佳方法。 不过,绝对鼓励您在数据中使用恐龙!

    How do check constraints affect performance? In terms of write operations, the impact is generally negligible. I attempted to create a variety of tables and apply all sorts of check constraints to them in order to generate latency, but was unable to move the needle much. In general, constraints are checked against the explicit scalar values prior to writing to the database. In other words, set-based logic does not revert to iterative approaches as a result of check constraints, even if they are complex, for example using math or string functions. If a check constraint includes a function that accesses other tables/views/objects, then the cost of those operations will weigh heavily on write operations, though.

    检查约束如何影响性能? 在写操作方面,影响通常可以忽略不计。 我试图创建各种表并对它们应用各种检查约束,以产生延迟,但无法进行很多操作。 通常,在写入数据库之前,针对显式标量值检查约束。 换句话说,基于集合的逻辑不会由于检查约束而恢复为迭代方法,即使它们比较复杂(例如使用数学或字符串函数)也是如此。 If a check constraint includes a function that accesses other tables/views/objects, then the cost of those operations will weigh heavily on write operations, though.

    Read operations can benefit from check constraints by providing the query optimizer valuable information about a table and its contents. For example, what if I write a query against our dinosaur table that looks for dinosaurs of a type that are not allowed by the CK_Dinosaur_Dinosaur_Type constraint? To mimic a more realistic query scenario, I’ll add an index on the Dinosaur_Type column first:

    Read operations can benefit from check constraints by providing the query optimizer valuable information about a table and its contents. For example, what if I write a query against our dinosaur table that looks for dinosaurs of a type that are not allowed by the CK_Dinosaur_Dinosaur_Type constraint? To mimic a more realistic query scenario, I'll add an index on the Dinosaur_Type column first:

     
    CREATE NONCLUSTERED INDEX IX_Dinosaur_Dinosaur_Type ON dbo.Dinosaur (Dinosaur_Type);
     
    SELECT
    	Dinosaur_Name
    FROM dbo.Dinosaur
    WHERE Dinosaur_Type = 'Ceratopsian';
     
    

    Well, there are no Ceratopsian dinosaurs in our table because the check constraint simply doesn’t allow them. The column is NOT NULL, so NULL isn’t valid either. The result set is empty, but the execution plan and IO statistics are interesting:

    Well, there are no Ceratopsian dinosaurs in our table because the check constraint simply doesn't allow them. The column is NOT NULL, so NULL isn't valid either. The result set is empty, but the execution plan and IO statistics are interesting:

    For this query, there were zero reads against the table and the execution plan shows a constant scan. Similar to when we tried to pull NULL from a NOT NULL column, the check constraint provided valuable information to SQL Server about the Dinosaur_Type column and what values it is allowed to contain. Without accessing the table, SQL Server was able to deduce from table metadata and the WHERE clause of my query that what I was looking for was not possible, and knowing that information, it could complete query optimization without any significant load on the server.

    For this query, there were zero reads against the table and the execution plan shows a constant scan. Similar to when we tried to pull NULL from a NOT NULL column, the check constraint provided valuable information to SQL Server about the Dinosaur_Type column and what values it is allowed to contain. Without accessing the table, SQL Server was able to deduce from table metadata and the WHERE clause of my query that what I was looking for was not possible, and knowing that information, it could complete query optimization without any significant load on the server.

    Check constraints are utilized early in the query optimization process and provide a very fast way to rule out invalid values. If a column only allows four dinosaur types, then we can deduce that all other types will not exist in the table. In a very large table, this performance benefit can be significant. Be sure to weigh the documentation and maintenance costs of a check constraint versus data integrity and performance to make a smart decision as to whether or not a given check constraint makes sense.

    Check constraints are utilized early in the query optimization process and provide a very fast way to rule out invalid values. If a column only allows four dinosaur types, then we can deduce that all other types will not exist in the table. In a very large table, this performance benefit can be significant. Be sure to weigh the documentation and maintenance costs of a check constraint versus data integrity and performance to make a smart decision as to whether or not a given check constraint makes sense.

    Foreign Key constraints (Foreign Key constraints)

    An important part of data modelling and enforcing data integrity comes via foreign key constraints. These constraints link a column within one table to a primary key in another table. The foreign key column must always contain a valid value from the parent primary key column. Like with check constraints, whenever a scenario arises in which data is changed in which the foreign key column contains invalid data, an error will be thrown. Foreign keys can reference multiple columns, if the parent table has a compound primary key.

    An important part of data modelling and enforcing data integrity comes via foreign key constraints. These constraints link a column within one table to a primary key in another table. The foreign key column must always contain a valid value from the parent primary key column. Like with check constraints, whenever a scenario arises in which data is changed in which the foreign key column contains invalid data, an error will be thrown. Foreign keys can reference multiple columns, if the parent table has a compound primary key.

    To demo foreign keys, we can use the table Production.Product in Adventureworks, which contains four different foreign keys:

    To demo foreign keys, we can use the table Production.Product in Adventureworks, which contains four different foreign keys:

    The definitions of these keys are as follows:

    The definitions of these keys are as follows:

     
    ALTER TABLE [Production].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_ProductModel_ProductModelID] FOREIGN KEY([ProductModelID])
    REFERENCES [Production].[ProductModel] ([ProductModelID]);
     
    ALTER TABLE [Production].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID] FOREIGN KEY([ProductSubcategoryID])
    REFERENCES [Production].[ProductSubcategory] ([ProductSubcategoryID]);
     
    ALTER TABLE [Production].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode] FOREIGN KEY([SizeUnitMeasureCode])
    REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode]);
     
    ALTER TABLE [Production].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode] FOREIGN KEY([WeightUnitMeasureCode])
    REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode]);
     
    

    For each product, the product model, subcategory, size, and weight are all tied directly to other tables. Whenever new rows are inserted or existing ones are updated, the values for these columns will be validated against their parent tables. Consider the following UPDATE statement:

    For each product, the product model, subcategory, size, and weight are all tied directly to other tables. Whenever new rows are inserted or existing ones are updated, the values for these columns will be validated against their parent tables. Consider the following UPDATE statement:

     
    UPDATE Production.Product
    	SET WeightUnitMeasureCode = 'UM'
    WHERE ProductID = 317 
     
    

    This results in an error:

    This results in an error:

    Msg 547, Level 16, State 0, Line 1
    The UPDATE statement conflicted with the FOREIGN KEY constraint “FK_Product_UnitMeasure_WeightUnitMeasureCode”. The conflict occurred in database “AdventureWorks2014”, table “Production.UnitMeasure”, column ‘UnitMeasureCode’.
    The statement has been terminated.

    Msg 547, Level 16, State 0, Line 1
    The UPDATE statement conflicted with the FOREIGN KEY constraint “FK_Product_UnitMeasure_WeightUnitMeasureCode”. The conflict occurred in database “AdventureWorks2014”, table “Production.UnitMeasure”, column 'UnitMeasureCode'.
    The statement has been terminated.

    The code “UM” is not present in the Production.Unitmeasure table, and therefore any attempt to set the WeightMeasureCode to this value will immediately fail. On the other hand, assigning a value that is present will work normally:

    The code “UM” is not present in the Production.Unitmeasure table, and therefore any attempt to set the WeightMeasureCode to this value will immediately fail. On the other hand, assigning a value that is present will work normally:

     
    UPDATE Production.Product
    	SET WeightUnitMeasureCode = 'MM'
    WHERE ProductID = 317
     
    

    A foreign key column can allow NULL, and if so, will let you enter NULL instead of a valid value, if that makes sense:

    A foreign key column can allow NULL, and if so, will let you enter NULL instead of a valid value, if that makes sense:

     
    UPDATE Production.Product
    	SET WeightUnitMeasureCode = NULL
    WHERE ProductID = 317
     
    

    This is completely valid, but only if the column does not possess the NOT NULL constraint.

    This is completely valid, but only if the column does not possess the NOT NULL constraint.

    Foreign keys can be created with the NOCHECK keyword, which will cause the key to enforce data integrity in the future, but will allow any existing bad data to continue to reside in the table. This is generally frowned upon as it allows bad data to exist in a column where we clearly want to maintain a certain level of integrity. While there are some valid use-cases for NOCHECK, typically temporary in nature, I’d advise against using it. Constraints are a form of database documentation, and the presence of a NOCHECK constraint provides mixed information about the contents of the column(s) it applies to.

    Foreign keys can be created with the NOCHECK keyword, which will cause the key to enforce data integrity in the future, but will allow any existing bad data to continue to reside in the table. This is generally frowned upon as it allows bad data to exist in a column where we clearly want to maintain a certain level of integrity. While there are some valid use-cases for NOCHECK, typically temporary in nature, I'd advise against using it. Constraints are a form of database documentation, and the presence of a NOCHECK constraint provides mixed information about the contents of the column(s) it applies to.

    There is a performance cost to pay for writing to columns that are part of a foreign key. In order to validate the values being inserted or updated, it is necessary to read the parent table in order to ensure the value being added is present. If the parent column is indexed (which it should be!), then the cost is at best a clustered index seek. If the parent column is not indexed (it’s a heap), then a table scan is the result.

    There is a performance cost to pay for writing to columns that are part of a foreign key. In order to validate the values being inserted or updated, it is necessary to read the parent table in order to ensure the value being added is present. If the parent column is indexed (which it should be!), then the cost is at best a clustered index seek. If the parent column is not indexed (it's a heap), then a table scan is the result.

    To see this in action, let’s consider an update of the SizeMeasureCode column in Production.Product:

    To see this in action, let's consider an update of the SizeMeasureCode column in Production.Product :

     
    UPDATE Production.Product
    	SET SizeUnitMeasureCode = 'DM'
    WHERE ProductID = 317;
     
    

    Without a foreign key, we would simply read Production.Product in order to find the row we want to update, and then update it with the new value as we requested. With the foreign key, though, here is what we get for IO and execution plan:

    Without a foreign key, we would simply read Production.Product in order to find the row we want to update, and then update it with the new value as we requested. With the foreign key, though, here is what we get for IO and execution plan:

    Table ‘UnitMeasure’. Scan count 0, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ‘Product’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'UnitMeasure'. Scan count 0, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Product'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    In order to check the foreign key for the value we are assigning, a seek against the UnitMeasure table was required. Since this is a small lookup table that contains 38 rows, the cost of this operation is negligible. The Assert operator is what validates that the values we are writing to Production.Product exist in Production.UnitMeasure. If this were not the case, then the query would fail here with a foreign key conflict error that would look something like this:

    In order to check the foreign key for the value we are assigning, a seek against the UnitMeasure table was required. Since this is a small lookup table that contains 38 rows, the cost of this operation is negligible. The Assert operator is what validates that the values we are writing to Production.Product exist in Production.UnitMeasure . If this were not the case, then the query would fail here with a foreign key conflict error that would look something like this:

    Msg 547, Level 16, State 0, Line 357
    The UPDATE statement conflicted with the FOREIGN KEY constraint “FK_Product_UnitMeasure_SizeUnitMeasureCode”. The conflict occurred in database “AdventureWorks2014”, table “Production.UnitMeasure”, column ‘UnitMeasureCode’.

    Msg 547, Level 16, State 0, Line 357
    The UPDATE statement conflicted with the FOREIGN KEY constraint “FK_Product_UnitMeasure_SizeUnitMeasureCode”. The conflict occurred in database “AdventureWorks2014”, table “Production.UnitMeasure”, column 'UnitMeasureCode'.

    When adding a foreign key to an important table, consider this write cost when doing so. Make sure your server and storage systems can handle the additional load that will result from the read operations needed to check that foreign key constraint. This is especially important if the parent table contains a very large amount of data. Whereas a seek against a 38 row table was very fast, the same seek against a table with a hundred million rows could be quite expensive!

    When adding a foreign key to an important table, consider this write cost when doing so. Make sure your server and storage systems can handle the additional load that will result from the read operations needed to check that foreign key constraint. This is especially important if the parent table contains a very large amount of data. Whereas a seek against a 38 row table was very fast, the same seek against a table with a hundred million rows could be quite expensive!

    Some companies with exceptionally large tables will consider removing foreign keys in order to improve performance on write-heavy tables. This is a valid approach, but one in which the need for database integrity must be weighed against performance and then the best decision made based on those criteria. If foreign keys are omitted, what protects against bad data? If invalid values are created, how will this affect the application? This is not a simple decision to make, and is one that should be handled on a case-by-case basis. There are other tools available that can greatly help in offsetting the costs of foreign keys, such as partitioning, indexed views, and in-memory OLTP. In general, any tool that helps alleviate the performance cost of reading very large tables will help in managing the costs of foreign keys. Performance optimization strategies that help speed up foreign key checks are likely beneficial to any environment in which the database administrators are considering dropping foreign keys for performance reasons anyway.

    Some companies with exceptionally large tables will consider removing foreign keys in order to improve performance on write-heavy tables. This is a valid approach, but one in which the need for database integrity must be weighed against performance and then the best decision made based on those criteria. If foreign keys are omitted, what protects against bad data? If invalid values are created, how will this affect the application? This is not a simple decision to make, and is one that should be handled on a case-by-case basis. There are other tools available that can greatly help in offsetting the costs of foreign keys, such as partitioning, indexed views, and in-memory OLTP. In general, any tool that helps alleviate the performance cost of reading very large tables will help in managing the costs of foreign keys. Performance optimization strategies that help speed up foreign key checks are likely beneficial to any environment in which the database administrators are considering dropping foreign keys for performance reasons anyway.

    Performance issues due to foreign keys are not a problem that everyone will face, so please don’t consider foreign keys a hog, as they certainly are not. They are exceptional tools for enforcing data integrity and the costs are very often worth the benefits they provide. Managing big data is a broader challenge with many effective solutions available to alleviate performance problems. Omitting foreign keys should be a last resort, and one I would not recommend without a serious design discussion to ensure that removing one problem is not creating a larger one in the future.

    Performance issues due to foreign keys are not a problem that everyone will face, so please don't consider foreign keys a hog, as they certainly are not. They are exceptional tools for enforcing data integrity and the costs are very often worth the benefits they provide. Managing big data is a broader challenge with many effective solutions available to alleviate performance problems. Omitting foreign keys should be a last resort, and one I would not recommend without a serious design discussion to ensure that removing one problem is not creating a larger one in the future.

    结论 (Conclusion)

    Constraints provide a variety of methods for enforcing data and relational integrity. They allow us to model our schema after business needs and allow database objects to provide some level of built-in documentation that helps explain what tables relate to which other tables, what columns are required, and what significant rules must be followed at all times.

    Constraints provide a variety of methods for enforcing data and relational integrity. They allow us to model our schema after business needs and allow database objects to provide some level of built-in documentation that helps explain what tables relate to which other tables, what columns are required, and what significant rules must be followed at all times.

    Constraints have performance implications, both improving performance in some cases and consuming resources in others. Most of the time, the costs are worth it in that we can improve the quality of our data without the need to build complex application logic or database scripts that try to keep data in order. A well thought out database design will often answer constraint questions up-front. This makes it so that we do not need to address technical debt years later when we suddenly realize that one column should always have been populated, or that dinosaurs shouldn’t be assigned to non-existent species. Constraints are valuable tools, and ones that can improve documentation, performance, data integrity, and make sense of what would otherwise be complex data.

    Constraints have performance implications, both improving performance in some cases and consuming resources in others. Most of the time, the costs are worth it in that we can improve the quality of our data without the need to build complex application logic or database scripts that try to keep data in order. A well thought out database design will often answer constraint questions up-front. This makes it so that we do not need to address technical debt years later when we suddenly realize that one column should always have been populated, or that dinosaurs shouldn't be assigned to non-existent species. Constraints are valuable tools, and ones that can improve documentation, performance, data integrity, and make sense of what would otherwise be complex data.

    References and further reading (References and further reading)

    Microsoft has done a good job of documenting constraints, their syntax and usage, and demos of how to create and use them. The internet as a whole is on a bit shakier ground. I’ve read too many articles or Q&A sites where constraints are completely shunned, where performance data is completely wrong, or there are misunderstandings as to why we use them or how they work.

    Microsoft has done a good job of documenting constraints, their syntax and usage, and demos of how to create and use them. The internet as a whole is on a bit shakier ground. I've read too many articles or Q&A sites where constraints are completely shunned, where performance data is completely wrong, or there are misunderstandings as to why we use them or how they work.

    The following are some useful links from Microsoft that help explain the usage of constraints in SQL Server and the optimal ways to implement them:

    The following are some useful links from Microsoft that help explain the usage of constraints in SQL Server and the optimal ways to implement them:

    SQL Server catalog views for constraints:

    SQL Server catalog views for constraints:

    翻译自: https://www.sqlshack.com/the-benefits-costs-and-documentation-of-database-constraints/

    数据库约束查找的约束

    展开全文
  • 目录 数据库备份 备份的应用场景 备份与还原的语句 备份格式: 还原格式: 备份操作: 还原操作 ...数据库约束 ...数据库约束的概述 ...约束的作用: ...约束种类: ...主键约束 ...唯一约束 ...什么是唯一约束...
  • MySQL约束课堂笔记

    2019-09-01 20:33:34
    文章目录学习目标DQL:查询语句排序单列排序组合排序聚合函数五个聚合函数语法分组having与where的...还原操作图形化界面备份与还原数据库表的约束 (重要)数据库约束的概述主键约束创建主键删除主键主键自增唯一约...
  • MYSQL查询与约束

    2021-07-31 09:29:20
    MySQL查询与约束 学习目标 能够使用SQL语句进行排序(掌握) 能够使用聚合函数(掌握) 能够使用SQL语句进行分组查询(掌握)(稍难) 能够完成数据的备份和恢复(无比简单) 能够使用SQL语句添加主键、外键、唯一、非空约束...
  • 浅谈约束理论

    千次阅读 2018-05-13 14:06:43
    先从下定义来谈什么是约束理论,约束理论(Theory of Constraints, TOC)是以色列物理学家、企业管理顾问戈德拉特博士(Dr.Eliyahu M.Goldratt)在他开创的优化生产技术(Optimized Production Technology,OPT)基础...
  • MySQL查询与约束

    2020-02-20 18:12:43
    能够使用SQL语句添加主键、外键、唯一、非空约束 能够说出多表之间的关系及其建表原则 能够理解三大范式 第1章 DQL语句 1.1 排序 通过ORDER BY子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库...
  • 约束
  • MySQL约束与设计

    千次阅读 2020-11-08 21:49:29
    1、DQL查询语句 1.1、排序 通过ORDER BY 子句,可以将查询的结果进行排序(排序只是显示方式,不会影响数据库数据的顺序) ...同时多个字段进行排序,如果第一个字段相等,则按第二个字段排序,以此类推。
  • 员工辅导-教练风格

    2020-12-13 13:34:20
    1、前言 优秀的管理者即是领导者又是激励者,即是约束者又是教练。 2、员工类型 意愿&技能的象限来分类 3、教练风格 推动式 引导式
  • 笔记27-MySQL约束

    2020-08-15 08:15:37
    约束 多表之间的关系 范式 数据库的备份和还原 DQL:查询语句 排序查询 语法: order by 子句 order by 排序字段1 排序方式1, 排序字段2 排序方法2… 排序方式 ASC : 升序,默认的 DESC :降序 注意: 如果...
  • MySQL表的约束

    2019-10-22 15:19:45
    这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。 2. 备份与还原的语句 备份格式: DOS 下,未登录的时候。这是一个可执行文件 exe,在 bin 文件夹 ...
  • 数据库表的约束 表关系的概念 数据库设计的范式 数据库备份和还原 数据库表的约束 对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建...
  • 数据库-MySQL约束-笔记

    2019-07-13 19:38:52
    MySQL约束 回顾 MySQL管理数据库 创建数据库 CREATE DATABASE IF NOT EXISTS 数据库名; 删除数据库 DROP DATABASE 数据库名; 表的管理 查看所有表 use 数据库名; 选中一个数据库 show tables; 创建表:student...
  • Mysql入门【Mysql约束

    2020-11-20 22:43:56
    能够使用SQL语句添加主键、外键、唯一、非空约束 能够说出多表之间的关系及其建表原则 1. DQL查询语句-条件查询 目标 能够掌握条件查询语法格式 讲解 前面我们的查询都是将所有数据都查询出来,但是有时候我们...
  • TOC瓶颈管理理论/约束理论

    万次阅读 2012-02-29 10:45:50
     约束理论(制约法) 指约束管理/ 约束理论 (theory of constraints ,TOC)。  简单的讲,约束理论是关于企业应作哪些变化以及如何最好地实现这些变化的理论。具体一些,约束理论是这样一套管理原则
  • 关键链项目管理(一) - TOC, 约束理论

    千次阅读 2012-10-24 00:59:09
    最终的约束定位和解决问题有决定性的影响。 在定位短板时,可以运用鱼骨图或思维导图之类的工具辅助思考。对于如何定位核心问题,还是要靠自己的分析和验证。 总之TOC引导我们分析并回答...
  • 企业员工考勤管理子系统

    千次阅读 2017-11-16 12:30:55
    企业员工考勤管理子系统企业员工考勤管理子系统
  • 《MySQL初级系列教程-第二章-DQL、约束、表关系》 一、DQL查询语句 1.1 排序 准备数据: use db02; drop table if exists student ; CREATE TABLE student ( id int, -- 编号 `name` varchar(20), -- 姓名 ...
  • mysql基础,单表操作,约束介绍,事务介绍
  • MySQL单表&约束&事务

    2021-02-17 16:21:29
    DQL操作单表 排序 # 通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示效果,不会影响真实...# 组合排序,同时多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推 -- 组合排序 S
  • day27 MySQL 表的约束与数据库设计 第1节 回顾 1.1 数据库入门 1.1.1 SQL 语句的分类: 1) DDL 数据定义语言 2) DML 数据操作语言 3) DQL 数据查询语言 4) DCL 数据控制语言 1.1.2 MySQL 管理数据库  1. ...
  • 2.3 唯一约束 唯一约束的特点: 表中的某一列的值不能重复( null不做唯一的判断 ) 语法格式 字段名 字段值 unique 添加唯一约束 #创建emp3表 为ename 字段添加唯一约束 CREATE TABLE emp3( eid INT PRIMARY KEY ...
  • MySQL复杂查询、DCL、约束、表关系 回顾 能够使用SQL语句建库、建表 建库: CREATE DATABASE 数据库名; 建表: CREATE TABLE 表名 (字段名 字段类型, 字段名 字段类型); 能够使用SQL语句进行数据的添删改查操作 添...
  • sum(列名) 这一列求总和 使用count 对于null的记录不会统计,建议如果统计个数则不要使用可能为null的列,建议如果统计个数则不要使用有可能为null的列。 如果列名不为空,如果为null,则返回默认值。 查询id字段,...
  • MySQL的单表&约束&事务

    2021-02-08 10:34:20
    唯一约束 唯一约束的特点: 表中的某一列的值不能重复( null不做唯一的判断 ) 格式: 字段名 字段值 unique 示例: -- 创建 emp3表 为ename 字段添加 唯一约束 CREATE TABLE emp3( eid INT PRIMARY KEY , ename ...
  • 防止插入违反唯一约束While organizations are rushing to secure their networks and infrastructure in order to gain a robust security posture that allows them to meet regulations and protect their assets...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,712
精华内容 2,284
关键字:

对员工的约束措施