2014.12.30 11:30 IT/MSSQL

MSSQL 함수 모음

- 집계 합수
  집계 함수는 어디에 사용되는가?
  집계 함수는 값 집합에 대한 계산을 수행하고 단일 값을 반환한다. 
  COUNT를 제외한 집계 함수는 Null 값을 무시한다. 
  집계 함수는 SELECT 문의 GROUP BY 절과 함께 사용하는 경우가 많다.
  집계 함수는 다음 경우에서만 식으로 사용할 수 있다. 
  
  SELECT 문의 선택 목록(하위 쿼리 또는 외부 쿼리) 
  COMPUTE 또는 COMPUTE BY 절
  HAVING 절

AVG 
그룹에서 값의 평균을 반환한다. Null 값은 무시된다.
BINARY_CHECKSUM 
테이블의 행 또는 식의 목록에 대해 계산된 이진 검사값을 반환한다. 
BINARY_CHECKSUM을 사용하여 테이블에 행에 대한 변경 사항을 찾을 수 있다.
CHECKSUM 
테이블의 행 또는 식의 목록에 대해 계산한 검사값을 반환한다. 
CHECKSUM 값은 해시 인덱스를 작성하는 데 사용하기 위한 것이다.
CHECKSUM_AGG 
그룹에서 값의 검사값을 반환한다. 
Null 값은 무시된다.
COUNT 
그룹에 포함된 항목 개수를 반환한다.
COUNT_BIG 
그룹에 포함된 항목 개수를 반환한다. 
COUNT_BIG은 COUNT 함수와 비슷하며 반환값만 다르다. 
COUNT_BIG은 bigint 데이터 형식 값을 반환하고 COUNT는 int 데이터 형식 값을 반환한다.
GROUPING 
행이 CUBE 또는 ROLLUP 연산자를 통해 추가될 때 추가 열의 출력이 1이 되거나 행이 CUBE 또는 ROLLUP의 결과가 아닐 때 추가 열의 출력이 0이 되도록 하는 집계 함수이다. 
그룹화는 CUBE 또는 ROLLUP 연산자가 들어 있는 GROUP BY 절과 관련된 선택 목록에서만 사용될 수 있다.
MAX 
식의 최대값을 반환한다.
MIN 
식에서 최소값을 반환한다.
SUM
식의 모든 값의 합계 또는 DISTINCT 값만 반환한다. 
SUM과 함께 사용할 수 있는 것은 숫자 열이다. Null 값은 무시된다.
STDEV 
특정 식의 모든 값에 대한 통계적 표준 편차를 반환한다.
STDEVP 
특정 식의 모든 값의 모집단에 대한 통계적 표준 편차를 반환한다.
VAR 
지정한 식에 있는 모든 값의 통계적 분산을 반환한다.
VARP 
지정한 식에 있는 모든 값의 모집단에 대한 통계적 분산을 반환한다.


 
- 수치 연산 함수
  수치 연산 함수는 우리가 쉽게 이해하는 수치 연산의 결과값을 반환한다. 
  일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 값을 반환한다.
  ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, SIGN 등의 산술 함수는 입력 값과 동일한 데이터 형식을 갖는 값을 반환한다. 
  EXP, LOG, LOG10, SQUARE, SQRT를 포함하여 삼각 함수와 그 밖의 함수는 입력 값을 float로 캐스트하고 float 값을 반환한다. 
  RAND를 제외한 모든 수치 연산 함수는 입력 값과 동일한 형식으로 반환하는 확정적인 함수이다.
ABS 
지정한 수식의 절대값을 양수로 반환한다.
ACOS 
코사인 값이 지정한 float 식인 각도를 라디안으로 반환한다. 
아크코사인이라고도 한다.
ASIN 
사인 값이 지정한 float 식인 각도를 라디안으로 반환한다. 
아크사인이라고도 한다
ATAN 
탄젠트 값이 지정한 float 식인 각도를 라디안으로 반환한다. 
아크탄젠트라고도 한다.
ATN2 
탄젠트 값이 지정한 두 개의 float 식 사이에 속하는 각도를 라디안으로 반환한다. 
아크탄젠트라고도 한다.
CEILING 
지정한 숫자 식 이상인 최소 정수를 반환한다.
COS 
지정한 식에서 지정한 각도의 삼각법 코사인을 라디안 단위로 반환한다.
COT 
지정한 float 식에서 지정한 각도의 삼각법 코탄젠트를 라디안 단위로 반환한다.
DEGREES
지정된 라디안 단위의 각도에 대해 해당하는 각도를 도 단위로 반환합니다.
EXP 
특정 float 식의 지수 값을 반환한다.
FLOOR 
주어진 숫자 식 이하인 최대 정수를 반환한다.
LOG 
주어진 float 식의 자연 로그를 반환한다.
LOG10 
주어진 float 식의 기수 10 로그를 반환한다.
PI 
PI의 상수 값을 반환한다.
POWER 
주어진 식의 값을 지정된 거듭제곱으로 반환한다.
 
RADIANS 
숫자 식을 도 단위로 입력하면 라디안으로 반환한다.
RAND
0부터 1까지의 임의 float 값을 반환한다.
ROUND 
특정 길이나 정밀도로 반올림한 숫자 식을 반환한다.
SIGN 
주어진 식의 양수(+1), 영(0) 또는 음수(-1) 기호를 반환한다.
SIN 
특정 각도(라디안)의 삼각 사인을 근사 숫자(float) 식으로 반환한다.
SQUARE 
특정 식의 제곱을 반환한다.
SQRT 
특정 식의 제곱근을 반환한다.
TAN 
입력한 식의 탄젠트를 반환한다.


- 날짜 및 시간 함수
  날짜 및 시간 함수는 다음과 같은 특징이 있다
  날짜 및 시간 함수는 우리가 쉽게 이해하는 날짜 및 시간의 결과값을 반환한다. 
  일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 문자열, 숫자 또는 날짜 및 시간 값을 반환한다.
DATEADD 
지정한 날짜에 시간 간격을 더하여 새 datetime 값을 반환한다.
DATEDIFF 
지정한 두 날짜 간에 교차되는 날짜와 시간 경계값을 반환한다.
DATENAME 
지정한 날짜의 특정 날짜 부분을 나타내는 문자열을 반환한다.

DATEPART 
지정한 날짜의 특정 날짜 부분을 나타내는 정수를 반환한다.
DAY 
지정한 날짜의 일 부분을 나타내는 정수를 반환한다.
GETDATE 
현재 시스템 날짜와 시간을 datetime 값의 Microsoft SQL Server 표준 내부 형식으로 반환한다.
GETUTCDATE 
국제 표준시 또는 그리니치 표준시를 나타내는 datetime 값을 반환한다. 현재 UTC 시간은 해당 지역의 현재 시간과 SQL Server가 실행 중인 컴퓨터 운영 체제의 표준 시간대를 기준으로 반환한다.
MONTH 
지정된 날짜의 월 부분을 나타내는 정수를 반환한다.
YEAR 
지정한 날짜의 연도 부분을 표시하는 정수를 반환한다.



- 문자열 함수
  문자열 함수는 다음과 같은 특징이 있다
  문자열 함수는 우리가 쉽게 이해하는 문자열의 결과값을 반환한다. 
  일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 숫자 또는 문자열 값을 반환한다.
