SQL server 2005 index

USE MASTER
DROP DATABASE MYDB00
CREATE DATABASE MYDB00

USE MyDB00
GO
SELECT USERID, USERNAME, ADDR1, AGE, SEX, JOB INTO mytable00
FROM testdb.dbo.고객정보
GO
SELECT*FROM MYTABLE00

create index mytable00_index_age
on mytable00 (age)

select*from mytable00 where age = 50

drop index mytable00.mytable00_index_age

exec sp_helpindex mytable00

use mydb00
go
create table mytable01
(
 num   int identity(1,1)    not null primary key,
 username  varchar(10)    not null
)
go
exec sp_helpindex mytable01
exec sp_help mytable01

alter table mytable01
drop constraint PK__mytable01__7D78A4E7
go

create clustered index mytable01_clindex_username
  on mytable01 (username)
go
exec sp_helpindex mytable01
exec sp_help mytable01

insert mytable01 values('이동희')
insert mytable01 values('김예린')
insert mytable01 values('김은희')
insert mytable01 values('김동훈')
select*from mytable01

drop index mytable01.mytable01_clindex_username
go
exec sp_helpindex mytable01

insert mytable01 values('허준')
insert mytable01 values('김미영')
select*from mytable01  ->

create clustered index mytable01_clindex_username
  on mytable01(username)
go
exec sp_helpindex mytable01
select*from mytable01

create nonclustered index mytable00_nclindex_idname
  on mytable00(userid, username)
exec sp_helpindex mytable00

select userid, username from mytable00

drop index mytable00.mytable00_nclindex_idname
go
exec sp_helpindex mytable00

create nonclustered index mytable00_nclus_username
  on mytable00(username)
exec sp_helpindex mytable00
select*from mytable00

select*from mytable00 where username='강태임'

create clustered index mytable00_clus_useid
 on mytable00 (userid)
go
exec sp_helpindex mytable00
select*from mytable00

drop index mytable00.mytable00_clus_useid
drop index mytable00.mytable00_nclus_username
go
exec sp_helpindex mytable00

create table mytable04
(
 userid   varchar(10) primary key,
 username  varchar(10),
 jumin  varchar(13),
 addr  varchar(20),
 age  int
)
go
create unique nonclustered index mytable04_unique_jumin
  on mytable04 (jumin)
go
exec sp_helpindex mytable04
exec sp_help mytable04
insert mytable04 values('A1234','김에린','0008294123457','서울시 마포구 도화동',2)
select * from mytable04
insert mytable04 values('B1234','김은희','0008294123456','서울시 영등포구',32)
delete mytable04 where userid ='A1234'

insert mytable04 values('c1234','김은히',default,'서울시 영등포구',32)
insert mytable04 values('c1234','김은흐',default,'서울시 영등포구',32)

drop index mytable04.mytable04_unique_jumin
exec sp_helpindex mytable04

insert mytable04 values('d1234','김은흐','0008294123456','서울시 영등포구',32)
select * from mytable04

create unique nonclustered index mytable04_unique_jumin
  on mytable04 (jumin)

use mydb00
select * from mytable04
select jumin from mytable04 group by jumin
 having count(jumin) > 1

group by....having문==================================================================
use testdb
select * from 고객정보

select addr1,count(*) 인원수 from 고객정보
group by addr1
having count(*)>30

select addr1 from 고객정보
group by addr1
having count(*)>30

select job,count(*) 인원수 from 고객정보
group by job
================================================================================

select userid, username, addr1, age, sex, job into mytable05
from testdb..고객정보
where 0=1
select * from mytable05

create unique nonclustered index mytable05_nclus_username
   On mytable05 (username)
exec sp_helpindex mytable05

select userid, username, addr1, age, sex, job from testdb..고객정보
where addr1 like '제주%'
 order by username

insert mytable05
select userid, username, addr1, age, sex, job from testdb..고객정보
where addr1 like '제주%'
 order by username
select*from mytable05  

drop index mytable05.mytable05_nclus_username
go
create unique nonclustered index mytable05_nclus_username
  on mytable05 (username)
 with ignore_dup_key
exec sp_helpindex mytable05

insert mytable05
select userid, username, addr1, age, sex, job from testdb..고객정보
where addr1 like '제주%'
 order by username
