수안이의 컴퓨터 연구실

마이크로소프트에서 SQL Server 2008을 출시하였습니다.
SQL Server 2000 부터 많은 활용을 하고 있는 저로서는 반가운 소식이네요.
특징을 대략 살펴보면 다음과 같습니다.

• 동시 작업 자원 관리 향상
• 기업 전체에 일관된 관리 프레임워크 사용
• 데이터 압축과 sparse 컬럼 사용으로 스토리지에 대한 요구를 줄이고, 쿼리 성능 성능
• 민감한 데이터를 보호하는 데이터 암호화 및 고급 감시 기능
• 2008 인스턴스를 모니터하여 기업 전반에 SQL 서버의 성능 분석
• 높은 성능 분석 솔루션을 구축하여 확장성, 성능, 데이터마이닝, SQL 서버에서 사용자 인터페이스 개선 분석 서비스
• 향상된 성능, 유용성, 시각화, Microsoft Office suites 2007과 Microsoft SQL Server Reporting 서비스가 통합
• 통합하는 위치에 대한 응용 프로그램에 대해 데이터를 공간적으로 제공
  

Microsoft SQL Server 2008 관련 문서 링크

• Microsoft SQL Server 2008 Your Data Any Place, Any Time
• Microsoft SQL Server 2008 Data Warehousing
• Microsoft SQL Server 2008 Product Overview

기본적인 것 뿐만 아니라 DW(Data Warehouse), DM(Data Mining) 관련 기능도 빨리 확인해 보고 싶네요.

http://www.devarticles.com/c/a/sql-serv ··· oring%2F

SQL Server Hardware Tuning and Performance Monitoring
(Page 1 of 5 )

In this article, Sayed will lead the way in presenting you guys with a performance enhancing solution for your system. Discover how to optimise your system by investigating what hardware you really need for your applications. 

The hardware configuration that SQL Server runs on top of can make or break you. How do you know how much hardware is really needed by your applications? How do you know if your applications have grown to the point where they are now overloading your system? 

In this article, I will give you the information you need to solve the specific bottleneck or to resolve future bottlenecking issues. To find out your hardware bottleneck is to use Performance Monitor. Using Performance Monitor, we can identify issues in each of a set of performance objects.

In the following lines we will see what the best way for hardware tuning and performance monitoring is:

I would like to say that the subject of SQL server performance tuning is big and complex. So, I will try to explain today SQL server hardware tuning and performance monitoring.

If you need more covering you can visit "The God Father", of course I mean SQL-server-performance website maintained by Brad.  Because it has a lot of valuable information about SQL Server performance tuning subject. At the end of this article I will give you resources to help you and to understand more about this important topic.

SQL Server Hardware Tuning and Performance Monitoring - Performance Monitoring Issues
(Page 2 of 5 )

Memory Performance Monitoring

Now that you know the memory limitations in SQL Server imposed by the operating system, what can you do to optimise that memory and determine if you need more? This is where we get into what is popularly called The Art of Performance Monitoring .

Due to the dynamic nature of the memory in SQL Server 7.0 and SQL server 2000, Microsoft removed support for one of the most useful tools available in SQL Server 6.5, DBCC MEMUSAGE. Now, to get this information, you have to monitor a variety of performance counters in the Buffer Manager object and the Cache Manager object since the information is no longer statically mapped in a table like sysconfigures.

While this handy tool is listed as Unsupported  now and no longer returns the breakdown of memory usage, try running it. You will see that this unsupported snap shot continues to return the top 20 list of buffered tables and indexes. This can be a very handy list. When analysing performance of an individual application un a test environment, it can be invaluable.

The Art of Performance Monitoring

Performance monitoring is an art, in that it is a combination of talent, experience, knowledge, and sometimes just plain luck. How do you know if you can do it? You have to try, try, and try again. Keep at it, read up on it. Keep a performance monitor continually open against your production server. Here are some good guidelines to get you started.

1. Make sure that you are running your typical processes (SQL Server) and  work loads (queries and stored procedures) during your monitoring.

2. Do not just do real-time monitoring of your servers. Capture long running logs. In Windows NT, install the Datalog/Monitor service from the NT Resource Kit; this functionality is available out of the box in Windows 2000.

3. Always have disk counters turned on by running from a command prompt the command DISKPERF Y  and then rebooting. Even in a production environment, the overhead is minimal;  the last thing you want to do in the middle of a crisis where logical and physical disk counters are necessary is to have to reboot.

4. For routine, daily, desktop monitoring, set up the chart windows with an interval of 18 seconds. In both the Windows NT PerMon and the Windows 2000 MMC SysMon, this will give your chart a Window of 30 minutes. For me, this has proven to be the ideal interval for both seeing the past and minimizing the impact on the server.

5. Utilize SQL Profiler for individual queries and processes in coordination with PerMon or SysMon to get a good picture of the impact of individual queries.

6. Know the terminology of Performance Monitoring. Objects are lists of individual statistics available. An example is the Processor object. A counter is a single statistic that falls under the heading of an object. An example is the  per centprocessor Time counter under the Processor object. An instance is further breakdown of a counter statistic into duplicate components. Not all counters will have separate instances. The per centprocessor Time counter has instances for each processor and a _Total instance as a summery of all processor activity.

7. Know your tools. While you may know how to set up a chart in PerMon, learn how to set up a log with Datalog or Performance Log. Other tools to be familiar with are DBCC MEMUSAGE, Task Manager, and SQL Enterprise Manager Current Activity.

8. Do not be afraid to experiment. The BackOffice Resource Kit has tools in it for creating test data (DataSim), creating test databases (DBGen), and simulating loading from multiple clients (SqlLS).

SQL Performance Monitoring and Bottlenecks

Bottlenecks occur when the hardware resources cannot keep up with the demands of the software. For example, when a software process or combination of processes, wants more I/O from a disk than the disk can physically deliver, a bottleneck occurs at the disk. When the CPU subsystem becomes too saturated and processes are waiting, a bottleneck has occurred. Bottlenecks are usually fixed in one of two ways.

The first is to identify the limiting hardware and increase its capabilities. In other words, get a faster hard driver or increase the speed of the CPU. The second way is to make the software processes utilize the hardware more efficiently. This could be done by putting an index on a table so that either the disk I/O necessary to service a query is reduced, or the CPU units necessary to process a join are lessened.

The following are five key areas to monitor when tracking server performance and identifying bottlenecks. Each bottleneck candidate will have varied performance monitoring objects and couners to consider.

• Memory usage SQL Server needs, relative to itself and to the operating system memory. If SQL Server has enough memory but the operating  system is starved of memory such that it has to frequently swap through the pagefile to disk, overall performance will suffer dramatically.
• CPU processor utilization High CPU utilization rates indicate the CPU subsystem is underpowered. Solutions could be upgrading the CPU or increasing the number of processors.
• Disk I/O performance Failure of the disk or disk controller to satisfy read or write requirements in a timely manner impacts performance.
• User connections. Improperly setting the number of user connections could rob memory otherwise available to SQL Server.
• Blocking Locks One process keeps another process from accessing or updating data. This is particularly noticeable to users and is the cause of some of your most server performance problems from a user perspective.

SQL Server Hardware Tuning and Performance Monitoring - Memory Tuning Issues
(Page 3 of 5 )

Memory Tuning: The Operating System and SQL Server

Start your intensive analysis of memory by looking at two counters,

• Memory: Available Bytes
• Memory: Pages Faults/sec

The Available Bytes counter tells how much memory is available for use by processes.

The Pages Faults/sec counter tells us the number of hard page faults, pages which had to be retrieved from the hard disk since they were not in working memory. It also includes the number of pages written to the hard disk to free space in the working set to support a hard page fault.

A low number for Available Bytes indicates that there may not be enough memory available or processes, including SQL Server, may not be releasing memory. A high number of  Pages Faults/sec indicate excessive paging. Further looks at individual instances of  process:Page Faults/sec to see if the SQL Server process, for example, has excessive paging. A low rate of  Pages Faults/sec (commonly 5-10 per second) is normal, as the operating system will continue to do some house keeping on the working set.

As previously noted, starting with SQL Server 7, memory is auto-tuning by default. In general though, you want to give SQL as much dedicated memory as possible. This is mostly dependent on what other application may be running on the server. By using the sp_configure stored procedure, you can set the values to MIN SERVER MEMORY and MAX SERVER MEMORY to dedicated values.

If SQL Server is the only application on the server, MIN SERVER MEMORY and MAX SERVER MEMORY to the same value. If SQL Server co-exists with one or more applications, lower the MIN SERVER MEMORY setting to count for the memory demands of the other application(s). If the other application fails to start in a timely manner, it may be because SQL Server has been operating at or near the MAX SERVER MEMORY setting and is slow in releasing memory to the new, and now starved, application. In this instance, lower the value of MAX SERVER MEMORY. Obviously, MAX SERVER MEMORY always needs to be greater than or equal to MIN SERVER MEMORY.

NOTE: If you have installed and are running the Full-Text Search support (Microsoft Search service, also known as MSSearch), then you must set the max server memory option manually to leave enough memory for the MSSearch service to run. Microsoft supplies a handy formula here:

Total Virtual Memory (SQL Server MAX + Virtual Memory for Other Processes) = 1.5 * Server Physical Memory.   

Once we have tuned the SQL Server memory settings, it is a good idea to decide if you want SQL Server 7/2000 to tune the process memory automatically or have values set for the configuration. For better performance, you can lock the amount of working set memory that SQL Server reserves. The trade-off here is that you may receive out of memory messages from other applications on the same server.

If you do decide to fix the amount of working set memory, two configuration settings are necessary. First, equalize the MIN SERVER MEMORY and MAX SERVER MEMORY settings. Then turn on the SET WORKING SET SIZE configuration flag using sp_configure. MAX SERVER MEMORY should generally not exceed the RAM available for the server.

SQL Server Process Memory Tuning

Once you have gotten the overall OS and SQL server memory tuned, look further at the SQL Server memory usage. Four counters are desirable here:

• process: Working Set:sqlserver
• SQL Server: Buffer Manager: Buffer Cach Hit Ratio
• SQL Server: Buffer Manager: Free Buffers
• SQL Server: Memory Manager: Total Server Memory (KB)

The process: Working Set:sqlserver instance shows the amount of memory that SQL Server is using. If the number is consistently lower than the amount SQL Server is configured to muse by the MIN SERVER MEMORY and MAX SERVER MEMORY  options, then SQL Server is configured for too much memory. Otherwise, you may need to increase RAM and  MAX SERVER MEMORY.

Buffer Cach Hit Ratio  should  be consistently greater than 90. This indicates that the data cach supplied 90 per cent of the requests for data. If this value is consistently low, it is a very good indicator that more memory is needed by SQL Server. If Available Bytes is low, this means that we need to add more RAM.

When Free Buffers is low, this means that there is not enough RAM to maintain a consistent amount of data cach. It too, is indicative of a need for more memory.

If Total Server Memory for SQL Server is consistently higher than the overall server memory, it indicates that there is not enough RAM.

SQL Server Hardware Tuning and Performance Monitoring - More Performance Monitoring Issues
(Page 4 of 5 )

CPU Performance Monitoring

In CPU performance monitoring, we are going to be using several counters:

• Processor: Per cent Processor Time
• Processor: Per cent Privileged Time
• Processor: Per cent User Time
• System: Per cent Total Processor Time

Generally, CPU performance monitoring is straightforward. You need to start by monitoring Processor: per cent Processor Time. If you have more than one processor, you should monitor each instance of this counter and also monitor System: per cent Total Processor Time to determine the average for all processors.

Utilization rates consistently above 80-90 per cent may indicate a poorly tuned or designed application. On the other hand, if you have put all the other recommendations of this book into use, they may indicate a need for a more powerful CPU subsystem. In general, I would spend a little bit of time analysing the applications before immediately going out and buying three more processors.

Spending this time experimenting to discover CPU performance problems and correcting them through software improvements will often keep you from just spending money on a more powerful CPU that only covers up poorly written software for little or no time.

If you do see high CPU utilization, you will then want to monitor Processor: Per cent Privileged Time. This is the time spent performing kernel level operations, such as disk I/O. If his counter is consistently above 80-90 per cent and corresponds to high disk performance counters, you may have a disk bottleneck rather than a CPU bottleneck.

What about SQL Server? Processor: Per cent User Time measures the amount of processor time consumed by non-kernel level applications. SQL is such an application. If this is high and you have multiple processes running on a server, you may want to delve further by looking at specific process instances through the instances of the counter Process: Per cent User Time. This can be very useful for occasions such as when our operating system engineers installed new anti-virus software on all our servers. It temporarily brought them to their knees until we were able to determine the culprit through analysing Process:Per cent User Time for the anti-virus software instance.

Disk Tuning and Performance Monitoring

Begin disk performance monitoring by looking at the following counters:

• PhysicalDisk: Per cent Disk Time
• PhysicalDisk: Current Disk Queue Length
• PhysicalDisk: Avg. Disk Queue Length

Applications and systems that are I/O-bound may keep the disk constantly active. This is called disk thrashing.

You should always know how many channels, what types of arrays, how many disks are in each array, and which array/channel your data and transaction logs are located on before you start thinking about disk performance tuning.

The PhysicalDisk: Per cent Disk Time counter monitors the percentage of time that the disk is conducting check the PhysicalDisk: Current Disk Queue Length counter to see the number of requests that are queued up waiting for disk access.

It is important at this point to be familiar with your disk subsystem. If the number of waiting I/O requests is a sustained value more than 1.5 to 2 times the number of spindles making up the physical disk, you have a disk bottleneck. For example, a RAID 5 configuration with seven spindles/disks would be a candidate for disk performance tuning should the Current Disk Queue Length continually rest above 12-14.

To improve performance in this situation, consider adding faster disk drives, moving some processes to an additional controller-disk subsystem, or adding additional disks to a RAID 5 array.

Most disks have one spindle, although RAID devices usually have more. A hardware RAID 5 device appears as one physical disk in Windows NT PerMon or Windows 2000 sysMon. RAID devices created through software appear as multiple instances.

WARNING: The Per cent Disk Time counter can indicate a value greater than 100 per cent if you are using a hardware based RAID configuration. If it does, use the PhysicalDisk: Avg. Disk Queue Length counter to determine the average number of system requests waiting for disk access. Again, this is indicative of a performance problem if a sustained value of 1.5 to 2 times the number of spindles in the array is observed.

SQL Server Hardware Tuning and Performance Monitoring - SQL Server Performance Tuning Resources, Websites and Articles
(Page 5 of 5 )

sql-server-performance.com: A website dedicated to SQL Server Performance Tuning. Contains a lot of information to cover performance tuning. A very valuable site :)

Communities & Newsgroups

http://www.microsoft.com/technet/itcommunity/chats/trans/sql/sql0513.asp
Review expert answers to user questions with this chat regarding getting the most from SQL Server.

http://www.microsoft.com/technet/itcommunity/chats/trans/sql/pass1124.asp
this chat was hosted by Neil MVP/MCSE, Protech Computing Ltd., as well as several MVPs and members of the Microsoft SQL Server team.

http://support.microsoft.com/default.aspx?scid=kb;en-us;322883 
The performance tuning guide that this article references provides principles and techniques to help database administrators configure SQL Server 7.0 for maximum performance and to help them determine the cause of poor performance in a SQL Server.

http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
To troubleshoot performance issues, you must complete a series of steps to isolate and determine the cause of the problem. Possible causes include: Blocking System resource contention Application design problems Queries or stored procedures that have...

http://support.microsoft.com/default.aspx?scid=kb;en-us;319942
This article describes the following configuration settings and considerations for their use: Affinity Mask Lightweight Pooling Max Async IO Max Worker Threads Memory Priority Boost Set Working Set Size SQL Server can obtain a very high level of...

Support WebCast

http://support.microsoft.com/default.aspx?scid=kb;en-us;324885
Session Summary During this session, we will show a practical approach to analyze performance tuning problems that involve Microsoft SQL Server lock monitoring data. We will briefly discuss sp_who2, sysprocesses, and syslocks. You should have a firm...

SQL Server 2000 Performance Tuning Books

1. Microsoft SQL Server 2000 Performance Tuning Technical Reference
2. The SQL Server 2000 Performance Optimization and Tuning Handbook

Ron Talmage

오늘날의 RDBMS 아키텍처에서 교착 상태는 피할 수 없으며 고용량의 OLTP 환경에서 흔히 나타납니다. 하지만 .NET 공용 언어 런타임 덕분에 SQL Server 2005는 개발자에게 새로운 오류 처리 방법을 제공하고 있습니다. 이번 달 칼럼에서 Ron Talmage는 TRY/CATCH를 사용하여 교착 상태를 해결하는 방법을 보여줍니다.

T-SQL은 메시지 전달에는 뛰어나지만 오류 차단 도구를 제공하는 데는 그다지 뛰어나지 않습니다. 제 생각에 거의 모든 DBA는 다음과 같은 1205 "deadlock victim" 오류 메시지에 익숙할 것입니다.

Transaction (Process ID 52) was deadlocked on lock 
resources with another process and has been chosen as 
the deadlock victim. Rerun the transaction.

코드에서 교착 상태 상태 발생한 경우, 저장 프로시저에서 응용 프로그램이 얼마나 깊이
중첩되어 있는지는 중요하지 않습니다.
교착 상태에서 처리되지 않은 spid의 자체 일괄 처리를 중단하고 1205 오류가
클라이언트에 반환됩니다.

트랜잭션을 다시 시도하라는 오류 메시지가 나타나지만 T-SQL 코드 내에서는 그럴 수
없습니다. 이 다시 시도 동작은 호출 응용 프로그램에서 수행되어야 합니다.
이 오류는 쉽게 포착되지 않으며 @@ERROR가 소용 없습니다.
그다지 좋은 상황은 아닙니다.

교착 상태 예제SQL Server 2000 및 2005에서 교착 상태을 유발하는 예제에 대해 먼저 살펴보겠습니다. 이 기사에서 저는 SQL Server 2005의 최신 CTP(Community Technology Preview)를 사용했지만 SQL Server 2005 베타 2(지난 7월에 출시)를 사용해도 괜찮습니다. 베타 2 또는 최신 CTP 중 하나에 액세스하지 못하는 경우 최신 버전의 SQL Server 2005 Express를 다운로드하여 시험할 수 있습니다.

발생할 수 있는 교착 상태의 종류는 다양하지만 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp (영문) 기사 및 교착 상태 기사 트리의 이어지는 기사 참조] 가장 흥미롭고도 어려운 교착 상태 중 하나는 판독기(reader)와 작성기(writer)가 서로를 차단하는 경우입니다. 다음 코드는 Pubs 데이터베이스에서의 이러한 교착 상태를 보여줍니다. (SQL Server 2000의 두 쿼리 분석기 창이나 SQL Server 2005의 두 Management Studio 쿼리에서 이 코드를 나란히 실행할 수 있습니다.) 첫 번째 창에서 코드 본문 앞에 다음 코드를 넣습니다.

-- Window 1 header
DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = '111-11-1111', @au_lname = 'test1'

두 번째 연결이 있는 두 번째 창에서 다음 코드를 사용합니다.

-- Window 2 header
DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = '111-11-1112', @au_lname = 'test2'

그런 다음 두 창에서 다음 코드를 코드 본문으로 사용합니다.

-- Body for both connections:
BEGIN TRANSACTION
INSERT Authors VALUES 
  (@au_id, @au_lname, '', '', '', '', '', '11111', 0)
WAITFOR DELAY '00:00:05'
SELECT *
  FROM authors
  WHERE au_lname LIKE 'Test%'
COMMIT

이 ID의 데이터가 Authors 테이블에 없는지 확인하기 위해 세 번째 창에서 다음 코드를 실행합니다.

DELETE FROM authors WHERE au_id = '111-11-1111'
DELETE FROM authors WHERE au_id = '111-11-1112'

이제 서로 5초 이내에 Window 1과 Window 2를 실행합니다. 각 연결이 INSERT를 종료합니다. 그리고 각각 5초를 기다린 후에 SELECT 문을 생성하므로 SELECT가 생성되기 전에 두 INSERT를 완료할 수 있습니다. 각 SELECT 문은 LIKE 'Test%'인 au_lname 값을 가지는 모든 데이터를 Authors 테이블에서 읽으려고 시도합니다. 따라서 각 SELECT 문은 자체 연결의 삽입된 데이터뿐만 아니라 다른 연결의 삽입된 데이터를 읽으려고 시도합니다.

READ COMMITTED 격리 수준은 SELECT 문이 커밋되지 않은 데이터를 읽지 않도록 하기 위해 공유 잠금을 생성합니다. 공유 잠금은 동일한 리소스상의 단독 잠금과 호환되지 않습니다. 그리고 요청자가 공유 잠금을 생성할 수 있으려면 단독 잠금이 해제될 때까지 기다려야 합니다. 각 연결의 삽입된 데이터에는 이미 단독 잠금이 있으므로 두 연결 간의 삽입된 데이터를 읽으려고 시도하는 SELECT 문이 공유 잠금을 획득하려고 시도하고 차단됩니다. 두 연결이 서로를 상호 차단하여 교착 상태를 유발합니다. SQL Server의 잠금 관리자는 교착 상태를 감지하고 배치 중 하나를 중단하고 트랜잭션을 롤백하여 차단 잠금을 해제하므로 다른 트랜잭션이 완료될 수 있습니다. 교착 상태 피해자의 트랜잭션이 롤백되고 다른 트랜잭션이 계속됩니다.

TRY/CATCH 사용 방법이제 코드 본문을 수정하고 TRY/CATCH를 사용해 보겠습니다. (이 예제에서는 SQL Server 2005 버전에서 코드를 실행해야 합니다.) TRY/CATCH를 사용할 경우 작업 코드와 오류 처리 코드를 분리해야 합니다. 작업 코드는 TRY 블록에 넣고 오류 처리 코드는 CATCH 블록에 넣습니다. TRY 블록의 코드가 실패하면 CATCH 블록으로 점프하여 코드가 실행됩니다. 이 동작은 전체 배치가 실행되는 것을 막는 오류(예: 개체 없음)를 제외한 모든 오류에서 유효합니다.

다음은 위와 동일한 코드를 사용하는 TRY/CATCH의 예제입니다. 동일한 헤더를 사용하고 코드 본문은 변경합니다.

BEGIN TRANSACTION
BEGIN TRY
  INSERT Authors VALUES 
  (@au_id, @au_lname, '', '', '', '', '', '11111', 0)
  WAITFOR DELAY '00:00:05'
  SELECT COUNT(*)  FROM Authors 
  COMMIT
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber
  ROLLBACK
END CATCH;
SELECT @@TRANCOUNT AS '@@Trancount'

이제 SQL Server 2005에 나란히 연결된 창에서 이 코드를 실행하면 삽입을 막는 특정 데이터가 Authors 테이블에서 삭제되었는지 확인할 수 있습니다. 선행 DELETE 문을 사용할 수 있습니다.

두 창이 @@TRANCOUNT 수준 0을 반환합니다. 교착 상태는 여전히 발생하지만 이제 TRY/CATCH가 이 교착 상태를 격리합니다. 더 이상 피해자의 일괄 처리가 중단되지 않으며 교착 상태 피해자의 출력에 다음과 같은 오류를 볼 수 있습니다.

ErrorNumber
-----------
1205

@@Trancount
-----------
0

이제 TRY/CATCH가 제공하는 성능을 알 수 있을 것입니다. CATCH 블록에서 교착 상태 오류까지도 격리할 수 있기 때문에 더 이상 일괄 처리가 중단되지 않으며 T-SQL 코드가 계속해서 실행될 수 있습니다. 교착 상태 피해자를 위해 교착 상태 오류 1205는 T-SQL 코드를 CATCH 블록에 넣습니다. 이 블록에서 새로운 오류 처리 기능을 사용하여 코드를 탐색할 수 있습니다. 선행 코드에서는 @@ERROR 대신 ERROR_NUMBER()만을 사용하지만 ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_SEVERITY() 및 ERROR_STATE()를 사용할 수도 있습니다. 이 기능은 쉬우면서도 이전의 기능보다 훨씬 뛰어난 성능을 제공합니다.

참고로 선행 CATCH 블록 코드에는 ROLLBACK이 포함되는데 그 이유는 교착 상태 오류가 격리되더라도 트랜잭션이 롤백되지 않기 때문입니다. 트랜잭션은 여전히 불안하지만 이제 TRY/CATCH 내에서 이 트랜잭션을 롤백하는 것이 여러분의 책임입니다. 그렇다면 무슨 차이가 있습니까? 비록 트랜잭션을 계속할 수는 없더라도 다시 시도할 수는 있다는 것입니다.

TRY/CATCH에서 다시 시도SQL Server 2000의 T-SQL에서 1205 오류는 "트랜잭션을 다시 실행"하라는 암시입니다. 문제는 트랜잭션을 다시 실행할 수 없다는 것입니다. 적어도 SQL Server 2000의 T-SQL에서는 그렇습니다. 하지만 SQL Server 2005의 TRY/CATCH를 사용하여 교착 상태 오류를 격리할 수 있으므로 이제 트랜잭션을 다시 시도할 수 있습니다.

다음 코드 본문은 다시 시도를 실행하기 위한 한 방법을 보여줍니다. 위에 나타난 동일한 헤더와 함께 이 코드를 다시 사용하십시오.

DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
    INSERT Authors VALUES 
      (@au_id, @au_lname, '', '', '', '', '', 
'11111', 0)
    WAITFOR DELAY '00:00:05'
    SELECT * FROM authors WHERE au_lname LIKE 'Test%'
    COMMIT
    BREAK
  END TRY
  BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
    ROLLBACK
    SET @Tries = @Tries + 1
    CONTINUE
  END CATCH;
END

이 코드가 수행하는 동작은 WHILE 루프를 사용하여 다시 시도를 추가하는 것입니다. 저는 다시 시도 횟수를 세 번으로 설정했지만 변경이 가능합니다. 요컨대 이전에는 불가능했지만 이제는 T-SQL 내에서 교착 상태 피해자의 코드를 다시 시도할 수 있습니다.

하지만 전체 트랜잭션은 WHILE 루프 외부가 아니라 내부에 있다는 점에 주의하십시오. 따라서 각 루프 내에서 트랜잭션이 시작할 뿐만 아니라 COMMIT(TRY 블록이 실행되는 경우) 또는 ROLLBACK(CATCH 블록이 실행되는 경우)으로 끝납니다. TRY 블록은 BREAK 명령으로 끝나며 이 TRY 블록이 성공한 경우 WHILE 루프가 종료됩니다. CATCH 블록은 다시 시도 카운터를 증가시키며, WHILE 루프를 다시 실행하는 CONTINUE 명령으로 끝납니다. 1205 오류와 마찬가지로 실제로 트랜잭션을 다시 시도하는 코드가 있습니다. 하지만 이제 T-SQL 내에서 다시 시도가 완벽하게 수행됩니다.

SQL Server 2005에는 교착 상태를 해결하기 위한 다른 방법이 있습니다. 예를 들어, SNAPSHOT ISOLATION 수준과 READ COMMITTED용의 새로운 옵션(READ COMMITTED SNAPSHOT)이 있습니다. 하지만 이제 SQL Server 2005에서 트랜잭션을 코딩하고 교착 상태 오류를 격리한 후 트랜잭션을 다시 시도할 수 있기 때문에 훨씬 더 강력해진 도구를 마음대로 사용할 수 있습니다.

Goetz Graefe
Microsoft, Redmond, WA 98052-6399
GoetzG@Microsoft.com

Abstract
Microsoft SQL Server was successful for many years for transaction processing and decision support workloads with neither merge join nor hash join, relying entirely on nested loops and index nested loops join. How much difference do additional join algorithms really make, and how much system performance do they actually add? In a pure OLTP workload that requires only record-to-record navigation, intuition agrees that index nested loops join is sufficient. For a DSS workload, however, the question is much more complex. To answer this question, we have analyzed TPC-D query performance using an internal build of SQL Server with merge-join and hash-join enabled and disabled. It shows that merge join and hash join are both required to achieve the best performance for decision support workloads.