ASCII 
문자 식에서 가장 왼쪽 문자의ASCII 코드 값을 반환한다.
CHAR 
int ASCII 코드를 문자로 변환한다.
CHARINDEX 
문자열에서 지정한 식의 시작 위치를 반환한다.
DIFFERENCE 
두 문자 식에서 SOUNDEX 값의 차이를 정수로 반환한다.
LEFT 
왼쪽에서부터 지정된 문자 수에서 시작하는 문자열의 일부를 반환한다.
LEN 
주어진 문자열 식의 바이트 수 대신 후행 공백을 제외한 문자 수를 반환한다.
LOWER 
대문자로 된 데이터를 소문자로 변환한 후 문자 식을 반환한다.
LTRIM 
선행 공백을 제거한 후 문자 식을 반환한다.
NCHAR 
유니코드 표준에서 정의된 대로 주어진 정수 코드로 유니코드 문자를 반환한다.
PATINDEX 
유효한 모든 text 및 character 데이터 형식에서 패턴이 처음으로 지정된 식에서 발생한 시작 위치를 반환하거나 패턴을 찾지 못하는 경우에는 0을 반환한다.
REPLACE 
첫째 문자열 식에서 둘째 문자열 식의 모든 인스턴스를 찾아 셋째 식으로 반환한다.
QUOTENAME 
입력 문자열이 유효한 기호와 함께 유니코드 문자열을 반환한다.
REPLICATE 
지정한 횟수만큼 문자 식을 반복한다.
REVERSE 
문자 식을 반대로 반환한다.
RIGHT 
오른쪽부터 지정한 수만큼의 문자를 제외한 나머지 문자열 일부를 반환한다.
RTRIM 
모든 후행 공백을 제거한 다음 문자열을 반환한다.
SOUNDEX 
두 문자열의 유사성을 평가하기 위한 네 글자(SOUNDEX) 코드를 반환한다.
SPACE 
반복된 공백의 문자열을 반환한다.
STR 
숫자 데이터에서 변환된 문자 데이터를 반환한다.
STUFF 
지정한 시작 지점에서 지정한 문자 길이를 삭제한 다음 다른 문자 집합을 삽입한다.
SUBSTRING 
문자, 이진, 텍스트, 이미지 식의 일부를 반환한다.
UNICODE 
유니코드 표준에서 정의한 대로 입력 식에 있는 첫 글자의 정수 값을 반환한다.
UPPER 
문자 식을 대문자로 변환된 소문자 데이터로 반환한다.


- 구성 함수
  구성 함수는 다음과 같은 특징이 있다
  구성 함수는 현재 구성된 서버의 옵션 설정에 대한 값들을 반환한다.  
  모든 구성 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
@@ATEFIRST 
매주 지정된 첫째 날을 나타내는 SET DATEFIRST 매개 변수의 현재 값을 반환한다. 
1은 월요일, 2는 화요일 순으로 하여 7은 일요일이다.
@@DBTS 
현재 데이터베이스에 대해 현재 timestamp 데이터 형식의 값을 반환한다.
@@LANGID 
현재 사용 중인 언어의 로컬 언어 식별자(ID)를 반환한다.
@@LANGUAGE 
현재 사용 중인 언어의 이름을 반환한다.
@@LOCK_TIMEOUT 
현재 세션의 현재 잠금 시간 제한 설정을 밀리초 단위로 반환한다.
@@MAX_CONNECTIONS 
SQL Server에서 허용한 최대 동시 사용자 연결 수를 반환한다.
 
@@MAX_PRECISION 
현재 서버에 설정되어 있는 decimal과 numeric 데이터 형식의 정밀도 수준을 반환한다.
@@NESTLEVEL 
현재 저장 프로시저 실행의 중첩 수준을 반환한다.
@@OPTIONS 
현재 SET 옵션에 대한 정보를 반환한다.
@@REMSERVER 
원격 SQL Server 데이터베이스 서버의 이름이 로그인 레코드에 나타날 때 이름을 반환한다.
@@SERVERNAME 
SQL Server를 실행하는 동안 로컬 서버의 이름을 반환한다.
@@SERVICENAME 
SQL Server가 실행 중인 레지스트리 키의 이름을 반환한다. @@SERVICENAME은 현재 인스턴스가 기본 인스턴스인 경우MSSQLServer를 반환한다. 이 함수는 현재 인스턴스가 명명된 인스턴스이면 인스턴스 이름을 반환한다.
@@SPID 
현재 사용자 프로세스의 서버 프로세스 식별자(ID)를 반환한다.
@@TEXTSIZE 
SELECT 문이 반환하는 text 또는 image 데이터의 최대 길이를 바이트 단위로 지정하는 SET 문의 현재 TEXTSIZE 옵션 값을 반환한다.
@@VERSION 
현재 설치되어 있는 SQL Server의 날짜, 버전, 프로세서 유형을 반환한다.



- 메타 데이터 함수
  메타 데이터 함수는 다음과 같은 특징이 있다
  메타 데이터 함수는 데이터베이스와 데이터베이스 개체에 대한 정보를 반환한다. 
  모든 메타 데이터 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
COL_LENGTH 
열의 정의된 길이(바이트)를 반환한다.
COL_NAME 
지정한 테이블 ID와 열 ID에 대한 데이터베이스 열의 이름을 반환한다.
COLUMNPROPERTY 
열 또는 프로시저 매개 변수에 대한 정보를 반환한다.
DATABASEPROPERTY 
주어진 데이터베이스와 속성 이름에 대해 명명된 데이터베이스 속성 값을 반환한다.
DATABASEPROPERTYEX 
지정한 데이터베이스에 대해 지정한 데이터베이스 옵션이나 속성의 현재 설정을 반환한다.
DB_ID 
데이터베이스 ID 번호를 반환한다.
DB_NAME
데이터베이스 이름을 반환한다.
FILE_ID 
현재 데이터베이스의 주어진 논리 파일 이름의 파일 ID 번호를 반환한다.
FILE_NAME 
주어진 파일 ID에 대한 논리 파일 이름을 반환한다.
FILEGROUP_ID 
주어진 파일 그룹 이름의 파일 그룹 ID를 반환한다.
FILEGROUP_NAME 
주어진 파일 그룹 ID에 대한 파일 그룹 이름을 반환한다.
FILEGROUPPROPERTY 
파일 그룹과 속성 이름이 주어졌을 때 지정된 파일 그룹 속성 값을 반환한다.
FILEPROPERTY 
파일 이름과 속성 이름이 주어졌을 때 지정된 파일 이름 속성 값을 반환한다.
fn_listextendedproperty 
데이터베이스 개체의 확장 속성 값을 반환한다.

FULLTEXTCATALOGPROPERTY 
전체 텍스트 카탈로그 속성에 대한 정보를 반환한다.
FULLTEXTSERVICEPROPERTY 
전체 텍스트 서비스 수준 속성에 대한 정보를 반환한다.
INDEX_COL 
인덱스된 열 이름을 반환한다.
INDEXKEY_PROPERTY 
인덱스 키에 대한 정보를 반환한다.
INDEXPROPERTY 
주어진 테이블 ID, 인덱스 이름, 속성 이름에 대해 명명된 인덱스 속성 값을 반환한다.
OBJECT_ID 
데이터베이스 개체 ID를 반환한다.
OBJECT_NAME 
데이터베이스 개체 이름을 반환한다.
OBJECTPROPERTY 
현재 데이터베이스의 개체에 대한 정보를 반환한다.
@@PROCID 
현재 프로시저의 저장 프로시저 식별자(ID)를 반환한다.
SQL_VARIANT_PROPERTY 
sql_variant 값에 대한 기본 데이터 형식 및 기타 정보를 반환한다.
TYPEPROPERTY 
데이터 형식에 관한 정보를 반환한다.



- 커서 함수
  커서 함수는 다음과 같은 특징이 있다
  커서 함수는 커서에 대한 정보를 반환한다. 
  모든 커서 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
@@CURSOR_ROWS 
현재 연결에서 마지막으로 열린 커서에 있는 자격을 갖춘 행의 수를 반환한다.
CURSOR_STATUS 
저장 프로시저의 호출자가 프로시저에서 주어진 매개 변수에 대해 커서 및 결과 집합을 반환한다.
@@FETCH_STATUS 
현재 연결로 열린 모든 커서에 대해 실행된 마지막 FETCH 문의 상태를 반환한다.



- 행 집합 함수
  행 집합 함수는 다음과 같은 특징이 있다
  행 집합 함수는 테이블 참조 대신 사용할 수 있는 개체를 반환한다. 
  모든 행 집합 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
