ANSI JOIN 의이해와 copy_t, copy_ymd 테이블을활용한쿼리 개발능력향상하기 이종인 엔코아컨설팅
이주제를이해하는데필요한지식 기본적인 T-SQL 작성기술 JOIN의기본적인이해 100 개념및 소개수준 200 중간수준 300 고급수준 400 전문가수준 200 에 Level 200 선수지식 불필요 100에더하여기술적세부사항설명 더하여능숙한사용경험, 아키텍처 지식필요
강사소개 [ 경력 ] 엔코아컨설팅책임컨설턴트 ( 현 ) 온디멘드수석컨설턴트다우교육원 SQL Server 전임강사 [ 자격증 ] MCT, MCSE+Internet, MCDBA, MCITP, OCP, HPCP-Master ASE [ 활동커뮤니티 ] SQL Specialist, PASS Korea
대상기술범위: ANSI JOIN 의처리방법이해 T-SQL 활용
목차 ANSI-JOIN 의처리절차와수행결과알아보기 copy_t 테이블을이용한 T-SQL 쿼리활용 copy_ymd ymd 테이블을이용한 T-SQL 쿼리활용 정리
ANSI JOIN 데이터베이스호환성수준 90 에서는 ANSI JOIN 만지원 Oracle 도 9i 에서부터 ANSI JOIN 지원 코드작성의효율성과직관적해석가능 기존 T SQL JOIN 과 PL/SQL JOIN 과의차이에대한 기존 T-SQL JOIN 과 PL/SQL JOIN 과의차이에대한올바른이해가필요함
ANSI JOIN 의논리적수행순서 5 1 2 3 4
ANSI LEFT OUTER 조인결과 1 Customer SalesOrderHeader 2 On JOIN 조건절 c.customerid=oh.customerid NULL WHERE 절조건 Od OrderDate >= '2003-01-01' NULL 4 TerritoryID ='4' 3 OUTER 테이블
ANSI LEFT OUTER 조인결과 2 Customer SalesOrderHeader NULL 2 On JOIN 조건절 c.customerid=oh.customerid oh.orderdate>= '2003-01-0101 01' TerritoryID ='4' NULL 모두 ON 절에 JOIN 의조건인경우 3 OUTER 테이블
ANSI LEFT OUTER 조인결과 3 Customer SalesOrderHeader 2 WHERE 조건절 c.customerid*=oh.customerid AND c.territoryid ='4 AND oh.orderdate >='2003 2003-01-0101 01' NULL NULL ON 조건절 c.customerid=oh.customerid AND oh.orderdate >='2003-01-01' WHERE 조건절 c.territoryid ='4' 3 OUTER 테이블 T-SQL JOIN 과같은결과를반환하는 ANSI LEFT OUTER JOIN
T-SQL JOIN vs. ANSI JOIN INNER JOIN 은 ON 조건과 WHERE 조건이동일한결과반환 INNER JOIN 은 ON 조건과 WHERE 조건이동일한결과반환 OUTER JOIN 은 ON 절에지정하는경우와 WHERE 절에지정하는경우가반환결과가달라지므로 JOIN 의논리적인순서를반드시숙지
Demo 1. ANSI JOIN 의올바른이해 ANSI LEFT OUTER JOIN 의수행순서이해 T-SQL JOIN과의비교
쿼리작성단계 1. 작성하고자하는쿼리요구사항에대한명확한분석 2. 요구사항에필요한테이블선별 3. 필요한내장함수선별 필요한경우사용자정의함수작성 4. 쿼리작성에필요한작업단계확인및지정 반환컬럼, 검색조건, 조인여부등 5. 쿼리작성 6. 데이터유효성검증 7. 성능효율성검증 액세스경로, 조인수행방법, 부분범위처리여부, 인덱스유효성확인 8. 응용프로그램에반영
copy_ t 테이블이란? 하나의데이터세트를여러개로복제하여 원하는결과를얻기위해가공하기위한장치 기본테이블과 CROSS JOIN 1996년대용량데이터베이스솔루션 ( 이화식저 ) 에서소개 (1992 년부터활용 ) 하여 Oracle 사용자에게는이미널리알려진활용방법 BUT, SQL Server 사용자에게는 Inside SQL Server 2005 Querying (Itzik 저 ) 에서 2006 년소개 (nums 테이블 )
copy_ t 테이블만들기 기존의사용자테이블을이용하여필요한행수만큼기존의사용자테이블을이용하여필요한행수만큼복제해서생성
쿼리작성전에고민 ( 분석 ) 하자: 배열처리 날아온배열값을어떻게행으로변환할까? 각배열값의구별자는? 각배열값의시작위치확인 charindex 내장함수를써볼까? Loop 를돌려볼까? copy_t를활용해볼까?
쿼리작성전에분석정리 배열값구별자위치확인 HOW charindex() 내장함수를사용하여각셀의종료값추출 언제까지반복해야하나? WHAT 배열값을 copy_t 를사용하여복제? Loop 로작성해볼까? 정합성검토 성능검토
Demo copy_t 테이블만들기와활용 copy_t 테이블만들기 copy_t 테이블활용
copy_ymd 무엇에쓰는물건인고? copy_t 테이블의변형으로날짜데이터세트처리용 기본테이블의날짜범위값으로 Between JOIN 범위내에포함되는날짜수확인 휴일여부도포함하면더욱다양하게활용가능
copy_ymd 를활용한이력관리 사원번호프로젝트명프로젝트시작일자 프로젝트 종료일자 투입율 101 AA 20070101 20070430 50 101 BB 20061108 20070223 50 102 AA 20070110 99991231 80 102 CC 20070401 99991231 20 103 AA 20070101 99991231 30 103 BB 20061201 20070308 30 103 CC 20070208 20070520 40 104 BB 20060910 99991231 100 2007 년 5 월 1 일프로젝트에투입된사람과프로젝트명은? 사원별로프로젝트에투입된공수를중복과휴일을제외하고알고자한다면? 이때필요한건뭐? SPEED? 아니져 copy_ymd? 마쑤미다..
쿼리작성전고민 ( 분석 ) : 이력관리 2007 년 5 월 1 일프로젝트에투입된사람과프로젝트명은? 사원별로프로젝트에투입된기간 ( 일자 ) 을휴일을제외하거나중복을고려해서구하고자한다면? 두가지요구사항모두copy_ymd가필요한가? 언제 copy_ymdymd 가필요한가? 선분에서특정점의위치확인은? 투입기간은휴일포함? 일자중복포함? datediff() 로가능? CASE 문도필요?
Demo copy_ymdymd 테이블만들기와활용 copy_ymd 테이블만들기 copy_ymd 테이블활용
copy_ t, copy_ymd 주의사항 약좋다고남용말고약모르고오용말자!!! 데이터베이스엔진은발전하는것임을잊지말자!!! 어제의최상의 Query 가오늘의최상의 Query 가아닐수도있다. 액세스경로, 쿼리분리 부분범위처리 인라인뷰 ( 서브쿼리 ) 이용 사용자정의스칼라함수이용
세션요약 T-SQL 쿼리도하나의작은프로젝트다!!! T-SQL 쿼리의논리적인처리순서를잊지말자!!! copy_t, copy_ymd, ymd 부분범위처리 의개념은이미 10여년전에정립된기술, 이론!!! 가장중요한것은데이터의정합성!!! 데이터의정합성을점검한뒤에는성능점검을잊지말자!!! 약좋다고남용말고약모르고오용말자!!!
참고자료 Ⅰ 참고서적 대용량데이터베이스솔루션 Ⅰ,Ⅱ ( 이화식著 ) 새로쓴대용량데이터베이스솔루션 Ⅰ( 이화식著 ) Inside SQL Server 2005 Querying (Itzik 외著 ) www.en-core.com www.sqlexpert.co.kr www.insidestsql.com
참고자료 Ⅱ 관련교육 대용량데이터베이스솔루션 For MS-SQL MS-SQL SQL 사용자를위한 SQL 활용 www.en-core.com/academy
IT 전문가를위한마이크로소프트리소스 TechNet 웹사이트 : http://www.microsoft.com/korea/technet TechNet 세미나및웹캐스트 : http://www.microsoft.com/korea/technet/events TechNet 뉴스레터 : http://www.microsoft.com/korea/technet/flash TechNet Plus Subscription : http://www.microsoft.com/korea/technet/subscriptions TechNet 커뮤니티 : http://www.microsoft.com/korea/technet/membership TechNet 매거진 : http://www.microsoft.com/technet/technetmag TechNet Virtual Lab : http://www.microsoft.com/technet/virtuallab com/technet/virtuallab 고객지원포털 : http://support.microsoft.com/default.aspx/gp/supportportal/ko 묻고답하기 (Q&A 게시판 ) : http://www.microsoft.com/korea/communities/webforum/webforum2.mspx 기술지원및계약문의 : 1577-9700
SQL Server 관련유용한웹사이트 SQL Server Product Homepage SQL Server Tech Center SQL Server Developer Center SQL Server Support Center SQL Server Download Center SQL Server Resource Center SQL Server Newsgroup http://www.microsoft.com/korea/sql http://www.microsoft.com/korea/technet/prodtechnol/sql http://www.microsoft.com/korea/msdn/sql com/korea/msdn/sql http://www.support.microsoft.com/ph/2855 http://www.microsoft.com/downloads/browse.aspx?displaylang=ko&productid= 261BA873-F3AB-420E-96D6-E3004596A551 http://www.microsoft.com/korea/sql/prodinfo/sql2005_resources.mspx http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx SQL Server Community http://www.microsoft.com/korea/communities/related/windows_server_communi /k / iti / t i d i Sites ty.mspx#server Microsoft Partner Portal http://partner.microsoft.co.kr/pds/mp_pds.asp
SQL Server 3 rd Party Tools
SQL Server 포켓가이드 < 완간 > SQL Server 2005 데이터통합가이드 < 근간 > SQL Server 2005 고가용성가이드 SQL Server 2005 튜닝가이드 SQL Server 2005 트러블슈팅가이드
SQL Server 상업용서적
SQL Server Product Homepage 어디일까요? http://www.microsoft.com/korea/sql
SQL Server Tech Center http://www.microsoft.com/korea/technet/prodtechnol/sql
SQL Server 개발자센터 http://www.microsoft.com/korea/msdn/sql
SQL Server Support Center http://www.support.microsoft.com/ph/2855
SQL Server Download Center http://www.microsoft.com/downloads/browse.aspx?displaylang=ko&productid=261ba873-f3ab-420e-96d6-e3004596a551
SQL Server 각종자료 http://www.microsoft.com/korea/sql/prodinfo/sql2005_resources.mspx
Microsoft Partner Portal http://partner.microsoft.co.kr/pds/mp_pds.asp
SQL Server Newsgroup g p http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
SQL Server Community Sites http://www.microsoft.com/korea/communities/related/windows_server_community.mspx#server SQL Server 관련국내사이트 SQL Server 관련해외사이트 www.sqlleader.com www.sqler.pe.kr www.mssql.org www.sqlworld.pe.kr www.olapforum.com www.devpia.com www.mcpworld.com www.dbguide.net www.databaser.net www.analysisservice.net net www.sqlservercentral.com www.sqljunkies.com www.sql-server-performance.com www.microsoft-oracle.com