[database] SQL index

 

begin transaction;

--- 50000개의 숫자 생성후 cross join으로 모든 조합 더 생성
begin try
    with Number as (
        select top 50000 ROW_NUMBER() over(order by (select null)) as n
        from sys.objects a
        cross join sys.objects b
        cross join sys.objects c
    )
    insert into student1 (student_id, name, dept_name, tot_cred)
	select
		'S' + right('00000' + cast(n as varchar(5)), 6),    -> student_id
		'Student_' +cast(n as varchar(10)),   -> name
		case                  ->dept_name
			when n % 10 = 0 then 'Computer Science'
			when n % 10 = 1 then 'Biology'
			when n % 10 = 2 then 'Electrical Engineering'
			when n % 10 = 3 then 'Mechanical Engineering'
			when n % 10 = 4 then 'Physics'
			when n % 10 = 5 then 'Chemistry'
			when n % 10 = 6 then 'Mathematics'
			when n % 10 = 7 then 'History'
			when n % 10 = 8 then 'Economics'
			else 'English'
		end,
			(n % 121) + 30  -> tot_cred
	from Number

	commit transaction;
print 'succesfully inserted 50,000 records into student1 table'
end try

--- 트랜잭션 실패 시
begin catch
    rollback transaction
    print 'error inserting record' + Error_Message();
end catch
Go

 

대규모 데이터 생성

 

declare @StartTime datetime, @Endtime datetime, @duration int

print 'Running query without index...'	
set @StartTime = getdate()

select *
from student1
where dept_name = 'Physics'

set @Endtime = GETDATE()
set @duration = DATEDIFF(millisecond, @starttime, @endtime)
print 'Query without index completed in ' + cast(@duration as varchar(10)) + 'ms'
go


---인덱싱 안한 시간 

create index ix_student_dept_name1 on student1(dept_name);

declare @starttime datetime, @endtime datetime, @duration int;
print 'running with index...'
set @starttime = getdate()

select *
from student1
where dept_name = 'Physics'

set @Endtime = GETDATE()
set @duration = DATEDIFF(millisecond, @starttime, @endtime)
print 'Query with index completed in ' + cast(@duration as varchar(10)) + 'ms'
go

---인덱싱 한 시간

 

인덱싱을 했을 때와 안 했을 때 비교

 

인덱싱 구문

create index ix_student_dept_name1 on student1(dept_name);

---데이터베이스에서 특정 컬럼의 검색 속도를 높이기 위한 자료구조예요.
---마치 책의 목차처럼, 원하는 데이터를 빠르게 찾을 수 있게 도와줍니다.

 

인덱싱 삭제

if exists (
    select name 
    from sys.indexes 
    where name = 'idx_course_dept_name' 
      and object_id = object_id('course')
)
    drop index idx_course_dept_name on course;
    
    
 --- 시스템 뷰에 저장되기 때문에 조건은 시스템뷰로 하고
 --- 삭제는 실제 table에서 함