CONTAINSTABLE 
문자 기반 데이터 형식이 포함된 열에서 특정 단어나 구와 정확히 일치하거나 비슷하게 일치하는 단어를 검색하거나, 서로 근접한 단어 검색, 가중치 검색에서 일치한 0개 이상의 행이 있는 테이블을 반환한다.
FREETEXTTABLE 
텍스트와 의미는 일치하지만 단어는 정확히 일치하지 않는 값의 문자 기반 데이터 형식이 포함된 열에 대해 행이 없거나 하나 이상인 테이블을 반환한다.
OPENDATASOURCE 
연결된 서버 이름을 사용하지 않고 네 부분으로 된 개체 이름의 연결 정보를 제공한다.
OPENQUERY 
OLE DB 데이터 원본인 주어진 연결된 서버에 대해 지정된 통과 쿼리를 실행한다. 쿼리는 여러 결과 집합을 반환하지만 OPENQUERY는 첫 번째 것만 반환한다.
OPENROWSET 
OLE DB 데이터 원본에서 원격 데이터를 액세스하는 데 필요한 모든 연결 정보를 제공한다. 쿼리는 여러 결과 집합을 반환하지만 OPENROWSET는 첫 번째 것만 반환한다.
OPENXML 
OPENXML은 XML 문서에 대한 행 집합 뷰를 제공한다. OPENXML이 행 집합 공급자이므로 테이블, 뷰 또는 OPENROWSET 함수 등의 행 집합 공급자가 표시될 수 있는 Transact-SQL 문에 OPENXML을 사용할 수 있다.



-보안 함수
 보안 함수는 다음과 같은 특징이 있다
 보안 함수는 과거 버전에서 저장프로시저가 담당했던 부분을 함수로 만들어 2000에서 제공하는 함수로 사용자와 역할에 관한 정보를 반환한다. 모든 보안 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
fn_trace_geteventinfo 
추적한 이벤트에 대한 정보를 반환한다.
fn_trace_getfilterinfo 
지정한 추적에 적용되는 필터에 대한 정보를 반환한다.
fn_trace_getinfo 
지정한 추적이나 기존 추적에 대한 정보를 반환한다.
fn_trace_gettable 
추적 파일 정보를 테이블 형식으로 반환한다.
HAS_DBACCESS 
사용자가 지정된 데이터베이스를 액세스할 수 있는지에 대한 정보를 반환한다.
IS_MEMBER 
현재 사용자가 지정된 Windows Server 그룹 또는 Microsoft SQL Server 역할의 구성원인지 여부를 표시한다.
IS_SRVROLEMEMBER 
현재 사용자 로그인이 지정된 서버 역할의 구성원인지 여부를 나타낸다.
SUSER_SID 
사용자 로그인 이름에 대한 보안 ID(SID)를 반환한다.
SUSER_SNAME 
사용자 보안 ID(SID)로부터 로그인 ID 이름을 반환한다.
USER_ID 
사용자의 데이터베이스 ID 번호를 반환한다.
USER 
기본값이 지정되지 않은 경우에 현재 사용자의 데이터베이스 사용자 이름에 대해 시스템이 제공한 값을 테이블에 삽입할 수 있도록 허용한다.


- 텍스트 및 이미지 함수
  텍스트 및 이미지 함수는 다음과 같은 특징이 있다
  텍스트 및 이미지 함수는 텍스트와 이미지 포인터의 위치를 반환한다. 
  모든 텍스트 및 이미지 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
ATINDEX 
유효한 모든 text 및 character 데이터 형식에서 패턴이 처음으로 지정된 식에서 발생한 시작 위치를 반환한다.
TEXTPTR 
varbinary 형식의 text, ntext 또는 image 열에 해당하는 텍스트 포인터 값을 반환한다.
TEXTVALID 
주어진 텍스트 포인터가 유효한지 여부를 반환한다.


'IT > MSSQL' 카테고리의 다른 글

MSSQL 함수 모음  (0) 2014.12.30
콤마로 된 문자열을 테이블로 변환 함수  (0) 2014.11.26
FullText Search  (0) 2014.10.13
MSSQL 인덱스(Index) 생성  (0) 2014.08.20
Code Formatter. 코드 정렬 도구.  (0) 2014.06.10
MSSQL 사용자 테이블 및 SP권한 부여 쿼리  (0) 2014.05.08
Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

콤마로 된 문자열을 테이블로 변환 함수

 

[ DelimitedSplit8K ]

 

1. 테이블 반환 함수

2. ItemNumber : 순번, Item : 각 콤마구분값

3. InputParameter : @pString - 문자열, @pDelimiter - 구분값(char(1))



  1. CREATE FUNCTION dbo.DelimitedSplit8K  
  2. /***************************************************************************************************  
  3.  Purpose:  
  4.  Split a given string at a given delimiter and return a list of the split elements (items).  
  5.   
  6.  Usage Example:  
  7.  SELECT *  
  8.    FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)  
  9.   
  10.  Returns:  
  11.  iTVF containing the following:  
  12.  ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)  
  13.  Item       = Element value as a VARCHAR(8000)  
  14.   
  15.  Notes:  
  16.  1. Optimized for VARCHAR(8000) or less.  
  17.  2. Optimized for single character delimiter.  
  18.  3. Optimized for use with CROSS APPLY.  
  19.  4. Does not "trim" elements just in case leading or trailing blanks are intended.  
  20.  5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a  
  21.     bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and   
  22.     compactness.   
  23.  6. If you don't know how a Tally table can be used to replace loops, please see the following...  
  24.     http://www.sqlservercentral.com/articles/T-SQL/62867/  
  25.   
  26.  Revision History:  
  27.  Rev 00 - 20 Jan 2010 - Jeff Moden   
  28.         - Base 10 redaction for CTE.  
  29.  Rev 01 - 08 Mar 2010 - Jeff Moden  
  30.         - Changed UNION ALL to UNPIVOT for bit of extra speed.  
  31.  Rev 02 - 13 Mar 2010 - Jeff Moden  
  32.         - Removed one additional concatenation and one subtraction from the SUBSTRING in the   
  33.           SELECT List.  
  34. ***************************************************************************************************/  
  35. --===== Define I/O parameters  
  36.         (  
  37.         @pString    VARCHAR(8000),  
  38.         @pDelimiter CHAR(1)  
  39.         )  
  40. RETURNS TABLE  
  41. AS  
  42. RETURN  
  43. WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).  
  44.       E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL   
  45.                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL   
  46.                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),--10  
  47.       E2(N) AS (SELECT 1 FROM E1 a, E1 b),   --100  
  48.       E4(N) AS (SELECT 1 FROM E2 a, E2 b),   --10,000  
  49. cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)  
  50. --===== Do the split  
  51.  SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,  
  52.         SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item  
  53.    FROM cteTally  
  54.   WHERE N < LEN(@pString) + 2  
  55.     AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter  
  56. ;  

 

<사용예>

 

1. 문자열 관련


