Q:Primary Key, Unique Key, Index区别
A:这3者都是Index,PK是unique和not null, UK是unique, Index就是平衡树(B树)了
Q:Cluster Index和Noncluster Index的区别是什么?
A:Cluster Index和Noncluster Index的结构都是平衡树 (B树)。它们的主要区别是Cluster Index的叶子节点是 DATA PAGE,而Noncluster Index的叶子节点是数据在DATA PAGE中的指针。
A:Cluster Index和Noncluster Index的结构都是平衡树 (B树)。它们的主要区别是Cluster Index的叶子节点是 DATA PAGE,而Noncluster Index的叶子节点是数据在DATA PAGE中的指针。
Q:
一个表里面有几个
Cluster Index
和
Noncluster Index?
A : CI 只有一个,因为 CI 决定了表的物理存贮结构, NCI 有无限个。
A : CI 只有一个,因为 CI 决定了表的物理存贮结构, NCI 有无限个。
Q:Replication种类和作用?
A:Transactional Replication: Publisher -> Subscriber. 每次Publisher的改动都会replicate到Subscriber.
A:Transactional Replication: Publisher -> Subscriber. 每次Publisher的改动都会replicate到Subscriber.
Merge Replication:Publisher <-> Subscriber. 每次Publisher/Subscriber的改动都会replicate到Subscriber/Publisher. Publisher保证数据唯一,不冲突。
Snapshot Replication:Publisher -> Subscriber. Publisher周期性的将最新的快照replicate到Subscriber。
Q:Subscription的种类?
A:Push subscription: Publisher -> Subscriber
A:Push subscription: Publisher -> Subscriber
Pull Subscription: Subscriber -> Publisher
Q:inner join, left join, right join, full join区别
A:
left outer join == left join
right outer join == right join
full outer join == full join
a表
1,'x'
2,'y'
3,'z'
b表
1,'a'
4,'b'
5,'c'
1,'x'
2,'y'
3,'z'
b表
1,'a'
4,'b'
5,'c'
CREATE TABLE [dbo].[a](
[id] [int] NOT NULL,
[name] [nchar](10) COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY]
[id] [int] NOT NULL,
[name] [nchar](10) COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY]
INSERT INTO [SF_GlobalData].[dbo].[a] VALUES (1, 'x')
INSERT INTO [SF_GlobalData].[dbo].[a] VALUES (2, 'y')
INSERT INTO [SF_GlobalData].[dbo].[a] VALUES (3, 'z')
CREATE TABLE [dbo].[b](
[id] [int] NOT NULL,
[name] [nchar](10) COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY]
[id] [int] NOT NULL,
[name] [nchar](10) COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY]
INSERT INTO [SF_GlobalData].[dbo].[b] VALUES (1, 'a')
INSERT INTO [SF_GlobalData].[dbo].[b] VALUES (4, 'b')
INSERT INTO [SF_GlobalData].[dbo].[b] VALUES (5, 'c')
inner join 内连接:
select a.id ,a.name,b.id,b.name from a,b
where a.id = b.id
1,'x',1,'a'
left outer join 左连接
select a.id ,a.name,b.id,b.name from a
left outer join b on a.id =b.id
1,'x',1,'a'
2,'y',null,null
3,'z',null,null
right outer join 右连接
select a.id ,a.name,b.id,b.name from a
right outer join b on a.id =b.id
1,'x',1,'a'
null,null,4,'b'
null,null,5,'c'
full outer join 外连接
select a.id ,a.name,b.id,b.name from a
full outer join b on a.id =b.id
1,'x',1,'a'
2,'y',null,null
3,'z',null,null
null,null,4,'b'
null,null,5,'c'
inner join 内连接:
select a.id ,a.name,b.id,b.name from a,b
where a.id = b.id
1,'x',1,'a'
left outer join 左连接
select a.id ,a.name,b.id,b.name from a
left outer join b on a.id =b.id
1,'x',1,'a'
2,'y',null,null
3,'z',null,null
right outer join 右连接
select a.id ,a.name,b.id,b.name from a
right outer join b on a.id =b.id
1,'x',1,'a'
null,null,4,'b'
null,null,5,'c'
full outer join 外连接
select a.id ,a.name,b.id,b.name from a
full outer join b on a.id =b.id
1,'x',1,'a'
2,'y',null,null
3,'z',null,null
null,null,4,'b'
null,null,5,'c'