긍적적인 사고, 음식의 절제, 규칙적인 운동

SQL Server 2005 TSQL - 세미나 자료

[ 관련 PPT ]

[ 관련 SQL query ]

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

INSERT INTO tbl_zipcode
([zipcode]
,[sido]
,[gugun]
,[dong]
,[bunji])

SELECT [우편번호], [일련번호], [주 소],[F4], isnull([F5],'') +' '+ isnull([F6],'') +' '+ isnull([F7],'') +' '+
isnull([번지],'') +' '+ isnull([아파트/건물명],'') FROM 고시형주소 where 우편번호 is not null

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

mssql은 테이블 정보에 기본값을 getdate()로 할 경우 값이 입력되면 자동으로 현재 일자와 일시가 입력되는데 mysql은 이와 같은 기능을 하는 함수가 있나요?


//----------------------------------------



CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise — that is, if the TIMESTAMP column is defined to allow NULL values but not using DEFAULT TIMESTAMP, as shown here…

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
…then you must explicitly insert a value corresponding to the current date and time. For example:

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

WITH APP_CUSTOM( ADIDX, ADSPONSORNAME, ADTYPE, ADPHONE ) AS (



SELECT idx as 'adidx', adcompanyname as 'adsponsorname', adtype1 as 'adtype', adphone as 'adphone'
FROM TBL_ADVERTISING_MEMBER
union all
SELECT '0' as 'adidx', pubname as 'adsponsorname',
( SELECT SESE_NAME FROM tbl_addtel_upjong where sese_code = ATB.Yp) as 'adtype',
( AreaCode + '-' + TelKuk + '-' + TelNo ) as 'adphone'
FROM TBL_ADDTELBOOK AS ATB


)
select * from
( select ADIDX, ADSPONSORNAME, ADTYPE, ADPHONE,row_number() over ( order by ADSPONSORNAME asc ) as 'ROWNUMBER'
from APP_CUSTOM
) as a where ROWNUMBER < 100

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

DMVStats 1.01
A SQL Server 2005 Dynamic Management View Performance Data Warehouse

http://www.codeplex.com/sqldmvstats


[설치 파일 다운로드]

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

SQL Server Best Practices

http://technet.microsoft.com/ko-kr/sqlserver/bb331794.aspx



[ 설치 파일 다운로드 ]

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

SQL Server 2005 Performance Dashboard Reports
- DashBoard 를 사용하기 위해서는 SQL Server 2005 sp2 가 설치되어 있어야 한다.

아래의 링크에서 DashBoard 를 다운받는다.

http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en

설치후 설치 경로는 아래와 같다.

C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashBoard

Sql 서버에 접속을 하는데, 연결계정은 sysadmin 역활을 가지고 있어야 한다.

SQL Management Studio 에서 "새 쿼리창"을 열어 다음의 쿼리 파일을 실행한다.
C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashBoard\setup.sql

SQL Management Studio 의 탐색창에서 해당 데이터베이스를 선택하고 마우스 오른쪽 버튼을 클립한다.
나타나는 메뉴에서 [보고서] - [Performance DashBoard] 를 선택한다.


pen file 대화상자에서 다음의 파일을 선택하고 [확인]을 클릭한다.
C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashBoard\performance_dashboard_main.rdl

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

동적 관리 뷰 및 함수는 서버 인스턴스 상태 모니터링, 문제 진단 및 성능 튜닝에 사용할 수 있는 서버 상태 정보를 반환합니다.

동적 관리 뷰 및 함수에는 다음과 같은 두 유형이 있습니다.

1. 서버 범위 동적 관리 뷰 및 함수. 이 유형에는 서버에 대한 VIEW SERVER STATE 권한이 필요합니다.

2. 데이터베이스 범위 동적 관리 뷰 및 함수. 이 유형에는 데이터베이스에 대한 VIEW DATABASE STATE 권한이 필요합니다.

동적 관리 뷰 또는 함수를 쿼리하려면 개체에 대한 SELECT 권한과 VIEW SERVER STATE 또는 VIEW DATABASE STATE 권한이 필요합니다. 이러한 사용 권한을 통해 동적 관리 뷰 및 함수에 대한 사용자 또는 로그인의 액세스를 선택적으로 제한할 수 있습니다. 이렇게 하기 위해서는 먼저 master에 사용자를 만든 다음 사용자가 액세스할 수 없도록 할 동적 관리 뷰 또는 함수에 대한 사용자의 SELECT 권한을 거부합니다. 그러면 사용자의 데이터베이스 컨텍스트에 관계없이 사용자가 이러한 동적 관리 뷰 또는 함수에서 선택할 수 없게 됩니다.
- DENY가 우선 적용되기 때문에 사용자에게 VIEW SERVER STATE 권한을 부여했지만 VIEW DATABASE STATE 권한을 거부한 경우 해당 사용자는 서버 수준 정보는 볼 수 있지만 데이터베이스 수준 정보는 볼 수 없습니다.