Abstract: This document describes how the Microsoft® SQL Server™ 2000 query processor interacts with an OLE DB provider to enable distributed and heterogeneous queries. It is intended primarily for OLE DB provider developers, and assumes a solid understanding of the OLE DB specification.

조금 오래된 문서이긴 하지만, 정리가 잘 되어있는 워드 문서이다.

Shankar Pal, Babu Krishnaswamy, Vasili Zolotov, Leo Giakoumakis _ Microsoft Corporation


적용 대상:
SQL Server 2005

요약: 이 문서에서는 Microsoft SQL Server 2005에서 XML 데이터 형식의 쿼리 및 데이터 수정 성능을 향상시키기 위한 몇 가지 방법을 설명합니다. 이 문서를 최대한 활용하기 위해서는 SQL Server 2005의 XML 기능에 익숙해야 합니다. 배경 자료로 MSDN 기사 Microsoft SQL Server 2005에서 XML 지원 및 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법을 참조하십시오.


소개

엔터프라이즈 응용 프로그램은 반구조적 데이터 및 구조화되지 않은 데이터 모델링에 XML을 점점 더 많이 사용하고 있습니다. Microsoft SQL Server 2005는 이러한 응용 프로그램 개발을 돕기 위해 XML 데이터 처리를 위한 광범위한 지원을 제공합니다. XML 데이터는 XML 데이터 형식 열에 원시적으로 저장되어, XML 스키마의 모음에 따라 형식화되거나 형식화되지 않은 상태로 남아 있을 수 있습니다. 그리고 Last Call 및 XML 데이터 조작 언어에서 현재 부상하고 있는 W3C 권장 사항인 XQuery를 통해 세분화된 데이터 조작이 지원됩니다. 쿼리 성능을 향상시키기 위해 XML 열을 인덱스할 수 있습니다. 엔터프라이즈 응용 프로그램은 반구조적 데이터 및 구조화되지 않은 데이터를 모델링하기 위해 점점 더 많이 XML을 사용하고 있으므로 SQL Server 2005의 XML 지원을 통해 이점을 얻게 될 것입니다.

이 문서에서는 XML 데이터 형식을 사용하는 응용 프로그램의 저장소, 쿼리 및 데이터 수정을 최적화하기 위한 제안을 제공합니다. 이러한 제안은 코드 샘플을 통해 설명됩니다. XML 데이터 모델링 및 사용을 위한 최상의 실행 방법에 관한 설명은 관련 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법을 참조하십시오. 또한 매핑을 사용하는 XML 뷰 기술의 최적화에 관한 내용은 MSDN Library에서 SQLXML 성능 최적화 (영문)를 참조하십시오.

이 문서에서는 우선, 데이터베이스 설계 원리를 포함하여 XML을 사용한 데이터 모델링 지침을 살펴본 다음 응용 프로그램의 성능 최적화를 위한 쿼리 및 데이터 수정 지침을 제공합니다.


XML 데이터 형식을 이용한 데이터 모델링

XML 데이터 형식은 기업 내의 반구조적 데이터 및 구조화되지 않은 데이터에 데이터 모델링 기능을 제공합니다. XML 저장소 및 쿼리 처리의 성능은 데이터베이스 스키마 설계에 좌우되며 XML 열에서 XML 데이터 및 속성 승격의 구조 및 단위와 같은 요소를 포함합니다.

맨 처음 내려야 할 결정은 응용 프로그램에 XML 데이터 모델의 기능이 필요한지 여부입니다. 구조화된 데이터는 관계형으로 가장 잘 모델링되고 관계형 열을 가진 테이블에 저장됩니다. 문서 순서 및 포함 계층을 유지해야 하고 재귀 구조를 가진 반구조적 데이터 또는 태그 데이터를 사용하는 경우 XML 데이터 모델이 가장 적합합니다.

XML 데이터 형식 열에 구조화된 데이터를 저장하면 유용한 경우가 종종 있는데, 예를 들면 데이터가 유연한 구조를 갖거나 구조가 사전에 알려져 있지 않은 경우입니다.

이런 시나리오는 개체에 대한 메타 데이터 정보가 XML로 모델링되고 XML 데이터 형식 열에 저장되는 경우 속성 관리에서 발생합니다. 다른 구조 및 콘텐츠 모델을 사용하는 서로 다른 형식의 개체 속성이 같은 XML 열에 저장되고 서로 쿼리될 수 있습니다. 가장 자주 쿼리되는 속성은 동일한 테이블 또는 다른 테이블의 열로 승격됩니다. 승격된 속성을 인덱싱하고 쿼리할 수 있으며 쿼리 계획은 XML 열을 쿼리하는 것보다 더 단순합니다.

또는 들어오는 XML 데이터는 테이블로 분해되어 SQL 언어를 사용하여 쿼리될 수 있습니다. XML 생성이 쿼리 작업 부하의 중요한 부분인 경우 XML 데이터 형식 열에 XML 데이터의 중복된 복사본을 저장하는 것이 유용할 수 있습니다. 중복된 복사본은 XML 생성의 런타임 비용을 방지합니다.

XML 데이터 형식을 이용한 데이터 모델링에는 절대적인 규칙이 없습니다. 각 모델링 상황에서 장점과 단점을 신중하게 심사숙고해야 합니다. 그리고 형식화된 XML 열과 형식화되지 않은 XML 열 중에서의 선택 및 XML 태그가 데이터에 도입되는 방법도 마찬가지로 중요합니다. 이 섹션의 나머지 부분에 이에 대한 고려 사항과 기타 고려 사항이 논의되어 있습니다.


XML 데이터의 구조

동일한 데이터를 서로 다른 방식(예: 요소 중심, 특성 중심, 요소와 속성의 결합)으로 표시할 수 있습니다. 이 선택은 콘텐츠를 구성하는 것(요소 값) 및 메타 정보를 구성하는 것(특성 값)의 인식, 그리고 태그의 카디널리티(예: 요소의 다중 발생)에 의해 결정됩니다. 한 가지 방식으로 반구조적 데이터 및 구조화되지 않은 데이터에 XML 태그를 도입하는 것은 다른 저장 및 쿼리 처리 방법에 비해 더 효과적일 수 있습니다.


구체적인 태그 사용

일반적인 요소 이름을 사용하고 추가 특성을 이용하여 서로 다른 요소 형식들을 구별하는 것이 편리한 경우가 종종 있습니다. 하지만 이 방법을 이용하면 XML 인덱스 조회를 효율적으로 수행할 수 없기 때문에 이 방법은 쿼리에서 잘 작동하지 않습니다. (XML 인덱싱에 관한 자세한 설명은 XML 데이터 인덱싱을 참조하십시오.)

한편 의미적으로 풍부한 구체적인 요소 이름은 더 읽기 쉽고 더 효과적인 쿼리 계획을 생성하는 데 도움이 되는 태그를 제공합니다. 반면 매우 장황한 태그는 저장소 비용도 증가시킵니다. 다음 예제는 이 두 가지 측면을 보여줍니다.


예제: 일반적 태그와 구체적 태그의 비교

도서 및 DVD 정보에 XML 태그를 사용하기를 원한다고 가정해 봅시다. 한 가지 선택은, 두 가지 항목 형식을 구별하기 위해 두 개의 값 "book"과 "DVD" 중 하나를 갖는 @type 특성을 가진 <item>이라는 일반 요소를 사용하는 것입니다. 도서 및 DVD는 다음과 같이 표시될 수 있습니다.

<item type="book"><title>Writing Secure Code</title></item> <item type="DVD"><title>The Godfather</title></item>

도서 및 DVD에 대한 경로 식은 각각 /item[@type = "book"] 및 /item[@type = "DVD"]로 작성될 수 있습니다. 한편 다음과 같이 <book> 및 <DVD> 는 더 직접적인 XML 태그입니다.

<book><title>Writing Secure Code</title></book> <DVD><title>The Godfather</title></DVD>


이 표시는 더 간단한 경로 식 /book 및 /DVD를 제공합니다. 또한 @type 특성에서 술어가 제거되기 때문에 쿼리 계획도 더 간단하고 효과적입니다. 게다가 이 표시를 이용하면 형식화되지 않은 XML의 경우에, 주 XML 인덱스(XML 데이터 인덱싱에 관한 자세한 내용은 XML 데이터 인덱싱 참조)에 있는 행의 수가 4개( <item>, 에 한 행, @type 및 그 값에 한 행, <title> 에 한 행, 제목 값에 한 행)에서 3개( <book> 또는 <DVD>, 에 한 행, <title> 에 한 행, 제목 값에 한 행)로 줄어듭니다. 이로 인해 3개에서 2개 행으로 저장소 오버헤드가 감소되며 이는 상당한 절감입니다.



특성 중심 태그

특성의 값은 형식화된 XML 및 형식화되지 않은 XML 둘 다에 대한 주 XML 인덱스의 단일 행에 특성 태그와 함께 저장됩니다. 비교해 보면, 형식화되지 않은 XML의 단순한 값 요소의 값은 요소 태그와 별개의 행에 저장됩니다. 따라서 형식화되지 않은 XML 내의 특성 값을 사용함으로써 저장소가 더 적게 필요합니다.

더구나 특성의 값이 주 XML 인덱스에 있는 태그로서 동일한 행에서 구해지기 때문에 술어의 평가가 더 효과적이므로, 값의 또 다른 행에 액세스할 필요가 없습니다. 이 내용은 다음 예제에 예시되어 있습니다.


예제: 특성 중심 태그

위 예제에서는 다음과 같이 제목이 요소 대신 특성으로 모델링될 수 있습니다

<book title="Writing Secure Code"/> <DVD title="The Godfather"/>

형식화되지 않은 XML의 경우, 이 방식을 이용하면 주 XML 인덱스에 있는 행의 수를 3개(<book>, <title>에 각각 한 행씩, 제목 값에 한 행)에서 2개(<book>에 한 행, @title 특성에 한 행)로 줄일 수 있습니다. DVD의 경우에도 유사합니다.

경로 식 /DVD[title = "The Godfather"]는 제목이 제목에 대한 요소 중심 태그가 있는 "The Godfather"인 DVD를 찾습니다. 특성 중심 태그의 경우에는 동일한 쿼리가 /DVD[@title = "The Godfather"]로 작성되고 쿼리에 필요한 JOIN 수가 하나 줄어듭니다.


형식화되거나 형식화되지 않은 XML

형식화되지 않은XML(즉, XML 데이터가 XML 스키마에 의해 설명되지 않음)의 요소 및 특성 값은 내부적으로 유니코드 문자열로 저장됩니다. 형식화되지 않은XML에서 작업하는 경우에는 데이터를 적합한 형식으로 변환해야 합니다. 예를 들어, 경로 식 (/book/price)[1] > 19.99 가 평가될 때 <price> 의 문자열 값이 숫자 비교를 위해 십진수로 변환됩니다. 이와 같은 비교가 많으면 비용이 많이 들게 됩니다. XML 스키마가 제공하는 형식 정보는 여러 방식으로 데이터베이스 엔진에 의해 사용됩니다. 삽입 및 업데이트된 XML 데이터는 XML 스키마를 준수하는지 검증된 다음 이진 표시("XML blob")로 저장됩니다. 요소 및 특성 값은 XML 인스턴스 내에 형식화된 값으로 저장됩니다. 이렇게 하면 XML blob이 상응하는 텍스트 형식보다 더 효율적으로 구문 분석될 수 있습니다. 형식화된 값은 XML 인덱스에 저장되며 데이터 변환이 제거될 때마다 인덱스 사용을 허용합니다. 쿼리 컴파일은 형식 정보를 사용하여 XQuery 식 및 데이터 수정 문의 정적 형식 정확성을 검사합니다. 형식 불일치 오류는 컴파일 시에 탐지되며 명시적 형식 캐스트를 사용함으로써 피할 수 있습니다.

또한 형식 추론에 기반한 쿼리 최적화가 수행됩니다(예: <book> 의 <price> 가 xs:decimal 형식인 경우, (/book/price)[1] 의 xs:decimal로의 변환이 제거됩니다). 이는 XML 인덱스 조회에 긍정적인 효과를 줄 수 있습니다. (/book/price)[1] < 19.99와 같은 범위 술어는 VALUE 형식의 보조 XML 인덱스에 범위 스캔을 수행합니다. (XML 데이터 인덱싱에 관한 자세한 내용은 이 문서의 XML 데이터 인덱싱 XML 데이터 인덱싱을 참조하십시오.) 형식화되지 않은 XML에 필요한 데이터 변환은 이러한 범위 스캔을 방지합니다. 또한 XML 스키마가 단일 <price> 요소를 지정하고 각 XML 인스턴스에서 단일 <book> 요소만을 허용하는 경우에는(/book/price)[1]의 서수 [1]이 필요하지 않습니다. 형식화된 XML에는 XML 데이터 삽입 및 수정 동안 유효성 검사가 필요합니다. 유효성 검사의 비용은 결코 사소하지 않으며 스키마 정의의 복잡성 및 XML 데이터에서 발생하는 태그의 수와 같은 요소에 따라 다릅니다.



속성 승격

쿼리 처리 동안 문서 순서 및 포함 계층과 같은 구조 정보가 XML 인스턴스에 보존됩니다. 따라서 쿼리 계획이 복잡해지는 경향이 있습니다. 스칼라 값을 XML 열에서 같은 테이블이나 다른 테이블의 관계형 열로 승격하고 이 열에 대해 직접적으로 쿼리를 작성함으로써 일부 쿼리에 대한 계획이 단순화될 수 있습니다. 승격된 속성은 인덱스할 수 있습니다. 속성 값을 구체화하고 인덱싱하면, 미리 계산된 값이 쿼리 성능을 높이는 방식과 같은 방식으로 XML 열에서 XQuery를 사용할 때보다 성능이 더 좋아집니다.

속성 값을 검색하거나 속성 값을 필터로 사용하여 해당 XML blob을 검색하는 경우 속성 승격은 성능을 향상시킵니다. 후자의 경우 속성 값의 선택성이 중요한 요소입니다.

단일값 속성은 계산 열과 동일한 테이블의 열로 승격할 수 있습니다. 단일값 속성 및 다중 값 속성은 둘 다 다른 테이블의 열로 승격할 수 있고 트리거를 사용하여 유지 관리할 수 있습니다. 아래에 이 두 가지 속성 승격 방법이 설명되어 있습니다.



계산 열 사용

XML 데이터 형식 메서드를 사용하여 스칼라 값을 추출하기 위해 먼저 Transact-SQL 사용자 정의 함수가 생성됩니다. 그런 다음 사용자 정의 함수에서 정의한 계산 열이 테이블에 추가됩니다. 각각의 승격된 속성에 대해 이 두 단계가 반복되고 필요에 따라 관계형 인덱스가 이들 열에 생성됩니다.

XML 열에 대한 XQuery 식을 계산 열을 사용하는 SQL 문으로 다시 작성해야 하며, XML 인스턴스가 일치하는 행에서 검색됩니다. 쿼리 비용을 기준으로 쿼리 최적화 프로그램이 계산 열의 인덱스를 선택합니다. 계산 열은 미리 계산되므로, 승격된 속성은 XML 열을 직접 쿼리하는 것보다 더 빠른 성능을 낳습니다.

열이 SELECT 목록에서만 사용되고 술어 평가에 사용되지 않는 경우 계산 열의 인덱싱을 피할 수 있습니다. 이 경우 성능상의 이점을 위해 계산 열의 지속성만으로도 충분합니다. 계산 열이 인덱스될 때, 계산 열 식이 정확하지 않거나 명확하지 않은 경우 계산 열이 계속 유지되어야 합니다.

아래 예제는 속성 승격을 위한 계산 열의 사용을 보여줍니다.



예제: 속성 승격을 위한 계산 열 사용

ISBN을 계산 열로 승격하는 것이 시간과 노력을 들일 만한 일이 되도록, 지정된 ISBN에 따라 책을 조회하는 일반적인 작업 부하를 가정해 봅시다. 다음과 같이 ISBN을 검색하는 사용자 정의 함수를 정의합니다.


CREATE FUNCTION udf_get_book_ISBN (@xData xml) RETURNS varchar(20) WITH SCHEMABINDING BEGIN RETURN @xData.value('(/book/@ISBN)[1]', 'varchar(20)') END


ISBN을 위해 계산 열을 docs 테이블에 추가합니다.


CREATE TABLE docs (id int PRIMARY KEY, xCol XML) ALTER TABLE docs ADD ISBN AS dbo.udf_get_book_ISBN(xCol)


ISBN 열에 클러스터되지 않은 인덱스를 만듭니다.


CREATE INDEX COMPUTED_IDX ON docs (ISBN)


아래 쿼리를

SELECT xCol FROM docs WHERE xCol.exist ('/book/@ISBN[. = "0-2016-3361-2"]') = 1

계산 열을 사용하기 위해 다음과 같이 다시 작성합니다.

SELECT xCol FROM docs WHERE ISBN = '0-2016-3361-2'

ISBN 값의 추출이 미리 처리되어 있기 때문에 다시 작성한 쿼리는 더 간단한 쿼리 계획을 생성합니다.



속성 테이블 사용

별도의 속성 테이블은 유지 관리를 위해 삽입, 삭제 및 업데이트 트리거 설정이 필요합니다. 이 테이블은 속성 테이블의 각 행이 속성 값(피벗되지 않은 표시)을 포함하고 있는 경우의 다중값 속성에 적합합니다. 속성 테이블의 생성 및 유지 관리를 보여주는 예제를 관련 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법에서 찾을 수 있습니다.

형제 항목의 관련 순서가 응용 프로그램에 중요한 경우 속성 테이블에 시퀀스 번호 열이 요구됩니다. 하지만 이로 인해, XML 하위 트리 삽입 및 제거의 경우 속성 테이블 유지 관리가 복잡해집니다.

편의상 단일값 속성 열을 테이블에 추가할 수 있습니다. 이렇게 하면 열에서 중복이 발생하지만 두 속성이 모두 필요한 경우 JOIN이 제거됩니다.

승격된 속성의 최대 카디널리티 N이 작고 사전에 알려진 경우, 별도의 속성 테이블 대신 N개의 계산 열을 생성하고 쿼리 프로세서가 이러한 열을 유지 관리하게 하는 것이 편리할 것입니다.



XML 데이터 대량 로드

SQL Server의 대량 로드 기능을 사용하여 XML 데이터를 XML 데이터 형식 열에 대량 로드할 수 있습니다. 여기에는 BCP IN, BULK INSERT 및 OPENROWSET 메서드가 포함됩니다.

BCP 입력은 가능한 경우 XML 데이터의 중간 복사본을 회피하도록 최적화되었습니다. 따라서 XML 열에 행 또는 열 제약 조건이 없는 경우 BCP가 세 개의 대체 방법들 중에서 최상의 성능을 갖습니다.



OpenRowset 사용

OPENROWSET는 파일에서 XML 데이터를 XML 열, 변수 및 매개 변수로 로드하는 편리한 방법입니다. 변수 또는 매개 변수에서 XML 데이터를 여러 번 쿼리하면 파일에서 같은 횟수만큼 데이터를 가져올 수 있습니다. 아래 예제에서와 같이 XML 데이터를 XML 변수로 한 번 읽어 들여 이를 여러 번 쿼리하는 것이 더 낫습니다.


예제: OPENROWSET의 출력 쿼리

다음 쿼리에서 XML 데이터는 파일에서 테이블 식 XmlFile의 [Contents] 열로 읽혀 들어갑니다. nodes() 메서드는 XML 인스턴스에서 <author> 요소를 찾습니다. 각 value() 메서드는 파일에서 XML 데이터를 매번 로드하는, <author> 요소에 관련된 경로 식을 평가합니다.

WITH XmlFile ([Contents]) AS ( SELECT CONVERT (XML, [BulkColumn]) FROM OPENROWSET (BULK N'C:\Filedata.xml', SINGLE_BLOB) AS [XmlData] ) SELECT nref.value('first-name[1]', 'nvarchar(32)') FirstName, nref.value('last-name[1]', 'nvarchar(32)') LastName FROM [XmlFile] CROSS APPLY [Contents].nodes('//author') AS p(nref)


다시 작성된 아래 쿼리에 나타난 대로 성능 향상을 위해 파일 데이터를 한 번만 로드하면 됩니다. 즉, 파일 내용이 XML 변수 @xmlData로 한 번만 읽혀 들어 가고 SELECT 문에서 다시 사용됩니다.

DECLARE @xmlData XML; SELECT @xmlData = CONVERT (XML, [BulkColumn]) FROM OPENROWSET (BULK N'C:\Filedata.xml', SINGLE_BLOB) AS [XmlData]; SELECT nref.value('first-name[1]', 'nvarchar(32)') FirstName, nref.value('last-name[1]', 'nvarchar(32)') LastName FROM @xmlData.nodes ('//author') AS p(nref)


XML 데이터 인덱싱


XML 인덱스

XML 열로의 세분화된 쿼리의 경우 XML 열에 주 XML 인덱스를 만드는 것이 좋습니다. 주 XML 인덱스는 형식화된 XML 열 및 형식화되지 않은 XML 열 모두에서 생성될 수 있고 전체 XML 열 내의 모든 경로 및 값을 인덱스합니다. 주 XML 인덱스는 XML 열에 XML 인스턴스의 분할된(shredded) 표시를 기준으로 B+트리를 만듭니다. XML 열에 XML blob뿐만 아니라 이 B+트리가 만들어지고 그 크기는 XML 열의 XML blob을 모두 합친 크기보다 더 큽니다. B+트리는 XML 데이터 형식 메서드를 이용하여 XML 데이터를 쿼리하는 데 사용됩니다. XML blob은 전체 XML blobSELECT * FROM docs와 같은 기본 테이블에서 검색되는 경우를 최적화하는 데 사용됩니다. 이렇게 하면 더 작은 크기와 직렬화 비용 덕분에 주 XML 인덱스에서 XML 콘텐츠를 직렬화할 때보다 더 빨라집니다.

보조 XML 인덱스는 쿼리 최적화 프로그램이 더 나은 계획을 제안하도록 추가 옵션을 제공합니다. 응용 프로그램은 PATH, PROPERTY, VALUE 형식의 보조 XML 인덱스를 사용함으로써 성능이 보다 더 향상될 수 있습니다.



PATH 인덱스는 XML 데이터 형식에서 /book[@ISBN = "0-2016-3361-2"]와 같은 경로 식이 발생하는 경우에 항상 유용합니다. 긴 경로 식일수록 그 이점은 더 큽니다. PATH 인덱스는 전반적으로 상당한 속도 증가를 제공합니다.
PROPERTY 인덱스는 XML 인스턴스의 여러 속성이 SELECT 문 내에서 검색되는 경우에 유용합니다. 각 XML 인스턴스의 속성을 함께 클러스터링하면 성능이 보다 더 향상될 수 있습니다.


