荣耀彩票代理

IT技术互动交流平台

SQL Server聚焦计算列持久化(二十一)

作者:JeffckyWang  来源:IT165收集  发布日期:2016-12-16 20:35:34

前言

荣耀彩票代理SHANGYIJIEWOMENJIESHULEHash Match AggregateHEStream AggregateDEJIANGJIE,BENXILIEWOMENLAIJIANGJIANGGUANYUSQL ServerZHONGDEJISUANLIEWENTI,JIANDUANDENEIRONG,SHENRUDELIJIE,Always to review the basics。

初探计算列持久化(Compued Column Persisted)

荣耀彩票代理ZAISQL Server 2005JIUYINRULEJISUANLIE,WOMENSHOUXIANSHAOWEIKANXIAZAImsdnGUANYUJISUANLIEDEDINGYI:JISUANLIEYOUKEYISHIYONGTONGYIBIAOZHONGDEQITALIEDEBIAODASHIJISUANDELAI。BIAODASHIKEYISHIFEIJISUANLIEDELIEMING、CHANGLIANG、HANSHU,YEKEYISHIYONGYIGEHUODUOGEYUNSUANFULIANJIEDESHANGSHUYUANSUDERENYIZUHE。BIAODASHIBUNENGWEIZICHAXUN。SHIJISHANGJIUSHIWEILEDINGYIYIGELIELAIDUIQITALIELAIJINXINGJISUANKEYISHILIEMING、HANSHUDENG,NEIMETADESHIYONGCHANGJINGSHISHENMENI?XIAMIANWOMENSHOUXIANLAIJUGELIZI。DANGXUYAODAOCHUYIXIEZHISHI,CISHIZHEIXIEZHIXUYAOTONGGUOJISUANCAINENGBEIDAOCHU,TONGSHINI,YOUYIXIELIEHAIYILAIYULINGWAIDEYILIEHUOZHEGENGDUOLIE,RUGUOYIGELIEJINXINGLEGENGXINZEQIYILAIDELIEBIXUTONGBUJINXINGGENGXIN,SHANGSHUCHANGJINGTONGGUODUIYIGELIEHUOZHEDUOGELIEJINXINGJISUAN,CISHIWOMENXUYAODINGYIYIGEJIANGYIGELIEHUOZHEDUOGELIEJINXINGJISUANDEDAODEZHIDELIE,ZHEIJIUSHIJISUANLIE。WOMENLAIKANYIGEDIANXINGDELIZI,ZAIYIGEGONGSISHANGBANDESUOYOUYUANGONG,ZAIGONGSINEIBUXITONGZHONGHUICUNQISUOYOUYUANGONGDEXINXI,BIRUYUANGONGBIANHAO、CHUSHENGRIQIDENG,RUGUOCISHIWOMENXUYAODAOCHUYUANGONGDETUIXIURIQINI,JIASHEZAIZHONGGUOXIANZAINANXINGTUIXIUSHIJIANWEI60NIANHOU,CISHIWOMENXUYAOTONGGUOCHUSHENGRIQISUANCHU60NIANHOUDERIQI,YEJIUSHUOZAIBIAOZHONGHAIXUYAODINGYIYIGETUIXIURIQILIE。XIAMIANWOMENCHUANGJIANBIAOLAIKANKANJISUANLIE。

USE TSQL2012
GO

CREATE TABLE Employee
(
    employeeNumber INT NOT NULL, --员工编号
    employeeBirth DATETIME NOT NULL, --出生日期
    employeeRetirement AS (DATEADD(YEAR, 60, (employeeBirth)-(1))) PERSISTED --退休日期
)

CISHIWOMENKANDAOBIAOZHONGGUANYUTUIXIURIQIDESHEJI,XIANSHIQIYIJINGSHICHIJIUHUALEDE

荣耀彩票代理JIEXIALAIWOMENCHARUCESHISHUJUKANKAN

USE TSQL2012
GO

INSERT INTO dbo.Employee( employeeNumber, employeeBirth )
SELECT 305423 ,'1985-12-13' UNION ALL
SELECT 587650 ,'1989-11-18' UNION ALL
SELECT 221836 ,'1990-01-19' UNION ALL
SELECT 746104 ,'1993-06-13' UNION ALL
SELECT 139024 ,'1995-07-23' 