SELECT * FROM DelimitedSplit8K('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',')  

 

 

2. 테이블과 조인

 

  - CROSS APPLY를 위해 SQL2005 이상 가능 ( 호환성모드 90 이상 )


  1. SELECT t.아이디,t.콤마로된필드,b.itemNumber,b.item  
  2. FROM 대상테이블 t  
  3. CROSS APPLY dbo.DelimitedSplit8K(t.m_interest,',') B  
  4. WHERE isnull(t.콤마로된필드,'') <> ''  
  5. ORDER BY T.아이디  

 

 

 

[ fn_Split ]

 

1. 테이블 반환 함수

2. 각 콤마구분값

3. InputParameter : @Sql - 문자열, @Splits - 구분값


  1. CREATE FUNCTION [dbo]. [fn_Split] ( @Sql VARCHAR (8000), @Splits VARCHAR (10))    
  2.     RETURNS @temp TABLE (item VARCHAR (100))    
  3. AS    
  4. BEGIN   
  5.     Declare @i Int    
  6.     SET @Sql    =  RTrim   (LTrim (@Sql))    
  7.     SET @i      =  CharIndex   (@Splits, @Sql)    
  8.     WHILE @i  >  =1    
  9.     Begin    
  10.         INSERT @temp VALUES(Left (@Sql, @i-1))    
  11.         SET @Sql    =  SUBSTRING   (@Sql, @i + 1, LEN (@Sql ) - @i)    
  12.         SET @i      =  CHARINDEX   (@Splits, @Sql)    
  13.     END ;    
  14.   
  15.   
  16.     IF @Sql     < >  ''    
  17.         INSERT @temp VALUES(@Sql)    
  18.    
  19.     RETURN  
  20. END    

 

<사용예>


  1. SELECT * FROM fn_Split('을지문덕,홍길동,가가가,일반적,소리샘,케이티,왔구나,뭘까요,납니다요,ㅋㅋㅋㅋㅋㅋㅋㅋ,긴문장을보여드리겠습니다. 뭐가 길까요알아서 맞춰보시길',',')  

 

'IT > MSSQL' 카테고리의 다른 글

MSSQL 함수 모음  (0) 2014.12.30
콤마로 된 문자열을 테이블로 변환 함수  (0) 2014.11.26
FullText Search  (0) 2014.10.13
MSSQL 인덱스(Index) 생성  (0) 2014.08.20
Code Formatter. 코드 정렬 도구.  (0) 2014.06.10
MSSQL 사용자 테이블 및 SP권한 부여 쿼리  (0) 2014.05.08
Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

2014.10.13 16:16 IT/MSSQL

FullText Search

http://rocabilly.tistory.com/1

'IT > MSSQL' 카테고리의 다른 글

MSSQL 함수 모음  (0) 2014.12.30
콤마로 된 문자열을 테이블로 변환 함수  (0) 2014.11.26
FullText Search  (0) 2014.10.13
MSSQL 인덱스(Index) 생성  (0) 2014.08.20
Code Formatter. 코드 정렬 도구.  (0) 2014.06.10
MSSQL 사용자 테이블 및 SP권한 부여 쿼리  (0) 2014.05.08
Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

인덱스는 기본적으로 생성해두는것이 효율성에 좋습니다. 

데이터가 적을 경우는 기본적인 인덱스로 결과를 출력할 수 있도록 하고, 데이터가 많아질 수록 쿼리에 맞는 인덱스를 걸어주면 효율적입니다. 


인덱스는 일반적으로 한 테이블당 5개전후로 많이 잡습니다. 그 이상을 걸면 데이터의 양에 오히려 좋지 않은 영향을 줄 수 있기 때문입니다. 


인덱스는 기본적으로 전체 테이블의 10%정도를 가져오는데 효과적입니다. 

하지만 1000만건중에 100만건을 가져오는데는 인덱스가 효과적이라고 보기는 힘들지만 기본적은 인덱스는 필수입니다. 


현재 느리다고 생각되는 쿼리가 있다면 다음의 과정으로 인덱스를 만들어보세요. 


1. 쿼리 분석

현재 이 서버에 돌아가고 있는 쿼리를 알기 위해 다음을 실행합니다. 

select * from master..syscacheobjects

sql이란 부분에 현재 캐시되어있는 쿼리가 보입니다. 

이 쿼리들을 돌려가며 느리다고 느껴지는 쿼리를 찾습니다.


2. 기본 인덱스, 부하가 걸리는 인덱스를 체크

sp_helpindex A

이 명령으로 A테이블의 인덱스를 찾을 수 있습니다. 


3. 인덱스 생성

create index A_ix01 on A(aa)

처음엔 고유증가 번호등의 잘 변하지 않는 필드를 Clustered Primary Key로 잡아야 합니다. 최종적으로 데이터를 찾아가는 길을 만들기 위함입니다. 이게 없으면 전체를 메모리에 띄워야 하기 때문에 많은 리소스를 잡아먹습니다.

Join문과 Where에서 찾는 필드명은 index로 걸어주는 것이 좋습니다. text등의 900바이트가 넘는 데이터는 index가 생성이 되지 않으니 주의 하세요. 

index를 걸어줄 때 필드를 여러개를 한번에 지정하는 방법이 있고 한개씩 여러번 지정하는 방법이 있습니다.


한번에 여러개를 지정하는 방법의 예:

create index A_ix01 on A(aa, bb)


한개씩 여러번 지정하는 방법의 예:

create index A_ix01 on A(aa)

create index A_ix02 on A(bb)


위의 방법은 aa순으로 정렬된 뒤에 bb순으로 정렬하는 것입니다. 때문에 where절등에서 bb를 먼저찾거나 bb만 찾게 되는 경우 위의 인덱스는 타지 않습니다. 

대신 아래처럼 한개씩 여러번 지정하는 것에 비해 aa,bb순으로 찾는 경우 가장 빠른 효율을 줍니다. 따라서 위방법과 아래방법은 전혀 다른 인덱스가 됩니다. 참고하면서 만드세요. 


4. 테스트

인덱스를 만들어가면서 대량의 데이터의 쿼리를 돌려봅니다. 

인덱스를 바꿔가면서 가장 맞는 인덱스를 생성합니다.

최초로 돌릴 경우는 메모리에 올리는 시간 때문에 느릴 수 있습니다. 때문에 쿼리를 여러번 돌린후 마지막 시간을 재는게 좋습니다.


5. 불필요한 인덱스 삭제

drop index AA.AA_ix01 

반드시 테이블명.인덱스명 의 형식으로 지워야 지워집니다. 


* syscacheobjects에 비슷한 쿼리가 많다면 그 쿼리는 Static SQL을 이용하는 것이 좋습니다. Static SQL을 이용하면 같은 쿼리의 변수만 바뀌는 식의 쿼리를 하나의 쿼리로 인식하여 처리하므로 비슷한 캐시를 줄여 캐시를 활용할 공간이 넓어집니다. 

'IT > MSSQL' 카테고리의 다른 글

콤마로 된 문자열을 테이블로 변환 함수  (0) 2014.11.26
FullText Search  (0) 2014.10.13
MSSQL 인덱스(Index) 생성  (0) 2014.08.20
Code Formatter. 코드 정렬 도구.  (0) 2014.06.10
MSSQL 사용자 테이블 및 SP권한 부여 쿼리  (0) 2014.05.08
MSSQL DateTime Convert.  (0) 2014.02.24
Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

Code Formatter. 코드 정렬 도구.


SQL Server Management Studio 쿼리를 작성하다 보면, 토드나 SQL Gate 등 코드정렬(포맷) 기능이 너무 아쉽다. 

이를 Cover 하는 기능이 아래와 같이 제공된다.


파일명 : SqlFormatterSSMSAddIn.Setup.1.4.4.msi.zip


관련 링크 : http://architectshack.com/PoorMansTSqlFormatter.ashx

관련 링크 : http://www.sqler.com/504675

'IT > MSSQL' 카테고리의 다른 글

FullText Search  (0) 2014.10.13
MSSQL 인덱스(Index) 생성  (0) 2014.08.20
Code Formatter. 코드 정렬 도구.  (0) 2014.06.10
MSSQL 사용자 테이블 및 SP권한 부여 쿼리  (0) 2014.05.08
MSSQL DateTime Convert.  (0) 2014.02.24
MSSQL STUFF, FOR XML PATH  (0) 2014.02.13
Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

>> SP

-- 변경

GRANT ALTER ON SP이름 TO DB이름

GO


-- 소유권 가져오기

GRANT TAKE OWNERSHIP ON SP이름 TO DB이름

GO


-- 실행

GRANT EXECUTE ON SP이름 TO DB이름

GO


-- 정의 보기

GRANT VIEW DEFINITION ON SP이름 TO DB이름

GO


-- 제어

GRANT CONTROL ON SP이름 TO DB이름

GO



>> 테이블

-- 변경 내용 추적 보기

GRANT VIEW CHANGE TRACKING ON 테이블이름 TO DB이름

GO


-- 변경

GRANT ALTER ON 테이블이름 TO DB이름

GO


-- 삭제

GRANT DELETE ON 테이블이름 TO DB이름

GO


-- 삽입

GRANT INSERT ON 테이블이름 TO DB이름

GO



-- 선택

GRANT SELECT ON 테이블이름 TO DB이름

GO


-- 소유권 가져오기

GRANT TAKE OWNERSHIP ON 테이블이름 TO DB이름

GO


-- 업데이트

GRANT UPDATE ON 테이블이름 TO DB이름

GO


-- 정의 보기

GRANT VIEW DEFINITION ON 테이블이름 TO DB이름

GO


-- 제어

GRANT CONTROL ON 테이블이름 TO DB이름

GO


-- 참조

GRANT REFERENCES ON 테이블이름 TO DB이름

GO


Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

DECLARE @dateReg datetime

SET @dateReg = GetDate()

SELECT Convert(nvarchar(20), @dateReg, 100) FROM [work] WHERE [idx] = 483

-> 07 15 2005 12:00AM

SELECT Convert(nvarchar(20), @dateReg, 101) FROM [work] WHERE [idx] = 483

-> 07/15/2005

SELECT Convert(nvarchar(20), @dateReg, 102) FROM [work] WHERE [idx] = 483

-> 2005.07.15

SELECT Convert(nvarchar(20), @dateReg, 103) FROM [work] WHERE [idx] = 483

-> 15/07/2005

SELECT Convert(nvarchar(20), @dateReg, 104) FROM [work] WHERE [idx] = 483

-> 15.07.2005

SELECT Convert(nvarchar(20), @dateReg, 105) FROM [work] WHERE [idx] = 483

-> 15-07-2005

SELECT Convert(nvarchar(20), @dateReg, 106) FROM [work] WHERE [idx] = 483

-> 15 07 2005

SELECT Convert(nvarchar(20), @dateReg, 107) FROM [work] WHERE [idx] = 483

-> 07 15, 2005

SELECT Convert(nvarchar(20), @dateReg, 108) FROM [work] WHERE [idx] = 483

-> 12:23:59

SELECT Convert(nvarchar(20), @dateReg, 109) FROM [work] WHERE [idx] = 483

-> 07 15 2005 12:23:59:

SELECT Convert(nvarchar(20), @dateReg, 110) FROM [work] WHERE [idx] = 483

-> 07-15-2005

SELECT Convert(nvarchar(20), @dateReg, 111) FROM [work] WHERE [idx] = 483

-> 2005/07/15

SELECT Convert(nvarchar(20), @dateReg, 112) FROM [work] WHERE [idx] = 483

-> 20050715

SELECT Convert(nvarchar(20), @dateReg, 113) FROM [work] WHERE [idx] = 483

-> 19 07 2005 12:23:36:

SELECT Convert(nvarchar(20), @dateReg, 114) FROM [work] WHERE [idx] = 483

-> 12:23:59:793 

Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

STUFF 와 FOR XML PATH  테스트 !

CREATE TABLE #TABLE1 (T1_ID INT)

CREATE TABLE #TABLE2 (T2_ID INT, T1_ID INT)


INSERT INTO #TABLE1 VALUES(1) -- 테이블 생성
INSERT INTO #TABLE1 VALUES(2)

INSERT INTO #TABLE2 VALUES(1, 1) -- 테이블 값 생성
INSERT INTO #TABLE2 VALUES(2, 1)
INSERT INTO #TABLE2 VALUES(3, 1)
INSERT INTO #TABLE2 VALUES(4, 2)
INSERT INTO #TABLE2 VALUES(5, 2)
INSERT INTO #TABLE2 VALUES(6, 2)


SELECT * FROM #TABLE1 -- 테스트 준비 완료
SELECT * FROM #TABLE2


테스트 시작 - TABLE2의 데이터를 한 ROW로 나타냄 : 결과

 SELECT  T3.T1_ID,
 STUFF(
(
SELECT ',' + CAST(T2.T2_ID AS CHAR)
FROM #TABLE1 AS T1 
        INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID  
WHERE T1.T1_ID = T3.T1_ID
GROUP BY T2.T2_ID, T2.T1_ID
ORDER BY T2.T1_ID
FOR XML PATH('') 
)
 , 1, 1, '') AS T2_ID
 FROM #TABLE1 AS T3
 WHERE T3.T1_ID IN (1,2)
 GROUP BY T3.T1_ID

 


STUFF 제거 : 앞에 , 살아남

 SELECT T3.T1_ID,
 --STUFF(
 (
SELECT ',' + CAST(T2.T2_ID AS CHAR)
FROM #TABLE1 AS T1 
        INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID  
WHERE T1.T1_ID = T3.T1_ID
GROUP BY T2.T2_ID, T2.T1_ID
ORDER BY T2.T1_ID
FOR XML PATH('')
AS T2_ID 
 --  , 1, 1, '') AS T2_ID
 FROM #TABLE1 AS T3
 WHERE T3.T1_ID IN (1,2)
 GROUP BY T3.T1_ID

 


 실제 여러 ROW의 데이터를 ,로 구분하는 역할은 FOR XML PATH('') 가 수행한다.

안의 SELECT 쿼리 테스트 결과 

 SELECT ',' + CAST(T2.T2_ID AS VARCHAR) AS T2_ID
 FROM #TABLE1 AS T1
 INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID 
 --WHERE T1.T1_ID = T3.T1_ID
 GROUP BY T2.T2_ID, T2.T1_ID
 ORDER BY T2.T1_ID

 


 SELECT ',' + CAST(T2.T2_ID AS VARCHAR)
 FROM #TABLE1 AS T1 
 INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID
 --WHERE T1.T1_ID = T3.T1_ID
 GROUP BY T2.T2_ID, T2.T1_ID
 ORDER BY T2.T1_ID
 FOR XML PATH('')

 



 SELECT ',' + CAST(T2.T2_ID AS VARCHAR)  AS TAG
 FROM #TABLE1 AS T1 
 INNER JOIN #TABLE2 AS T2 ON T1.T1_ID = T2.T1_ID 
 --WHERE T1.T1_ID = T3.T1_ID
 GROUP BY T2.T2_ID, T2.T1_ID
 ORDER BY T2.T1_ID
 FOR XML PATH('')


- STUFF

STUFF(문자열, 시작위치, 변경할 문자길이, 변경할 문자)

EX) STUFF('ABCDEF', 2, 2 , 'abc') => 'AabcDEF'
두번째부터 시작하여, 2 길이의 문자를 abc로 변경

Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

기본적으로 SQL 에서 제공해주는 데이터로 우리는 원하는 기간동안의 날짜를 모두 출력 해 낼 수 있다. 

 

select convert(char(10),dateadd(증가시킬데이터(m:월, d:일, hh:시간, mm:분) ,number,'시작날짜'),120) as regdate
from master..spt_values with(nolock) 
where type = 'P' 
and convert(char(10),dateadd(d,number,'시작날짜'),120) < '마지막날짜'

이렇게 원하는 기간 내의 날짜를 출력 해 낼 수 있다.

 

 

ex)