VALUE 인덱스는 하위 항목 축(예: //-operator) 및 와일드카드(예: /book[@* = "novel"])를 포함하는 경로 식에 유용합니다. 하나 이상의 보조 XML 인덱스가 도움이 되는지 여부를 결정하기 위해서는 쿼리 작업 부하의 분석이 필요합니다. 또한 XML 데이터 인덱스의 전체적인 혜택을 측정하는 데 인덱스 유지 관리 비용을 고려해야 합니다.

대부분의 응용 프로그램은 예상되는 쿼리 작업 부하를 알고 있으며 쿼리에서 발생하는 경로만을 인덱싱하여 이점을 얻을 수 있습니다. 이 경로는 이 문서의 후반부 "속성 승격"에 설명된 대로 속성으로 승격될 수 있습니다.



부분적 XML 업데이트

XML 데이터 형식의 인플레이스 업데이트는 세분화된 데이터 수정 동안 상당한 성능 향상을 제공합니다. 새 상태(데이터 수정 후) 및 이전 상태(데이터 수정 전) 간의 차이점이 처리되고 XML 열 저장소를 비롯하여 주 XML 인덱스에 적용됩니다. 또한 주 XML 인덱스의 변경 사항이 보조 XML 인덱스에도 전파됩니다. 이렇게 하면 저장소에서 업데이트되는 데이터의 양이 더 적고 그에 따라 트랜잭션 로그가 절감되므로 성능 혜택을 얻을 수 있습니다. 대부분의 경우 이러한 절감은 새 상태 및 이전 상태를 비교하는 비용을 상쇄합니다.

이 경우에 가장 적합한 시나리오는 XML DML에서 명령문의 "값 대체"를 이용한 특성 또는 요소 값의 수정입니다. 이렇게 하려면 XML 열에 있는 각각의 주 XML 인덱스 및 보조 XML 인덱스에서 행을 하나씩 업데이트해야 합니다. 또한 이 업데이트는 업데이트된 특성 또는 요소를 포함하는 XML blob의 디스크에 있는 페이지에 로컬입니다. 물론 이전 값을 큰 값으로 대체하면 새 디스크 페이지가 기록됩니다. 다음은 업데이트가 매우 효율적인 경우의 예제입니다.


예제: 특성 값 업데이트

이 예제에 나타난 대로 <book> 의 <price> 수정은 XML 인스턴스 및 XML 인덱스의 인플레이스 업데이트를 수행합니다.

UPDATE docs SET xCol.modify ('replace value of (/book/price/text())[1] with 29.99')

특성, 요소 또는 하위 트리 삽입의 경우, 새로 삽입된 노드 및 이 노드를 따르는 형제 항목이 해당 하위 트리와 함께 업데이트되거나 삽입됩니다. XML blob에서도 이와 비슷한 변경이 발생합니다. 노드 삭제의 경우에도 이와 유사하며 삭제 지점 위의 형제 항목이 업데이트됩니다.

최악의 시나리오는 노드를 XML 데이터 형식 인스턴스의 가장 왼쪽 조각으로서 삽입하거나 루트 요소의 가장 왼쪽 자식(child)을 삽입하는 동안 발생합니다. 이렇게 하면 전체 XML 인스턴스가 업데이트됩니다. 이 상황은 노드를 XML 인스턴스의 가장 오른쪽 조각 또는 루트 요소의 가장 오른쪽 자식(child)으로 삽입함으로써 피할 수 있습니다.

루트 요소의 가장 왼쪽 조각 또는 가장 왼쪽 자식(child)의 삭제에도 이와 비슷한 비용이 듭니다. 요소가 자주 삽입되고 삭제되는 경우에는 요소를 가장 오른쪽 조각 또는 루트 요소의 가장 오른쪽 자식(child)으로 삽입하는 편이 더 낫습니다. 다음 예제는 비용이 많이 드는 경우를 보여줍니다.


예제: 비용이 많이 드는 업데이트

<publisher> 요소가 <book> 요소의 가장 왼쪽 자식(child)으로 삽입되어 <book> 의 모든 하위 요소가 업데이트됩니다.

UPDATE docs SET xCol.modify (' insert <publisher>Microsoft Press</publisher> before (/book/title)[1]')

<publisher> 를 <book> 의 가장 오른쪽 자식(child)으로 삽입하면 더 효율적입니다.

UPDATE docs SET xCol.modify (' insert <publisher>Microsoft Press</publisher> into (/book)[1]')

XML 스키마 제약 조건은 삽입 지점을 결정할 수 있고 새 노드를 가장 오른쪽의 가능한 위치에 삽입하면 최상의 성능을 얻을 수 있습니다.



형식의 결합으로 인한 최적화 저해

암시적 캐스트를 요구하는 union 형식의 값은, 보조 XML 인덱스가 경로를 일치시키는 데 사용될 수 있는 경우에도 값에 대한 보조 XML 인덱스의 조회를 막습니다. 이와 같이 union 형식의 값은 범위 스캔이 VALUE 보조 XML 인덱스에서 발생하지 못하게 합니다. 자세한 내용은 이 문서의 "범위 제약 조건"을 참조하십시오. 같은 추론이 <xs:anyAttribute>에도 적용됩니다.

모델 그룹(<xs:choice> 및 <xs:all> ), 대체 그룹 및 와일드카드 섹션(xs:any)은 컨텐츠 모델로서 형식의 결합을 갖습니다. 정확한 형식이 쿼리 컴파일 및 최적화 동안 알려지지 않는 경우 런타임 형식 캐스트가 그 값에 대해 작업하기 위해 필요할 수 있습니다. 이렇게 하면 쿼리 속도가 느려집니다. 따라서 성능상의 이유로 가능한 경우 이러한 XML 스키마 구조 및 데이터 형식을 피해야 합니다.

요소의 단일 발생을 나타내는 스키마 구조를 사용하면 쿼리 최적화에 도움이 됩니다. 이러한 이유로 <xs:choice> 구조가 선택적 요소가 있는 <sequence> 에 비해 선호됩니다.



XML 인덱스 선택 비활성화

XML 인덱스가 제약 조건을 평가하기 전 또는 후에 수정되도록 쿼리 최적화 프로그램이 보장하지 않기 때문에 XML 인덱스 선택은 check 제약 조건에서 비활성화됩니다. 이 문서의 성능 지침을 따라 제약 조건이 XML blob 상에서 효율적으로 평가될 수 있도록 하기 위해 충분한 주의를 기울여야 합니다. 뿐만 아니라 XML 인덱스 선택은 CHECK OPTION이 있는 뷰에서 비활성화됩니다.



XML 열의 전체 텍스트 인덱스

XML 열의 XML 인덱스와는 독립적으로 이 열에 전체 텍스트 인덱스를 만들 수 있습니다. 전체 텍스트 인덱스는 요소 콘텐츠를 인덱싱하고 XML 태그 및 특성 값을 무시하며 태그를 토큰 경계로 사용합니다.

XQuery 함수 fn:contains()가 리터럴, 부분 문자열 일치의 의미론을 갖는 반면 CONTAINS()를 사용하는 전체 텍스트 검색은 형태소 분석을 이용한 토큰 일치를 사용합니다. 따라서 이들의 의미론은 상이합니다. 차이점을 예로 들어 보자면, 단어 "data"를 검색하면 XQuery에서는 단어 "database"와 일치하지만 전체 텍스트 의미론에서는 일치하지 반면, 단어 "drove"를 검색하면 전체 텍스트 의미론에서는 단어 "driving"과 일치하지만 XQuery에서는 일치하지 않습니다. 게다가 전체 텍스트 검색은 특성 값 검색에 사용할 수 없고, 한편 XQuery 식은 혼합 콘텐츠를 검색하기 위해 집계 함수 fn:string()을 사용해야 합니다.

전체 텍스트 인덱스가 XML 열에 존재하는 경우 다음 작업을 수행하는 것이 좋습니다.


전체 텍스트 검색을 사용하여 관심 있는 XML 값을 필터링합니다.
선택한 XML 인스턴스를 XML 데이터 형식 메서드를 사용하여 쿼리합니다. 이 단계 동안 XML 열의 XML 인덱스가 사용됩니다. 이렇기 때문에 전체 텍스트 인덱스 및 XML 인덱스를 모두 사용하게 됩니다. 전체 텍스트 검색에서 검색 단어 또는 구의 높은 선택성은 XQuery 검색을 위한 추가 처리 범위를 기본 테이블에 있는 비교적 적은 행의 수로 좁혀줍니다. 이로 인해 쿼리의 속도가 상당히 빨라질 수 있습니다. 이 접근 방법은 검색 구가 키워드 경계와 일치하는 어간으로 이루어진 경우에 사용할 수 있습니다.


예제: XQuery 일치와 전체 텍스트 결합

다음 쿼리는 키워드 'data'에 대한 전체 텍스트 검색을 수행하고 단어 "data"가 <book>의 <title>요소의 컨텍스트에서 나타나는지 확인합니다. 이 쿼리는 전체 텍스트 contains() 메서드를 사용하여 검색 단어를 포함하고 있는 XML 인스턴스를 찾습니다. 즉, XML 데이터 형식 메서드 exist()는 XML 인스턴스가 올바른 컨텍스트에서 부분 문자열을 포함하는지 확인합니다.


select * from docs where contains(xCol, 'data') AND xCol.exist('/book/title/text()[contains(.,"data")]') = 1

예제: 전체 텍스트에서 접두사 검색 사용

전체 텍스트 인덱스에서 접두사 검색을 수행할 수 있습니다. "database"와 같은 "data"로 시작하는 모든 키워드와 일치하는 쿼리에 대해, 위 쿼리는 다음과 같이 다시 작성될 수 있습니다. XQuery 검색은 "database"와도 일치합니다.

select * from docs where contains(xCol, '"data*"') and xCol.exist('/book/title/text()[contains(.,"data")]') = 1

전체 텍스트 contains() 메서드에서 큰따옴표의 사용에 주의하십시오.



스냅샷 격리 및 XML 인덱스

XML 데이터 수정은 이전 XML 인스턴스를 새 XML 인스턴스로 업데이트합니다. 이러한 변경은 주 XML 인덱스 및 보조 XML 인덱스에 전파됩니다. 기본 테이블 및 XML 인덱스에서 수정된 행은 잠겨지고 행 및 페이지 잠금이 쿼리 분석기의 결정에 따라 테이블 잠금으로 에스컬레이션될 수 있습니다. 특히 작업 부하에서 수정이 잦은 경우에는 잠금 에스컬레이션으로 인해 동시성이 나빠집니다.

SQL Server 2005에서 스냅샷 기반 격리는 "스냅샷"이라는 새로운 격리 수준과 새로운 read-committed 격리 수준의 구현을 도입했습니다. 이에 대한 자세한 내용은 SQL Server 온라인 설명서에서 찾아볼 수 있습니다. 이들은 데이터베이스를 스냅샷 격리에 사용할 수 있는 경우 판독기와 작성기 사이의 잠금 경합을 제거하는 내부 버전 관리 메커니즘에 기반합니다. 잠금 경합이 감소되면 처리량이 더 높아질 수 있습니다.

스냅샷 기반 격리 하에서 읽기 작업은 동시 업데이트에서 차단되지 않고서 버전 관리되는 데이터에 액세스할 수 있습니다. 이렇게 차단이 감소되면 동시 작업 부하에서 트랜잭션 처리량이 잠재적으로 향상됩니다.

스냅샷 격리를 사용하면 업데이트 시 XML 열 값과 해당 주/보조 XML 인덱스 행이 버전 관리됩니다. 이렇게 하면, XML이 아닌 열의 수정으로 인해 포함하는 행이 버전 관리되는 경우 XML 열의 불필요한 버전 관리를 피할 수 있습니다. 이 최적화는 XML 처리에 있어 스냅샷 격리를 매우 유용하게 만듭니다.



쿼리 및 데이터 수정

인덱스된 XML에 대한 여러 value() 메서드 실행 병합

인덱스된 경우에 더 빠른 실행을 위해, SELECT 목록의 동일한 형식화된 XML 열에서 여러 value() 메서드의 실행을 결합할 수 있습니다. 실행을 결합할지 여부는 쿼리 분석기가 쿼리 비용을 기반으로 결정합니다. 이로 인해 속도가 현저히 향상됩니다. 아래에 예제가 나와 있습니다.


예제: 여러 value() 메서드 실행 결합

XML 스키마 모음 bookCollection에 있는 XML 스키마 이름 공간 "http://www.microsoft.com/book"에 의해 요소의 콘텐츠 모델이 정의된다고 가정합시다. 또한 이 예제에서 XML 열 xDoc이 docs 테이블에 추가되고 bookCollection을 사용하여 형식화된다고 가정합시다. XML 스키마 정의는 아래와 같습니다.

CREATE XML SCHEMA COLLECTION bookCollection AS '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.microsoft.com/book" targetNamespace="http://www.microsoft.com/book"> <xsd:element name="book" type="bookType" /> <xsd:complexType name="bookType"> <xsd:sequence> <xsd:element name="title" type="xsd:string" /> <xsd:element name="author" type="authorName" maxOccurs="unbounded"/> <xsd:element name="price" type="xsd:decimal" /> </xsd:sequence> <xsd:attribute name="subject" type="xsd:string" /> <xsd:attribute name="releasedate" type="xsd:integer" /> <xsd:attribute name="ISBN" type="xsd:string" /> </xsd:complexType> <xsd:complexType name="authorName"> <xsd:sequence> <xsd:element name="first-name" type="xsd:string" /> <xsd:element name="last-name" type="xsd:string" /> </xsd:sequence> </xsd:complexType> </xsd:schema>' GO </P> <P>ALTER TABLE docs ADD COLUMN xDoc XML (DOCUMENT bookCollection) GO


아래 쿼리에서는, value() 메서드가 동일한 XML 열에서 호출되고 <title> 및 <price> 요소의 단일 카디널리티가 XML 스키마에서 정적으로 추론되기 때문에 value() 메서드의 실행을 결합합니다.

WITH XMLNAMESPACES ('http://www.microsoft.com/book' AS "bk") SELECT xCol.value ('/bk:book/bk:title', 'nvarchar(128)') Title, xCol. value('/bk:book/bk:price', 'decimal(5,2)') Price FROM docs

최적화가 발생하려면 다음 조건이 유지되어야 합니다.

열을 형식화하는 XML 스키마 모음에서 노드의 단일 카디널리티를 추론할 수 있도록 XML 열을 형식화해야 합니다. 해당되는 경우 열 옵션 DOCUMENT을 사용하여 XML 열을 선언해야 합니다. 그렇지 않으면 nodes() 메서드를 사용하여 단일 노드 참조를 생성해야 합니다. 또한 nodes() 메서드가 단일 요소를 보장하는 데 사용되고 value() 메서드가 그러한 요소에서 특성 값을 추출하는 경우 형식화되지 않은 XML에 대해서도 최적화가 작동합니다.
최적화가 발생하려면 전체 경로를 지정해야 합니다. 와일드카드(*), 하위 항목 축(// operator), 상위 항목 축(..), XPath 함수 및 노드 테스트(예: node())를 포함하는 경로는 이러한 최적화의 발생을 막습니다.
value() 메서드의 경로 식은 술어 또는 서수를 포함하지 않을 수 있습니다.
nodes() 메서드에서 얻은 컨텍스트 항목과 관련된 경로 식에 대해 최적화가 발생하는데, 이러한 경우 value() 메서드의 첫 번째 인수는 위 제약과 더불어 전체 상대 경로여야 합니다.
value() 메서드 호출이 병합되려면 SELECT 목록에서 연속적으로 나타나야 합니다. 불연속적인 value() 메서드의 실행은 결합될 수 없습니다.

또한 xCol.value(…) = xCol.value(…)와 같은 술어에 대해 T-SQL WHERE 절의 value() 메서드에서 최적화가 발생할 수 있습니다. xCol.value(…) = constant의 술어에서는 최적화가 발생하지 않습니다.


존재 확인을 위해 exist() 메서드 사용

보다 나은 성능을 위해 가능한 경우 XML 데이터 형식의 value() 메서드 대신 exist() 메서드를 사용하십시오. exist() 메서드는 SQL WHERE 절에서 사용하는 경우 가장 유용하며 value() 메서드보다 XML 인덱스를 더 효과적으로 활용합니다. XQuery 식에서 sql:variable() 및 sql:column()을 사용하는 경우에도 마찬가지입니다.

예를 들어, exist() 메서드를 사용하여 "Writing Secure Code"라는 제목의 책을 검색하는 다음 쿼리를 살펴봅시다.

SELECT * FROM docs WHERE xCol.exist('(/book/title/text())[.="Writing Secure Code"]') = 1

PATH 또는 VALUE 보조 XML 인덱스가 이들 인덱스에서의 값 조회(이 예제에서 "Writing Secure Code")를 포함하여 경로 식((/book/title/text())[.="Writing Secure Code"])을 평가하여 반환할 XML 인스턴스를 산출하기 위해 사용됩니다. 경로 및 검색 값의 선택성이 높은 경우 결과 실행이 열의 모든 XML blob에 대해 경로 식을 평가하는 것보다 훨씬 더 빠를 수 있습니다. 검색 값은 sql:variable() 또는 sql:column()을 사용하여 제공할 수 있습니다. 자세한 내용은 이 문서의 "XQuery 및 XML DML 식 매개 변수화"를 참조하십시오.

아래와 같이 value() 메서드를 사용하여 작성한 쿼리는

SELECT * FROM docs WHERE xCol.value('(/book/title)[1]', 'varchar(50)') = 'Writing Secure Code'

먼저 모든 책 제목을 평가한 다음 필터 "Writing Secure Code"를 적용합니다. 이렇게 하면 XML 인덱스 조회에서 필터 값 "Writing Secure Code"가 사용되지 않기 때문에 쿼리 실행의 효율성이 떨어집니다. SQL 변수 또는 또 다른 value() 메서드를 사용하여 지정한 필터 값도 유사한 동작(behavior)을 나타냅니다.

nodes()-value() 결합 사용
nodes() 메서드는 value() 메서드 내에서 사용할 수 있는 내부 노드 참조의 행 집합을 생성하여 이들 노드에서 스칼라 값을 추출합니다. 이들 메서드를 함께 사용하여 XML 데이터를 관계형 형식으로 표시할 수 있습니다.

아래 예제에서 보는 대로, value() 메서드에서 컨텍스트 노드의 특성을 선택하는 데 사용되는 서수 술어가 제거될 수 있도록 nodes() 메서드의 출력에서 각 행은 단일 참조를 표시합니다. 또한 nodes() 메서드가 정확히 하나의 참조를 제공할 경우 nodes() 메서드를 완전히 제거하면 쿼리가 더 빠르게 수행됩니다. 이러한 최적화는 XML 변수 및 매개 변수에 가장 유용합니다.

예제: nodes() 메서드로 서수 술어 제거

이 쿼리는 docs테이블의 xCol 열에 있는 각 책의 인스턴스에서 ISBN 특성을 추출합니다. nodes() 메서드는 각 개별 <book> 요소(컨텍스트 노드)에 대한 별도의 참조를 내보내고 컨텍스트 노드에는 최대 하나의 @ISBN 특성이 있을 수 있습니다.

SELECT ref.value('@ISBN', 'nvarchar(32)') FROM docs CROSS APPLY xCol.nodes('/book') AS node(ref)

각 XML 인스턴스에서 요소가 하나만 발생할 경우 다음의 다시 작성된 쿼리가 더 빠릅니다.

SELECT xCol.value('(/book/@ISBN)[1]', 'nvarchar(32)') FROM docs

XML blob을 위한 최적화

XML 변수 및 매개 변수의 더 나은 확장성을 위한 여러 개의 tempDB 파일

XML 변수 및 매개 변수는 그 값이 작은 경우에는 주 메모리를 저장소로 사용합니다. 단, 큰 값은 tempdb 저장소에서 백업합니다. 다중 사용자 시나리오에서 많은 수의 대형 XML blob이 발생할 경우 tempdb 경합이 충분한 확장성을 위해 병목 상태를 일으킬 수 있습니다. 여러 개의 tempdb 파일을 만들면 저장소 경합이 감소되고 확장성이 훨씬 더 좋아집니다. 다음 예제는 여러 개의 tempdb 파일을 만들 수 있는 방법을 보여줍니다.


예제: 여러 개의 tempdb 파일 만들기

이 예제에서는 각각 처음 크기가 8MB인 두 개의 tempdb용 추가 데이터 파일과 각각 처음 크기가 1MB인 두 개의 로그 파일을 만듭니다.

USE TEMPDB GO ALTER DATABASE tempdb ADD FILE (NAME = 'Tempdb_Data1', FILENAME = 'C:\temp\Tempdb_Data1.MDF', SIZE = 8 MB), (NAME = 'Tempdb_Data2', FILENAME = 'C:\temp\Tempdb_Data2.MDF', SIZE = 8 MB) GO ALTER DATABASE tempdb ADD log FILE (NAME = 'Tempdb_Log1', FILENAME = 'C:\temp\Tempdb_Log1.LDF', SIZE = 1 MB), (NAME = 'Tempdb_Log2', FILENAME = 'C:\temp\Tempdb_Log2.LDF', SIZE = 1 MB) GO


이 파일은 ALTER DATABASE tempdb REMOVE FILE 명령을 사용하여 제거될 수 있습니다. 자세한 내용은 SQL Server 2005용 SQL Server 온라인 설명서를 참조하십시오.


XML 데이터 형식으로의 추가 캐스트 제거

XML 형식의 입력 인수를 가진 inlinable 함수에서 호출자는 XML 데이터 형식으로 암시적으로 변환되는 텍스트 또는 이진 값을 제공할 수 있습니다. 호출 수신자의 본문에서 XML 인수의 각 사용은 입력 값을 XML 데이터 형식으로 캐스트합니다. 이 비용은 인수를 XML 데이터 형식 변수(XML 데이터 형식으로 한 번의 인수 값 변환 유발)로 복사함으로써 피할 수 있고 XML 변수는 함수 또는 저장 프로시저의 본문에서 여러 번 사용할 수 있습니다. 다음 예제는 이러한 특징을 예시합니다.


예제: 변환 제거

책의 제목 및 ISBN을 반환하는 아래 GetTitleAndIsbnOfBook() 함수를 살펴봅시다.

CREATE FUNCTION GetTitleAndIsbnOfBook (@book XML) RETURNS TABLE AS RETURN SELECT @book.value ('(/book/@ISBN)[1]', 'nvarchar(32)') ISBN, @book.value ('(/book/title)[1]', 'nvarchar(128)') title


함수가 문자열 값으로 호출되면 각 value() 메서드 호출에 대해 XML 데이터 형식으로의 변환이 발생합니다. 함수는 XML 데이터 형식으로의 인수 변환이 한 번만 발생하도록 다음과 같이 다시 작성될 수 있습니다. 하지만 다중 명령문 테이블 반환 함수에 필요한 테이블 변수 @retTab으로 인해 추가 비용이 발생하는데, 이 비용은 XML 데이터의 크기가 큰 경우 XML 변수에 대한 충분한 횟수의 액세스로 상쇄될 수 있습니다.

CREATE FUNCTION GetTitleAndIsbnOfBookOpt (@book varbinary(max)) RETURNS @retTab TABLE (ISBN nvarchar(32), title nvarchar(128)) AS BEGIN DECLARE @xbook XML SET @xbook = @book INSERT INTO @retTab SELECT @xbook.value ('(/book/@ISBN)[1]', 'nvarchar(32)'), @xbook.value ('(/book/title)[1]', 'nvarchar(128)') RETURN END


단일 요소 지정

단일 카디널리티 평가는 쿼리 및 데이터 수정 문에서 서수를 지정할 필요성을 제거합니다. 이로 인해 쿼리 계획이 간소화되고 효율적인 JOIN 작업이 생성됩니다. 일반적으로 단일 카디널리티 평가에는 중첩 루프 조인에서 내부 및 외부 루프에 대한 적절한 선택이 수반됩니다.

형식화된 XML에서 기본적으로 요소는 minOccurs 및 maxOccurs의 값을 사용하여 재정의되지 않는다면 XML 스키마 구조의 단일 카디널리티를 갖습니다. 또한 형식화된 XML 열, 변수 및 매개 변수의 DOCUMENT 제약 조건은 XML 데이터 형식 인스턴스에서 정확히 하나의 최상위 요소를 보장합니다.

형식화되지 않은 데이터의 경우 또는 스키마에 여러 형제 요소가 허용되는 경우, 아래 예제에 나타난 대로 경로 식을 만족시키는 노드를 정확하게 하나만 선택하는 서수 값을 사용하여 노드의 단일 카디널리티가 경로 식에 표시될 수 있습니다. 서수 [1]은 Transact -SQL TOP 1 오름차순을 사용하여 평가되는 반면 서수 last()는 TOP 1 내림차순으로 평가됩니다. 또한 nodes() 메서드는 결과 XML 인스턴스 각각에 대해 단일 컨텍스트 항목을 설정합니다.

단일 노드의 선택이 생략된 경우 쿼리 최적화 프로그램이 지나치게 높을 수 있는 기본 카디널리티 평가를 사용합니다. 예를 들면, 이는 술어를 처리해야 하는 경우 중첩 루프 조인에서 내부 및 외부 루프에 대해 차선책을 선택하게끔 유발할 수 있습니다. 그 효과는 XML 인덱스가 존재하지 않고 보다 효율적인 카디널리티 평가를 위해 사용할 수 있는 통계 정보가 없는 XML blob의 경우에 더욱 크게 나타납니다.



예제: 형식화되지 않은 XML의 단일 카디널리티 지정

xCol 열의 각 XML 인스턴스가 <title> 하위 요소를 하나만 갖는 최상위 요소를 하나만 포함한다고 가정합시다. 다음 쿼리를 살펴봅시다.

SELECT xCol.query ('/book/title') FROM docs

쿼리 최적화 프로그램은 <title> 요소에 기본 카디널리티 평가를 사용합니다. 각 <book>은 <title>이 단일 요소가 되도록 하나의 제목을 갖지만 최적화 프로그램의 평가는 그에 비해 훨씬 더 높습니다. 다시 공식화한 다음 쿼리는

SELECT xCol.query ('(/book/title)[1]') FROM docs

올바른 카디널리티를 최적화 프로그램에 전달합니다. 비슷한 모양의 경로 식 (/a/b)[1]과 /a/b [1] 간의 의미론적 차이점이 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법에 설명되어 있습니다.


XML 데이터 형식 메서드의 다중 실행 제거

다음 쿼리는

SELECT case isnumeric (xCol.value ('(/book/price)[1]', 'nvarchar(32)')) when 1 then xCol.value ('(/book/price)[1]', 'decimal(5,2)') else 0 end FROM docs

책의 <price>를 계산한 다음 가격이 숫자 형식인 경우 가격을 십진수(5, 2)로 변환합니다. 이 논리는 응용 프로그램에서 숫자가 아닌 가격 값이 발생할 수 있는 경우에 유용합니다.

SELECT case isnumeric(Price) when 1 then CAST (Price AS decimal(5,2)) else 0 end FROM (SELECT xCol.value ('(/book/price)[1]', 'nvarchar(32)') Price FROM docs) T

동일한 최적화가 NULLIF()와 같은 다른 곳에서도 사용될 수 있습니다.

SELECT NULLIF (Title, '') FROM (SELECT xCol.value ('(/book/title)[1]', 'nvarchar(64)') Title FROM docs) T

NULLIF()에서 value() 메서드를 사용하면 이 메서드가 비어 있지 않은 문자열을 반환할 경우 value() 메서드가 두 번 처리됩니다.


Data(),text() 및 string() 접근자

XQuery는 노드에서 형식화된 원자 값을 추출하기 위한 fn:data() 함수, 텍스트 노드를 반환하기 위한 노드 테스트 text() 및 노드의 문자열 값을 반환하기 위한 fn:string() 함수를 제공합니다. 이들의 사용은 혼동될 수 있습니다. SQL Server 2005에서 이를 올바로 사용하기 위한 지침이 XML 인스턴스 <age>12</age>를 사용하여 아래에 예시되어 있습니다.


형식화되지 않은 XML: 경로 식 /age/text()는 값이 “12”인 <age> 아래에 텍스트 노드를 반환합니다. fn:data(/age) 함수는 fn:string(/age)처럼 문자열 값 “12”를 반환합니다.

형식화된 XML: /age/text() 식은 SQL Server 2005에서 모든 단순한 형식화된 <age> 요소에 대한 정적 오류를 반환합니다. <age>가 단순한 정수 콘텐츠를 갖는 경우에는 fn:data(/age)가 정수 12를 반환하는 반면, fn:string(/age[1])은 문자열 “12”를 산출합니다. 이들 함수는 서로 다른 성능 특성을 갖습니다. Fn:string()은 컨텍스트 노드 아래의 모든 텍스트 노드를 재귀적으로 집계합니다. 컨텍스트 노드가 단일값인 경우 이 방식은 과도하며, 이 경우에는 fn:data() 및 text()로도 충분할 뿐만 아니라 더 효과적입니다.

형식화되지 않은 XML의 경우 노드의 값이 필요할 때, text()를 사용하여 텍스트 노드를 반환하는 것이 fn:data()를 사용하여 반환하는 것보다 더 빠릅니다. 경로 식 /book/text()는 <book> 요소의 텍스트 노드 자식(child)을 반환합니다. query() 메서드 내에서 이러한 텍스트 노드는 직렬화되고 텍스트 노드 값의 연결로 나타납니다. 반면, fn:data()는 <book> 요소의 하위 트리에 있는 모든 값을 집계합니다. 이 집계는 간단한 콘텐츠를 가진 요소의 경우일지라도 fn:data()의 계산이 text()의 계산보다 비용이 더 많이 소요되게 만듭니다.


형식화되지 않은 XML에서 텍스트 집계

XQuery 의미론에 따라, 형식화되지 않은 XML에서 다음과 같은 두 쿼리는 술어를 평가하기 위해 <title> 요소 아래의 모든 텍스트 노드를 집계해야 합니다. 이 때문에 검색 문자열에 대한 XML 인덱스 조회가 억제됩니다.

SELECT xCol.value ('(/book/title[.="Writing Secure Code"])[1]', 'nvarchar(64)') FROM docs

또는

SELECT xCol.value ('(/book/title [fn:string()="Writing Secure Code"])[1]'), 'nvarchar(64)') FROM docs

<title> 요소에 텍스트 노드가 하나만 있는 경우 더 효율적인 쿼리 작성 방법은 아래와 같이 텍스트 노드의 술어를 평가하는 방법입니다.

SELECT xCol.value ('(/book/title/text())[1] [. = "Writing Secure Code"]', 'nvarchar(64)') FROM docs

이 경우에는 "Writing Secure Code" 값에 대한 XML 인덱스 조회가 발생합니다.



XQuery 및 XML DML 식 매개 변수화

XQuery 및 XML DML 식은 자동으로 매개 변수화되지 않습니다. 따라서 두 XQuery 식이 매개 변수의 값만 다른 경우, 동적 SQL 문을 사용하는 대신 sql:column() 또는 sql:variable()을 사용하여 XQuery 또는 XML DML 식에 매개 변수 값을 제공하는 것이 더 좋습니다. 이들 함수를 사용하면 쿼리가 자동으로 매개 변수화됩니다.

아래 예제는 저장 프로시저 실행을 보여줍니다. 이 기법은 쿼리, 함수/메서드 호출 또는 데이터 수정 문의 매개 변수화에 적용할 수 있습니다.

예를 들어, 아래 저장 프로시저는 입력 인수보다 낮은 가격의 책을 찾습니다.

CREATE PROC sp_myProc @Price decimal AS SELECT * FROM docs WHERE 1 = xCol.exist('(/book/price)[. < sql:variable("@Price")]')

ADO.NET 및 OLEDB에서 @Price의 입력 값을 매개 변수에 바인딩합니다. 이렇게 하면 매개 변수가 서로 다른 값에 바인딩될 때 쿼리를 다시 컴파일할 필요가 없습니다. sql:column()을 사용하면 이와 비슷한 이점을 얻을 수 있습니다.

다음 Visual Basic.NET 코드는 저장 프로시저 호출에서 매개 변수 바인딩을 보여줍니다.

'myConn is the connection string SqlCommand cmd = New SqlCommand("sp_myProc", myConn) cmd.CommandType = CommandType.StoredProcedure 'Parameter binding Dim myParm As SqlParameter = cmd.Parameters.Add("@Price", _ SqlDbType.Decimal) myParm.Direction = ParameterDirection.Input myParm.value = 2 'Invoke the stored procedure SqlDataReader myReader = cmd.ExecuteReader() 'Invoke the stored procedure a second time myParm.value = 49.99 SqlDataReader myReader = cmd.ExecuteReader()

자세한 내용은 Microsoft Visual Studio.NET 설명서 (영문)를 참조하십시오.


예제: 데이터 수정에서 sql:variable() 사용

ISBN이 "0-2016-3361-2"인 <book>의 <price>가 10% 할인되었다고 가정합시다. 할인과 ISBN 둘 다 XML 데이터 수정 문에 매개 변수로 전달될 수 있고 이 문은 다른 책 또는 다른 할인에 대해 똑같이 유지됩니다.

DECLARE @discountFactor decimal, @sqlisbn nvarchar(32) SET @discountFactor = 0.9 SET @sqlisbn = N'0-7356-1588-2' UPDATE docs SET xCol.modify('replace value of (/book/price/text())[1] with sql:variable("@discountFactor")*(/book/price/text())[1]') WHERE xCol.exist('/book[@ISBN = sql:variable("@sqlisbn")]') = 1


예제 : 요소 구성에서 sql:variable() 사용

아래에 나와 있는 modify() 메서드는 구성된 요소 내에서 값을 제공하기 위한 sql:variable()의 사용을 보여줍니다.

DECLARE @name nvarchar(64) SET @name = 'Microsoft Press' UPDATE docs SET xCol.modify (' insert < publisher Name = "{sql:variable("@name")}"></publisher> into (/book/title)[1]')

술어 및 서수의 최적화

노드 테스트 또는 분기가 없는(즉, 경로의 중간 노드에 술어 또는 서수가 없는) 전체 경로(즉, 루트 노드로부터 자식(child) 및 자신의 축만을 포함하는 선택한 노드에 이르는 절대 위치 경로)는 분기가 있는 경로 식보다 효율적으로 평가할 수 있습니다. 인덱스된 경우에 전체 경로는 인덱스 탐색에 사용될 수 있습니다. XML blob의 경우, 이러한 경로의 구문 분석은 분기 또는 와일드카드(*)가 있는 경로의 구문 분석보다 더 빠릅니다.

경로 끝부분의 노드 테스트 및 술어는 선택한 노드에서 필터로 사용됩니다. 인덱스가 사용됩니다. XML blob의 경우 구문 분석이 효율적입니다. 아래에 예제가 나와 있습니다.


예제: 전체 경로 평가

이름이 Davis인 저자가 쓴 책을 선택하는 경로 식을 생각해 봅시다.

SELECT xCol.query ('/book[author/first-name = "Davis"]') FROM docs

술어가 <book> 요소에 직접 존재하지 않더라도 축소 경로 /book/author/first-name을 사용하여 위치한 <first-name> 노드는 값 "Davis"에 의해 필터링됩니다. 반환된 <book> 요소는 주어진 술어를 충족하는 것들입니다.

경로 기반 조회는 /book//first-name과 같이 술어 또는 서수가 없는 부분적으로 지정된 경로에도 효율적입니다. 쿼리 컴파일러는 XML 인덱스에서 이러한 경로를 일치시키기 위해 LIKE 연산자를 사용합니다. 그러므로 가능한 한 경로의 많은 부분을 지정하는 것이 보다 효율적인 처리에 도움이 됩니다.

/book[@ISBN = "1-8610-0157-6"]/author/first-name에서와 같이 분기(즉, 경로 식의 중간에 있는 노드 테스트 및 서술어)는 경로 식 /book[@ISBN = "1-8610-0157-6"] 및 /book/author/first-name을 평가하고 <book> 요소의 두 집합 사이의 교집합을 취합니다. 따라서 분기 없는 경로 식보다 실행이 느려집니다. 경로 식의 중간에 노드 테스트 및 술어를 사용하는 것을 가능한 한 피하는 것이 효과적입니다. 이는 “일반적 태그와 구체적 태그의 비교” 예제에 설명된 대로, 데이터 모델링 시 신중한 주의를 기울임으로써 가능한 경우가 종종 있습니다.


경로의 끝으로 서수 이동

정적 형식의 정확성을 위해 경로 식에 사용되는 서수는 경로 식의 끝부분에 넣을 수 있는 좋은 후보입니다. 경로 식 book[1]/title[1]은 (/book/title)[1]과 동일합니다. 후자는 문서 순서에서 <book> 요소 아래의 첫 번째 <title> 요소를 확인함으로써 XML 인덱스 및 XML blob 두 경우 모두에 대해 모두 더 빠르게 평가될 수 있습니다. 이와 유사하게 경로 식 (/book/@ISBN)[1]은 /book[1]/@ISBN보다 더 빠른 실행을 가져옵니다.


컨텍스트 노드를 사용한 술어 평가

술어, 서수 및 노드 테스트를 경로 식의 끝으로 이동하는 것 외에도 컨텍스트 노드를 사용하여 이러한 조건을 평가하면 한층 더 나은 성능을 얻을 수 있습니다. 아래에 이에 대한 쿼리 재작성 예제가 나와 있습니다.


예제: 컨텍스트 노드를 사용한 술어 평가

아래 쿼리는 “security”라는 제목의 책을 검색합니다. 이 쿼리에는 두 경로 식, /book 및 /book/@subject의 평가와 후자 경로에 대한 “security” 값 확인이 필요합니다.

SELECT * FROM docs WHERE xCol.exist ('/book[@subject = "security"]') = 1

다시 작성된 아래 쿼리는 단일 경로 /book/@subject를 평가하고 이 경로에 "security" 값이 있는지 확인합니다. 이렇게 하면 위 쿼리보다 쿼리 계획이 더 단순해지고 실행이 훨씬 더 빨라집니다.

SELECT * FROM docs WHERE xCol.exist ('/book/@subject[. = "security"]') = 1

범위 조건

범위 조건은 형식화된 XML의 사용을 통해 이점을 얻습니다. XML 열 및 XML 인덱스에 저장된 데이터는 XML 스키마에 지정된 형식 정의에 따라 형식화됩니다. 값 비교는 데이터의 런타임 변환을 피하고 VALUE 보조 XML 인덱스에 대한 범위 스캔을 허용합니다. 또한 이를 위해서는 다음 예제에 나타난 대로, 효율적인 액세스를 위해 범위 조건에 컨텍스트 노드(.)를 지정해야 합니다.


예제: 범위 조건의 컨텍스트 노드

$9.99와 $49.99 범위에 있는 가격대의 책을 찾는 쿼리를 고려해 봅시다.

SELECT xCol FROM docs WHERE xCol.exist ('/book[price > 9.99 and price < 49.99]') = 1

경로 식 /book/price > 9.99 및 /book/price < 49.99가 각각 따로 평가됩니다. <book> 요소 아래에 여러 <price> 요소가 존재할 수 있기 때문에 쿼리 최적화 프로그램은 <price> 요소가 동일하다는 사실을 알지 못합니다. 이 때문에 VALUE 보조 XML 인덱스에 대한 범위 스캔이 억제됩니다. 아래에 다시 작성된 쿼리는 <price>에 동일한 컨텍스트 노드를 사용하고 9.99와 49.99 사이의 값에 대해 VALUE 보조 XML 인덱스의 범위 스캔이 발생하도록 보장합니다. 이로 인해 성능이 더 좋아집니다.

SELECT xCol FROM docs WHERE xCol.exist ('/book/price[. > 9.99 and . < 49.99]') = 1


동적 쿼리

XQuery 식은 XML 데이터 형식 메서드 내에서 리터럴로 지정됩니다. 이들의 평가는 사용 가능한 경우 쿼리 최적화 프로그램에서 선택한 XML 인덱스를 사용합니다.

XQuery 식을 리터럴 대신 동적으로 지정할 수 있으면 응용 프로그램 개발이 편리합니다. 이는 다음 방식으로 가능합니다.

쿼리 구성
쿼리를 문자열로 작성하고 sp_executesql을 사용하여 실행합니다. EXEC와 달리, 이렇게 하면 컴파일된 쿼리 계획이 캐시되어 최적화 프로그램이 컴파일된 계획을 다시 사용할 수 있습니다. 쿼리는 문자열로 구성되므로 매개 변수화될 수 있고 포함된 매개 변수를 포함할 수 있습니다. SQL 주입 공격을 피하려면 충분한 주의를 기울여야 합니다.

XPath 함수 사용
XPath 식의 각 위치 단계를 name() 함수 또는 local-name() 및 namespace-URI() 함수로 대체합니다. 이렇게 하면 노드 이름 및 검색 값으로 전달할 수 있는 쿼리가 만들어집니다. "XQuery 또는 XML DML 식 매개 변수화" 예제에 설명된 대로 추가적으로 매개 변수화할 수 있습니다. 이와 같은 매개 변수화된 쿼리는 응용 프로그램 개발에 편리합니다. 하지만 컴파일 시에 구체적인 경로가 알려지지 않으므로, 이에 대해 생성된 쿼리 계획은 XML 인덱스를 무시합니다.

쿼리 구성 접근 방법은 경로 식 매개 변수화보다 더 능률적으로 작동하지만 런타임 쿼리 컴파일 비용이 들기 때문에, 전체 쿼리를 리터럴로 지정하는 것보다 더 느려집니다. 사용자가 전달한 실제 쿼리는 SQL 주입 공격을 피하기 위해 유효성이 검사되어야 합니다. 그렇지 않으면, 쿼리의 매개 변수화(이 문서의 “XQuery 및 XML DML 식 매개 변수화")를 위해 이 접근 방법을 사용하지 말아야 합니다. 다음 예제는 이 접근 방법을 예시합니다.

노드 이름을 사용하여 노드 테스트를 지정하는 두 번째 접근 방법은 SQL 주입 문제를 방지합니다. 하지만 쿼리 계획이 복잡해지고 원래 쿼리보다 훨씬 더 비능률적으로 수행됩니다. 이 접근 방법은 두 번째 예제에 나와 있습니다.


예제: sp_executesql를 사용한 쿼리

다음 쿼리를 동적으로 작성하여 매개 변수를 사용하여 @subject의 검색 값을 전달하기를 원한다고 가정합시다.

SELECT * FROM docs WHERE xCol.exist('/book[@subject = "security"]') = 1


동적 쿼리를 아래에 나타난 대로 작성하고 실행할 수 있습니다. 쿼리 문자열은 @SQLString 변수에 만들어지고 exist() 메서드에서 사용된 포함된 변수 @bksubj를 포함합니다. @subj 변수는 매개 변수의 런타임 값을 제공합니다. @SQLString을 사용하여 전달된 동적 쿼리는 SQL 주입 공격을 피하기 위해 유효성이 검사되어야 합니다(아래에는 표시되어 있지 않습니다).

DECLARE @SQLString NVARCHAR(500) DECLARE @subj NVARCHAR(64) DECLARE @ParmDefinition NVARCHAR(500) --- Build the SQL string once SET @SQLString = N'SELECT * FROM docs WHERE xCol.exist(''/book[@subject=sql:variable("@bksubj")]'')=1' SET @ParmDefinition = N'@bksubj NVARCHAR(64)' --- Execute the string with the first parameter value SET @subj = 'security' EXECUTE sp_executesql @SQLString, @ParmDefinition, @bksubj = @subj

예제: local-name()을 사용한 쿼리

위 쿼리를 다음과 같이 태그 이름을 리터럴로 사용하도록 다시 작성할 수 있습니다.

DECLARE @elemName nvarchar(4000), @attrName nvarchar(4000) DECLARE @subjValue nvarchar(4000) SET @elemName = N'book' SET @attrName = N'subject' SET @subjValue = N'security' SELECT * FROM docs WHERE xCol.exist('/*[local-name() = sql:variable("@elemName") and @*[local-name() = sql:variable("@attrName") and . = sql:variable("@subjValue")]]') = 1

다시 작성된 쿼리는 와일드카드(*)와 노드 이름을 사용하는 노드 테스트를 포함하므로 효과적으로 최적화하기가 어렵습니다. 결과적으로 원래 쿼리 및 쿼리 구성 접근 방법에 비해 훨씬 더 비능률적으로 수행됩니다.


XML 데이터에서 행 집합 생성

일부 응용 프로그램은 하나 이상의 속성을 행 집합의 열로 승격함으로써 XML 데이터에서 행 집합을 생성해야 합니다. 예를 들어, 응용 프로그램은 책의 저자를 쿼리하고 성 및 이름에 대해 두 개의 열을 포함하는 테이블로 결과를 표시할 수 있습니다. 이러한 행 집합 생성은 서로 다른 성능 특성을 지닌 서버 또는 클라이언트에서 모두 수행될 수 있습니다.


서버에서 다음 메커니즘 중 하나를 사용하십시오.
XML 데이터 형식의 nodes() 및 value() 메서드의 결합
OpenXML
CLR(공용 언어 런타임)에서 테이블 반환 함수 스트리밍
또한 XML 결과가, 클라이언트 쪽 프로그래밍(예: DataSet)을 사용하여 데이터를 행 집합으로 변환하는 클라이언트에게 반환됩니다. 클라이언트 쪽 행 집합 생성은 서버의 부하를 덜어주므로 서버에서 클라이언트로 전송된 거의 전체 데이터가 행 집합으로 매핑되는 경우에 유용합니다. 그렇지 않은 경우에는 데이터 제공 비용이 클라이언트 쪽 처리 이점보다 더 클 수 있습니다.

서버 쪽 행 집합 생성은 서버에서 들어오는 XML 데이터로부터의 행 집합 생성에 유용합니다. 이 방식은 서버에 저장된 XML 데이터의 일부분만이 행 집합의 열로 승격되는 경우에 일반적으로 더 바람직합니다. 서버 쪽 접근 방법들의 상대적 장점 및 단점에 관한 자세한 설명은 MSDN 기사 Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법에서 찾을 수 있습니다.



제공 : DB포탈사이트 DBguide.net
출처명 : 한국 마이크로소프트

적용 대상:
Microsoft SQL Server 2005
Microsoft Visual Studio 2005
Microsoft .NET Framework 2.0
XML 및 관계형 데이터

요약: Visual Studio 2005/SQL Server 2005 환경에서 XML 데이터 처리를 위한 세 가지 옵션을 설명하고 이 옵션들 중에서 선택하는 데 도움을 주는 사용 시나리오 및 지침을 제공합니다.



System.Xml, SQLXML 및 XML 데이터 형식 소개


섹션에서는 Microsoft SQL Server 2000에 제공된 XML 지원에 관한 간략한 배경 설명과 함께 XML 및 관계형 데이터 조작을 위해 Microsoft Visual Studio 2005/SQL Server 2005 환경에 제공된 세 가지 옵션의 개요를 제공합니다. 이 세 옵션은 1) System.Xml 이름 공간의 클래스, 2) SQLXML 클래스, 3) SQL Server 2005에 제공된 XML 데이터 형식입니다.