select*from mytable05  

drop index mytable05.mytable05_nclus_username
exec sp_helpindex mytable05

create clustered index mytable05_clus_username
  on mytable05 (username)
go
select*from mytable05
sp_help  mytable05

drop index mytable05.mytable05_clus_username
create clustered index mytable05_clus_username
  on mytable05 (username desc)
go
select*from mytable05
sp_help  mytable05

use mydb00
go
create table mytable06
(
 num int not null,        
 message char(2011) not null
)

declare @ncount  int
set @ncount=1
while @ncount<=4
begin
 insert mytable06 values(@ncount,'test message...')
 set @ncount=@ncount+1
end
select*from mytable06

dbcc showcontig (mytable06)

create clustered index mytable06_clus_num
  on mytable06(num)
with
 fillfactor = 100            
go                                
dbcc showcontig (mytable06)

drop index mytable06.mytable06_clus_num
go
create clustered index mytable06_clus_num
 on mytable06(num)
with
 fillfactor = 50              
go
dbcc showcontig (mytable06)

insert mytable06 values(5,'test message....')
dbcc showcontig (mytable06)

insert mytable06 values(2,'test message....')
dbcc showcontig (mytable06)

delete mytable06
declare @ncount int
set @ncount=0
while @ncount < 16
begin
 set @ncount=@ncount+2
 insert mytable06 values(@ncount,'test massage.....')
end
go
select*from mytable06

drop index mytable06.mytable06_clus_num
go
create clustered index mytable06_clus_num
 on mytable06(num)
with
 fillfactor = 100
dbcc showcontig (mytable06)

insert mytable06 values(5,'test message....')
insert mytable06 values(13,'test message....')
go
dbcc showcontig (mytable06)

delete mytable06
declare @ncount int
set @ncount=0
while @ncount < 16
begin
 set @ncount=@ncount+2
 insert mytable06 values(@ncount,'test massage.....')
end
go
select*from mytable06
drop index mytable06.mytable06_clus_num
create clustered index mytable06_clus_num
 on mytable06(num)
with
 fillfactor = 50
dbcc showcontig (mytable06)
insert mytable06 values(1,'test message....')
insert mytable06 values(7,'test message....')
insert mytable06 values(13,'test message....')
insert mytable06 values(15,'test message....')
dbcc showcontig (mytable06)

delete mytable06

declare @ncount int
set @ncount=0
while @ncount < 80
begin
 set @ncount=@ncount+2
 insert mytable06 values(@ncount,'test massage.....')
end
go
select * from mytable06
dbcc showcontig (mytable06)
drop index mytable06.mytable06_clus_num
create clustered index mytable06_clus_num
 on mytable06(num)
with
 fillfactor = 100
dbcc showcontig (mytable06)

delete mytable06
declare @ncount int
set @ncount=3
while @ncount < 80
begin
 insert mytable06 values(@ncount,'test massage.....')
 set @ncount=@ncount+8
end
go
dbcc showcontig (mytable06)

dbcc indexdefrag (mydb00,mytable06,mytable06_clus_num)
dbcc showcontig (mytable06)          
40+10=50/4=12.2=13

dbcc dbreindex ('mydb00.dbo.mytable06', mytable06_clus_num,50)
dbcc showcontig (mytable06)

dbcc dbreindex (mytable06,'',100)
go
dbcc showcontig (mytable06)

sp_help mytable06
--또는
sp_helpindex mytable06
--또는
select*from sysindexes

sp_helpconstant

use mydb00
go
select indexproperty(object_id('mytable06'),
'mytable06_clus_num','indexfillfactor')
dbcc dbreindex('mydb00.dbo.mytable06',mytable06_clus_num, 20)
dbcc dbreindex ('mydb00.dbo.mytable06', mytable06_clus_num,50)
dbcc dbreindex ('mydb00.dbo.mytable06', mytable06_clus_num,100)
dbcc showcontig (mytable06)

create clustered index mytable06_clus_num
 on mytable06 (num)
with drop_existing

drop index mytable06.mytable06_clus_num

index.... 예를 들어 회원가입시에 id중복 확인에 많이 쓰인다
select id from 회원 where id ='textbox1.text'
select 컬럼명, 컬럼명 from table명 where 컬럼명(+index) = 값