RANHOUWOMENLAICHAXUNBIAO

USE TSQL2012
GO

SELECT *
FROM dbo.Employee

荣耀彩票代理CISHIWOMENTONGGUOCHAXUNGUYUANBIAODEDAOQIMEIGEGUYUANDETUIXIURIQI,DAOZHEILISHIMEISHENMEWENTIDE,JIRANWOMENSHEZHITASHICHIJIUHUADE,YEJIUSHUODANGQITALIEFASHENGGAIBIANSHIJISUANLIEYEHUIDUIYINGFASHENGGAIBIAN,TURANYOUYITIANBIANHAOWEI305423DEGUYUANHELURUXINXIDETONGSHIJIAOLIU,TAQISHISHI1986NIANCHUSHENGDE,SHANGMIANDE1985NIANSHISHENFENZHENGSHANGDE,SHENFENZHENGGAOCUOLE,CISHIWOMENXUYAOGENGXINQICHUSHENGRIQIDAO1986NIAN,RUXIA

UPDATE dbo.Employee SET employeeBirth = '1986-12-13' WHERE employeeNumber = '305423'

荣耀彩票代理JIEXIALAIWOMENZAILAICHAXUNSHUJUKANKAN。

CISHIWOMENFAXIANDANGCHUSHENGRIQIFASHENGXIUGAISHI,QIDUIYINGDEJISUANLIEYEJINXINGLETONGBUYOUYUANLAIDE2045GENGXINDAOLE2046,SHANGSHUWOMENTIANJIAZAIJISUANLIEZHONGTIANJIALEPersistedGUANJIANZI,SHIBUSHIYINWEITIANJIAZHEIGEGUANJIANZIDAOZHICHIJIUHUACONGERDANGYIGELIEJINXINGGENGXINSHI,JISUANLIEYEJIUTONGBUGENGXINLENI,NANDAOZHEIJIUSHIPersistedCHIJIUHUADEZUOYONGMA,SHIJIQINGKUANGBUSHIZHEIYANGDE,DANGNIQUDIAOPersistedGUANJIANZICISHIYEHUIJINXINGTONGBUGENGXIN(BUXINNIKEYISHISHI),NEIMEPersistedGUANJIANZIDEZUOYONGSHISHENMENI?SHISHIQINGKUANGSHIZHEIYANGDE,DANGWOMENZAILIESHANGCHUANGJIANLEJISUANLIESHI,CISHIJISUANCHULAIDESHUJUBINGMEIYOUCUNZAILIEZHONG(ZHIYUCUNZAINALIWOYEBUZHIDAO),JISUANDESHUJUSHIZAIYUNXINGSHIJISUANCHULAIDE,DANGYONGPersistedGUANJIANZIBIAOSHIJISUANLIEZHIHOU,ZHEIGESHIHOUCAISHIJIANGJISUANJIEGUOCUNZAIBIAOZHONGJISUANLIESHANG。JIXUWANGXIAKANSHUJUCUNCHUKONGJIANSHIYONGQINGKUANGJIUKEYIDEDAOYANZHENG。

通过计算列持久化进一步探讨数据存储空间

荣耀彩票代理XIAMIANWOMENLAIKANKANDANGWEITIANJIAJISUANLIE、TIANJIAJISUANLIE、JISUANLIECHIJIUHUASHIBIAOSHUJUCUNCHUKONGJIANQINGKUANG。XIAMIANWOMENLAICHUANGJIANCESHIBIAO

USE TSQL2012
GO

CREATE TABLE [dbo].[ComputeColumn] 
(
ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100)
)
GO

荣耀彩票代理ZAIBIAOZHONGCHARU10WANTIAOSHUJU

INSERT INTO [ComputeColumn] (ID,FirstName,LastName)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

CISHIWOMENLAIKANKANYOUGUANBIAOCUNCHUKONGJIANSHIYONGQINGKUANG

USE TSQL2012
GO