모든 동적 관리 뷰 및 함수는 sys 스키마에 있어야 하며 dm_* 명명 규칙을 따라야 합니다. 동적 관리 뷰 또는 함수를 사용할 경우 뷰 또는 함수 이름에 sys 스키마 접두사를 지정해야 합니다. 예를 들어 dm_os_wait_stats 동적 관리 뷰를 쿼리하려면 다음 쿼리를 실행합니다.

SELECT wait_type, wait_time_ms

FROM sys.dm_os_wait_stats

GO

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

TOP (<식>)
- TOP 에 변수 사용 가능
- 구문
TOP (expression) [PERCENT]
[WITH TIES]



Common Table Expression
- 명명된 테이블 식
- 구문:
WITH ( )
AS ( )

- WITH 절은 다은에 이어지는 SELECT/INSERT/DELETE/UPDATE 구문과 한 문장임
- 단일 WITH 절에서 다중의 CTE를 하나의 T-SQL 문에 정의 가능함.
- 두 가지 유형
재귀 CTE: 쿼리에서 재귀호출이 가능하도록 해 줌
비재귀 CTE: 파생된 테이블과 뷰의 특성이 혼합된 기능임.

- 가 자신을 참조 -> 재귀
- CTE의 재귀형식
<비재귀 SELECT>
union all

- 두번째 SELECT문의 결과가 없을 때 재귀 호출이 중단됨


오류처리
@@ERROR
- 전역변수
- 마지막으로 실행된 T-SQL 문의 오류번홀르 반환함
- T-SQL 문이 성공적으로 실행된 경우 -> @@ERROR = 0
- T-SQL 문이 실행될 때마다 새로운 값이 설정됨
- 제약 사항
SQL문 다음에 매번 @@ERROR 값을 검사해야함
GOTO 문과 Label 을 사용하기 때문에 중앙 집중화된 오류처리를 구현하게 됨
트랜잭션 또는 일괄처리를 중단시키는 오류의 경우에는 오루 처리가 되지 않음.
자세한 오류 정보가 제공되지 않음.

TRY ~ CATCH
- TRY 블록 영역
begin try ~ end try 사이에 있는 코드
- CATCH 블록영역
begin catch ~ end catch 사이에 있는 코드
- TRY 블록영역 내에서 오류가 발생
실행이 관련 CATCH블록으로 이동함
- TRY...CATCH 구조는 중첩 가능함
- CATCH 블록영역 내에서 오류가 발생
중첩 try...catch 구조의 경우에는 가장 가까운 catch 블록으로 이동함
중첩 구조가 아닌 경우에는 호출자에게 오류가 반환됨
- TRY...CATCH가 포착 가능한 오류
@@ERROR 값 설정이 발생하는 모든 런타임 오류

- CATCH 블록영역에서 사용 가능한 함수
error_number() - 오류번호
error_message() - 오류 메시지
error_severity() - 오류 심각도
error_state() - 오류 상태 번호
error_line() - 오류를 발생시킨 줄 번호
error_procedure() - 오류가 발생한 저장 프로시저 또는 트리거의 이름
- CATCH 블록영역의 외부에서 실행하면 NULL이 반환됨.

TRY...CATCH가 처리할 수 없는 오류
- 경고메시지 및 정보 제공 메시지
- 데이터베이스 연결을 끊는 오류
- Attentions ( 클라이언트 인터럽트 요청 또는 손상된 클라이언트 연결 )
- KILL 문
- 컴파일 및 SQL 문 차원의 재컴파일 오류

예 :
-- 일반 select
USE AdventureWorks;
GO

BEGIN TRY
-- Table does not exist; object name resolution
-- error not caught.
SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH

-- 프로시저에서 사용

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL
DROP PROCEDURE usp_ExampleProc;
GO

-- Create a stored procedure that will cause an
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
SELECT * FROM NonexistentTable;
GO

BEGIN TRY
EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH;




커밋 불가 트랜잭션
- TRY 블록 내에서의 트랜잭션 중단 오류
- 커밋불가 트랜잭션
커밋될 수 없는 모든 활성 트랜잭션
커밋 불가 트랜잭션에서는 오직 읽기 또는 ROLLBACK TRANSACTION만 실행가능
- 쓰기 작업 또는 COMMIT TRANSACTION은 실행 불가