사용자에게 다음과 같은 기능을 제공하기 위해 XML 지원이 Microsoft SQL Server 2000에 추가되었습니다.



관계형 데이터를 XML로 노출

XML 문서를 행 집합으로 분할(Shredding)

XDR(XML-Data Reduced) 스키마를 사용하여 XML 스키마를 데이터베이스 스키마에 매핑하여 XML 뷰 생성

XPath를 사용하여 XML 뷰에서 쿼리 작성

HTTP를 통해 SQL Server에 데이터 노출

이 지원은 이후의 SQLXML 웹 릴리스에서 더욱 향상되었습니다. 향상 기능은 다음과 같습니다.


XML 뷰에 적용된 변경 내용을 유지하기 위한 Updategrams 및 XML Bulkload

매핑을 설명하기 위한 주석 달린 XML 스키마 정의 언어(XSD) 지원(여전히 XDR을 지원하지만 사용이 권장되지 않습니다.)

클라이언트 쪽 FOR XML

SQLXML 관리 클래스

웹 서비스 지원

Microsoft .NET Framework 1.0은 XML 문서 읽기, 쓰기 및 처리를 위한 포괄적인 지원을 제공합니다. 이 지원은 다양한 XML 클래스의 성능 및 유용성을 개선하기 위해 .NET Framework 2.0에서 보다 더 강화되었습니다. .NET Framework에서 제공하는 System.Xml 이름 공간의 새 클래스는 XML 데이터를 관계형 데이터에 매핑하는 데 사용될 수 있습니다.

SQLXML은 SQL Server 데이터베이스에 있는 관계형 데이터와 XML의 원활한 통합을 가능하게 하는 일련의 라이브러리 및 기술입니다. SQLXML은 중간 계층 구성 요소이며 FOR XML 및 OPENXML에서 제공하는 서버 쪽 XML 지원을 포함하지 않습니다. SQLXML은 관계형 원본 데이터에서 XML을 생성하고 다시 관계형 테이블에 관계형 정보를 나타내는 XML을 로드하기 위한 스키마 중심의 매핑 방법을 제공합니다. SQLXML 클래스는 XML 지원을 SQL Server 2000 데이터베이스 이상 버전에 제공합니다.

Microsoft SQL Server 2005에는 XML 데이터 형식의 형태로 된 XML에 대한 기본 제공 지원이 추가되었습니다. XML 데이터는 XML 데이터 형식 열에 원시적으로 저장될 수 있습니다. 그 뿐만 아니라 XML 데이터 형식 열은 XML 스키마 모음을 이 열에 연결함으로써 한층 더 제약될 수 있습니다. XML 데이터 형식 열에 저장된 XML 값은 XQuery 및 XML DML(Data Modification Language)을 통해 조작될 수 있습니다. 쿼리 성능 향상을 위해 인덱스를 XML 데이터를 기반으로 구축할 수 있습니다. 또한 FOR XML 및 OPENXML이 새로운 XML 데이터 형식을 지원하도록 향상되었습니다.

이전의 다양한 SQL Server 버전에 제공된 XML 기능과 더불어 XML 데이터를 저장 및 처리하기 위해 SQL Server 2005에 새롭게 도입된 기능은 XML 데이터를 XML 응용 프로그램에 저장하고 처리할 수 있는 여러 방법을 개발자에게 제공합니다. SQL Server 2005에서 제공하는 대체 접근 방법을 사용하여 XML 응용 프로그램을 구축할 수 있는 방법에는 여러 가지가 있으므로 올바른 선택을 할 수 있도록 다양한 기술 시나리오, 트레이드 오프 및 시너지 효과를 이해하는 것이 중요합니다. 이 문서는 SQL Server 2005에서의 XML 응용 프로그램을 개발을 위해 적합한 대안을 선택하는 데 도움이 되는 지침을 제공합니다.



XML 사용 시나리오



XML이 사용되는 영역은 크게 다음과 같이 분류될 수 있습니다.



비즈니스 통합을 위한 XML: 엔터프라이즈 응용 프로그램 통합(EAI)으로도 알려진 비즈니스 통합은 A2A(application-to-application), B2B(business-to-business) 및 B2C(business-to-consumer) 응용 프로그램을 포함합니다. 서로 다른 시스템에서 작동하는 응용 프로그램은 XML 기반 메시지를 사용하여 서로 통신합니다.
콘텐츠 관리를 위한 XML: XML에 기반한 콘텐츠 관리 시스템을 사용하여 사용자가 XML 문서를 저장, 검색, 수정 및 쿼리할 수 있습니다. 이 시스템은 XML 문서를 원시 형식으로 저장합니다.

이제 위에서 언급한 범주에 속하는 몇 가지 시나리오를 설명하겠습니다. SQL Server 2005/Visual Studio 2005 환경에서 사용할 수 있는 여러 XML 옵션 처리에 대한 자세한 설명과 함께 이 시나리오에 대한 솔루션이 다음에 이어지는 섹션에 제공되어 있습니다.



시나리오 1: 보험금 청구

인터넷 상에서 서비스를 제공하는 한 자동차 보험 회사는 보험 구매자 또는 대리인이 회사의 웹 사이트를 통해 보험금 청구를 신청할 수 있도록 합니다. 이러한 청구는 본사에 있는 중앙 집중식 시스템에서 처리됩니다. 처리가 완료되면 이 시스템은 청구에 관련된 특정 정보를 특정 XML 형식으로 저장해야 합니다. 법적인 목적으로 이러한 XML 문서의 정확한 복사본이 이 시스템에서 유지 관리되어야 합니다. 이 시나리오는 콘텐츠 관리를 위한 XML 사용을 보여줍니다.



시나리오 2: 자동차 제조업체 및 부품 공급업체 사이의 데이터 교환 I

자동차 제조업체는 회사에 필요한 부품을 조달하기 위해 여러 부품 공급업체와 상호 작용합니다. 현재 이 제조업체는 공급업체들로부터 송장을 받습니다. 그러고 나면 이 송장에 해당하는 데이터가 기존의 송장 처리 시스템에 수동으로 전달됩니다. 송장 처리 시스템은 데이터를 관계형 형식으로 저장합니다. 이제 제조업체는 기존 송장 처리 시스템으로의 송장 데이터 전달 절차를 자동화하기를 원합니다. 이 시나리오는 비즈니스 통합을 위한 XML 사용의 예입니다.



시나리오 3: 자동차 제조업체와 부품 공급업체 사이의 데이터 교환 II

이 시나리오에는 이전 시나리오에서 언급한 대로 여러 부품 공급업체와 상호 작용하는 자동차 제조업체가 관련됩니다. 제조업체의 현 시스템은 공급업체가 송장의 현황을 확인하거나 제조업체의 지불 지침 복사본을 얻기 위한 기능을 제공하지 않습니다. 현재로서는 단지 전화를 통해서만 이 정보를 공급업체가 이용할 수 있습니다. 공급업체가 이 작업을 자동으로 수행할 수 있도록 자동차 제조업체는 이 정보를 웹 상에 노출할 수 있어야 합니다. 이 시나리오는 비즈니스 통합을 위한 XML의 사용을 보여줍니다.



시나리오 4: 콘텐츠 관리 시스템

의료, 법 및 기술 분야의 정보를 웹, 책, CD-ROM 등의 다양한 채널을 통해 고객들에게 제공하는 한 회사는 고객들에게 더 빠른 시간 내에 고품질의 콘텐츠를 전달할 수 있는 콘텐츠 관리 시스템을 구축하기를 원합니다. 이 시나리오는 콘텐츠 관리를 위한 XML의 사용을 예시합니다.



시나리오 5: 고객 설문조사

인터넷에서 항공권 예약 서비스를 제공하는 한 회사는 현 시즌에 고객들에게 가장 인기 있는 여행 목적지를 확인하기 위해 각 계절마다 설문조사를 실시합니다. 각 계절에 사용되는 질문은 서로 다르며 이 질문은 나중에 변경될 수 있습니다. 회사는 정보를 분석하고 분석 결과는 최대 고객 수의 요구를 충족할 수 있는 패키지 여행 정책을 설계하는 데 사용됩니다. 이 시나리오는 콘텐츠 관리를 위한 XML의 사용으로 분류될 수 있습니다.



.NET Framework의 XML 클래스

Microsoft .NET Framework에는 XML 기반 제품 개발을 위한 탁월한 지원 기능이 있습니다. .NET Framework에서 XmlTextReader, XmlTextWriter, XmlDocument, XmlValidatingReader 등과 같은 핵심 클래스는 모든 XML 클래스의 루트 이름 공간인 System.Xml 이름 공간에서 사용할 수 있습니다. 이들 핵심 클래스는 사용자가 스트림 기반 및 DOM 기반(문서 개체 모델 기반) 탐색/액세스 모델을 모두 사용하여 XML 문서를 읽고 쓰고 확인할 수 있게 해줍니다. System.Xml 이름 공간은 다음과 같은 하위 이름 공간을 포함합니다.



System.Xml.Schema - XML 스키마 정의 언어(XSD) 스키마를 다루는 클래스를 포함합니다.
System.Xml.Serialization - XML 형식 문서 또는 스트림으로의 개체 직렬화를 위한 클래스를 제공합니다.
System.Xml.XPath - Xpath 식을 사용하여 XML 문서를 탐색하기 위한 클래스를 포함합니다.
System.Xml.Xsl - XSLT(Extensible Stylesheet Transformations) 수행을 위한 클래스를 포함합니다.



System.Xml 이름 공간의 향상 기능

Visual Studio 2005에서 XsltCommand와 같은 새로운 클래스와 XmlDocument와 같은 기존 XML 클래스의 기능 향상은 XML 문서 수정, XSL 변환 적용 등을 포함하여 XML 데이터에 대해 다양한 작업을 수행하는 데 사용될 수 있습니다.

System.Xml 이름 공간의 XML 클래스와 관련된 Visual Studio 2005의 몇 가지 향상된 기능은 다음과 같습니다.



XML 스키마 유효성 검사 지원이 XmlDocument 클래스에 추가되었습니다.
XmlReader 및 XmlWriter 클래스는 상당한 성능 개선을 제공하고 XML 스키마 형식을 지원하기 위해 향상되었습니다. 또한 생성된 형식을 구성하는 XmlReaderSettings 및 XmlWriterSettings 클래스를 사용하여 XmlReader 및 XmlWriter의 인스턴스를 만들기 위한 보다 쉬운 방법을 제공하기 위해 정적 Create 메서드가 추가되었습니다.

System.Xml의 기능 향상에 대한 자세한 내용은 Visual Studio 2005 및 .NET Framework 2.0 릴리스를 위한 System.Xml의 새로운 기능 (영문) 백서를 참조하십시오.

System.Xml 이름 공간의 클래스는 사용자 지정 XML 구문 분석, 조작 및 저장 논리를 구현하는 데 사용될 수 있습니다. SQL Server 2005의 공용 언어 런타임(CLR) 호스팅 기능을 활용하고 Visual Studio 2005의 XML 클래스를 사용하여 XML 처리를 중간 계층 또는 데이터베이스 계층에서 수행할 수 있습니다.

.NET Framework XML 클래스의 사용에는 XML 문서를 데이터베이스에 [n]varchar(max) 또는 varbinary(max) 형식의 열로 또는 파일 시스템에 파일로 저장하고, System.Xml 이름 공간의 클래스를 사용하여 중간 계층 또는 데이터베이스에서 이러한 문서를 처리하는 작업이 포함됩니다. .NET Framework의 XML 클래스는 또한 XML 데이터 형식으로 저장된 데이터에 작업하는 데 사용될 수 있습니다.

.NET Framework XML 클래스는 다음의 경우에 적합합니다.


스트리밍 파서, 문서 형식 정의(DTD) 및 XSD 유효성 검사, XSLT 처리 등과 같은 모든 .NET Framework XML 기능에 액세스할 수 있기를 원합니다.
단순히 XML 문서의 데이터 저장소로서 SQL Server를 사용하기를 원하며 데이터베이스 내부에 세분화된 액세스는 필요하지 않습니다.
.NET Framework XML 클래스를 사용하여 XML 문서의 대부분이나 전부를 처리하며 문서 수준에서 업데이트를 수행합니다.

XML을 저장하는 데 [n]varchar(max), varbinary(max) 또는 XML 데이터 형식을 사용할 수 있습니다.

[n]varchar(max) 또는 varbinary(max)를 사용할 경우 다음과 같은 이점을 얻을 수 있습니다.


공백 및 서식 지정을 포함하여 문서의 정확한 복사본으로 XML 문서의 원문 충실도를 유지합니다.
응용 프로그램은 문서 전체에 대한 삽입 및 검색 작업을 위해 가능한 가장 빠른 성능을 얻습니다.

XML 데이터 형식 사용의 이점은 후반부의 섹션에 설명되어 있습니다.



중간 계층에서 XML 처리 수행

XML 처리는 .NET Framework에서 제공하는 다양한 XML 클래스를 사용하여 중간 계층에서 수행될 수 있습니다. 앞서 언급한 대로, 이 접근 방법을 채택하면 XML 문서는 데이터베이스에 [n]varchar(max) 형식 또는 XML 형식의 열로 저장되거나 파일 시스템에 파일로 저장될 수 있습니다. 중간 계층에서는 이러한 문서를 데이터베이스에서 가져와서 다음과 같이 사용자의 요구 사항에 따라 처리할 수 있습니다.