sp_spaceused '[ComputeColumn]'
GO

SHANGSHUWOMENDEZHICUNCHUSHUJUWEI2680KB,XIAMIANWOMENZAILAICHUANGJIANJISUANLIEKANKAN。

ALTER TABLE dbo.[ComputeColumn] ADD
FullName AS (FirstName+' '+LastName)
GO

荣耀彩票代理CONGZHEILIWOMENKEYIDECHUDANGCHUANGJIANJISUANLIESHIQISHUJUGENBENMEIYOUCUNZAILIESHANG,WOMENZAILAIKANKANTIANJIACHIJIUHUAGUANJIANZISHIQINGKUANGYOUSHIZENYANGDENI

ALTER TABLE dbo.[ComputeColumn] ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED
GO

DANGTIANJIACHIJIUHUAGUANJIANZISHICISHIBIAOCUNCHUSHUJUKONGJIANBIANWEILE4784KB,DAOCIYANZHENGLEDANGWEITIANJIAPersistedGUANJIANZISHI,ZAIJISUANLIESHANGDESHUJUGENBENMEIYOUCUNZAILIESHANGERSHIZAIYUNXINGSHIJINXINGLEJISUAN,DANGYONGPersistedGUANJIANZIBIAOSHIJISUANLIESHICISHISHUJUCAICUNZAILIESHANG。

通过计算列持久化深入探讨数据存储空间

WOMENZHIDAORUGUODUILIECHUANGJIANSUOYINDEHUAKENDINGXUYAOYIDINGKONGJIANLAICUNCHUSUOYIN,SHANGSHUWOMENDUILIEJINXINGLECHIJIUHUA,CISHIHUIZENGJIABIAOCUNCHUKONGJIAN,YAOSHIWOMENCHUANGJIANSUOYINSHIBUSHIHUIZENGJIABIAOSHUJUCUNCHUKONGJIANDAXIAONI?WOMENZAIWEICHUANGJIANJISUANLIEQIANXIANCHUANGJIANSUOYINKANKANQIBIAOZHONGGEZHONGSHUJUKONGJIANCUNCHUDAXIAO,JIZAICHUANGJIANDELIEFullNameSHANGCHUANGJIANSUOYIN。

USE TSQL2012
GO

CREATE NONCLUSTERED INDEX idx_comCol_FullName
ON dbo.ComputeColumn (FullName)

YINWEICHUANGJIANLESUOYIN,SUOYIZHISHIDAOZHISUOYINKONGJIANBIANDALE,XIAMIANWOMENZAICHUANGJIANJISUANLIECHIJIUHUABINGKANKANQIBIAOKONGJIANSHIYONGQINGKUANG

USE TSQL2012
GO

ALTER TABLE dbo.ComputeColumn ADD
FullName_P AS (FirstName+' '+LastName) PERSISTED

荣耀彩票代理CONGSHANGWOMENKEYIKANDAOZENGJIASUOYINWEIDAOZHIBIAOSHUJUDAXIAODEZENGJIA,ERCHUANGJIANJISUANLIECHIJIUHUAZEXUYAOEWAIDEKONGJIAN。FENXIDAOZHEILIWEIZHI,WOMENLAIJICHUYIGEJIBENJIELUN:

荣耀彩票代理JISUANLIEFENXIJIELUN:JISUANLIEDEYONGTUZHUYAOYONGYUDUOGEJISUANBINGQIEBIJIAOFUZADEJISUAN,RUGUODUIJISUANLIEJINXINGCHIJIUHUASUIRANNENGGOUDADAJIANSHAOJISUANKAIXIAODANSHITAHUIEWAIZENGJIACIPANKONGJIAN。

总结

荣耀彩票代理BENJIEWOMENXUEXILEJISUANLIEYIJIJIANGQICHIJIUHUADEJICHUNEIRONG,XIAYIJIEWOMENJIANGJIANGGUANYUJISUANLIEYIJIJISUANLIECHIJIUHUADEXINGNENGWENTI,JIANDUANDENEIRONG,SHENRUDELIJIE,WOMENXIAJIEZAIHUI。 

Tag标签:      
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规