select convert(char(10),dateadd(d,number,'2012-04-24'),120) as regdate
   from master..spt_values with(nolock) 
   where type = 'P' 
   and convert(char(10),dateadd(d,number,'2012-04-24'),120) < DateAdd("m", 1, '2012-04-24')  

 

 마지막 날짜를 mssql 에서 제공하는 DateAdd 함수를 이용해서 한달을 더해 출력 해 보았다

결과는 다음과 같다.

 

2012-04-24
2012-04-25
2012-04-26
2012-04-27
2012-04-28
2012-04-29
2012-04-30
2012-05-01
2012-05-02
2012-05-03
2012-05-04
2012-05-05
2012-05-06
2012-05-07
2012-05-08
2012-05-09
2012-05-10
2012-05-11
2012-05-12
2012-05-13
2012-05-14
2012-05-15
2012-05-16
2012-05-17
2012-05-18
2012-05-19
2012-05-20
2012-05-21
2012-05-22
2012-05-23 

 

 

이걸 이용하여 left조인 하여 원하는 날짜에 원하는 데이터를 추출 해 낼 수 있다.. 

 

보너스. 

활용을 조금 더 하자면 시간대 별 데이터 까지 출력이 가능하다. 

 

select right(convert(char(13),dateadd(hh,number,'시작날짜'),120),2) as regdate
from master..spt_values with(nolock) 
where type = 'P' 
and convert(char(10),dateadd(hh,number,'시작날짜'),120) < '마지막날짜' 

 

이렇게 해서 원하는 기간내에서 해당되는 날짜와 또한 그 날짜의 시간대 별 데이터까지  

