DataBase/MS-SQL

[MS-SQL] OUTER APPLY 와 CROSS APPLY 의 사용 방법 예제 및 APPLY 연산자를 사용하는 이유

본론으로 먼저 들어가보자. CROSS APPLY는 INNER JOIN 동일한 쿼리 실행 결과와 동일하며, OUTER APPLY는 LEFT JOIN과 동일한 쿼리 실행 결과와 동일하다. 그렇다면 APPLY 연산자를 언제, 왜 사용하는 것일까? 일반적으로 JOIN을 사용하여 동일한 결과를 얻을 수 있는데 말이다. 그 이유는 이렇다. 첫번째로 APPLY의 경우 테이블 반환 함수를 생성하여 조인해야할 경우에 사용한다. 만약 테이블 반환 함수와 JOIN을 사용하면 오류가 발생한다. 두번째로 APPLY 연산자를 사용하면 쿼리 성능이 향상된다고 한다. 스칼라 서브쿼리를 사용하여 공통코드의 이름을 가져오는 것 보다 APPLY 연산자를 사용하여 가져오는게 쿼리 성능면에서 좋다. 그렇다면 그 내용을 살펴보기 위해 테이블을..

2022. 8. 25. 18:06

DataBase/MS-SQL

[MSSQL] 소수점관리 반올림(ROUND), 올림(CEILING), 내림(FLOOR), FORMAT함수를 사용한 0 자릿수 채우기, 문자열 반복 REPLICATE 함수

MSSQL 소수점관리 반올림, 올림, 내림 관련하여 ROUND, CEILING, FLOOR 함수에 대한 사용법을 알아보자. 다음과 같이 소숫점 자리를 제외한 정수값을 리턴받는 자바스크립트 예시가 있다. const rate = Math.round(e.actCnt > 0 ? (e.actCnt / e.totActCnt) * 100 : 0); 자바스크립트에서 ROUND 처리의 결과와 MSSQL의 CEILING 함수를 사용한 리턴 결과는 동일하게 소숫점 자리가 없는 정수이지만 결과값이 다른 경우가 발생한다. 데이터를 통일해야하는 경우 쿼리에서 값을 계산 후 가져와서 사용하자. ROUND 함수 사용법 (반올림) ROUND (대상 값, 반올림 자릿수, 반올림 여부(0은 반올림, 1은 내림)) [기본 예제] 반올림 자..

2022. 8. 19. 15:32

DataBase/MS-SQL

[MSSQL] 쿼리 조회 시간이 오래 걸리는 쿼리에 대한 성능 분석하는 방법 (SET STATISTICS IO, SET STATISTICS TIME )

쿼리(Query) 실행시간이 오래 걸리는 쿼리가 있을 때 실행시간을 줄이기 위해(오래걸리는 문제를 해결) 쿼리의 성능을 분석해야하는 상황이 발생한다. 프로파일러 및 실행 계획을 사용하는 것도 방법이지만 SET STATISTICS IO 및 SET STATISTICS TIME 사용하여 확인하는 방법에 대해 알아보자. -- 컴파일 시간 및 실행 시간 SET STATISTICS TIME ON; -- 각 구문의 IO 실행 결과 SET STATISTICS IO ON; STATISTICS IO 옵션을 ON으로 설정하면 통계 정보가 표시된다. SET STATISTICS TIME 옵션을 ON으로 설정하면 문에 대한 시간 통계가 표시된다. SET STATISTICS TIME ON 문은 쿼리의 실행 시간 통계에 대한 매우 ..

2022. 8. 19. 12:53

DataBase/MS-SQL

[MS-SQL]SQL Server의 쿼리에서 열 이름(column names)을 가져오는 방법

엑셀 다운로드를 위한 쿼리를 작성하였는데 엑셀 헤더값으로 들어갈 컬럼을 동적으로 가져와야하는 상황이 생겼다. 해결방법은 SYS 테이블을 이용하는 것이다. SELECT name FROM sys.dm_exec_describe_first_result_set ('작성한 쿼리문', NULL, 0) ; 가령 TB_USER 라는 테이블을 전체 조회했을 때 다음과 같이 작은 따옴표로 묶어서 스트링값으로 기입한다. 그렇게 하면 조회한 데이터들의 컬럼 값을 확인할 수 있다. SELECT name FROM sys.dm_exec_describe_first_result_set ('select * from TB_USER', NULL, 0) ; 만약, 프로시저에서 확인해야하는 경우라면 다음 예시와 같이 동적쿼리를 생성하면 유용하게..

2022. 8. 17. 16:24

DataBase/MS-SQL

[MS-SQL] 이전행의 특정 컬럼값이나 다음행의 특정 컬럼값 가져오는 방법(LAG, LEAD)

