반응형

SQL서버에서는 VALUES를 기존 INSERT 문에서의 행의 값을 나열하는 목적 이외에 다양하게 활용이 가능한데 이 부분을 정리해 보겠습니다.

INSERT .. VALUES

가장 일반적으로 INSERT 할 데이터의 값을 나열하는 데 사용될 수 있습니다.

INSERT INTO Production.UnitMeasure  
VALUES (N'FT', N'Feet', '20080414'); 

SQL서버 2008 이상에서는 VALUES가 다중 행의 값을 지정할 수 있습니다.
INSERT .. VALUES로 한 번에 여러 행(최대 1,000행 까지)을 삽입할 수 있습니다.

INSERT INTO Production.UnitMeasure  
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923')
    , (N'Y3', N'Cubic Yards', '20080923'); 

VALUES 다음에 하나의 행을 하나의 괄호로 묶고 쉼표로 구분해야 합니다.

VALUES의 괄호 안에 올 수 있는 값은 상수, 변수 또는 식입니다(단, 식은 EXECUTE 문을 포함할 수 없습니다).
따라서 다음과 같이 사용자 정의 함수를 사용할 수도 있습니다.

INSERT INTO MyTable
VALUES (1, dbo.MyFunc(10, 20)),
       (2, dbo.MyFunc(30, 40))

식이 가능하기 때문 다른 SELECT문이 포함될 수도 있습니다.

INSERT INTO dbo.MyProducts (Name, ListPrice)  
VALUES ('Helmet', 25.50),  
       ('Wheel', 30.00),  
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),  
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));  

SELECT .. FROM (VALUES)

INSERT뿐 아니라 SELECT의 FROM절에서도 VALUES를 사용할 수 있는데 일종의 가상 파생 테이블과 같다고 볼 수 있겠습니다.

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);

실행결과

앞서 INSERT .. VALUES에서 설명드린 것처럼 하나의 행을 하나의 괄호로 묶고 쉼표로 구분하면 됩니다.

CROSS APPLY (VALUES)

CROSS APPLY 의 VALUES에서 공급받은 행의 값을 이용하여 다양하게 활용할 수 있습니다.

CREATE TABLE #temp
(
    col1 int
,   col2 int
,   col3 int
,   col4 int
,   col5 int
)
INSERT #temp VALUES(1,2,3,4,5), (10,20,30,40,50)

SELECT 
    CASE WHEN a.col1 = b.col THEN b.col END AS col1,
    CASE WHEN a.col2 = b.col THEN b.col END AS col2,
    CASE WHEN a.col3 = b.col THEN b.col END AS col3,
    CASE WHEN a.col4 = b.col THEN b.col END AS col4,
    CASE WHEN a.col5 = b.col THEN b.col END AS col5
FROM #temp a
    CROSS APPLY (VALUES (a.col1),(a.col2),(a.col3),(a.col4),(a.col5)) b (col)

실행결과

CREATE TABLE #temp
(
    col1 int
,   col2 int
,   col3 int
,   col4 int
,   col5 int
)
INSERT #temp VALUES(1,2,3,4,5), (10,20,30,40,50)

SELECT 
    a.*, b.total
FROM 
    #temp a 
    CROSS APPLY 
    (
        SELECT SUM(x) total 
        FROM (VALUES (a.col1), (a.col2), (a.col3), (a.col4), (a.col5)) MyTable (x)
    ) b

실행결과

MERGE .. USING (VALUES)

MERGE문의 USING에 VALUES를 통해서 파생 원본 테이블로 사용할 수 있습니다.

MERGE INTO Sales.SalesReason AS Target  
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))  
       AS Source (NewName, NewReasonType)  
ON Target.Name = Source.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = Source.NewReasonType  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)  
OUTPUT $action INTO @SummaryOfChanges;  

(응용) 열 데이터에 대하여 집계 함수 사용

다음과 같이 열 데이터를 행으로 전환하는데 VALUES를 이용하여 한 행의 여러 칼럼 값들에 대하여 집계 함수를 사용할 수 있습니다.

CREATE TABLE #temp
(
    col1 int
,   col2 int
,   col3 int
,   col4 int
,   col5 int
)
INSERT #temp VALUES(4,1,8,10,57), (17,21,130,12,48)

SELECT 
    col1, col2, col3, col4, col5
,   (   SELECT AVG(a) 
        FROM (VALUES (col1), (col2), (col3), (col4), (col5)) x(a)
    ) average
FROM #temp

실행결과

이 글이 도움이 되셨으면 추천과 댓글을 부탁드려요!

[참조]

INSERT(Transact-SQL)
테이블 값 생성자(Transact-SQL)

 
 
 
 