XML 문서를 읽어야 하는 경우 XmlReader.Create() 메서드를 통해 생성된 XmlReader를 사용하여 데이터베이스에서 얻은 문서를 로드합니다. Read()를 사용하여 문서를 탐색합니다. XmlReader 클래스는 XML 문서에 대해 가장 빠른 읽기 전용, 전진 전용의 캐시되지 않은 액세스를 제공합니다.
XML 문서에 대한 쓰기 액세스 권한이 필요하고 XML 데이터에 대한 완전한 탐색 액세스 권한이 요구되는 경우, XmlDocument 클래스를 사용하여 XML 문서를 로드하고 액세스합니다. XmlDocument는 .NET Framework에서 문서 개체 모델(DOM)의 구현으로서 이는 XML 문서의 탐색 및 편집을 가능하게 하는 XML 문서의 인-메모리 트리 표시입니다.
DTD/XSD에 기반하여 XML 문서의 유효성을 검사하거나 런타임에 XSD 정보를 얻어야 하는 경우 XmlReader 클래스를 사용합니다. XmlReaderSettings 클래스에서 true로 설정된 XsdValidation 또는 DTDValidation 중 하나를 이용하여 메서드를 만듭니다. 또한 ValidationEventHandle() 이벤트 처리기는 읽는 동안 발생한 유효성 검사 오류를 처리하도록 설정할 수 있습니다.
XSL 변환을 XML 문서에 적용해야 하는 경우 XslCommand 클래스를 사용하여 XML 문서를 로드하고 변환을 적용하기 위해 XPathDocument 클래스를 사용합니다. XPathDocument 클래스는 XSLT를 사용하여 XML 문서 처리를 위한 빠른 고성능 캐시를 제공합니다.
XPath 식을 사용하여 XML 문서를 쿼리해야 하는 경우 XPathDocument(읽기 전용) 또는 XmlDocument(읽기/쓰기)를 사용하여 XML 문서를 로드합니다. CreateNavigator() 메서드를 사용하여 XPathNavigator의 인스턴스를 만들고 인수로 필요한 Xpath 식을 XPathNavigator의 Select() 메서드에 전달합니다.



데이터베이스에서 XML 처리 수행

SQL Server 2005와 CLR의 통합으로 인해 개발자들은 .NET Framework에서 제공하는 XML 클래스를 사용하여 데이터베이스 계층에서도 처리를 수행할 수 있습니다. 이 통합은 .NET Framework에서 지원하는 모든 언어에서 저장 프로시저 작성, 함수, 트리거 및 사용자 정의 형식의 기능을 제공합니다. 또한 CLR 호스팅은 완전한 .NET Framework 기반 클래스 라이브러리에 대한 액세스 권한도 제공합니다. 결과적으로 앞 섹션에서 설명한 여러 XML 처리 옵션은 데이터베이스에서도 수행될 수 있습니다.

CLR 통합 사용의 이점은 다음과 같습니다.



관리되는 코드에서 데이터베이스 개체를 만들기 위해 C# 및 Visual Basic .NET과 같은 개체 지향 언어를 사용할 수 있는 기능을 제공합니다.
관리되는 데이터베이스 개체는 이전 SQL Server 버전에서 사용할 수 있는 확장 저장 프로시저보다 안전합니다.
사용자 정의 데이터 형식 및 사용자 정의 집계를 정의할 수 있는 기능을 제공합니다.
특정 조건에서, 컴파일된 관리 데이터베이스 개체가 Transact-SQL에 대해 향상된 성능을 제공합니다.

