'Programming/Microsoft SQL Server'에 해당되는 글 54건

Programming/Microsoft SQL Server

일반적으로 텍스트 검색시 사용하는 쿼리로 Like검색을 들 수 있습니다.

 

Select *
From [Sales].[OrderLines]
Where [Description] Like '%car%';

 

그런데 위와 같은 방식은 테이블스캔작업이 발생되기에 행(Row)이 많으면 원하는 내용을 찾는데 오랜 시간이 소모될 것입니다. 물론 인덱스를 타게 할 수도 있으나 car%와 같은 방법으로 해야만 해서 내용 중간에 'car'가 있는 데이터는 찾을 수 없고 더군다나 무작위의 문자열을 다루는 열(Column)에는 특별한 경우가 아닌이상 인덱스를 걸어놓지 않습니다.

 

결국 신문기사 검색과 같은 텍스트검색을 위해서는 Like이외에 다른 방법이 필요하게 됐고 보통의 인덱스처럼 열(Column)에 인덱스를 적용하는 것이 아니라 특정 단어나 문장을 대상으로 인덱스를 적용하는 '전체 텍스트 검색'을 사용자에게 제공하게 되었습니다.

 

전체 텍스트 검색기능을 이용하려면 일단 서버에 SQL Full-text Filter Daemon 서비스가 실행중이어야 하는데

 

 

이 서비스는 SQL Server를 설치할때 'Full-Text and Semantic Extractions for Search'항목을 옵션으로 지정해 설치를 해야 합니다.

 

 

이것으로 보아 전체 텍스트 검색을 위한 엔진이 별도로 존재한다는 것을 알 수 있을 것입니다. 만약 사용자가 전체 텍스트 검색을 위한 쿼리를 실행하면 전체 텍스트 엔진은 전체 텍스트 인덱스를 검색해 해당 열을 찾아 그 결과를 SQL Server 서비스에 알려주고 서비스는 그 열의 내용을 가져와 사용자에게 반환하는 방식으로 전체 텍스트검색을 수행하게 됩니다.

 

그럼 실제 검색할 문자열이 존재하는 테이블을 대상으로 전체 텍스트 검색을 사용해 보겠습니다. 아래 예제의 대상은 WideWorldLmporters라는 Sample 데이터베이스를 사용한 것입니다.

 

전체 텍스트 검색사용을 위해서는 우선 전체 텍스트 카탈로그를 생성해야 합니다. 이는 전체 텍스트 인덱스를 저장하고 있는 저장소 역활을 수행합니다.

 

Create Fulltext Catalog SalesOrderLinesCatalog As Default;

 

카타로그는 Create Fulltext Catalog 구문을 사용하며 SalesOrderLinesCatalog라는 이름으로 카타로그를 생성하였습니다. 참고로 sys.fulltext_catalogs 테이블을 살펴보면 생성한 카타로그의 정보를 확인해 볼 수 있습니다.

 

Select *
From sys.fulltext_catalogs;

 

만약 생성한 카탈로그의 is_default값이 1이면 해당 카탈로그가 기본 카탈로그임을 의미하는 것입니다.

 

카탈로그 삭제는 Drop FullText를 사용합니다.

 

Drop Fulltext Catalog SalesOrderLinesCatalog

 

카탈로그를 생성하고 나면 이제 해당 카탈로그에 전체 텍스트검색 인덱스를 생성해야 합니다.

 

Create FullText Index On Sales.OrderLines([Description])
Key Index PK_Sales_OrderLines
On SalesOrderLinesCatalog
With Change_Tracking Auto;

 

위 예제에서 인덱스는 Sales.OrderLines 테이블의 Description 열에 생성하였으며 전체 텍스트 검색엔진이 검색후 결과를 PK_Sales_OrderLines 인덱스키로 SQL Server 서비스에 반환하도록 하였습니다. 즉, 전체 텍스트 검색엔진은 검색 후에 그 내용자체가 아닌 해당 내용이 있는 열을 가리키고 있는 키값을 반환하기 때문에 전체 텍스트 인덱스를 생성하려고 하는 테이블은 반드시 값이 중복될 수 없는 Primary Key나 Unique Key를 가지고 있어야 합니다. 참고로 Description 열은 nVarChar형인데 char, nChar, text, nText, xml등 문자열이 들어가는 거의 모든 열에 지정이 가능합니다. 또한 일반 인덱스와 다르게 전체 텍스트 인덱스는 테이블당 하나만 생성이 가능합니다.

 

혹시 인덱스 생성시 Key Index로 어떤걸 지정해야 할지 모르는 경우라면

 

Select [name]
From sysindexes
Where OBJECT_ID('Sales.OrderLines') = id;

 

처럼 특정 테이블의 개체를 확인하고 PK로 시작하는 이름을 확인하시기 바랍니다.

 

그리고 해당 인덱스는 On구문을 통하여 이전에 만들어둔 전체 텍스트 카탈로그인 SalesOrderLinesCatalog에 생성되도록 합니다. 다만 SalesOrderLinesCatalog는 현재 기본 카탈로그이므로 생략이 가능합니다.

 

그리고 마지막으로 만약 실제 테이블의 값(Sales.OrderLines의 Description열)이 변경되는 경우 이를 인덱스에도 자동으로 반영되도록 Change_Tracing 옵션을 Auto로 지정하였습니다. 성능이 문제가 되는 경우라면 Auto대신 Munual을 지정해 변경데이터를 수동으로 적용시켜 주는 방법도 있으며 Off를 통해 아예 변경내용을 업데이트하지 않도록 처리할 수도 있습니다.

 

인덱스를 생성 후 sys.fulltext_indexes 테이블을 통해 인덱스의 상황을 확인할 수 있습니다.

 

Select *
From sys.fulltext_indexes;

 

만약 결과에서 is_enabled가 1이라면 현재 해당 인덱스는 활성화중이라는 것을 의미합니다.

 

인덱스를 생성하고 나면 어떤 단어들이 인덱스로 생성되었는가를 확인할 수 있습니다.

 

Select *
From sys.dm_fts_index_keywords(DB_ID(), OBJECT_ID('Sales.OrderLines'));

 

위 결과에서 display_term은 생성된 단어들 그리고 column_id는 해당 단어가 들어간 열의 순서값(2라면 2번째열), 마지막으로 document_count는 해당 단어가 몇번이나 들어가 있는지를 나타냅니다.

 

단어를 나열해 보면 가령 필요없는 단어들까지 인덱스로 만들어놓은걸 확인할 수 있습니다. 예들 들어 'ta' 이라는 단어까지 인덱스로 만들필요가 없다면 이들 단어는 중지목록에 포함시켜 인덱스에서 제외할 수 있습니다.

 

특정 단어를 중지목록에 넣으려면 우선 중지목록자체를 생성해야 합니다. SSMS(SQL Server Management Studio)에서 Object Explorer창을 통해 원하는 DB의 Storage->Full Text Stoplists 항목을 찾아가 마우스 오른쪽 버튼을 눌러 'New Full-Text Stoplist'메뉴를 선택합니다.

 

 

Full-text stoplist name에 적절한 목록이름을 입력하고 아래 옵션중 원하는 방식으로 목록옵션을 선택합니다. 옵션중에서 Create an empty stoplist는 말 그대로 빈목록을 만들고 거기에서 시작하겠다는 의미이며 Create from the system stoplist는 이미 시스템상에 만들어져 있는 목록에서 추가하는 형태로 새로운 목록을 만들겠다는 것을 의미합니다. 마지막으로 Create from an existing full-text stoplist는 다른 곳에서 만들어진 목록을 가져와 추가하는 형태로 목록을 만드는 경우 선택하는 옵션인데 이들은 인덱스를 생성하고자 하는 목적에 따라 적절이 선택하시면 됩니다. 예제에서는 이름을 mystoplist하고 Create an empty stoplist를 선택하도록 하겠습니다.

 

 

중지목록을 만들고 나면 위에서 처럼 목록이 생성되는 것을 확인할 수 있습니다. 아이콘 모양을 언뜻보면 뭔가 중지된 느낌이 들지만 중지목록이라는 의미로 모양자체가 원래 그런것이니 신경쓰지 않아도 됩니다.

 

이 작업은 아래 쿼리를 통해서도 동일하게 처리할 수 있습니다.

 

Create FullText Stoplist mystoplist;

 

혹은 시스템 목록에서 만들고자 하는 경우

 

Create FullText Stoplist mystoplist From System StopList;

 

이제 목록에 인덱스에서 예외로 하고자 하는 단어를 추가해 봅시다. 목록에서 마우스 오른쪽 버튼을 눌러 Properties(속성)을 선택합니다.

 

 

위 화면에서 예외로 하고자 하는 단어를 입력하고 OK(확인)버튼을 누르면 해당 단어가 추가됩니다. 이 작업은 아래 쿼리를 통해서도 동일하게 처리할 수 있습니다.

 

Alter FullText StopList mystoplist Add 'ta' Language 'English';

 

이제 기존의 만들어둔 인덱스를 삭제하고

 

Drop FullText Index On Sales.OrderLines;

 

새롭게 인덱스를 구성해 보겠습니다. 쿼리는 이전과 완전히 동일하지만 중지목록에 있는 단어들은 제외하도록 지정해야 합니다.

 

Create FullText Index On Sales.OrderLines([Description])
Key Index PK_Sales_OrderLines
On SalesOrderLinesCatalog
With Change_Tracking Auto, StopList = mystoplist;

 

위에서 처럼 인덱스를 생성하고 나면 중지목록에 있는 단어는 더이상 포함되어 있지 않음을 확인할 수 있습니다.

 

인덱스를 생성하였으면 이제 테이블에서 전체텍스트 검색을 통해 데이터를 찾아보로고 하겠습니다.

 

Select *
From Sales.OrderLines
Where CONTAINS([Description], 'to');

 

Description 컬럼에 to라는 단어가 들어간 모든 데이터를 찾도록 합니다.

 

실행계획에서 FullTextMatch를 확인해 볼 수 있습니다.

 

Select *
From Sales.OrderLines
Where Contains([Description], 'to*');

 

to로 시작하는 단어를 찾습니다.

 

Select *
From Sales.OrderLines
Where CONTAINS([Description], 'to And become');

 

to 와 become 두개가 동시에 들어간 데이터를 찾습니다.

 

Select *
From Sales.OrderLines
Where Contains([Description], 'Near((to, become), 2)');

 

to와 become라는 단어가 서로 근접한것을 찾습니다. 이때 이 두단어 사이에 최대 2개까지의 단어만 포함하도록 합니다.

 

Select *
From Sales.OrderLines
Where Contains([Description], 'IsAbout (to weight (1.0), become weight (0.5))');

 

가중치를 부여한 검색을 수행합니다. 0~1까지 지정이 가능하며 숫자가 높을수록 가중치를 많이 부여합니다.

 

Select *
From Sales.OrderLines
Where CONTAINS([Description], 'to Or box');

 

to 또는 box단어가 존재하는 데이터를 찾습니다.

 

Select *
From Sales.OrderLines
Where FreeText([Description], 'press F1 to');

 

FreeText는 빗스한 내용을 찾아주는 역활을 수행합니다. 예제에서 press F1 to로 지정했는데 이와 완전히 동일하지 않더라도 비슷한 내용이 있으면 그 결과를 반환할 것입니다.

 

Select *
From ContainsTable(Sales.OrderLines, [Description], 'to');

 