반응형
반응형

정기적으로 로그 백업을 하고 있다면 로그 파일 크기 내에서 재활용은 하겠지만, 대량의 트랜잭션이 발생하여 로그파일의 크기가 늘어나더라도 로그 백업을 통해 이미 늘어난 파일 크기가 줄어들지는 않습니다. 이로 인해서 불필요하게 디스크 공간을 차지하게 되므로 가끔씩 로그파일 크기를 줄여줄 필요가 있습니다.

현재 로그파일 크기 확인

sp_helpfile

sp_helpfile로 확인해 보면 현재 로그파일 크기가 9,024,448 KB로 나타납니다 (운영 중인 DB라 가렸습니다).

이는 탐색기에서 확인되는 사이즈와 동일합니다.

VLF 구성 확인

DBCC loginfo

DBCC loginfo로 로그파일의 VLF(가상 로그 파일)를 확인해 보면 81개로 구성되어 있음을 알 수 있습니다.

로그파일 크기 줄이기

sp_helpfile 조회 결과의 'name' 컬럼에 해당하는 이름이 로그파일명 입니다.

DBCC SHRINKFILE (로그파일명)

DBCC SHRINKFILE 명령이 정상적으로 수행되면 다음과 같이 결과를 보여 줍니다.

위에서 CurrentSize는 줄어든 후 로그파일의 페이지 수를 나타냅니다. SQL 서버에서 한 페이지는 8KB 이므로 KB로 변경하면 1,025,504 * 8 = 8,204,032 가 됩니다.

sp_helpfile로 다시 확인해보면 다음과 같습니다.

작업 직전에 로그 백업을 받고 진행하지 않아서 많이 줄어들지는 않았지만 그래도 크기가 줄어들었습니다.

DBCC loginfo로 재확인 해보면 VLF 개수가 80개로 줄었습니다.

참고로 쿼리로 로그 테이블의 초기 사이즈를 확인하기 위해서는 다음과 같이 하면 됩니다.

SELECT name, size * 8 AS init_size_KB, physical_name
FROM master.sys.master_files where database_id = DB_ID('데이터베이스명')

DBCC SHRINKFILE를 통해 줄어들 수 있는 최대치는 이 초기 사이즈이기 때문에 이하로는 줄어들지 않습니다.

[참조]

DBCC SHRINKFILE(Transact-SQL)

반응형
반응형

2013년에 포스팅한 내용이 너무 간단한 것 같아서 좀 더 자세한 내용으로 다시 올립니다.

hackhyun.tistory.com/231

 

[SQL서버] PK(Primary Key) 변경 하기

SQL서버에서 기존의 PK를 다른 컬럼 구성으로 변경 하려면 다음과 같이 한다. -- 기존 PK명 확인 sp_help 테이블명 --기존 PK 삭제 ALTER TABLE 테이블명 DROP CONSTRAINT 기존_PK명 --새로운 PK 정의 ALTER TABLE..

hackhyun.tistory.com

SQL서버에서 기존의 PK로 사용된 컬럼을 다른 컬럼으로 변경하려면 다음과 같이 합니다.

1. 기존 PK 명(인덱스 명) 확인

sp_helpindex 테이블명
  • primary key로 지정된 인덱스 명을 확인 합니다.

2. 기존 PK 삭제

ALTER TABLE 테이블명
DROP CONSTRAINT 기존_PK명
  • 1에서 확인된 인덱스 명을 이용합니다.

3. 새로운 PK 정의

ALTER TABLE 테이블명
ADD CONSTRAINT 새_PK명 
PRIMARY KEY [NONCLUSTERED](컬럼1, 컬럼2, ...)
  • 새 PK에 포함될 컬럼은 NULL이 허용되지 않는 컬럼이어야 합니다.
  • 별도로 지정하지 않으면 PK 컬럼이 클러스터 인덱스로 생성됩니다.
  • PK가 아닌 다른 컬럼을 클러스터 인덱스로 사용할 계획이라면 NONCLUSTERED 옵션을 지정합니다.

4. 잘 변경되었는지 확인

 

반응형
반응형

이따금 SQL서버에서
"메시지 3998, 커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다."
라는 오류가 발생하는 경우가 있습니다.

기술적 내용을 알고나면 지극히 당연한 오류 메시지인데, 그 전에는 도대체 무슨 뜻인지 이해하기가 쉽지 않습니다.

SQL서버에서 명시적으로 트랜잭션을 시작하는 경우, 런타임 중에 오류가 발생하면 해당 트랜잭션에 대하여 직접 완료(커밋 또는 롤백)처리를 해야 하는 모드와 SQL서버가 자동으로 롤백 처리해 주는 모드가 있습니다.

