有这样一个表,表结构为:
CREATE TABLE [dbo].[tb_ulog](
[VER] [varchar](2) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TIME] [varchar](8) COLLATE Chinese_PRC_CI_AS NOT NULL,
[NETID] [varchar](15) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MON] [decimal](2, 0) NULL DEFAULT (datepart(day,getdate())),
[SEC] [varchar](6) COLLATE Chinese_PRC_CI_AS NULL,
[USERID] [varchar](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TYPE] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[RIQI] [datetime] NULL DEFAULT (getdate()),
[USER_VER] [decimal](6, 0) NULL,
CONSTRAINT [PK_tb_ulog_01_01] PRIMARY KEY CLUSTERED
(
[TIME] ASC,
[NETID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]我想按照time的月份和日期来建立分区视图
我先建立了一组表 表名为dbo.tb_ulog_月份数_日期数
然后为每个月建立一个视图
create view [dbo].[view_ulog_01] as select * from dbo.tb_ulog_01_01 union all select * from dbo.tb_ulog_01_02 union all ...... union all select * from dbo.tb_ulog_01_31
建立出来的视图可以查询但是无法进行更新和删除,看网上说分区视图是可更新视图啊,是不是我建立视图的语句有问题呢,请指教~谢谢!
CREATE TABLE [dbo].[tb_ulog](
[VER] [varchar](2) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TIME] [varchar](8) COLLATE Chinese_PRC_CI_AS NOT NULL,
[NETID] [varchar](15) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MON] [decimal](2, 0) NULL DEFAULT (datepart(day,getdate())),
[SEC] [varchar](6) COLLATE Chinese_PRC_CI_AS NULL,
[USERID] [varchar](6) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TYPE] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[RIQI] [datetime] NULL DEFAULT (getdate()),
[USER_VER] [decimal](6, 0) NULL,
CONSTRAINT [PK_tb_ulog_01_01] PRIMARY KEY CLUSTERED
(
[TIME] ASC,
[NETID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]我想按照time的月份和日期来建立分区视图
我先建立了一组表 表名为dbo.tb_ulog_月份数_日期数
然后为每个月建立一个视图
create view [dbo].[view_ulog_01] as select * from dbo.tb_ulog_01_01 union all select * from dbo.tb_ulog_01_02 union all ...... union all select * from dbo.tb_ulog_01_31
建立出来的视图可以查询但是无法进行更新和删除,看网上说分区视图是可更新视图啊,是不是我建立视图的语句有问题呢,请指教~谢谢!
分区视图是提高查询性能的一个很好的办法
--看下面的示例 --示例表
create table tempdb.dbo.t_10(
id int primary key check(id between 1 and 10),name varchar(10)) create table pubs.dbo.t_20(
id int primary key check(id between 11 and 20),name varchar(10)) create table northwind.dbo.t_30(
id int primary key check(id between 21 and 30),name varchar(10))
go --分区视图
create view v_t
as
select * from tempdb.dbo.t_10
union all
select * from pubs.dbo.t_20
union all
select * from northwind.dbo.t_30
go --插入数据
insert v_t select 1 ,'aa'
union all select 2 ,'bb'
union all select 11,'cc'
union all select 12,'dd'
union all select 21,'ee'
union all select 22,'ff' --更新数据
update v_t set name=name+'_更新' where right(id,1)=1 --删除测试
delete from v_t where right(id,1)=2 --显示结果
select * from v_t
go --删除测试
drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
drop view v_t /**//*--测试结果 id name
----------- ----------
1 aa_更新
11 cc_更新
21 ee_更新 (所影响的行数为 3 行)
==*/
CHECK (substring(CONVERT([varchar],[TIME],0),(7),(8))='01') 即time最后两位数要为指定日期
是不是一定要把check条件用between限制在某范围才行呢,而且你的例子里的表主键是id字段,而我的表的主键是time,netid两个字段的复合主键,是否两个字段都要做check约束呢,还望指教~谢谢!
substring(CONVERT([varchar],[TIME],0),(7),(8))='01'
等于day(TIME)=1 这个不?