-
MSSQL 함수 및 잡다한 것 모아둔거카테고리 없음 2024. 1. 23. 11:36336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.
@@@@@@@@@@@@#######################################
DB프로시져에서 엔터키 지정
char(13) + char(10)
&&&&&&&&&&&&&&&&&& IF EXISTS(
<25가지 SQL작성법> 오라클 기준으로 하되, MSSQL에서도 적용됩니다.
1.데이터와 비즈니스 어플리케이션을 잘 알아야 한다.
동일한 정보는 다른 비즈니스 데이터 원천으로부터 검색될 수 있다. 이러한 원천
에 익숙해야 한다. 당신은 당신의 데이터베이스 안의 데이터의 크기와 분포를 반
드시 알아야 한다. 또한 SQL을 작성하기 전에 비즈니스 개체 안의 관계와 같은
데이터 모델을 전체적으로 이해해야 한다. 이러한 이해는 당신이 여러 테이블에
서 정보를 검색하는데 있어서 보다 좋은 쿼리를 작성할 수 있다. DESIGNER/2000
과 같은 CASE TOOLS은 다른 비즈니스와 데이터베이스 객체사이의 관계를 문서화
하는데 좋은 역할을 한다.
2.실제 데이터를 가지고 당신의 쿼리를 검사하라.
대부분의 조직은 개발, 검사, 제품의 3가지 데이터베이스 환경을 가진다. 프로그
래머는 어플리케이션을 만들고 검사하는데 개발 데이터베이스 환경을 사용하는
데, 이 어플리케이션이 제품 환경으로 전환되기 전에 프로그래머와 사용자에 의
해 검사 환경하에서 보다 엄격하게 검토되어야 한다.
SQL이 검사 환경하에서 테스트될 때, 검사 데이터베이스가 가지고 있는 데이터
는 제품 데이터베이스를 반영해야 한다. 비실제적인 데이터를 가지고 테스트된
SQL문은 제품 안에서는 다르게 작동할 수 있다. 엄격한 테스트를 보장하기 위해
서는, 검사 환경하에서의 데이터 분포는 반드시 제품 환경에서의 분포와 밀접하
게 닮아야 한다.
3.동일한 SQL을 사용하라.
가능한한 BIND VARIABLE, STORED PROCEDURE, PACKAGE의 이점을 활용하라. IDENTICAL
SQL문의 이점은 PARSING이 불필요하기에 데이터베이스 서버안에서 메모리 사용
의 축소와 빠른 수행을 포함한다. 예로서 아래의 SQL 문은 IDENTICAL하지 않다.
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 20;
그러나 I_EMPID라고 이름 주어진 BIND VARIABLE을 사용하면 SQL 문은 이렇게 된
다.
SELECT * FROM EMPLOYEE WHERE EMPID = :I_EMPID;
4.주의 깊게 인덱스를 사용하라.
테이블상에 모든 필요한 인덱스는 생성되어야 한다. 하지만 너무 많은 인덱스는
성능을 떨어뜨릴 수 있다. 그러면 어떻게 인덱스를 만들 칼럼을 선택해야 하는
가?
*최종 사용자에 의해 사용되는 어플리케이션 SQL과 쿼리의 WHERE 절에서 빈번
하게 사용되는 칼럼에 인덱스를 만들어야 한다.
*SQL 문에서 자주 테이블을 JOIN하는데 사용되는 칼럼은 인덱스되어야 한다.
*같은 값을 가지는 ROW가 적은 비율을 가지는 칼럼에 인덱스를 사용하라.
*쿼리의 WHERE 절에서 오직 함수와 OPERATOR로 사용되는 칼럼에는 인덱스를 만들
면 안된다.
*자주 변경되거나 인덱스를 만들때 얻는 효율성보다 삽입, 갱신, 삭제로 인해 잃는
효율성이 더 큰 칼럼에는 인덱스를 만들면 안된다. 이러한 OPERATION은 인덱스를
유지하기 위한 필요 때문에 느려진다.
*UNIQUE 인덱스는 더 나은 선택성 때문에 NONUNIQUE 인덱스보다 좋다. PRIMARY
KEY 칼럼에 UNIQUE 인덱스를 사용한다. 그리고 FOREIGN KEY 칼럼과 WHERE 절
에서 자주 사용되는 칼럼에는 NONUNIQUE 인덱스를 사용한다.
5.가용한 인덱스 PATH를 만들어라
인덱스를 사용하기 위해서는 기술한 SQL문을 이용할 수 있는 식으로 SQL을 작
성하라. OPTIMIZER는 인덱스가 존재하기 때문에 인덱스를 사용하는 ACESS PATH
를 사용할 수 없다. 따라서 ACCESS PATH는 반드시 SQL이 사용할 수 있게 만들
어 져야 한다. SQL HINT를 사용하는 것은 인덱스 사용을 보증해주는 방법중 하
나이다. 특정 ACCESS PATH를 선택하기 위한 다음의 힌트를 참고 하라
6.가능하면 EXPLAIN과 TKPROF를 사용하라
만약 SQL문이 잘 다듬어지지 않았다면 비록 오라클 데이터베이스가 잘 짜여져
있어도 효율성이 떨어질 것이다. 이럴 경우 EXPLAIN TKPROF에 능숙해져야 한
다. EXPALIN PLAN은 SQL이 사용하는 ACCESS PATH를 발견할 수 있게 해주고
TKPROF는 실제 PERFORMANEC의 통계치를 보여준다. 이 TOOL은 오라클 서버 소
프트웨어에 포함되어 있고 SQL의 성능을 향상시켜 준다.
7.OPTIMIZER를 이해하라.
SQL은 RULE-BASED나 COST-BASED중 하나를 이용해서 기동된다.기존의 소
프트웨어는 RULE BASED 방식을 채택하고 있다. 그리고 많은 오라클 소프트웨
어가 이러한 방식을 오랫동안 사용해 왔다. 그러나 새로 출시된 소프트웨어에 대
해서는 COST BASED 방식의 OPTIMIZER를 고려해야 한다. 오라클은 새로 출
시되는 프로그램을 COST BASED방식으로 업그레이드 시켜왔으며 이러한 방식
은 시스템을 훨씬 더 안정적으로 만들었다. 만약 COST BASED방식의
OPTIMIZER를 사용한다면 반드시 ANALYZE 스키마를 정기적으로 사용해야 한
다. ANALYZE스키마는 데이터베이스 통계를 데이터 사전 테이블에 기록하는 역
할을 수행하며 그렇게 되면 COST BASED OPTIMIZER가 그것을 사용하게 된
다. SQL은 COST BASED OPTIMIZER를 사용할 때만 잘 조정될 수 있다. 만약
RULE BASED에서 COST BASED로 바꾸고 싶다면 데이터베이스를 사용하는 모
든 소프트웨어의 모든 SQL문의 성능을 평가해 보아야 한다.
8.지엽적으로 동작하더라도 전역적으로 생각하라
항상 주의할 것은 하나의 SQL문을 조정하기 위해 생긴 데이터베이스안의 변화
는 다른 응용프로그램이나 다른 사용자가 이용하는 다른 명령문에 영향을 미친다
는 사실이다.
9.WHERE절은 매우 중요하다.
비록 인덱스가 가용하다고 해도 다음의 WHERE 절은 그 인덱스 ACCESS PATH
를 사용하지 않는다.(즉 COL1 과 COL2는 같은 테이블에 있으며 인덱스는
COL1에 만들어진다.)
COL1 > COL2
COL1 < COL2
COL1 > = COL2
COL1 <= COL2
COL1 IS NULL
COL1 IS NOT NULL.
인덱스는 NULL값을 갖는 칼럼에는 ROWID를 저장하지 않는다. 따라서 NULL값
을 갖는 ROW를 검색할 때는 인덱스를 사용하지 못한다.
COL1 NOT IN (VALUE1, VALUE2 )
COL1 != EXPRESSION
COL1 LIKE ''%PATTERN''.
이럴 경우 THE LEADING EDGE OF THE INDEX(?) 는 작동되지 않고 인덱스가 사
용되지 못하게 한다. 한편 COL1 LIKE ''PATTERN %''이나 COL1 LIKE ''PATTERN %
PATTERN%'' 는 한정된 인덱스 스캔을 수행하기 때문에 인덱스를 사용할 수 있다.
NOT EXISTS SUBQUERY
EXPRESSION1 = EXPRESSION2.
인덱스된 컬럼을 포함하는 표현(EXPRESSION), 함수, 계산(CALCULATIONS)은 인덱스
를 사용하지 못한다. 다음의 예에서 보면 UPPER SQL 함수를 사용하면 인덱스
스캔을 사용할 수 없고 FULL TABLE SCAN으로 끝나고 만다.
SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) LIKE ''SALES%'';
10.레코드 필터링을 위해서는 HAVING보다는 WHERE를 사용하라
인덱스가 걸려있는 칼럼에는 GROUP BY와 같이 HAVING절을 사용하지 마라. 이 경
우 인덱스는 사용되지 않는다. 또한 WHERE절로 된 ROW를 사용하지 마라. 만약
EMP테이블이 DEPTID컬럼에 인덱스를 가지고 있다면 다음 질의는 HAVING 절을
이용하지 못한다.
SELECT DEPTID,
SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;
그러나 같은 질의가 인덱스를 사용하기 위해 다시 씌여질 수 있다.
SELECT DEPTID,
SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;
11. WHERE 절에 선행 INDEX 칼럼을 명시하라.
복합 인덱스의 경우, 선행 인덱스가 WHERE절에 명시되어 있다면 쿼리는
그 인덱스 를 사용할 것이다. 다음의 질의는 PART_NUM과 PRODUCT_ID 칼럼
에 있는 PRIMARY KEY CONSTRAINT에 기초한 복합 인덱스를 이용할 것이다.
SELECT *
FROM PARTS
WHERE PART_NUM = 100;
반면, 다음의 쿼리는 복합인덱스를 사용하지 않는다.
SELECT *
FROM PARTS
WHERE PRODUCT_ID = 5555;
같은 요청(REQUEST)이 인덱스를 이용하기 위해 다시 씌어 질 수 있다. 다음 질의
의 경우, PART_NUM컬럼은 항상 0 보다 큰 값을 가질것이다.
SELECT *
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;
12.인덱스 SCAN과 FULL TABLE SCAN을 평가하라.
한 행(ROW)의 15% 이상을 검색하는 경우에는 FULL TABLE SCAN이 INDEX
ACESS PATH보다 빠르다. 이런 경우, SQL이 FULL TABLE SCAN을 이용할 수 있도록
여러분 스스로 SQL을 작성하라. 다음의 명령문은 비록 인덱스가 SALARY
COLUMN에 만들어져 있어도 인덱스 SCAN을 사용하지 않을 것이다. 첫 번째 SQL
에서, FULL HINT를 사용한다면 오라클은 FULL TABLE SCAN을 수행할 것이다. 인덱
스의 사용이 나쁜 점이 더 많다면 아래의 기술을 이용해서 인덱스 수행을 막을
수 있다.
SELECT * --+FULL
FROM EMP
WHERE SALARY = 50000;
SELECT *
FROM EMP
WHERE SALARY+0 = 50000;
다음의 명령문은 비록 인덱스가 SS# COLUMN에 있어도 인덱스 SCAN을 사용하
지 않을 것이다.
SELECT *
FROM EMP
WHERE SS# || '' '' = ''111-22-333'';
오라클이 불분명한 데이터 변환을 수행해야 하는 경우 인덱스가 항상 사용되지
않는 것은 아니다. 다음의 예를 보면, EMP 칼럼에 있는 SALARY는 숫자형 칼
럼이고 문자형이 숫자값으로 변환된다.
SELECT *
FROM EMP
WHERE SALARY = ''50000'';
테이블의 행이 15%이거나 그보다 작을 경우 인덱스 스캔은 보다 잘 수행 될 것
이다. 왜냐 하면 인덱스 스캔은 검색된 행(ROW)하나 하나 마다 다중의 논리적인
읽기 검색(READ)을 할 것이기 때문이다. 그러나 FULL TABLE SCAN은 하나의 논리적
인 읽기 검색 영역 안의 BLOCK에 있는 모든 행들을 읽을 수 있다. 그래서 테이
블의 많은 행들에 접근해야 하는 경우에는 FULL TABLE SCAN이 낫다. 예로 다음의
경우를 보자. 만약 EMP TABLE과 그 테이블의 모든 인덱스에 대해 ANALYZE라
는 명령어가 수행된다면, 오라클은 데이터 사전인 USER_TABLES와
USER_INDEXES에 다음과 같은 통계치를 산출해 낸다.
TABLE STATISTICS:
NUM_ROWS = 1000
BLOCKS = 100
INDEX STATISTICS:
BLEVEL = 2
AVG_LEAF_BLOCKS_PER_KEY = 1
AVG_DATA_BLOCKS_PER_KEY = 1
이러한 통계치 에 근거해서, 아래에 보이는 것이 각각의 다른 SCAN에 대한 논리
적인 읽기(READ)-즉 ACESS된 BLOCK이 될 것이다.
USE OF INDEX TO RETURN ONE ROW = 3
(BLEVEL+(AVG_LEAF_BLOCKS_PER_KEY - 1) +
AVG_DATA_PER_KEY
FULL TABLE SCAN = 100
(BLOCKS)
USE OF INDEX TO RETURN ALL ROWS = 3000
(NUM_ROWS * BLOCKS ACCESSED TO RETURN ONE ROW USING INDEX)
13. 인덱스 스캔에 ORDER BY를 사용하라
오라클의 OPTIMIZER는 , 만약 ORDER BY라는 절이 인덱스된 칼럼에 있다면 인
덱스 스캔을 사용할 것이다. 아래의 질의는 이러한 점을 보여 주는 것인데 이 질
의는 비록 그 칼럼이 WHERE 절에 명시되어 있지 않다고 해도 EMPID컬럼에 있
는 가용한 인덱스를 사용할 것이다. 이 질의는 인덱스로부터 각각의 ROWID를
검색하고 그 ROWID를 사용하는 테이블에 접근한다.
SELECT SALARY
FROM EMP
ORDER BY EMPID;
만약 이 질의가 제대로 작동하지 않는다면, 당신은 위에서 명시되었던 FULL HINT
를 사용하는 같은 질의를 다시 작성함으로써 다른 대안들을 이용해 볼 수 있다.
14. 자신의 데이터를 알아라
내가 이미 설명한 것처럼, 당신은 당신의 데이터를 상세하게 알고 있어야 한다.
예를 들어 당신이 BOXER라는 테이블을 가지고 있고 그 테이블이 유일하지 않은
인덱스를 가진 SEX라는 컬럼과 BOXER_NAME이라는 두 개의 테이블을 가지고 있
다고 가정해 보자. 만약 그 테이블에 같은 수의 남자, 여자 복서가 있다면 오라
클이 FULL TABLE SCAN을 수행하는 경우 다음의 질의가 훨씬 빠를 것이다.
SELECT BOXER_NAME
FROM BOXER
WHERE SEX = ''F'';
당신은 다음과 같이 기술함으로써 질의가 FULL TABLE SCAN을 수행하는지를 확실
하게 해 둘 수 있다.
SELECT BOXER_NAME --+ FULL
FROM BOXER
WHERE SEX = ''F'';
만약 테이블에 980 명의 남성 복서 데이터가 있다면, 질의는 인덱스 SCAN으로
끝나기 때문에 아래형식의 질의가 더 빠를 것이다.
SELECT BOXER_NAME --+ INDEX (BOXER BOXER_SEX)
FROM BOXER
WHERE SEX = ''F'';
이 예는 데이터의 분포에 대해 잘 알고 있는 것이 얼마나 중요한 가를 예시해 준
다. 데이터가 많아지고(GROW) 데이터 분포가 변화하는 것처럼 SQL 도 매우 다
양할 것이다. 오라클은 OPTIMIZER 가 테이블에 있는 데이터의 분포를 잘 인식하
고 적절한 실행 계획을 선택하도록 하기 위해 오라클 7.3 에 HISTOGRAMS라는
기능을 추가했다.
15. KNOW WHEN TO USE LARGE-TABLE SCANS.
작거나 큰 테이블에서 행들을 추출할 때, 전체 테이블의 검색은 인텍스를 사용한
검색보다 성능이 더 좋을 수도 있다. 매우 큰 테이블의 인덱스 검색은 수많은
인덱스와 테이블 블록의 검색이 필요할수도 있다. 이러한 블록들이 데이터베이
스 버퍼 캐쉬에 이동되면 가능한한 오래도록 그곳에 머무른다. 그래서 이러한
블록들이 다른 질의등에 필요하지 않을 수도 있기 때문에, 데이터베이스 버퍼 히
트 비율이 감소하며 다중 사용자 시스템의 성능도 저하되기도 한다. 그러나 전
체 테이블 검색에 의해서 읽혀진 블록들은 데이터베이스 버퍼 캐쉬에서 일찍 제
거가 되므로 데이터베이스 버퍼 캐쉬 히트 비율은 영향을 받지 않게 된다.
16. MINIMIZE TABLE PASSES.
보통, SQL질의시 참조하는 테이블의 숫자를 줄임으로 성능을 향상시킨다. 참조
되는 테이블의 숫자가 적을수록 질의는 빨라진다. 예를 들면 NAME, STATUS,
PARENT_INCOME, SELF_INCOME의 네개의 컬럼으로 이루어진 학생 테이블
에서 부모님에 의존하는 학생과 독립한 학생의 이름과 수입에 대해서 질의시, 이
학생 테이블을 두번 참조하여 질의하게 된다..
SELECT NAME, PARENT_INCOME
FROM STUDENT
WHERE STATUS = 1
UNION
SELECT NAME, SELF_INCOME
FROM STUDENT
WHERE STATUS = 0;
( NAME이 프라이머리 키이며, STATUS는 독립한 학생의 경우는 1, 부모님에
의존적인 학생은 0으로 표시한다)
위의 같은 결과를 테이블을 두번 참조하지 않고도 질의 할 수 있다.
SELECT NAME,PARENT_INCOME*STATUS + SELF_INCOME(1-STATUS)
FROM STUDENT;
17. JOIN TABLES IN THE PROPER ORDER.
다수의 테이블 조인시 테이블들의 조인되는 순서는 매우 중요하다. 전반적으로,
올바른 순서로 테이블이 조인되었다면 적은 수의 행들이 질의시 참조된다. 언제
나 다수의 조인된 테이블들을 질의시 우선 엄격하게 조사하여 행들의 숫자를 최
대한으로 줄인다. 이러한 방법으로 옵티마이저는 조인의 차후 단계에서 적은 행
들을 조사하게 된다. 뿐만 아니라, 여러 조인을 포함하는 LOOP JOIN에서는 가
장 먼저 참조되는 테이블(DRIVING TABLE)이 행들을 최소한으로 리턴하도록 해야
한다. 그리고, 마스터와 상세 테이블 조인시에는(예를 들면 ORDER & ORDER
LINE ITEM TABLES) 마스터 테이블을 먼저 연결 시켜야 한다.
규칙에 근거한 옵티마이저의 경우에는 FROM CLAUSE의 마지막 테이블이 NESTED
LOOP JOIN의 DRIVING TABLE이 된다. NESTED LOOP JOIN이 필요한 경우에는
LOOP의 안쪽의 테이블에는 인텍스를 이용하는 것을 고려할 만하다. EXPLAIN
PLAN과 TKPROF는 조인 타입, 조인 테이블 순서, 조인의 단계별 처리된 행들
의 숫자들을 나타낸다.
비용에 근거한 옵티마이저의 경우에는 WHERE CLAUSE에 보여지는 테이블의 순
서는 옵티마이저가 가장 최적의 실행 계획을 찾으려고 하는 것과 상관 없다. 조
인되는 테이블의 순서를 통제하기 위해서 ORDERED HINT를 사용하는 것이 낫다.
SELECT ORDERS.CUSTID, ORDERS.ORDERNO,
ORDER_LINE_ITEMS.PRODUCTNO --+ORDERED
FROM ORDERS, ORDER_LINE_ITEMS
WHERE ORDERS.ORDERNO = ORDER_LINE_ITEMS.ORDERNO;
18. USE INDEX-ONLY SEARCHES WHEN POSSIBLE.
가능하다면, 인덱스만을 이용하여 질의를 사용하라. 옵티마이저는 오직 인덱스만
을 찾을 것이다. 옵티마이저는 SQL을 만족시키는 모든 정보를 인덱스에서 찾을
수 있을 때, 인덱스만을 이용할 것이다. 예를들면, EMP테이블이 LANME과
FNAME의 열에 복합 인덱스를 가지고 있다면 다음의 질의는 인덱스만은 이용할
것이다.
SELECT FNAME
FROM EMP
WHERE LNAME = ''SMITH'';
반면에 다음의 질의는 인덱스와 테이블을 모두 참조한다.
SELECT FNAME , SALARY
FROM EMP
WHERE LNAME = ''SMITH'';
19. REDUNDANCY IS GOOD.
WHERE CLAUSE에 가능한한 많은 정보를 제공하라. 예를 들면 WHERE COL1 =
COL2 AND COL1 = 10이라면 옵티마이저는 COL2=10이라고 추론하지만,
WHERE COL1 = COL2 AND COL2 = COL3이면 COL1=COL3이라고 초론하지는
않는다.
20. KEEP IT SIMPLE, STUPID.
가능하면 SQL문을 간단하게 만들라. 매우 복잡한 SQL문은 옵티마이저를 무력
화시킬 수도 있다. 때로는 다수의 간단한 SQL문이 단일의 복잡한 SQL문보다
성능이 좋을 수도 있다. 오라클의 비용에 근거한 옵티마이저는 아직은 완벽하지
않다. 그래서 EXPLAIN PLAN에 주의를 기울여야 한다. 여기서 비용이란 상대적인
개념이기에 정확히 그것이 무엇을 의미하는지 알지 목한다. 하지만 분명한 것은
적은 비용이 보다 좋은 성능을 의미한다는 것이다.
종종 임시 테이블을 사용하여 많은 테이블들을 포함하는 복잡한 SQL 조인을 쪼
개는 것이 효율적일 수도 있다. 예를 들면, 조인이 대량의 데이터가 있는 8개의
테이블을 포함할 때, 복잡한 SQL을 두 세개의 SQL로 쪼개는 것이 낫을 수 있
다. 각각의 질의는 많아야 네개정도의 테이블들을 포함하며 그 중간 값을 저장
하는 것이 낫을 수 있다.
21. YOU CAN REACH THE SAME DESTINATION IN DIFFERENT WAYS.
많은 경우에, 하나 이상의 SQL문은 의도한 같은 결과를 줄 수 있다. 각각의
SQL은 다른 접근 경로를 사용하며 다르게 수행한다. 예를들면, MINUS(-) 산술
자는 WHERE NOT IN (SELECT ) OR WHERE NOT EXISTS 보다 더 빠르다.
예를들면, STATE와 AREA_CODE에 각각 다른 인덱스가 걸려 있다. 인덱스에
도 불구하고 다음의 질의는 NOT IN의 사용으로 인해 테이블 전체를 조사하게
된다.
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (''VA'', ''DC'', ''MD'')
AND AREA_CODE NOT IN (804, 410);
그러나 같은 질의가 다음 처럼 쓰여진다면 인덱스를 사용하게 된다
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (''VA'', ''DC'', ''MD'')
MINUS
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE AREA_CODE IN (804, 410);
WHERE절에 OR을 포함한다면 OR대신에 UNION을 사용할 수 있다. 그래서,
SQL 질의를 수행하기 전에 먼저 실행계획을 조심스럽게 평가해야 한다. 이러한
평가는 EXPLAIN PLAN AND TKPROF를 이용하여 할 수 있다.
22. USE THE SPECIAL COLUMNS.
ROWID AND ROWNUM 열을 이용하라. ROWID를 이용하는 것이 가장 빠르다.
예를들면, ROWID를 이용한 UPDATE는 다음과 같다.
SELECT ROWID, SALARY
INTO TEMP_ROWID, TEMP_SALARY
FROM EMPLOYEE;
UPDATE EMPLOYEE
SET SALARY = TEMP_SALARY * 1.5
WHERE ROWID = TEMP_ROWID;
ROWID값은 데이터베이스에서 언제나 같지는 않다. 그래서, SQL이나 응용 프
로그램이용시 ROWID값을 절대화 시키지 말라. 리턴되는 행들의 숫자를 제한
시키기위해 ROWNUM을 이용하라. 만약에 리턴되는 행들을 정확히 모른다면
리턴되는 행들의 숫자를 제한하기위해 ROWNUM을 사용하라
다음의 질의는 100개 이상의 행들을 리턴하지는 않는다.
SELECT EMPLOYE.SS#, DEPARTMENT.DEPT_NAME
FROM EMPLOYEE, DEPENDENT
WHERE EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
AND ROWNUM < 100;
23.함축적인 커서대신 명시적인 커서를 사용하라.
함축적 커서는 여분의 FETCH를 발생시킨다. 명시적 커서는 DECLARE, OPEN,
FETCH와 CLOSE CURSOR문을 사용하여 개발자에 의해서 생성된다. 함축 커서는
DELETE, UPDATE, INSERT와 SELECT문을 사용하면 오라클에 의해서 생성
된다.
24.오라클 병렬 쿼리 옵션을 찾아서 이용하라.
병렬 쿼리 옵션을 사용하면, 보다 빠른 성능으로 SQL을 병렬로 실행할 수 있다.
오라클 7에서는, 오직 FULL TABLE SCAN에 기반한 쿼리만이 병렬로 수행될 수 있다.
오라클 8에서는, 인덱스가 분할되어있다면 INDEXED RANGE SCANS에 기반한 쿼리도
병렬로 처리될 수 있다. 병렬 쿼리 옵션은 다수의 디스크 드라이버를 포함하는
SMP와 MPP SYSTEM에서만 사용될 수 있다.
오라클 서버는 많은 우수한 특성을 가지고 있지만, 이러한 특성의 존재만으로는
빠른 성능을 보장하지 않는다. 이러한 특성을 위해서 데이터베이스를 조정해야하
며 특성을 이용하기 위해 특별하게 SQL을 작성해야 한다. 예를 들면, 다음의
SQL은 병렬로 수행될 수 있다.
SELECT * --+PARALLEL(ORDERS,6)
FROM ORDERS;
25.네트웍 소통량을 줄이고 한번에 처리되는 작업량을 늘려라.
ARRAY PROCESSING과 PL/SQL BLOCK을 사용하면 보다 나은 성능을 얻을 수 있고
네트웍 소통량을 줄인다. ARRAY PROCESSING은 하나의 SQL문으로 많은 ROW를 처
리할 수 있게 한다. 예를 들면, INSERT문에서 배열을 사용하면 테이블내의
1,000 ROW를 삽입할 수 있다. 이러한 기술을 사용하면 주요한 성능 향상을 클라
이언트/서버와 배치시스템에서 얻어질 수 있다.
복합 SQL문은 과도한 네트웍 소통을 유발할 수 있다. 그러나 만일 SQL문이 단
일 PL/SQL 블록안에 있다면, 전체 블록은 오라클 서버에 보내져서 그곳에서 수
행되고, 결과는 클라이언트의 APPLICATION에게 돌아온다.
개발자와 사용자는 종종 SQL을 데이터베이스에서 데이터를 검색하고 전송하는
간단한 방법으로 사용한다. 때때로 직접적으로 SQL을 작성하지 않고 코드 발생
기를 사용하여 작성한 APPLICATION은 심각한 성능 문제를 일으킨다. 이러한 성능
감퇴는 데이터베이스가 커지면서 증가한다.
SQL은 유연하기 때문에, 다양한 SQL문으로 같은 결과를 얻을 수 있다. 그러나
어떤 문은 다른 것보다 더 효율적이다. 여기에 기술된 팁과 기법을 사용하면 빠
르게 사용자에게 정보를 제공할 수 있는 APPLICATION과 리포트를 얻을 수 있다.
$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#
$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#
$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#$#
요일별 레코드 검색
SELECT 컬럼 FROM 테이블 WHERE DatePart(w, 날짜컬럼) = 1(일요일) ~ 7(토요일)
ex) SELECT * FROM TempTable WHERE DatePart(w, Ddate) = 3
날짜는 Between 을 쓰셔서 두날짜사이의 값을 뽑아낼수 있구여
요일은 DataPart 함수를 써서 뽑아낼수 있습니다
SELECT *
FROM TableName
WHERE ( 날짜필드 BETWEEN '2002-09-22' AND '2002-09-25' )
AND ( DATEPART(weekday, News_Update) IN (2, 3, 4) )
마지막에 (DATEPART(weekday, News_Update) IN (2, 3, 4)) 에서
IN 안의 값은 1 - 7 까지값이 있고 1 이 일요일 입니다
select DATEADD ( dd , 1-DATEPART(dw, GETDATE()), convert(varchar(10),getDate(),111) )
,DATEADD ( dd , 7-DATEPART(dw, GETDATE()),convert(varchar(10),getDate(),111) )
입니다.
일요일과 토요일 을 구합니다.
1-DATEPART(dw, GETDATE()) 요부분과 7-DATEPART(dw, GETDATE()) 요부분에서 숫자를 수정하시면 다른 요일들도 구할수가 있네요
지정일자에 해당하는 주의 첫째날을 구하는 함수
CREATE FUNCTION DBO.FCN_GET_FIRSTDATE(@TODAY AS DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @FIRST_DATE VARCHAR(10)
SET @FIRST_DATE = LEFT( CONVERT(VARCHAR(14), DATEADD(D,(DATEPART(DW,@TODAY)* -
1)+1 , @TODAY),120),10)
RETURN @FIRST_DATE
END
요일 구하는 법
Select Case DatePart(dw,'2005/04/05') When 1 Then '일요일'
When 2 Then '월요일'
When 3 Then '화요일'
When 4 Then '수요일'
When 5 Then '목요일'
When 6 Then '금요일'
When 7 Then '토요일'
End
한주당 자료를 구해오는 SQL문
테이블명이라는 테이블에 상품명, price, 입력일 컬럼 있다고할때의 쿼리문입니다.
ceiling(convert(float, (datediff(dd, '2003-01-01', 입력일)+datepart(dw, '2003-01-01')))/7)은
몇주차인지를 구하는 방법입니다.
(날짜 + 그달의 첫일의 요일(일요일이면 1, 월요일이면 2, ....토요일이면 7) -1)/7을 해주면 그
달의 몇주차인지를 구할수 있습니다. datediff(dd, '2003-01-01', 입력일) 하면 날짜-1의 값이 나
옵니다.
더 효율적인 쿼리가 있을거라 생각됩니다. 더 깊이 생각해보시기 바랍니다.
select 상품명, sum(week1) '1주', sum(week2) '2주', sum(week3) '3주', sum(week4) '4주',
sum(week5) '5주', sum(week1+week2+week3+week4+week5) '월계'
from (select min(상품명) 상품명, sum(case when 주=1.0 then price
else 0
end) week1,
sum(case when 주=2.0 then price
else 0
end) week2,
sum(case when 주=3.0 then price
else 0
end) week3,
sum(case when 주=4.0 then price
else 0
end) week4,
sum(case when 주=5.0 then price
else 0
end) week5
from (select 상품명, price, 입력일,
ceiling(convert(float, (datediff(dd, '2003-01-01', 입력일)
+datepart(dw, '2003-01-01')))/7) 주
from 테이블명
where datediff(mm, '2003-01-01', 입력일)=0) x
group by 상품명, 주) x
group by 상품명
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
mssql - 테이블 컬럼정보 | Oracle 2004/10/12 11:19
http://blog.naver.com/hero/80006526426
SELECT A.NAME AS '테이블 이름',
B.NAME AS '컬럼 이름',
C.NAME AS '컬럼 타입',
B.LENGTH AS '컬럼길이(OR 기본 BYTES수)',
MAX(CASE WHEN B.COLID = F.COLID THEN 'Y'
ELSE 'N' END) AS 'PRIMARY KEY 여부',
CASE WHEN B.COLSTAT = 1 THEN 'Y'
ELSE 'N' END AS 'IDENTITY 여부',
CASE WHEN B.ISNULLABLE = 1 THEN 'Y'
ELSE 'N' END AS 'NULL값 허용 여부',
CASE WHEN ISNULL(SUBSTRING(D.TEXT,2,LEN(D.TEXT)-2),'') = '' THEN 'N'
ELSE 'Y' END AS 'DEFAULT값 유무 여부',
D.TEXT AS 'DEFAULT값',
B.COLORDER AS '테이블에서의 컬럼순서'
FROM DBO.SYSOBJECTS A,
DBO.SYSCOLUMNS B,
DBO.SYSTYPES C,
DBO.SYSCOMMENTS D,
DBO.SYSINDEXES E,
DBO.SYSINDEXKEYS F
WHERE A.XTYPE = 'U' --TABLE을 나타냄
AND A.UID = 1 --SA가 아닌 DB OWNER의 아이디값
AND B.ID = A.ID
AND C.XTYPE = B.XTYPE
AND C.XUSERTYPE = B.XUSERTYPE
AND D.ID =* B.CDEFAULT
AND E.ID = B.ID
AND E.INDID = 1
AND F.ID = E.ID
AND F.INDID = E.INDID
GROUP BY A.NAME,B.NAME,C.NAME,B.LENGTH,B.COLID,B.COLSTAT,B.ISNULLABLE,B.COLORDER,D.TEXT
ORDER BY A.NAME,B.COLORDER
위의 쿼리문을 이용하는데 제약조건이 있습니다.
우선 TABLE의 생성자가 SA가 아니라야 한다는 거죠.
SA이외의 DB OWNER를 만들어 놓으셨다면 그대로 이용하셔도 될듯 합니다.
그리고 특정 테이블 하나만 보는게 아니고 DB OWNER의 전체 테이블을 본다는거죠.
만약 SA로 테이블을 만드셨다면 WHERE 절을 약간 수정을 보셔야 하는데,
SA의 UID는 1 입니다.
그리고 특정 테이블을 검색 하시고 싶으시다면
WHERE 절에 A.NAME = "특정 테이블 이름" 으로 검색조건을 추가 시켜주어야 합니다.
SELECT * FROM SYSOBJECTS WHERE name = 'matrix'
SELECT * FROM SYSCOLUMNS WHERE id = 823010013
-- 테이블의 컬럼 값....
select B.NAME FROM DBO.SYSOBJECTS A, DBO.SYSCOLUMNS B where
A.NAME = 'Matrix' AND B.ID = A.ID order by B.colid asc
##########################################################################
[MSSQL] 백업 , 복구, 로그자르기 | 컴퓨터공학도 2004/08/12 10:45
-- 경로를 지정하여 백업하기
BACKUP DATABASE DB이름 TO disk = '파일경로및이름'
-- 백업정보보기
restore headeronly from disk = '파일경로및이름'
-- 백업한 DB 복구하기
RESTORE DATABASE DB이름
FROM disk = '파일경로및이름' WITH REPLACE,
MOVE '논리적장치이름(Data)' TO 'Data파일경로및이름',
MOVE '논리적장치이름(Log)' TO 'Log파일경로및이름'
-- 논리적 장치이름
RESTORE FILELISTONLY from disk = '파일경로및이름'
--attach
EXEC sp_attach_db @dbname = N'디비 이름',
@filename1 = 'Data파일경로및이름',
@filename2 = 'Log파일경로및이름'
--------------------------------------------------------------------------------------
--디바이스(장치)
--1.디바이스 설치
sp_addumpdevice 'disk', '장치명', '경로 및 파일'
----sp_addumpdevice 'disk', 'XERP', 'd:databackup\dbbackup\xerp'
--2.디바이스 삭제
sp_dropdevice '장치명'
--sp_dropdevice 'XERP'
--3.디바이스 정보
sp_helpdevice '장치명'
--sp_helpdevice XERP
--BACKUP
1. 전체 BACKUP
BACKUP DATABASE 'DB명' TO '디바이스명'
BACKUP DATABASE 'DB명' TO '디바이스명' WITH INIT--(장치내역 HISTORY 삭제)
2. 차등 BACKUP
BACKUP DATABASE 'DB명' TO '디바이스명' WITH DIFFERENTIAL
3. 로그 BACKUP
BACKUP LOG 'DB명' TO '디바이스명'
RESTORE HEADERONLY FROM XERP
BACKUP DATABASE XERP TO XERP -- WITH INIT
BACKUP DATABASE XERP TO XERP WITH DIFFERENTIAL
BACKUP LOG XERP TO XERP
--RESTORE
1. RESTORE HEADERONLY FROM '디바이스명(장치명)' 을 하여 복원할 파일의 번호(POSITION)를 알아낸다.
2. RESTORE DATABASE 'DB명' FROM '디바이스(장치)명' WITH FILE = 복원할 파일의 번호(POSITION) , RECOVERY --NORECOVERY(복원해야 할 것이 더 있을때)
RESTORE LOG 'DB명' FROM '디바이스(장치)명' WITH FILE = 복원할 파일의 번호(POSITION) , RECOVERY --NORECOVERY(복원해야 할 것이 더 있을때)
WITH INIT 을 한 파일을 복원 할때 = RESTORE DATABASE 'DB명' FROM '디바이스(장치)명'
--EM을 사용하여 복원하는걸 권장 (IDC에서도 정기적으로 백업을 함)
@@@@@@@@@@@@@@@@@@@@@@@@@@ 로그 정보보기 @@@@@@@@@@@@@
-로그 정보
DBCC SQLPERF(LOGSPACE)
만약, 로그 파일의 사용률이 100%에 달했다면 파일증가 속성에 지정된 임계값의 크기로 확장하는 시간 동안 DML이나 DDL과 같은 쿼리의 실행은 대기 상태에 있게 됩니다. 이럴 때 확장 크기가 너무 크다면 확장하는 시간 동안, 너무 작다면 너무 빈번하게 확장을 시도하게 되어 두 경우 모두 쿼리가 대기하게 되므로 응답속도가 느려지는 결과를 초래하게 됩니다.
실제로 DBA의 실수로 로그 파일 증가 속성을 1MB로 지정해놓고 대형 인덱스를 재구성하여 약 1시간 정도면 종료 되어야 하는 작업이 10시간이 넘게 수행되는 경우도 목격하였고, 대형의 배치작업을 수행하다가 트랜잭션 로그 파일이 있는 디스크에 충분한 공간이 있음에도 불구하고 로그가 가득 찼다는 9002 오류를 유발하고 전체 작업이 롤백되는 현상도 목격 하였습니다. 이와 같이 로그 파일의 사용률과 로그 파일의 확장 속성의 지정 여부도 쿼리의 응답속도에 영향을 미칠 수 있으므로 DBA는 항상 로그 파일의 사용률을 모니터링하고 미리 미리 충분한 로그 파일의 공간을 확보해야 합니다.
[출처] [MSSQL] 쿼리속도가 갑자기 느려질 경우|작성자 한곰이
--로그잘라내기
1. BACKUP LOG 'DB명' TO '디바이스명(장치명)' --로그백업
BACKUP LOG 'DB명' WITH TRUNCATE_ONLY --로그 잘라내기
/*
ex)BACKUP LOG XERP TO XERP
BACKUP LOG XERP WITH TRUNCATE_ONLY
위의 두가지 방법 모두 사용할 수 있으나 로그백업을 권장함(단순복구모델일때는 로그 잘라내기를 함)
*/
2. DBCC SHRINKFILE('로그파일명', 파일크기) --파일크기를 정해주어 파일을 축소하는 방법
DBCC SHRINKFILE('로그파일명', TRUNCATEONLY) --불필요한 로그 파일을 잘라 파일을 축소하는 방법
/*
ex)DBCC SHRINKFILE ('XERP_LOG',500)
DBCC SHRINKFILE ('XERP_LOG',TRUNCATEONLY)
DBCC LOGINFO
위의 두가지 방법 모두 사용할 수 있으니 TRUNCATEONLY를 더 권장 함
*/
--파일명 알아내기
EXEC SP_HELPFILE
SELECT * FROM SYSFILES
#### 사용예 ####
sp_helpdb SAT2010
-- 해당하는 DB의 트랜잭션 로그를 비운다.
BACKUP LOG SAT2010 with NO_LOG
-- 로그파일의 물리적인 파일을 줄인다.
DBCC SHRINKFILE (SAT2010_log, TRUNCATEONLY)
sp_helpdb SAT2010
-----------------------------------------------------------
-- LDF 파일 축소
--MSSQL 2005 이전 버전
BACKUP LOG DB이름 WITH NO_LOG
DBCC SHRINKFILE (Log명, 2)
--MSSQL 2008 버전 부터
ALTER DATABASE DB이름 SET RECOVERY Simple
USE DB이름
DBCC SHRINKFILE (Log명, 2)
ALTER DATABASE DB이름 SET RECOVERY FULL
##########################################################################
SELECT '가나' as 한글 , '영어' as 언어
=====================
| 한글 | 언어
--------------------
| 가나 | 영어
=====================
##############################################################################
### CONVERT 및 날짜관련 함수 ####
##############################################################################
세기 포함
안함(yy) $ 세기 포함(yyyy) $ 표준 $ 입력/출력**
============================================================================
- $ 0 또는 100 (*) $ 기본값 $ mon dd yyyy hh:miAM(또는 PM)
1 $ 101 $ USA $ mm/dd/yy
2 $ 102 $ ANSI $ yy.mm.dd
3 $ 103 $ 영국/프랑스 $ dd/mm/yy
4 $ 104 $ 독일 $ dd.mm.yy
5 $ 105 $ 이탈리아 $ dd-mm-yy
6 $ 106 $ - $ dd mon yy
7 $ 107 $ - $ Mon dd, yy
8 $ 108 $ - $ hh:mm:ss
- $ 9 또는 109 (*) $ 기본값 + 밀리초 $ mon dd yyyy hh:mi:ss:mmmAM(또는 PM)
10 $ 110 $ USA $ mm-dd-yy
11 $ 111 $ 일본 $ yy/mm/dd
12 $ 112 $ ISO $ yymmdd
- $ 13 또는 113 (*) $ 유럽 기본값 + 밀리초 $ dd mon yyyy hh:mm:ss:mmm(24h)
14 $ 114 $ - $ hh:mi:ss:mmm(24h)
- $ 20 또는 120 (*) $ ODBC 표준 $ yyyy-mm-dd hh:mi:ss(24h)
- $ 21 또는 121 (*) $ ODBC 표준(밀리초) $ yyyy-mm-dd hh:mi:ss.mmm(24h)
- $ 126(***) $ ISO8601 $ yyyy-mm-dd Thh:mm:ss.mmm(스페이스 없음)
- $ 130* $ 회교식**** $ dd mon yyyy hh:mi:ss:mmmAM
- $ 131* $ 회교식**** $ dd/mm/yy hh:mi:ss:mmmAM
========================================================================
TO_CHAR(SYSDATE,'YYYY-MM-DD') -> 2003-01-23
TO_CHAR(SYSDATE,'YYYY/MM/DD') -> 2003/01/23
TO_CHAR(SYSDATE,'YYYYMMDD') -> 20030123
반대로 처리할때는 TO_DATE함수를 사용하면 되죠~
숫자를 처리할때는
TO_CHAR(2500000,'L9,999,999') -> w2,500,000
TO_CHAR(2500000,'9,999,999.99') -> 2,500,000.00
반대로 처리할때는 TO_NUMBER함수를 사용하면 되죠~
이 외에도 활용할 수있는 용도가 무지 많습니다.
CONVERT(VARCHAR(10),GETDATE(),120) -> 2003-01-23
CONVERT(VARCHAR(10),GETDATE(),111) ->2003/01/23
CONVERT(VARCHAR(8),GETDATE(),112) ->20030123
#################@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
날짜 관련 함수입니다.
2.1.1 GETDATE() 함수
시스템의 날짜를 리턴합니다.
SELECT GETDATE()
2002-05-24 오전 10:51:05
SELECT GETDATE()+100
2002-09-01 오전 10:51:05
2.1.2 CONVERT() 함수
결과값의 데이타 타입을 변환시킵니다.
SELECT CONVERT(varchar(30), GETDATE(),100)
May 24 2002 10:51AM
SELECT CONVERT(varchar(30), GETDATE(),101)
05/24/2002
SELECT CONVERT(varchar(30), GETDATE(),102)
2002.05.24
SELECT CONVERT(varchar(30), GETDATE(),103)
24/05/2002
SELECT CONVERT(varchar(30), GETDATE(),104)
24.05.2002
SELECT CONVERT(varchar(30), GETDATE(),105)
24-05-2002
SELECT CONVERT(varchar(30), GETDATE(),106)
24 May 2002
SELECT CONVERT(varchar(30), GETDATE(),107)
May 24, 2002
SELECT CONVERT(varchar(30), GETDATE(),108)
10:51:05
SELECT CONVERT(varchar(30), GETDATE(),109)
May 24 2002 10:51:05:140AM
SELECT CONVERT(varchar(30), GETDATE(),110)
05-24-2002
SELECT CONVERT(varchar(30), GETDATE(),111)
2002/05/24
SELECT CONVERT(varchar(30), GETDATE(),112)
20020524
SELECT CONVERT(varchar(30), GETDATE(),113)
24 May 2002 10:51:05:160
SELECT CONVERT(varchar(30), GETDATE(),114)
10:51:05:160
2.1.3 DATEPART() 함수
날짜에서 지정한 날자형식부분만 추출해줍니다
형식: DATEPART(날짜형식, 날짜)
SELECT DATEPART(yy, GETDATE())
2002
SELECT DATEPART(mm, GETDATE())
5
SELECT DATEPART(dd, GETDATE())
24
SELECT DATEPART(hour, GETDATE())
10
SELECT DATEPART(mi, GETDATE())
51
SELECT DATEPART(ss, GETDATE())
5
SELECT DATEPART(dw, GETDATE())
6
년/월/일/시/분/초/요일을 숫자로 보여준 예제입니다.
2.1.4 YEAR() , MONTH() , DAY() 함수
해당 년,월,일을 각각 뽑아줍니다.
형식: YEAR(날짜) , MONTH(날짜) , DAY(날짜)
SELECT YEAR(GETDATE())
2002
SELECT MONTH(GETDATE())
5
SELECT DAY(GETDATE())
24
2.1.5 DATEADD() 함수
DATEADD함수는 날짜에 지정한 만큼을 더합니다.
형식: DATEADD(날짜형식, 더할 값, 날짜)
SELECT DATEADD(mm,20,GETDATE())
2004-01-24 오전 10:51:05
현재날짜에 월에 20을 더한 날짜를 출력하라.
SELECT DATEADD(dd,100,GETDATE())
2002-09-01 오전 10:51:05
오늘부터 100일 후의 날짜를 출력하라.
2.1.6 DATEDIFF() 함수
DATEDIFF함수는 두 날짜사이의 날짜형식에 지정된 부분을 돌려줍니다.
형식: DATEDIFF(날짜형식, 시작 날자, 끝 날짜)
SELECT DATEDIFF(dd,GETDATE(),'3000.1.1')
364369
현재날짜와 3000.1.1일 사이의 일수는 얼마일까.??
SELECT DATEDIFF(MM,GETDATE(),'2003.1.1')
8
현재날짜와 2003.1.1일 사이의 월수는 얼마일까.??
update mem_resume set
re_old = datediff(yyyy,convert(char(4),re_BirthY)+'-'+convert(char(2),re_BirthM)+'-'+convert(char(2),re_BirthD),Register_Date)
select datediff(yyyy,'1978-12-16','2008-12-02 11:34:04.017') ''현재 내 나이를 만으로 출력
select datediff(yyyy,cast(('1978'+'-12'+'-16') as char(10)),'2008-12-02 11:34:04.017')
2.1.7 DATENAME() 함수
DATENAME함수는 지정한 날짜의 날자형식의 이름을 돌려줍니다.
형식: DATENAME(날짜형식, 날짜)
SELECT DATENAME(mm,GETDATE())
May
SELECT DATENAME(dd,GETDATE())
24
SELECT DATENAME(dw,GETDATE())
Friday
day_name = right(FormatDateTime(Now,1),3) ==> 월요일, 화요일,,, 등으로 저장
time_now = left(FormatDateTime(Now,4),2) ==> 시간이 저장 ㅡ
############################## 말일, 초일 구하기 ###################################################
declare @날자 datetime
set @날자='2008-01-01'
---현재월의 초일 --
SELECT dateadd(d,-day(@날자)+1,@날자)
---현재월의 말일 --
SELECT dateadd(d,-day(dateadd(m,1,@날자)), dateadd(m,1,@날자))
---전월의 초일 --
SELECT dateadd(m,-1, dateadd(d,-day(@날자)+1,@날자))
---전월의 말일 --
SELECT dateadd(d,-day(@날자),@날자)
############################## CAST ###################################################
SELECT cast(11.11 as char(20)), convert(varchar(20),11.11)
,cast(getdate() as char(20)), convert(varchar(20), getdate())
,cast('1234' as int ), convert(smallint, '1234')
go
--cast로 문자형으로 바꿔준다. (오라클, ms-sql에 있음. SQL-92표준)
--convert로 문자형으로 바꿔준다. (ms-sql에만 있음. T-sql구문)
select '1234' + 1
select cast('1234'as int )+1, convert(int,'1234')+1
select cast('123456' as smallint), convert(smallint, '123456')
select
select getdate()
-- getdate는 date 타입
select cast('2004-06-03' as datetime)
select cast('2004-06-03' as smalldatetime)
select cast('1234' as int), convert(int, '1234')
select cast('1234' as int)+1, convert(int, '1234')+1
select cast('123456' as int), convert(int, '123456')
#################################################################################
DATEDIFF
지정한 두 날짜 간에 교차되는 날짜와 시간 경계값을 반환합니다.
구문
DATEDIFF ( datepart , startdate , enddate )
인수
datepart
차이를 계산할 날짜 부분을 지정하는 매개 변수입니다. 다음은 Microsoft® SQL Server™에서 인식하는 날짜 부분과 약어입니다.
날짜 부분 # 약어
=========================
Year # yy, yyyy
quarter # qq, q
Month # mm, m
dayofyear # dy, y
Day # dd, d
Week # wk, ww
Hour # hh
minute # mi, n
second # ss, s
millisecond # ms
startdate
계산의 시작 날짜입니다. startdate는 날짜 형식에서 datetime 또는 smalldatetime 값이나 문자열을 반환하는 식입니다.
smalldatetime은 분 단위로만 정확하므로 smalldatetime 값을 사용할 경우 초와 밀리초는 항상 0입니다.
연도의 마지막 두 자리 숫자만 지정할 경우 two digit year cutoff 구성 옵션 값의 마지막 두 자리 숫자보다 작거나 같은 값은 구분 기준 연도와 같은 세기에 해당합니다. 이 옵션 값의 마지막 두 자리 숫자보다 큰 값은 구분 기준 연도보다 이전 세기에 해당합니다. 예를 들어, two digit year cutoff가 2049(기본값)일 경우 49는 2049년으로 해석되고 2050은 1950년으로 해석됩니다. 이러한 애매함을 피하기 위해 네 자리 연도를 사용하십시오.
시간 값 지정에 대한 자세한 내용은 시간 형식을 참조하십시오. 날짜 지정에 대한 자세한 내용은 datetime 및 smalldatetime을 참조하십시오.
enddate
계산의 종료 날짜입니다. enddate는 날짜 형식에서 datetime 또는 smalldatetime 값이나 문자열을 반환하는 식입니다.
반환 형식
integer
비고
enddate에서 startdate를 뺍니다. startdate가 enddate보다 크면 음수 값이 반환됩니다.
DATEDIFF의 경우, 결과가 정수 값의 범위를 벗어나면 오류가 발생합니다. 밀리초의 경우, 최대값은 24일, 20시간, 31분, 23.647초입니다. 초의 경우, 최대값은 68년입니다.
분, 초, 밀리초 등 교차된 경계값을 계산하는 방법은 DATEDIFF의 결과가 모든 데이터 형식에서 일관성을 유지시킵니다. 결과는 첫 번째 날짜와 두 번째 날짜 사이에 겹쳐지는 datepart 경계값에 해당하는 부호 있는 정수값입니다. 예를 들어, 1월 4일 일요일과 1월 11일 일요일 사이의 주 수는 1입니다.
예제
다음은 pubs 데이터베이스의 titles 테이블에 대해 현재 날짜와 출판 날짜 간의 일 수 차이를 확인하는 예제입니다.
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
#########################################################################
오늘부터 30일 뒤의 날짜 또는 현재 시간에서 45분뒤의 시간을 계산할수 있습니다.
date 어느날을 더하려면 일년을 기준으로 한 일("y"), 일("d"), 또는 요일("w")를 사용하면 됩니다.
DateAdd 함수에서는 잘못된 날짜는 반환하지 않습니다. 아래예제는 1월31일에 한달을 더합니다.
NetDate = DateAdd("m", 1, "31-jan-95")
#########################################################################
테이블 소유자 변경법
#########################################################################
sp_changeobjectowner '개체명(테이블명)', 'dbo(또는 원하는 소유자명)'
sp_changeobjectowner 'iPaqWebCS.stockupdate', 'dbo'
#########################################################################
dbo의 로그인 이름 변경법
#########################################################################
exec sp_changedbowner 'sa'
#########################################################################
로그파일을 완전삭제하고 다시 만드는 방법입니다..
#########################################################################
로그파일 전혀 유지할 필요가 없으실때 사용하시면 됩니다..
1. 일단 로그파일을 삭제하고자 하는 db를 single user로 만들어 줍니다..단독모드로 열려있지 않으면 디비파일을 삭제할수 없습니다.
exec sp_dboption 'pixmovie(db명)','single user','true'
2. DB를 내립니다..
exec sp_detach_db 'pixmovie(db명)'
3. Db가 저장되어 있는 폴더에 가서 해당 DB의 ldf파일의 이름을 변경합니다..혹시 나중에 쓸지 모르니까요...
4. DB를 올립니다. 이 프로시져를 실행하면 해당디비의 ldf파일이..500k정도의 크기로 재 생성됩니다.
exec sp_attach_single_file_db 'pixmovie(db명)','d:\sqldb\pixmovie_Data.MDF(위치)'
5. 단독모드를 풀어줍니다..
exec sp_dboption 'pixmovie(db명)','single user','false'
#########################################################################
그외
backup log 데이터 베이스이름 with TRUNCATE_ONLY
#########################################################################
#########################################################################
mssql 첫라인빼고 다 select 해오기 | 자바야한다 2006/05/10 18:15
#########################################################################
http://blog.naver.com/kimtkddn/40024320894
부분범위 처리를 말씀 하시는것 같습니다.
오라클의 경우에 ROWNUM과 인덱스를 이용해서 부분범위 처리가 가능합니다.
(자세한 사항은 엔코아사이트 참조)
MSSQL의 경우 부분범위처리가 참 까다로운것 같습니다.
특정한 몇건을 가지고 와야 할 경우 SELECT TOP 10 * FROM TAB 이런식으로 상위 n개만
가지고 올수 있으니....
게다가 웹게시판의 유행적인 모습으로 검색이라는 기능과 하단의 < 1 2 3 ~~~ 10 >
이런식의 페이징 때문에 질문자님께서 원하시는 만큼의 100%는 해결하기 불가능 합니다.
다만 제가 알고 있는 부분은
TAB라는 테이블에 SEQ(pk),TITLE,CONTENTS가 있다고 했을 경우
SELECT TOP 10 TITLE,CONTENTS
FROM TAB
WHERE SEQ NOT IN (
SELECT TOP 0 SEQ
FROM TAB
)
보통은 이런모양으로 부분범위 처리를 합니다.
단점은... 1~10 정도 페이지는 빠르게 처리 하나 뒤로 가면 갈수록 느려지는게 있습니다.
## 서브쿼리 안에 0 라는 숫자는 변수에 의한 연산이 되어야 합니다.
그리고 적절한 인덱스를 형성하셨다면 ORDER BY 문을 사용하지 마시고
해당 인덱스로 정렬 하시면 원하시는 효과를 보실수 있을꺼라 생각 됩니다.(대략 70%정도)
#########################################################################
@@@@@@@@@@@@@@@ IDENTITY값 0으로 만들기@@@@@@@@@@@@@@@@@@@@@
#########################################################################
DBCC CHECKIDENT(테이블명, RESEED, 0) '현재 IDENT값을 0으로 만들어버림 등록시 1증가하여 저장됨
IDENTITY 속성 | MS-SQL 2006/05/02 14:27
http://blog.naver.com/whwlfnsl/70003875172
숫자 datatype에서 선언 할 수 있는 칼럼 속성이다.
각 table에서 한 개만 가질 수 있다.
IDENTITY [ ( 시작값 , 증가값 ) ]
시작값과 증값을 지정하지 않으면 1부터 시작해서 1씩 증가한다.
테이블에 지정된 시드값과 증가값을 알고 싶은때 다음과 같은 명령어를 실행한다.
SELECT IDENT_SEED('table_name'), IDENT_INCR('table_name')
테이블에서 IDENTITY 속성을 임시로 비활성화 시키려면
SET IDENTITY_INSERT table_name ON 옵션을 사용한다.
SET IDENTITY_INSERT table_name ON 옵션을 사용하면 유일성이 보장되지 않는다. 유일성을 강화하려면 칼럼에 UNIQUE나 PRIMARY KEY 제약을 선언해야 한다.
IDENTITYCOL 키워드는 이름에 상관없이 IDENTITY 속성을 가진 테이블에 있는 특정 칼럼을 자동으로 참조한다.
SELECT IDENTITYCOL FROM table_name
IDENTITY 속성에 의해 생성된 값을 바로 알고 싶을때는
SELECT @@IDENTITY 를 사용한다.
동일한 테이블이나 다른 테이블에 대해 한 일괄 처리에서 여러 개의 INSERT 문이 실행되면 변수가 마지막 문의 값만을 갖는다. 또 행을 삽입한 후 발생하는 INSERT 트리거가 있고, 이 트리거가 IDENTITY 속성을 가진 테이블에 행들을 삽입한다면 @@IDENTITY 가 원래 INSERT 문에 의해 삽입된 값을 갖지 않을 것이다.
-->해당세션에서 IDENTITY값이 insert 되지 않은 경우네는 NULL값이 반환됨.
--> 실패하던 성공하던 insert 시도만 해도 증가한다.
(연속속은 100% 보장하지 않는다.)
--> 트리거에 의해 수행한 값과 상관있다.
SELECT SCOPE_IDENTITY()
SCOPE_IDENTITY() 함수는 동일한 범위(저장프로시저, 트리거, 일괄처리 등)에서 테이블에 삽입된 마지막 ID값을 반환한다.
--> 해당세션, 해당영역에서 IDENTITY 값이 insert된 마지막 IDENTITY 값이 반환됨.
--> 명시적으로 insert된 값 트리거가 아닌 사용자의 insert문장만 처리해 준다는 것이다.
--> 트리거에 의해 수행한 값과 전혀 상관이없다.
SELECT IDENT_CURRENT('table_name')
IDENT_CURRENT() 함수는 임의의 세션과 범위에서 지정한 테이블에 생성된 마지막 ID 값을 반환한다.
--> 세션구분없이 특정 테이블에 마지막으로 insert된 IDENTITY 값이 반환됨.
DBCC CHECKIDENT
DBCC CHECKIDENT 명령어를 사용하여 ID 값을 적절한 번호로 재 설정 할 수 있다.
#########################################################################
직접값을 넣을때 셀렉트값을 가져와서 넣기.....
INSERT INTO OrderDetail
(od_oid, od_gid, od_gcid, od_color, od_qty, od_price, od_selcolor, od_selsize)
SELECT 104137 AS Expr1, cr_gid, cr_gcid, cr_color, cr_qty, cr_price, cr_selcolor,
cr_selsize
FROM Cart
WHERE (cr_sessionid = '8205551')
###########################################################################
SELECT g_id
FROM Goods
WHERE (g_use = '1') AND (g_id NOT IN
(SELECT TOP 2 g_id
FROM Goods
WHERE g_use = '1'
ORDER BY g_moddate DESC))
Top 2개의 값을 빼고 나머지값들을 찾을때
update Goods set g_use = '0' where g_id IN (
SELECT g_id
FROM Goods
WHERE (g_use = '1') AND (g_id NOT IN
(SELECT TOP 2 g_id
FROM Goods
WHERE g_use = '1'
ORDER BY g_moddate DESC))
)
Top 2개의 값을 빼고 나머지값들을 업데이트할때
sSQL = "SELECT a.ridx as midx, (select g_smlimg from goods where g_id = a.ridx) as rSimg " &_
", (select g_bigimg from goods where g_id = a.ridx) as rBimg " & _
", (select g_name from goods where g_id = a.ridx) as rname " & _
", (select g_use from goods where g_id = a.ridx) as ruse " & _
" FROM goodsRelation a, goods b " &_
" WHERE a.gidx = b.g_id and b.g_use = 1 and a.gidx = " & gid
@33333333@@333333333333333333333333333333333333333333333333333333333333333333333333
고급쿼리 종류별로 있음.
@33333333@@333333333333333333333333333333333333333333333333333333333333333333333333
스칼라 서브쿼리
-- 하나의 행에서 하나의 칼럼값(필드값= 스칼라값)을 출력하는 서브쿼리
-- 서브쿼리의 결과값이 메인쿼리의 조건값으로 쓰인다.
select employee_id, last_name, (case
when department_id=
(select department_id
from departments
where location_id = 1800)
then 'Canada' else 'USA' end) location
from employees;
select * from departments where location_id = 1800;
***********************************************************************
CORRELATED Subquery
select last_name, salary, department_id
from employees outer
where salary > (select avg(salary)
from employees
where department_id = outer.department_id);
-- 메인 쿼리의 행을 받아서 서브쿼리에 적용해서 메인쿼리을 수행하고
다시 다음 행을 하나씩 순차적으로 서브쿼리에 적용해서 메인쿼리을 실행한다.
-- 각 행과 서로 연관된 값을 출력할때 사용
-- 부서별 평균 월급을 검색하고 각 부서별 평균 월급 보다 많이 받는 사원을 출력하라
select avg(salary) from employees where department_id =90; -- 부서별 평균 월급
select last_name, salary from employees where department_id =90; -- 부서별 검색
select avg(salary) from employees where department_id =60;
select last_name, salary from employees where department_id =60;
************************************************************
select e.employee_id, last_name, e.job_id
from employees e
where 2<= (select count(*) from job_history where employee_id = e.employee_id);
-- employees 테이블에서 사원 id를 받아서 서브쿼리의 job_history 테이블에서
count 한값을 메인쿼리의 조건으로 해서 두번 이상 업무를 바꾼 사원정보를
순차적으로 출력하라.
select * from job_history;
**********************************************************************
EXISTS 연산자 사용예
select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 'x'
from employees
where manager_id= outer.employee_id);
-- 서브쿼리에서 만족하는 값이 검색되면 더이상 서브쿼리에서 검색않고 메인쿼리에서
출력한후 서브쿼리에서 다음 행을 검색한다.
-- IN 연산자를 사용하여 같은 결과를 나오게 할 수 있다
select employee_id, last_name, job_id, department_id
from employees
where employee_id in (select manager_id
from employees
where manager_id is not null);
NOT EXISTS 연산자 사용 예
SELECT department_id, department_name
FROM departments d
where not exists (select 'x'
from employees
where department_id = d.department_id);
-- 서브쿼리에서 조건에 만족하지 않은값일 경우 메인쿼리에서 출력
-- NOT IN을 사용할수 있다 . 하지만 NULL 값이 존재할경우 조건에 만족하더라도
쿼리에서 no rows 로 출력될수 있다
SELECT department_id, department_name
FROM departments
where department_id not in (select department_id from employees);
*********************************************************************************
CORRELATED UPDATE
--연관관계를 이용해서 UPDATE 함으로써 추가된 칼럼에 필드 값 입력
alter table employees add (department_name varchar2(14));
desc employees
select employee_id, last_name, job_id, manager_id, department_name from employees;
update employees e
set department_name = (select department_name
from departments d
where e.department_id = d.department_id);
select employee_id, last_name, job_id, manager_id, department_name from employees;
alter table employees drop (department_name) ;
desc employees
CORRELATED DELETE
-- 연관관련 서브쿼리을 이용해서 copy_emp 테이블의 row 중에서 job_history 테이블에
존재하는 행을 삭제
create table copy_emp as select * from employees; -- TEST 테이블
desc copy_emp
desc job_history
select employee_id from copy_emp;
DELETE from copy_emp e
where employee_id =
(select distinct employee_id from job_history
where employee_id = e.employee_id);
select employee_id from copy_emp;
rollback;
*********************************************************************************
WITH 절 사용 예
-- 내부적으로 WHIT절은 인라인 뷰 혹은 임시 테이블로 해석 된다.
with
dept_costs as
(select d.department_name, sum(e.salary) as dept_total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name),
avg_cost as
(select sum(dept_total)/count(*) as dept_avg
from dept_costs)
select *
from dept_costs
where dept_total > (select dept_avg from avg_cost)
order by department_name;
-- 부서별 salary total이 전체 salary total의 평균보다 높은 부서를 출력하라.
-- dept_costs : 부서별 salary total avg_cost : 전체 부서의 salary total의 평균
-- 여러번 사용되어야 하는 인라인 뷰 혹은 임시 테이블을 WITH 절을 이용하여 미리 생성한 후
select 문에서 호출한다. 쉼표로써 여러개 생성할수 있다.
select d.department_name, sum(e.salary) as dept_total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name; -- 부서별 월급 합계
select avg(sum(e.salary))
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name; -- 부서별 월급 합계의 평균
##########@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
▒▒▒ Database Name ▒▒▒
SELECT name FROM master.dbo.sysdatabases WHERE HAS_DBACCESS(name) = 1
▒▒▒ Table Name ▒▒▒
SELECT name FROM 데이타베이스네임.dbo.sysobjects WHERE xtype='U' AND status > 0
▒▒▒ Table Schema ▒▒▒
EXEC SP_COLUMNS '테이블이름'
##########@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
다른테이블의 값을 곧바로 업데이트 시키기
values() 값을 없애고 들어갈값을 정해준다
insert into StockPartTbl(l_id, p_code,rohs,goodnum,badnum,shortnum,moddatetime)
select '01',p_code,rohs,0,0,0,getdate() from CodeProductPartTbl
##########@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
두개의 테이블에서 업데이트 시키기
UPDATE [붙여넣을DB명].[붙여넣을테이블명] SET MC_NAME=A.MC_NAME
FROM [복사할DB명].[복사할테이블명] A JOIN [붙여넣을DB명].[붙여넣을테이블명] B
ON A.MC_SEQ = B.MC_SEQ
UPDATE M_PartTbl SET spec=A.g_spec
FROM goods A JOIN M_PartTbl B
ON A.op_id = B.part_idx
a.에는 업데이트 될 파트수량 이고 b.에는 업데이트될 레코드를 찾는다 M_PartTbl의 위치를 잘보라
Update M_PartTbl Set stock=(stock+a.part_ea)
From O_TransPartTbl a join M_PartTbl b
ON a.part_code=b.part_code
where b.posarea='03' and a.tkey='KEY32142366233887'
--:> 업데이트 AS 이름을 앞으로 빼서 쓰면 됨.
UPDATE T
SET T.M_ID = 'S_ID' , T.M_PW = 'S_PW';
FROM TBL_login T
JOIN HumanInfo H ON T.M_ID = H.M_ID
WHERE H.M_ID ='비밀번호'
UPDATE H
SET H.M_ID = 'S_ID'
FROM TBL_login T
JOIN HumanInfo H ON T.M_ID = H.M_ID
WHERE H.M_ID ='비밀번호'
====> 상관쿼리 업데이트 필드가 한개인경우 가능
UPDATE TBL_HEOWON [M] SET
NAME = (SELECT NAME FROM TBL_HEOWON_BACKUP WHERE ID = [M].ID),
ADDR = (SELECT ADDR FROM TBL_HEOWON_BACKUP WHERE ID = [M].ID)
##########@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
두개의 테이블에서 삭제 시키기
DELETE FROM TB_masterpayDet
FROM TB_masterpay A,
TB_masterpayDet B
WHERE A.mp_idx = B.mp_idx and A.mp_idx = 1
##########@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
데이터형바꾸기
CONVERT(int,sum((od_out_cost-(od_out_cost*od_discount*0.01))*od_ea))
#############$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@
한필드에서 중복되는것은 제외시키고 하나씩만 가져ㅗㅁ
distinct
#############$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@
where aaa is not NULL
#############$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@
with cube과 with rollup
주석문을 해제해 가시면서 찬찬히 실행해 보세요...
아울러.. 좀더 자세한 글을 원하심..
OLAP 책을 보시거나..
정원혁 선생님이 쓰신...
대림출판사 MSSQL7.0을 사서 보시길 역시 권장합니다.
SELECT type, pub_id, price /*AVG(price)*/
FROM titles
/* GROUP BY type, pub_id */
/* WITH ROLLUP */
SELECT type, pub_id, AVG(price)
FROM titles
GROUP BY type, pub_id
/* WITH ROLLUP */
SELECT type, pub_id, AVG(price)
FROM titles
GROUP BY type, pub_id
WITH ROLLUP
SELECT pub_id, type, AVG(price)
FROM titles
GROUP BY pub_id, type
SELECT pub_id, type, AVG(price)
FROM titles
GROUP BY pub_id, type
WITH CUBE
################@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
DB가 오라클이시면..
SELECT Z.bigcode,DECODE(Z.midcode,'0',Z.bigcode,Z.midcode) as midcode
FROM (
SELECT A.bigcode,A.midcode
, (select count(*) from 테이블명 where bigcode=A.bigcode) as mul
FROM 테이블명 A ) Z
WHERE ((Z.midcode <> '0') OR (mul <2))
....
오라클이아니면 DECODE문이 지원이 안될것입니다.
SELECT Z.bigcode, (CASE WHEN Z.midcode = '0' THEN Z.bigcode
ELSE Z.midcode END) as midcode
FROM (
SELECT A.bigcode,A.midcode
, (select count(*) from 테이블명 where bigcode=A.bigcode) as mul
FROM 테이블명 A ) Z
WHERE ((Z.midcode <> '0') OR (mul <2))
주석을 달아드리면..
=============================================================
SELECT Z.bigcode,DECODE(Z.midcode,'0',Z.bigcode,Z.midcode) as midcode
--님깨서 처리했던 거와 같이 0이면 bigcode ,0이아니면 midcode
리턴
FROM (
SELECT A.bigcode,A.midcode
, (select count(*) from 테이블명 where bigcode=A.bigcode) as mul
FROM 테이블명 A ) Z
--해당 테이블에 bigcode 및 midcode 를 가지고오는데 bigcode 의 갯수도
함깨 가져온다.
WHERE ((Z.midcode <> '0') OR (mul <2))
-- midcode 가 0인경우에 1보다 작으면(bigcode 가 2개이상 존재) 조회가 안된다..
조건이 좀 복잡해서 그렇치 WHERE 절에 조건만 잘 파악하시면
되실듯...
테스트해보시고 문제점있으시면 쪽지주세요..
그럼이만..
=======================
아래는 일단 MSSQL에서만 테스트 했습니다.
오라클에서도 그대로 쓸수 있을것입니다...
SELECT Z.bigcode, (CASE WHEN Z.midcode = '0' THEN Z.bigcode
ELSE Z.midcode END) as midcode
FROM (
SELECT A.bigcode,A.midcode
, (select count(*) from (SELECT DISTINCT bigcode ,midcode
FROM 테이블명 ) as C
where C.bigcode=A.bigcode) as mul
FROM (Select DISTINCT bigcode ,midcode FROM 테이블명 ) as A ) Z
WHERE ((Z.midcode <> '0') OR (mul <2))
쪽지내용 테이블에서.
================
b1 0 *
b1 0 *
b2 0
b2 m1 *
b2 m1 *
b2 m2
b3 0 *
b3 0 *
b4 0
위의 내용이
========
b1 b1
b2 m1
b2 m2
b3 b3
b4 b4
이렇게 나오게 처리했습니다.. 맞으시면 테스트해보시고 사용해보시고
주석은 일단 위에서 확장된것이라서 주석부분은 제외시켰습니다.
몇번 테스트 해보시면 이해하실수있으실것입니다.....
###########@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@#################
복수행 연산자 (IN, NOT IN, ANY, ALL, EXISTS)
일단 쉬운 in , not in 부터 설명을 드리죠
만약 emp라는 테이블에 name이라는 컬럼이 있다고 가정하겠습니다.
name 에는
a,b,c,d,e,f라는 값이 들어있습니다.
그럼
첫째
select name from emp where name in ('a','b','c')
이라는 sql문은 name값이 a나 b나 c중에 일치되는게 있으면
출력이 되는겂니다.
따라서 결과는 a,b,c겠죠?
둘째
select name from emp where name not in ('a','b','c')
는 조건에 해당하는 a,b,c중에 해당하는게 없어야 됩니다.
따라서 결과는 d,e,f가 되겠죠
이제 any , all , exists 등은 서브쿼리 할시에
이용하는 것들입니다.
만약 서브쿼리를 모르신다면 서브쿼리 공부하실때 자연히 이해되실겁니다.
예제 만들기가 마땅치 않아
오라클의 scott계정 테이블을 이용하겠습니다.
select * from emp
where salary > any (100,200,300)
위의 쿼리는 조건값 100,200,300 중 아무거나(any)중에 하나만
조건에 해당되면 참이 되는겂니다.
즉 위의 쿼리는 select * from emp where sal > 100 과 같은 얘기가 됩니다.
이와 반대로
select * from emp
where salary > all (100,200,300)
위의 쿼리는 100,200,300 조건값보다 모두(all) 커야 합니다.
따라서 select * from emp where sal>300 과 같은 얘기가 되겠죠.
마지막으로
select * from emp outer
where exists (select * from emp where manager_id = outer.empno)
이런 구문은 서브쿼리절에 해당되는 데이타가
있어야 결과값이 참이 되는 케이스입니다.
그럼^^
(CASE WHEN ISNUMERIC(SUBSTRING(UPPER(p_serial),14,1)) ='1' THEN
##############################################################################
select * from TB_Class wc where wc.br_code='SL03' and wc.cl_useyn='Y' AND
Patindex('%,'+CAST(wc.cl_grade AS varchar(3))+',%',','+
(select replace(sf_chargegrade, ' ', '') from tb_staff where br_code='SL03 ' and sf_retireyn='N' and sf_code='03003')+',')>0
or wc.sf_code='03003'
해당되는 값이 몇개인지 확인 시켜준다.
select Patindex('%5,%',',5,6,7,8,')
출력값 2
2번째 존재한다는것을 알려줌
#####################################
보통 select문을 실행하게 되면 SQL서버는 'Shared Lock'을 걸게됩니다. 즉 select하는 순간에 다른 사용자에 의한 데이터의 변경을 잠시 막게되지요.. 이 Lock때문에 다른 사용자의 SQL문 수행이 방해받게 됩니다.
대부분의 웹프로그래밍에서는 select시 다른 사용자에 의해 데이터가 변경되어도 큰 문제가 발생하지 않습니다.
다음과 같이 'nolock'이라는 잠금힌트를 사용해서 select를 하면 잠금이 걸리지 않은 상태에서 실행할 수 있습니다.
use pubs
go
select * from authors with (nolock)
여기서 'nolock' 을 잠금힌트라고 합니다. 'nolock' 잠금힌트를 사용할 경우 커밋되지 않은 데이터나 읽기도중 롤백된 데이터도 읽어오게 됩니다. 즉 dirty page를 읽게되지요..
그러나 게시판의 조회수가 5에서 6으로 되었는데 5로 읽어온다고 해서 크게 문제가 되진 않겠지요?
좀 정리를 하면 select 결과가 실제 데이터와 다르더라도 큰 문제가 되지 않는 경우 'nolock'힌트를 사용하면 쿼리실행의 성능향상을 도모할 수 있습니다.
출처 : http://www.webproducer.org
######################### mssql에서 특정 테이블의 속성에 대한 값을 출력 ####################################################
select * from sys.columns col
where col.object_id = object_id('tb_studentrecord')
모든테이블값을 뽑기
select
tbl.name as 'Table Name',
col.name as 'Column Name',
col.column_id as 'Column Position',
TYPE_NAME(col.system_type_id) as 'Type Name',
col.max_length as 'Max Length',
col.is_identity as 'Identity',
col.is_nullable as 'Allow Null'
from sys.columns col
join sys.tables tbl
on col.object_id = object_id(tbl.name)
where tbl.name='tb_student'
select
tbl.name as 'Table Name',
col.name as 'Column Name',
col.column_id as 'Column Position',
TYPE_NAME(col.system_type_id) as 'Type Name',
col.max_length as 'Max Length',
col.is_identity as 'Identity',
col.is_nullable as 'Allow Null'
from sys.columns col
join sys.tables tbl
on col.object_id = object_id(tbl.name)
order by tbl.name, col.column_id
#@@@@ ==> sp_columns 'TB_Book'
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
select * from sys.foreign_keys
select * from sys.foreign_key_columns
select * from sys.tables
select Fk.* from sys.foreign_keys Fk
left outer join sys.tables TbR
on TbR.object_id = Fk.referenced_object_id
inner join sys.foreign_key_columns Fk_Cl
on Fk_Cl.constraint_object_id = Fk.object_id
where
Fk.parent_object_id = object_id(N'dbo.tb_student')
or Fk.referenced_object_id = object_id(N'dbo.tb_student')
####################################################################################
mssql 2005 에서 테이블 복사
exec('select * into ' + @BackupTableName + ' from Tb_Componentitem')
########################$$$$$$$$$$$$$$$$$$$$$$$$$@@@@@@@@@@@@@@@@@@@@@@@@@
두개의 디비를 한쿼리문에서 조절
UPDATE [붙여넣을DB명].[붙여넣을테이블명] SET MC_NAME=A.MC_NAME
FROM [복사할DB명].[복사할테이블명] A JOIN [붙여넣을DB명].[붙여넣을테이블명] B
ON A.MC_SEQ = B.MC_SEQ
select a.* from
SAT2010.dbo.tb_student as a
where a.st_code=603537
#############################@#@#@#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
임시 테이블 생성 및 사용
select *
into #leekma
from tb_student
--DROP TABLE #leekma
임시 테이블이 생성된다 tmp 디비에....
이후 문제는 알아서 찾아봐라.
#############################@#@#@#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
ROLLUP 연산자
- GROUP BY 절에 있는 컬럼들의 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고
각 그룹에 계산 함수를 적용한다.
- GROUP BY 절의 결과는 누적 계산 결과이다.
CUBE 연산자
- GROUP BY 절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
#############################@#@#@#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
특정 테이블값을 다른 테이블로 복사할때 ㅋㅋ
SELECT * INTO 바꾼회원 FROM 회원
select * into copy_student from student
into 테이블 이렇게만 넣으면 되네 --;;
#############################@#@#@#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
필드의 크기 알아보기
select datalength(content) from board
#############################@#@#@#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
실행 시간 알아내기
SET STATISTICS IO ON
SET STATISTICS TIME ON
테이블 'TB_Board'. 검색 수 1, 논리적 읽기 수 485, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 34497, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 105.
SQL Server 실행 시간:
CPU 시간 = 94ms, 경과 시간 = 3270ms.
#############################@#@#@#@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
ㅁ 전체텍스트 검색을 위한 기본 쿼리
USE DATABASE_NAME
-- 1. 데이터베이스 전체 텍스트 인덱싱 사용(↔disable) 설정
EXEC sp_fulltext_database 'enable'
-- 2. 전체 텍스트 카탈로그인 Catalog 테이블 생성(↔drop)
-- sp_fulltext_catalog 사용법 참조
EXEC sp_fulltext_catalog 'Catalog_Tabel_Name', 'create'
-- 3. 사용할 테이블(TMP01)의 메타 데이터 생성
-- Catalog_Tabel_Name라는 카탈로그에 생성
-- TMP01의 Primary Key 값(해당 테이블의 PK 정보 참조) 이용
EXEC sp_fulltext_table 'TMP01', 'create', 'Catalog_Tabel_Name', 'PK_Value'
-- 3-1. TMP01의 전체 텍스트 메타 데이터를 삭제한다.
-- EXEC sp_fulltext_table 'TMP01', 'drop'
-- 4 .TMP01의 Column01을 전체 텍스트 인덱싱에 추가(↔drop)
-- Column01은 ''로 막지 않는다.
EXEC sp_fulltext_column 'TMP01', Column01, 'add'
-- 5. Catalog_Tabel_Name 카탈로그의 전체 채우기 시작
EXEC sp_fulltext_catalog 'Catalog_Tabel_Name', 'start_full'
예제)
USE HSITMS
EXEC sp_fulltext_database 'enable'
-- 2. 전체 텍스트 카탈로그인 Cal_ANC_MTT라는 빈 테이블 생성
EXEC sp_fulltext_catalog 'Cal_ANC_MTT', 'create'
-- 3. MNG_ANC_MTT 테이블의 메타 데이터를 Cal_ANC_MTT의 카탈로그에 생성성
-- 'PK__MNG_ANC_MTT__1C0818FF'는 MNG_ANC_MTT 테이블의 PK 정보임
EXEC sp_fulltext_table 'MNG_ANC_MTT', 'create', 'Cal_ANC_MTT', 'PK__MNG_ANC_MTT__1C0818FF'
-- 4 .MNG_ANC_MTT 테이블의 CONT 열을 전체 텍스트 인덱싱에 추가
EXEC sp_fulltext_column 'MNG_ANC_MTT', CONT, 'add'
-- 5. Cat_Desc 카탈로그의 전체 채우기 시작
EXEC sp_fulltext_catalog 'Cal_ANC_MTT', 'start_full'
ㅁ 불필요한 어미 삭제후 검색(contains관련 메뉴얼 참고)
select *
from Table_Name
where contains (Column01,'"어간+어미형의 구문 등"')
예제)
select *
from MNG_ANC_MTT
where contains (cont,'"업무에 필요한"')
ㅁ 가중치 부여 검색
select *
from Table_Name A inner join FREETEXTTABLE (Table_Name, CONT,' ISABOUT(구문01 WEIGHT (가중치01), 구문02 WEIGHT (가중치02), 구문03 WEIGHT (가중치03)) ' ) AS RK
ON A.PK_NAME = RK.[KEY]
ORDER BY RK.RANK DESC
예제)
select *
from MNG_ANC_MTT A inner join FREETEXTTABLE (MNG_ANC_MTT, CONT,' ISABOUT(업무에 WEIGHT (.3), 참조해 WEIGHT (.5), 정보를 WEIGHT (.2)) ' ) AS RK
ON A.ANC_NO = RK.[KEY]
ORDER BY RK.RANK DESC
ㅁ 기타
isnull(column명,'바꿀문자') column이 null일 경우 바꿀 문자로 바꿈
A. CONTAINS와 <simple_term> 사용
다음은 가격이 $15.00이고 "bottles"라는 단어가 포함된 모든 제품을 검색하는 예제입니다.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
AND CONTAINS(QuantityPerUnit, 'bottles')
GO
B. CONTAINS와 <simple_term>의 구 사용
다음은 "sasquatch ale" 또는 "steeleye stout" 구가 포함된 모든 제품을 반환하는 예제입니다.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO
C. CONTAINS와 <prefix_term> 사용
다음은 ProductName 열에서 choc 접두사로 시작하는 단어가 하나 이상 포함된 모든 제품 이름을 반환하는 예제입니다.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO
D. CONTAINS와 OR, <prefix_term> 사용
다음은 "sea" 또는 "bread" 문자열이 포함된 모든 범주 설명을 반환하는 예제입니다.
USE Northwind
SELECT CategoryName
FROM Categories
WHERE CONTAINS(Description, '"sea*" OR "bread*"')
GO
E. CONTAINS와 <proximity_term> 사용
다음은 "spread"와 "Boysenberry"라는 단어가 서로 근접해 있는 제품 이름을 모두 반환하는 예제입니다.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, 'spread NEAR Boysenberry')
GO
F. CONTAINS와 <generation_term> 사용
다음은 dried, drying 등 dry에서 파생된 단어가 포함된 모든 제품을 검색하는 예제입니다.
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO
G. CONTAINS와 <weighted_term> 사용
다음은 spread, sauces, relishes 단어가 포함된 모든 제품 이름을 검색하는 예제로서 단어별로 각기 다른 가중치가 지정됩니다.
USE Northwind
GO
SELECT CategoryName, Description
FROM Categories
WHERE CONTAINS(Description, 'ISABOUT (spread weight (.8),
sauces weight (.4), relishes weight (.2) )' )
GO
H. CONTAINS와 변수 사용
다음은 특정 검색 용어 대신 변수를 사용하는 예제입니다.
USE pubs
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE CONTAINS(pr_info, @SearchWord)
출처 : Tong - 삶은닥알님의 [Oracle] 팁통
@@@@@@@@@@@@#######################################
sp_who
현재 디비에 접속되어있는 세션
@@@@@@@@@@@@#######################################
mssql 버전 정보 보기
SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'
@@@@@@@@@@@@#######################################
IP번호 끝자리만 바꾸기 기본적으로 222.111. 은 같은 조건으로 봤을때 가능하다
DECLARE @aaa varchar(15)
set @aaa='222.111.8.25'
select SUBSTRING(@aaa,9+(Patindex('%.%',SUBSTRING(@aaa,9,15))),15)
@@@@@@@@@@@@#######################################
순위
그룹단위로 순위를 구분할수 있음.
RANK() OVER (partition by cg_code, cl_code order by (a.ls_point) Desc)
-- 순서대로 카운터
select rowidx, Row_number() over(order by rowidx) from MMS_SET_CATEGORI
-- 해당 순서값을 자신에게 다시 업데이트
UPDATE MMS_SET_CATEGORI SET
sNum = B.UPNum
FROM (SELECT ROWIDX as IDX, Row_number() over(order by ROWIDX) AS UPNum FROM MMS_SET_CATEGORI) AS B
INNER JOIN (SELECT ROWIDX as IDX FROM MMS_SET_CATEGORI) AS C
ON B.IDX=C.IDX
where ROWIDX=C.IDX
@@@@@@@@@@@@####################################### UPDATE case
UPDATE tb_pollTypePaper SET
pt_answer1 = CASE pt_answer1 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer2 = CASE pt_answer2 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer3 = CASE pt_answer3 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer4 = CASE pt_answer4 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer5 = CASE pt_answer5 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer6 = CASE pt_answer6 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer7 = CASE pt_answer7 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer8 = CASE pt_answer8 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer9 = CASE pt_answer9 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end,
pt_answer10 = CASE pt_answer10 WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 4 THEN 2 WHEN 5 THEN 1 else NULL end
where pt_code=695
@@@@@@@@@@@@####################################### GROUP BY 페이징
IF key1 <> "" THEN SEARCHSQL = " AND [CodeName] LIKE '%" & key1 & "%' "
IF Len(key2) = "2" THEN SEARCHSQL = SEARCHSQL & " AND [StoreID] = '" & key2 & "' "
SET RS = DBCON.EXECUTE ("SELECT COUNT([TT].[StoreID]) FROM (SELECT [StoreID] FROM [DBO].[MMS_SALE_OTHER] WHERE CONVERT(CHAR(4),[SaleDate],121) ='" & YDate &"' " & SEARCHSQL & " GROUP BY [StoreID], [CodeName], CONVERT(CHAR(7),[SaleDate],121)) AS [TT] ")
IntTotalCount = CheckNull(RS(0), 0) '전체레코드수
IntPageCount = INT((IntTotalCount - 1) / IntPageSize) + 1 '전체 페이지 수
RS.CLOSE : SET RS=NOTHING
SQL = "SELECT TOP " & IntPageSize & " [GP].[IDX], [GP].[WName], [GP].[CodeName], [GP].[DateYM], [GP].[TotalCost] FROM "
SQL = SQL & "( SELECT ROW_NUMBER() OVER (order by [CodeName]) AS [IDX], [DBO].[MMS_FNC_ReturnName]('WareHouse', storeID) AS [WName], [CodeName], "
SQL = SQL & " CONVERT(CHAR(7),[SaleDate],121) AS [DateYM], SUM([SalePrice]) AS [TotalCost] FROM [DBO].[MMS_SALE_OTHER] WHERE CONVERT(CHAR(4),[SaleDate],121) ='" & YDate &"' "
SQL = SQL & SEARCHSQL
SQL = SQL & " GROUP BY [StoreID], [CodeName], CONVERT(CHAR(7),[SaleDate],121)) AS [GP] WHERE"
SQL = SQL & " [GP].[IDX] NOT IN "
SQL = SQL & " (SELECT TOP " & (IntPage - 1) * IntPageSize & " ROW_NUMBER() OVER (order by [CodeName]) AS [IDX] FROM [DBO].[MMS_SALE_OTHER] "
SQL = SQL & " WHERE CONVERT(CHAR(4),[SaleDate],121) ='" & YDate &"' "
SQL = SQL & SEARCHSQL
SQL = SQL & " GROUP BY [StoreID], [CodeName], CONVERT(CHAR(7),[SaleDate],121) ) "
SQL = SQL & " ORDER BY [GP].[IDX] ASC "
@@@@@@@@@@@@#######################################
set nocount on
SET @TABLE_NAME = 'MMS_' + @TABLE
if (@TABLE = 'Customer') begin
SET @stmt = 'SELECT TOP 1 @TOP = ' + @Columne + 'Id FROM ' + @TABLE_NAME + ' ORDER BY IDX DESC '
end else begin
if (@TABLE = 'C_AREA') begin
SET @stmt = 'SELECT TOP 1 @TOP = ' + @Columne + ' FROM ' + @TABLE_NAME + ' ORDER BY IDX DESC '
END ELSE BEGIN
SET @stmt = 'SELECT TOP 1 @TOP = ' + @Columne + '_Id FROM ' + @TABLE_NAME + ' ORDER BY IDX DESC '
END
end
EXEC sp_ExecuteSQL @stmt, N'@TOP varchar(10) OUTPUT', @TOP OUTPUT
IF (@TOP is NOT NULL) BEGIN
SET @stmt2 = 'SELECT TOP 1 ' + @TWO_THREE + ' AS RET FROM MMS_BASIC_CODE WHERE IDX = (SELECT TOP 1 IDX FROM MMS_BASIC_CODE WHERE ' + @TWO_THREE + ' = (''' + @TOP + ''')) + 1 '
END ELSE BEGIN
SET @stmt2 = 'SELECT TOP 1 ' + @TWO_THREE + ' AS RET FROM MMS_BASIC_CODE WHERE IDX = ' + CAST (@INDEX AS VARCHAR(5))
END
@@@@@@@@@@@@#######################################
간단하게 MS-SQL 에서 지원하는 함수로 가능합니다.
그런데 6조로 나눌경우 남는 두명은 1조부터 차례로 배정됩니다.
참고하세요. ^^*
SELECT NAME , NTILE(6) OVER (ORDER BY NEWID())
FROM MEMBER
NTILE : 정렬된 파티션의 행을 지정된 수의 그룹으로 분산시킵니다
NEWID :uniqueidentifier 형식의 고유 값을 만듭니다. (랜덤으로 정렬을 만들기 위한 함수)
select mid, mname from member order by mid, newid()
@@@@@@@@@@@@#######################################
테이블 용량 체크 내장 프로시져
sp_spaceused TB_MovieBook
@@@@@@@@@@@@#######################################
--
exec sp_who2
DBCC INPUTBUFFER(54) --DBCC INPUTBUFFER(spid)
--lock 프로세스 확인 (X인것이 문제)
exec sp_lock;
--spid 로 세부 검색
dbcc inputbuffer(72);
sp_who 72
--lock 프로세서 제거
--kill 69
@@@@@@@@@@@@#######################################
UPDATE [DBO].[TB_SHOP] SET
[Sp_Name] = '1316 팬클럽 Level 2 (유형)123'
FROM [DBO].[TB_SHOP] WHERE [SP_CODE] = '0000000322'
@@@@@@@@@@@@###### 인덱스 조각화 확인 및 재구성 #################################
--테이블에 걸린 인덱스 확인법
sp_helpindex TB_Online_Student
--인덱스 삭제
drop index TB_classreceipt.IX_TB_ClassReceipt_1
--인덱스 정보확인
EXEC SP_HELPINDEX tb_booksell
--테이블 인덱스 세부정보
DBCC SHOW_STATISTICS (tb_classreceipt,IX_TB_ClassReceipt_1)
--테이블의 인덱싱 상세 확인
DBCC SHOWCONTIG(tb_booksell, IX_TB_BookSell)
--인덱스재구성및재작성
DBCC DBREINDEX(tb_booksell, PK__TB_BookSell__117F9D94)
@@@@
DBCC DBREINDEX (테이블명,,채우기비율)
ex) DBCC DBREINDEX (T_table, ,80) - T_table의 모든 인덱스를 채우기 비율(Fillfactor) 80% 로 재작성하라는 의미임
- 만약 Fillfactor 를 이전에 지정하지 않았다면 채우기비율을 "0"으로 셋팅하면 됩니다. 괜히 채우기 비율 지정했다가 낭패를 보는 수가 있으니깐요..ㅎㅎ
- 장점 : 경험상 위 보다 성능상 더 좋음
- 단점 : 테이블에 LOCK을 발생시키므로 온라인 상에서 하기에는 부담이 온다(최대한 사용자가 적을 때 사용해야 함)
@@@@
--논리 조각화 상태 검색
SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, b.name, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
항목 설명
스캔한 페이지 1페이지 = 8KB 이므로 스캔한 페이지는 800KB
스캔한 익스텐트 익스텐트 = 1페이지 * 8 이므로 17 * 8KB * 8 = 1088 KB
전환된 익스텐트 익스텐트를 스캔하면서 익스텐트와 익스텐트 사이를 옮겨간 횟수를 의미.
익스텐트 당 평균 페이지 수 하나의 익스텐트는 8개의 페이지라고 볼 수 있는데 익스텐트가 조각나서 하나의 익스텐트에 평균 5.9개의 페이지가 있는 것
스캔밀도 스캔밀도가 100% 이면 조각난 상태가 아니며 100% 미만이면 그 만큼 조각난 상태. 그러므로 위의 경우 약 25%가 조각난 상태라고 볼 수 있다.|
논리 스캔 조각화 상태 논리적인 조각화 상태를 의미합니다. 삽입, 갱신 등의 DML문에 의해서 인덱스의 트리구조가 전환된 것을 의미.
익스텐트 스캔 조각화 상태 인덱스의 잎 페이지 스캔에서 순서가 바뀐 익스텐트의 비율.
페이지 당 사용 가능한 평균 바이트 수 하나의 페이지는 최대 8060바이트가 저장될 수 있다. 이것은 페이지들이 사용할 수 있는 가용 페이지의 공간을 의미.
평균 페이지 밀도(전체) 스캔한 페이지의 조각화 상태. 100%이면 조각난 상태가 아니며, 100% 미만이면 조각난 상태.
출처 : http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b764b20b-6c09-40d3-b977-759c49ee0c66
-> 권장 사항 : 조각화 정도가 5%~30% 일 때 재구성(Reorg), 30% 를 초과하면 재작성(Rebuild)
@@@############################################################################
DBCC DBREINDEX
★작업시 주의사항
해당작업시 lock 으로 인하여 테이블 사용이 안되니 되도록이면 사용자가 없을때 야간 에 해야 함.
해당테이블 인덱스 상태 -> DBCC SHOWCONTIG (@TableName)
인덱스상태 -> exec sp_spaceused 'user_table'
1.인덱스 다시 작성
Employee_EmployeeID clustered index with a fill factor of 80 on the Employee table in the AdventureWorks database.' xml:space="preserve">다음 예에서는 AdventureWorks 데이터베이스의 Employee 테이블에 채우기 비율 80의 Employee_EmployeeID 클러스터형 인덱스를 다시 작성하는 방법을 보여 줍니다.
Transact-SQL
복사
USE AdventureWorks2012; GODBCC DBREINDEX ("HumanResources.Employee", PK_Employee_BusinessEntityID,80);GO
2.모든 인덱스 다시 작성
Employee table in AdventureWorks by using a fill factor value of 70.' xml:space="preserve">다음 예에서는 채우기 비율 값 70을 사용하여 AdventureWorks의 Employee 테이블에서 모든 인덱스를 다시 작성하는 방법을 보여 줍니다.
Transact-SQL
복사
USE AdventureWorks2012; GODBCC DBREINDEX ("HumanResources.Employee", " ", 70);GO
-> 원래 fillfactor 가 0 이었다면 DBCC DBREINDEX ("HumanResources.Employee", " ", 0);
참고 : http://www.underroom.com/index.php?mid=mssql&document_srl=3370
[출처] database 성능향상 dbreindex|작성자 천재
@@@############################################################################
-- 해당 테이블 인덱스 재구성
declare @tableName varchar(50)
set @tableName = 'TB_DayPersonReceipt'
declare indexName cursor for
select i.name from sysindexes i, sysobjects t where t.id = i.id and ( i.name like 'PK%' OR i.name like '%IX%' ) and t.name = @tableName
open indexName;
declare @indexName varchar(50);
fetch next from indexName into @indexName;
while @@FETCH_STATUS = 0
begin
--처리문
select @indexName
DBCC DBREINDEX (@tableName, @indexName)
fetch next from indexName into @indexName;
end;
close indexName;
--100개가 넘으면 오류가 남.. 사용하긴 좀...
--전체 테이블 인덱스 재구성
declare tableName cursor for
select o.name as tableName from sysobjects o where not o.name like 'sys%' and o.xtype = 'U' order by o.name
open tableName;
declare @tableName varchar(50);
fetch next from tableName into @tableName;
while @@FETCH_STATUS = 0
begin
--처리문
declare indexName cursor for
select i.name from sysindexes i, sysobjects t where t.id = i.id and ( i.name like 'PK%' OR i.name like '%IX%' ) and t.name = @tableName
open indexName;
declare @indexName varchar(50);
fetch next from indexName into @indexName;
while @@FETCH_STATUS = 0
begin
--처리문
select @tableName,@indexName
DBCC DBREINDEX (@tableName, @indexName)
fetch next from indexName into @indexName;
end;
close indexName;
DEALLOCATE indexName;
fetch next from tableName into @tableName;
end;
close tableName;
DEALLOCATE tableName;
@@@@@@@@@@@@@@##### 저장 프로시져(sp) 내용 검색##################################
select o.name
from sysobjects o join syscomments c
on o.id = c.id
where o.type = 'P' and c.text like '%텍스트%'
@@@@@@@@@@@@@@##### 저장 프로시져(sp) 내용 검색##################################
Apply 구문에는 Cross Apply 와 Outer Apply가 존재하며
차이는 Inner Join 과 Outer Join 과 동일함
--기본 쿼리
select * From
tb_booksell as a
left outer join tb_bookselldetail as b on b.bs_code=a.bs_code
where b.bs_code is null
--변경 쿼리 (빠름)
select * from
tb_booksell as a
outer apply (select top 1 bs_code from dbo.tb_bookselldetail as b where b.bs_code=a.bs_code) as c
where c.bs_code is null
또한 상관쿼리형식으로 내부에서 as a의 값을 연동시킬수 있는 장점이 있다
상관쿼리 업데이트
UPDATE TBL_HEOWON [M] SET
NAME = (SELECT NAME FROM TBL_HEOWON_BACKUP WHERE ID = [M].ID),
ADDR = (SELECT ADDR FROM TBL_HEOWON_BACKUP WHERE ID = [M].ID)
@@@@@@@@@@@@@@##### 저장 프로시져(sp) 내부에서 자신의 프로시 이름을 구하기 ##################################
DECLARE @ProcName NVARCHAR(256)
SELECT @ProcName = OBJECT_NAME(@@PROCID)
@@@@@@@@@@@@@@##### 쿼리 모니터링 (쿼리로 가능) ##################################
많은 분들이 이미 아시다시피 MSSQL2005부터
서버 모니터링을 위해 몇 가지 기능을 제공하고 있습니다.
그것이 바로 DMV(Dynamic Management View)와 DMF(Dynamic Management Function)입니다.
자세한 내용은 MSDN을 참조
http://msdn.microsoft.com/ko-kr/library/ms188754(SQL.90).aspx
MSSQL2000이전 실행 Query를 확인하기 위해선 Profiler라는 도구를 이용하는 수 밖에 없었는데
이제 T-SQL로도 확인이 가능하능해진 거죠.
그렇다 하여 Profiler와 같이 모든 Query를 모니터링할 수 있는 것은 아닙니다.
단지, 캐쉬된 실행계획에 있는 녀석을 가져올 뿐이죠.
(이 말은 물론 캐쉬에서 실행 계획이 제거되면 뷰에서도 제거가 된다는 말입니다.)
각설하고,
이제 Query를 가져오기 위해 이용할 DMV는
sys.dm_exec_query_stats와 sys.dm_exec_sql_text 입니다.
먼저 두 녀석을 실행해보죠.
(SELECT 필드는 MSDN을 참조해서 변경하시면 됩니다)
SELECT creation_time,
last_execution_time,
SUBSTRING(st.TEXT, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text,
total_worker_time,
execution_count,
total_physical_reads,
total_logical_writes,
total_elapsed_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
실행 결과 입니다.
참고로 캐쉬를 삭제 후 다시 실행해 보죠.
DBCC freeproccache --캐쉬를 삭제하면 내용은 삭제된다.
@@@@@@@@@@@@@@##### 힌트주는 법 ##################################
MSSQL 에서 WITH로 HINT주는 법
SELECT * FROM DBO.[TABLE1] WITH (NOLOCK)
= SELECT * FROM DBO[TABLE1] TB1 WITH (NOLOCK)
= SELECT * FROM DBO.[TABLE1] (NOLOCK)
= SELECT * FROM DBO.[TABLE1] (NOLOCK) TB1
= SELECT * FROM DBO.[TABLE1] TB1 (NOLOCK)
-- WITH 를 ALIAS NAME 앞에 사용하게 되면 오류 발생
?!= SELECT * FROM DBO.[TABLE1] WITH (NOLOCK) TB1
-- LEFT OUTER JOIN 을 사용할 시에 기술 방법
SELECT *
FROM DBO.[TABLE1] TB1 WITH (NOLOCK)
LEFT JOIN DBO.[TABLE2] TB2 WITH (INDEX=[INDEX_NAME], NOLOCK)
ON TB2.[COL1] = TB1.[COL2]
@@@@@@@@@@@@@@##### 테이블 조건이 많은경우 WITH 로 미리 묶어서 사용 가능 ##################################
WITH [TABLEC]([SDATE],[CHARTID],[CVA_R]) AS
(SELECT [SDATE],[CHARTID],[CVA_R] FROM [D2SK].[dbo].[LS_PRESCRIPTION] WHERE [USED]= 'Y' AND [CVA_R] <> '')
UPDATE [MiERP].[dbo].[L_ORDER_PRE]
SET [CVA_OD] = [CVA_R]
FROM [MiERP].[dbo].[L_ORDER_PRE] AS [A]
JOIN [TABLEC] AS [C]
ON [A].[SDATE] = [C].[SDATE] AND [A].[CHARTID] = [C].[CHARTID] ;
@@@@ 추가 내용 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
WITH문으로 쿼리를 임시테이블 처럼 사용
WITH 임시테이블명 AS
(
SELECT * FROM EMPLOYEE
)
SELECT * FROM 임시테이블명
위의 문장은 WITH문의 기본문법이다.
이렇게도 사용가능하다.
WITH TEST_1 AS
(
SELECT * FROM EMPLOYEE1
),
WITH TEST_2 AS
(
SELECT * FROM EMPLOYEE2
)
SELECT STAFF_NAME, STAFF_NUMBER, STAFF_ADDRESS
FROM TEST_1 A, TEST_2 B
WHERE A.STAFF_NUMBER = B.STAFF_NUMBER
@@@@@@@@@@ varchar text내용의 값을 변경할때
/**********************************************************/
update TB_BOARD set
bo_content=replace(cast(bo_content as varchar(max)),'''''''''','')
where bo_content like '%''''''''''''%'
@@@@@@@@@@ PIVOT(피벗) 세로를 가로내용으로
SELECT CREATE_DAY, COMP_CODE, COUNT(USER_ID) AS USER_CNT
FROM TB_VISITED_LOG
WHERE COMP_CODE IS NOT NULL
GROUP BY COMP_CODE, CREATE_DAY
ORDER BY CREATE_DAY DESC
_ _ _ _ _ _ _ _ _ _ _ __ _ _ _ _ __ _ __
DECLARE @Prod varchar(2000)
SELECT @Prod = ''
SELECT @Prod = @Prod + '[' + COMP_CODE + '],'
FROM (SELECT Distinct COMP_CODE FROM TB_VISITED_LOG WHERE COMP_CODE IS NOT NULL) A
SELECT @Prod = LEFT(@Prod, LEN(@Prod) - 1)
DECLARE @NSQL NVARCHAR(MAX)
SELECT @NSQL = N'
SELECT CREATE_DAY, ' + @Prod + '
FROM (
SELECT CREATE_DAY, COMP_CODE, ISNULL(COUNT(USER_ID), 0) AS CNT
FROM TB_VISITED_LOG
WHERE COMP_CODE IS NOT NULL
GROUP BY CREATE_DAY, COMP_CODE
) T
PIVOT (MAX(CNT) FOR COMP_CODE IN (' + @Prod + ')
) AS PVT
ORDER BY CREATE_DAY DESC
'
PRINT @NSQL
EXECUTE dbo.SP_EXECUTESQL @NSQL
아래가 이해가 쉽징
select * from Sales
1 Apr 3955.67
1 Aug 1554.12
1 Dec 5649.75
1 Feb 987.50
1 May 3318.13
1 Nov 2309.09
1 Oct 4802.03
1 Sep 4158.51
2 Apr 1764.80
2 Aug 3977.50
2 Dec 1237.02
2 Jul 3091.09
2 Jun 4695.95
3 Feb 7479.35
3 Jan 5703.59
3 Jul 867.48
3 Jun 9459.18
SELECT PivotData.*
FROM Sales
PIVOT
(Sum(Amount)
FOR MonthName IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
) PivotData
GO
===============================
2005부터 생긴 PIVOT 기능을 이용하여 그룹별 데이터를 가로로 표시할 수 있게 되었습니다.
엑셀을 아시는분들은 피벗테이블 이라고 생각하시면 되겠네요~
아래의 샘플쿼리를 쿼리분석기에서 테스트 해보시면 별로 어렵지 않게 이해하실 듯 합니다.
업무하시는데 조금이나마 도움이 되었으면 하네요~ ^^
-------------------------------------------------------------------------------
DECLARE @table1 TABLE
(
code VARCHAR(4),
date INT,
pri int
)
insert @table1 select 'GG01',1,10
insert @table1 select 'GG01',2,20
insert @table1 select 'GG01',3,30
insert @table1 select 'GG02',1,40
insert @table1 select 'GG02',2,50
insert @table1 select 'GG02',3,60
insert @table1 select 'GG03',1,70
insert @table1 select 'GG03',2,80
insert @table1 select 'GG03',3,90
--원본 테이블
SELECT * FROM @table1
--code별 일자에대한 금액
SELECT code,[1],[2],[3] FROM @table1
PIVOT (SUM(pri) FOR date IN ([1],[2],[3]) ) AS pvt
############################# 이런 조건도 좋네그려 ##########################################
AND (CASE WHEN (@sGRADE = '') THEN '' ELSE SCLV.CL_grade END) = @sGRADE
############################# 제한시간 만료시 ##########################################
제한시간이 만료 되었습니다.
메뉴 > 도구 > 옵션 > 디자이너 > 테이블 및 데이터베이스 ... > 트랜젝션 제한 시간을 늘려줌
############################# mssql에서 mysql 서버 연결 ##########################################
exec sp_addlinkedserver
@server='Whistle',
@srvproduct = 'mysql',
@provider = 'MSDASQL',
@provstr = 'DSN=Whistle;server=222.111.8.98;database=Whistle;UID=whistle;PWD=whistle~sql'
--sp_addlinkedsrvlogin 'Whistle', 'false', NULL, 'whistle', 'whistle~sql'
--연결된 서버보기
select * from master.dbo.sysservers
--mysql 서버 가보기
select * from Whistle...sms_message_tbl
연결된 서버의 Table을 OpenQuery를 이용하여 조회하고 등록하고 수정하고 삭제해보겠다.
## Select
SELECT * FROM OPENQUERY(L_TESTDB, 'SELECT * FROM LinkedServerTestTable');
## Insert
INSERT OPENQUERY(L_TESTDB, 'SELECT NO, NAME FROM LinkedServerTestTable ') VALUES (1, '홍길동');
## Update
UPDATE OPENQUERY(L_TESTDB, 'SELECT NAME FROM LinkedServerTestTable WHERE NO = 1') SET NAME = '임꺽정';
## Delete
DELETE FROM OPENQUERY(L_TESTDB, 'SELECT NO FROM LinkedServerTestTable WHERE NO = 1');
어렵지않다. 찾으면 쉽게 알 수 있다.
공부하자~~!!!!
########## 추가 ##########
프로시저에서 openquery를 이용하여 update하는데, where절에 NO = @P_NO 파라미터를 넣었더니 바로 에러가 나더라는.....
그래서 확인해보니 openquery에는 string 만들어가야 한다는 사실을 알게 되었다.. 그놈 참... 까다롭네그려... ㅡㅡ;;;
그래서
@_QUERY = '
UPDATE OPENQUERY(L_TESTDB, 'SELECT NAME FROM LinkedServerTestTable WHERE NO = '+@SMS_SEQ+'')
SET NAME = '''+@NAME+'' '
Execute sp_executesql @_QUERY, @_PARAMS
주의사항 : 파라미터 부분에 작은 따옴표 " ' "를 넣을때 특수문자여서 하나더 넣어야한다는거 다들 아시지요??
@SMS_SEQ가 스트링일 경우, '''''+@SMS_SEQ+''''' 요렇게...
@##@@@@@@@@@@@@@@@@@@@@@@@@@@ 메뉴 순서 정리 #2@@@@@@@@@@@@@@@@@@@@@@@@@@
UPDATE tb_menugroupdet SET
md_alignno = B.mdno
FROM (SELECT md_code as mdCode, Row_number() over(order by md_alignno) AS mdno FROM tb_menugroupdet where mg_code='SF' and br_code='SL01'
) AS B
INNER JOIN (SELECT md_code as mdCode FROM tb_menugroupdet) AS C
ON B.mdCode=C.mdCode
where md_code=C.mdCode and mg_code='SF' and br_code='SL01'
@##@@@@@@@@@@@@@@@@@@@@@@@@@@ DB복구시 사용자 매핑 #2@@@@@@@@@@@@@@@@@@@@@@@@@@
복구된 데이터베이스는 기존의 mssql 시스템의 사용자를 기억해서 복구되는 mssql 시스템에서의 같은 사용자를 이름만 같은 다름 사용자로 인식을 한다
그래서 같은 사람이라고 말해주면 된다.
즉, 매핑을 시켜주면 된다.
그러기위해 사용하는 명령어가 sp_change_users_login 이다.
ex)
USE 해당 계정
go
EXEC sp_change_users_login 'Update_One', 'hisuser', 'hisuser'