#眉標=SQL Server 2008、DBA、B-tree
#副標=實作資料庫系統調校（6）
#大標=資料庫索引概念和設計
#作者=文／圖 陳泓翔



========程式1 <BOX>==============
--create test table
create table test1
(c1 int null,
c2 varchar(10) null)
--insert data
declare @i int
set @i=1
while @i<2016
begin
insert into test1 values(@i,null); 
set @i=@i+1;
end
--create nonclustered without compression
create nonclustered index nidx_1 on test1
(c1)
========<END>==============




========程式2 <BOX>==============
SELECT o.name,
  ips.index_id,
  ips.partition_number,
  ips.index_type_desc,
  ips.record_count, ips.avg_record_size_in_bytes,
  ips.min_record_size_in_bytes,
  ips.max_record_size_in_bytes,
  ips.page_count, ips.compressed_page_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'demotest'), OBJECT_ID(N'demotest.dbo.test1'), NULL, NULL, 'DETAILED') ips
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY index_id asc,record_count DESC ;
========<END>==============



========程式3 <BOX>==============
--create nonclustered with compression
create nonclustered index nidx_2 on test1
(c1)
WITH ( DATA_COMPRESSION = PAGE ) ;
========<END>==============



========程式4 <BOX>==============
SQL Statement:
select t1.POLICY_NO,t1.POLICY_TYPE
from bill_test t1
wheret1.POLICY_NO='AYBF315610'
========<END>==============



========<反灰>==============
create nonclustered index nidx_1 on bill_test
(POLICY_NO )
========<END>==============



========<反灰>==============
dbcc freeprocCache
dbcc dropcleanbuffers
========<END>==============





========程式5 <BOX>==============
SQL Statement:
select t1.ORG_COLLECT_ID,t1.NEW_COLLECT_ZONE,t1.NEW_COLLECT_ID
from bill_test t1
where t1.ORG_COLLECT_ID='G22068742201'
========<END>==============




========<反灰>==============
create nonclustered index nidx_2 on bill_test
(ORG_COLLECT_ID asc )
include 
(NEW_COLLECT_ZONE,NEW_COLLECT_ID)
========<END>==============




========<反灰>==============
dbcc freeprocCache
dbcc dropcleanbuffers
========<END>==============





========程式6 <BOX>==============
SQL Statement:
select t1.BILL_NO,t1.CANCEL_REASON,t1.CANCEL_DATE
from bill_test t1
where t1.CANCEL_REASON IS NOT NULL
========<END>==============




========<反灰>==============
CREATE NONCLUSTERED INDEX nidx_3 on bill_test
(CANCEL_REASON )
include
(CANCEL_DATE)
========<END>==============




========<反灰>==============
drop index nidx_3 on bill_test
CREATE NONCLUSTERED INDEX nidx_3 on bill_test
(CANCEL_REASON )
include
(CANCEL_DATE)
where CANCEL_REASON IS NOT NULL
========<END>==============




========<反灰>==============
dbcc freeprocCache
dbcc dropcleanbuffers
========<END>==============






========程式7 <BOX>==============
SQL Statement:
select t1.ORG_COLLECT_ID,t1.BILL_TYPE,t2.OWNER_ID
from bill_test t1
join blxx_test t2
on t1.ORG_COLLECT_ID=t2.ORG_COLLECT_ID 
where t2.MODX=12
========<END>==============




========<反灰>==============
create nonclustered index nidx_1 on blxx_test
(ORG_COLLECT_ID asc )
include 
(OWNER_ID)

create nonclustered index nidx_2 on blxx_test
(MODX asc )
include 
(OWNER_ID)

create nonclustered index nidx_4 on bill_test
(ORG_COLLECT_ID asc )
include 
(BILL_TYPE)
========<END>==============




========<反灰>==============
dbcc freeprocCache
dbcc dropcleanbuffers
========<END>==============





========<反灰>==============
create nonclustered index nidx_5 on bill_test
(OWNER_ID desc)
include
(CENTER_CODE,PCPO_NO)
========<END>==============



========程式8 <BOX>==============
SQL Statement:
select t1.OWNER_ID,t1.CENTER_CODE
from bill_test t1
where t1.OWNER_ID like 'A%'
order by t1.OWNER_ID desc 
========<END>==============