ContainsTable은 지정한 열을 포함하는 키값별로 지정한 단어를 가장 많이 그리고 정확하게 포함하고 있는 가중치값을 표현합니다. 비슷한 기능으로 FreeTextTable이 있으며 개념은 FreeText와 같습니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 전체 텍스트 검색  (0) 2018.09.27
트랜잭션 (Transaction)  (0) 2018.09.12
분산 트랜잭션 설정  (0) 2018.07.11
[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23
[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
0 0
Programming/Microsoft SQL Server

MS SQL Server에서 데이터베이스(DB)를 생성하면 실제 데이터베이스파일이 만들어 지는데 일부로 변경한 경우가 아니라면 아래와 같은 위치에 파일이 생성될것입니다.

 

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

 

실제 위와 같은 위치로 들어가면 확장자가 mdf인 파일과 ldf인 파일을 확인해 볼 수 있는데, 예를 들어 abc라는 데이터베이스를 생성했다면 abc.mdf와 abc_log.ldf파일이 존재함을 알 수 있습니다. 여기서 mdf는 실제 데이터가 들어가 있는 파일이며 ldf가 트랜잭션로그파일입니다. 이들 파일은 일반적으로는 한개만 존재할 경우가 많지만 필요에 따라 여러개로 나뉘어질 수 있는데 만약 데이터파일이 나뉘어진거라면 *.ndf형식으로 데이터파일이 생성되어 있을 것입니다.

 

이번에 주목해야할 것은 ldf즉 트랜잭션로그파일입니다. 이 파일은 Update나 Insert, Delete등의 데이터 변경작업을 기록하는 파일입니다. 만약 사용자가 Select와 같은 데이터조회 쿼리를 서버에 전달하면 서버는 mdf에서 원하는 데이터를 찾아 사용자에게 돌려주게 됩니다. 당연히 이 과정에서는 따로 트랜잭션로그가 남지 않습니다.(참고로 로그를 남기는 행위는 어떻게든 성능면에서는 불이익입니다. 트랜잭션로그도 마찬가지인데 로그를 남기지 않도록 하면 데이터의 무결성을 보증하기는 힘들어지지만 성능은 훨씬 좋아질것입니다.)

 

아래 쿼리는 데이터 변경 쿼리로 트랜잭션로그를 남기게 될 것인데 여기서 로그는 변경구문 그 자체를 기록하는것을 의미합니다.

 

Update [Person].[Person]
Set Title = NULL,
    FirstName = 'Ovidiu',
    MiddleName = 'V',
    LastName = 'Cracium'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set Title = 'Ms.',
    FirstName = 'Janice',
    MiddleName = 'M',
    LastName = 'Galvin'
Where BusinessEntityID = 13;

 

그런데 위 구문은 Update가 2개이므로 2번의 트랜잭션을 발생시키게 됩니다. MS SQL은 기본적으로 자동 커밋이기에 사용자가 따로 트랜잭션시작과 종료시점을 명시하지 않는다면 변경 구문 하나하나마다 별도의 트랜잭션을 자동적으로 추가하게 됩니다.

 

Begin Tran

Update [Person].[Person]
Set Title = NULL,
     FirstName = 'Ovidiu',
     MiddleName = 'V',
     LastName = 'Cracium'
Where BusinessEntityID = 11;
Commit Tran;

Begin Tran
Update [Person].[Person]
Set Title = 'Ms.',
     FirstName = 'Janice',
     MiddleName = 'M',
     LastName = 'Galvin'
Where BusinessEntityID = 13;
Commit Tran;

 

그래서 만약 여러개의 변경구문을 묶어 단 한번만 트랜잭션을 유발시키려면 Begin Tran와 Commit Tran사이에 필요한 내용을 모두 명시해야 합니다.

 

Begin Tran

Update [Person].[Person]
Set Title = NULL,
     FirstName = 'Ovidiu',
     MiddleName = 'V',
     LastName = 'Cracium'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set Title = 'Ms.',
     FirstName = 'Janice',
     MiddleName = 'M',
     LastName = 'Galvin'
Where BusinessEntityID = 13;
Commit Tran;

 

여기서 Begin Tran과 Commit Tran에서의 Tran은 Transaction의 줄임표현이며 원한다면 Begin Transaction처럼 풀어써주는것도 가능합니다.

 

트랜잭션은 위에서 처럼 Begin Tran과 Commit Tran을 명시적으로 해줘야 하는 경우도 있고 오라클처럼 Begin Tran은 자동으로 붙여주되 Commit Tran이나 Rollback Tran은 명시적으로 붙여줘야 하는 방식을 취할 수도 있습니다. 이러한 방식을 암시적 트랜잭션이라고 하는데 MS-SQL에서 이 방식을 사용하려면 다음과 같이 설정을 부여해야 합니다.

 

Set Implicit_Transactions On;

 

다만 일반적으로 트랜잭션은 중복으로 실행될 수 있지만 암시적 트랜잭션은 Commit이나 Rollback을 만나기 전까지는 중복실행이 불가능합니다.

 

위에서 처럼 MS-SQL서버에 트랜잭션이 발생하게 되면 그러니까 Begin Tran이 시작되면 ldf에 Begin Tran구문을 기록하고 변경 쿼리의 내용대로 데이터를 임시로 변경하게 됩니다. 임시로 변경한다는 것은 실제 데이터를 변경하는 것이 아니라 변경이 필요한 행 데이터(페이지)를 메모리캐시에 올려놓고 올려놓은 데이터를 대상으로 변경하는 것을 의미합니다. 그리고 나서 변경 쿼리에 일련번호를 부여하여 ldf파일에 로그로 기록하게 됩니다. 이 과정을 Commit이나 Rollback이 나올때까지 변경쿼리마다 하나씩 반복합니다.

 

만약 이 과정중 Commit Tran을 만나게 되면 ldf에 로그를 기록하고 캐시에 기록된 내용대로 실제 mdf데이터에 해당 내용을 적용(Commit)하게 됩니다. 그리고 나서 ldf의 Commit Tran부분에 체크포인트를 설정합니다. 체크포인트는 지금까지의 내용이 모두 실제 mdf에 적용되었음을 표시하는 것인데 만약 CheckPoint 문을 임의로 실행하면 캐시의 내용을 실제 데이터에 적용하고 그 시점에 체크포인트를 설정할 수 있도록 합니다.

 

Begin Tran부터 변경쿼리를 통해 캐시내용을 바꾸는 과정중 어떠한 이유로 인해서 서버나 혹은 서비스가 종료되는 상황이 발생하면 메모리의 내용이 모두 사라지게 될 것입니다. 변경내용이 모두 메모리에 존재하고 있었는데 이 캐시데이터를 모두 잃어버리게 되었고 로그상 아직 Begin Tran중이었으니 처음 Begin Tran이나 혹은 이전 체크포인트까지의 처리를 모두 취소하여 데이터의 무결성을 유지하게 됩니다.

 

그런데 처리과정이 ldf에 Commit Tran을 기록하는 것까지 도달하였으나 캐시데이터가 아직 실제 데이터에 적용되기 전에 사고가 발생한 경우라면 Begin Tran시점(또는 이전 체크포인트 이후)부터 모든 쿼리에 대한 트랜잭션을 다시 재 수행하여 변경절차를 마무리하게 됩니다.

 

Commit Tran은 캐시데이터를 실제 데이터에 적용하지만 Rollback Tran은 변경이전으로 되돌리는 작업을 수행합니다. 그런데 트랜잭션이 여러개 중첩되어 사용되는 경우 Rollback Tran을 사용할때 오해할 수 있는 부분이 하나 있는데 Rollback Tran은 이전의 트랜잭션을 되돌리는 것이 아니라 지금까지의 모든 트랜잭션을 되돌린다는 것입니다.

 

Begin Tran
     Update [Person].[Person]
     Set Title = NULL,
      FirstName = 'Ovidiu1',
      MiddleName = 'V1',
      LastName = 'Cracium1'
     Where BusinessEntityID = 11;

Begin Tran
      Update [Person].[Person]

Set EmailPromotion = 1
Where BusinessEntityID = 11;

RollBack Tran;

 

Begin Tran 이후 다시 Begin Tran으로 트랜잭션을 두번 호출하고 마지막에 RollBack Tran으로 변경 데이터를 되돌립니다. 얼핏봐서는 이전의 Begin Tran만 되돌릴것 같지만 사실은 걸려있는 모든 트랜잭션을 취소합니다. 따라서 만약 특정 트랜잭션만을 명시해 취소하고자 한다면 특정 지점에서 트랜잭션을 Save하고 Save한 트랜잭션까지 취소하는 방법을 써야 합니다.

 

Begin Tran
     Update [Person].[Person]
     Set Title = NULL,
     FirstName = 'Ovidiu1',
     MiddleName = 'V1',
     LastName = 'Cracium1'
     Where BusinessEntityID = 11;
Save Tran my_tran;
Begin Tran
     Update [Person].[Person]
     Set EmailPromotion = 1
     Where BusinessEntityID = 11;
RollBack Tran my_tran;

 

중간에 Save Tran으로 트랜잭션 지점을 생성하고 RollBack Tran에서 복원지점을 명시하면 명시한 지점까지만 트랜잭션을 복원하게 됩니다. 따라서 위 쿼리의 경우 EmailPromotion을 Update하는 부분만 적용되고 그 위로는 트랜잭션이 취소되지 않습니다.

 

참고로 특정 테이블에 트랜잭션이 걸려져 있는 상태라면 해당 테이블에는 잠금(Lock)이 발생할 수 있습니다. 이 현상을 확인하려면 Begin Tran을 발생시키고 다른 세션(별도의 쿼리창)에서 해당 테이블의 Select를 시도해 보면 쉽게 확인 할 수 있습니다. 따라서 임의로 트랜잭션을 구현할때는 주의해야 하며 필요한 경우 아래 쿼리를 실행(Select가 필요한 세션안에서)하여 테이블잠김상태라도 데이터를 가져올 수 있도록 설정할 수 있습니다.

 

Alter Database [대상DB] Set Allow_Snapshot_Isolation On;
Set Transaction Isolation Level Snapshot;

 

특정 세션에서 트랜잭션을 발생시킨뒤 데이터 변경 쿼리를 실행하고 테이블을 조회해 보면 변경된 상태의 데이터가 출력됨을 확인할 수 있는데 이것은 실제 테이블의 데이터를 가져오는 것이 아니라 캐시상태의 데이터를 가져와 보여주고 있기 때문입니다.

 

Snapshot을 취소하려면 다음과 같이 수행합니다.

 

Alter Database AdventureWorks Set Allow_Snapshot_Isolation Off;

 

위에서 알아본대로 트랜잭션은 데이터의 무결성을 유지하기 위한 아주 좋은 장치입니다. 그런데 트랜잭션은 장애발생에 대한 조치용이지 모든걸(모든 장애를) 해결해 주는 것으로 오해해서는 안됩니다. 예를 들어 아래 쿼리를 보면

 

Begin Tran

Update [Person].[Person]
Set Title = NULL,
     FirstName = 'Ovidiu1',
     MiddleName = 'V1',
     LastName = 'Cracium1'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set EmailPromotion = 'ABC'
Where BusinessEntityID = 11;
Commit Tran;

 

두번째 Update문에서 EmailPromotion은 Int형인데 여기에 'ABC'라는 문자열을 처리하도록 하고 있습니다. 트랜잭션에 대해 오해를 하고 있으면 여기에서 실패가 발생해 자동적으로 모든 Update문이 RollBack되리라 생각할 수 있겠지만 사실은 그렇지 않습니다. 이것은 엄밀히 말해 장애가 아닌 오류상황이기 때문입니다. 따라서 이런 경우에는 운영자가 명시적으로 Rollback이 되도록 처리를 해줘야 하며 이 때 가장 많이 쓰이는 방식은 Try Catch 구문을 활용하는 것입니다.

 

Begin Try

Begin Tran
Update [Person].[Person]
Set Title = NULL,
FirstName = 'Ovidiu1',
MiddleName = 'V1',
LastName = 'Cracium1'
Where BusinessEntityID = 11;

Update [Person].[Person]
Set EmailPromotion = 'ABC'
Where BusinessEntityID = 11;
Commit Tran;

End Try
Begin Catch
     Rollback Tran;
End Catch;

 

Begin Tran안에서 오류가 발생하면 Begin Catch에서 잡아 RollBack Tran을 실행할 것입니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 전체 텍스트 검색  (0) 2018.09.27
트랜잭션 (Transaction)  (0) 2018.09.12
분산 트랜잭션 설정  (0) 2018.07.11
[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23
[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
0 0
Programming/Microsoft SQL Server

하나의 MS-SQL 서버에서 다른 MS-SQL서버로 Insert나 Update, Delete작업의 수행시 아래와 같은 메세지를 볼 수 있습니다.

[분산 트랜잭션을 시작할 수 없으므로 요청한 작업을 수행할 수 없습니다.]

이 문제를 해결하려면 다음 절차를 따라합니다. [Server 2008이상]


1. 시작 -> 실행 에서 'dcomcnfg'를 입력합니다.


2. 구성 요소 서비스 -> 컴퓨터 -> 내 컴퓨터 -> Distributed Transaction Coordinator -> 로컬 DTC를 찾아갑니다.



3. 로컬 DTC에서 마우스 오른쪽 버튼을 눌러 '속성'을 클릭한뒤 '보안'탭에서 다음과 같이 설정합니다.



4. 위와 같은 설정을 통신하는 다른 서버에도 동일하게 적용합니다.


5. 방화벽설정에서 DTC관련 항목을 모두 예외처리합니다.


6. 트랜잭션 수행시 SET XACT_ABORT ON; 으로 시작하고 SET XACT_ABORT OFF; 로 끝을 맺습니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 전체 텍스트 검색  (0) 2018.09.27
트랜잭션 (Transaction)  (0) 2018.09.12
분산 트랜잭션 설정  (0) 2018.07.11
[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23
[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
0 0
Programming/Microsoft SQL Server

Select @@LANGUAGE;
sp_defaultlanguage 'SA', 'Korean'; -> SA계정으로 접근시 기본언어 한글

'Programming > Microsoft SQL Server' 카테고리의 다른 글

트랜잭션 (Transaction)  (0) 2018.09.12
분산 트랜잭션 설정  (0) 2018.07.11
[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23
[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
동적 쿼리 실행 예시 (sp_executesql)  (0) 2013.08.22
0 0
Programming/Microsoft SQL Server

스키마(Schema)는 DB안의 테이블(Table)이나 프로시저(Procedure)등 DB에 등록된 개체들을 효휼적으로 관리하기위한 논리적인 그룹단위에 해당합니다. 예를 들어 원래는 DB안에서 테이블을 명시할때는 다음과 같은 형식으로 해야 합니다.


[DB명].[스키마명].[개체명]


DB명의 경우 개체를 다루고 있는 DB를 명시하는 것인데 이것은 현재 선택된 DB가 같은 경우 생략이 가능합니다. 스키마명은 대부분 dbo 라는 이름을 많이 볼 수 있는데 이것은 특별한 스키마를 지정하지 않으면 기본적으로 dbo라는 기본스키마가 지정되기 때문입니다.


스키마는 아래와 같은 방법으로 생성합니다.


Create Schema MySchema;


스키마를 생성하고 나면 해당 스키마에 소속된 개체를 생성할 수 있게 됩니다.


Create Table MySchema.MyTable (
    Idx Int,
    Content Char(20)
);


스키마를 삭제하려면 Create대신 Drop구문을 사용하면 됩니다.


Drop Schema MySchema;


다만 이미 등록된 스키마에 소속된 개체가 존재하면 해당 스키마는 삭제할 수 없으며 등록된 스키마는 SSMS의 DB -> Security(보안) -> Schemas(스키마)를 통해 확인할 수 있습니다.

 

Create Schema mySchema;

▶ 스키마 생성

 

Drop Schema mySchema;

▶ 스키마 삭제

 

Alter Schema mySchema Transfer 현재스키마.테이블명;

▶ 기존 테이블의 스키마 변경

'Programming > Microsoft SQL Server' 카테고리의 다른 글

분산 트랜잭션 설정  (0) 2018.07.11
[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23
[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
동적 쿼리 실행 예시 (sp_executesql)  (0) 2013.08.22
[SQL] CTE  (0) 2011.03.07
0 0
Programming/Microsoft SQL Server

백업된 DB를 다시 복원시도하면 기본적으로 본래 백업대상이된 DB의 이름으로 복원을 시도하게 됩니다. 본래 DB가 잘못되어 삭제된 상태면 상관없지만 간혹 다른 이름으로 DB를 복원하여 테스트등의 용도로 사용하고자 할때가 있습니다.

 

우선 백업된 DB의 논리적이름을 확인합니다.

 

Restore Filelistonly From Disk='C:\BackUp\<백업DB파일명>';

 

그런다음 아래 쿼리로 복원을 시도합니다.

 

Restore Database <복원하려는 DB>
From Disk='C:\BackUp\<백업된DB파일명>'
With
Move '<확인된 논리적DB이름>' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\<복원할DB파일명>.mdf',
Move '<확인된 논리적DB Log이름>' To 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\<복원할DB파일명>.ldf', Replace;

 

* DB경로는 필요에 따라 임의로 수정해야 합니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23
[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
동적 쿼리 실행 예시 (sp_executesql)  (0) 2013.08.22
[SQL] CTE  (0) 2011.03.07
[SQL] 뷰(VIEW)  (0) 2011.03.04
0 0
Programming/Microsoft SQL Server
Declare @sql nVarChar(3000);
Declare @pam nVarChar(300);
Declare @p Char(10);

Set @sql = 'Select @p = Convert(Char(10), GetDate(), 120)';
Set @pam = '@p As Char(10) OutPut';

Exec sp_executesql @sql, @pam, @p OutPut;
Select @p;

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 스키마(Schema)  (0) 2018.01.23
[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
동적 쿼리 실행 예시 (sp_executesql)  (0) 2013.08.22
[SQL] CTE  (0) 2011.03.07
[SQL] 뷰(VIEW)  (0) 2011.03.04
[SQL] 커서(Cursor)  (2) 2011.03.03
0 0
Programming/Microsoft SQL Server
Common Table Expressions의 약자인 CTE는 어떠한 값을 산출하는 계산식을 하나의 Table처럼 사용할 수 있도록 해주며 크게 재귀CTE와 비재귀CTE 두가지로 나눌 수 있습니다.

1. 비재귀 CTE

View나 사용자정의함수 혹은 하위Query를 통한 Table집계등 어떤 결과를 산출하는 식을 대체할 수 있는 것으로서 다음과 같은 방법으로 생성할 수 있습니다.

With [CTE이름](Column) As
(
 처리내용
)

생성된 CTE는 다음과 같이 일반 Select문을 사용하여 쉽게 호출할 수 있습니다.

Select * From CTE이름

With SalesOrderPrice(ID, Price) As
(
 Select SalesOrderID, (UnitPrice * OrderQty)
 From Sales.SalesOrderDetail
)
Select ID, Price
From SalesOrderPrice;

CTE를 활용한 간단한 예를 보여주고 있습니다.

UnitPrice * OrderQty를 계산(사실은 LineTotal열값과 같음)한 SalesOrderPrice CTE를 생성하고 조회 합니다. 이때 CTE를 생성하는 부분과 이를 호출하는 Select Query는 하나의 단위가 되어야 하므로 같이 실행하고 있습니다.

이러한 이유로 CTE 정의시에 구현된 Query문의 끝에 ;를 붙여 Query가 끝났음을 알리게 되면 오류가 발생하게 됩니다.


With으로 CTE를 정의할때는 하나가 아닌 여러개의 CTE를 정의를 한꺼번에 할 수도 있습니다.

With SalesOrderPrice(ID, Price) As(
 Select SalesOrderID, (UnitPrice * OrderQty)
 From Sales.SalesOrderDetail
),
SalesOrderNumber(ID, Price, OrderNumber) As(
 Select ID, Price, B.SalesOrderNumber
 From SalesOrderPrice As A, Sales.SalesOrderHeader As B
 Where A.ID = B.SalesOrderID
),
SalesAccountNumber(ID, Price, OrderNumber, AccountNumber) As(
 Select ID, Price, B.SalesOrderNumber, B.AccountNumber
 From SalesOrderPrice As A, Sales.SalesOrderHeader As B
 Where A.ID = B.SalesOrderID
)

Select ID, Price, OrderNumber
From SalesOrderNumber;


여러 CTE를 정의할경우 각 CTE는 ,문자로 구분해야 합니다.
또한 CTE를 정의할때는 이전에 정의한 CTE를 참조하거나 전혀 다른 Table을 가져와 새로운 CTE로 정의할 수도 있습니다.


CTE를 정의하면 단순한 조회뿐만이 아니라 CTE를 통해 새로운 Data를 저장하거나 수정하는 것이 가능합니다.

With InsertDepartment(ID, Name, [Group]) As(
 Select DepartmentID, Name, GroupName
 From HumanResources.Department
)

Insert Into InsertDepartment
Values('Admin', 'AdminGroup');


Insert 구문으로 InsertDepartment이름의 CTE를 통해 Data를 추가합니다.

Select DepartmentID, Name, GroupName
From HumanResources.Department;


With UpdateDepartment(ID, Name, [Group]) As(
 Select DepartmentID, Name, GroupName
 From HumanResources.Department
 Where DepartmentID = 17
)

Update UpdateDepartment
Set Name = 'Name',
 [Group] = 'Group';


UpdateDepartment로 정의한 CTE를 통해 DepartmentID가 17(위에서 추가한)인 Name과 GroupName값을 수정합니다.

Select DepartmentID, Name, GroupName
From HumanResources.Department;

변경 사항을 확인합니다.


2. 재귀CTE

재귀호출이라함은 자기자신을 다시 호출하는 경우를 말합니다. 이러한 재귀호출이 가능한 CTE가 바로 재귀CTE입니다.

재귀CTE는 비재귀CTE와는 달리 두가지의 Query문으로 CTE가 정의됩니다.

With [CTE이름](Column) As(
 처리내용
 Union All
 처리내용
)

첫번째 처리내용에 포함되는 Query문을 AM(Anchor Member)이라 하고 두번째 처리문의 Query문을 RM(Recursive Member)이라고 합니다. AM과 RM은 필요하다면 두가지 이상이 들어갈 수 있으며 Union이나 Union All을 통해 연결되지만 주의할 점은 반드시 AM이 먼저 나와야 한다는 것입니다.

재귀CTE가 동작하는 방식은 처음 재귀CTE가 호출되면 AM이 먼저 실행(한번만 실행!)되고 그 값을 가지고 RM이 재귀호출되면서 결과를 만들어 내는 식으로 처리가 진행됩니다. 이때 AM은 재귀CTE를 포함하지 않는 독립된 형태의 Query로 구성되며 RM에서 재귀CTE가 포함되는 구조를 가지고 재귀호출이 발생하게 되는 것입니다.

With HumanLevel(ID, [Level]) As(
 Select BusinessEntityID, OrganizationLevel
 From HumanResources.Employee
 Where BusinessEntityID = 2
 Union All
 Select BusinessEntityID, OrganizationLevel
 From HumanResources.Employee As A, HumanLevel As B
 Where A.OrganizationLevel = B.ID
)
Select ID, [Level]
From HumanLevel

재귀 CTE를 호출하면 첫번째 Query요소인 AM에 해당하는

Select BusinessEntityID, OrganizationLevel
From HumanResources.Employee
Where BusinessEntityID = 2

문이 실행되어 BusinessEntityID가 2인 BusinessEntityID와 OrganizationLevel값을 반환하게 됩니다.


그럼 RM에서는 AM이 반환한 값을 가지고 BusinessEntityID(재귀 CTE를 참조한 값이므로 여기서는 ID가 됩니다.)와 OrganizationLevel값이 같은 항목을 조회하여 최초로 다음 형태의 값을 주게 되는 것입니다.


앞서 말씀드렸듯이 AM은 한번만 실행되지만 RM은 반환하는 값이 전혀없을때까지 재귀호출(자기자신을 호출)되면서 동작합니다. 이는 결과적으로 OrganizationLevel값이 3인것을 다시 조회하고 그 조회한 값에서 다시 OrganizationLevel과 같은 값을 조회하면서 계속 반복하고 최종적으로 다음과 같은 형태의 결과값을 출력하게 됩니다.




RM에서 재귀호출로 인한 동작이 발생하면 더이상 반환값이 없을때까지 반복한다고 설명을 드렸는데 만일 재귀호출될 때마다 값을 반환하게 되면 어떻게 될까요?

재귀호출을 중단하는 조건은 더이상 값을 반환하지 않을 때까지 이므로 무조건 값을 반환하는 형태라면 무한 Loop로 빠지게 되는 문제가 생기게 될 것입니다. 이 문제를 해결하기 위해서 Maxrecursion라는 옵션이 마련되어 있습니다.

Maxrecursion을 사용하려면 재귀CTE를 호출하는 Select문에서 다음과 같이 추가합니다.

Select ID, [Level]
From HumanLevel
Option(Maxrecursion 10)

Maxrecursion 뒤에 숫자를 지정하면 해당 수만큼만 재귀호출이 발생합니다.(Maxrecursion를 지정하지 않을시 기본값은 100이며 0으로 지정하게 되면 무한대가 됩니다.)

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 다른 이름으로 DB복원  (0) 2017.09.05
동적 쿼리 실행 예시 (sp_executesql)  (0) 2013.08.22
[SQL] CTE  (0) 2011.03.07
[SQL] 뷰(VIEW)  (0) 2011.03.04
[SQL] 커서(Cursor)  (2) 2011.03.03
[SQL] 트리거(Trigger)  (2) 2011.03.02
cte
0 0
Programming/Microsoft SQL Server

VIEW는 실제 존재하지 않는 가상적 Table로서 다른 Table에서 Data를 가져와 이를 한곳에 묶어서 보여주는 역활을 합니다. 이러한 특징을 통해 개발자는 사용자가 보길 원하는 Data만 보여줄 수 있을 뿐 아니라 VIEW가 참조하고 있는 실제 Table에 대한 접근제한(VIEW를 통해서도 Data의 추가, 삭제, 변경등이 가능합니다.)을 통해 보안성을 기대할 수 있습니다.

1. View의 생성

Create view <이름>
As
<table 조회 구문>


<이름>에는 View의 이름을 지정하며 <table 조회 구문>에서는 실제 Table을 조회하는 Select문을 작성합니다.


Create view viewDepartment
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;


HumanResources.EmployeeDepartmentHistory와 HumanResources.Department Table의 Join을 통해 Department의 ID와 Name, GroupName를 같이 조회하는 View를 생성합니다.


Select * From viewDepartment;


생성된 View를 조회합니다.


만일 생성된 VIEW를 변경하고자 한다면 Alter를 사용하면 됩니다.


Alter view viewDepartment
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName, StartDate
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;


HumanResources.EmployeeDepartmentHistory Table에 있는 StartDate열을 View에 추가합니다.


Select * From viewDepartment;


변경된 View를 조회합니다.


View의 삭제는 Drop문을 통해 구현합니다.

Drop view viewDepartment;

앞서 보신바와 같이 View는 실제존재하는 Table를 참조하여 원하는 Data를 보여주고 있습니다. 그런데 이 상태에서 View가 참조하고 있는 Table 혹은 Table의 Column 이름(또는 형식)이 변경되는 상황이 발생하면 분명 문제가 발생할 것입니다.

이러한 문제를 방지하려면 Schemabinding Option을 사용합니다.

Create view viewDepartment With Schemabinding
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;

Schemabinding Option을 통해 View를 생성하면 해당View에서 쓰이는 Table(혹은 Column과 형식)이름은 변경이 불가능하게 됩니다.

Exec Sp_rename 'HumanResources.EmployeeDepartmentHistory','EmployeeDepartment'

Table이름 변경을 시도하고 있습니다.

Exec Sp_rename 'HumanResources.Department.[Name]', 'Title', 'Column'

Table의 Column이름을 변경합니다.

Alter Table HumanResources.Department Alter Column Name nVarchar(60);

Table의 Column형식을 변경합니다.

참고:
sp_rename은 명칭변환에 관한 Procedure입니다.


Schemabinding 옵션으로 Error가 발생한다는 것을 알 수 있습니다.

2. View를 통한 data변경

생성된 View만을 가지고도 Data를 Update하거나 Insert, Delete할 수 있습니다. 실제 이렇게 하기위한 특별한 방법이 있는 것은 아니므로 일반 Table과 똑같이 쓰시면 됩니다.

Update viewDepartment
Set GroupName = 'Engineering and Development'
Where BusinessEntityID = 2;

BusinessEntityID가 2인것의 GroupName을 변경하고 있습니다.

Select * From viewDepartment;

View를 확인해 보시면


뭔가 이상한듯 합니다. Update할때는 BusinessEntityID가 2인것만 변경하라고 했는데 다른 부분이 같이 변경되어 있기 때문이죠.

이런 현상이 발생한 이유는 View에서의 Data변경이 실제 Table의 Data에도 반영되기 때문입니다. 즉, GroupName열의 실제 Table에 해당하는 HumanResources.Department Table에서 변경작업이 반영되었기 때문에 이를 참조하는 View에서도 GroupName을 가져오는 부분이 전부 바뀌게 된 것입니다.

이처럼 View에서의 Data변경(Insert와 Delete 포함)은 실제 Table에도 영향을 미치게 되므로 주의해야 합니다. 또한 View에서 Data를 변경하는 작업을 할때는 View가 참조하고 있는 Table하나만을 대상으로 해야 합니다.

예를 들어 위에서 구현한 Update문에서는 HumanResources.Department Table의 GroupName열만을 변경하려고 했기 때문에 가능했지만 다음 예제와 같이 GroupName과 HumanResources.EmployeeDepartmentHistory Table에 해당하는 BusinessEntityID열의 변경을 같이 추가하는 변경작업은 불가능합니다.


Update viewDepartment
Set GroupName = 'Engineering and Development',
     BusinessEntityID = 300
Where BusinessEntityID = 2;


HumanResources.Department 와 HumanResources.EmployeeDepartmentHistory 두개의 Table을 대상으로 View를 통한 Update를 시도하였습니다.

주의:
위 예제는 이해를 돕기위한 예제일 뿐입니다. View의 제약사항이 아니라 하더라도 HumanResources.EmployeeDepartmentHistory Table의 특성상 실제로는 작동할 수 없는 Query입니다.


View를 통해 Data를 변경할 수 없는 경우는 또 있습니다. 그것은 View를 생성할때 다른 Table의 열을 그대로 가져와 생성한 것이 아닌 Sum이나 Avg혹은 기타 다른 방법을 통해서 계산된 형태의 열을 가져오는 경우오도록 구현된것 또한 변경이 불가능합니다.


뿐만 아니라 View에 대한 데이터삽입의 제한도 가능한데 예를 들어 다음과 같은 뷰가 존재한다고 가정해 보겠습니다.


Create View MyView
As
Select ProductID, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal, ModifiedDate
From [Sales].[SalesOrderDetail]
Where UnitPrice >= 2039.994;


이 뷰는 SalesOrderDetail의 UnitPrice가 2039.994 이상의 데이터만 조회하도록 하는 뷰입니다. 그런데 다음과 같이 UnitPrice가 2039.994 이하의 데이터삽입이 일어나는건


Insert Into MyView
Values(100, 1, 100.000, 0.00, 2000.000, GETDATE());


논리적으로 맞지가 않습니다. 때문에 뷰에서는 뷰가 수행하는 조건과 맞지 않는 데이터 삽입요청이 오면 Check Option구문으로 차단할 수 있습니다.


Create View MyView
As
Select ProductID, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal, ModifiedDate
From [Sales].[SalesOrderDetail]
Where UnitPrice >= 2039.994
With Check Option;


3. View의 Index

일반적인 View는 실제 Data를 가지지 않고 다른 Table로 부터 Data를 가져와 보여주는 역활을 합니다. 이런 View에 Index를 부여하게 되면 이 View는 다른 Table로 부터가 아닌 View에서 직접적으로 Data를 조회하게 됩니다.

그래서 Index를 가지는 View는 보통 일반 View보다 성능적으로 개선될 수 있지만 만일 이 View가 참조하고 있는 Table에서 Data변경이 발생하면 View에도 Data변경이 같이 발생하기 때문에 변경작업에는 오히려 좋지않은 영향을 끼칠 수있습니다.

따라서 이와 같은 특징을 잘 고려하여 View에 Index를 생성할지 여부를 결정해야 합니다.

View에 Index를 생성하는 방법은 다음과 같습니다.


Create Unique Clustered Index inx_viewDepartment On viewDepartment(BusinessEntityID, DepartMentID);


위에서 생성한 viewDepartment View에 BusinessEntityID와 DepartMentID열을 가지고 Index를 생성합니다.(현재 내용상 BusinessEntityID에 중복값이 있기 때문에 EusinessEntityID열만 가지고 Index를 만들 수 없는 없습니다.)

View에 Index를 생성하고 나서 해당 View를 조회 하려면 다음과 같이 할 수도 있겠으나


Select * From viewDepartment;


이것은 Index가 생성된 View에는 권장할 만한 방법이 아닙니다. Index를 제대로 타지 않기 때문이므로 되도록 다음과 같이 조회하는 것을 권장합니다.


Select *
From viewDepartment With (Noexpand);


Noexpand Option을 추가하여 View에 Index를 제대로 적용하도록 지정합니다.


4. View에 대한 상세한 정보확인

View가 생성되면 생성될때의 Create문은 sys.sql_modules에 저장됩니다.

Select * From sys.sql_modules;


sys.sql_modules는 object id별로 Data를 저장하는데 이것만 봐서는 내가 원하는 View가 어떤 id로 저장되어 있는지 알 수가 없습니다. 따라서 OBJECT_ID를 이용하여 생성한 View에 대한 object id를 얻고 이를 통해 얻어진 id값을 비교해서 조회 하여야 합니다.

Select * From sys.sql_modules
Where object_id = OBJECT_ID('viewDepartment');


이렇게 하면 View를 생성할때의 생성문을 확인할 수 있습니다.

이때 만일 View생성에 대한 Crate문을 볼 수 없도록 보안조치를 취하려면 View생성시 Encryption을 사용하여 암호화 하도록 합니다.

Create view viewDepartment With Encryption
As
Select BusinessEntityID, A.DepartMentID, Name, GroupName
From HumanResources.EmployeeDepartmentHistory As A, HumanResources.Department As B
Where A.DepartmentID = B.DepartmentID;
Select * From sys.sql_modules
Where object_id = OBJECT_ID('viewDepartment');

암호화가 처리가 정상적으로 적용되었는지 확인하기 위해 sys.sql_modules를 조회합니다.


생성문이 보여야할 definition 열이 null로 표시됨을 알 수 있습니다.

주의:
View 생성문이 한번 암호화 되면 되돌릴 수 있는 방법은 존재하지 않습니다. 따라서 암호화 하기전 다시한번 심사숙고 해야하며 View생성문 자체를 백업해 둬야 합니다.

VIEW자체에 대한 정보는 sys.view에서 확인할 수 있으며

Select * From sys.views
Where object_id = OBJECT_ID('viewDepartment');


생성한 View가 어떤 Column을 반환하는지에 대한 정보는 sys.columns에서 알 수 있습니다.

Select * From sys.columns
Where object_id = OBJECT_ID('viewDepartment');


또한 뷰가 참조하는 테이블이나 열(Column)을 확인하려면 sp_depends 프로시저를 사용합니다.

Exec sp_depends MyView;

5. 시스템 뷰


MS SQL Server는 자체적인 시스템관리를 위해 몇가지 테이블을 가지고 있습니다. 하지만 관리자를 포함한 사용자는 이 시스템테이블에 접근할 수 없으며 관련 방법자체도 제공하지 않습니다.


대신 시스템 정보 확인을 위해 시스템 테이블과 연계된 몇가지 뷰를 제공하고 있는데 이런걸 '시스템뷰'라고 합니다.


시스템뷰는 예를 들어 DB정보를 간략히 보여주는 sys.databases 와 같은 뷰가 있습니다.


Select *
From sys.databases;

'Programming > Microsoft SQL Server' 카테고리의 다른 글

동적 쿼리 실행 예시 (sp_executesql)  (0) 2013.08.22
[SQL] CTE  (0) 2011.03.07
[SQL] 뷰(VIEW)  (0) 2011.03.04
[SQL] 커서(Cursor)  (2) 2011.03.03
[SQL] 트리거(Trigger)  (2) 2011.03.02
[SQL] 사용자 정의 함수  (1) 2011.02.28
0 0
Programming/Microsoft SQL Server

Cursor는 집합단위로 처리하는 Query를 행단위로 처리하도록 합니다. 그런데 저는 개인적으로 Cursor는 잘 쓰지 않는 편입니다. Cursor를 쓸 경우에는 Cursor로 인해 얻는 이득보다는 아무래도 실이 더 많은듯 합니다.

Cursor를 쓰는 경우의 대부분을 일반 Query로 대체할 수 있을 뿐만 아니라 무엇보다도 속도문제가 가장 걸리기 때문입니다. 따라서 되도록이면 Cursor를 쓰지 말라고 권하고 싶습니다.(되도록이면 이지 무조건이 아닙니다..;;;)

Cursor가 사용되기 위해서는 먼저 생성, 열기, 사용, 닫기, 제거등의 절차를 거쳐야 합니다.

1. Cursor 생성

Declare (커서이름) Cursor
For (Query)


Declare를 통해 먼저 Cursor를 선언하고 For다음에 가져올 행과 열을 지정합니다.

Declare ex_cursor Cursor
For Select DepartmentID, Name, GroupName
 From HumanResources.Department
 Where DepartmentID = 10;


ex_cursor이라는 이름의 Cursor를 선언하였습니다. 이 Cursor는 HumanResources.Department Table의 DepartmentID가 10이라는 행의 DepartmentID, Name, GroupName Column을 가져옵니다.

이렇게 Cursor선언하고 사용하려면 Cursor에 대한 Open이 필요합니다.

2. Cursor Open

Open ex_cursor;


Open한 Cursor는 Fatch구문을 통해 선언할때의 Data를 가져오게 됩니다. 커서는 전역(Global)커서와 지역(Local)커서로 구분될 수 있는데 만약 전역와 지역커서모두 동일한 이름의 커서가 존재한다면 Local커서가 우선적으로 열리게 됩니다. 그러므로 전역커서를 명시적으로 Open하려면 다음과 같이 Global을 지정해 주도록 합니다.

 

 

Open Global ex_cursor;

 

Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)
Fetch
From ex_cursor
Into @ID, @Name, @GroupName;

 

 

 

Data를 가져오기 위해 우선 @ID, @Name, @GroupName변수를 선언하고 Into절을 통해 해당 변수에 Cursor에서 가져온 Data값을 넣습니다.

 

Select @ID, @Name, @GroupName;

변수에 담은 Data를 조회합니다.


3. Cursor Close

 

Open된 Cursor를 사용하고 나면 다시 Close하고 커서의 할당을 해제하도록 합니다.

 

Close ex_cursor;

Deallocate ex_cursor;


참고:
Deallocate 써서 Cursor제거를 시도하면 그 즉시 Cursor가 삭제되지 않는 경우도 있습니다. 이렇게 되는 이유는 Cursor완전히 제거되기 위해선 해당 Cursor를 사용하고 있는 다른 부분도 완전히 종료되어야 하기 때문입니다.

4. @@Fetch_Status

Cursor에서 가져온 Data가 존재하는지 여부를 확인하기 위해 쓰입니다. 0이면 Data가 존재하고 -1이면 존재하지 않음을 의미합니다.

Declare ex_cursor Cursor
For Select DepartmentID, Name, GroupName
 From HumanResources.Department
 
Open ex_cursor;
Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)

Fetch
From ex_cursor
Into @ID, @Name, @GroupName;

Select @ID, @Name + ' - ' + @GroupName;

While @@Fetch_Status = 0
Begin
 Fetch
 From ex_cursor
 Into @ID, @Name, @GroupName;
 
 Select @ID, @Name + ' - ' + @GroupName;
End

Close ex_cursor;

Deallocate ex_cursor;


먼저 Fetch를 실행한뒤 Data를 가져오는데 성공하면(@@Fetch_Status = 0) While문을 통해 행단위 Data를 순차적으로 가져옵니다.


5. Insensitive

이 Option은 Cursor를 정의할때 Data들을 tempdb에 저장되도록 하여 원본과의 동일성을 차단합니다. 즉, 원본 Table의 내용이 바뀌어도 Cursor의 내용에는 변함이 없도록 하기 위한 것입니다.

Declare ex_cursor Insensitive Cursor
For Select DepartmentID, Name, GroupName
 From HumanResources.Department
 Where DepartmentID = 16
 
Open ex_cursor;
Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)

Fetch
From ex_cursor
Into @ID, @Name, @GroupName;

Update HumanResources.Department
Set GroupName = 'Executive General'
Where DepartmentID = 16;

Select @ID, @Name, @GroupName;

Select DepartmentID, Name, GroupName
From HumanResources.Department
Where DepartmentID = 16;

Close ex_cursor;

Deallocate ex_cursor;


Insensitive를 적용하여 Cursor를 생성하였습니다. Fetch이후에 Update를 통하여 HumanResources.Department Table의 GroupName을 'Executive General and Administration' 에서 'Executive General'로 변경하고 Fetch를 통해 가져온 Data와 실제 Table의 내용을 같이 조회하였습니다.

 

비슷한 역활을 하는 것으로 Static이 있습니다. 기능은 Insensitive와 동일합니다.

 

Declare ex_cursor Cursor Static
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
Where DepartmentID = 10;

 

Static외에 Dynamic과 Keyset 옵션도 존재합니다.

 

Declare ex_cursor Cursor Keyset
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
Where DepartmentID = 10;

 

Keyset은 테이블에서 키에 해당하는 열만 tempdb에 복사하도록 합니다. Cursor가 이동할때 키에 해당하는 데이터는 실제 테이블에서 가져오게 되므로 데이터가 삭제나 변경을 그대로 반영할 수 있습니다. 다만 새로 삽입되는 행의 경우에는 tempdb에 해당 키값이 존재하지 않을 것이므로 Insert는 반영되지 않습니다.

 

Dynamic은 Cursor 포인터의 키만 tempdb에 복사하도록 합니다. Cursor의 포인터가 이동할때마다 그때 그때의 키가 복사되는 형태이므로 Keyset과 다르게 신규추가되는 행까지도 모두 반영이 가능합니다.

 

주의할 점은 Keyset이나 Dynamic은 모두 테이블에서 복사할 키열을 필요로 하므로 테이블에 키가 없다면 옵션을 지정하더라도 암시적으로 Static이 될 수 있다는 것입니다. 만약 Keyset이나 Dynamic을 지정했을때 정상적으로 처리할 수 없는 상황(테이블에 고유 인덱스가 없는 상황)이라면 type_warning 옵션을 지정해 오류를 발생시켜 명확히 오류상황임을 파악할 수 있습니다.

 

Declare ex_cursor Cursor Dynamic Type_warning

 

6. Cursor의 이동

 

Cursor는 기본적으로 처음부터 끝까지 이동되는 동작방식을 가지며 필요에 따라 이전과 다음으로의 자유로운 이동을 가능하게 할 수도 있습니다.

 

Declare ex_cursor Cursor Static Scroll
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
--Where DepartmentID = 10;

 

Cursor를 선언할때 Scroll 옵션을 지정했습니다. Scroll은 Cursor의 자유로운 이동을 가능하게 하는 것으로 별도로 지정하지 않으면 Scroll은 기본값입니다.

 

Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)
Fetch Next From ex_cursor
Into @ID, @Name, @GroupName;
Select @ID, @Name, @GroupName;

 

Fetch Next 를 통해 Cursor를 다음으로 이동하도록 하였습니다. 따라서 위 쿼리를 실행할때마다 HumanResources.Department 테이블에서 하나씩 행의 값을 순서대로 가져올 것입니다.

 

Declare @ID Int
Declare @Name nVarChar(50)
Declare @GroupName nVarChar(50)
Fetch Prior From ex_cursor
Into @ID, @Name, @GroupName;
Select @ID, @Name, @GroupName;

 

이번에는 Fetch Prior를 통해 Cursor를 이전행으로 이동하도록 하였습니다.

 

Fetch First From ex_cursor

 

Cursor의 포인터를 처음으로 이동시킵니다.

 

Fetch Last From ex_cursor

 

Cursor의 포인터를 맨 마지막으로 이동시킵니다.

 

만약 Cursor가 뒤로 되돌아갈일이 없다면 Forward_only 옵션을 지정해 Cursor의 성능을 향상시킬 수 있습니다.

 

Declare ex_cursor Cursor Static Forward_only
For
Select DepartmentID, [Name], GroupName
From HumanResources.Department
--Where DepartmentID = 10;


7. Global

Cursor를 Open할때 Global Option을 사용하면 해당 Cursor를 다른곳에서도 사용핡 수 있게 됩니다.

예를 들어 어떤 한 Procedure에서 ex_cursor이라는 Cursor를 다음과 같이 Open한 경우

 

Open Global ex_cursor;


ex_cursor Cursor는 현재 Cursor를 열고 있는 Procedure뿐만이 아니라 전혀 다른 Procedure안에서도 ex_cursor Cursor를 다룰 수 있게 되는 것입니다.(예를 Procedure로 들었지만 SPID가 다른 사용자간의 공유도 가능해 집니다.)

 

Global이외에 Local로 지역커서를 설정할 수 있는데 이 경우에는 해당 Cursor를 열고 있는 세션안에서만 사용이 가능합니다. 이를 테면 Procedure안에서 사용하는 경우 Procedure의 수행이 종료되면 커서도 같이 소멸합니다. 다만 Procedure안에서 Output등의 방법으로 커서를 반환하는 형태라면 커서를 반환받은 변수등이 소멸해야만 비로소 Cursor도 같이 소멸됩니다.

 

아무것도 지정하지 않으면 기본값을 따라가며 기본값을 변경하지 않았다면 Global이 기본값이 됩니다.

 

기본값은 SSMS에서 해당 DB의 속성(Properties)에 들어가 Opetion -> Default Cursor 항목을 보면 확인할 수 있는데 이미 커서가 생성된 상태라면 sp_describe_cursor 프로시저를 통해 cursor의 상태를 확인함으로서 해당 Cursor가 Global인지 Local인지를 알 수 있습니다.

 

Declare @my_cursor Cursor;
Exec sp_describe_cursor @my_cursor Output, 'Global', 'ex_cursor';
Fetch Next From @my_cursor

 

인수중 ex_cursor는 확인하고자 하는 커서의 이름이므로 상황에 따라 바꿔야 하며 해당 Global인지 Local인지를 같이 지정하면 됩니다.

 

결과에서 cursor_scope가 2라면 Global, 1이면 지역커서를 의미하며 참고로 model이 1이면 Cursor가 Static, 2면 Keyset, 3이면 Dynamic임을 의미합니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] CTE  (0) 2011.03.07
[SQL] 뷰(VIEW)  (0) 2011.03.04
[SQL] 커서(Cursor)  (2) 2011.03.03
[SQL] 트리거(Trigger)  (2) 2011.03.02
[SQL] 사용자 정의 함수  (1) 2011.02.28
[SQL Server] 예약 작업(작업 Schuduling)  (0) 2011.02.23
2 0
Programming/Microsoft SQL Server
Trigger는 Trigger가 정의된 Table(또는 View)에서 어떠한 작업(Insert, Update, Delete 등)이 이루어 지면 자동으로 실행되는 일련의 작업단위입니다. 또한 Trigger는 자동으로 실행된다는 개념의 Procedure이므로 수동적으로 호출하여 실행하는 것은 불가능합니다.

1. Trigger 생성

Trigger는 다음과 같은 방법으로 생성할 수 있습니다.

Create trigger 이름
On table 또는 view명
Event
As
Query

<이름>부분에는 정의할 Trigger이름을 지정하고 Trigger가 작동하도록 정의할 Table이나 View를 On 구문 다음에 입력합니다. 그리고 <Event>부분에 Trigger를 실행시킬 작업을 정의하면 됩니다.

실제 Trigger가 어떤 작업을 할지는 As 다음의 <Query>부분에 입력하면 되는데 실제 구현부분은 다음 예제를 참고해 주십시오.

Create trigger In_Department
On HumanResources.Department
After Insert
As
Select getdate();

In_Department라는 이름의 Trigger는 HumanResources.Department Table에 Insert작업이 실행된 후 현재 시간을 Select하도록 정의하였습니다.

Trigger의 작동을 확인하기 위해 HumanResources.Department Table에 Data를 추가하여 보겠습니다.

Insert Into HumanResources.Department
Values('Administrator', 'Administrator Group', getdate());

Trigger가 정의된 Table에 Data를 추가합니다.


Insert 작업이 완료되자 마자 Trigger정의로 인해 현재 시간이 출력되고 있습니다.

Trigger작동이후 Insert 작업이 올바르게 실행되었는지 확인해 보도록 하겠습니다.

Select * From HumanResources.Department;


마지막에 'Administrator'이름으로 Data가 추가되었습니다.

Trigger정의시 After Insert는 해당 Table에 Insert작업이 먼저 실행된 후 정의된 작업을 수행하도록 하는 것입니다.(After는 View에는 할 수 없고 Table에서만 가능합니다.) 이때 Trigger를 발생시키는 작업의 정의는 콤마(,)구분을 통해서 하나이상 정의하는 것 또한 가능합니다.

Alter trigger [HumanResources].[In_Department]
On HumanResources.Department
After Insert, Delete
As
Select *
From HumanResources.Department;


Insert 혹은 Delete작업이 발생하면 Trigger가 발생하도록 하고 있으며 실제 Trigger의 작동내용도 HumanResources.Department Table의 내용이 보여지도록 변경하였습니다.(Trigger의 변경은 Alter구문을 이용합니다.)

Trigger의 작동을 확인해 보기 위해 이전에 추가하였던 Data를 삭제해 보겠습니다.

Delete From HumanResources.Department
Where DepartmentID = 17;

 


Administrator이라는 이름으로 등록된 Data를 삭제 후 HumanResources.Department Table의 내용이 나타나고 있습니다.

참고로 만약 트리거에 Update와 Delete가 동시에 정의되어 있는 경우 COLUMNS_UPDATE() 함수를 사용해 Update에 의한 트리거 발생인지, Delete에 의한 트리거 발생인지 구분이 가능합니다.

 

If (COLUMNS_UPDATED() > 0)
Begin
     --업데이트시
End
Else
Begin
     --삭제시
End;


Trigger생성시 After대신에 Instead of를 사용하여 해당 Table에 정의된 작업이 시도되면 그 작업은 무시되고 Trigger에 정의된 작업이 실행되도록 할 수 있습니다. 즉, After는 Table작업 시도 후 Trigger가 실행되지만 Instead of는 작업 시도전 Trigger를 실행시키게 되는 것입니다.(Instead of는 Table뿐만 아니라 View에도 정의할 수 있습니다.)

주의:
Instead of로 인해 Trigger가 작동된 후에는 이전에 Table에 시도한 작업은 실행되지 않습니다.

Alter trigger [HumanResources].[In_Department]
On HumanResources.Department
Instead of Delete
As
Insert Into HumanResources.Department
Values('Administrator', 'Administrator Group', getdate());

Select *
From HumanResources.Department;


HumanResources.Department Table에 Delete작업이 발생하면 해당 Delete작업은 무시되고 Trigger정의된 Insert작업이 실행된 후 Table의 내용이 보여지도록 하였습니다.

그럼 HumanResources.Department Table에 삭제 작업을 시도하여 Trigger작동을 확인해 보도록 하겠습니다.

Delete From HumanResources.Department
Where DepartmentID = 16;


삭제하려고 시도 했던 작업은 무시되어 Data가 그대로 남아 있으며 Trigger에 의해 새로운 Data가 추가되었습니다.

 

2. Rollback

 

트리거에서 사용자의 시도한 데이터작업은 트랜잭션을 Rollback 함으로서 취소가 가능합니다. 예를 들어 In_Department 테이블에서 Insert작업이 발생하는 경우 Insert 시도를 취소하고자 한다면 다음과 같이 트리거를 생성하면 됩니다.

 

Create trigger [HumanResources].[In_Department]
On HumanResources.Department
After Insert
As
     Rollback Tran;

Rollback Tran이 가능한 이유는 트리거가 완전히 종료되어야만 Insert 시도한 트랜잭션이 완료되기 때문입니다.


3. Trigger의 추가생성과 실행주기

Trigger는 하나의 Table나 View에 2개이상을 정의할 수 있습니다.

Create trigger [HumanResources].[Up_Department]
On HumanResources.Department
After Update
As
Raiserror('Update 되었음', 16, 1);
Create trigger [HumanResources].[In_Department]
On HumanResources.Department
After Insert
As
Raiserror('Insert 되었음', 16, 1);

Create trigger [HumanResources].[In2_Department]
On HumanResources.Department
After Insert
As
Raiserror('Insert 되었음2', 16, 1);

Create trigger [HumanResources].[Del_Department]
On HumanResources.Department
After Insert
As
Raiserror('Delete 되었음', 16, 1);


HumanResources.Department Table에 Update, Insert, Delete관련 Trigger와 Insert에 대한 Trigger를 하나 더 생성하였습니다.(위 Trigger문은 한꺼번에 실행되지 않으므로 하나씩 생성해야 합니다.)

이렇게 Trigger를 생성해둔 상태에서 Insert작업을 시도한다면 관련 Trigger는 정의된 순서대로 모두 실행될 것입니다.

Insert Into HumanResources.Department
Values('Administrator', 'Administrator Group', getdate());


이때 만일 두번째로 정의한 Insert Trigger를 먼저 실행시키고자 한다면 Sp_settriggerorder로 그 순서를 지정해 주면 됩니다.

sp_settriggerorder '[HumanResources].[In2_Department]', 'First', 'INSERT'

Insert작업에 대해 In2_Department Trigger를 먼저 실행시키고

sp_settriggerorder '[HumanResources].[In_Department]', 'Last', 'INSERT'

In_Department Trigger를 나중에 실행시킵니다.

위와 같이 설정 후 다시 Insert작업을 시도하면

Insert Into HumanResources.Department
Values('Administrator', 'Administrator Group', getdate());


실행 순서가 바뀌어 있음을 확인할 수 있습니다.

 

다만 순서는 First, Last만 지정할 수 있고 그 외 나머지 트리거는 순서를 지정할 수 없으며 트리거를 수정하게 되면 기존에 순서는 파기되므로 다시 순서를 정해야 합니다.

4. trigger 내부 제어

Trigger가 작동할때 Table이나 View자체가 아닌 특정 Column을 확인하여 Trigger를 작동시킬 수도 있습니다.

Alter trigger [HumanResources].[In_Department]
On HumanResources.Department
After Update
As
If Update(GroupName)
Begin
 Raiserror('GroupName 열에 Update가 발생하였습니다.', 16, 1);
End


HumanResources.Department Table에 Update작업이 발생했을 때 Column을 확인하여 GroupName열이 변경되었으면 Message를 보여주도록 합니다.

실제 HumanResources.Department Table에 Update를 시도하여 Trigger의 작동을 확인해 보면

Update HumanResources.Department
Set GroupName = 'Executive General'
Where DepartmentID = 16;



Trigger에서 정의해둔 Message가 나타남을 확인 할 수 있습니다.

Select *
From HumanResources.Department;

HumanResources.Department Table에 Update가 제대로 이루어 졌는지 확인합니다.


만일 HumanResources.Department Table의 다른 Column에 Update를 시도한다면

Update HumanResources.Department
Set Name = 'Exec'
Where DepartmentID = 16;



If와 Update(GroupName)로 Trigger가 작동할 수 있는 조건이 지정되어 있으므로 Trigger는 실행되지 않을 것입니다.

또한 Update()를 통해 특정 열의 Update여부를 확인하는 대신 Columns_Update() 함수를 이용할 수도 있습니다. 이 함수는bColumn에 대한 변경 여부를 판단한다는 의미에서 Update()함수와 동일하지만 대신 Binary값을 통해 확인한다는 차이가 있습니다.

 

Columns_Update()는 Update작업이 아루어 지는 Table의 Column을 Bit값을 통해 나타내는데 쉽게 얘기하면 Table의 열을 첨부터 끝까지 1, 2, 4, 6, 8....(2의 승수) 순으로 보고 Update되는 해당 Column만 값을 더하여 산출합니다. 이 결과를 보게 되면 변경되는 Column을 확인할 수 있게 되는 것입니다.

예를 들어 HumanResources.Department Table에는 DepartmentID, Name, GroupName, ModifiedDate 4개의 Column이 존재하는데 이들 각각의 열을 1, 2, 4, 8 값으로 보겠습니다. 그러면 Name과 GroupName만 Update되었을때 Columns_Update() 함수는 2+4 인 6의 값을 반환할 것입니다.

5. Trigger의 중첩실행방지

만일 어떤 하나의 Table에 Trigger가 설정되어 있을때 그 Table에 특정 작업을 시도하면 정해진 Trigger가 실행될것입니다. 그런데 이 Trigger가 작동하면서 다른 Table의 Data를 변경할때 그 Table에도 Trigger가 설정되어 있다면 해당 Table의 Trigger도 작동할 것입니다.

이런식으로 어느 하나의 Table에서 부터 시작하여 점차적으로 다른 Table에 영향을 미치게 되어 Trigger가 연속적으로 실행되는 경우를 Trigger의 중첩실행이라고 합니다. 만일 이 중첩실행을 방지하고자 한다면 sp_configure를 통해 Server설정을 변경해야 합니다.

sp_configure 'nested trigger', 0


Trigger가 중첩 실행될 수 있는 횟수는 32회 까지로 이 횟수를 넘어서면 Trigger는 종료됩니다. 그럼 이전까지 실행된 Trigger는 어떻게 될까? 라는 의문을 가질 수 있지만 Trigger는 내부에는 Transaction을 구현하지 않아도 자동적으로 Transaction을 동반하므로 모두 Rollback처리 됩니다.

6. 재귀 Trigger

Trigger가 실행되어 다른 Table에 Data변경 작업이 발생할때 그 Table의 Trigger가 또 작동되는 경우는 위에서 말씀드린 중첩실행에 해당합니다. 그런데 Trigger가 실행될때 Trigger안에서 자신을 호출한 Table의 Data를 변경한다면 또 다시 자기자신에 해당하는 Trigger가 실행될 수 있습니다.

자기자신을 호출한다는 의미로 이런 경우를 재귀호출이라 할 수 있는데 자신을 호출한 Table이 아닌 다른 Table의 Data변경으로 인해 그 Table의 Trigger가 호출되면서 다시 원래 Table의 Data변경 작업을 시도하여 본래 실행되었던 Trigger를 다시 호출하는 경우도 재귀 Trigger라 할 수 있습니다.

이런 재귀 Trigger는 다음과 같은 방법으로 방지할 수 있습니다.

Sp_dboption database, 'recursive trigger', true

 

7. inserted 와 deleted 테이블

 

Insert나 Delete, Update가 걸린 트리거의 경우 상황에 따라 Inserted 또는 Deleted 라는 2개의 임시테이블을 사용합니다.  Insterted는 Insert나 Update의 경우 사용되는 것으로서 해당 작업이 처리된 이 후의 데이터를 가지고 있으며 Deleted는 Delete와 Update에 사용되고 해당 작업이 처리되기 전의 데이터를 가지고 있게 됩니다. 또한 이 테이블은 임의로 조작이 불가능하며 단지 Select만 가능한 테이블입니다.

 

따라서 inserted 와 deleted 두개의 테이블을 사용하면 데이터가 처리되기 전과 처리 이후의 상황에 대한 데이터를 다음과 같은 형태로 참조할 수 있습니다.

 

Create trigger [HumanResources].[In_Department]
On HumanResources.Department
After Delete
As
    Insert Into backup_table
    Select *
    From deleted;


8. DDL Tirgger

Trigger가 작동하는 대상을 Table이나 View가 아닌 SQL Server자체나 특정 Database전체를 대상으로 할 수도 있습니다. 즉, Server나 Database에 Trigger를 생성해 놓으면 해당 작업이 발생할때 Trigger가 실행되도록 할 수 있는 것입니다.

DDL Trigger를 생성하기 위한 구문은 다음과 같습니다.

Create trigger 이름
On All server 혹은 Database
Event
As
Query


On다음에는 해당 Trigger가 Server를 대상으로 하는지 Database를 대상으로 하는지를 지정해 줘야하며 <Event>에는 On에서 설정한 Server혹은 Database에 Trigger가 실행될 특정 작업(Event)을 지정해 줍니다.

Trigger가 Table이나 View를 대상하는 하는 경우에는 Insert, Update, Delete등의 구문이 들어갔지만 Server나 Database가 Trigger의 대상이 되는 경우 SQL Server에서 별도로 제공해 주는 Event를 따로 써야합니다.

다음은 Server와 Database에서 DDL Trigger를 생성한 예제입니다.

Create trigger User_add
On All Server
After Create_login
As
Raiserror('사용자를 생성할 수 없습니다.', 16, 1)
Rollback Tran;


All Server로 인해 해당 Trigger가 Server 내에서 작동하도록 하였으며 Create_Login Event를 지정하여 Server의 사용자가 추가될때 Trigger가 작동하도록 합니다.

위와 같이 설정 후 실제 Server에 사용자를 추가하려고 하면 아래와 같은 오류가 발생할 것입니다.


위 Trigger생성시 Raiserror로 인해 오류 Message를 반환하도록 한 후 실제 사용자 추가가 취소되도록 Rollback Tran하였습니다. DDL Trigger는 오로지 After Option만 사용할 수 있기 때문에 작업이 일어나기 전에는 Trigger가 작동하지 않게 되기 때문입니다. 따라서 일단 사용자가 추가되면 오류를 일으킴과 동시에 ollback되도록 하여 사용자 추가가 불가능하도록 만들어 둔 것입니다.


이번에는 Database를 대상으로 하여 DDL Trigger를 생성해 보도록 하겠습니다.

Create trigger Add_table
On Database
After DDL_Table_Events
As
Raiserror('해당 Database 내에서는 Table을 추가/삭제/변경할 수 없습니다.', 16, 1)
Rollback Tran;

On 다음에 Database를 지정해 해당 Database내에서 Table을 추가하거나 삭제/변경할때 Trigger가 발생하도록 하였습니다.

Create Table tmp_table(
 Col1 Int,
 Col2 Int
)


Trigger를 생성 후 Table만들기를 시도합니다.


위에서 Trigger를 설정할때 지정한 DDL_Table_Events는 하나의 작업에 대한 Event가 아닌 여러 Event를 묶어 Group화된 Event를 지정한 것입니다. 이때 Group화된 DDL_Table_Events는 다음과 같은 3개의 Event를 포함하고 있습니다.

(1) Create_Table

Table을 만들때 발생하는 Event입니다.

(2) Alter_Table

Table을 변경할때 발생하는 Event입니다.

(3) Drop_Table

Table을 삭제할때 발생하는 Event입니다.

이상입니다. 따라서 DDL_Table_Events 를 지정하면 Database에서 위 세가지 Event가 발생할때 Trigger가 작동하게 되는 것입니다.

이처럼 DDL Trigger생성시에는 특정 Event 뿐만 아니라 여러 Event를 묶은 그룹화된 Event도 설정이 가능다는 것을 알아두기시 바랍니다.(각각의 Event와 Group된 Event가 어떤 종류가 있는지에 대해서는 SQL 도움말을 참고하십시오.)

 

9. Trigger의 정보 확인

 

설정된 Trigger의 정보를 확인하려면 sp_helptrigger 프로시저를 호출합니다.

 

Exec sp_helptrigger 'In_Department';


10. Trigger의 암호화

Trigger는 생성시에 암호화 하여 다른 사람이 내용을 볼 수 없도록 할 수 있습니다.

Create trigger [HumanResources].[In_Department]
On HumanResources.Department
With Encryption
Instead of Delete
As
Insert Into HumanResources.Department
Values('Administrator', 'Administrator Group', getdate());
Select * From HumanResources.Department;

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 뷰(VIEW)  (0) 2011.03.04
[SQL] 커서(Cursor)  (2) 2011.03.03
[SQL] 트리거(Trigger)  (2) 2011.03.02
[SQL] 사용자 정의 함수  (1) 2011.02.28
[SQL Server] 예약 작업(작업 Schuduling)  (0) 2011.02.23
[SQL] 자주쓰는 트리거 이벤트(Trigger Event)  (0) 2011.02.22
2 0
Programming/Microsoft SQL Server
합계나 평균을 내기 위해서는 Sum()이나 Avg()같은 함수를 쓰게 됩니다. Data형 변환에도 Cast()등의 함수를 사용합니다.(SQL Server에서 기본적으로 제공하는 유형의 함수를 내장함수라고 합니다.)

이처럼 여러가지 함수를 조합하여 사용자가 원하는 결과를 만들어 내는데 SQL Server에서는 이런 정해진 함수가 아닌 사용자가 원하는 특정한 함수를 직접 만들어서 사용할 수도 있습니다. 이러한 유형의 함수를 '사용자 정의 함수'라고 합니다.

1. 사용자 정의 함수 생성

Create Function <이름>(
 @<변수명>
)
Returns <데이터형>
As
Begin
 <query>
 Return @<결과값>
End

값을 반환하는 함수를 정의합니다.

함수생성 구문에서 @<변수명>은 사용자 함수가 실행되는 본체내부에서 필요한 값을 전달해 주는 매개변수입니다. 이 매개변수를 받아서 <Query>내에 구현된 실행문에 따라 처리가 진행되고 그 결과를 Return을 통해 얻게 됩니다.(물론 매개변수가 필요하지 않으면 @<변수명>부분은 생략할 수 있습니다.)

Data Returns의 <Data Type>에는 반환되는 값의 Data형을 지정합니다.

다음은 두개의 정수값을 받아서 이 값을 더하고 결과를 표시하는 함수의 작성 예제입니다.

Create Function data_Sum(
 @Value1 Int,
 @Value2 Int
)
Returns Int
As
Begin
 Declare @Sum_Value Int;
 Set @Sum_Value = (@Value1 + @Value2);
 
 Return @Sum_Value;
End

Select dbo.data_Sum(10, 20);

함수를 생성한 후 해당 함수에 값을 넘겨 결과를 확인합니다.


사용자 정의함수 생성시 매개변수를 전달하고자 할때 기본값을 정하고자 한다면 @Value1 Int = 10의 형태를 통해서 원하는 값을 설정할 수 있습니다. 이렇게 하면 해당 매개변수에 값을 지정하지 않았을 경우 기본값으로 정의된 10이라는 Ddata를 사용하게 되는 것입니다.(기본값이 지정된 매개변수에 값을 넘겨주지 않고 기본값 그대로 사용하기 위해서는 Default구문을 사용합니다.)

Create Function data_Sum(
 @Value1 Int = 10,
 @Value2 Int = 20
)
Returns Int
As
Begin
 Declare @Sum_Value Int;
 Set @Sum_Value = (@Value1 + @Value2);
 
 Return @Sum_Value;
End

@Value1과 @Value2에 10과 20이라는 기본값을 지정하였습니다.

Select dbo.data_Sum(Default, 50);

기본값 사용의 경우 Default라는 Keyword를 사용합니다.


위에서 보시는 것처럼 값을 반환화는 함수의 경우 Return문을 통해 특정값을 지정하여 반환하도록 하고 있습니다. 그러나 반환하는 Data가 값이 아닌 Table형태의 경우 약간 다른 방식으로 Return을 지정해야 합니다.

Create Function Return_table(
 @Value1 Int = 10
)
Returns Table
As
Return(
 Select DepartmentID, Name, GroupName
 From HumanResources.Department
 Where DepartmentID = @Value1
)

Table형태의 Data를 반환하는 함수로서 넘겨지는 매개변수의 @Value1의 값에 따라 HumanResources.Department Table을 조회하도록 하였습니다. 값 형태와 달리 Table반환 형식에는 Begin/End가 존재하지 않으며 결과는 Return()을 통해 Select 조회 전체를 지정하고 있는데 이때 Returns를 통해 반환되는 데이터형에도 Table만 올 수 있습니다.

Select *
From dbo.Return_table(8);

Table반환형식의 함수를 호출합니다. 만일 Table반환형식의 함수 호출이라면 함수가 아닌 실제 Table처럼 From절까지 써서 호출해야 합니다.


지금까지 값을 반환하는 형태와 Table을 반환하는 형태의 두가지 함수정의에 대해 살펴보았습니다.

값을 반환하는 형태의 경우 Begin/End안의 Query문을 통해 특정값을 얻을 수 있도록 다소 자유로운 구현이 가능했습니다만 오로지 값의 형태만 반환할 수 있다는 단점이 있었습니다. Table의 경우에도 값이 아닌 Table형태의 반환값으로 값형식 보다는 좀더 넓은 범위의 반환형태를 가지고 있지만 Begin/End가 없어 Table조회 이외의 구문구현이 어렵다는 단점이 있었습니다.

만일 어떤 함수에서 위 두가지 특징을 모두 가져야 하는 경우에는 이 두개를 섞은 결합적인 함수를 구현할 수도 있습니다.

Create Function Return_table(
 @Value1 Int = 10,
 @Value2 Int = 20
)
Returns @temp_table Table(
 ID   Int,
 Name  nVarChar(50),
 GroupName nVarChar(50)
)
As
Begin
 Declare @Return_Value Int;
 
 Set @Return_Value = (@Value1 + @Value2);
 
 Insert Into @temp_table
 Select DepartmentID, Name, GroupName
 From HumanResources.Department
 Where DepartmentID = @Return_Value;
 
 Return;
End

위 함수는 Table형식의 값을 반환하면서 값 형식처럼 특정 Query구현이 가능한 형태를 가지는 사용자 함수정의입니다. Table반환값의 경우에는 Returns다음에 반환할 임시 Table형태를 지정하고 이후 Beign/End를 써서 Returns에정의된 임시Table에 값을 넣는 구문을 완성하면 됩니다. 이렇게 되면 Return문을 만나 끝나는 시점에서 Returns에 지정된 임시 Table의 내용을 반환하게 될 것입니다.

Select *
From dbo.Return_table(8, 2);

위에서 구현한 사용자 정의 함수를 호출합니다.


정의된 함수를 수정할때는 Create대신 Alter문만 써주면 됩니다. 그외에 것은 생성할때와 별 차이가 없으므로 큰 어려움이 없을 것입니다.(물론 함수이름은 맞아야 하며 값형태의 반환값을 갖는 함수에서 Table형태의 값을 반환하도록 수정하는 것은 불가능하고 그 반대의 경우도 마찬가지 입니다.)

Alter Function Return_table(
 @Value1 Int = 10,
 @Value2 Int = 20,
 @Value3 Int
)
Returns @temp_table Table(
 ID   Int,
 Login_ID nVarChar(256),
 JobTitle nVarChar(50)
)
As
Begin
 Declare @Return_Value Int;
 
 Set @Return_Value = (@Value1 + @Value2);
 
 Set @Return_Value = (@Return_Value - @Value3);
 
 Insert Into @temp_table
 Select BusinessEntityID, LoginID, JobTitle
 From HumanResources.Employee
 Where BusinessEntityID = @Return_Value;
 
 Return;
End

Return_table함수를 수정하여 인수를 3개 가지도록 하였으며 대상 Table도 HumanResources.Employee Table로 바꾸었습니다.

Select ID, Login_ID, JobTitle
From dbo.Return_table(8, 2, 1)

변경된 사용자 정의 함수를 호출합니다.


사용자 정의 함수의 삭제하려면 Drop을 사용합니다.

Drop Function Return_table;

2. Schemabinding

위에서 HumanResources.Employee Table을 조회하는 사용자 함수를 생성했을때 만일 HumanResources.Employee Table의 이름이 바뀌거나 혹은 함수내에서 구현한 Column의 이름이 바뀌면 어떻게 될까요? 당연히 함수가 작동할때 오류를 일으키게 될 것입니다.
이런 상황이 생기는 것을 방지하기 위해 함수를 정의할때는 함수내에서 사용하는 특정 Table이나 View와 같은 대상의 이름이 바뀌지 않도록 Schemabinding Option을 지정할 수 있습니다.

Create Function Return_table(
 @Value1 Int = 10,
 @Value2 Int = 20,
 @Value3 Int
)
Returns @temp_table Table(
 ID   Int,
 Login_ID nVarChar(256),
 JobTitle nVarChar(50)
)
With Schemabinding
As
Begin
 Declare @Return_Value Int;
 Set @Return_Value = (@Value1 + @Value2);
 Set @Return_Value = (@Return_Value - @Value3);
 
 Insert Into @temp_table
 Select BusinessEntityID, LoginID, JobTitle
 From HumanResources.Employee
 Where BusinessEntityID = @Return_Value;
 
 Return;
End

With Schemabinding option을 설정한 후

sp_rename 'HumanResources.Employee', 'Employees'

Employee Table의 이름이 Employees로 바뀌도록 변경을 시도합니다.


3. 생성한 사용자 함수가 어디에서 쓰이고 있을까?

사용자 정의 함수를 삭제하는 것은 위에서 본것처럼 생성이나 변경할때보다 아주 간단합니다. 하지만 잘못 삭제했다가는 해당 함수를 사용하고 있는 다른 부분에서 생각지 못한 오류를 만날 수도 있습니다. 따라서 현재 삭제하고자 하는 함수가 혹시 다른곳에서도 사용되고 있는지 확인해 볼 필요가 있는데 이 경우 Sp_depends를 사용하면 됩니다.

sp_depends ufnGetContactInformation;

Adventureworks2008 예제 Database의 ufnGetContactInformation 이라는 사용자 정의 함수가 어디에서 쓰이고 있는지 확인합니다.


4. Apply

Table형태의 값을 반환하는 함수에 한하여 매개변수에 값을 전달할때 일반적으로 사용자가 직접 값을 작성하지만 Apply를 이용하면 다른 Table을 통해 값을 순차적으로 줄 수도 있습니다.

Create Table tmp_table(
 Col1 Int,
 Col2 Int20
)
Create Function (
 @Value1 Int = 10
)
Returns Table
As
Return(
 Select , Name, GroupName
 From HumanResources.Department
 Where DepartmentID = @Value1
)

Apply를 Test하기 위한 함수를 생성합니다.

Select *
From HumanResources.EmployeeDepartmentHistory As A Outer Apply Return_table(A.DepartmentID);

HumanResources.EmployeeDepartmentHistory Table의 내용을 가져오되 위에서 정의한 사용자 함수를 호출하여 매개변수에 HumanResources.EmployeeDepartmentHistory의 DepartmentID열값을 전달하며 이렇게 조회된 함수의 내용과 HumanResources.EmployeeDepartmentHistory Table이 Outer Join되어 결과를 표시하게 됩니다.(Outer는 Join방식입니다. 필요하다면 다른 Join을 지정해 사용할 수도 있습니다.)

1 0
Programming/Microsoft SQL Server
SQL Server에서는 특정에 원하는 작업을 한번 혹은 반복적으로 수행할 수 있도록 Schuduling기능을 제공합니다.

예를 들면 매일 오전 06:00시에 Database Backup을 지정해 놓으면 사용자가 일일이 간섭하지 않아도 시간이 되면 Backup이 이루어지게 하는 것입니다.

원하는 작업을 예약하시려면 다음과 같은 방법을 통해 설정합니다.

SQL Server Management에서 개체탐색기창에 SQL Server Agent항목을 선택합니다.


만일 SQLSERVERAGENT Service가 실행되고 있지 않으면 위 그림과 같이 '사용 안 함'으로 표시되는데 실제 작업을 예약하기 위해서는 SQLSERVERAGENT Service가 실행되고 있어야 합니다.

이런경우 SQL Server Agent에서 Mouse오른쪽 Button을 눌러 '시작'을 눌러주십시오. 곧 SQLSERVERAGENT Service가 실행될 것입니다.


SQL Server Agent가 정상적으로 실행되면 'SQL Server 에이전트 -> 작업' 에서 Mouse 오른쪽 Click -> '새 작업' 항목을 선택합니다.


새 작업창의 '일반'항목에서 작업의 이름과 설명을 간단히 입력하고 '단계'를 선택합니다.


단계 항목은 실제 수행할 작업을 여러 단계로 나누어 실행할 수 있도록 설정하는 곳입니다. 여기서 '새로 만들기' Button을 눌러 '새 작업 단계'창을 열고 '단계 이름'을 입력한 뒤 '작업 유형'을 선택합니다.


현재 시도할 작업은 Database Backup관련 Query문을 직접 실행하는 것이므로 'Transaction-SQL 스크립트(T-SQL)'항목을 선택하도록 합니다.

아래 Database 항목 에서는 실제 Query문이 실행될때 대상이 되는 Database를 선택하고 명령에서 작업을 수행할 Query문을 입력합니다.


입력된 Query 문은 master Database를 D드라이브의 dbbackup folder에 master.bak라는 이름으로 Backup되도록 합니다. 만일 입력된 Query문이 정확한지 확인해 보시려면 '구문분석'을 눌러 Query가 정확한지 확인해 볼 수 있습니다.


'고급'항목에서는 작업이 성공했을때와 실패했을 경우 후속작업및 출력, 실행계정등을 지정합니다.

모든 입력이 완료되었으면 '확인'을 눌러 설정한 작업을 저장합니다.

그 다음 '일정'->'새로 만들기'->'새 작업 일정'에서 언제 작업을 실행할지 지정합니다.


적절한 일정 이름을 입력하고 '일정휴형'에는 '되풀이'를 선택합니다. '되풀이'는 지정한 시간대마다 작업을 반복적으로 수행하도록 합니다.

여기서 아래와 같이 작업수행의 빈도와 시작, 종료일을 선택합니다.


'알림'항목에서는 작업 수행과 관련하여 성공이나 실패에 따라 사용자에게 결과를 알려주도록 설정할 수 있습니다.


대상에서는 현재 Server만을 대상으로 할지 아니면 여러 다른 Server를 대상으로 할지 지정할 수 있습니다.


모든 설정 항목을 확인한 후 '확인'을 누르면 작업설정이 완료 됩니다.


이제 시간이 되면 자동으로 Backup이 이루어질 것입니다.

0 0
Programming/Microsoft SQL Server
1. Server 관련

(1) DDL_LOGIN_EVENS (Group)

ⓐ Create_Login : 사용자 생성
ⓑ Alter_Login : 사용자 변경
ⓒ Drop_Login : 사용자 삭제

2. Database 관련

(1) Database(생성, 수정, 삭제)

ⓐ Create_database
ⓑ Alter_database
ⓒ drop_database

(2) Table(생성, 수정, 삭제)

ⓐ Create_table
ⓑ Alter_table
ⓒ Drop_table

(3) Procedure(생성, 수정, 삭제)

ⓐ Create_procedure
ⓑ Alter_procedure
ⓒ Drop_procedure

(4) Function(생성, 수정, 삭제)

ⓐ Create_function
ⓑ Alter_function
ⓒ Drop_function

(5) Index(생성, 수정, 삭제)

ⓐ Create_Index
ⓑ Alter_Index
ⓒ Drop_Index

(6) Trigger(생성, 수정, 삭제)

ⓐ Create_Trigger
ⓑ Alter_Trigger
ⓒ Drop_Trigger

(7) User(생성, 수정, 삭제)

ⓐ Create_User
ⓑ Alter_User
ⓒ Drop_User
0 0
Programming/Microsoft SQL Server
1. Rank()

Rank는 Over이하에 지정한 열의 순서대로 순번을 정합니다.(순서가 같은 경우에는 그 같은 수만큼 다음번 순위에 더해져서 조회됩니다.)

Select BusinessEntityID, Rank() Over(Order By OrganizationLevel Asc)
From HumanResources.Employee

OrganizationLevel의 값이 오름차순정렬된 순서대로 순번을 지정합니다.(이때 같은 순번만큼 다음 순위에 더하게 됩니다.)


보시는 바와 같이 순위가 2다음에는 3이 되어야 하지만 순위가 2위인 OrganizationLevel열값이 같은 것만큼 다음 순위에 더해진 후 조회되고 있습니다.

2. Dense_Rank()

Dense_Rank()는 순위가 같은건 묶어서 출력하되 순서대로 순번을 지정하여 조회하도록 합니다.

Select BusinessEntityID, Dense_Rank() Over(Order By OrganizationLevel Asc)
From HumanResources.Employee

OrganizationLevel값을 오름차순정렬된 순서대로 순위를 지정합니다.


기존 Rank()에서는 2다음 순위가 8이었으나 Dense_Rank()에서는 건너뛰는 순위가 없이 순서대로 지정됩니다.

3. Row_Number()

Row_Number()에서는 특정한 열값의 정렬순서대로 데이터를 정렬하긴 하지만  행단위로 순번을 지정하기 때문에 중복되는 순번이 존재하지 않으며 같은 순번만큼 더해서 출력하는 경우도 없습니다. 따라서 모든 행마다 순번을 순서대로 지정할 수 있습니다.

Select BusinessEntityID, Row_Number() Over(Order By OrganizationLevel Asc)
From HumanResources.Employee

OrganizationLevel의 값을 오름차순으로 정렬한 순서대로 조회합니다.


4. Ntile(x)

조회하는 데이터를 x에 지정한 수만큼 분할하여 순위를 부여합니다.

Select ProductID, Name, Ntile(10) Over(Order By SafetyStockLevel Asc)
From Production.Product

SafetyStockLevel열값을 오름차순으로 정렬한 후 출력되는 행전체를 10등분합니다. 그리고 10등분한 만큼 순위를 지정하여 출력하도록 합니다.




SafetyStockLevel이 오름차순으로 정렬된 Production.Product Table 전체 504행이 정확히 10등분 되고 각 등분마다 순위가 지정되었습니다.

Partition By는 지정된 열별로 Group을 나누어 그 Group안에서만 순위를 지정하도록 합니다. Partition By는 Rank, Dense_Rank, Row_Number, Ntile에 모두 적용할 수 있습니다.

Select ProductID, SafetyStockLevel, Rank() Over(Partition By SafetyStockLevel Order By StandardCost Asc)
From Production.Product

StandardCost에서 오름차순 순서대로 순위를 지정합니다. 이때 SafetyStockLevel이 같은것끼리만 묶어 각각의 Group안에서만 순위를 지정하도록 합니다.


보시는 바와 같이 Rank에 의해 순위가 지정되었으나 SafetyStockLevel이 4인것만 54까지 순위가 지정되고 SafetyStockLevel의 값이 다른것은 순위가 재지정되었습니다.
0 0
Programming/Microsoft SQL Server
Cast와 Convert는 모두 숫자를 문자열형이나 실수형으로 바꾸는 등의 형변환을 해주는 함수입니다.

1. Cast

특정 Data를 명시적으로 바꾸어 주는 함수로서 기본적은 구문은 다음과 같습니다.

Cast(데이터 As 변환형)

Data에는 변환대상을, 변환형에는 변환하고자 하는 형을 입력합니다.

Select cast('123' As Int) + 123;

문자열 123을(' <- 문자로 둘러 싸서 문자열로 표현하였습니다.) Int형으로 변환하고 123값을 더해 줍니다.


물론 위와 같은 상황에서는 굳이 cast함수를 쓰지 않아도 자동으로 형변환이 가능합니다.

Select '123' + 123;

그러나 되도록이면 Cast함수를 사용하여 명시적인 형변환을 하십시오. 가독성은 물론이거니와 자동형변환이 되지 않는 경우에는 오류가 발생할 여지가 많기 때문입니다. 또한 경우에 따라서는 원하지 않는 결과를 가져 오기도 합니다.

2. Convert

Convert또한 Cast와 마찬가지로 형변환을 해주는 함수 입니다. 차이점은 아래 구문형식처럼 형변환에 대한 형태(Style)를 지정할 수 있다는 것입니다.

Convert(변환형, 데이터, 형태)

또한 Convert는 날짜형을 변환하는데 유용하게 사용될 수 있습니다. 이때 날짜를 표현하는데는 여러가지 형식이 있을 수 있는데 이 형식을 Convert에서 지정하여 원하는 형식을 얻을 수 있습니다.

Select convert(Char(08), GetDate(), 112);
Select convert(Char(10), GetDate(), 120);


기타 Convert를 통한 날짜형식의 변환은 다음글을 참고해 주시기 바랍니다.

[Develop/SQL Server] - [SQL] 시간관련 형식 변환

비슷한 함수로 Try_Convert함수도 존재합니다. Convert와 같지만 변환에 실패하는 경우 NULL을 반환합니다.

3. Parse

특정 데이터를 지정한 형식으로 읽어들이는 함수입니다. 특히 일반문자열에서 날짜형식으로 변환하고자 하는 경우 유용하게 사용될 수 있습니다.

Select Parse('2016.12.31' As Date);

Try_Parse함수는 Parse와 같지만 변환에 실패하면 NULL을 반환합니다.


0 0
Programming/Microsoft SQL Server
Union은 두개 이상의 Table을 결합하여 마치 하나의 Table처럼 조회할 수 있도록 합니다. Table을 결합한다는 의미에서는 Join과 비슷하지만 Join과는 전혀 다른 개념이 적용되므로 주의하시기 바랍니다.

1. Union

먼저 다음 두개의 Table을 살펴보겠습니다.

Select * From Person.BusinessEntityAddress
Select * From Person.BusinessEntityContact


위의 두 Table에서 BusinessEntityID열만을 조회하여 보도록 하겠습니다. 이때 Union문을 통해 두 Table을 결합합니다.

Select BusinessEntityID
From Person.BusinessEntityAddress
Union
Select BusinessEntityID
From Person.BusinessEntityContact


위에서 처럼 Union을 통해 각각의 Table을 결합하여 조회하려면 조회하고자 하는 Table의 열 형식이 같아야 합니다.(열의 이름은 상관없습니다.) 단, 형식이 같아야 한다는 조건은 조회하고자 하는 열에만 해당합니다. 조회대상이 아닌 열은 포함되지 않아도 됩니다.

Select MakeFlag
From Production.Product
Union
Select FolderFlag
From Production.Document


이때 조회될때 표시되는 열 이름은 첫번째 Table의 열이름으로 정해지게 됩니다.

또한 Union사용시 Data정렬순서를 지정하고자 한다면 Union대상의 Table마다 일일이 Order By를 지정해 줄 필요없이 마지막 Table에만 Order By를 지정해 주시면 됩니다.

위 예제에서 Person.BusinessEntityAddress Table의 행은 19614행이고 Person.BusinessEntityContact Table은 909행이었으나 Union된 결과는 19579행입니다. 결과가 이렇게 되는 이유는 Union을 통해 Table이 결합되면 열값이 같은 경우(조회 대상이 열에 한하여) 중복해서 표시하지 않기 때문입니다.

2. Union All

Union을 통해 Table결합하여 조회하는 경우 중복값은 생략하고 결과를 표시하였습니다. 반면 Union All은 모든 테이블의 데이터를 생략없이 전부 조회하게 됩니다.

Select BusinessEntityID
From Person.BusinessEntityAddress
Union All
Select BusinessEntityID
From Person.BusinessEntityContact
Order By BusinessEntityID


3. Except

Except는 첫번째 조회결과중에서 두번째 결과에 해당하는 것을 제외하고 나머지를 출력하도록 합니다.

테스트를 위해 아래와 같이 간단하게 데이터를 추출합니다.

Select Top 100 AddressID, AddressLine1, AddressLine2, City, PostalCode
Into #Tmp
From [Person].[Address];

이제 [Person].[Address] 테이블의 데이터를 Select하되 Except를 사용하여 City가 Miami인 것은 제외하도록 하겠습니다.


Select AddressID, AddressLine1, AddressLine2, City, PostalCode
From [Person].[Address]
Except
Select AddressID, AddressLine1, AddressLine2, City, PostalCode
From #Tmp
Where City = 'Miami';


4. Intersect


Except와 반대로 첫번째 조회결과를 두번째 결과에 해당하는 것만 출력하도록 합니다.


Select AddressID, AddressLine1, AddressLine2, City, PostalCode
From [Person].[Address]
Intersect
Select AddressID, AddressLine1, AddressLine2, City, PostalCode
From #Tmp
Where City = 'Miami';

0 0
Programming/Microsoft SQL Server
1. Pivot

Pivot은 세로로 나열된 Table의 행(Row)을 가로열(Column)로 표시하는 것을 말합니다. 먼저 Pivot을 해보기 이전에 피벗되지 않은 상태로 Table을 조회하여 보도록 하겠습니다.

Select * From Sales.SalesPerson


위에서 조회한 Sales.SalesPerson Table에서 TerritoryID별로 Bonus의 합계를 구한 결과를 Pivot하여 가로로 표시하도록 하려면 다음과 같이 구현할 수 있습니다.

Select [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select TerritoryID, Bonus From Sales.SalesPerson) As PivoTable
Pivot (Sum(Bonus) For TerritoryID In ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) As APivoRtn


Pivot은 먼저 Select구문에 가로로 표시할 열을 []를 통해 줍니다. (Table의 특정 열값을 가로로 표시할 수 있도록 조회하는 것이기 때문에 가로로 표시될때의 이름은 조회하고자 하는 값 중에 존재하는 이름으로 지정해야 합니다.  따라서 위 Pivot문에서 Pivot할때는 TerritoryID 열값이 1~10까지 존재(null 제외)하였으므로 열 이름을 1~10까지 지정해 준것입니다.)

Select [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]

그 다음 From을 통해 실제 데이터를 조회할 Table을 지정합니다. (여기서는 Sales.SalesPerson Table에서 TerritoryID와 Bonus열만 조회하면 되므로 하위쿼리를 통해 이 두개의 열만 조회하도록 하였습니다.)
TerritoryID열은 가로로 표시할 열이며 Bonus열은 실제 데이터를 표시할 열에 해당합니다.

From (Select TerritoryID, Bonus From Sales.SalesPerson) As PivoTable

마지막으로 Pivot을 통해 표시할 데이터와 가로열을 지정하면 Pivot 구문이 완성됩니다.

2. Case문을 통한 Pivot

Case문을 사용하면 Pivot과 비슷한 결과를 얻으실 수 있습니다.
이전 Pivot한것과 비교해 보시기 바랍니다.

Select [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
From (Select TerritoryID, Bonus From Sales.SalesPerson) As PivoTable
Pivot (Sum(Bonus) For TerritoryID In ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) As APivoRtn

Select Sum(Case TerritoryID When 1 Then Bonus Else 0 End) As [1],
 Sum(Case TerritoryID When 2 Then Bonus Else 0 End) As [2],
 Sum(Case TerritoryID When 3 Then Bonus Else 0 End) As [3],
 Sum(Case TerritoryID When 4 Then Bonus Else 0 End) As [4],
 Sum(Case TerritoryID When 5 Then Bonus Else 0 End) As [5],
 Sum(Case TerritoryID When 6 Then Bonus Else 0 End) As [6],
 Sum(Case TerritoryID When 7 Then Bonus Else 0 End) As [7],
 Sum(Case TerritoryID When 8 Then Bonus Else 0 End) As [8],
 Sum(Case TerritoryID When 9 Then Bonus Else 0 End) As [9],
 Sum(Case TerritoryID When 10 Then Bonus Else 0 End) As [10]
From Sales.SalesPerson


Case문에서는 데이터 조회시 TerritoryID값이 각각에 해당하는 경우(1부터 10까지) 그에 맞는 Bouns열의 값을 Sum하여 표시하도록 하고 있습니다.

이 Case문을 이용한 방법은 실제 Pivot문을 사용해 Pivot하는 것보다 더 많이 쓰이고 있는 방법이기도 합니다. 또한 Pivot문을 통한 Table조회시에는 Data를 표시할때 해당 값에 포함되지 않는 경우 null로 표시됩니다. Null대신에 다른 값으로 표시하려고 해도 Pivot안에서는 이를 대체할 마땅한 대안이 없는 것입니다.

만약 Pivot 문에서 null 대신 다른 값을 표시하려면 ISNULL([01], 0) AS [01] 처럼 처리합니다. null 이라면 0이 대신 표시되는데 0대신 다른 원하는 값이 있으면 그것으로 대체할 수 있습니다.
- sqlpivotinpivot 님

Select WorkOrderID, [772], [725], [726], [729]
From (Select WorkOrderID, ProductID, OrderQty From Production.WorkOrder Where WorkOrderID <= 5) As PvtTable
Pivot (Sum(OrderQty) For ProductID In ([772], [725], [726], [729])) As PvtReturn


하지만 Case문을 이용하면 Else절에 의해 Null대신 Else로 지정된 데이터로 치환하여 표시할 수 있습니다.

Select WorkOrderID,
 Sum(Case ProductID When 772 Then OrderQty Else 0 End) As [772],
 Sum(Case ProductID When 725 Then OrderQty Else 0 End) As [725],
 Sum(Case ProductID When 726 Then OrderQty Else 0 End) As [726],
 Sum(Case ProductID When 729 Then OrderQty Else 0 End) As [729]
From Production.WorkOrder
Where WorkOrderID <= 5
Group By WorkOrderID


3. unPivot

Pivot이 가로열로 표시되도록 하는 것이라면 unPivot은 가로표시된 것을 다시 세로로 표시할 수 있도록 해줍니다. unPivot을 Test하기 위해 Pivot된 데이터를 임시 테이블에 저장한 후 임시 테이블에서 unPivot을 구현해보도록 하겠습니다.

Select [1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
Into #Temp
From (Select TerritoryID, Bonus From Sales.SalesPerson) As PivoTable
Pivot (Sum(Bonus) For TerritoryID In ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) As APivoRtn

Select * From #Temp


unPivot을 하기위해서는 먼저 세로로 표시할 열 이름을 지정해 줘야 합니다.(이때 열 이름은 사용자임의대로 지정합니다.)
그다음 unPivot을 통해 해당 열에 표시할 데이터를 지정해 주면 되는데 Pivot할때와 반대로 위에서 지정한 열 이름에 Pivot할때의 열 이름을 그대로 For를 통해 다시 구현하면 됩니다.

Select TerritoryID, Bonus
From #Temp
UnPivot (Bonus For TerritoryID In ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) As UnPivoRtn

6 0
Programming/Microsoft SQL Server
Insert는 Table에 Data를 추가하는 Query입니다.

먼저 Insert 구문의 Test를 위해 임시 Table을 생성하도록 하겠습니다.

Create Table #Product(
 Number Smallint,
 Name nChar(10),
 Price Int,
 RegDate DateTime
)


Table 이름 #Product에서 #은 생성하는 Table이 임시 Table이라는 것을 의미합니다.

1. Table의 모든 열에 Data 추가하기

Insert Into #Product
Values(0, '자전거', 50000, GetDate())

#Product Table에 존재하는 모든열에 Values에서 지정한 Data를 추가합니다.


#Product 임시Table에서 Number와 Price는 열 Data형이 정수형이므로 '와 '로 값을 지정하지 않았지만 Name은 nChar형(문자형)이므로 '와 '로 값을 지정하였습니다.

Insert Into #Product
Values('0', '자전거', 50000, GetDate())

위 예제에서는 정수형임에도 불구하고 '0'처럼 문자형으로 데이터를 지정하였습니다. 하지만 0이라는 문자도 정수형으로 변환될 수 있기때문에 정상적으로 처리되지만 되도록이면 Table의 열특성에 맞게끔 지정해 주는것이 성능면에서 더 좋은 선택이 될 것입니다.

참고로 위 Insert문에서 GetDate()는 현재시간(내 Computer가 아닌 Database Server시간 기준)을 출력하는 함수입니다.

위 그림에 보시면 Query로 인해 1개의 행이 영향을 받았음을 알려주고 있습니다. 그럼 어떻게 Data가 추가되었는지 확인해 보도록 하겠습니다.

Select * From #Product


참고로 SQL Server 2008부터는 다중 Insert가 가능하게 되었습니다. 이때는 데이터를 입력하는 괄호사이를 콤마(,)로 구분합니다.

Insert Into #Product Values
(0, '자동차', 300, GETDATE()),
(1, '비행기', 200, GETDATE());

2. 특정 열에만 Data 추가하기

위 Insert문에서 Values를 통해 값을 추가한 경우는 #Product 임시 Table에 존재하는 Number, Name, Price, RegDate 의 모든열에 Data를 추가하여야 하므로 해당 열 순서대로 값을 나열해야 했습니다.

이때 만일 Table에서 전체열이 아닌 특정열에만 Data를 추가하려면 Table명 옆에 추가하고자 하는 열을 지정해 주면 됩니다.

Insert Into #Product(Number, Name, RegDate)
Values(1, '자동차', GetDate())

#Product Table에서 Number, Name, RegDate열에만 지정한 Data를 추가합니다.


이제 Data가 어떤 식으로 처리되었는지 확인해 보도록 하겠습니다.

Select * From #Product


Insert에서 지정되지 않은 열은 자동적으로 Null처리가 됨을 알 수 있습니다.

3. Identity와 Default처리

Insert 하려는 테이블(Table)에 특정 컬럼(Column)이 Identity가 설정되어 있는 경우 해당 컬럼(Column)은 무시할 수 있습니다.

Create Table #Product(
 Number Smallint Identity,
 Name nChar(10),
 Price Int,
 RegDate DateTime
)


Insert Into #Product Values ('자동차', 300, GetDate());


만약 Identity값을 강제적으로 입력하고자 한다면 Identity_Insert 설정을 On으로 바꿔주고 해당 컬럼(Column)을 지정해 데이터를 Insert합니다.


Set Identity_Insert #Product On;

Insert Into #Product(Number, Name, Price, RegDate) Values (10, '자동차', 300, GetDate());


Default의 경우 Default로 설정된 값으로 Insert처리를 수행하고자 한다면 Insert에서 Default를 지정해 주기만 하면 됩니다.


Create Table #Product(
 Number Smallint,
 Name nChar(10) Default '자동차',
 Price Int,
 RegDate DateTime
)


Insert Into #Product Values(100, Default, 300, GetDate());


4. Table을 통한 Data 추가

Insert문은 Values를 통해 단일값만을 지정할 수 있는 것이 아니라 다른 Table의 Data를 원하는 만큼 추가할 수도 있습니다.

Test를 위해 임시Table을 하나더 생성합니다.

Create Table #Product2(
 Number Smallint,
 Name nChar(10),
 Price Int,
 RegDate DateTime
)

위에서 생성한 Table에 Data를 추가합니다.

Insert Into #Product2
Values(3, '비행기', 29900, GetDate())

추가된 Data를 확인합니다.

Select * From #Product2


이제 #Product2 Table에 있는 Data를 #Product Table에 추가하고 그 내용을 확인해 보도록 하겠습니다.

Insert Into #Product
Select * From #Product2

#Product2에 있는 모든 값을 #Product Table에 추가합니다.

Select * From #Product


Insert 구문을 통해 한 Table에서 다른 Table로 Data를 추가하려면 해당 Table의 열 형식이 일치하거나 Insert 구문에 있는 Table의 열 형식이 Select 구문에 있는 열 형식을 포함할 수 있어야 합니다.(포함되어야 한다는 것은 '1' 에서 1이나 1에서 '1'처럼 문자형에서 정수형으로 혹은 정수형에서 문자형으로 상호간 형변환이 가능해야 함을 의미합니다.)

Insert 와 Select를 통해 Data를 추가할 때에도 원하는 열만 추가할 수 있습니다.

Insert Into #Product(Number, Name)
Select Number, Name From #Product2

#Product2의 Number와 Name열의 Data를 #Product Table의 Number과 Name열에 추가합니다.


5. 다른 Table의 Data로 새로운 Table생성하기

Table을 통한 Data 추가는 이미 존재하는 Table에서 Data를 가져와 전혀 새로운 Table을 생성하는 방식으로 활용될 수 있습니다.

Select Number, Name, Price, RegDate
Into #Product3
From #Product

#Product의 Number, Name, Price, RegDate 열 Data를 불러와 #Product3 임시Table을 생성한뒤 불러온 Data를 담습니다.


Table과 Data가 정상적으로 생성되고 추가되었는지 확인해 보도록 하겠습니다.

Select * From #Product3


보시는 것처럼 #Product3 임시 Table이 생성되고 Data또한 정상적으로 추가되었음을 확인할 수 있으며 Into를 통한 Table 생성은 임시Table 뿐만 아니라 정상적인 Table생성도 가능합니다.


0 0
Programming/Microsoft SQL Server
XML 참고 : [Computer, IT] - XML을 써보자

SQL Server에서 특정 Data를 조회하기 위해 Query를 날리면 보통의 경우 Table형태로 그 결과를 출력하게 됩니다.

Select *
From HumanResources.Department;


하지만 필요한 경우 결과를 XML형태로도 조회할 수 있습니다. 구현 방법도 아주 간단한데 단지 Query 뒤에 for xml option을 추가하고 원하는 형식에 따라 raw, auto, explicit을 덧붙이기만 하면 됩니다.

1. Raw

Raw은 Xml형태로 결과를 출력할때 Table에 해당하는 각 Column을 요소의 속성으로 한뒤 Table의 실제 Data를 속성의 값으로 표현하도록 합니다.

Select DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw;


이때 각 행 요소의 이름은 row로 표시됩니다. 만일 이 이름이 마음에 들지 않는다면 괄호(())안에 원하는 이름을 지정해 줄 수 있습니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw('mytag');

요소 이름을 row가 아닌 mytag로 출력되도록 지정하였습니다.


Raw에 Elements요소를 추가하면 위에서처럼 단일 행으로 출력되는 형태가 아니라 각 행별(<row>)로 Column을 XML 요소로 하고 Table의 내용을 요소의 값으로 표현하도록 합니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw, Elements;


위 결과에서 Table의 Data를 요소의 값으로 표시하였으며 더불어 각 행마다 계층적으로 Query결과가 표현되었음을 확인할 수 있습니다. 이때 이 계층 구조의 요소에 최상위 요소를 추가하고자 하신다면 Root를 구문을 사용하십시오.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw, Elements, Root('topelement');

Root option으로 'ttopelement'라는 최상위 요소가 추가되도록 하였습니다.


참고:
이전까지의 내용도 그렇고 이후에 설명될 내용에서도 마찬가지지만 각 Option을 어떻게 사용할지는 개발자가 비교적 자유롭게 정할 수 있는 대목입니다. 예제에서도 Root는 마치 Elements option이 쓰여졌을때만 사용한다라는 오해의 소지를 갖게 될 수 있으나 실제로는 그렇지 않고 단독으로 쓸 수 있습니다.(물론 다른 Option도 특별한 경우를 제외하고는 모두 마찬가지 입니다.)

XML문서를 이루는 요소중의 하나로 DTD라는 것이 있습니다. 이 DTD를 통해 해당 XML문서의 구조를 알 수 있도록 하는데 Query에서 XmlData를 쓰면 그 결과로 XML이 표시될때 해당 XML이 어떻게 구성되어 있는지에 대한 DTD를 같이 나타내도록 할 수 있습니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Raw, XmlData;


XML출력결과의 상단에 Schema라는 이름으로 해당 XML구조를 나타내고 있습니다.

일반적인 Data형 Table의 경우에는 대부분 XML형태로 결과를 얻는데 큰문제가 없습니다. 그러나 Column의 Datatype형태가 Image나 Text와 같은경우에는 이 Data자체를 XML로 표현하기가 곤란할 수 있습니다. 따라서 해당 Column의 내용을 Binary로 변화시켜서 표현해야 하는데 이때 Binary Base64구문을 사용하시면 됩니다.

Select Top(2) ProductPhotoID, ThumbNailPhoto, ThumbnailPhotoFileName
From Production.ProductPhoto
For Xml Raw, Binary Base64;

AdventureWorks2008 예제 Database의 Production.ProductPhoto Table에는 ThumbNailPhoto라는 Image Datatype의 사진(그림)정보가 저장된 Column이 있습니다.

(실제 Binary Base64 출력결과를 보면 이보다 훨씬 길게 나타납니다.)

일반적인 방법으로 XML형태의 표현이 불가능한 경우는 또 있습니다. 바로 Table에 Null이 있는 경우 입니다.

Select Top(3) AddressID, AddressLine1, AddressLine2
From Person.Address
For Xml Raw;

Person.Address Table의 AddressID와 AddressLine1, AddressLine2를 3행까지 XML 형태로 출력하도록 합니다.


결과를 놓고 보니 뭔가 좀 이상하다는 생각할 할 수 있습니다. 분명 Query에서는 AddressLine2까지 표시하라고 했지만 AddressLine1만 나왔기 때문입니다. 이는 XML출력시에 Null은 결과에서 아예 제외하기 때문입니다. 이러한 현상을 막으려면 Xsinil Option을 써야합니다.

Select Top(3) AddressID, AddressLine1, AddressLine2
From Person.Address
For Xml Raw, Elements Xsinil;

Xsinil이 Elements와 함께 구현되었습니다.


결과에서는 Null에 해당하는 부분의 속성이 xsi:nil="true" 형태로 나타나 이 요소에 대한 Column의 내용이 null이라는 것을 알려주고 있습니다.

2. Auto

Raw사용시 이름을 지정해 주지 않으면 XML의 각 요소를 row항목으로 표시했었는데 Auto는 row가 아닌 조회되는 해당 Table명으로 XML의 요소를 이루도록 합니다.

Select Top(3) DepartmentID, Name, GroupName, ModifiedDate
From HumanResources.Department
For Xml Auto;


row대신 해당 Table명으로 각 요소를 이루고 있습니다.

어떻게 보면 Raw에서 Table영으로 이름을 지정해 준것과 결과가 동일하다고 볼 수 있겠습니다. 아무래도 의미없는 row대신 Table명으로 요소를 자동표시한다고 해여 이름이 auto인듯 합니다.^^;;

참고:
Table이름에 As구문을 사용하여 다른 이름을 부여했다면 해당 이름으로 요소명이 지정됩니다.

3. Explicit

Explicit는 Raw나 Auto와는 달리 출력되는 XML의 각 요소를 비교적 자유롭게 작성할 수 있도록 해줍니다. Result set을 원하는 XML형태로 조회하는 것이 가능하다는 얘기인데 기능이 우수한 만큼 사용하기도 다소 번거롭습니다.

일단 간단하게나마 짚어보도록 하겠습니다.

Select 1 As Tag, Null As Parent, DepartmentID As [Department!1!ID]
From HumanResources.Department
Order By [Department!1!ID]
For XML Explicit;

HumanResources.Department Table의 DepartmentID Column을 Explicit style로 출력합니다.

Select에서 1 As Tag는 출력할 Tag요소가 하나(1)인것만을 지정한다는 의미로 해석하시면 됩니다. 또한 출력하려는 요소의 부모요소는 현재 존재하지 않으므로 Null As Parent로 부모 요소를 지정합니다.(나중에 설명 드리겠지만 요소를 늘리려면 조회하는 구문을 하나 더 써서 Union으로 Join해야 합니다.)

[Department!1!ID]에서 Department는 Department요소를 추가시킨다는 의미 입니다. 뒤이은 !은 요소와 요소의 차수 그리고 속성을 구분해 주는 구분자 역활을 합니다.

차수는 현재 1단계 level의 요소만을 출력할 것이므로(Select 에서 1 As Tag로 하여 출력할 Tag요소가 하나라는 것을 이미 정의하였습니다.)1이라고 쓰고 다시 구분자(!)를 둔뒤 속성명으로 ID를 부여하여 Department요소에 ID라는 속성을 추가시켜 해당 속성값으로 DepartmentID Column내용을 출력하도록 합니다.


이번에는 위 결과에 하위 요소를 하나더 추가시켜 보겠습니다. Explicit option으로 요소를 추가하려면 해당 조회구문을 더 만들고 Union으로 Join하는 형태로 나가야 합니다.

Select Top(3) 1 As Tag, Null As Parent, DepartmentID As [Department!1!ID], Null As [Names!2!Nm]
From HumanResources.Department
Union
Select Top(3) 2 As Tag, 1 As Parent, DepartmentID, Name
From HumanResources.Department
Order By [Department!1!ID], [Names!2!Nm]
For XML Explicit;

DepartmentID Column에 이어 Name Column을 Department 요소의 하위요소로 추가하여 조회 하도록 하였습니다. 이처럼 요소를 늘리려면 그 만큼 Union Join을 통해 각 요소의 추가적인 조회 구분을 더 만들어야 합니다.

두번째 Select문에서는 2 As Tag라고 하였습니다. 말 그대로 해당 Select문에서는 DepartmentID와 Name두개의 Column을 조회하여 각 Tag 요소로 출력하기 때문입니다.

또한 1 As Parent라고 한것은 Name조회한 요소를 Department요소의 하위 요소로 출력할 경우 부모 요소는 Department요소가 되기 때문에 Parent를 1이라고 정의한 것입니다. 반면 첫번째 Select문에서는 여전히 조회하는 Column이 하나이므로 출력할 Tag요소도 하나이기 때문에 1 As tag는 변하지 않으며 DepartmentID에 대한 요소의 부모 요소도  첫번째 Select문에 존재하지 않으므로 부모 요소는 Null을 유지하고 있습니다.

한편 첫번째 Select문에 DepartmentID이후 Null을 조회하도록 한 것은 해당 요소의 내용은 두번재 Select문에서 가져오기 때문입니다. 결국 두번째 Select문에서 가져온 Name column값을 Names라는 요소로 만들고 하위 2번째 요소로 정한다음 Nm속성을 만들어 속성의 값으로 표시([Names!2!Nm])한 것입니다.

두번째 Select문에서 DepartmentID와 Name은 이미 첫번째 Select문에서 각 Column에 대한 요소를 [Names!2!Nm]처럼 정의했기 때문에 또 다시 정의하는 것은 불필요합니다. 왜냐하면 Column의 이름만 써주면 그걸로 충분하기 때문이죠.

Top(3)은 출력되는 부분이 너무 커서 행을 다소 줄이기 위해 추가한 것입니다. 그리고 Order By는 조회할 각 Column을 정렬해 주지 않으면 XML출력시 결과요소가 서로 뒤엉키는 문제가 생기지 않도록 정렬하기 위한 것입니다.


이번에는 Department요소와 Names요소에 이어 Group요소를 하나더 추가해 보겠습니다.

요소를 늘리기 위해 조회 구문을 하나 더 만들어 Union으로 join합니다. 세번째 Select문의 구현 원리도 두번째 설명드린 구현원리와 같습니다.


물론 부모 요소는 반드시 첫번째 Select문의 1이어야할 필요는 없습니다. 필요하다면 바꿀 수 있는데 이런경우 당연히 출력되는 형태도 달라질 것입니다.

Select Top(3) 1 As Tag, Null As Parent, DepartmentID As [Department!1!ID], Null As [Names!2!Nm], Null As [Group!3!Name]
From HumanResources.Department
Union
Select Top(3) 2 As Tag, 1 As Parent, DepartmentID, Name, Null
From HumanResources.Department
Union
Select Top(3) 3 As Tag, 2 As Parent, DepartmentID, Name, GroupName
From HumanResources.Department
Order By [Department!1!ID], [Names!2!Nm], [Group!3!Name]
For XML Explicit;

세번째 Select문에서 부모 요소를 1이 아닌 2라고 지정하였습니다. 즉, 두번째 요소인 Names요소를 부모요소로 지정한 것입니다.


이번에는 좀 다르게 요소를 추가하지 않고 Names 요소에다가 Name과 GroupName Column의 내용을 Nm, Group의 두개 속성으로 만들어 표현해 보도록 하겠습니다.

Select Top(3) 1 As Tag, Null As Parent, DepartmentID As [Department!1!ID], Null As [Names!2!Nm], Null As [Names!2!Group]
From HumanResources.Department
Union
Select Top(3) 2 As Tag,  1 As Parent,  DepartmentID,  Name,  GroupName
From HumanResources.Department
Order By [Department!1!ID], [Names!2!Nm], [Names!2!Group]
For XML Explicit;

이전보다 Select문이 하나 더 줄었습니다. 기존에는 Department와 Names 그리고 Group 이렇게 3개의 요소를 출력하려 했으므로 세개의 Select문을 Union Join해야 했지만 이번에는 기존 Group요소를 없애고 대신 해당 Group내용을 Name요소에 통합하여 두개의 속성으로 만들고 출력하기 때문에(출력할 요소가 Department와 Name두개 이기 때문에) Join될 Select구문도 두개면 충분한 것입니다.

먼저 첫번째 Select문에서 두번째 조회 column부분에 [Name!2!Nm]이라고 정의하면 Name요소가 만들어질 것이고 그 이후에 만들어진 Name요소에다가 속성만을 추가할 것이므로 세번째는 요소명과 출력될 요소의 차수를 똑같이 써서 추가할 속성명만 [Names!2!Group]처럼 지정해 주면 됩니다.

물론 두번째 Select문에서는 Group속성에 표시할 Column(예제에서는 GroupName)을 지정해 줘야 하겠지요.


Names요소에 Nm과 Group속성 두개가 추가되고 각각의 속성에 Name과 GroupName내용을 표시하고 있습니다.

이제까지 예제에서는 모두 속성을 통해 값을 표시하고 있지만 이렇게 하지 않고 요소 자체에다가 해당 내용이 보여지도록 지정할 수도 있습니다.

Select Top(3) 1 As Tag, Null As Parent, DepartmentID As [Department!1!ID], Null As [Names!2!Nm!Element], Null As [Names!2!Group!Element]
From HumanResources.Department
Union
Select Top(3) 2 As Tag, 1 As Parent, DepartmentID, Name, GroupName
From HumanResources.Department
Order By [Department!1!ID], [Names!2!Nm!ELEMENT], [Names!2!Group!ELEMENT]
For XML Explicit;

요소 지시자에 !로 구분하여 Element를 추가하였습니다. 첫번째 Select문에서 두번재와 세번째 Column조회 부분을 주목해 주십시오.


각 Column의 값이 속성이 아닌 Tag 요소를 통해 표시됨을 확인할 수 있습니다.

참고:
출력하려는 XML의 각 요소에 ID를 지정하고자 하신다면 Element를 지정한 것과 같이 ID를 지정하시면 됩니다. 또한 조회하는 Column에 Null이 있다면 Element대신 Elementxsinil을 사용하시면 Null이 있는 Column도 조회가 가능합니다.
0 0
Programming/Microsoft SQL Server
1. Coalesce

지정된 인수중 Null아닌 것중에 첫번째 값을 반환하는 함수입니다.

Select Top(20) *
From Person.Person;
Select Top(20) Coalesce (Title, FirstName, MiddleName, LastName)
From Person.Person;

Person.Person Table에서 Title, FirstName, MiddleName, LastName열값을 가져오되 Null아닌것중에서 첫번째 값을 가져오도록 합니다.


2. Nullif

Nullif함수는 지정된 두 인수의 값을 비교하여 같으면 Null을, 같지 않으면 두 인수중 첫번째 인수값을 반환합니다.

Select BusinessEntityID, EmailAddressID
From Person.EmailAddress
Order By BusinessEntityID;

Select Nullif(BusinessEntityID, EmailAddressID)
From Person.EmailAddress
Order By BusinessEntityID;


Person.EmailAddress Table에서 BusinessEntityID, EmailAddressID열값을 가져와 같으면 Null, 같지 않으면 BusinessEntityID열값을 가져옵니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] Insert  (0) 2010.10.15
[SQL] For Xml을 이용해 Table을 XML형태로 조회하기  (0) 2010.10.01
[SQL] Coalesce 와 Nullif  (0) 2010.09.14
[SQL] xact_abort와 Transaction  (0) 2010.09.13
[SQL] Output으로 변경Data 확인하기  (0) 2010.09.10
[SQL Server] SQLCMD  (0) 2010.09.09
0 0
Programming/Microsoft SQL Server
Batch처리내에서 Query문이 수행될때 오류가 발생하게 되면 다음 오류의 종류에 따라 약간씩 다른 방식으로 Query문이 종료됩니다.

(1) 문법에 맞지 않는 Query문 수행시

▶ Batch처리내에 있는 모든 Query문은 실행되지 않습니다.

(2) 잘못된 Table이나 변수등이 사용될시

▶ xact_abort Option에 따름

(3) 연산관련 오류 발생시

▶ xact_abort Option에 따름

(4) Table의 제약조건에 맞지 않는 동작수행시

▶ xact_abort Option에 따름

여기에서 xact_abort Option은 Batch처리내(Go)내에서 Query 수행시 오류가 발생하면 자동적으로 Rollback처리할지를 지정하는 Option으로서 On이면 오류가 발생한 Query문다음의 모든 Query실행을 취소하고 이전에 실행한 Query문에 대해서 자동 Rollback처리합니다. Off으로 설정시에는 오류가 발생한 해당 Query문만 실행되지 않고 다음에 나오는 Query문을 계속 실행하게 됩니다.

보통 Transaction을 걸어둔 상태에서 Query실행시 사용자가 Query 수행이 잘못되었다고 판단되면 임의적으로 Rollback하여 Table을 원래상태로 되돌렸으나 xact_abort는 Batch처리내에서 오류가 발생했을경우에 한해 명시적으로 Rollback하지 않아도 자동으로 Rollback을 수행하는 것입니다.
단, Rollback도 Transaction의 일부분이므로 Batch처리에서 Transaction이 같이 동반되어야만 자동적인 Rollback이 가능합니다.

Select *
From HumanResources.Department


Set xact_abort On

xact_abort Option을 On으로 설정합니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insertt Into humanResources.Department
Values('RES', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

Go부터 세개의 Insert 문을 Batch처리로 하여 Query를 실행합니다. 이때 두번째 Insert문에서는 원래 Insert라고 해야하지만 Insertt라고 하여 문법상 맞지 않는 문장을 사용합니다.


해당 Query실행으로 오류가 발생하였습니다. 어떻게 처리되었는지 결과를 알아보기 위해 Table을 확인해 보도록 하겠습니다.

Select *
From HumanResources.Department


Table에 어떠한 내용도 추가되지 않았습니다.

위 Query에서 잘못된것은 중간에 있는 Insertt뿐이었지만 다른 정상적인 Insert문의 실행도 같이 취소되었기 때문입니다.
문법상 오류가 발생한 부분에 대해서는 Batch처리내에 있는 모든 Query문을 실행하지 않는 것입니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Departmentt
Values('RES', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

이번에는 잘못된 Table 이름을 지정하였습니다.(중간에 있는 Insert문에서 Table이름을 humanResources.Department대신 humanResources.Departmentt로 하였습니다.)


처음의 Query문은 정상적으로 실행하였으나 이후 부터는 오류가 발생하였습니다.
처리된 내용을 확인하기 위해 Table을 확인하여 보겠습니다.

Select *
From HumanResources.Department


처음 Query문은 올바르게 실행되었다고 나왔지만 Table을 확인해보니 추가된 내용은 없습니다. 이는 오류가 발생한 시점의 Query문부터 Batch처리내의 모든 Query문이 실행되지 않았고 동시에 정상적으로 실행했던 Insert문에 대해서도 xact_abort옵션에 의해 자동 Rollback이 되었기 때문입니다.

이처럼 자동 Rollback이 되는 부분은 연산및 제약조건관련 에러에서도 동일하게 작용합니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values(100/0, 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

100을 0으로 나누는 작업을 수행하도록 합니다.


처음만 정상적으로 처리되고 이후에는 오류가 발생하였습니다.

Select *
From HumanResources.Department


Table에는 추가된 부분이 없습니다. 오류가 발생하여 Query문이 실행되지 않았고 이전에 실행한 Query문에 대해서는 자동적으로 Rollback되었습니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values('MAT', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

humanResources.Department Table의 Name열에는 동일한 내용의 문자열이 들어갈 수 없도록 제약이 설정되어 있으나 예제에서는 이전의 Insert문과 똑같은 이름의 'MAT'을 추가하도록 하였습니다.


Select *
From HumanResources.Department


오류가 발생한 시점부터 이후 Query문은 실행되지 않았으며 이전에 실행된 Insert문에 대해서는 자동 Rollback처리되었습니다.

HumanResources.Department Table에서 DepartmentID열은 자동증감이 설정된 열이기 때문에 임의적으로 Data를 추가할 수 없으며 Data가 추가될 때마다 자동적으로 열값이 1씩 증가하도록 되어 있습니다. 따라서 DepartmentID열을 제외한 다른열에 Insert문을 통하여 Data를 추가하도록 하였습니다. 이때 예제에서 시도한 Batch처리문에서 오류가 발생하였는데 다만 처음 한행은 올바르게 실행되었다는 점을 주목해 주십시오.

Table에 아무런 변화가 없는것은 올바르게 실행된 Insert에 대해 자동적으로 Rollback이 실행되었기 때문입니다. 하지만 이 Rollback은 이미 추가된 Data를 원상태로 되돌리기 위한 Rollback일뿐 Table의 변화까지도 Rollback되진 않습니다.

즉, 이전에 실행된 Insert문에 의해 HumanResources.Department Table의 DepartmentID값이 1이 증가된 상황에서 Rollback한다 하더라도 증가된 값까지 Rollback으로 되돌아 가진 않는 것입니다.
따라서 해당 Table에 Data가 정상적으로 추가되면 자동증가값이 순서대로 들어가지 않게 되는 문제가 발생할 수 있는 것입니다.

Insert Into humanResources.Department
Values('abcd', 'abcde', getdate())

humanResources.Department Table에 임의로 Data를 추가 합니다.


Select *
From HumanResources.Department

humanResources.Department Table을 확인합니다.


DepartmentID열값이 18로 들어가 있습니다. 이것은 이전에 실행한 Insert문에 의해 이미 DepartmentID값이 증가한 상태이며 이후에 실행한 Insert문에대해서도 값의 자동증가에 따라 1이 증가되었기 때문입니다.

지금까지 살펴본 Batch처리문에서 한가지 의문을 가질 수 있는건 'Batch처리를 실행할때 Transaction을 시작하고 Query를 실행하였다면 오류가 발생했을때 이후의 Query문이 실행되지 않았으므로 당연히 Commit Tran문도 실행되지 않은 것으로 봐야하지 않을까?'하는 것입니다. 결국 Data의 추가가 완료되지 않았을뿐 'Rollback되었다 라고는 보기 어렵다' 라고 하는 것인데 이러한 부분은 xact_abort Option을 Off로 한뒤에 같은 Query를 실행해 보면 확연히 다르다는 것을 알 수 있습니다.

Set xact_abort Off

xact_abort Option을 Off로 설정합니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Departmentt
Values('RES', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran


이전과 마찬가지로 같은 오류내용이 나타났습니다. 처리결과를 확인해 보기 위해 Table을 조회해 보도록 하겠습니다.

Select * From HumanResources.Department


오류가 발생한 이후의 Query문은 실행되지 않았지만 처음 정상적으로 실행되었던 Insert문에 대해서는 Rollback되지 않았습니다. 이것은 xact_abort가 Off로 되어 있기 때문입니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values(100/0, 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

중간 Insert문에서 0으로 나누는 작업을 시도합니다.


일부러 의도한 Query문에 대해서는 오류가 나타났으며 나머지 두 Insert문은 정상적으로 실행되었음을 나타내고 있습니다. 그럼 정말 그런지 Table을 확인해 보도록 하겠습니다.

Select *
From HumanResources.Department


중간 Insert문을 제외하고 나머지 쿼리문은 올바르게 실행되었습니다. xact_abort가 Off인 상태에서 연산관련 오류가 발생하면 해당 Query문만 실행되지 않을뿐 나머지 Query에 대한 실행은 계속하게 되는 것입니다.

Go
Begin Tran
Insert Into humanResources.Department
Values('MAT', 'Mat Management', getdate());
Insert Into humanResources.Department
Values('MAT', 'Res Management', getdate());
Insert Into humanResources.Department
Values('OSP', 'Osp Management', getdate());
Commit Tran

두번째 Insert문에서 첫번재 Insert문과 똑같은 이름의 값을 추가하도록 합니다.


연산오류와 동일하게 문제가 되는 Query를 제외하고 다른 부분은 올바르게 실행되었음을 나타냅니다.

Select *
From HumanResources.Department


오류가 발생한 Insert문을 제외하고 다른 Query문은 정상적으로 수행되었습니다. Table의 제약조건을 위반하는 경우에도 xact_abort가 Off면 해당 Query문을 제외한 다른 Query문은 정상적으로 수행합니다.
0 0
Programming/Microsoft SQL Server
Insert, Update, Delete를 통해 Table이 변경될때는 우선 내용을 일시적으로 담고 있는 임시Table이 Server의 Merory상에 생성되고 변경이 완료되면(해당 변경구문의 Transaction이 종료되는 시점) 그 즉시 다시 사라지게 됩니다. 이때 Output(보통 Trigger에서 많이 사용되는)을 사용하면 Table이 변경될때의 Data를 일시적으로나마 확인할 수 있게 됩니다.

아래는 Output을 통해 변경 Data를 확인하는 예제입니다.

Insert Into HumanResources.Department
Output inserted.*
Values('Executive', 'Executive General and Administration', getdate())


Update HumanResources.Department
Set Name = 'Management',
 GroupName = 'Management Group'
Output deleted.*, inserted.*,
Where DepartmentID = 16

Update의 경우에는 Update를 위한 별도의 임시Table이 존재하지 않고 이와 관련된 Inserted와 Deleted Table이 생성됩니다.

즉 변경하기전 Data는 Deleted에 또한 변경할 Data는 Inserted에 담게 되는 것입니다.(즉, Update는 Data삭제와 추가를 병행함으로서 실질적인 변경작업을 수행합니다.)


Delete From HumanResources.Department
Output deleted.*
Where DepartmentID = 16


Select문에서도 사용되는 * 문자는 해당 Table의 모든 열을 의미합니다. 만일 별도의 열값만 조회할 필요가 있다면 다음과 같이 * 대신 열이름을 지정해 줄 수도 있습니다.

Insert Into HumanResources.Department
Output inserted.Name, inserted.GroupName
Values('Executive', 'Executive General and Administration', '1998-06-01 00:00:00.000')


Output을 활용하면 편법이긴 합니다만 Table이 변경되는 이력(일종의 Log)을 남겨두는 용도로 쓰이기도 합니다.

예를 들어 HumanResources.Department Table에서 Data가 삭제될때마다 삭제되는 Data의 이력을 남겨두고자 한다면 우선 HumanResources.Department Table과 동일한 형태의 Log Table을 만들고

Create Table HumanResources.Department_DeleteLog(
 DepartmentID Smallint,
 Name   nVarChar(50),
 GroupName  nVarChar(50),
 ModifiedDate DateTime
)

Delete 문으로 HumanResources.Department Table을 삭제시 Output을 이용해 위에서 만든 Log Table에 삭제되는 Data를 담아두는 것입니다.

Delete From HumanResources.Department
Output deleted.* Into HumanResources.Department_DeleteLog
Where DepartmentID = 16

Output을 통해 HumanResources.Department_DeleteLog의 Log Table에 삭제되는 Data를 저장합니다.

Select *
From HumanResources.Department_DeleteLog

삭제된 이력을 확인합니다.


다만 다음과 같이 임시Table을 만드는 동시에 Data를 저장하는 방법은 통하지 않으니 Log Table을 미리 만들어 둬야 합니다.

Select *
Into #Temp
From HumanResources.Department

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] Coalesce 와 Nullif  (0) 2010.09.14
[SQL] xact_abort와 Transaction  (0) 2010.09.13
[SQL] Output으로 변경Data 확인하기  (0) 2010.09.10
[SQL Server] SQLCMD  (0) 2010.09.09
[SQL] 케이스(Case)  (0) 2010.09.07
[SQL] 예외처리(try ~ catch)  (0) 2010.09.06
0 0
Programming/Microsoft SQL Server
SQL Server Management는 Microsoft SQL Server에서 사용되는 Database관리 도구중 하나입니다.


보시는 도구는 Visual적인 사용자 Interface와 Server관리를 위한 다양한 기능들을 제공하는 도구로서 표준적으로 가장 널리 사용되고 있습니다.

반면 지금 부터 설명해드릴 SQLCMD는 위과 같은 Interface는 제공하지 않지만 몇가지 명령어를 통해 간단하게 Server에 접속하고 Query를 통한 작업도 할 수 있도록 마련된 명령행 도구입니다.

먼저 SQLCMD를 시작하시려면 Windows 작업표시줄의 '시작 -> 실행' 에서 'sqlcmd'라고 입력하고 '확인'을 눌러줍니다.



이것으로 SQL Server에 성공적으로 연결이 되었습니다. 별다른 절차없이 sqlcmd명령어 만으로 Server에 연결된 것은 Localhost의 SQL Server로 Windows계정을 통한 접속이 이루어 졌기 때문입니다.

만일 원하는 Server로 연결하고자 하신다면 'sqlcmd -E -S <Server>'와 같은 형식으로 대상 Server를 지정하여 입력하시면 됩니다.


sqlcmd 사용에 관한 좀더 자세한 Option을 보시려면 'sqlcmd /?'를 입력해 보십시오. 위 화면에서 처럼 각 Option에 따른 도움말을 보실 수 있는데 여기서 -E 는 trusted connection 으로 다른 계정이 아닌 현재 사용중인 Windows 계정을 통하여 Server에 접속할 것임을 의미합니다.

주의:
-로 시작하는 각 Switch는 대소문자를 구별합니다.

특정한 계정을 통해 Server에 접속하고자 하시는 경우 'sqlcmd -U <계정명> -P <암호> -S <서버>'형식으로 명령을 입력하시면 됩니다.


만일 -P Switch를 통해 비밀번호가 노출되는 것을 원하지 않으신다면 -P Switch는 생략할 수도 있습니다. 이 경우 Login하기 전에 비밀번호를 묻게 되며 이때 입력된 비밀번호는 화면에 나타나지 않기 때문에 좀더 안전하게 접속하실 수 있습니다.


-S Switch는 생략할 경우 자동으로 Localhost가 지정됩니다.

이제 Server연결에 성공하였으니 간단한 Query문을 통해 원하는 Data를 불러와 보도록 하겠습니다.


'use'문을 통해 Database를 선택한 후 'Select'로 Database의 Table을 조회합니다.

'go'는 이전에 호출했던 'go'문 이후에 입력된 모든 Query를 실행하도록 합니다. 만일 입력한 Query를 취소하고 새로 입력하고자 한신다면 Reset을 통해 최기화 하시면 됩니다.

참고:
입력한 Query가 실행중일때는 Ctrl + C 키를 누르시면 실행을 중지할 수 있습니다.


처음에는 'Select Name, GroupName From HumanResources.Department'문을 통해 Name와 GroupName만을 조회하려고 하였으나 Reset을 통해 이를 취소하고 'Select * From HumanResources.Department'로 모든 열을 조회하였습니다.

만일 sqlcmd안에서 Query작성도중 외부 명령어를 실행시킬 필요가 있을 때는 sqlcmd를 빠져나갈 필요없이 !! 구문을 통해 외부명령어를 호출 할 수도 있습니다.


!!dir/w 문장을 통해 현재 경로에 있는 folder및 Directory안의 file목록을 조회합니다.

작업을 완료하신 후 sqlcmd를 빠져나가시려면 exit나 quit를 입력하시면 됩니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] xact_abort와 Transaction  (0) 2010.09.13
[SQL] Output으로 변경Data 확인하기  (0) 2010.09.10
[SQL Server] SQLCMD  (0) 2010.09.09
[SQL] 케이스(Case)  (0) 2010.09.07
[SQL] 예외처리(try ~ catch)  (0) 2010.09.06
[SQL] Batch처리(일괄처리)의 개념  (0) 2010.09.03
0 0
Programming/Microsoft SQL Server
Case문은 일종의 조건식입니다. 비슷한 조건식으로 If문이 있는데 If문은 참 또는 거짓형태만을 조건식으로 지정할 수 있는반면 Case문은 If문보다 분기할 수 있는 조건의 선택폭이 좀더 넓다고 할 수 있습니다.

Declare @iTF Bit;
Set @iTF = 0;
Select Case @iTF When 0 Then  N'0 입니다.' Else N'1 입니다.' End;

Case 문을 통해 @iTF값이 0일때와 1일때 각각 다른 내용의 Message를 반환하도록 합니다.


When와 Then절을 사용한 예입니다.

Declare @iTF Tinyint;
Set @iTF = 3;

Select Case @iTF When 0 Then N'0 입니다.'
  When 1 Then N'1 입니다.'
  When 2 Then N'2 입니다.'
  When 3 Then N'3 입니다.'
  Else N'3보다 큰 수 입니다.' End;


When에 의해 지정된 값을 비교하면서 해당 값과 맞으면 Then을, 값이 존재하지 않으면 Else절에 있는 구문을 실행하도록 하였습니다.


Case를 통해 각기 다른 조건에 따라 처리를 지정하려면 'When 조건식 Then 처리내용'과 같은 방법으로 지정합니다.

Select Color, (Case When Color = 'Silver' Then SafetyStockLevel
  When Color = 'Red' Then ReorderPoint
  When Color = 'Black' Then StandardCost
  Else ListPrice End)
From Production.Product
Where Color Is Not Null;

Color가 Silver이면 SafetyStockLevel Column의 값을, Red이면 ReorderPoint Column의 값을, Black이면 StandardCost Column의 값을 가져오고 Silver나 Red, Black이 아니면 ListPrice값을 가져오도록 합니다.


주의:
Case문에서 Then절에 의한 처리가 여러개일때 값의 형태는 문자열형이든 숫자형이든 Data형이 같아야 합니다. 만일 Data형이 다른 경우 자동변환을 할 수는 있지만 그것 조차 안되는 경우 오류를 일으킬 수 있습니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] Output으로 변경Data 확인하기  (0) 2010.09.10
[SQL Server] SQLCMD  (0) 2010.09.09
[SQL] 케이스(Case)  (0) 2010.09.07
[SQL] 예외처리(try ~ catch)  (0) 2010.09.06
[SQL] Batch처리(일괄처리)의 개념  (0) 2010.09.03
[SQL] Update  (0) 2010.09.01
0 0
Programming/Microsoft SQL Server
Query에서는 문법적인오류를 제외한 대부분의 오류는 Try / Catch문으로 처리가 가능합니다.

Begin Try
     Select 10/0;
End Try
Begin Catch
      Select 'Error';
End Catch

Begin Try와 End Try사이에 있는 Query문 수행시 오류가 발생할 경우 Begin Catch와 End Catch사이에 있는 Query문을 대신 실행하도록 합니다.


Try Catch문을 통해 오류를 처리하는 경우 정확히 어떠한 내용의 오류가 발생하는지 확인해 보려면 <2-1>에 표시하고 있는 함수를 사용합니다. 오류 정보를 확인할 수 있는 함수는 대개 Error로 시작하는 함수이며 그 종류와 내용은 다음과 같습니다.

 error_line()  오류가 발생한 행수를 나타냅니다.
 error_message()  어떠한 오류인지 그 내용을 표시합니다.
 error_number()  오류번호를 나타냅니다.
 error_procedure()  오류가 발생한 해당 Procedure, Triger등의 이름을 나타냅니다.
 error_severity()  오류 심각도를 나타냅니다.
 error_state()  오류 상태를 나타냅니다.

숫자형태로 표시되는 심각도는 그 내용에 따라 의미가 각기 다릅니다. 자세한 내용은 아래 표를 참고하십시오.

 10 ~ 16  사용자의 잘못으로 인한 오류입니다.
 17 ~ 25  Software및 Hardware관련 오류입니다.(17 ~ 19 까지는 상황에 따라 오류가 발생하여도 작업은 계속 진행될 수 있습니다.)

Begin Try
     Select 10/0;
End Try
Begin Catch
      Select error_line(), error_message(), error_number(), error_procedure(), error_severity(), error_state()
End Catch

오류함수를 이용하여 error에 관한 자세한 정보를 표시하도록 하고 있습니다.


Try / Catch문을 Procedure안에 포함시키면 특정 오류가 발생했을때 자동 Rollback하도록 처리 할 수 있습니다.

Begin Try
 Begin Tran
  Insert Into HumanResources.Department
  Values('QC', 'QC Management', getdate());
 
  Insert Into HumanResources.Department
  Values('QA', 'QA Management', getdate());
 
  Select 10/0;
 
  Commit;
End Try
Begin Catch
 Rollback;
 Select 'Error';
End Catch
Select * From HumanResources.Department;

Insert문을 두번 실행한뒤 10을 0으로 나누는 오류를 일으키도록 하였습니다. 오류가 발생하면 Catch부분을 통해 Rollback되고 'Error'이라는 문구를 표시할 것입니다.


참고로 고의적으로 예외(오류)를 발생시킬 수도 있는데 이때는 Raiserror이나 Throw등이 사용됩니다. Raiserror에 대해서는 아래 링크를 참고해 주십시오.

2010/01/25 - [Programming/Microsoft SQL Server] - [SQL] Message 처리


Throw의 사용방식은 아래와 같습니다.


Throw 55555, N'오류 발생', 1;


Throw의 첫번째 인자는 오류번호를 의미합니다. 임의로 50000~21억까지 지정할 수 있습니다. 그 다음 오류관련 메세지를 기록하고 state를 지정합니다. 예제에서 state는 1이며 0~255까지 지정가능합니다.


'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL Server] SQLCMD  (0) 2010.09.09
[SQL] 케이스(Case)  (0) 2010.09.07
[SQL] 예외처리(try ~ catch)  (0) 2010.09.06
[SQL] Batch처리(일괄처리)의 개념  (0) 2010.09.03
[SQL] Update  (0) 2010.09.01
[SQL] Replicate로 문자열 채워넣기  (0) 2010.08.26
0 0
Programming/Microsoft SQL Server
T-SQL에서의 Batch처리는 Go문부터 실행(혹은 또다시 Go가 나올때 까지)하는 전체를 의미합니다.

원래 SQL Server의 Query실행단위는 하나의 Batch처리단위로 실행하는 것을 원칙으로 합니다만 일반적인 상황에서는 Batch처리가 필요하지 않고 저장Procedure를 생성하여 Query를 작성하는 경우에 Batch처리의 개념으로 Go문이 사용됩니다.

또한 일괄처리 내에서 작성되는 Query중 다음 Create문은 같은 Batch처리단위에서 사용될 수 없으며 Go를 써서 처리를 분리해야 합니다.

Create Default
Create Function
Create Procedure
Create Trigger
Create View


Procedure의 Batch처리단위

참고:
(1) 위 Procedure의 끝부분에 있는 End; 에서 ; 문자는 종결자라고 합니다. 종결자는 Query가 실행되는 하나의 Query단위의 종료문자에 해당합니다.
(2) Go뒤에 숫자를 써주게 되면 해당 하는 숫자만큼 Go영역의 배치처리실행을 반복하게됩니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 케이스(Case)  (0) 2010.09.07
[SQL] 예외처리(try ~ catch)  (0) 2010.09.06
[SQL] Batch처리(일괄처리)의 개념  (0) 2010.09.03
[SQL] Update  (0) 2010.09.01
[SQL] Replicate로 문자열 채워넣기  (0) 2010.08.26
[SQL] Delete  (0) 2010.08.25
0 0
Programming/Microsoft SQL Server
Table의 Data를 변경하기 위한 구문으로는 Update가 사용됩니다. Update문을 다루는 것에는 큰 어려움없이 무난하게 접근하실 수 있으나 Data를 변경하는 것이므로 사용시에 주의를 기울이시는게 좋습니다.

1. 모든열 수정

Update HumanResources.Department
Set GroupName = GroupName + ' Part'

HumanResources.Department Table의 GroupName 열뒤에 'Part' 문자열을 추가합니다.


2. 선택적 열 수정

Update문도 Where절을 통해 특정 조건을 지정하여 원하는 행만 수정되도록 할 수 있습니다.

Update HumanResources.Department
Set GroupName = 'an executive department'
Where Name = 'Executive'

HumanResources.Department Table에서 Name이 'Executive'인것만 GroupName을 'an executive department'로 변경합니다.


Update 조건은 Where문을 이용한 단순한 비교식 이외에도 In이나 Like및 다른 Table과의 조인, 하위Query등 Select문에서 다루었던 거의 모든 조건을 Update문에서도 동일하게 적용할 수 있습니다.

3. 두건이상의 열변경

Update HumanResources.Department
Set Name = 'Admin', GroupName = 'Administrator Group'
Where DepartmentID = 16

HumanResources.Department 에서 DepartmentID가 16인 행의 Name열과 GroupName열 Data를 변경합니다.


4. 변경대상을 조건으로 하기

필요하다면 변경하고자 하는 열 자체를 다음과 같이 조건으로 지정할 수도 있습니다.

Update HumanResources.Department
Set Name = 'Sales Group'
Where Name = 'Sales'

HumanResources.Department Table의 Name열이 'Sales'인것을 'Sales Group'으로 변경합니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] 예외처리(try ~ catch)  (0) 2010.09.06
[SQL] Batch처리(일괄처리)의 개념  (0) 2010.09.03
[SQL] Update  (0) 2010.09.01
[SQL] Replicate로 문자열 채워넣기  (0) 2010.08.26
[SQL] Delete  (0) 2010.08.25
[SQL] 임시 Table과 변수 Table  (2) 2010.08.24
0 0
Programming/Microsoft SQL Server
Replicate함수를 이용하면 지정된 횟수만큼 특정 문자열을 채울 수 있습니다.

Select Replicate('a', 3);
Select Replicate('b', 6);

'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] Batch처리(일괄처리)의 개념  (0) 2010.09.03
[SQL] Update  (0) 2010.09.01
[SQL] Replicate로 문자열 채워넣기  (0) 2010.08.26
[SQL] Delete  (0) 2010.08.25
[SQL] 임시 Table과 변수 Table  (2) 2010.08.24
[SQL] 구성함수(전역변수)  (0) 2010.08.20
0 0
Programming/Microsoft SQL Server
Table의 Data를 삭제하기 위해서는 Delete구문을 사용합니다. Delete는 사용방법에 관해서는 Select나 Insert, Update보다 훨씬 쉬운 DML구문에 해당합니다.
그 만큼 너무나도 삭제가 간단하므로 Delete문을 사용하기전 Transaction을 걸어놓는등의 주의가 꼭 필요합니다.

참고:
(1) DML(Data Manipulation Language) : Table의 Data를 조회하거나, 변경, 삭제 하는 SQL구문
(2) Transaction : Table의 Data를 변경하거나 혹은 삭제했을때 그것을 당장 반영하지 않고 대기상태에 머물게 합니다. 이 후 따로 확인명령이 있어야지만 변경된 작업을 반영하는 Data관리 개념입니다.

1. 테이블 전체 행 지우기

Delete From HumanResources.EmployeeDepartmentHistory

HumanResources.EmployeeDepartmentHistory Table의 모든 행을 삭제합니다.



2. 원하는 행만 삭제하기

Delete문도 Select나 Update처럼 Where조건을 지정해 원하는 행만 삭제할 수 있습니다.

Delete From HumanResources.EmployeePayHistory
Where BusinessEntityID = 290

HumanResources.EmployeePayHistory Table에서 BusinessEntityID가 290인 행을 삭제합니다.


3. 중복행 삭제하기

Table에 중복되는 Data가 많을 경우 중복부분을 삭제해야 하는 경우가 종종 발생하곤 합니다. 이때는 Select문에서 보았던 Top절을 이용해 삭제하는 방법을 많이 쓰고 있습니다.

먼저 중복행 삭제를 위해 간단한 Table 하나를 살펴보겠습니다.

Select *
From Sales.SalesTerritoryHistory
Where TerritoryID = 1

Sales.SalesTerritoryHistory Table에서 TerritoryID가 1인것만 모두 조회합니다.


이중에서 TerritoryID가 1인것 하나만 남겨두고 상위 2개는 모두 삭제해 보겠습니다.

Delete Top(2) Sales.SalesTerritoryHistory
Where TerritoryID = 1

Sales.SalesTerritoryHistory Table에서 상위 TerritoryID가 1인것 2행을 삭제합니다.


Delete는 테이블에 특정한 처리를 행하지 않기 때문에 비교적 빠른 속도로 Data를 삭제하지만 이것도 너무 많은 경우 그 만큼 시간이 오래 걸릴 수 있습니다. 삭제하면서 트랜잭션 로그를 기록하기 때문인데요.

이럴때는 Truncate 구문을 사용하면 시간을 단축 시킬 수 있습니다. 예를 들어 대상 Table이 Sales.SalesTerritoryHistory일 경우 다음과 같이 작성하면 됩니다.

Truncate Table Sales.SalesTerritoryHistory

Truncate는 Data삭제할 때 실제 Data를 지우지 않고 IAM(Index Allocation Map)의 정보만을 삭제하며 트랜잭션 로그를 생성하지 않기에 삭제시간을 단축시킬 수 있는 것입니다. 이것은 마치 책의 실제내용이 아닌 색인정보만을 지워서 실제 책안에 내용이 아무것도 없는것처럼 하는것과 처리가 비슷하다고 보시면 됩니다.


'Programming > Microsoft SQL Server' 카테고리의 다른 글

[SQL] Update  (0) 2010.09.01
[SQL] Replicate로 문자열 채워넣기  (0) 2010.08.26
[SQL] Delete  (0) 2010.08.25
[SQL] 임시 Table과 변수 Table  (2) 2010.08.24
[SQL] 구성함수(전역변수)  (0) 2010.08.20
[SQL] 트랜잭션(Transaction)  (0) 2010.08.19
0 0
1 2
블로그 이미지

클리엘