group by 시킬 수가 있다. 

 

실전활용예제 )

 

select 
   regdate,
   isnull(tot,0),
   isnull(cnt,0)
from 
(
 select right(convert(char(13),dateadd(hh,1,'2012-04-24'),120),2) as regdate
 from master..spt_values with(nolock) 
 where type = 'P' 
 and convert(char(10),dateadd(hh,number,'2012-04-24'),120) < DateAdd(d, 1, '2012-04-24')
) as t1 
left join 
(
 SELECT right(CONVERT(char(13), d_regdate, 120),2) as 열1, sum(열1) as tot, count(열1) as cnt 
 FROM 데이터를 조회할 테이블
 WHERE convert(char(10),D_REGDATE,120) >= '2012-04-24'
 AND convert(char(10),D_REGDATE,120) <= '2012-04-24'
  group by right(CONVERT(char(13), d_regdate, 120),2)
) as t2
on t1.regdate = t2.d_regdate
group by t1.regdate, t2.d_regdate, tot,cnt
order by t1.regdate; 

 

난 하루치만 조회할 예정이었으므로 음하하핫. 

이렇게 left join문을 써서 복잡하게 조회 한 까닭은 데이터를 페이지에 뿌려주는데, join을 하지 않고 그냥 쿼리문을 썼을 때 데이터가 없는 시간대에는 0을 해주고 싶은데, 데이터 자체가 없기에 그 시간대는 아예 결과에 나타나지 않았다.

예를 들면

01    1233      4

02    1234      2

04    3412      4

 

이런식으로 3시 대의 결과가 빠져서 된다.

이를 해결해 주기위해서 웹언어로 구현을 해도 되겠지만, 그렇게 되면 조회되는 데이터에 따라 달라지기에 소스가 복잡해 질 수 있기에, 모든 시간대를 결과로 출력하고 isnull(열이름,0) 을 통해

null일때는 0을 출력하게 만들어줌으로 결과가 없는 시간대에는

 

01 1233 4

02 1234 2

03    0  0

04 3412 4

  

이라는 결과를 얻을 수 있다. 


자세한 설명은 읽어보지 않았지만 써보면 master..spt_values 단순히 0, 1, 2, 3, 4,  쭉 증가하는 값을 가진 테이블이라고 보면 될 것 같다.

Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부

먼저 중첩 Transaction에 대해서 알아보도록 하겠습니다. 중첩 Transaction은 말 그대로 Transaction 안에 Transaction을 또 다시 정의하는 것을 말합니다.

예제의 그림을 보면서 설명을 드리도록 하겠습니다.



그림을 보시면 상당히 복잡한 문장 같은 데요. 하나하나 살펴보도록 하겠습니다.

이 문장은 기존에 임대되었던 임대내역을 갱신하는 문장입니다. 즉 고객이 테이프를 빌려가면서, 이미 임대에 대한 지불을 끝내고 나서, 테이프를 하나 더 빌리는 경우, 임대정보를 기존에 입력한 내역에 대해서 추가를 하는 업무입니다. 따라서 임대정보 Table에 있는 총계 정보를 갱신하고, 임대품목에는 하나의 임대내역을 추가하여야 합니다.

먼저 BEGIN TRAN TEST_1은 Transaction에 대해서 TEST_1이라는 명칭을 부여하였습니다. 이 Transaction은 모든 전체적인 문장의 Transaction입니다. 먼저 임대정보 Table에 있는 임대번호가 '2000010602'인 Data에 대해서 총계를 1500으로 갱신을 하는 문장입니다. 그리고 그 다음 문장을 보시면, BEGIN TRAN TEST_2로 또 하나의 BEGIN TRANSACTION 문장을 사용하고 있습니다. 이것이 바로 중첩 Transaction입니다. 자세한 설명은 잠시 후 그림을 통해서 드리도록 하겠습니다.

그 다음은 임대품목에 하나의 임대내역을 추가하는 문장과 해당 테이프의 대여여부를 갱신하는 문장입니다.

다음의 문장이 좀 중요한데요 보시는 바와 같이, Error에 대한 검사를 하여, 에러가 발생된 경우에는 ROLLBACK TRAN 문장을 수행하고, 그렇지 않은 경우, 즉 에러가 발생하지 않은 경우에는 두 개의 Transaction을 모두 COMMIT하여 Database에 적용을 하는 문장입니다. Transaction은 두 개이기 때문에 COMMIT문장은 두 번 쓰인 것은 이해할 수 있지만, ROLLBACK 구문이 한 번 쓰인 이유는 무엇일까요?

우선 중첩 Transaction에 대해서 설명을 드리고 ROLLBACK 구문이 한 번 사용된 이유에 대해서 설명을 드리도록 하겠습니다.

아래의 그림은 앞선 문장의 Transaction을 간략하게 정리한 것입니다.



보시는 바와 같이 BEGIN TRANSACTION 구문을 이용하여 Transaction을 시작을 한 경우에는 반드시 COMMIT을 이용하여 Transaction을 마무리 하여야 합니다.

또한 처음 시작한 Transaction 안에 여러 개의 Transaction을 중복하여 사용을 할 수 있습니다. 즉 Transaction 안에 또 다른 Transaction을 만들 수 있다는 것입니다.

앞선 예제에서는 Transaction 마다 이름을 주었지만, 이들은 생략이 가능합니다.

만약 이들 이름을 생략하게 된다면 COMMIT TRAN TEST_2는 바로 앞서서 BEGIN TRANSACTION TEST_2를 이용하여 시작하였던 Transaction에 대해서 COMMIT를 실행합니다. 즉 COMMIT 구문에서 명칭을 부여하지 않은 경우에는 가장 가까이 위치한 Transaction에 대해서 COMMIT 구문을 적용하게 된다는 것입니다.

그럼 ROLLBACK 구문은 어떨까요?

물론 Transaction이 하나의 BEGIN TRANSACTION의 문장으로 시작한 경우, ROLLBACK구문은 해당 Transaction의 모든 연산을 취소하게 됩니다. 하지만 하나 이상의 Transaction. 즉 방금 전 살펴보았던, 중복 Transaction일 경우 어떻게 될까요?

ROLLBACK의 경우에는 중복된 Transaction의 개수와는 상관없이, 가장 먼저 시작한 Transaction에 대해서 Rollback을 수행하게 됩니다. 즉 해당 Transaction 안에서 몇 개의 Transaction이 있는지에 상관없이 전체 Transaction에 대한 실행취소를 하게 됩니다.

즉 BEGIN TRANSACTION에 의해서 시작된 수 만큼 COMMIT 문장을 실행하는 것과는 조금 다른데요. 앞선 예제에서 ROLLBACK 구문이 한 번 사용된 이유는 여기에 있습니다. 따라서 앞서 예제 문장에서 만약 에러가 발생되었다면, 모든 문장에 대한 연산을 취소하고 맨 처음 시작한 TEST_1 Transaction이 시작되기 전의 상태로 되돌아가게 됩니다.


2. SAVEPOINT

조금은 불편하죠? 예제는 몇 개 되지않은 연산이었지만, 만약 여러 연산을 한 번에 실행하여야 하는 경우에, 마지막 연산에서 에러가 발생하여 Rollback으로 Transaction을 취소하였다면, 서버자원의 상당한 낭비도 초래하게 됩니다. 앞선 예제에서 만약 TEST_2 Transaction에 대한 내역만 취소하고 나머지 연산은 성공할 수 있도록 한다면 이러한 낭비는 줄일 수 있을 것입니다.

이렇게 특정 시점까지만 ROLLBACK을 할 수 있도록 지원하는 것이 바로 SAVEPOINT입니다.

우선 SAVEPOINT의 기본적인 구문을 살펴보도록 하겠습니다.

SAVE TRAN[SACTION] {savepoint_name | @savepoint_variable}

보시는 바와 같이 COMMIT 구문이나 ROLLBACK 구문과 사용방법은 같습니다.

예제를 통해서 설명을 드리도록 하겠습니다.



그림을 보시면 우편번호 Table에 Data를 입력하는 3개의 입력연산이 있습니다.

