평범한 프로그래머

데이터베이스에서 루프문을 사용하고 싶은경우가 있습니다. 성능테스트를 위하여 일괄로 테스트데이터를 만들어 넣거나 반복해서 처리해야될 경우가 있습니다. 이때는 WHILE문을 사용하면됩니다. While문 사용예시 while문을 조건이 만족할때까지 루프문을 반복합니다. 아래 예제의 경우는 Address테이블에 임의의값을 넣는부분으로 @iLoopCnt값이 30보다 작거나 같을경우에 반복합니다. 이와같이 루프문을 이용하면 테이블에 테스트 데이터를 넣을수 있고 해당부분으로 테스트 할수 있습니다. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 declare @iLoopCnt int declare @iCnt int set @iLoopCnt =0 set @iCnt = 30 --반복처리 whil..

오라클,MSSQL,MySql등 다양항 DBMS를 관리할수 있는 툴이 있어서 소개시켜드리겠습니다. 프로젝트를 하다보면 데이터베이스를 평소에 자주 사용하지 않고 연동등의 목적으로 잠깐 사용해야될경우가 있습니다. 이때 DBMS별로 관리툴을 설치해야되는 번거러움이 있는데 DBeaver를 사용하면 간단하게 사용할수있습니다. 먼저 설치를 해보겠습니다. 아래사이트에서 설치파일을 다운로드 받습니다. 다운로드경로: dbeaver.io/ 윈도우/MAC/Linux 운영체제를 지원하고 있으며 저는 윈도우버전으로 설치해보도록 하겠습니다. 프로그램을 다운받아서 설치하면 아래와 같이 프로그램이 실행됩니다. DB2,마리아DB,오라클,PostgreSQ,MSSQL등 다양한 데이터베이스에 대하여 지원하는것을 볼수있습니다. MSSQL을 선..

