정리하며 배우다.

MSSQL을 이용해서 binary 데이터를 저장해야 하는 경우가 종종 있습니다.

데이터 량이 많은 대신, 크게 가변적이지 않고, 조회이슈가 적은 경우에 한해서 저는 간혹 binary 데이터를 이용해서 저장을 합니다.

varbinary란?

varbinary [(n | max)] 가변 길이 이진 데이터입니다. n 은 1부터 8000 사이의 값이 될 수 있습니다. max 는 최대 스토리지 크기가 2^31-1바이트임을 나타냅니다. 스토리지 크기는 입력된 실제 데이터 길이에 2바이트를 더한 값입니다. 입력된 데이터의 길이가 0바이트일 수 있습니다.

테이블

CREATE TABLE [dbo].[TEST] (
    [Idx]               INT                          NOT NULL,
    [CustomData]        VARBINARY(MAX)               NULL,
    [RegDate]           DATETIME DEFAULT (GETDATE()) NOT NULL,
);

테이블의 형태는 VARBINARY 형태로 저장합니다.

C#에서의 Binary 데이터 이용

// Insert/Update 시
string CustomData = "여러가지 형태의 데이터";
byte[] customBytes = Encoding.UTF8.GetBytes(CustomData);



//Dapper 이용시
var p = new DynamicParameters();
p.Add("@idx", idx, dbType: DbType.Int32);
p.Add("@custom", custom, dbType: DbType.Binary);


// Select 시
string Custom = Encoding.UTF8.GetString(CustomData);

주로 binary 데이터에 저장을 위해서 byte[] 형태로 저장을 하기 때문에,

간단히 C#을 이용해서 데이터가 저장되고 조회되는 과정을 간단하게 정리하였습니다.

MSSQL 2005 이전 버전만 하더라도, 페이징 쿼리문을 꽤 복잡하게 짰던 기억이 있었습니다.

한동안 페이징 쿼리에 대해서 잊고 있다가, 사용할 일이 생겨서 검색하다가 정리해봅니다.

2012qjwjsdptj tkdydgksms 2012버전에서 사용하는 ROW OFFSET 방식이 이전버전들에비해 20% 정도 성능향상이 있다고 합니다.

쿼리문

DECLARE @in_page_number INT
DECLARE @in_page_count INT

SET @in_page_number = 1
SET @in_page_count = 10



SELECT 
	SeqNo
,	RoomName
,	UserID
,	UserName
,	[Message]
,	RegDate
FROM dbo.UserHistory
WHERE	RoomName = @in_room_name
ORDER BY SeqNo DESC
OFFSET (@in_page_number-1) * @in_page_count ROWS
FETCH NEXT @in_page_count ROWS ONLY

OFFSET 과 FETCH NEXT 구문을 이용하면 손쉽게 페이징 쿼리를 작성할 수 있었습니다. 

MSSQL에서 컬럼 타입을 변경하거나, 테이블의 구조를 변경한 뒤에 테이블을 저장하려는 경우, 

아래 이미지 처럼 변경 내용을 저장할 수 없습니다.  알림 메시지가 뜨는 경우가 발생합니다. 

테이블을 다시 만들어야 하는 변경

아래의 원인으로 해당 메시지가 발생 할 수 있습니다.

• 열의 Null 허용 설정을 변경합니다.
• 테이블의 열 순서를 변경합니다.
• 열 데이터 형식을 변경합니다.
• 새 열을 추가 합니다.

테이블의 메타 데이터 구조를 변경 하고, 다음 테이블을 저장하는 테이블을 변경할 때, 위에 메시지가 출력 될 수 있습니다. 

해결방안

도구 > 옵션에 가면

디자이너 영역이 보여집니다. 

해당 메뉴 내에 있는 테이블 및 데이터베이스 디자이너 메뉴에서 

테이블을 다시 만들어야 하는 변경 내용 저장 안 함을 체크 해제 후 저장 하면 됩니다.

급하게 테이블 수정해야 하는 상황이 발생하였는데, 테이블 저장이 안되는 상황이 발생하였습니다. 항상 T-SQL쿼리문으로 작업을 하다가 급하게 SSMS에서 테이블을 수정하려고 하자 발생한 오류였고, 이를 정리해 봅니다.

발생 원인

MSSQL의 SQL Server Management Studio (SSMS) 를 이용해서 아래의 내용으로 테이블을 수정하고 저장하려고 하면 다음과 같은 메시지가 나타날 수 있습니다. 

• 열의 Null 허용 설정을 변경할 때.
• 테이블의 열 순서를 변경할 때.
• 열 데이터 형식을 변경할 때.
• 새 열을 추가할 때.

이러한 사항은 테이블을 다시 만들어야 하는 변경 내용 저장 안함 옵션을 사용할때 발생 합니다. 

이를 해제 하기 위해서는 옵션에서 해당 내용을 변경해 줍니다. 

해결 방법

도구 > 옵션 > 디자이너 메뉴에 들어가면, 아래와 같이 테이블을 다시 만들어야 하는 변경 내용 저장 안함 부분이 선택되어 있는 것을 보실 수 있습니다. 

이를 체크 해제하면, 위 상황에서도 테이블을 저장 할 수 있습니다. 

다른 방법으로는 Transact SQL 쿼리문을 이용해서 바로 수정을 하게 되면, 정상적으로 반영 됩니다. 

위에 옵션메뉴 체크 해제를 진행하게 되면, 무분별한 테이블 수정이 발생하기 때문에

쿼리를 이용한 데이터 수정이 추천 합니다.

  ALTER TABLE [dbo].[USER] ALTER COLUMN create_Time DATETIME NOT NULL

https://support.microsoft.com/ko-kr/help/956176/error-message-when-you-try-to-save-a-table-in-sql-server-saving-change

페이지 단에서 1분가량 소요되는 쿼리를 날리시는 것이 제가 봤을때는 그다지 좋은 방법은 아닐것 같습니다.
혹시 배치 작업이라면 SQL Server 내의 스케쥴을 이용하시는것이 좋을것 같습니다.
그것으로도 해결이 불가능 하다면 쿼리 또는 프로시저의 튜닝으로 위 문제를 해결(시간은 단축)하시는 것이 좋겠습니다. 반드시 웹에서 처리 해야 하는 일이라면 말씀하신 대로 timeout시간을 강제로 조절을 할 수가 있는 데요

page에서 처리

SqlCommand.CommandTimeout = 60

web.config에서 처리

<httpRuntime executionTimeout="60"/>

이러한 형태로 처리 하시면 될 것 같네요

MSDN

http://msdn2.microsoft.com/ko-kr/library/system.data.sqlclient.sqlcommand.commandtimeout(VS.80).aspx

위 URL도 참고해 보세요 ^^ 그럼 도움이 됬으면 좋겠네요