보시는 바와 같이 두 번째 Line을 보시면, SAVE TRAN 구문 다음에 SAVEPOINT의 명칭을 명시하여 SAVEPOINT를 정의하고 있습니다.

이렇게 SAVEPOINT를 정의하고, 다시 ROLLBACK을 하기 위해서는 ROLLBACK TRANSACTION 문 다음에 SAVEPOINT로 지정된 명칭을 명시하여 주면 됩니다.

자세한 내용은 실습을 통해서 설명을 드리도록 하겠습니다.

3. Transaction의 처리순서

이렇게 SQL Server는 Transaction을 이용하여 Data의 일관성을 유지할 수 있도록 지원하고 있습니다. 그럼 SQL Server가 어떻게 내부적으로 Transaction을 처리하는지에 대해서 알아보도록 하겠습니다.



그림에서 보시는 것과 같은 문장을 실행하였을 경우, MSSQL Server는 어떠한 처리과정을 거치는지 알아보도록 하겠습니다. 앞선 강좌에서 미루었던 Log에 대한 부분도 여기서 설명을 드리도록 하겠습니다.

설명을 드리기 전에 우선, Cache에 대한 부분에 대해서 알아보도록 하겠습니다. 앞서서 MSSQL Server는 Data에 대한 부분은 Data 파일에, Log에 대한 부분은 Log 파일에 저장을 한다고 하였습니다.

이렇게 Data는 SQL Server가 설치된 Computer의 Hard Disk 상에 존재하게 됩니다. 그렇다면 사용자에 의해서 Data에 대한 반환요구가 있을 경우, 매번 Hard Disk에서 해당 Data를 읽어와서 반환을 하는 작업을 거쳐야 하는데요. 이는 사용자가 느끼는 SQL Server의 속도와도 관계가 있기 때문에 성능상의 문제로 발전하게 됩니다.

이미 알고 계시듯이 Hard Disk에서 Data를 읽는 속도는 RAM의 속도와는 비교가 안될 정도로 차이가 나게 됩니다. MSSQL Server는 이렇게 성능향상을 위해서 Computer에 있는 RAM에 일부 Data를 저장하여 놓게 되는데요. 이를 Cache라고 합니다. Data와 Log에 대한 부분이 Hard Disk 상에 다른 파일로 존재하듯이, Cache에도 Data와 Log를 따로 저장하도록 하고 있습니다. 이를 Data Cache와 Log Cache로 나누어서 RAM을 사용하고 있습니다.

다시 앞선 예제문장으로 돌아가도록 하겠습니다.

1) 먼저 Transaction을 시작하는 BEGIN TRAN 문장이 수행되면, MSSQL Server는 Log Cache에 Transaction이 시작되었음을 알리는 내용을 기록합니다.

2) 두 번째로 UPDATE문이 실행이 되면, MSSQL Server는 갱신할 Data가 Data Cache에 있는지를 보고서, 만약 있다면 이를 사용하게 되고, 그렇지 않은 경우에는 Hard Disk로부터 해당 Data를 읽어와 해당 Data의 복사본을 Data Cache에 기록을 하고, 갱신문장을 수행하여 Data를 갱신하게 됩니다. 이때 Data에 대한 갱신하는 작업은 Log Cache에 기록되어 집니다. 이때 Dirty Page라는 것이 발생하게 되는데요. 이는 Cache 상의 복사본 Data와 Hard Disk 상에 있는 Data의 내용이 틀린 것을 말합니다. 앞서서 UPDATE 문장에서 기존에 있던 주소 Data(즉, Hard Disk에 저장되어 있는 Data)와 UPDATE 문장에 의해서 변경된 복사본 Data(즉, Cache에 저장되어 있는 Data)의 내용이 다른 Page를 말하는 것입니다.

3) 마지막으로 COMMIT TRANSACTION 문에 의해서 Transaction이 성공적으로 마치게 되면, Dirty Page의 내용은 Hard Disk 상의 Log 파일에 기록이 됩니다.

이렇게 SQL Server는 복사본의 내용을 Data 파일에 기록하기 전에, Log Cache를 Hard Disk의 Log 파일에 기록을 하게 됩니다. 이를 Database에서는 먼저 쓰기 로그(Write Ahead Log)라고 부릅니다.

Log만 기록하면 Data Cache에 기록된 Data는 언제 Hard Disk로 기록을 할까요? 다른 사용자가 해당 Data를 Hard Disk에서 읽어가면 갱신된 Data가 아니라 갱신되기 이전의 Data를 읽어가게 되는데, 그럼 Data의 일관성을 해치는 결과를 낳게 되는데. 이 문제들은 어떻게 할까요?

우선 첫번째로 Data Cache에 있는 변경된 Data는 Checkpoint나 Lazy Writer에 의해서 Hard Disk로 변경된 Data를 옮기게 됩니다. 바로 뒤에서 설명을 드리겠습니다.

두 번째, 다른 사용자가 Hard Disk 상에 있는 갱신되기 전의 Data를 읽어가면 어떻게 할까요? 이 문제는 걱정을 하지 않으셔도 됩니다. 앞서서 Data Cache에 대한 부분을 말씀을 드렸는데요. 만약 해당 Data에 대해서 다른 사용자가 사용을 하려 한다면, MSSQL Server는 Data Cache에 해당 Data가 있는지를 찾아보고 있다면, 그 Data를 사용하기 때문에, 다른 사용자가 갱신되지 않은 Data를 Hard Disk로부터 읽어가는 불상사는 일어나지 않습니다.

자, 그럼 방금 전 말씀을 드렸던 Checkpoint와 Lazy Writer는 무엇일까요? 하나하나 설명을 드리도록 하겠습니다.

- Checkpoint

우선 Checkpoint는 말 그대로 '검사점'입니다. 이 검사점 명령이 발생되면, SQL Server는 Cache에 있는 Data와 Log를 Hard Disk에 기록하게 됩니다. 즉, Hard Disk에 있는 내용과 사용자의 변경에 의해서 달라진 내용. 즉 Dirty Page의 내용을 Hard Disk로 넘겨주는 것입니다. 이는 언제 발생되는지는 알 수 없으며, 다만 Transaction의 사용정도와 Memory의 cache의 크기에 따라서 비주기적으로 발생됩니다.

- Lazy Writer

Computer 상의 RAM은 그 크기가 한정되어 있기 때문에, Cache 역시 그 크기가 한정되어 있습니다. 만약 Cache가 꽉 찬다면 어떻게 될까요? 이러한 경우에도 걱정하실 필요가 없습니다. Lazy Writer라는 것이 바로 Cache에 있는 내용을 바로 Hard Disk로 옮겨 적어주기 때문입니다.

이렇게 Cache에 있는 내용을 물리적인 Disk로 적어주는 기능은 Checkpoint와 Lazy Writer에 의해서 이루어지게 됩니다.

앞서서는 BEGIN TRANSACTION, COMMIT, ROLLBACK 등의 구문을 이용하여 Data의 일관성을 유지하는 것에 대해서 알아보았습니다. 이 외에도 MSSQL Server는 정전 또는 기타 다른 문제에 의해서 오류가 발생했을 경우에도 Data의 일관성을 유지할 수 있도록 지원을 하고 있습니다.

4. Transaction의 Recovery(복구)

Database에서 System 실패가 발생하는 경우는 여러 가지가 있습니다. 우선 Transaction 안에서 Overflow가 발생된다든지 하는 지역적인(Local) 고장과 정전이나 CPU의 고장, Hard Disk의 고장 등의 전역적인(Global) 고장 등이 있습니다. 지역적인 고장의 경우에는 해당 Transaction에만 영향을 미치지만, 전역적인 고장의 경우에는 고장 시 진행 중이던 Transaction에 치명적인 영향을 미치게 됩니다. 이런 경우 Database의 Data의 일관성이 깨어지게 되기 때문에, 이전의 일관된 상태로 되돌리기 위한 복구(Recovery) 작업이 필요로 하게 됩니다.

이러한 시스템 복구(Recovery)작업에 대해서 고장유형별로 알아보도록 하겠습니다. 먼저 시스템 고장일 경우의 시스템 복구에 대해서 알아보도록 하겠습니다.

