김연욱님에 심심풀이 1탄

심심풀이 퀴즈(?) - 내용 1차수정(3,4번 추가)

1. 문자컬럼에다가 숫자컬럼을 비교하면 인덱스를 타는가 ?

2. 숫자컬럼에다가 문자컬럼을 비교하면 인덱스를 타는가 ?

3. 문자컬럼에다가 datetime컬럼을 비교하면 인덱스를 타는가 ?

4. datetime컬럼에다가 문자컬럼을 비교하면 인덱스를 타는가 ?


밑에 결과 보지말구 그냥 5초만 생각보세요.

중수이상 되시는 분은 빨랑 백스페이스를 눌러서 나가주세용.

/* =============================================================================== */
--tempdb에서 테스트
use tempdb
go
/* =============================================================================== */
--있으면 지우고
drop table a
go
--테스트용 테이블 하나 만들고
create table a(id int identity, cha varchar(10))
go
--역시나 테스트용 데이터 넣구
set nocount on
go
declare @i int
set @i = 1
while (@i <= 10000)
begin
insert into a(cha) values (convert(varchar(10), @i))
set @i = @i + 1
end
go
set nocount off
go
--인덱스 만들구
create index idx on a (cha)
create index idx2 on a (id)
go

--테스트를 위해 io량을 보고, 실행계획을 보기위해 설정
set statistics io on
set statistics profile on
go

--어떤경우에 인덱스를 쓸까 ?
--1. 그냥한번 풀로 읽어보면
select * from a
  |--Table Scan(OBJECT:([tempdb].[dbo].[a]))
'a' 테이블. 스캔 수 1, 논리적 읽기 수 28

--2. 정상적으로 컬럼형식을 맞춰서 검색하면 ?
select * from a where id = 1
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx2]), SEEK:([a].[id]=Convert([@1])) ORDERED
FORWARD)
'a' 테이블. 스캔 수 1, 논리적 읽기 수 3

select * from a where cha='1'
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx]), SEEK:([a].[cha]='1') ORDERED FORWARD)
'a' 테이블. 스캔 수 1, 논리적 읽기 수 3
--제대로 3페이지만 인덱스를 사용하여 검색

--3. 그럼 숫자컬럼에다가 문자를 비교하면 ?
select * from a where id = '1'
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[a]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[a].[idx2]), SEEK:([a].[id]=Convert([@1])) ORDERED
FORWARD)
'a' 테이블. 스캔 수 1, 논리적 읽기 수 3

--4. 문자컬럼에다가 숫자를 비교하면 ?
select * from a where cha=1
  |--Table Scan(OBJECT:([tempdb].[dbo].[a]), WHERE:(Convert([a].[cha])=Convert([@1])))
'a' 테이블. 스캔 수 1, 논리적 읽기 수 28
--웬 테이블 스캔....

생각하신대로 결과가 나왔나요 ?
/* =============================================================================== */
그럼 이번에는 많이 사용하는 datetime 컬럼을 테스트해보자.

--다른 샘플을 만들기 위해 잠시 off시키고
set statistics io off
set statistics profile off
go

--있으면 지우고
drop table b
go
--테스트용 테이블 하나 만들고
create table b(dt1 datetime, dt2 char(8))
go
--역시나 테스트용 데이터 넣구
set nocount on
go
declare @i datetime
set @i = '2000-01-01'
while (@i <= '2003-12-31')
begin
insert into b values (@i, convert(varchar(8), @i, 112))
set @i = dateadd(dd, 1, @i)
end
go
set nocount off
go
--인덱스 만들구
create index idx on b (dt1)
create index idx2 on b (dt2)
go

--테스트를 위해 io량을 보고, 실행계획을 보기위해 설정
set statistics io on
set statistics profile on
go

--어떤경우에 인덱스를 쓸까 ?
--1. 그냥한번 풀로 읽어보면
select * from b
  |--Table Scan(OBJECT:([tempdb].[dbo].[b]))
'b' 테이블. 스캔 수 1, 논리적 읽기 수 5

--2. 정상적으로 컬럼형식을 맞춰서 검색하면 ?
declare @dt1 datetime
set @dt1 = '2003-05-05'
select * from b where dt1 = @dt1
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx]), SEEK:([b].[dt1]=[@dt1]) ORDERED FORWARD)
'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

declare @dt2 char(8)
set @dt2 = '20030505'
select * from b where dt2 = @dt2
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx2]), SEEK:([b].[dt2]=[@dt2]) ORDERED FORWARD)
'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

--3. datetime 컬럼에다가 문자를 비교하면 ?
declare @dt2 char(8)
set @dt2 = '20030505'
select * from b where dt1 = @dt2
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[b]))
       |--Index Seek(OBJECT:([tempdb].[dbo].[b].[idx]), SEEK:([b].[dt1]=Convert([@dt2])) ORDERED
FORWARD)
'b' 테이블. 스캔 수 1, 논리적 읽기 수 3

--4. 문자컬럼에다가 datetime을 비교하면 ?
declare @dt1 datetime
set @dt1 = '2003-05-05'
select * from b where dt2 = @dt1
  |--Table Scan(OBJECT:([tempdb].[dbo].[b]), WHERE:(Convert([b].[dt2])=[@dt1]))
'b' 테이블. 스캔 수 1, 논리적 읽기 수 5
--웬 테이블 스캔....

생각하신대로 결과가 나왔나요 ?

/* =============================================================================== */
결론, 문자대 숫자의 비교는 무조건 문자를 숫자로 변환후, 또는 문자를 datetime형으로 변환후 
비교할려구 한다.(자동형변환에 의하야 - 무조건 형식을 &#47583;춰서 사용해야하겠네요.)

프로그램 개발시 주의하세요 !!!