트랜잭션 상태 조회
XACT_STATE()
- 트랜잭션의 상탤르 반환함
- 1 = 활성상태의 커밋 가능한 트랜잭션
- 0 = 비활성 트랜잭션
- -1 = 활성 상태의 커밋 불가한 트랜잭션



예외처리 - RAISERROR
- RAISERROR : 오류를 발생시키는데 사용됨
- RAISERROR 는 TRY 또는 CATCH 블록에서 사용함
- TRY 블록에서 RAISERROR 를 사용
심각도 11-19 인 RAISERROR 가 실행되면 CATCH 블록으로 이동함
- CATCH 블록에서 RAISERROR를 사용
RAISERROR 를 사용하여 오류를 호출자에게 반환함

오류처리 가이드
- TRY...CATCH 구조를 사용하여 오류 처리 로직을 구현할 것을 권고함
- 기존의 @@ERROR 기반의 오류처리코드를 TRY...CATCH로 대체할 것을 권고함
- 오류정보를 기록할 것을 권고함
문제점을 진단하기 위해서는 오류정보가 필요함
심각한 오류는 테이블에 기록을 남길 것을 권고함
- CATCH 블록 코드를 테스트할 것을 권고함

RANKING 함수
- RANK()
결과 집합의 각 행에 대하여 지정된 파티션 내에서 정렬기준에 의거하여 등급을 부여하는 함수
- DENSE_RANK()
결과집합의 각 행에 대하여 지정된 파티션 내에서 정렬 기준에 의거하여 연속적인 등급을 반환하는 함수
- NTILE()
결과 집합의 각 파티션에 잇는 행들을 지정한 개수만큼의 그룹으로 쪼개어 등급을 부여하는 함수
- ROW_NUMBER()
결과 집합의 각 행에 대하여 행의 우치 번호를 반환하는 함수

- 집계 작업에 사용 가능
- 사용자 정의 함수와 함께 사용 가능

예:
SELECT
RANK() OVER(ORDER BY CITY) AS RANK,
RANK() OVER(PARTITION BY CITY ORDER BY LASTNAME) AS PART_RANK,
DENSE_RANK() OVER(ORDER BY CITY) AS DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY CITY) AS ROW_NUM,
NTILE(4) OVER(ORDER BY CITY) AS NTILE_4,
LASTNAME,
FIRSTNAME,
CITY
FROM EMPLOYEES
ORDER BY CITY, LASTNAME

- OVER 절
파티션 방식 제공
결과 집합의 행들을 정렬

페이징
예: 50에서 60까지의 주문 정보를 일자순으로 정렬
WITH OrderedOrders AS
(select SalesOrderID, OrderDate, Row_Number() OVER(order by OrderDate) as RN from SalesOrderHeader)
select * from OrderedOrders WHERE RN between 50 and 60

PIVOT

UNPIVOT

APPLY

OUTPUT
- DML 연산의 일부로서, 행을 반환하는 기능임
- 작업의 결과를 확인 가능함
"Inserted" 와 "Deleted" 를 사용하여 업데이트 전후의 데이터를 확인 가능함.
- 결과 행을 테이블 또는 변수에 저장 가능함
OUTPUT...INTO...
- 예:
USE AdventureWords;
GO
DELETE SalesShoppingCartItem OUTPUT DELETED.*;
SELECT COUNT(*) AS [Rows in Table] FROM SalesShoppingCartItem;
GO

EXCEPT 와 INTERSECT
- 두 개의 서로 다른 행 집합들을 기반으로 결과 집합을 제공하는 집합 연산자임.
- EXCEPT
첫 번째 결과 집합에는 존재하고 두번째 결과 집합에는 존재하지 않는 행들의 집합을 반환함.
- INTERSECT
양쪽 집합에 모두 존재하는 행들의 집합을 반환함.
- 전제 조건
모든 쿼리의 컬럼들의 수와 순서가 동일해야 함.
데이터 타입이 호환되어야 함
- 예 :
SELECT * FROM TABLEA EXCEPT SELECT * FROM TABLEB;
SELECT * FROM TABLEA INTERSECT SELECT * FROM TABLEB;



UNION, EXCEPT, INTERSECT 다른 T-SQL 문과 연계 사용 시 가이드
- INTO 절을 사용하여 최종 결과 집합을 테이블에 저장하고자 하는 경우에는 첫 번째 쿼리에만 INTO를 기술함.
- ORDER BY 는 SQL문의 마지막 부분에 기술함.
- GROUP BY 와 HAVING 절은 개별 쿼리에만 사용가능하며, 최종 결과 집합에는 영향을 미치지 않음.

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장

크리에이티브 커먼즈 라이센스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

Posted by 홍반장