▣ System 고장

시스템 고장은 예를 들어 정전과 같은 경우가 발생되는 경우, 현재 진행 중인 Transaction에 치명적인 영향을 미치게 됩니다. 이러한 경우에는 운영체제가 다시 시작될 때, MSSQL Server가 자동적으로 Transaction에 대한 내용을 다시 시작하거나(redo, roll forward), Transaction에 대한 내용을 취소하든지(undo, rollback) 하도록 지원을 합니다. 이러한 경우 시스템이 실패하였기 때문에, Cache에 기록된 내용은 이미 알 수가 없으므로, Log 파일의 기록에 의존할 수밖에 없습니다. 따라서 Log 파일에 기록된 내용을 읽어서 Database의 내용을 바꾸게 됩니다.

그럼 어떻게 Transaction의 내용을 복구하는지에 대해서 알아보도록 하겠습니다.



우선 그림을 보시면 5개의 Transaction이 시간별로 실행되고 종료되는 것을 표로 작성을 하였습니다. 보시는 바와 같이 일부는 Checkpoint가 발생되기 전에 이미 Commit이 되었고, 또 다른 Transaction은 Commit이 되기 전에 정전으로 인해서 Transaction에 대한 정보를 잃어버리게 되었습니다. 이들 각각의 Recovery(복구)작업은 다른 형태로 진행되게 됩니다.

자세한 내용은 실습을 통해서 설명을 드리도록 하겠습니다.

▣ 장치고장

다음은 장치고장의 경우입니다. 장치고장은 예를 들어 Hard Disk의 고장 또는 Disk Controller와 같은 Hardware의 고장 등이 있을 수 있습니다. 이러한 경우는 시스템 고장보다 더욱 심각한 경우인데요. 이 경우에는 앞서 시스템 고장과 같이 SQL Server가 자체적으로 복구를 할 수 없는 경우입니다. 따라서 관리자에 의해서 Data에 대한 복구가 이루어져야만 합니다. 장치고장에 대한 복구는 Database의 Backup과 Restore와 관련된 부분에서 자세하게 설명을 드리도록 하겠습니다.

5. Consideration for Using Transactions

Data의 일관성을 위해서 Transaction을 사용하는 것은 좋은 습관입니다. 하지만 무분별하게 Transaction을 오랫동안 사용하고 있다든지, 중첩 Transaction을 남용하는 것은 상당히 위험합니다. 이렇게 Transaction을 사용하는데 있어서 몇 가지 주의해야 할 점이 있습니다.

▣ Transaction Guidelines

우선 Transaction에 대한 주의할 점에 대해서 알아보도록 하겠습니다.

  • 가능하면 Transaction은 짧게 사용하여야만 합니다. 특히 WHILE문과 같이 Loop를 수행하는 문장이나 Data Definition Language와 같은 구문은 짧게 사용할수록 좋습니다. 이는 Lock과도 연관이 되는데요. Lock에 대해서는 뒤에서 설명을 드리도록 하겠습니다.
  • Transaction을 시작하기 전에, 필요한 사용자의 정보는 미리 받아 놓는 것이 좋습니다. 만약 Transaction이 정의된 상태에서 사용자의 정보를 얻거나 사용자에 의해서 입력을 받아야 하는 경우에는 Transaction이 수행되고 있는 동안에, 사용자가 해당 Data를 입력하지 않고 있다면, Transaction은 종료되지 않고 사용자의 입력을 계속해서 기다리게 됩니다.
  • Transaction 내에서 INSERT, UPDATE, DELETE 구문이 주가 되어야만 합니다. 또한 이들 구문에 의해서 영향을 받는 Row는 적어야만 합니다.

▣ Issues in Nesting Transactions

다음은 중첩 Transaction에 대한 내용입니다.

중첩 Transaction의 사용은 제한이 없지만, 가능하면 사용하지 않는 것이 좋습니다. 앞서 중첩 Transaction에서 설명을 드린 것과 같이 중첩된 Transaction과는 상관없이 가장 먼저 정의된 Transaction이 Commit 되었는지, 아니면 Rollback 되었는지에 따라서 Transaction의 성공여부가 결정되기 때문에 중첩 Transaction은 의미를 가지지 못합니다.

@@trancount라는 전역변수를 사용하여 현재 정의되어 있는 Transaction의 수를 반환 받을 수 있습니다. 만약 @@trancount가 0인 경우에는 어떠한 Transaction도 정의되어 있지 않다는 것을 뜻합니다. 또한 DBCC OPENTRAN 구문을 이용하여도 Transaction에 대한 정보를 얻을 수 있습니다.

6. Setting the Implicit Transactions Option

앞서서 묵시적 Transaction에 대해서 설명을 드렸습니다. 묵시적 Transaction 역시 나름대로 장점을 가지고 있지만, 항상 BEGIN TRAN으로 Transaction을 시작하고, COMMIT이나 ROLLBACK 구문을 이용하여 Transaction을 종료하는 것이 잘못된 입력을 막아서 Data의 일관성을 지킬 수 있도록 할 수 있기 때문입니다.

묵시적 Transaction Option은 이렇게 Transaction을 정의하는 것을 좀 더 쉽게 할 수 있도록 지원을 하고 있습니다. 묵시적 Transaction을 설정하는 기본적인 문장은 다음과 같습니다.

SET IMPLICIT_TRANSACTIONS {ON | OFF}

만약 IMPLICIT_TRANSACTION의 Option을 ON으로 하였다면, 특정 문장이 시작되면, 그 전에 BEGIN TRAN이라는 구문을 SQL Server가 자동으로 실행하여 줍니다. BEGIN TRAN 구문을 사용자가 직접 정의하지는 않았지만, SQL Server에서 정의를 해주었기 때문에 이 문장은 반드시 COMMIT 또는 ROLLBACK 구문을 이용하여 종료하여야 합니다.이렇게 MSSQL Server가 자동으로 BEGIN TRAN 문장을 실행하여 주기 때문에, 중첩 Transaction에 대한 정의는 불가능합니다.

또한 다음의 문장이 실행되면, MSSQL Server는 암시적으로 BEGIN TRAN 구문을 실행하여 Transaction을 시작하게 됩니다.

  • ALTER TABLE
  • CREATE
  • DELETE
  • DROP
  • FETCH
  • GRANT
  • INSERT
  • OPEN
  • REVOKE
  • SELECT
  • TRUNCATE TABLE
  • UPDATE

묵시적 Transaction Option의 기본값은 OFF이기 때문에 이를 사용하기 위해서는 반드시 SET 구문을 이용하여 암시적 Transaction의 Option을 ON으로 변경하여 주어야 합니다.

자세한 사용 예는 실습을 통해서 설명을 드리도록 하겠습니다

여기서 주의하여야 할 점은 묵시적 Transaction으로 정의된 문장은 반드시 COMMIT나 ROOLBACK 구문을 이용하여 Transaction을 종료하여야만 합니다.

7. Restrictions on User-Defined Transactions

사용자 지정 Transaction에는 몇 가지 제약이 있습니다. 몇몇 System Stored Procedure는 사용자 지정 Transaction에서 사용될 수 없습니다. 그 이유는 임시 Table을 생성하기 때문입니다. 예를 들어 sp_dboption과 같은 System Stored Procedure는 사용될 수 없습니다.

또한 다음의 문장들 역시 명시적 Transaction에서는 사용될 수 없습니다.

  • ALTER DATABASE
  • BACKUP LOG
  • CREATE DATABASE
  • DROP DATABASE
  • RECONFIGURE
  • RESTORE DATABASE
  • RESTORE LOG
  • UPDATE STATISTICS

 


Posted by 경제/부동산/프로그래머/안드로이드/PHP/mysql/asp 당양부부
이전버튼 1 2 3 이전버튼

블로그 이미지
경제를 좋아하는 일산의 행복한 프로그래머입니다. 감사합니다.
당양부부
Yesterday54
Today25
Total113,515

달력

 « |  » 2018.10
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

최근에 달린 댓글

최근에 받은 트랙백

글 보관함