해결 방안 : 스택 오버플로우에 답변을 올려주신 분이 있어서, 해당 코드를 참고하였다. (매우 감사한 분이다.)
GetCreateIfNotExistPool 함수명이 싫어서 기존과 동일한 connect 로 함수를 변경하여 사용했다.
1. 여러 MSSQL DB의 커넥션 풀을 관리하는 mssql-connection-pooling.js 파일을 하나 만든다.
- 이 파일에서는 여러 MSSQL DB의 커넥션 풀을 pools 객체에 넣어관리한다. config 객체를 문자열로 만든 뒤 key로 사용한다.
connection을 요청할 때 동일한 key가 있다면 pools에서 해당 pool을 제공한다.
동일한 key가 없으면 새로운 pool을 연결하여 pools 에 새로 넣어놓고 pool을 제공한다.
2. mssql-connection-pooling.js 파일을 기존 코드에 불러와 사용한다.
// mssql-connection-pooling.js 파일 코드
const { ConnectionPool } = require('mssql');
const pools = {};
// 새로운 커넥션 풀 생성
function CreatePool(config) {
let key = JSON.stringify(config);
if (GetPool(key)) {
throw new Error('Pool already exists');
}
pools[key] = (new ConnectionPool(config)).connect();
return pools[key];
}
// pools 안에 생성된 connection pool 받기
function GetPool(name) {
if (pools[name]) {
return pools[name];
} else {
return null;
}
}
// pool이 생서되어 있으면 return pool, 없으면 새로 생성
function connect(config) {
let key = JSON.stringify(config);
let pool = GetPool(key);
if (pool) {
return pool;
} else {
return CreatePool(config);
}
}
function ClosePool(config) {
let key = JSON.stringify(config);
if (pools[key]) {
const pool = pools[key];
delete pools[key];
pool.close();
return true;
}
return false;
}
// 모든 pool 종료
function CloseAllPools() {
pools.forEach((pool) => {
pool.close();
});
pools = {};
return true;
}
module.exports = {
ClosePool,
CloseAllPools,
CreatePool,
GetPool,
connect,
}
쿼리의 마지막 라인에 사용한 임시테이블을 제거(DROP)하는 방법으로 쿼리 작성도 가능하지만
실행 시 임시테이블이 있는지 확인하고 있다면 지우고 다시 생성하도록 하는 방법도 있다.
SELECT *
INTO #TEMP_TT
FROM (
SELECT 'A' AS T UNION ALL
SELECT 'B' AS T UNION ALL
SELECT 'C' AS T
) A
SELECT *
FROM #TEMP_TT
<임시테이블에 데이터를 입력하고 입력된 데이터를 확인하는 쿼리>
위 쿼리를 처음 실행하면 아래와 같이 정상적으로 실행된다.
정상실행 화면
위 쿼리를 다시한번 실행하면 아래와 같은 오류가 발생한다.
MSSQL ERROR MESSAGE - 메시지 2714, 수준 16, 상태 6, 줄 2
데이터베이스에 '#TEMP_TT'(이)라는 개체가 이미 있습니다.
쿼리 상단에 아래와 같은 코드를 넣어주면, 쿼리 실행 시 임시테이블의 존재 여부를 확인하고
임시테이블이 있다면 제거 후 쿼리를 실행하게 된다.
* #이 2개 붙어있는 전역 임시테이블에 사용할 때는 다른 SESSION에서 사용되고 있을 수 있으니 주의해야한다.
IF OBJECT_ID('TEMPDB..#TEMP_TT') IS NOT NULL
BEGIN
DROP TABLE #TEMP_TT
PRINT 'DROP TABLE #TEMP_TT.'
END
ELSE
BEGIN
PRINT '#TEMP_TT IS NOT EXISTS.'
END
SELECT *
INTO #TEMP_TT
FROM (
SELECT 'A' AS T UNION ALL
SELECT 'B' AS T UNION ALL
SELECT 'C' AS T
) A
SELECT *
FROM #TEMP_TT
임시테이블이 있다면 제거 후 쿼리를 실행할 수 있도록 조건문 추가.
위 쿼리를 실행하면 아래와 같이 임시테이블 존재 여부에 따라 메시지가 표시되며, 정상 실행된다.
숫자형 데이터를 문자형 데이터로 변경할 때 앞에 0을 채워서 지정된 길이로 만들어 사용하고 싶을 때가 있습니다.
단순히 CONVERT를 사용하면 앞에 0이 채워지지 않은 상태로 숫자만 문자형으로 변경되기 때문에
다른 방법을 사용해야 합니다.
FORMAT 함수를 사용하면 앞에 0을 채워 지정한 길이의 문자열로 만들어 사용할 수 있습니다.
-- FORMAT 함수를 사용하여 숫자를 10자리 문자열로 변경
SELECT FORMAT(1230,'0000000000') AS FORMATTED_TEXT
-- CONVERT 함수를 사용하여 숫자를 문자로 변경
SELECT CONVERT(NVARCHAR(10),1230) AS CONVERTED_TEXT
위 코드를 실행하면 아래와 같은 결과가 나옵니다.
코드 실행 결과
기타 다른 상황에 대한 실행 결과
-- 숫자 길이가 서식보다 길 때
SELECT FORMAT(12345678901234567890,'0000000000') AS FORMATTED_OVER_TEXT
-- 숫자를 3자리 마다 쉼표로 구분하고 싶을 때
SELECT FORMAT(12345678901234567890,'0,000,000,000') AS FORMATTED_COMMAS_TEXT
-- 숫자 앞에 문자를 고정으로 넣어주고 싶을 때
SELECT FORMAT(12345678901234567890,'FORMATTED_0000000000') AS FORMATTED_COMMAS_TEXT
LAG(컬럼명, 이전 행 수, 이전 행이 없을 때 기본값) OVER ( PARTITION BY 그룹으로 묶을 컬럼 리스트
ORDER BY 이전행을 찾을 때 사용할 정렬 기준)
파란색 으로 표시한 글자 생략 가능
/*********************************
* 이전 행 데이터 가져와 계산 TEST 쿼리
**********************************/
-- 1. TEST에 필요한 BASE DATA 만들기
SELECT A.ITEM
, A.VALUE
INTO #TEMP1
FROM (
SELECT 'A' ITEM, 1 VALUE UNION ALL
SELECT 'A' ITEM, 2 VALUE UNION ALL
SELECT 'A' ITEM, 3 VALUE UNION ALL
SELECT 'A' ITEM, 4 VALUE UNION ALL
SELECT 'A' ITEM, 5 VALUE UNION ALL
SELECT 'B' ITEM, 6 VALUE UNION ALL
SELECT 'B' ITEM, 7 VALUE UNION ALL
SELECT 'B' ITEM, 8 VALUE UNION ALL
SELECT 'B' ITEM, 9 VALUE UNION ALL
SELECT 'B' ITEM, 10 VALUE
) A
-- 2. BASE DATA 확인
SELECT *
FROM #TEMP1
-- 3. LAG 윈도우 함수 사용
SELECT ITEM
, VALUE
, LAG(VALUE,1,0) OVER( PARTITION BY ITEM ORDER BY VALUE ) STACKED_SUM
FROM #TEMP1
VIEW SERVER STATE 권한이 있는 sa 와 같은 계정으로 실행해야 정상 실행됨.
-- Table의 Index 별 사용 횟수 확인.
-- USER_SEEK : 정상적으로 INDEX만 사용하여 검색됨.
-- USER_SCANS : INDEX 내에서 SCAN됨.
-- USER_LOOKUPS : INDEX 내에서 조회됨.
-- RANGE_SCAN_COUNT : INDEX에서 범위로 찾은 후 데이터에서 다시 조회됨.
-- INDEX KEY, PK 외 다른 조건이 붙어 있는 경우.
DECLARE @V_WORK_DATE NVARCHAR(8) = CONVERT(NVARCHAR,GETDATE(),112)
SELECT LEFT(@V_WORK_DATE,4) + '-' + SUBSTRING(@V_WORK_DATE,5,2) + '-' + RIGHT(@V_WORK_DATE,2) AS WORK_DATE
, OBJECT_NAME(IUS.OBJECT_ID) AS TABLE_NAME
, I.NAME AS INDEX_NAME
, IUS.INDEX_ID
, IZ.INDEX_SIZE
, IUS.USER_SEEKS
, IUS.USER_SCANS
, IUS.USER_LOOKUPS
, IOS.RANGE_SCAN_COUNT
FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS
INNER JOIN SYS.INDEXES AS I
ON I.OBJECT_ID = IUS.OBJECT_ID
AND I.INDEX_ID = IUS.INDEX_ID
INNER JOIN SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(),null,NULL,NULL) AS IOS
ON IOS.OBJECT_ID = IUS.OBJECT_ID
AND IOS.INDEX_ID = IUS.INDEX_ID
INNER JOIN
(
SELECT I.OBJECT_ID
, I.INDEX_ID AS INDEX_ID
, SUM(AU.USED_PAGES) * 8 / 1024 AS 'INDEX_SIZE'
FROM SYS.INDEXES I
INNER JOIN SYS.PARTITIONS AS P
ON P.OBJECT_ID = I.OBJECT_ID
AND P.INDEX_ID = I.INDEX_ID
INNER JOIN SYS.ALLOCATION_UNITS AS AU
ON AU.CONTAINER_ID = P.PARTITION_ID
WHERE 1=1--I.object_id = OBJECT_ID(@V_TABLE_NAME)
GROUP BY I.OBJECT_ID
, I.INDEX_ID
) AS IZ
ON IZ.OBJECT_ID = I.OBJECT_ID
AND IZ.INDEX_ID = I.INDEX_ID
WHERE 1=1 --IUS.OBJECT_ID = OBJECT_ID(@V_TABLE_NAME)
AND IUS.DATABASE_ID = DB_ID()
ORDER BY 1,2,4