SQL Server 2005에서 데이터베이스 개발자는 저장 프로시저, 트리거 및 사용자 정의 함수를 위한 두 가지 옵션을 가질 수 있습니다. 이 옵션은 Transact-SQL 및 .NET Framework에서 사용할 수 있는 모든 언어(C# 또는 Visual Basic .NET)입니다. 언어의 선택은 데이터에 수행하는 작업의 종류에 따라 달라집니다. Transact-SQL은 코드가 절차적 논리를 거의 또는 전혀 사용하지 않고서 데이터 액세스를 대부분 수행하는 경우에 가장 적합합니다. 관리되는 클래스는 문자열 처리, 날짜 작업, 시스템 리소스 액세스, 파일 액세스 또는 이미지 처리와 같이 계산 집중적인 함수 및 절차에 가장 적합합니다.

데이터베이스 계층에서 .NET Framework의 XML 클래스 사용에 따르는 단계는 다음과 같습니다.


관리되는 어셈블리 개발. .NET Framework에서 사용할 수 있는 모든 언어를 사용하여 처리 기능을 어셈블리로 구현하고 이 어셈블리를 DLL로 패키지로 만듭니다. 또한 어셈블리는 다른 어셈블리를 참조할 수 있습니다.
어셈블리 등록 및 사용 권한 부여. .NET Framework를 사용하여 개발한 어셈블리는 CREATE ASSEMBLY T-SQL 문을 사용하여 SQL Server에 등록될 수 있습니다. 또한 어셈블리를 등록하는 동안 어셈블리에 허용되는 코드 액세스 권한을 지정할 수 있습니다. 어셈블리는 DROP ASSEMBLY T-SQL 문을 사용하여 등록 취소할 수 있습니다.
T-SQL에 관리되는 형식 노출. 어셈블리에서 제공한 처리 기능은 스칼라 반환 사용자 정의 함수, 테이블 반환 사용자 정의 함수, 사용자 정의 절차(UDP) 또는 사용자 정의 트리거를 통해 T-SQL에 노출될 수 있습니다. 스칼라 사용자 정의 함수는 모든 스칼라 식에서 사용될 수 있습니다. 테이블 반환 사용자 정의 함수는 모든 FROM 절에서 사용될 수 있습니다. UDP는 EXEC 문에서 호출될 수 있습니다.



시나리오 분석

보험금 청구는 청구 ID, 정책 번호, 청구 중재 데이터 등과 같은 데이터 중심 정보와 사고 손해에 대한 설명과 같은 문서 중심 정보를 포함합니다. XML 문서는 데이터 중심 및 문서 중심 정보 집계에 있어 탁월합니다. 제공된 시나리오 (시나리오 1: 보험금 청구 섹션 참조)에서의 주요 요구 사항은 보험금 청구의 정확한 복사본을 XML 형식으로 유지 관리해야 한다는 점입니다. SQL Server에서는 보험금 청구를 [n]varchar(max) 또는 varbinary(max) 형식의 열로 데이터베이스에 저장하여 이 요구 사항을 쉽게 충족할 수 있습니다. 중요하지 않은 공백, 특성 순서, 이름 공간 접두사 및 XML 선언 등과 같은 정보를 보존해야 하는 경우에는 문서를 저장하는 데 XML 데이터 형식을 사용하지 않아야 한다는 점에 주의해야 합니다.



이점

저장소 매체로서 [n]varchar(max) 또는 varbinary(max)를 사용하고 XML 문서의 조작을 위해 System.Xml 이름 공간의 클래스를 사용하는 경우의 이점은 다음과 같습니다.



XML 문서의 스키마를 변경해야 하는 경우에 유연합니다. 또한 서로 다른 스키마를 사용하는 XML 문서를 동일한 열에 저장하려는 경우에도 유용합니다.
XML을 저장하는 데 [n]varchar(max) 또는 varbinary(max)를 사용하는 경우 XML 문서에 대한 원문 충실도를 제공합니다. 이는 보험금 청구와 같은 법적 문서를 다루는 응용 프로그램의 요구 사항이 될 수 있습니다.
XML 인스턴스를 파일 시스템에 파일로 저장할 때와 비교하여 트랜잭션 업데이트, 동시 액세스, 백업, 복제 등과 같은 데이터베이스 기능을 활용할 수 있습니다.
이 접근 방법은 데이터베이스에서 제공하는 XML 지원에 의존하지 않으므로 응용 프로그램이 SQL Server, Oracle 등과 같은 여러 데이터베이스 서버를 지원하도록 쉽게 확장될 수 있습니다.
클라이언트 시스템의 처리 능력을 사용할 수 있어 서버에서 로드가 감소합니다. CPU를 많이 사용하는 XML 처리를 중간 계층에서 수행함으로써 서버가 로드의 일부를 덜고 다른 중요한 작업에 사용될 수 있습니다.
문서 수준 삽입 및 검색 작업에 최상의 성능을 제공합니다.
XSL 변환과 같은 복잡한 작업을 데이터베이스에서 저장 프로시저, 트리거 또는 함수로 수행할 수 있습니다.



제한

저장을 위해 [n]varchar(max) 또는 varbinary(max) 사용 시 및 XML 인스턴스 처리를 위해 System.Xml 이름 공간의 클래스 사용 시 제한 사항은 다음과 같이 요약될 수 있습니다.



XML 데이터 형식 (SQL Server 2005의 XML 데이터 형식 섹션 참조) 또는 SQLXML 옵션(SQLXML 섹션 참조)과 비교하여 코딩이 더 복잡합니다. 데이터베이스 논리가 단순하다 하더라도 중간 계층 또는 데이터베이스 계층에서 XML의 구문 분석 및 처리를 다루는 코드의 구현이 복잡해집니다.
이 솔루션을 구현하는 데 필요한 코드의 양이 많습니다. 결과적으로 SQLXML 옵션과 비교하여 유지 관리 비용 또한 높습니다.
XML 문서가 데이터베이스에 [n]varchar(max)로 저장된 이후에는 XML 문서에서 세분화된 업데이트, 삽입 또는 삭제가 불가능합니다. 이 경우에는 쿼리 기능이 제한적입니다.
[n]varchar (max) 데이터 형식에 저장될 수 있는 XML 문서의 크기는 2GB로 제한됩니다.
이런 식으로 저장된 문서의 열을 XML 콘텐츠를 기준으로 검색하는 것은 비용이 매우 많이 소요됩니다.



.NET Framework에서 XML 클래스 사용의 예

이 문서의 앞부분에서 설명한 보험금 청구 시나리오를 생각해 봅시다(시나리오 1: 보험금 청구 섹션 참조). 보험 회사는 청구가 승인되면 법적인 목적으로 청구 정보를 저장하기를 원합니다. 청구 정보는 데이터베이스에 [n]varchar(max) 데이터 형식으로 저장될 수 있습니다.

응용 프로그램의 흐름은 다음과 같습니다.

1. 청구 처리 후 응용 프로그램은 청구를 승인하거나 거부합니다. 2. System.Xml 이름 공간의 클래스를 사용하여 청구용 XML 문서가 생성됩니다. 3. 생성한 XML 문서는 저장 프로시저로 전달됩니다. 4. 저장 프로시저는 XML 문서를 테이블에 삽입합니다. 다음 코드 예제는 시스템에서 사용할 수 있는 청구 정보를 사용하여 XML 문서를 생성하고 XML 문서를 데이터베이스에 삽입합니다.


using System; using System.Xml; using System.IO; using System.Text; using System.Data; using System.Data.SqlClient; namespace InsuranceClaim { class Insurance { static void Main(string[] args) { Insurance.InsertInsuranceClaim(); } static void InsertInsuranceClaim() { StringWriter strWriter = null; XmlWriter writer = null; XmlWriterSettings settings = null; SqlConnection connection = null; SqlCommand command = null; try { strWriter = new StringWriter(); settings = new XmlWriterSettings(); //Use indenting for readability. settings.Indent = true; settings.Encoding = System.Text.Encoding.UTF8; writer = XmlWriter.Create(strWriter, settings); //Write the XML delcaration. writer.WriteStartDocument(); writer.WriteStartElement("InsuranceClaim"); writer.WriteStartElement("ClaimInfo"); writer.WriteElementString("ClaimID", "C1234"); writer.WriteElementString("ClaimType", "3"); writer.WriteStartElement("SettlementDetails"); writer.WriteStartElement("PaymentDetails"); writer.WriteElementString("PaidTo", "Jeff"); writer.WriteElementString("Amount", "2000"); writer.WriteElementString("Date", "05/12/2002"); writer.WriteElementString("ApprovedBy", "Mike"); writer.WriteEndElement();//End of PaymentDetails writer.WriteEndElement();//End of SettlementDetails writer.WriteEndElement();//End of ClaimInfo writer.WriteStartElement("DamageReport"); writer.WriteString("Minor accident occured on "); writer.WriteElementString("Address", "ABC Street, Sample City, Sample State"); writer.WriteString(" due to "); writer.WriteElementString("Cause", "bad weather"); writer.WriteString(" resulted in damage to "); writer.WriteElementString("DamagedItem", "Head Lights"); writer.WriteElementString("DamagedItem", "Engine"); writer.WriteEndElement();//End of DamageReport writer.WriteEndElement();//End of InsuranceClaim writer.WriteEndDocument(); //Write the XML to file and close the writer. writer.Flush(); connection = new SqlConnection(); connection.ConnectionString = @"server=localhost; database=AdventureWorks;Integrated Security=SSPI;"; command = connection.CreateCommand(); command.CommandText = "InsertInsuranceClaim"; command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add("@CustomerID", System.Data.SqlDbType.Char); command.Parameters.Add("@Claim", System.Data.SqlDbType.VarChar); String xml = strWriter.ToString(); string strCustomerID = "1001"; command.Parameters[0].Value = strCustomerID; command.Parameters[0].Size = strCustomerID.Length; command.Parameters[1].Value = xml; command.Parameters[1].Size = xml.Length; connection.Open(); command.ExecuteNonQuery(); connection.Close(); } finally { if (connection.State == ConnectionState.Open) connection.Close(); if (writer != null) writer.Close(); if (strWriter != null) strWriter.Close(); } } } }



다음은 데이터베이스 테이블을 만드는 스크립트입니다.


CREATE TABLE [InsuranceClaim]( [CustomerID] [char](4) NOT NULL, [Claim] [varchar](max) NOT NULL, [ModifiedDate] [datetime] NOT NULL DEFAULT (getdate()) )



다음 저장 프로시저는 XML 문서를 데이터베이스에 삽입하는 데 사용됩니다.


CREATE PROCEDURE [dbo].[InsertInsuranceClaim] @CustomerID [char](4), @Claim [varchar](max) AS BEGIN SET NOCOUNT ON; INSERT INTO [InsuranceClaim] ( CustomerID, Claim ) VALUES ( @CustomerID, @Claim ) END;



SQLXML



SQL Server 2000에 도입된 SQLXML은 클라이언트 쪽의 XML 처리와 관련된 기능의 전 범위를 포괄합니다. SQLXML은 관계적으로 구조화된 데이터를 설명하는 XML을 사용하여 SQL Server 데이터베이스에서 관계형 데이터의 원활한 통합을 가능하게 하는 일련의 라이브러리 및 기술입니다.

SQL 2000 이전에는 개발자들이 관계형 데이터와 XML 형식의 데이터 간에 상호 작용을 위한 코드 레이어를 제공해야 했습니다. 하지만 SQLXML의 등장으로 관계형 데이터와 XML 사이의 연결이 제공되었으므로 작업이 더욱 편리해졌습니다. 이 문서에 설명된 항목은 SQLXML 관리 클래스에 제한됩니다. 이 라이브러리의 다른 기능 적용 가능성에 관한 자세한 내용은 MSDN에서 SQLXML (영문) 페이지를 참조하십시오.

SQLXML은 XML 지원을 위해 SQL Server에 도입된 많은 기능으로 구성됩니다. 이러한 기능은 다음과 같습니다.

- 클라이언트 쪽에서 쿼리 결과를 XML로 변환하는 기능

주석 달린 XSD 매핑 스키마 파일을 사용하여 관계형 데이터의 XML 뷰를 만드는 기능 - 다음을 수행할 수 있습니다.
XML 뷰에 대한 XPath 쿼리 정의
updategrams로 알려진 XML 템플릿을 사용하여 데이터베이스의 데이터에 삽입, 업데이트, 삭제 수행
XML 대량 로드 작업 수행

- HTTP를 사용하여 SQL Server에 액세스하는 기능 - 다음을 수행할 수 있습니다.


URL에 SQL 문 지정
URL에 템플릿 쿼리 지정
URL에 템플릿 파일 지정
URL에 주석 달린 XSD 매핑 스키마 파일에 대해 작성된 XPath 쿼리 지정

저장 프로시저, 사용자 정의 함수 및 템플릿 쿼리에서 제공되는 기능을 SOAP 기반 웹 서비스로 노출하는 기능 SQLXML 관리 클래스를 사용하여 SQLXML에서 제공되는 XML 기능의 이점을 활용하도록 .NET Framework에서 코드를 작성하는 기능

클라이언트 쪽 XML 서식 설정. 클라이언트 쪽에서 FOR XML 절을 지정하면 중간 계층에서 쿼리에 대한 응답으로 서버가 반환한 행 집합에 대해 FOR XML 변환을 수행합니다. 클라이언트 쪽에서 XML 서식 설정을 수행하려면:


SQLXML 관리 클래스를 사용 중인 경우 SqlXmlCommand 개체의 ClientSideXml 속성을 True로 설정합니다.
SQLXMLOLEDB 공급자를 사용 중인 경우 ClientSideXML 공급자별 속성을 True로 설정합니다.
템플릿 쿼리를 사용 중인 경우 템플릿에 client-side-xml="1"를 지정합니다.
HTTP를 사용하여 SQL Server에 액세스하는 경우 설정 탭의 가상 디렉터리에서 클라이언트에서 실행 옵션을 선택합니다.
클라이언트 쪽에서 FOR XML과 함께 사용할 수 있는 XML 서식 설정 모드는 RAW, NESTED 및 EXPLICIT입니다. RAW 모드가 사용되면, 결과 XML 문서는 쿼리 결과의 각 행에 대한 XML 요소와 행의 각 열에 해당하는 특성을 포함합니다. NESTED 모드가 지정되면, 기본 테이블 이름이 결과 XML 문서의 요소 이름으로 반환됩니다. EXPLICIT 모드는 쿼리 자체에 원하는 XML의 형식을 지정할 수 있도록 허용함으로써 모든 형태의 XML 문서를 생성합니다.
XML 뷰. XML 뷰는 관계형 데이터와 XML 데이터 간의 매핑을 정의하는 주석 달린 XSD 스키마를 사용하여 생성됩니다. 이 XML 뷰는 XPath 쿼리를 사용하여 쿼리할 수 있습니다. 또한 XML 뷰를 통해 노출된 관계형 데이터를 수정한 다음 updategrams를 사용하여 수정 사항을 데이터베이스에 제출할 수 있습니다. 뿐만 아니라 XML 뷰는 COM 기반 XML Bulk Load 개체의 도움으로 대형 XML 문서를 데이터베이스에 삽입하는 데도 유용합니다.
SQL Server에 대한 HTTP 액세스. SQLXML은 IIS Virtual Directory Management 유틸리티로 알려진 유틸리티를 제공하며 이 유틸리티를 사용하여 HTTP를 통해 SQL Server의 XML 기능을 노출하도록 IIS 가상 디렉터리를 설정할 수 있습니다. URL에서 직접 SQL 문, 저장 프로시저, 템플릿 쿼리, 템플릿 파일 및 XPath 쿼리를 지정하기 위한 지원은 SQL ISAPI 확장 기능을 통해서 제공됩니다.
SQLXML의 웹 서비스 지원. SQL Server의 기능을 SOAP 기반 웹 서비스로 노출하기 위한 지원은 SQLXML 3.0에서 추가되었습니다. 이 기능을 사용하면 SQL Server가 클라이언트로부터 SOAP HTTP 요청을 받아 저장 프로시저, 사용자 정의 함수 및 템플릿을 실행할 수 있습니다.
SQLXML 관리 클래스. .NET Framework에서의 SQLXML 기능 액세스는 SQLXML 관리 클래스를 통해 제공됩니다. SQLXML에는 세 가지의 관리되는 클래스가 있습니다.
SqlXmlCommand - 데이터베이스 연결 및 쿼리 실행 측면을 다룹니다.
SqlXmlParameter - 쿼리에 매개 변수를 지정하도록 도와줍니다.
SqlXmlAdapter - .NET Framework에서 데이터 집합과의 상호 작용을 촉진합니다.

SQLXML 관리 클래스를 사용하여 다음 작업을 수행할 수 있습니다.


FOR XML 절이 있는 SQL 쿼리 실행
매핑 스키마에 대한 XPath 쿼리 실행
템플릿 쿼리 실행
템플릿 쿼리 파일 실행
updategram 실행
DiffGram 실행

관계형 데이터를 XML 문서로 노출하기 위해 SQLXML을 사용하는 것은 다음과 같은 경우에 적합한 선택입니다.


응용 프로그램이 고도로 구조화되고 관계형 테이블에 잘 매핑되는 XML 데이터를 받습니다.
응용 프로그램이 외부 응용 프로그램에서 받은 대형 XML 문서를 데이터베이스에 로드해야 하고 이 문서를 관계형 형식으로 유지해야 합니다.
응용 프로그램이 문서 순서를 반드시 지켜야 할 필요가 없습니다.
응용 프로그램이 동일한 데이터를 여러 데이터 소비자에게 여러 다른 형식으로 제공해야 합니다.
DML 작업 성능이 응용 프로그램에 대단히 중요합니다.
응용 프로그램이 쿼리 최적화를 위한 최적기의 모든 가능성을 활용해야 할 필요가 있습니다.
응용 프로그램이 세분화된 데이터 조작을 수행합니다.
응용 프로그램이 기존의 관계형 데이터를 XML로 노출해야 합니다.



시나리오 분석

XML 사용 시나리오에 설명된 첫 번째 데이터 교환 시나리오(시나리오 2: 자동차 제조업체와 부품 공급업체 간의 데이터 교환 I 섹션 참조)에서, 자동차 제조업체와 여러 부품 공급업체 간의 상호 작용은 SQLXML 사용의 전형적인 경우를 나타냅니다. 제조업체는 송장 데이터를 교환하기 위해 여러 공급업체들과 통신해야 합니다. 제안된 솔루션은 이 문제를 해결하기 위해 웹 서비스와 SQLXML을 사용합니다. 제조업체는 공급업체가 제조업체에게 송장을 보내기 위해 사용할 수 있는 웹 서비스를 노출합니다. 웹 서비스는 송장을 공급업체 형식에서 제조업체가 사용하는 일반적인 형식으로 변환하기 위해 고객에 따라 다른 XSLT를 사용합니다. 그런 다음 웹 서비스는 송장 문서의 내용을 관계형 테이블의 열에 매핑하는 XML 뷰를 사용하여 XML 문서를 분할합니다. 기존의 송장 처리 시스템은 관계형 테이블에서 데이터를 선택하여 처리 작업을 진행할 수 있을 것입니다. 이 시나리오의 경우 XML 뷰 사용의 이점은 다음과 같습니다.



낮은 유지 관리 비용. 공급업체가 송장 스키마에 적용한 모든 변경은 공급업체에 따라 다른 XSLT 파일을 수정함으로써 쉽게 수용될 수 있습니다.
FOR XML과 비교하여 코딩이 덜 복잡합니다(관계형/XML 통합을 위한 서버 쪽 지원(FOR XML/OPENXML) 섹션 참조).
공급업체에 대해 특정 XSLT 파일을 생성함으로써 새 공급업체를 쉽게 지원할 수 있습니다.



이점

SQLXML 사용의 이점은 다음과 같이 요약될 수 있습니다.



관계형 데이터를 XML 데이터에 매핑하기 위해 주석 달린 매핑 스키마를 만드는 작업은 비교적 간단하며 서버 쪽의 FOR XML EXPLICIT보다 효과적으로 관리 가능한 솔루션입니다.
SQLXML은 FOR XML을 사용하여 만든 관계형 데이터의 읽기 전용 XML 표시와 비교하여 업데이트 가능한 양방향 XML 뷰를 만들 수 있는 기능을 제공합니다.
XSD 매핑은 주요 코드 변경 없이 변경 요청을 XML 형식으로 수용할 수 있는 기능을 제공합니다. 이로 인해 유지 관리가 쉬워집니다.
SQLXML은 클라이언트 쪽에서 XML 형식 지정을 수행할 수 있는 기능을 제공하므로 사용자가 ClientSideXML 속성을 SqlXMLCommand 클래스에 대해 true로 설정할 수 있어 서버에서 로드가 감소됩니다.



제한

부정적인 면을 들자면, SQLXML이 클라이언트 쪽에서 사용되는 경우 몇 가지 제한이 있습니다.



XML 뷰는 XML 문서의 계층이 너무 깊거나 깊이를 알 수 없을 정도로 순환하는 경우에는 적합하지 않습니다.
SQLXML은 제품 카탈로그, 뉴스 보고 등과 같은 혼합된 콘텐츠 표시 및 정렬된 데이터를 포함하는 설명 문서에는 적합하지 않습니다.
문서 순서가 유지되지 않기 때문에 원래의 XML 문서를 다시 작성하기가 어렵습니다.
XML 문서를 관계형 테이블로 세분화하면 우수한 검색 성능을 얻을 수 있지만 XML 간의 변환에는 비용이 많이 들 수 있습니다.
XSD 매핑 스키마에서 기본 매핑이 사용된 경우 데이터베이스 테이블 이름 및 열 정보가 노출될 수 있어 원하지 않은 정보 누출이 발생할 수 있습니다. 이러한 위험은 테이블 및 열에 대해 명시적인 매핑을 지정함으로써 피할 수 있습니다.
URL의 SQL 문은 신뢰할 수 있는 인트라넷에서만 사용되어야 합니다. 인터넷에서 이러한 쿼리를 사용하면 잠재적 보안 위험이 따를 수 있습니다.



SQLXML 사용의 예

이제 SQLXML의 개요 설명을 마쳤으니, 다음 단계로 SQLXML 관리 클래스가 적용될 수 있는 예제를 탐구해 봅시다. 특정 고객에 대한 판매 주문 정보를 내보내는 간단한 예제를 살펴보겠습니다. 이 예제에 사용되는 테이블은 AdventureWorks 데이터베이스에서 이용할 수 있습니다.

데이터베이스의 데이터는 프레젠테이션 계층에서 표시될 수 있는 XML 형식으로 클라이언트에서 사용할 수 있어야 합니다. 그런 다음 SQL 데이터베이스의 관계형 데이터가 어떻게 SQLXML 클래스를 사용하여 XML 데이터로 조작될 수 있는지 확인하게 될 것입니다. 매핑 XML 스키마가 XML 노드 이름을 테이블 필드에 매핑하고 조작하는 데 사용됩니다. SQLXML 관리 라이브러리를 사용한 관계형 데이터 조작에 관한 자세한 내용은 MSDN에서 SQLXML (영문) 페이지를 참조하십시오.

아래의 주석 달린 XSD 스키마는 관계형 테이블 [Sales.Customer], [Sales.SalesOrderHeader], [Sales.SalesOrderDetail]과 고객에 대한 판매 주문 정보의 대상 XML 표시 사이에 매핑을 정의합니다. 또한 아래 XSD 스키마에서 보는 바와 같이 XSD 매핑 스키마를 사용하여 XML에서의 부모(parent)-자식(child) 관계도 정의될 수 있습니다.


http://www.w3.org/2001/XMLSchema xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> parent="Sales.Customer" parent-key="CustomerID" child="Sales.SalesOrderHeader" child-key="CustomerID" /> parent="Sales.SalesOrderHeader" parent-key="SalesOrderID" child="Sales.SalesOrderDetail" child-key="SalesOrderID" /> sql:relationship="CustomerOrderHeader" maxOccurs="unbounded" > sql:relation="Sales.SalesOrderDetail" sql:relationship="OrderHeaderOrderDetail" maxOccurs="unbounded" > type="xsd:integer" />

class ExportOrders { /// <SUMMARY> /// This method use SqlXmlCommand class to select the records from /// Sales.Customer, Sales.SalesOrderHeader and Sales.SalesOrderDetail /// tables.The data is fetched as such from server and formatted into /// xml at client side. Note that the ClientSideXml is set to true. /// </SUMMARY> static void Main(string[] args) { if (args.Length < 1) { Console.WriteLine("Usage"); Console.WriteLine("CustomerOrders <CUSTOMERID>[OrderID]"); return; } try { StringBuilder strBuilder = new StringBuilder(); strBuilder.Append("/Customer[@CustomerID='"); strBuilder.Append(args[0]); strBuilder.Append("']"); if (args.Length > 1) { strBuilder.Append("/Order[@SalesOrderID='"); strBuilder.Append(args[1]); strBuilder.Append("']"); } SqlXmlCommand xmlCommand = new SqlXmlCommand(@"Provider= SQLOLEDB; Server=localhost; database=AdventureWorks; Integrated Security=SSPI;"); xmlCommand.ClientSideXml = true; xmlCommand.RootTag = "CustomerOrders"; xmlCommand.SchemaPath = @"CustomerOrderDetails.xsd"; xmlCommand.CommandType = SqlXmlCommandType.XPath; xmlCommand.CommandText = strBuilder.ToString(); Stream reader = xmlCommand.ExecuteStream( ); FileStream fsOut = File.Create("CustomerOrder.xml"); StreamWriter sw = new StreamWriter(fsOut); using (StreamReader sr = new StreamReader(reader)) { sw.Write(sr.ReadToEnd()); } sw.Flush(); sw.Close(); fsOut.Close(); } catch (Exception exception) { Console.WriteLine( exception.ToString() ); } } }


위에서 제공한 방법은 명령줄 인수로 지정한 Customer ID에 대한 판매 주문 정보를 응용 프로그램으로 내보냅니다. 데이터는 클라이언트 쪽에서 XML 형식으로 변환되므로 서버 쪽에서 성능 문제를 피할 수 있습니다. 위에서 제공한 주석 달린 XSD 스키마 매핑은 CustomerOrderDetails.xsd로 저장되어야 위 코드 단편이 적절하게 작동될 수 있습니다.

참고 데이터베이스에서 데이터를 XML로 가져오는 데 필요한 코드의 양은 예제에서 볼 수 있듯이 아주 적습니다.



관계형/XML 통합을 위한 서버 쪽 지원(FOR XML/OPENXML)



SELECT 문의 FOR XML 확장을 사용하여 서버 쪽에서 SQL 쿼리 결과를 XML 문서로 반환하기 위한 지원이 SQL Server에 제공되어 있습니다. 한편 OPENXML 키워드는 XML 문서에서 행 집합을 추출할 수 있는 기능을 제공합니다.



FOR XML

서버 쪽 FOR XML은 네 가지 XML 변환 모드 RAW, AUTO, EXPLICIT, PATH를 지원합니다.

기본적으로, RAW 모드는 쿼리 결과 집합의 각 행을 XML 요소에 매핑하고 행의 각 열을 특성에 매핑합니다. ELEMENTS 옵션이 ROW 모드로 지정된 경우 행의 각 열은 행에 대해 생성된 요소의 하위 요소에 매핑됩니다. 또한 XMLSCHEMA 옵션을 지정하여 생성된 XML의 인라인 스키마를 요청할 수 있습니다.

AUTO 모드는 기본적으로, SELECT 절에 최소한 하나의 열이 나열된 FROM 절의 각 테이블이 XML 요소에 매핑되고 SELECT 절에 나열된 열이 특성(또는 ELEMENTS 옵션이 지정된 경우 하위 요소)에 매핑되는 경우 중첩된 XML 요소를 생성하기 위한 지원을 제공합니다.

EXPLICIT 모드는 쿼리 결과에서 생성된 XML의 형태를 최대한 제어할 수 있게 해줍니다. 이 모드를 이용하면 쿼리 자체에 원하는 XML에 대해 형식을 지정함으로써 어떠한 형태의 XML이라도 생성할 수 있습니다.

EXPLICT 모드를 사용하여 복잡한 XML 문서를 작성하는 일은 번거롭습니다. PATH 모드를 중첩 FOR XML 쿼리를 작성하는 기능 및 XML 형식 인스턴스를 반환하는 TYPE 지시어와 함께 사용하면 복잡한 EXPLICIT 모드 쿼리 작성을 위한 대안을 얻을 수 있습니다. PATH 모드는 열 이름을 XPath형 구문으로 해석하여 SELECT 쿼리에서 반환한 행 집합의 열을 특성 및 하위 요소에 매핑합니다. SQL Server 2005의 FOR XML 기능 향상에 대한 자세한 내용은 Microsoft SQL Server 2005에서 FOR XML의 새로운 기능 (영문) 백서를 참조하십시오.



OPENXML

sp_xml_preparedocument 및 sp_xml_removedocument 시스템 저장 프로시저와 함께 OPENXML은 XML 문서의 관계형 행 집합 뷰를 제공합니다. XML 문서에서 OPENXML을 사용하려면 sp_xml_preparedocument를 인-메모리 XML 문서 표시를 만드는 데 사용해야 합니다. 이 저장 프로시저는 MSXML 파서를 사용하여 XML 문서를 구문 분석하고 OPENXML과 함께 사용할 수 있는 XML 문서에 핸들을 반환합니다. 이제 XML 문서 핸들과 같은 매개 변수, rowpattern(XML 데이터의 노드를 행에 매핑하는 XPath 식), 행 집합 스키마 및 행 집합 열과 XML 노드 간의 매핑이 OPENXML에 전달될 수 있어 행 집합을 얻을 수 있습니다. XML 문서는 더 이상 필요 없게 되면 sp_xml_removedocument 저장 프로시저를 사용하여 메모리에서 언로드되어야 합니다.



FOR XML의 향상 기능

FOR XML은 SQL Server 2005에서 다음과 같은 기능으로 향상되었습니다.



새로운 TYPE 지시어를 사용하여 FOR XML의 결과를 형 변환하는 기능
FOR XML의 결과를 XML 형식의 변수에 할당하는 기능
XML 계층을 생성하기 위해 FOR XML 쿼리를 중첩하는 기능
복잡한 XML 문서를 생성하기 위해 새로운 PATH 모드를 사용하는 기능
각각 XMLDATA 및 XMLSCHEMA 옵션을 사용하여 XDR 또는 XSD 형식으로 인라인 스키마를 생성하는 기능
요소 중심 XML을 생성하기 위해 RAW 모드와 함께 ELEMENTS 지시어를 사용하는 기능
xsi:nil="true" 특성을 가진 요소에 NULL 값을 매핑하기 위해 ELEMENT 지시어와 함께 XSINIL 옵션을 사용하는 기능



OPENXML의 향상 기능

SQL Server 2005에서 OPENXML은 다음과 같은 기능을 지원하도록 향상되었습니다.



XML 형식 데이터를 sp_xml_preparedocument에 전달하는 기능
WITH 절에서 새 데이터 형식을 사용하는 기능

XML 문서를 작성 및 분리하기 위해 FOR XML 및 OPENXML을 사용하는 것은 다음의 경우에 적합한 선택입니다.


응용 프로그램이 데이터를 관계적으로 저장하고 이 정보를 다른 응용 프로그램에 XML로 노출해야 합니다.
응용 프로그램이 XML의 순서를 지켜야 할 필요가 없습니다.
응용 프로그램이 과중한 요소 수준의 DML 작업을 수행합니다.
응용 프로그램에 세분화된 데이터 액세스 및 업데이트가 필요합니다.
응용 프로그램에서 웹 서비스를 통해 관계형 데이터를 노출해야 합니다.



시나리오 분석

시나리오 3의 요구 사항(시나리오 3: 자동차 제조업체와 부품 공급업체 간의 데이터 교환 II 섹션 참조)은 공급업체가 송장의 현황을 입수하거나 지불 지침의 복사본을 얻기 위해 이용할 수 있는 웹 서비스를 제공하는 것입니다. 웹 서비스와 함께 FOR XML은 제조업체가 인터넷에서 이러한 서비스를 노출할 수 있도록 하는 솔루션을 제공합니다. 공급업체는 웹 서비스를 이용하여 송장의 현황에 관해 문의합니다. 그러면 웹 서비스가 공급업체가 제공한 송장 ID를 이용해서 FOR XML 문을 사용하여 관계형 데이터로부터 XML 형식으로 응답을 생성합니다. 생성된 XML 문서는 공급업체에게 반환됩니다. 현재의 시나리오에서 FOR XML 문 기반 접근 방법은 다음과 같은 이점을 제공합니다.



FOR XML은 관계형 데이터에서 간단한 XML 문서를 동적으로 작성할 수 있는 손쉬운 방법을 제공합니다.
FOR XML 쿼리가 간단한 XML 문서를 작성하는 데 사용될 경우 FOR XML 쿼리의 유지 관리는 XML 뷰에 비해 쉽습니다.



이점

다음은 FOR XML/OPENXML 사용 시의 몇 가지 이점입니다.

FOR XML은 서버의 관계형 데이터로부터 XML을 생성하는 간단한 방법을 제공합니다.
FOR XML은 웹 서비스를 통해 비즈니스 정보를 노출할 수 있는 기능을 제공합니다.
OPENXML을 이용하면 행 집합을 단지 한 번의 네트워크 라운드 트립으로 대량의 INSERT, UPDATE, DELETE 작업을 수행할 수 있는 XML 형식으로 저장 프로시저에 전달할 수 있습니다.
FOR XML은 XSL과 함께 응용 프로그램 통합 또는 비즈니스 통합에 사용될 수 있습니다.



제한

XML 문서를 작성 및 분리하기 위해 FOR XML/OPENXML을 사용하는 경우의 제한 사항은 다음과 같습니다.

FOR XML을 EXPLICT 옵션과 함께 사용하여 XML 구조를 형성하는 것은 어려운 작업입니다.
FOR XML EXPLICIT를 사용하여 작성한 복잡한 쿼리를 유지 관리하기가 어렵습니다.
FOR XML AUTO에 의해 생성된 XML 문서는 데이터베이스 테이블 이름 및 열 정보를 노출할 수 있어 부주의한 정보 노출이 발생할 수 있습니다. 이 상황은 테이블 및 열에 별칭을 지정함으로써 방지할 수 있습니다.



FOR XML 및 OPENXML 사용의 예

다음 예제는 SQL Server 2005와 함께 제공되는 AdventureWorks 데이터베이스를 사용합니다. 지정된 범위의 고객에 대해 [Sales.Customer], [Sales.SalesOrderHeader], [Production.Product], [Sales.SalesOrderDetail] 테이블에서 고객, 주문 및 주문 상세 정보를 얻기 위해 FOR XML이 사용되는 예제를 살펴보도록 합시다.



예제: FOR XML 사용
SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, Detail.SalesOrderID, Detail.LineNumber,Detail.ProductID, Product.Name, Detail.OrderQty FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader, Sales.SalesOrderDetail Detail, Production.Product Product WHERE Cust.CustomerID = OrderHeader.CustomerID AND OrderHeader.SalesOrderID = Detail.SalesOrderID AND Detail.ProductID = Product.ProductID AND (Cust.CustomerID BETWEEN 44 AND 46) ORDER BY OrderHeader.CustomerID, OrderHeader.SalesOrderID FOR XML AUTO



쿼리 결과는 아래와 같습니다.


OrderQty="3">


다음 예제는 OPENXML 및 XPath 식을 사용하여 XML 문서에 지정된 주문 상세 정보를 추출합니다.



예제: OPENXML 사용
DECLARE @XmlDocumentHandle int DECLARE @XmlDocument nvarchar(max) SET @XmlDocument = N'<ROOT> <CUST CustomerID="44"> </CUST> </ROOT>' -- Create an internal representation of the XML document. EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument -- Execute a SELECT statement using OPENXML rowset provider. SELECT * FROM OPENXML (@XmlDocumentHandle, '/ROOT/Cust/OrderHeader/Detail',2) WITH (CustomerID varchar(10) '../@CustomerID', OrderID int '../@SalesOrderID', LineNumber int '@LineNumber', ProductID int '@ProductID', Quantity int '@OrderQty') -- Remove the internal representation. EXEC sp_xml_removedocument @XmlDocumentHandle


쿼리 결과는 아래와 같습니다.


--------------------------------------------------------
CustomerID OrderID LineNumber ProductID Quantity
--------------------------------------------------------
44 53575 2 952 2
44 53575 1 969 1
44 53575 3 972 1
44 59024 1 972 3
44 59024 2 957 2
46 48354 1 730 1
--------------------------------------------------------



SQL Server 2005의 XML 데이터 형식



XML 데이터의 계층적 특성으로 인해 데이터의 구조가 복잡해질수록(예: 계층 깊이의 증가) XML 데이터를 관계형 데이터로 모델링하기가 어려워집니다. 게다가 XML 데이터가 관계형 데이터에 매핑될 때 XML 인스턴스에 있는 요소의 순서가 유지되지 않으며 분리된 관계형 데이터에서 원래의 XML 문서를 작성하는 데 관련하여 많은 비용이 듭니다. XML 데이터를 저장하기 위한 관계형 모델의 제한 사항 때문에 XML 인스턴스를 원시적으로 저장하는 것이 가장 좋습니다. 원시 XML 인스턴스는 관계형 모델의 제한 사항에 영향을 받지 않으며 계층 구조 데이터 또는 중첩 데이터를 처리하는 기능, 요소의 순서를 유지하는 기능, XML 데이터를 저장 및 검색하는 간편한 방법, 다중 스키마를 지원하는 유연성 등과 같은 기능을 제공합니다.

Microsoft SQL Server 2005는 XML 데이터 처리를 위한 광범위한 지원을 제공합니다. SQL Server 2005에서는 XML 값이 XML 데이터 형식 열에 원시적으로 저장될 수 있어 XML 스키마의 모음에 따라 형식화되거나 혹은 형식화되지 않은 상태로 있을 수 있습니다. 세분화된 데이터 조작은 XQuery 및 XML DML을 사용하여 지원되며 후자의 경우는 데이터 수정을 위한 확장입니다. 게다가 XML 열은 쿼리 성능 향상을 위해 인덱스될 수 있습니다.



형식화된 XML

형식화된 XML은 XML 데이터를 설명하는 XML 스키마가 있는 경우에 이상적입니다. 이와 같은 경우 XML 스키마 모음을 XML 열에 연결하여 형식화된 XML을 제공할 수 있습니다. XML 형식 열에 대한 유효성 검사는 열과 연결된 XML 스키마 모음을 기준으로 수행됩니다. 또한 형식화된 XML 데이터는 노드 값의 런타임 변환이 필요하지 않으므로 형식화된 XML 데이터를 포함하는 쿼리의 성능은 형식화되지 않은 XML 데이터와 비교하여 더 우수합니다.



형식화되지 않은 XML

형식화되지 않은 XML의 사용은 스키마가 있지만 서버에서 데이터 유효성을 검사하기를 원하지 않는 경우 또는 사용할 수 있는 스키마가 없는 경우에 적합합니다. 다음과 같은 경우에는 스키마가 제공되어 있어도 형식화되지 않은 XML을 저장할 수 있습니다.

응용 프로그램에 고정 스키마가 없습니다.
서버에서 데이터를 저장하기 전에 응용 프로그램이 클라이언트 쪽에서 유효성 검사를 수행합니다.
응용 프로그램이 스키마에 준하여 유효하지 않은 XML 데이터를 일시적으로 저장합니다.
응용 프로그램이 서버에서 지원되지 않는 스키마 구성 요소(예: key/keyref)를 사용합니다.

형식화되지 않은 XML 문서는 어떤 스키마와도 연결되지 않은 경우에라도 제대로 형성될 수 있도록 검사됩니다. 형식화되지 않은 XML은 노드 값의 런타임 변환으로 인해(노드 값이 내부적으로 유니코드 문자열로 저장되기 때문), 성능 손실을 초래한다는 점에 주의하십시오.



XML 데이터 형식의 사용 시나리오

SQL Server 2005의 새로운 XML 데이터 형식을 사용하면 이제 다음과 같은 작업을 할 수 있습니다.



관계형 열뿐 아니라 XML 형식의 열이 하나 이상인 테이블을 만듭니다.
XML 열을 XML 스키마 모음에 연결하여 형식화된 XML 열 형식을 만듭니다.
업무 규칙을 유지하기 위해 다른 XML을 포함하는 XML 열 또는 비 XML 형식 열에 제약 조건을 만듭니다.
XML 데이터 형식의 인스턴스를 저장하는 데 사용할 수 있는 XML 형식의 변수를 만듭니다.
저장 프로시저 또는 사용자 정의 함수에 XML 형식의 매개 변수를 만듭니다.
사용자 정의 함수에서 XML 형식 값을 반환합니다.
새로운 TYPE 지시어를 사용하여 얻은 FOR XML 쿼리 결과를 XML 형식의 변수에 할당합니다.
XQuery의 하위 집합을 실행하여 XML 구조로 쿼리하고 XML 데이터를 변환합니다.
XML 형식의 열을 기반으로 계산 열을 만듭니다.
쿼리 성능을 향상시키기 위해 XML 형식의 열에 XML 인덱스를 만듭니다.
XML DML을 사용하여 XML 인스턴스에서 요소 수준의 삽입, 삭제, 업데이트 작업을 수행합니다.
XML 형식 데이터의 인스턴스를 sp_xml_preparedocument로 전달하여 인-메모리 XML 문서 표시를 준비합니다.
XQuery 및 XML DML을 사용하여 관계형 열 및 XML 열을 모두 포함하는 도메인 간 쿼리를 작성합니다.
각각 CAST 및 CONVERT를 사용하여 XML 형식을 varchar 또는 nvarchar 형식으로 캐스팅하거나 변환합니다.
CAST 또는 CONVERT를 사용하여 [n]varchar, [n]text, varbinary, image와 같은 문자열 데이터 형식을 XML 형식으로 변환하거나 캐스팅합니다.



XML 데이터 형식 메서드 및 XML DML

XML 데이터 형식 열에 대한 쿼리 및 조작은 다섯 가지 메서드를 통해 지원됩니다. XML 문서의 조각은 XML 데이터 형식의 query() 메서드를 사용하여 추출될 수 있습니다. query() 메서드는 XQuery 식을 인수로 받아들여 형식화되지 않은 XML 인스턴스를 반환합니다. 스칼라 값은 XQuery 식과 반환되기를 원하는 SQL 형식을 지정하여 value() 메서드를 사용하여 XML 인스턴스에서 추출될 수 있습니다. XML 인스턴스에 대한 존재 확인은 exist() 메서드를 사용하여 수행될 수 있습니다. XML 문서를 관계형 데이터로 분해하는 작업이 nodes() 메서드를 통해 용이해집니다.

데이터 조작 작업은 modify() 메서드를 사용하여 XML 인스턴스에서 수행될 수 있습니다. XML DML 지원은 XQuery에 추가된 삽입, 삭제, 업데이트 키워드를 통해 제공됩니다. 삽입, 삭제, 업데이트 키워드를 각각 사용하여 하나 이상의 노드가 삽입, 삭제 및 업데이트될 수 있습니다.



XML 인덱싱

XML 데이터 형식 열에서 쿼리 처리 작업에 포함되는 구문 분석 및 분할은 XML 인스턴스의 크기가 매우 큰 경우 엄청나게 긴 시간이 소비될 수 있습니다. XML 데이터 형식의 쿼리 성능은 이러한 열에 인덱스를 생성함으로써 향상될 수 있습니다. XML 데이터의 크기 및 사용 시나리오는 필요한 인덱스의 종류를 지정하는 데 중요한 역할을 수행합니다. SQL Server는 두 가지 종류의 인덱스, 즉 주 XML 인덱스와 보조 XML 인덱스를 지원하며 후자는 전자 없이는 존재할 수 없습니다.

XML 열에서 주 XML 인덱스의 생성은 XML BLOB를 분할하고 이 값을 내부 테이블에 저장합니다. 이로 인해 런타임에 수반되는 분할 작업이 없어져 쿼리 실행 시간 동안 성능이 향상되었습니다. 사용 시나리오에 따라 보조 XML 인덱스를 생성함으로써 쿼리 성능을 한층 더 향상시킬 수 있습니다. 각각 경로, 속성, 값을 기준으로 쿼리 성능을 향상시키기 위해 보조 XML 인덱스의 세 가지 유형인 PATH, PROPERTY, VALUE를 만들 수 있습니다. XML 형식 열에 적합한 보조 인덱스 선택에 관한 자세한 내용은 “XML 데이터 형식을 위한 성능 최적화” 백서에서 볼 수 있습니다.


XML 문서를 XML 데이터 형식으로 저장하는 것은 다음의 경우에 적합한 선택입니다.


응용 프로그램이 XML 인스턴스의 Infoset 콘텐츠를 보존하도록 요구됩니다. XML 문서의 Infoset 콘텐츠는 문서 계층, 요소 순서, 요소 및 특성 값 등을 포함합니다. 특성 순서, 이름 공간 접두사, 중요하지 않은 공백 및 XML 선언과 같은 정보는 보존되지 않습니다.
응용 프로그램이 XML 문서에 대한 요소 수준의 수정 및 쿼리 작업을 필요로 합니다.
응용 프로그램이 쿼리 처리 속도를 높이기 위해 XML 데이터 형식 열에 대한 인덱스를 필요로 합니다.
XML 데이터에 스키마가 있을 수도 있고 없을 수도 있습니다.
응용 프로그램이 다양한 구조의 XML 문서 또는 관계형 구조에 매핑하기에 너무 어려운 서로 다르거나 복잡한 스키마를 따르는 XML 문서를 사용합니다.



시나리오 분석: 콘텐츠 관리 시스템

이제 XML 사용 시나리오(시나리오 4: 콘텐츠 관리 시스템 섹션 참조)에서 설명한 콘텐츠 관리 시스템을 분석해 봅시다. 출판 회사는 텍스트, 이미지, 오디오, 비디오 등 다양한 형식의 정보를 처리합니다. 독립적으로 사용될 수 있는 정보 블록은 다양한 소스에서 수집되고 데이터베이스에서 유지 관리됩니다. 이러한 정보 블록은 구성 요소로 알려져 있습니다. 개별 구성 요소를 조합하여 문서가 만들어집니다. 문서에 어떤 구성 요소가 포함되는지는 사용자의 요구에 따라 다릅니다. 이러한 문서는 다양한 채널을 통해 가입한 사용자들에게 제공됩니다. 콘텐츠 관리 시스템은 일반적으로 높은 성능과 확장성을 갖추고 콘텐츠를 저장, 조회, 검색 및 업데이트할 수 있는 능력이 있어야 합니다.

통합 데이터 모델인 XML은 동일한 문서에 XML 데이터 및 XML 콘텐츠를 모두 저장할 수 있는 뛰어난 옵션을 제공합니다. XML은 또한 데이터에서 표시를 분리하는 기능을 제공하는데, 이 기능은 동일한 정보가 각 사용자마다 서로 다르게 표시될 수 있기 때문에 중요합니다. SQL Server 2005에서 제공하는 원시 XML 데이터 형식을 사용하여 이러한 콘텐츠 관리 시스템의 요구 사항을 맞출 수 있습니다. XML 데이터 형식을 사용하면 XML 문서를 저장하고, XML DML을 사용하여 요소 수준에서 XML 문서를 수정하고 XQuery를 사용하여 XML 문서에서 쿼리를 수행할 수 있습니다.



시나리오 분석: 고객 설문조사

고객 설문조사 XML 사용 시나리오(시나리오 5: 고객 설문조사 섹션 참조)에서 주요 요구 사항은 여러 스키마를 사용하는 설문조사 정보를 저장할 수 있는 능력입니다. 고정 스키마가 없는 데이터는 관계형 테이블을 하나만 사용하여 모델링될 수 없습니다. XML 열이 있는 관계형 테이블은 이러한 정보를 저장할 수 있는 우수한 옵션을 제공합니다. 설문조사의 형식을 저장하기 위해 추가 열을 관계형 테이블에 추가할 수 있습니다. 설문조사 형식 열을 사용하여 설문조사 형식의 모든 레코드를 반입함으로써 설문조사의 특정 형식에 해당하는 정보를 분석할 수 있습니다. 일반적인 설문조사에서 고객은 대개 모든 질문에 대해 대답하지는 않습니다. 따라서 몇 개의 열을 만들고(설문조사에서 각 질문마다 하나씩) 대답하지 않은 질문에 대해 데이터베이스에 NULL 값을 저장하는 대신, 하나의 열에 고객 한 명에 대한 설문조사 정보를 XML로 저장하는 것이 유용할 수 있습니다. 이 시나리오의 경우에는 다음과 같은 이유 때문에 고객 설문조사 정보를 XML 형식 열로 저장하는 것이 더 적절합니다.



XML 형식 열을 사용하면 서로 다른 스키마를 사용하는 설문조사 정보를 단일 XML 형식 열에 저장할 수 있습니다. XML 형식 열을 XML 스키마 모음에 연결하면 사용자가 여러 설문조사 형식의 데이터를 저장할 수 있습니다.
설문조사 정보의 유효성 검사를 사용자 인터페이스에서 수행할 수 있으며 데이터베이스 수준에서 유효성 검사를 실시하기 위해 이 정보를 형식화된 XML 데이터 형식 열로 저장할 필요가 없습니다.
XQuery를 사용하여 데이터를 분석할 수 있습니다.



이점

XML 데이터를 XML 데이터 형식 열에 저장할 경우의 이점은 다음과 같이 요약될 수 있습니다.



XML 데이터 형식은 문서 순서 및 문서 구조를 유지하면서 서버에서 XML 데이터를 저장하는 간단하고 손쉬운 방법을 제공합니다. 이는 문서 순서 및 문서 구조가 매우 중요한 문서의 경우에 특히 그렇습니다. 응용 프로그램이 일부 데이터 원본에서 XML 문서를 가져와서 그 문서를 저장하도록 작동하는 간단한 시나리오를 가정해 봅시다. 문서를 nvarchar 또는 text 열에 저장하면 XML이 잘 구성되도록 보장되지 않으며 문서 내용에 쉽게 액세스할 수 없습니다. 이와 같은 경우에는 들어오는 XML 문서를 XML 열에 원시적으로 저장하는 것이 적절합니다.
XML 데이터 형식은 XML 데이터에서 세분화된 쿼리를 수행하고 작업을 수정할 수 있는 능력을 제공합니다. SQL Server 2005 이전에는 XML을 데이터베이스에 원시적으로 저장하는 방법이 없었습니다. 따라서 XML 데이터를 수정하거나 쿼리하려면, nvarchar 또는 text 열에서 데이터를 로드해서 문자열을 이용하여 XML 문서를 만든 다음 수정해야 했습니다. 데이터베이스에 다시 데이터를 기록하고 수정하기 위해서도 이와 비슷한 단계를 거쳐야 했습니다. 이제 XML 데이터 형식을 사용하면 이러한 작업이 훨씬 쉬워집니다.
XML 데이터 형식을 사용하면 더 빠른 쿼리 처리를 위해 XML 데이터 형식 열에 인덱스를 만들 수 있습니다.
XML 데이터 형식을 사용하는 경우 XML 스키마 모음과 XML 데이터에 대한 제약 조건을 사용하여 업무 규칙을 유지할 수 있습니다. XML 스키마는 데이터 유효성을 검사하고, 형식 기반 작업 상의 의미론을 추가하고, 쿼리 및 데이터 수정 문의 컴파일 동안 형식화되지 않은 XML보다 더 정확한 형식 검사를 수행하고 저장 및 쿼리 처리를 최적화합니다.
XML 형식 데이터는 데이터베이스에 저장되므로 백업 및 복원, SQL Server 보안, 트랜잭션, 로깅 등과 같은 다양한 데이터베이스 작업에 포함됩니다.



제한

다음은 새로운 XML 데이터 형식으로 작업할 경우에 알아두어야 할 제한 사항들 중 일부입니다.



데이터의 정확한 복사본이 저장되지 않습니다. 중요하지 않은 공백, 이름 공간 접두사, 특성 순서 및 XML 선언이 보존되지 않습니다.
XML 문서에서 최대 계층 깊이는 128입니다.
XML 문서의 내부 이진 표시의 최대 크기는 2GB입니다.
XML 인스턴스를 비교할 수 없습니다. 따라서
XML 열이 기본 키 또는 외부 키 제약 조건의 일부가 될 수 없습니다.
XML 열을 GROUP BY 문에서 그룹화 값으로 사용할 수 없습니다.
text, ntext, image 데이터 형식은 SQL Server 2005에서 사용이 지양되므로 XML은 이러한 형식으로 캐스팅될 수 없습니다. 하지만 XML 데이터 형식은 [n]varchar 및 [n]varbinary 형식으로 캐스팅될 수 있습니다.



XML 데이터 형식 사용의 예

예제 응용 프로그램은 AdventureWorks 데이터베이스에서 Sales.Store 테이블을 사용합니다. Sales.Store 테이블은 기본 키로 CustomerID를, XML 열로 Demographics를 포함합니다. Demographics 열은 Store Survey 정보를 포함합니다. Store Survey의 일부로 저장된 정보는 선택적입니다. 이는 Demographics 열이 모든 요소를 포함할 수도 있고 그렇지 않을 수도 있음을 의미합니다. 동일한 정보가 관계형 형식으로 저장된 경우에는 이들 요소를 테이블의 열로 만들어야 합니다. Store Survey 정보의 대부분은 선택적이기 때문에 이 열은 데이터의 대부분에 대해 NULL 값을 포함할 것입니다. 이러한 낭비를 피하기 위해 Demographics 열에 있는 Store Survey 정보는 XML 형식으로 저장됩니다. Demographics 열은 판매 정보 즉, 각 고객에 대한 연간 판매, 연간 수익, 은행 이름 등을 포함합니다. 이들 필드는 데이터의 XML 요소로 저장됩니다.

예제 응용 프로그램은 다음 기능을 수행합니다.



모든 고객의 목록을 인구 통계학적 정보와 함께 표시합니다.
특정 고객에 대한 인구 통계학적 정보를 표시합니다.
Sales.Store 테이블에 새 고객을 인구 통계학적 정보와 함께 삽입합니다.
해당 고객에 대한 인구 통계학적 정보의 일부 요소(예: Annual Sales, Annual Revenue 등)를 수정합니다.
해당 고객에 대한 인구 통계학적 정보를 삭제합니다.

응용 프로그램이 System.Data.SqlTypes.SqlXml 클래스를 사용하여 XML 열에서 데이터를 검색합니다. SQLXML 클래스는 XML 열에 대한 직접적인 매핑입니다.

SQLXML 클래스를 사용하면 어떤 매핑이나 변환 없이도 XML 열에서 데이터를 직접 검색할 수 있습니다.

여기에서 customer ID 12에 대한 Annual Revenue 요소를 검색하는 방법의 예를 살펴보도록 합시다. 다음 코드 예제는 위에서 언급한 두 번째 기능을 보여줍니다.


Public void RetreiveAnnualRevenue () { SqlConnection conn = new SqlConnection(); conn.ConnectionString = @"Server=localhost; Database=AdventureWorks; integrated security=SSPI"; conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = @"select Demographics.query( 'declare namespace SS="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey" <STOREINFO> <ANNUALREVENUE> {data(/SS:StoreSurvey/SS:AnnualRevenue)} </ANNUALREVENUE> </STOREINFO>') as Result from Sales.Store where CustomerID=12"; SqlDataReader datareader = command.ExecuteReader(); System.Text.StringBuilder builder = new System.Text.StringBuilder(); While (datareader.Read()) { SqlXml sqlxml = datareader.GetSqlXml(0); builder.Append(sqlxml.Value); } //Note: xml1 is a XML web control this.xml1.DocumentContent = builder.ToString(); this.xml1.TransformSource = @"StoreInfo.xslt"; }


위 메서드에서는 AdventureWorks 데이터베이스에 SqlConnection 개체의 인스턴스를 만듭니다. 명령 텍스트 속성은 customer ID 12에 대한 Annual Revenue 필드의 값을 검색하는 쿼리를 포함합니다.

이 코드는 XML 열에서 직접적으로 실행되는 XQuery 입니다. 쿼리의 반환 값은 XML 조각으로서 SQLXML 클래스에 매핑됩니다. 그러고 나서 XML 조각은 SQLXML 클래스의 Value 속성을 사용하여 검색될 수 있습니다.

그런 다음 검색된 XML 조각은 XML 웹 서버 컨트롤을 사용하여 클라이언트 응용 프로그램에 표시됩니다.



다양한 접근 방법 비교

기능 .NET Framework의 XML 클래스 FOR XML / OPENXML SQLXML XML 데이터 형식
코드 복잡도 높음. XML 데이터와 관계형 데이터 사이를 직접적으로 매핑하는 클래스가 없습니다. 보통. FOR XML EXPLICIT를 사용한 쿼리 작성이 어렵습니다. 낮음. 클래스가 관계형 데이터를 XML 데이터로 조작하는 메커니즘을 제공하고 또한updategrams가 레코드를 업데이트하는 기능을 제공합니다. 낮음. XML 데이터가 그 자체로서 열에 저장되므로 복잡도가 줄어듭니다. 뿐만 아니라 Visual Studio 2005는 XML 데이터 형식을 조작하기 위한 클래스를 제공합니다. XML DML은 XML 데이터를 수정하는 데 사용할 수 있습니다.
유지 관리성 복잡함. 테이블의 필드 또는 XML을 변경하는 데 코드 변경이 필요합니다. 어려움. 테이블의 필드 또는 XML을 변경하는 데 쿼리 변경이 필요합니다. 쉬움. 대부분의 경우 매핑 XSD 파일을 수정하면 변경 사항이 적용될 수 있습니다. 쉬움. XQuery는 데이터베이스의 XML 열 쿼리를 위한 쉬운 구문을 제공합니다.
설치 .NET Framework 이외의 다른 특별한 설치가 필요하지 않습니다. 특별한 설치가 필요하지 않습니다. SQLXML 라이브러리가 클라이언트 시스템에 설치되어 있어야 합니다. 특별한 설치가 필요하지 않습니다.
보안 정상적인 경우에는 데이터 유형 및 형식이 클라이언트 쪽에 노출되지 않기 때문에 상당히 안전합니다. 테이블 이름 및 열 이름의 노출을 방지하도록 적절한 주의를 기울인다면 안전합니다. 매핑 XSD 파일이 중간 계층 대신 클라이언트 쪽에 저장된 경우 매핑 XSD 파일의 보안을 유지하도록 설계해야 합니다. 보안됨
.NET Compact Framework에 대한 지원 제한된 지원. Microsoft .NET Compact Framework에서 XmlDataDocument는 지원되지 않습니다. 지원됨 지원되지 않음 지원되지 않음. SQL Server의 XML 데이터 형식 열이 SQL Server 모바일에 동기화되면 ntext로 변환됩니다.
데이터 유효성 검사 클라이언트 및 서버에 의해 실행될 수 있습니다. 서버에 의해 실행될 수 있습니다. 클라이언트에 의해 수행될 수 있습니다. XML 스키마를 사용하여 서버에 의해 실행될 수 있습니다.
데이터 저장소 [n]varchar(max), XML, or varbinary(max) 관계형 테이블(XML을 필드로 사용할 수 있음) 관계형 테이블(XML을 필드로 사용할 수 있음) XML 데이터 형식
충실도 원문 충실도(바이트 수준에서 XML 데이터를 보존함) 관계 충실도(데이터의 계층 구조는 보존하지만 요소 간의 순서는 무시함) 관계 충실도 InfoSet 충실도(XML 데이터의 InfoSet 콘텐츠를 보존함)
저장소에서의 데이터 액세스 및 업데이트 문서 수준에서 업데이트를 지원합니다. 세분화된 데이터 액세스 및 업데이트를 지원합니다. 세분화된 데이터 액세스 및 업데이트를 지원합니다. 세분화된 데이터 액세스 및 업데이트를 지원합니다.


결론

이 문서에서는 SQL Server 2005에서 XML을 처리하기 위한 여러 옵션에 대해 알아보았습니다. System.Xml 이름 공간, SQLXML 및 XML 데이터 형식을 각각의 관련 이점 및 제한과 함께 예제 시나리오를 들어 설명했습니다. 이상적인 시나리오에서 나타난 성과를 통해 사용자는 자신의 응용 프로그램에 맞는 적절한 XML 옵션을 선택할 수 있습니다.


추가 정보

- Microsoft SQL Server 2005 웹 사이트
- Microsoft SQL Server 2005에서의 XML 지원
- Microsoft SQL Server 2005를 위한 최상의 XML 사용 방법
- Microsoft SQL Server 2005에서 FOR XML의 새로운 기능
- XML 데이터 형식을 위한 성능 최적화


제공 : DB포탈사이트 DBguide.net
출처명 : 한국 마이크로소프트

Tony Patton ( TechRepublic ) 2006/06/09


.NET 애플리케이션 코드에서 예외를 처리하는 것은 매우 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 살펴본다.

개발자들은 예외(exception)를 처리하는데 친절하게도 많은 노력을 기울이기 때문에 사용자들은 알 수 없는 시스템 에러 메시지를 걱정할 필요가 없다. 이런 이유 때문에, 예외 처리는 모든 .NET 애플리케이션의 표준 항목이다. try/catch 블록은 개발자가 예외를 잡아내고 그 시점에서의 애플리케이션 실행을 컨트롤할 수 있도록 해준다. 많은 에러들은 데이터베이스 처리 중에 발생하지만 많은 개발자들은 데이터베이스 부분에서 생기는 에러를 처리하는 것을 알지 못한다. 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 알아보자.

T-SQL에서 발생한 에러 처리하기
SQL 서버가 제공하는 T-SQL 언어는 저장 프로시저, 함수 등에서 발생할 수 있는 치명적이지 않은 에러를 쉽게 처리할 수 있게 해주지만, 모든 에러가 쉽게 처리할 수 있게 되는 것은 아니다. 사실, 에러에는 치명적인 에러와 치명적이지 않은 에러가 있는데, 치명적이지 않은 에러와는 달리 치명적인 에러는 실행이 중단된다.

트랜잭션
변경사항이 모두 완료돼 모든 것이 정상인 것을 확실히 하기 위해서는 데이터베이스 코드에 트랜잭션을 사용해야만 한다. SQL 서버 온라인 도움말은 selects, inserts, updates 혹은 deletes와 같은 명령행의 연속으로 이루어진 논리적 작업 단위라고 설명한다. 만약 트랜잭션동안 에러가 없다면 트랜잭션의 모든 변경 사항은 데이터베이스에 적용될 것이며, 만약 에러가 발생하면, 어떤 변경사항도 데이터베이스에 적용되지 않는다.

트랜잭션은 BEGIN TRANSACTION과 END TRANSACTION 명령 사이에 포함된다. ROLLBACK TRANSACTION 명령은 모든 변경사항을 취소하도록 하여, 어떤 변경사항도 이루어지지 않게 한다. COMMIT TRANSACTION 명령은 변경사항을 데이터베이스에 반영한다. 이제, T-SQL에서 에러를 처리하는 방법을 알아보자.

@@Error
@@Error 함수는 T-SQL을 만들 때 에러를 처리하도록 해준다. 이 함수는 시스템의 에러 코드를 돌려준다. 만약 에러가 없으면 0을 리턴 한다. @@Error 함수는 각 T-SQL 명령이 실행되면 초기화되기 때문에, 명령을 호출한 직후 바로 불러야한다.

RAISERROR
RAISERROR 명령은 커스텀 에러 메시지를 만들거나 sysmessages 테이블에 이미 있는 메시지를 사용할 수 있게 해준다. 이 구문의 문법은 온라인으로 볼 수 있지만, 가장 기본적인 형태는 에러의 심각도, 상태와 함께 메시지나 메시지 ID를 포함한다. 상태는 SQL 서버에서 사용하지 않기 때문에 임의의 숫자를 이용해 처리한다. 심각도는 에러의 심각성을 나타내는데 0~18은 사용자가 사용할 수 있으며 19~25는 관리자를 위해 예약돼 있다.

예제 1. 이 예제 저장 프로시저는 Northwind 데이터베이스의 개별 레코드를 업데이트하는데 이 기능들을 사용한다. 에러가 없을 경우 전화 번호 칼럼의 값을 프로시저를 통해 수정한다. 만약 에러가 발생하면 음수를, 에러가 없으면 양수를 돌려주는 리턴 값을 사용한다.

저장 프로시저의 리턴 값 사용하기
.NET 코드에 저장 프로시저의 리턴 값을 사용할 수 있다. SqlCommand 객체는 저장된 리턴 값뿐만 아니라 쉽게 프로시저에 파라미터를 넘길 수 있도록 해준다. 파라미터의 Direction 속성은 저장 프로시저 호출을 통한 리턴 값을 얻는데 사용되는데, 이 속성은 InputOutput과 Output이 될 수 있다. 다음 예제에서는 상태 값을 받기 위해 Output을 사용하였다.

다음 예제는 Northwind 데이터베이스의 customers 테이블의 특정 레코드에 새로운 값을 저장하는 간단한 ASP.NET 페이지이다. id 값은 실제로는 hidden 필드로 저장된다. form을 통해 값을 쉽게 넘길 수 있지만, 데모를 위해 예제와 같이 했다. text 필드에 입력된 값은 phone 필드를 업데이트 하는데 사용된다.

파라미터는 SqlCommand 객체에 추가할 수 있으며 저장 프로시저의 파라미터 값과 정확히 일치해야한다. 이 작업은 SqlCommand 객체의 ExecuteNonQuery를 통해 실행된다. 이것이 실행되면, 파라미터를 통해 리턴 값을 받을 수 있다.

다음 예제는 리턴 값을 검사 하고(-1은 문제가 있음을 뜻한다) Label 컨트롤에 메시지를 표시한다. 추가로 데이터베이스 처리 중에 발생할 수 있는 치명적인 에러를 잡기 위해 try/catch 블록이 사용되었다. 예제 2. 예제 3은 같은 작업을 하는 VB.NET 코드이다

필요한 모든 것 제공
.NET 애플리케이션 코드에서 예외를 처리하는 것은 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 하지만, 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, SQL 서버의 T-SQL은 코드를 실행하면서 확인할 수 있는 모든 것들을 제공한다.@


http://www.zdnet.co.kr/builder/dev/etc/ ··· 2C00.htm

Johnny Papa│MJM Investigations 정보 기술 담당 부사장

응용 프로그램이 더 빠르게 실행되도록 하기 위해서는 여기 저기를 조금씩 손보기만 하면 됩니다. 문제는 어떻게 손보는가에 있죠! 조만간 응용 프로그램의 SQL 쿼리가 여러분이 의도한 방식대로 응답하지 않는 상황에 직면하게 될 것입니다. 원하는 데이터를 반환하지 않거나 아니면 너무 길어서 적합하지 않습니다. SQL이 보고서나 엔터프라이즈 응용 프로그램의 속도를 떨어뜨려 엄청난 시간 동안 기다려야 하는 상황이 발생하면 사용자는 그리 즐거울 수 없을 것입니다. 부모님이 자녀가 귀가 시간을 어긴 이유를 듣고 싶어하지 않듯 사용자 역시 쿼리가 그렇게 오래 걸리는 이유를 알고 싶어하지 않습니다. (“엄마, 죄송해요. LEFT JOIN을 너무 많이 사용했네요.”) 사용자는 응용 프로그램이 신속히 응답하고 보고서가 분석 데이터를 즉시 반환하기를 원합니다. 저 역시도 웹 서핑 중 한 페이지를 로드하는데 10초(사실 5초 정도) 이상이 걸리면 참을 수가 없어집니다.


이러한 문제를 해결하기 위해서는 그 문제의 원인을 찾아 내는 것이 중요합니다. 그렇다면 어디부터 시작해야 할까요? 문제의 원인은 일반적으로 데이터베이스 디자인과 그 데이터베이스를 액세스하는 쿼리에 있습니다. 이번 달 컬럼에서는 SQL Server 기반 응용 프로그램의 성능이나 확장성을 향상시키는데 사용할 수 있는 네 가지 테크닉을 살펴 보겠습니다. 그리고 LEFT JOIN과 CROSS JOIN 사용 및 IDENTITY 값 검색도 살펴 보겠습니다. 마술같은 해결책은 없다는 것을 기억하십시오. 데이터베이스와 쿼리를 조정하려면 시간이 걸리고 분석과 함께 수차례의 테스팅이 필요합니다. 여기 제시된 테크닉은 증명이 된 것이지만 사용자 응용 프로그램에 따라 더 잘 실행되는 테크닉과 그렇지 않은 테크닉이 있을 수 있습니다.

INSERT에서 IDENTITY 반환 T

가장 궁금한 문제 즉, SQL INSERT를 실행한 후 어떻게 IDENTITY 값을 검색하는지부터 살펴 보겠습니다. 문제는, 그 값을 검색하는 쿼리를 어떻게 작성하는지가 아니라 언제 어디서 작성하는가 입니다. SQL Server에서, 활성 데이터베이스 연결에서 가장 최신 SQL 문 실행에 의해 만들어진 IDENTITY 값을 검색하는 문은 다음과 같습니다.

SELECT @@IDENTITY

이 SQL은 강력하지가 않으므로 가장 최근의 SQL 문이 INSERT가 아니거나 INSERT SQL이 아닌 다른 연결에 대해 이 SQL을 실행한다면 예상하는 값을 얻지 못할 것이라는 사실을 명심해야 합니다. IDENTITY를 검색하려면 다음과 같이 INSERT SQL 직후에 동일한 연결에서 이 코드를 실행해야 합니다.

INSERT INTO Products (ProductName) VALUES ('Chalk') SELECT @@IDENTITY
단일 연결에서 Northwind 데이터베이스에 대해 이러한 쿼리를 실행하면 Chalk라는 신제품에 대한 IDENTITY 값이 반환될 것입니다. 따라서 ADO를 사용하는 Visual Basic 응용 프로그램에서 다음 명령문을 실행할 수 있습니다.

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _ (ProductName) VALUES ('Chalk');SELECT @@IDENTITY") lProductID = oRs(0)
이 코드는 그 쿼리에 대한 행 카운트를 반환하지 않도록 SQL Server에 알리고 INSERT 문을 실행하며 그 새 행에 대해 만들어진 IDENTITY 값을 반환합니다. SET NOCOUNT ON 문은 반환된 Recordset에 새 IDENTITY 값이 들어 있는 한 행과 열이 있다는 것을 뜻합니다. 이 문이 없으면 (INSERT 문이 데이터를 반환하지 않으므로) 빈 Recorset가 반환되며 그 다음 반환되는 두 번째 Recordset에 IDENTITY 값이 들어 있습니다. 따라서 INSERT가 Recordset를 반환하도록 할 생각이 아니었던 경우에는 특히나 당황스러울 수 있습니다. 이러한 상황은, SQL Server는 행 카운트(즉, 영향을 받는 행)를 확인하고 그 카운트를 Recordset 표시로 해석하기 때문에 발생합니다. 따라서 올바른 데이터는 두 번째 Recordset로 밀려납니다. ADO에서 NextRecordset 메서드를 사용하면 이 두 번째 Recordset를 확인할 수 있지만 이 Recordset이 반환되는 첫 번째이자 유일한 값이라면 훨씬 쉽고 효율적일 것입니다.


이 테크닉이 작업을 실행하긴 하지만 SQL 문에 추가 코드가 필요합니다. 동일한 결과를 얻을 수 있는 또 다른 방법은 다음 코드에서 볼 수 있는 것처럼 INSERT 앞에 SET NOCOUNT ON 문을 사용하고 그 테이블의 FOR INSERT 트리거에 SELECT @@IDENTITY 문을 넣는 것입니다. 이렇게 하면 그 테이블에 대한 어떤 INSERT 문이나 자동으로 IDENTITY 값을 반환하게 됩니다.

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS SELECT @@IDENTITY GO
이 트리거는 Product 테이블에 INSERT가 실행될 때만 발생하므로 성공적인 INSERT 후에는 언제나 IDENTITY를 반환합니다. 이 테크닉을 사용하면 응용 프로그램 내 어디서나 동일한 방식으로 IDENTITY 값을 검색할 수 있습니다.


인라인 값 VS. 임시 테이블


종종 쿼리는 GROUP BY 후 표준 쿼리를 실행해야만 수집할 수 있는 다른 데이터에 데이터를 조인해야 하는 경우가 있습니다. 예를 들어 가장 최근 주문 5건에 대한 정보를 반환하고 싶다면 먼저 그 최근 주문 5건이 무엇인지부터 알아야 합니다. 이 주문은 주문 ID를 반환하는 SQL 쿼리를 사용하면 검색할 수 있습니다. 이 데이터는 임시 테이블에 저장될 수 있으며 그런 다음 Product 테이블로 조인되어 그 주문에 대해 판매된 제품 수량을 반환합니다.

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _OrderDate DATETIME NOT NULL) INSERT INTO #Temp1 (OrderID, OrderDate) SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM #Temp1 t INNER JOIN [Order Details] od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName DROP TABLE #Temp1
이 SQL 일괄 처리는 임시 테이블을 만들어 그 테이블에 데이터를 입력하고 다른 데이터를 조인한 다음 그 임시 테이블을 삭제합니다. 이 쿼리는 I/O가 많으므로 임시 테이블 대신 인라인 뷰를 사용하도록 다시 작성할 수 있습니다. 인라인 뷰는 간단하게 말하면 FROM 절에서 조인될 수 있는 쿼리입니다. 따라서 임시 테이블의 tempdb에서 많은 I/O 와 디스크 액세스를 허비하는 대신 인라인 뷰를 사용해서도 동일한 결과를 얻을 수 있습니다.

SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM ( SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC ) t INNER JOIN [Order Details] od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName

이 쿼리는 이전 쿼리보다 효율적일 뿐만 아니라 더 짧습니다. 임시 테이블은 많은 리소스를 소비합니다. 따라서 데이터를 다른 쿼리에 조인시키기만 하면 되는 경우에는 인라인 뷰를 사용하여 리소스를 보존하는 방법을 시도해 보는 것이 좋을 것입니다.


LEFT JOIN과 NULL 피하기

물론, LEFT JOIN을 실행하고 NULL 값을 사용해야 할 때가 있습니다. 하지만 모든 경우에 항상 그래야 하는 것은 아닙니다. SQL 쿼리를 구성하는 방식을 변경하면 실행 시간이 몇 분이 걸리는 보고서를 단 몇 초 만에 실행되는 보고서로 만들 수 있습니다. 그리고 응용 프로그램이 원하는 방식으로 쿼리의 데이터를 변경해야 하는 경우도 종종 있습니다. TABLE 데이터 형식은 리소스 사용량을 줄여주지만 쿼리에는 최적화할 수 있는 부분이 여전히 많이 남아 있습니다. SQL에서 일반적으로 사용되는 아주 유용한 기능은 LEFT JOIN입니다. 이 기능은 첫 번째 테이블의 모든 행과 두 번째 테이블의 일치하는 모든 행, 그리고 첫 번째 테이블의 행과 일치하지 않는 두 번째 테이블의 모든 행을 검색하는데 사용할 수 있습니다. 예를 들어 모든 Customer와 그 주문을 반환하고 싶다면 LEFT JOIN은 주문을 한 Customer와 주문을 하지 않은 Customer를 표시할 것입니다.


이 도구는 지나치게 남용될 수 있습니다. LEFT JOIN은 NULL(존재하지 않음) 데이터에 대해 데이터를 일치시키는 작업을 하므로 부담이 큽니다. 이 실행을 피할 수 없는 경우도 있지만 그럴 경우 부담이 커집니다. LEFT JOIN은 INNER JOIN보다 부담이 더 크므로 LEFT JOIN을 사용하지 않도록 쿼리를 다시 작성할 수 있다면 부담이 크게 줄어들 수 있습니다(그림 1의 다이어그램 참조).


LEFT JOIN을 사용하는 쿼리의 속도를 높이기 위해서는 TABLE 데이터 형식을 만들고 첫 번째 테이블(LEFT JOIN의 왼쪽에 있는 테이블)에서 모든 행을 삽입한 다음 두 번째 테이블의 값을 사용하여 그 TABLE 데이터 형식을 업데이트합니다. 이 테크닉은 2단계 프로세스이지만 표준 LEFT JOIN과 비교하면 시간을 크게 줄일 수 있습니다. 자신의 응용 프로그램에 맞는 최고 성능의 쿼리를 얻을 때까지 각각에 대해 서로 다른 테크닉을 시도하여 시간을 측정해 보는 것이 좋습니다.


쿼리 속도를 테스트할 때는 여러 번 실행해서 평균을 구하는 것이 좋습니다. 쿼리(또는 저장 프로시저)는 SQL Server 메모리의 프로시저 캐시에 저장될 수 있으므로 처음에는 더 오래 걸리지만 계속 시도할수록 점점 더 짧아집니다. 이 외에도 쿼리 실행 중 동일한 테이블에 대해 다른 쿼리가 실행되고 있을 수도 있습니다. 그렇게 되면 다른 쿼리가 테이블을 잠궜다가 해제하는 동안 사용자의 쿼리는 기다려야 할 수도 있습니다. 예를 들어 다른 사람이 테이블의 데이터를 업데이트하는 동안 그 테이블에 대해 쿼리를 실행하면 그 업데이트가 실행되는 동안에는 쿼리 실행에 더 많은 시간이 걸릴 수 있습니다.


LEFT JOIN으로 인한 감속을 피할 수 있는 가장 쉬운 방법은 가능한 한 많이 LEFT JOIN을 중심으로 데이터베이스를 디자인하는 것입니다. 예를 들어, 한 제품에 카테고리가 있을 수도 있고 없을 수도 있다고 가정해 봅시다. 제품 테이블에 그 카테고리의 ID가 저장되어 있는데 특정 제품에 대한 카테고리가 존재하지 않는다면 그 필드에는 NULL 값을 저장할 수 있습니다. 그런 다음 LEFT JOIN을 실행하여 모든 제품과 그 카테고리를 얻습니다. “No Category” 값을 가진 카테고리를 만들 수 있으므로 NULL 값을 허용하지 않도록 외래 키 관계를 지정합니다. 이렇게 하면 이제 INNER JOIN을 사용하여 모든 제품과 그 카테고리를 검색할 수 있습니다. 추가 데이터로 인해 작업이 더 많아진 것처럼 보일 수도 있지만 SQL 일괄 처리에서 부담이 큰 LEFT JOIN을 제거할 수 있으므로 아주 유용한 테크닉입니다. 데이터베이스의 보드 전체에 이 개념을 사용하면 처리 시간이 크게 줄어듭니다. 불과 몇 초도 사용자에게는 많은 것을 의미하며 온라인 데이터베이스 응용 프로그램을 액세스하는 사용자가 많을 경우 이 시간은 더욱 길어진다는 것을 기억하십시오.


Cartesian 곱을 현명하게 사용하기

이 팁의 경우에는 일반적인 의견과 달리, 특정한 경우 Cartesian 곱을 사용하도록 주장합니다. Cartesian 곱(CROSS JOIN)은 많은 비난을 받았으며 개발자들은 종종 이 제품을 절대 사용하지 말 것을 권고 받습니다. 많은 경우 이 제품은 부담이 너무 커서 효과적이지 않습니다. 하지만 SQL의 여느 도구와 마찬가지로 이 도구 역시 적절히 사용하기만 하면 유용합니다. 예를 들어, 그 달에는 주문을 하지 않은 고객에 대해서조차 매달 데이터를 반환할 쿼리를 실행하고 싶다면 Cartesian 제품이 아주 편리할 것입니다. 그림 2 (영문)의 SQL이 바로 그렇습니다.



이 방법이 마법처럼 여겨지지는 않겠지만 Customer에서 Orders로의 표준 INNER JOIN을 실행하여 월별로 묶고 판매를 요약하면 해당 고객이 주문을 한 월만을 반환 받게 된다는 점을 생각해 보십시오. 그렇게 되면 고객이 제품을 주문하지 않은 달에 대해서는 0 값을 반환 받지 못합니다. 월별 판매가 표시된 고객별 그래프를 만들려고 한다면 시각적으로 식별할 수 있도록 월 판매가 0인 달도 포함된 그래프를 그리고 싶을 것입니다. 그림 2 (영문)의 SQL을 사용하면 Order 테이블에 비판매 관련 행이 없으므로 이 데이터는 판매 금액이 0인 달은 건너뜁니다(발생하지 않은 것은 저장하지 않는 것으로 가정됩니다).



그림 3 (영문)의 코드는 더 길지만, 판매가 없는 달에 대해서도 모든 판매 데이터를 받는다는 목표를 달성할 수 있습니다. 먼저, 지난 해 모든 달의 목록을 받아서 첫 번째 TABLE 데이터 형식 테이블(@tblMonths)에 넣습니다. 그러면 이 코드는 그 기간 동안 판매를 한 모든 고객의 회사명 목록을 받아 또 다른 TABLE 데이터 형식 테이블(@tblCus-tomers)에 넣습니다. 이 두 테이블은 실제 판매 수치를 제외하고는 결과 집합을 만드는데 필요한 기본 데이터를 모두 저장합니다.


첫 번째 테이블에는 모든 달(12행)이 표시되며 그 기간 동안 판매를 한 모든 고객은 두 번째 테이블(81)에 표시됩니다. 모든 고객이 지난 12달 동안 매달 제품을 구입하지는 않았으므로 INNER 또는 LEFT JOIN을 실행하면 매달 모든 고객을 반환하지는 않으며 고객이 뭔가를 구입한 달과 그 고객만을 반환합니다.


Cartesian 곱은 모든 달에 대해 모든 고객을 반환할 수 있습니다. Cartesian 곱은 기본적으로 첫 번째 테이블에 두 번째 테이블을 곱하므로 첫 번째 테이블의 행 수 곱하기 두 번째 테이블의 행수가 들어 있는 행 집합이 만들어 집니다. 따라서 Cartesian 곱은 @tblFinal 테이블에 972행을 반환합니다. 마지막 단계는 이 날짜 범위동안 개별 고객에 대한 월간 총 판매량을 사용해 @tblFinal 테이블을 업데이트하고 최종 행 집합을 선택하는 것입니다.


Cartesian 곱은 리소스 집약적이므로 진짜 이 제품이 필요한 것이 아니라면 CROSS JOIN을 주의해서 사용하는 것이 좋습니다. 예를 들어, 제품과 카테고리에 CROSS JOIN을 실행한 다음 WHERE 절, DISTINCT 또는 GROUP BY를 사용하여 대부분의 행을 필터링하는 경우 INNER JOIN을 사용하면 훨씬 효과적인 방식으로 동일한 결과를 얻을 수 있습니다. 월간 판매일이 표시된 그래프를 로드하고자 하는 경우처럼 모든 가능성에 대해 데이터가 반환되기를 원하는 경우 Cartesian 곱은 아주 유용할 수 있습니다. 하지만 대부분의 경우 INNER JOIN이 훨씬 더 효율적이므로 다른 용도로는 사용하지 말아야 합니다.


기타


다음은 SQL 쿼리의 효율성을 높이는데 도움이 될 수 있는 다른 몇몇 일반적인 테크닉입니다. 모든 판매 담당자를 지역별로 묶은 다음 그 판매액을 집계하려고 한다고 가정합니다. 단, 데이터베이스에서 활성으로 표시된 판매 담당자만을 원한다고 합니다. 이 판매 담당자를 지역별로 묶은 다음 HAVING 절이나 WHERE 절을 사용하여 활성 상태가 아닌 판매 담당자를 제거할 수 있습니다. WHERE 절에서 이 작업을 하면 그룹화해야 하는 행의 수가 줄어들므로 HAVING 절을 사용하는 것보다 훨씬 효과적입니다. HAVING 절에서 행 기반 기준을 필터링하면 이 쿼리는 WHERE 절에서 제거되었을 데이터를 그룹화합니다.


효율성을 높이기 위한 또 다른 트릭은 GROUP BY 절을 사용하는 대신 DISTINCT 키워드를 사용하여 고유한 데이터 행 목록을 찾아내는 것입니다. 이 경우 DISTINCT 키워드를 사용하는 SQL이 더 효과적입니다. GROUP BY는 집계 함수 (SUM, COUNT, MAX 등)를 계산해야 하는 경우를 위해 남겨 두십시오. 이 외에도, 쿼리가 항상 고유한 행을 반환하는 경우에는 IDSTINCT 키워드를 사용하지 마십시오. 이러한 경우 DISTINCT 키워드는 오버헤드만 가중시킬 뿐입니다.


다양한 테크닉을 사용하여 쿼리를 최적화하고 특정한 비즈니스 규칙을 구현할 수 있다는 것을 보았습니다. 문제는 몇몇 테크닉을 시도하여 그 성능을 비교해 보아야 한다는 것입니다. 가장 중요한 것은 테스트를 하고 또 해야 한다는 것입니다. 이 컬럼의 다음 호에서는 데이터베이스 디자인, 우수한 인덱싱 실행 기준, 그리고 SQL Server 보안 패러다임을 포함한 SQL Server 개념을 살펴 보겠습니다.




제공 : DB포탈사이트 DBguide.net

출처 : 한국마이크로소프트

Paul Litwin │Fred Hutchinson Cancer Research Center 수석 프로그래머


ASP.NET 및 Microsoft SQL Server과 같은 강력한 데이터베이스 서버의 고급 서버측 기술을 통해 개발자는 동적인 데이터 중심 웹 사이트를 매우 쉽게 만들 수 있습니다. 하지만 ASP.NET 및 SQL의 기능은 SQL 주입 공격이라는 너무나 일반적인 공격 방식을 알고 있는 해커들에게도 쉽게 악용될 수 있습니다.


SQL 주입 공격에 대한 기본 개념은 다음과 같습니다. 사용자가 텍스트 상자에 텍스트를 입력할 수 있도록 웹 페이지를 만들고 이러한 텍스트는 데이터베이스에 대한 쿼리를 수행하는 데 사용됩니다. 해커는 이러한 텍스트 상자에 쿼리의 특성을 변경하여 백엔드 데이터베이스에 침입하거나 데이터베이스를 손상시킬 수 있는 잘못 형성된 SQL 문을 입력합니다. 어떻게 이런 일이 가능할까요? 몇 가지 예를 통해 이러한 방법에 대해 설명하겠습니다.


SQL 문의 변환

여러 ASP.NET 응용 프로그램에서는 그림 1에 표시된 것과 같은 폼을 사용하여 사용자를 인증합니다. 사용자가 BadLogin.aspx의 Login 단추를 클릭하면 사용자가 폼의 텍스트 상자 컨트롤에 입력한 값과 UserName 및 Password가 일치하는 Users 테이블에 있는 레코드 수를 계산하는 쿼리를 실행하여 cmdLogin_Click 메서드가 사용자를 인증하도록 시도합니다.


대부분의 경우 폼은 정확히 의도된 대로 작동합니다. 사용자는 Users 테이블에 있는 레코드와 일치하는 사용자 이름 및 암호를 입력합니다. 동적으로 생성된 SQL 쿼리를 사용하여 일치하는 행의 개수를 검색합니다. 그런 다음 사용자를 인증하고 요청된 페이지로 리디렉션합니다. 잘못된 사용자 이름 및/또는 암호를 입력하는 사용자는 인증되지 않습니다. 하지만 이 경우에도 해커가 UserName 텍스트 상자에 겉보기에는 잘못된 것이 없는 다음과 같은 텍스트를 입력하여 유효한 사용자 이름 및 암호를 알지 못하더라도 시스템에 침입할 수 있습니다.

' Or 1=1 --

해커는 잘못 형성된 SQL을 쿼리에 주입하여 시스템에 침입합니다. 이 경우의 해킹은 다음과 같이 사용자가 입력한 고정 문자열 및 값의 연결을 통해 실행 쿼리가 형성되기 때문에 작동됩니다.

string strQry = "SELECT Count(*) FROM Users WHERE UserName='" + txtUser.Text + "' AND Password='" + txtPassword.Text + "'";

유효한 사용자 이름인 "Paul"과 암호 "password"를 사용자가 입력하는 경우 strQry는 다음과 같이 됩니다.

SELECT Count(*) FROM Users WHERE UserName='Paul' AND Password='password'

하지만 해커가 다음을 입력하면

' Or 1=1 --

쿼리가 다음과 같이 됩니다.

SELECT Count(*) FROM Users WHERE UserName='' Or 1=1 --' AND Password=''

이중 하이픈은 SQL에서 주석의 시작 부분을 나타내므로 쿼리는 다음과 같이 됩니다.

SELECT Count(*) FROM Users WHERE UserName='' Or 1=1

식 1=1은 테이블의 모든 행에 대해 항상 True이고 다른 식이 포함된 True 식 or'd는 항상 True를 반환합니다. 따라서 User 테이블에 적어도 하나 이상의 행이 있다고 가정할 경우 이 SQL은 항상 0이 아닌 레코드 개수를 반환합니다.


일부 SQL 주입 공격에는 폼 인증이 포함되지 않습니다. 폼 인증과 관련한 SQL 주입 공격에 필요한 사항은 동적으로 구성된 일부 SQL과 트러스트되지 않은 사용자 입력이 있는 응용 프로그램입니다. 정확한 조건만 주어진다면 이러한 공격으로 인한 피해 범위를 해커의 SQL 언어 및 데이터베이스 구성에 대한 지식 수준으로만 제한할 수 있습니다.


이제 BadProductList.aspx에서 가져온 그림 2에 표시된 코드를 살펴 보십시오. 이 페이지는 Northwind 데이터베이스의 제품을 표시하고 사용자가 txtFilter라는 텍스트 상자를 사용하여 제품 결과 목록을 필터링하도록 할 수 있습니다. 마지막 예에서와 같이 이 페이지는 실행 SQL이 사용자가 입력하는 값으로 동적으로 생성되기 때문에 SQL 주입 공격에 당할 가능성이 높습니다. 이러한 특정 페이지는 약삭빠른 해커가 공격하여 기밀 정보를 훔치고, 데이터베이스의 데이터를 변경하고, 데이터베이스 레코드를 손상시키고, 심지어는 새로운 데이터베이스 사용자 계정을 만들 수도 있기 때문에 해커에게는 천국과도 같습니다.


SQL Server를 포함한 대부분의 SQL 호환 데이터베이스는 메타데이터를 sysobjects, syscolumns, sysindexes 등의 이름으로 일련의 시스템 테이블에 저장합니다. 즉, 해커는 이러한 시스템 테이블을 사용하여 데이터베이스에 대한 스키마 정보를 확신하고 추가적인 데이터베이스 손상을 위한 도움을 얻을 수 있습니다. 예를 들어 다음과 같이 txtFilter 텍스트 상자에 입력된 텍스트는 데이터베이스에서 사용자 테이블의 이름을 확인하는 데 사용될 수 있습니다.

' UNION SELECT id, name, '', 0 FROM sysobjects WHERE xtype ='U' --

UNION 문은 해커가 한 쿼리의 결과를 다른 쿼리로 분할할 수 있도록 하기 때문에 해커에게 특히 유용합니다. 이러한 경우 해커는 데이터베이스의 사용자 테이블 이름을 제품 테이블의 원래 쿼리로 분할합니다. 여기에 사용된 방법은 단지 열의 개수와 데이터 형식을 원래의 쿼리와 일치시키는 것 뿐입니다. 이전 쿼리는 Users라는 테이블이 데이터베이스에 있음을 나타낼 수 있습니다. 두 번째 쿼리는 Users 테이블에 있는 열을 노출시킬 수 있습니다. 해커는 이러한 정보를 사용하여 txtFilter 텍스트 상자에 다음을 입력할 수 있습니다.

' UNION SELECT 0, UserName, Password, 0 FROM Users --

이 쿼리를 입력하면 그림 3에서와 같이 Users 테이블에 있는 사용자 이름 및 암호를 노출시킵니다.



SQL 주입 공격은 또한 데이터를 변경하거나 데이터베이스를 손상시키는 데에도 사용될 수 있습니다. SQL 주입 해커는 txtFilter 텍스트 상자에 다음을 입력하여 첫 번째 제품의 가격을 $18에서 $0.01로 바꾸고 이러한 사실을 다른 사람이 눈치채기 전에 일부 제품을 재빠르게 구매할 수 있습니다.

'; UPDATE Products SET UnitPrice = 0.01 WHERE ProductId = 1--

이러한 해킹은 SQL Server에서 세미콜론이나 공백을 사용하여 구분된 여러 SQL 문을 함께 입력할 수 있도록 허용하기 때문에 가능합니다. 이 예에서 DataGrid는 아무 것도 표시하지 않지만 업데이트 쿼리는 성공적으로 실행됩니다. 이러한 같은 기술을 사용하면 DROP TABLE 문을 실행하거나 새로운 사용자 계정을 만들고 이 사용자를 sysadmin 역할에 추가하는 시스템 저장 프로시저를 실행할 수도 있습니다. 이러한 해킹은 모두 그림 2에 표시된 BadProductList.aspx 페이지를 사용하여 가능합니다.


동일한 해킹 기회


SQL 주입 공격은 SQL Server에만 국한된 문제가 아닙니다. Oracle, MySQL, DB2, Sybase 등의 다른데이터베이스에서도 이러한 종류의 공격을 받을 수 있습니다. SQL 주입 공격은 SQL 언어에 다음과 같이 강력하고 유연한 여러 기능이 포함되어 있기 때문에 가능합니다.


이중 하이픈을 사용하여 SQL 문에 주석을 포함시킬 수 있는 기능
여러 SQL 문을 함께 입력하고 이를 일괄 처리로 실행할 수 있는 기능
SQL을 사용하여 표준 시스템 테이블 집합으로부터 메타데이터를 쿼리할 수 있는 기능


일반적으로 데이터베이스에서 지원되는 SQL 언어의 기능이 강력할수록 데이터베이스에 대한 공격 가능성도 높아집니다. 따라서 SQL Server가 주입 공격의 일반적인 대상이 되는 것입니다.


SQL 주입 공격은 ASP.NET 응용 프로그램으로만 제한되지 않습니다. 기존의 ASP, Java, JSP 및 PHP 응용 프로그램도 모두 같은 위험이 있습니다. 실제로 SQL 주입 공격은 데스크톱 응용 프로그램에 대해서도 수행될 수 있습니다. 예를 들어 이 문서에 대한 다운로드 파일(이 문서의 맨 위에 있는 링크로 제공)에 SQL 주입 공격을 받을 수 있는 SQLInjectWinForm이라는 Windows Forms 응용 프로그램 예제를 포함되어 있습니다.


SQL 주입 공격 방지를 위한 한 두 개의 핵심 방법을 쉽게 설명할 수도 있지만 이 문제에 대해서는 계층적 방식을 사용하는 것이 가장 좋습니다. 이러한 방식에서는 일부 취약성으로 인해 보안 방식 중 하나가 무효화되더라도 계속해서 보호 상태를 유지할 수 있습니다. 권장되는 계층은 그림 4에 설명되어 있습니다.




모든 입력에 대한 검사 수행


그림 4에 나열된 첫 번째 원칙은 매우 중요한 것입니다. 모든 사용자 입력은 악의적인 것으로 간주하십시오! 데이터베이스 쿼리에서 검사되지 않은 사용자 입력을 사용해서는 안됩니다. 특히 RegularExpressionValidator 컨트롤과 같은 ASP.NET 유효성 검사 컨트롤은 사용자 입력의 유효성을 검사하기 위한 훌륭한 도구입니다.


유효성 검사에 대한 기본적인 두 가지 방식은 문제가 있는 문자를 허용하지 않거나 적은 수의 필수 문자만 허용하는 것입니다. 하이픈과 작은따옴표와 같은 문제가 되는 일부 문자를 쉽게 허용하지 않을 수도 있지만 이 방법은 두 가지 이유로 인해 적합하지 않을 수 있습니다. 첫 번째, 해커에게 유용하게 사용되는 문자를 놓칠 수 있으며, 두 번째 잘못된 문자를 표현하는 방법이 여러 가지일 수 있습니다. 예를 들어 해커는 작은따옴표를 이스케이프 처리하여 유효성 검사 코드에서 놓치도록 만들거나 이스케이프 처리된 따옴표를 데이터베이스에 전달하여 일반적인 작은따옴표 문자와 동일하게 취급되도록 할 수 있습니다. 더 나은 방법은 허용 가능한 문자를 식별하고 해당 문자만 허용하는 것입니다. 이러한 방식에는 더 많은 작업이 필요하지만 입력에 대해 보다 세밀한 제어가 가능하며 보다 안전합니다. 어떤 방식을 사용하던 간에 일부 해킹에는 많은 수의 문자가 필요하므로 입력에 대한 길이를 제한할 수 있습니다.


GoodLogin.aspx(다운로드 코드에서 제공)에는 두 개의 일반 식 유효성 검사 컨트롤이 포함되며, 이 중에서 하나는 사용자 이름에 대한 컨트롤이고 다른 하나는 암호에 대한 컨트롤입니다. 또한 여기에는 4-12개의 숫자, 알파벳 문자 및 밑줄로 입력을 제한하는 다음과 같은 ValidationExpression 값이 포함되어 있습니다.

[\d_a-zA-Z]{4,12}

사용자가 텍스트 상자에 잠재적으로 손상될 가능성이 있는 문자를 입력하도록 허용해야 할 수 있습니다. 예를 들어 사용자 이름의 일부로 작은따옴표(또는 어포스트로피)를 입력해야 할 수 있습니다. 이러한 경우 정규 식이나 String.Replace 메서드를 사용하여 각각의 작은따옴표를 두 개의 작은따옴표로 바꾸면 작은따옴표를 안전하게 렌더링할 수 있습니다. 예를 들면 다음과 같습니다.

string strSanitizedInput = strInput.Replace("'", "''");


동적 SQL 방지

이 문서에서 설명하는 SQL 주입 공격은 모두 동적 SQL의 실행을 기반으로 합니다. 즉, 사용자가 입력한 값과 SQL을 연결하여 생성되는 SQL 문입니다. 하지만 매개 변수가 있는 SQL을 사용하면 해커가 SQL을 코드에 주입할 수 있는 가능성이 크게 줄어듭니다.


그림 5의 코드는 매개 변수가 있는 SQL을 사용하여 주입 공격을 방지합니다. 매개 변수가 있는 SQL은 사용자가 임시 SQL을 사용해야 하는 경우 뛰어난 성능을 보여 줍니다. 이러한 방식은 IT 부서에서 저장 프로시저를 믿지 않거나 버전 5.0까지 이를 지원하지 않은 MySQL과 같은 제품을 사용하는 경우에 필수적입니다. 하지만 가능하다면 추가 기능에 대해 저장 프로시저를 사용하여 데이터베이스에 있는 기본 테이블에 대한 모든 권한을 제거하여 그림 3에 표시된 것과 같은 쿼리를 만들 수 있는 가능성을 제거해야 합니다. 그림 6에 표시된 BetterLogin.aspx는 procVerifyUser라는 저장 프로시저를 사용하여 사용자에 대한 유효성을 검사합니다.



최소 권한으로 실행

BadLogin.aspx 및 BadProductList.aspx에서 보여진 잘못된 구현 방법 중 하나는 sa 계정을 통해 연결 문자열을 사용한다는 점입니다. 다음은 Web.config에서 발견할 수 있는 연결 문자열입니다.

<add key="cnxNWindBad" value="server=localhost;uid=sa;pwd=;database=northwind;" />


이 계정은 로그인 생성과 데이터베이스 삭제 등 거의 모든 작업을 수행할 수 있는 System Administrators 역할로 실행됩니다. 말할 필요도 없이 응용 프로그램 데이터베이스 액세스에 대해 sa(또는 고급 권한이 있는 계정)를 사용하는 것은 매우 잘못된 생각입니다. 대신 제한된 액세스 계정을 만들고 이를 사용하도록 하는 것이 훨씬 좋습니다. GoodLogin.aspx에 사용된 계정은 다음과 같은 연결 문자열을 사용합니다.

<ADD key="cnxNWindGood" value="server=localhost;uid=NWindReader;pwd=utbbeesozg4d; database=northwind;" />


NWindReader 계정은 db_datareader 역할에 따라 실행되며 이 역할은 데이터베이스에 대한 테이블의 읽기로 액세스를 제한합니다. BetterLogin.aspx는 저장 프로시저와 WebLimitedUser 로그인을 사용하여 상황을 더욱 향상시켜 줍니다. 이 로그인에는 해당 저장 프로시저를 실행하는 권한만 포함되어 있으며 기본 테이블에 대해서는 어떠한 권한도 없습니다.


기밀 정보를 안전하게 저장하기

그림 3에 표시된 SQL 주입 공격은 Users 테이블의 사용자 이름과 암호를 노출시킵니다. 이러한 종류의 테이블은 일반적으로 폼 인증을 적용하는 경우에 사용되며 대부분의 응용 프로그램에서 암호는 일반 텍스트로 저장됩니다. 더 나은 방법은 데이터베이스에서 암호화된 암호 또는 해시된 암호를 저장하는 것입니다. 해시된 암호는 암호를 해독할 수 없기 때문에 암호화된 암호보다 안전합니다. 해시에 salt(암호화 방식으로 안전한 임의 값)를 추가하여 해시된 암호의 보안 성능을 더욱 높일 수도 있습니다. BestLogin.aspx에는 사용자가 입력한 암호를 SecureUsers 테이블에 저장된 암호의 salt로 지정된 해시된 암호와 비교하는 코드가 포함되어 있습니다(그림 7 참조). 해시된 퍼즐에 대한 또 다른 측면은 AddSecureUser.aspx에서 볼 수 있습니다. 이 페이지를 사용하면 salt로 지정되어 해시된 암호를 생성하고 이를 SecureUsers 테이블에 저장할 수 있습니다. BestLogin.aspx 및 AddSecureUser.aspx는 모두 그림 8에 표시된 것과 같이 SaltedHash 클래스 라이브러리의 코드를 사용합니다. Jeff Prosise가 만든 이 코드는 System.Web.Security 네임스페이스의 FormsAuthentication.HashPasswordForStoringInConfigFile 메서드를 사용하여 암호 해시를 만들고 System.Security.Cryptography 네임스페이스의 RNGCryptoServiceProvider.GetNonZeroBytes 메서드를 사용하여 16바이트의 임의 salt 값(Convert.ToBase64String을 사용하여 문자열로 변환할 겨우 24 문자가 됨)을 만들 수 있습니다.


SQL 주입 공격과 직접 관련되지는 않지만 BestLogin.aspx는 연결 문자열의 암호화라는 또 다른 최선의 보안 구현 방법을 보여 줍니다. 연결 문자열은 포함된 데이터베이스 계정 암호가 들어 있는 경우 BestLogin.aspx에서와 같이 특히 중요하게 보호되어야 합니다. 데이터베이스에 연결하려면 연결 문자열의 암호를 해독해야 하기 때문에 연결 문자열은 해시할 수 없습니다. 그 대신 연결 문자열을 암호화해야 합니다. 다음은 Web.config에 저장되어 있고 BestLogin.aspx에서 사용되는 암호화된 연결 문자열을 보여 줍니다.

<ADD key="cnxNWindBest" value="AQAAANCMnd8BFdERjHoAwE/ Cl+sBAAAAcWMZ8XhPz0O8jHcS1539LAQAAAACAAAAAAADZgAAqAAAABAAAABdodw0YhWfcC6+ UjUUOiMwAAAAAASAAACgAAAAEAAAALPzjTRnAPt7/W8v38ikHL5IAAAAzctRyEcHxWkzxeqbq/ V9ogaSqS4UxvKC9zmrXUoJ9mwrNZ/ XZ9LgbfcDXIIAXm2DLRCGRHMtrZrp9yledz0n9kgP3b3s+ X8wFAAAANmLu0UfOJdTc4WjlQQgmZElY7Z8" />

BestLogin은 그림 9에서와 같이 SecureConnection 클래스로부터 GetCnxString 메서드를 호출하여 cnxNWindBest AppSetting 값을 검색하고 이를 다음 코드로 암호 해독합니다.

string strCnx = SecureConnection.GetCnxString("cnxNWindBest");

순서대로 SecureConnection 클래스는 호출을 Win32 DPAPI(데이터 보호 API)로 래핑하는 DataProtect 클래스 라이브러리(여기에 표시되지는 않지만 이 문서의 다운로드에 포함되어 있음)를 호출합니다. DPAPI의 다양한 기능 중 하나는 사용자를 위해 암호화 키를 관리하는 것입니다. DataProtect 클래스 라이브러리 및 이 라이브러리를 사용할 때 고려해야 하는 추가 옵션에 대한 자세한 내용은 Microsoft 패턴 및 연습 가이드 중 하나인 "보안적인 ASP.NET 응용 프로그램 작성: 인증, 권한 부여 및 보안 통신 (영문)"을 참조하십시오.



EncryptCnxString.aspx 페이지를 사용하면 시스템 특정 암호화된 연결 문자열을 만들어서 구성 파일에 붙여 넣을 수 있습니다. 이 페이지는 그림 10에 표시되어 있습니다. 물론, 암호화하거나 해시해야 하는 기밀 정보에는 암호 및 연결 문자열 외에도 신용 카드 번호 및 해커에게 노출될 경우 위험할 수 있는 기타 모든 정보가 포함됩니다. ASP.NET 2.0에는 암호 해싱 및 연결 문자열 암호화를 단순하게 만들어 주는 여러 기능이 포함되어 있습니다.


안전한 실패

런타임 예외를 적절하게 처리하지 못할 경우에도 해커는 이를 악용할 수 있습니다. 따라서 모든 프로덕션 코드에 예외 처리기를 포함시키는 것이 중요합니다. 또한 처리된 예외 및 처리되지 안은 예외는 항상 해커에게 도움이 될 수 있는 정보를 최소한으로만 제공해야 합니다. 처리된 예외의 경우 오류 메시지에서 원래 사용자에 대한 유용성과 악의적인 해커에게 너무 많은 정보를 제공하지 않는 보안성 간의 균형을 맞추어야 합니다.


처리되지 않은 예외의 경우에는 컴파일 요소(Web.config 파일)의 디버그 특성을 False로 설정하고 customErrors 요소의 모드 특성을 On 또는 RemoteOnly로 설정하여 해커에게 최소한의 정보만 표시되도록 해야 합니다. 예를 들어 다음을 보십시오.

<COMPILATION <br defaultLanguage="c#">debug="false" /> <CUSTOMERRORS <br mode="RemoteOnly">/>

RemoteOnly 설정은 localhost로부터 사이트에 액세스하는 사용자에게는 유용한 오류 메시지를 제공하고 원격 위치로부터 사이트에 액세스하는 다른 사용자에게는 예외에 대한 어떤 유용한 정보도 노출시키지 않는 일반적인 오류 메시지를 제공하도록 보장합니다. 로컬 사용자를 포함한 모든 사용자에게 일반 오류 메시지를 표시하도록 하려면 On 설정을 사용하십시오. 프로덕션 환경에서는 Off 설정을 절대로 사용하지 마십시오.


결론

SQL 주입 공격은 보안 시스템에 침입하여 데이터를 훔치거나, 변경 또는 삭제할 우려가 있기 때문에 응용 프로그램 개발자가 신중하게 다루어야 하는 문제입니다. 사용하는 ASP.NET 버전과는 관계없이 이러한 공격에는 너무나 쉽게 취약해질 수 있습니다. 실제로 ASP.NET을 사용하지 않는 경우에도 SQL 주입 공격에 쉽게 당할 수 있습니다. Windows Forms 응용 프로그램과 같이 사용자 입력 데이터를 사용하여 데이터베이스를 쿼리하는 모든 응용 프로그램은 잠재적으로 주입 공격의 대상이 될 수 있습니다.


SQL 주입 공격으로부터 자신을 보호하는 방법은 그렇게 어렵지 않습니다. 모든 사용자 입력에 대한 유효성을 검사 및 확인하고, 동적 SQL을 절대로 사용하지 않고, 최소 권한으로 계정을 사용하고, 해당 기밀 정보를 해시 또는 암호화하고, 해커에게 유용한 정보를 제공하지 않도록 거의 정보를 표시하지 않는 오류 메시지를 제공하는 응용 프로그램은 SQL 주입 공격을 매우 효과적으로 방지할 수 있습니다. 여러 계층의 공격 방어 방법을 사용할 경우 하나의 방법이 실패하더라도 계속해서 보호 상태를 유지할 수 있습니다. [주입 공격 취약성에 대한 응용 프로그램 테스트 정보는 측면 메뉴에 있는 "주입 공격 테스트"를 참조하십시오.]


제공 : DB포탈사이트 DBguide.net

출처명 : 한국마이크로소프트