데이터베이스의 테이블 전체리스트 항목을 조회 해보고 싶은 경우가 있습니다. 그럴경우는 INFORMATION_SCHEMA시스템 테이블을 조회해서 해당목록을 추출할수 있습니다. 1. 테이블 목록추출 INFORMATION_SCHEMA테이블을 조회합니다. 1 2 SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME 쿼리결과 2. 테이블전체리스트 확인 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 SELECT * FROM ( select TABLE_NAME, ORDINAL_POSITION , COLUMN_NAME, DATA_TYPE , CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN..

시퀀스란 무엇일까요? 말 그대로 숫자값의 순서를 부여하는것을 의미하며 지정된 간격으로 오림차순 또는 내림차순으로로 숫자를 부여할수 있습니다. MSSQL의 경우 시퀀스를 2012버전이상부터 지원하고 있으며 IDENTITY필드열과 동일한 기능으로 동작한다고 보시면됩니다. 시퀀스의 경우 어플리케이션에서 시퀀스 개체를 참조하여 처리합니다. 1. 시퀀스 생성방법 1부터 1씩증가하는 정수값을 만드는 시퀀스를 생성해보겠습니다. 1 2 3 4 5 CREATE SEQUENCE [dbo].[DailySEQ] AS [int] START WITH 1 INCREMENT BY 1 GO START WITH 1 : 1번값으로 시작한다는 의미입니다. INCREMENT BY 1 : 1씩증가한다는 의미입니다. 시퀀스가 정상적으로 만들어지..

데이터베이스의 개체들이 생성된 시점을 확인하고 싶은 경우가 있습니다. sys.objects시스템테이블정보를 조회하면 관련정보를 확인할수 있습니다. 1. 개체생성(저장프로시저,뷰,제약조건등) 1 2 3 4 /* 개체 생성시간 (제약 조건, 기본값, 로그, 규칙, 저장 프로시저) */ SELECT * FROM sys.objects order by create_date desc 쿼리결과. create_date , modify_date로 해당개체의 생성일과 변경일을 확인할수 있습니다. type필드정보 타입정보는 아래와 같으며 해당부분을 where 조건으로하면 테이블,뷰,저장프로시저등의 만들어진시간을 확인할수 있습니다. 2. 테이블 생성시간확인 1 2 3 4 5 /* 테이블 생성시간 */ SELECT * FROM..

데이터베이스 lock걸린 쿼리를 찾아서 해당세션을 종료시키는 방법에 대하여 알아보도록 하겠습니다. lock이 걸리게 되면 해당 작업이 끝날때까지 해당테이블 insert/update/delete/select가 되지 않기때문에 성능에 문제가 생기게됩니다. 처리순서요약 처리순서 쿼리문 1. 락걸렸는지 유무확인 sp_lock 2. 락걸린 세션소유자확인 sp_who 3. 쿼리문확인 dbcc inputbuffer(spid) 4. 락걸린세션 삭제 kill spid 강제로 members테이블에 lock이 걸리도록 트랜잭션을 걸은후 commit을 하지 않아 보도록 하겠습니다. 테이블이 lock이 걸렸기때문에 select문으로 조회해서 결과값을 얻어올수 없습니다. 1. sp_lock (락걸렸는지 유무확인) sp_lock문..

쿼리결과의 레코드번호를 임의로 지정하고 싶을 경우가 있습니다. 이때 ROW_NUMBER()함수를 사용하여 일련번호를 임의로 만들어 표시할수 있습니다. 아래와 같이 사용하면되고 정렬하고자하는 필드값을 지정후 정렬옵션을 기입하면됩니다. ROW_NUMBER() OVER (ORDER BY ModifiedDate ASC) 오름차순으로 일련번호 ROW_NUMBER() OVER (ORDER BY ModifiedDate DESC) 내림차순으로 일련번호 ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY ModifiedDate ASC) 특정필드에 대한 그룹을 두어 레코드번호를 부여 조회쿼리 1 2 3 4 5 6 7 8 USE AdventureWorks2014 GO /* Produc..

MSSQL에서 날짜형을 특정문자열 포멧으로 변경하는 방법입니다. 날짜형을 문자열로 변경할때는 Format문을 사용해서 변환이 가능하며 프로그램언어와 같이 사용자가 원하는 포멧으로 변경할수 있습니다. 일반적으로 가장 많이 사용하는 yyyyMMddHHmmss형태로 변환하는 방법입니다. 1 2 3 4 5 6 SELECT FORMAT(cast('2020-12-21 11:22:00:123' as datetime), 'yyyyMMddHHmmssffff') 'yyyyMMddHHmmssffff', FORMAT(cast('2020-12-21 11:22:00' as datetime), 'yyyyMMddHHmmss') 'yyyyMMddHHmmss', FORMAT(cast('2020-12-21 11:22:00' as date..

프로시저,트리거등 쿼리문작업이 Sleep을 주고 싶은경우가 있습이다. 프로그램에서는 sleep을 사용하지만 mssql에서는 waitfor delay명령을 사용하여 특정시간동안 대기시킬수 있습니다. msdn으로 검색해보면 아래와 같은 구문으로 사용하하는법을 알수있습니다. 1. 특정시간 딜레이 : WAITFOR DELAY 밀리세컨드단위와 초단위로 딜레이를 줘보겠습니다. waitfor dealy '0:0:0:100' 으로 실행한것을 볼수 있는데 '시:분:초:밀리세컨드' 형식으로 딜레이를 주는것을 볼수있습니다. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 /* 100m/s대기 */ print convert(varchar(30),getdate(),126) waitfor delay '0:0:0:..

UNION은 언제 사용할까요? 일반적으로 테이블을 연결시키기 위해서는 JOIN문을 많이 사용합니다. 정규화되지 않은 테이블을 서로 합치기 위해서 UNION을 사용합니다. 하나의 테이블을 성능향상을 위하여 두개이상으로 나누어서 설계하는 경우가 있습니다. 회원정보가 1000만명이 있다고 가정을 하고 그중에 거의 최초가입후 사용하지 않는 계정이 800만명이 있다고 가정해보겠습니다. 이럴경우 사용하지 않는 휴먼계정을 별도 테이블로 구분을 하여 저장하고 실제 사용되는 200만명에 대하여 서비스를 하면 서비스의 성능이 올라갈겁니다. 물론 테이블을 파티셔닝하여 구분하는 방법도 있으나 DB버전에 따라 지원이 되지 않는경우도 있고, 이미 서비스가 위와같이 테이블을 분리하여 사용을 하고 있는 경우에는 UNION을 이용하여..