반응형
- /**********************************************************************************************
-- Title : [2k5] 포함된 열이 있는 인덱스(ROW INCLUDED INDEX)
-- Reference : mcpworld.com
-- Key word : row include index
**********************************************************************************************/
use adventureworks;
go
if objectproperty(object_id('address_test'), 'isusertable') = 1
drop table address_test;
go
select * into address_test from person.address;
go
alter table address_test
add constraint pk_address_test primary key clustered (addressid);
go
create nonclustered index ix_address_postalcode
on dbo.address_test (postalcode)
include (addressline1, addressline2, city, stateprovinceid);
go
select addressline1, addressline2, city, stateprovinceid, postalcode
from dbo.address_test
where postalcode between n'98000' and n'99999';
go
-- included column 용도 확인
create procedure get_index_columns (@object sysname, @index sysname = null)
as
declare @oid int
select @oid = object_id(@object)
select c.name as [column],
case ic.is_included_column when 0 then 'key'
else 'included'
end as [column usage]
from sys.index_columns ic join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
join sys.indexes i
on i.object_id = ic.object_id
and i.index_id = ic.index_id
where i.object_id = @oid and i.name = @index
and i.index_id > 0
go
exec dbo.get_index_columns 'dbo.address_test','ix_address_postalcode';
go
exec sp_columns address_test;
go
alter table address_test
alter column addressline1 nvarchar(100) not null;
go
반응형