특정 데이터의 기간 데이터를 가져와야할 경우가 있다. 시작일과 종료일이 있다면 수월하게 조회쿼리를 작성할 수 있지만 시작일과 종료일이 아닌 등록일이나 수정일로 기간데이터를 가져와야 할경우 어떻게 하면 좋을까? SQL Server 2012 버전 부터 이전 행과 다음 행의 값을 가져올 수 있는 LAG, LEAD 함수를 제공해주고 있다. 그럼 시작해볼까?? 쿼리 테스트를 위한 사전 작업 1. 테스트 쿼리 작성을 위해 임시 테이블을 생성 한다. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TB_GRADE_SAMPLE]( [SEQ_ID] [bigint] IDENTITY(100000,1) NOT ..

2022. 8. 9. 14:18

DataBase/MS-SQL

[MSSQL] 이전월 1일부터 말일까지 검색 조건 생성(통계 쿼리 작성시)

현재일자를 기준으로 이전월 통계 자료를 구해야하는 경우, 현재일자를 기준으로 전월 시작일과 종료일을 구하는 방법에 대해 알아본다. DATEADD 함수를 사용하면 쉽게 해결할 수 있으며, DATEPART 구분자를 사용하여 년,월,일,시간,분 등의 차이값을 구할 수 있다. datepart 이름 datepart약어 year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns 기본예제 SELECT DATEADD(month, 1, '20220705'); SELECT DAT..

2022. 7. 5. 09:37

DataBase/MS-SQL

[MSSQL] 날짜와 날짜 사이의 차이 일수 구하는 방법 : DATEDIFF

시작일과 종료일 사이의 차이 일수를 구하는 방법으로 DATEDIFF 함수를 사용하면 쉽게 구할 수 있다. DATEPART 구분자를 사용하여 년,월,일,시간,분 등의 차이를 확인할 수 있다. SELECT DATEDIFF(DAY, '2022-07-04', '2022-07-10') -- 몇일 차이 나는지 확인 SELECT DATEDIFF(M, '2022-07-04', '2022-07-10') -- 몇 개월 차이 나는지 확인 SELECT DATEDIFF(YEAR, '2022-07-04', '2022-07-10') -- 몇 차이 나는지 확인 DATEPART 구분자는 풀네임을 사용해도 되고, 약어를 사용해도 된다. 결과는 동일하다. SELECT DATEDIFF(MONTH, '2022-07-04', '2022-07-..

2022. 7. 4. 22:17

DataBase/MS-SQL

[mssql] 트리 구조의 데이터에서 자식노드(하위노드)의 부모노드(상위노드) 찾는 방법(재귀 쿼리)

트리구조로 구성된 테이블의 데이터에서 자식노드가 속한 부모노드를 모두 찾는 함수를 구현하였다. MS-SQL 2005부터 사용가능해진 재귀쿼리를 이용하여 트리구조의 데이터에 대한 어떤 값을 찾아올 때 유용하다. WITH TEMP AS ( SELECT 컬럼 FROM 테이블명 UNION ALL SELECT 컬럼 FROM 테이블명 INNER JOIN TEMP ON 테이블명.컬럼 = TEMP.컬럼 ) CREATE FUNCTION FN_GET_P_NODE ( @DEPT_IDBIGINT ) RETURNS @TB_TREE_DEPT TABLE ( DEPT_IDBIGINT ,PARENT_DEPT_IDBIGINT ) AS BEGIN WITH CTE_DEPT AS ( SELECTDEPT_ID ,PARENT_DEPT_ID FR..

2022. 6. 21. 23:47

DataBase/MS-SQL

[MSSQL] 날짜 시간 형식 포맷 변환표convert()

CAST 및 CONVERT(Transact-SQL) [구문] -- CAST Syntax: CAST ( expression AS data_type [ ( length ) ] ) -- CONVERT Syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 쿼리 실행 결과 리턴 코드 타입 CONVERT(CHAR(19), GETDATE(), 0) 01 02 2022 1:14PM MM DD YYYY H:MM CONVERT(CHAR(10), GETDATE(), 1) 01/02/2022 MM/DD/YYYY CONVERT(CHAR(8), GETDATE(), 2) 00.01.02 YY.MM.DD CONVERT(CHAR(8), GETDATE(), 3) 0..

2022. 5. 2. 22:34

DataBase/MS-SQL

MSSQL 날짜 포맷 형식 타입 변경 및 시간 포맷 변경 코드표

날짜 변환 및 시간 변환이 필요할 때 참고하자!! GETDATE()는 오늘 날짜를 의미한다. 쿼리 결과 코드 CONVERT(VARCHAR(19), GETDATE(), 0) 01 02 2022 1:14PM MM DD YYYY H:MM CONVERT(VARCHAR(10), GETDATE(), 1) 01/02/2022 MM/DD/YYYY CONVERT(VARCHAR(8), GETDATE(), 2) 00.01.02 YY.MM.DD CONVERT(VARCHAR(8), GETDATE(), 3) 02/01/00 DD/MM/YY CONVERT(VARCHAR(8), GETDATE(), 4) 02.01.00 DD.MM.YY CONVERT(VARCHAR(8), GETDATE(), 5) 02-01-00 DD-MM-YY CON..

2022. 2. 25. 20:05

DataBase/MS-SQL

[MSSQL]프로시저 내에서 CURSOR 대신 WHILE 사용하여 반복처리하는 방법

프로시저 내에서 여러가지 작업을 반복적으로 처리해야할 때 CURSOR를 사용해도 되지만 WHILE문을 사용하여 FOREACH문과 동일한 효과를 볼 수 있다. 프로시저내에서 WHILE문을 사용하여 WHILE문 안에서 여러 작업을 처리해야할 경우, 조회한 결과 리스트에 RONUM 칼럼을 생성 후 ROWNUM의 조건에 WHILE문의 조건문을 추가하여 처리하는 경우 여러개의 데이터가 있을 때(ROW별 데이터는 다른 경우) 조회결과가 가장 마지막값으로 처리되는 문제가 생긴다. 그럼으로 이럴 때는 반드시 테이블 변수를 선언하거나 임시테이블을 생성 후 조회결과를 먼저 INSERT 처리 후 WHILE문에서 임시테이블이나 테이블 변수를 반복처리해야한다. 그래야 WHILE문의 오류(?)를 범할 수 있다. 그 샘플 스크립트..

2022. 2. 23. 09:28

DataBase/MS-SQL

[MS-SQL] EXEC 프로시저 실행 후 프로시저 내에서 SELECT 결과값 리턴 받는 방법

프로시저내에서 프로시저를 실행하여 리턴값을 전달 받고 싶은 경우에 대한 처리 방법입니다. 1. 프로시저 실행 후 리턴값이 개별일경우 ( 1개 이거나 각각 리턴이거나) 첫번째 프로시져 생성합니다. CREATE PROCEDURE dbo.SP_FIRST ( @IDX int ,@RTN_VAL1 varchar(10) OUTPUT ,@RTN_VAL2 int OUTPUT ) AS Begin SET @RTN_VAL1 = '집에가자' SET @RTN_VAL2 = 11 RETURN End GO 두번째 프로시저 생성 후 첫번째 프로시저를 호출하여 리턴값을 변수로 전달 받습니다. CREATE PROCEDURE dbo.SP_SECOND ( @SECOND_IDX int ) AS Begin Declare @FIRST_VAL var..

2022. 2. 14. 17:44

DataBase/MS-SQL

[MS-SQL] 공통코드에 따른 시간 체크 스크립트

다음 스크립트는 현재시간 기준으로 7시부터 22시까지 15분 단위로 시간를 체크하여 어느 시간 영역에 포함되는지 확인 후 리턴값을 반환하는 스크립트 입니다. DECLARE @P_TIME_CDVARCHAR(10) = '' SET @P_TIME_CD = ( SELECT CASE WHEN CONVERT(time,GETDATE()) = CONVERT(time, '07:15') AND CONVERT(time,GETDATE()) = CONVERT(time, '07:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '07:45') THEN '14' WHEN CONV..

2022. 1. 18. 15:51

DataBase/MS-SQL

[MS-SQL] ORDER BY 번호 1(으)로 식별되는 SELECT 항목에 열 위치를 식별하는 식의 일부로 변수가 포함되어 있습니다. 변수는 열 이름을 참조하는 식을 기준으로 정렬할 때만 사용할 수 있습니다

프로시저 내의 SELECT 쿼리의 ORDER BY 절에 @PARAM 파라미터 변수를 대입하였더니 발생한 오류이다. ORDER BY 번호 1(으)로 식별되는 SELECT 항목에 열 위치를 식별하는 식의 일부로 변수가 포함되어 있습니다. 변수는 열 이름을 참조하는 식을 기준으로 정렬할 때만 사용할 수 있습니다 오류가 발생한 쿼리문은 다음과 같다. SELECT * FROM TB_TEMP_TEST WHERE a.CUSTOMER_ID = @P_CUST_ID AND ( @P_SEARCH_STR = '' OR ( a.TEL_NO LIKE '%'+@P_SEARCH_STR+'%' OR a.MOBILE_NO LIKE '%'+@P_SEARCH_STR+'%' ) ) ORDER BY @P_ORDER_BY_TP 마이크로소프트가 ..

2022. 1. 14. 11:01

DataBase/MS-SQL

[MS-SQL] 데이터 마이그레이션을 위한 스크립트 작성 및 오라클에 있는 ROWNUM 만드는 방법

엑셀파일 데이터를 데이터베이스에 INSERT 작업 후 실제로 사용하는 테이블에 데이터를 등록해야할 때 사용하면 유용한 스크립트 작성을 하였습니다. 커서(CURSOR)를 사용하여 반복처리해도 되지만 조회 리스트에 순차적인 번호를 조회시 생성하여 질의한 후 WHILE문을 통해 데이터를 INSERT하는 방법입니다. 이 방법은 기존 데이터는 모두 삭제 처리 후 신규로 밀어넣는다는 조건으로 처리되었습니다. 기존 데이터를 살리면서 업데이트 혹은 INSERT를 해야한다면 MERGE문을 사용하거나 조건을 걸어 UPDATE 또는 INSERT문을 분기하면 됩니다. ROWNUM 만들기 구문(문법) ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] ord..

2022. 1. 3. 16:51