다음 명령을 통해서 모드 변경이 가능 합니다.

SET XACT_ABORT { ON | OFF }

T-SQL 디폴트는 OFF 상태 이며, 이 때는 직접 완료처리를 해야 합니다.

USE AdventureWorks2014
GO

SET XACT_ABORT OFF;
GO

BEGIN TRANSACTION; --- ①

SELECT 10 /0       --- ②
SELECT TOP 1 * FROM Sales.Store    --- ③

SELECT @@TRANCOUNT as trnCount

① 에서 명시적 트랜잭션이 시작 되었습니다.
② 에서 0으로 나누기 오류가 발생 됩니다. 그러나 다음 행의 쿼리가 계속 수행 됩니다.
③ SELECT 문이 정상적으로 수행 됩니다.

결과는 다음과 같습니다.

이 시점에서 @@TRANCOUNT는 보시는 바와 같이 1 입니다.
따라서 개발자가 롤백을 하든지 커밋을 하든지 직접 해야 합니다.
물론 위에서는 UPDATE, DELETE 가 없으므로 커밋을 하든 롤백을 하든 결과가 달라질 것은 없습니다.
XACT_ABORT가 OFF 상태일 때는 런타임 오류가 발생하여도 오류가 발생된 이후의 쿼리가 계속 수행되고
트랜잭션 상태가 유지 됩니다.

이제 XACT_ABORT가 ON인 경우를 보겠습니다.

USE AdventureWorks2014
GO

SET XACT_ABORT ON;   --- ①
GO

BEGIN TRANSACTION;

SELECT 10 /0   --- ②
SELECT TOP 1 * FROM Sales.Store

SELECT @@TRANCOUNT as trnCount

① 에서 XACT_ABORT가 ON으로 변경 되었고, 나머지 쿼리는 전과 동일 합니다.

결과는 다음과 같습니다.

② 에서 0 나누기 오류가 발생하자 그 이후의 쿼리는 수행되지 않고 트랜잭션이 종료되고 자동으로 롤백이 됩니다.
따라서 이때 개발자가 직접 롤백처리 하고자 하면 오류가 발생 합니다.

그럼 "커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다." 오류는 언제 발생하는 걸까요?

다음 쿼리의 실행 결과를 보겠습니다.

USE AdventureWorks2014
GO

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
BEGIN TRY   --- ①
    SELECT * FROM person.Person WHERE BusinessEntityID = 1

    INSERT INTO person.personPhone (BusinessEntityID, PhoneNumber, PhoneNumberTypeID)
    VALUES (1, '697-555-0142', 1)    --- ②

    COMMIT
END TRY
BEGIN CATCH  --- ③
    SELECT XACT_STATE() AS XACTSTATE,  ERROR_NUMBER() AS ERR_NUMBER,   --- ④
           ERROR_MESSAGE() AS ERR_MESSAGE 
END CATCH
GO

SELECT @@TRANCOUNT as trnCount

명시적 트랜잭션 실행 후,
① 에서 TRY/CATCH를 처리하고 있습니다.
② 에서 PRIMARY KEY 제약 조건 위반(중복 키 삽입) 오류가 발생 됩니다.
③ TRY 내에서 발생된 오류(예외)로 인해서 CATCH로 제어가 넘어 오게 됩니다.

드디어 "커밋할 수 없는 트랜잭션이 일괄 처리 맨 끝에서 검색되었습니다. 트랜잭션이 롤백됩니다." 오류가 발생 하였습니다. 

XACT_ABORT가 ON 상태에서 명시적으로 트랜잭션이 시작되고, TRY 내에서 예외 발생이 되었으나 CATCH에서 트랜잭션에 대하여 롤백 처리를 하지 않고 일괄처리가 끝나면 위와 같은 오류가 발생하게 됩니다.

이제는 오류 메시지가 무슨 의미인지 이해가 될 것입니다.

참고로 ④에서 XACT_STATE()를 사용하고 있는데 반환값이 -1 이면 활성 사용자 트랜잭션이 있지만 오류가 발생하여 트랜잭션이 커밋할 수 없는 상태라는 의미 입니다.
만약 이 상태에서 CATCH 내에서 ROLLBACK이 아닌 COMMIT을 시도하게 되면 다음과 같은 오류가 발생 합니다.

현재 트랜잭션은 커밋할 수 없으며 로그 파일에 쓰는 작업을 지원하지 않습니다. 트랜잭션을 롤백하십시오

이 글이 도움이 되셨으면 추천과 댓글을 부탁 드려요!

반응형

+ Recent posts