접속(Connectivity)

유닉스, 매킨토시 등과 같이 마이크로소프트의 운영 체제를 사용하지 않는 컴퓨터에서 SQL Server에 접속하는 방법은? : 유닉스, 매킨토시 등과 같이 마이크로소프트의 운영 체제를 사용하지 않는 컴퓨터에서 SQL Server에 접속하는 방법은?

명쾌하게 답변하기 어려운 질문이다. 마이크로소프트는 도스/윈도우/NT 등의 운영 체제를 사용하지 않는 경우에 필요한 접속 방법을 더 이상 제공하지 않는다. 따라서 다른 업체에서 나온 제품을 사용해야 하는데 다음과 같은 것들이 있다.

현재 사용중인 운영 체제에 맞는 사이베이스의 Open CT-Lib 클라이언트가 있다면 이 제품을 사용해 보면 된다. 사이베이스는 마이크로소프트 외의 운영 체제를 위한 드라이버를 제공하는 데 좀 더 적극적이다. 그런데 SQL Server 4.x 이후로는 사이베이스와 마이크로소프트의 TDS 프로토콜 사용 방법이 서로 다른 길을 걷게 되었다. 그 후로 출시된 마이크로소프트의 6.0, 6.5, 그리고 특히 7.0의 경우에는 사이베이스의 CT-Lib가 제대로 동작하지 않을 수 있다. 사용자는 사이베이스로부터 옛날 버전의 제품을 받아야 하는데 필요한 모든 기능을 지원하지 않을 가능성이 높으며 기술 지원도 받을 수 없다.

공식적으로 알려지지는 않았지만 TDS 프로토콜을 직접 리버스 엔지니어링으로 분석해 볼 수 있다. 다만 SQL Server 버전 마다 변하기 때문에 계속해서 유지 보수를 할 수 있는 경우에 시도해 볼 만 하다. 몇몇 사람이 자신들이 필요로 하는 TDS 부분에 대해서 분석해 본 결과 그렇게 어렵지 만은 않았다고 한다. 한편 FreeTDS 프로젝트라는 것이 있는데 TDS 규약을 리버스 엔지니어링으로 분석하여 TDS를 위한 CTLIB, DBLIB, JDBC 인터페이스를 현재 구현 중에 있다. ODBC와 Perl DBD 드라이버도 계획 단계에 있다. FreeTDS JDBC 드라이버는 타입 4 드라이버로 어떠한 JVM과도 호환된다. CTLIB와 DBLIB 인터페이스는 AIX, 리눅스, FreeBSD 환경에서 아무런 문제 없이 컴파일 되는 것으로 알려져 있다. 보다 자세한 정보는 http://sunsite.unc.edu/freetds/를 참조하기 바란다. 메일링 리스트의 내용도 http://franklin.oit.unc.edu/cgi-bin/lyris.pl?enter=freetds에 보관되어 있다.

Perl에서 SQL Server로 접속하는 것이 괜찮다면 표준 CPAN 사이트에서 SybPerl과 ODBC Perl 인터페이스를 구할 수 있다. 웹에서 검색해 보면 된다(SybPerl은 Sybase를 위해서 설계된 것이기 때문에 CT-Lib와 동일한 호환성 문제를 가지고 있음을 유념해야 한다)

권장하고 싶은 방법은 기술 지원을 받을 수 있는 드라이버 벤더로부터 ODBC/OLE-DB 드라이버를 구하는 것이다. 그러나, 많은 ODBC 벤더들이 순수한 클라이언트 드라이버로부터 사람들이 별로 선호하지 않는 3-tier 드라이버 시스템으로 전환하였거나 ODBC에서 OLE-DB로 이전하였다. 따라서 원하는 드라이버를 찾기가 쉽지는 않을 것이다. 연락해 볼만한 벤더는(무순) 다음과 같다.

OpenLink (http://www.openlink.com)

Intersolv (http://www.intersolv.com)

Visigenic (http://www.visigenic.com)

필자가 아는 한 가장 완벽한 ODBC/JDBC/OLDDB 벤더 목록은 Ken North에 의해서 다음과 같이 운영되고 있다.

ODBC (http://ourworld.compuserve.com/homepages/Ken_North/odbcvend.htm)

JDBC (http://ourworld.compuserve.com/homepages/Ken_North/jdbcvend.htm)

OLDDB (http://ourworld.compuserve.com/homepages/Ken_North/oledbven.htm)

SQL Server와 클라이언트의 NT 도메인 을 방금 변경하였는데 왜 더 이상 서버에 접속할 수 없는 것인가?

이것은 SQL Server의 문제가 아니라 NT의 문제이다. NT 인증을 필요로 하는 Net-Lib(네임드 파이프나 멀티프로토콜)를 사용하고 있다면 SQL Server를 실행하고 있는 NT로부터 인증을 받아야 한다. 인증을 받는지는 클라이언트의 명령 프롬프트에서 “NET VIEW \서버이름”과 같이 실행해 보면 확인할 수 있다. 만약 접근 거부(Access Denied) 메시지나 패스워드를 입력하라고 나오면 인증을 받지 못하는 것이다.

이런 경우에는 도메인 간에 트러스트 관계를 설정해야 한다. 또는 TCP/IP 소켓과 같이 인증이 필요 없는 Net-Lib를 사용하면 된다.

어떤 Net-Lib의 속도가 가장 빠른가?

일반적인 LAN과 속도가 빠른 WAN 환경에서는 어떤 Net-Lib를 사용해도 응답 시간의 차이를 느끼기 어렵다. 이런 여건에서는 응용 프로그램/데이터베이스/서버의 응답 시간에 비하면 네트워크의 응답 및 패킷 수가 미치는 영향은 미미하기 때문이다.

그러나, 64kbps 이하 정도로 네트워크의 속도가 느린 경우에는 TCP/IP 소켓 Net-Lib를 사용하면 향상된 성능을 경험할 수 있다.

정확한 userid를 사용해도 SQL Server에서 “login failed” 에러가 발생한다.

매우 흔하게 발생하는 문제다. “login failed” 는 “접속할 수 없음” 만을 뜻하는 일반적인 메시지이기 때문에 로그온 인증 문제가 아닌 경우가 많다. 다음과 같이 점검해 본다.

SQL Server가 요구하는 것은 NT가 아니라 SQL Server의 로그인과 패스워드임을 기억해야 한다. SQL Server의 디폴트 userid는 “sa”이고 패스워드는 없다.

“네트워크” 접속 문제일 수 있다. 엔터프라이즈 매니저로 로컬 서버를 등록한다면 “.” 으로 등록해야 한다(따옴표는 없이). 서버와 동일한 컴퓨터에 존재하는 툴에서 서버 이름을 지정하지 않고 접속해 본다. 그러면 로컬 네임드 파이프를 통해서 접속을 시도하게 된다.

SQL Server가 접속할 수 없는 상태일 수도 있다. \log\errorlog 파일을 점검해서 확인하도록 한다. SQL Server가 단일 사용자 모드로 기동 되어 있는 것은 아닌지도 확인해야 하는데 다음의 레지스트리 키를 점검하면 된다.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

\MSSQLServer\Parameters

여기에는 SQLArg0, SQLArg … 등의 타입 파라미터가 있는데 단일 사용자 모드는 “/m”에 해당된다. 만약 이것이 있다면 삭제한다.

마지막으로 만약 userid가 정확한 것이 확실하다면 바이러스를 검사하는 소프트웨어가 방해를 하는 경우가 있으므로 이를 정지 시킨다.

특정한 컴퓨터에서만 접속할 수 있도록 SQL Server를 설정하려면 어떻게 해야 하는가?

SQL Server에는 이를 위한 기능이나 툴이 포함되어 있지 않다. 또한 이와 같은 기능을 하는 스토어드 프로시저를 접속 시에 실행하는 기능도 없다. 따라서 다음과 같은 방법을 사용해야 한다.

SQL Server를 방화벽 뒤에 설치하여 방화벽이 사용자의 접근을 제한하도록 한다. 가장 안전하고 원하는 기능을 제대로 구현하는 방법이다.

사용자가 직접 ODS 게이트웨이를 만들어서 클라이언트로 하여금 SQL Server 대신 ODS 게이트웨이를 가리키도록 한다. 그러면 ODS 게이트웨이가 먼저 클라이언트를 점검하게 되는 방식이다. 그러나 클라이언트가 SQL Server로 직접 접속하는 것을 막을 방법은 없다. 마이크로소프트의 웹 사이트에서 무료로 다운로드 받을 수 있는 SQL Programmers Toolkit에 보면 ODS 코드에 대한 예제가 나와 있다.

스토어드 프로시저를 항상 띄워놓거나 스케줄링 하여 sysprocesses에 있는 관련 컬럼(net_address)을 점검하여 실행 중이면 안될 프로세스를 KILL 시키도록 한다. 단, 이 방법은 MAC 어드레스에 대해서만 사용할 수 있다. 그러나 사용자들은 발견되어서 KILL 당하기 전까지는 작업을 할 수 있다.

특정한 프로그램만 접속할 수 있도록 SQL Server의 사용을 제한할 수 있는가?(사용자들이 Access를 이용해서 데이터를 직접 건드리는 것을 막고 싶다)

SQL Server 6.5 이하 버전에서는 이를 위한 툴이나 기능이 없으며 이런 기능을 하는 스토어드 프로시저를 클라이언트에서 접속 시에 실행할 수 있는 방법도 없다. 사실 모든 데이터가 Integrated Security로 정확하게 보호되고 모든 감사(auditing)/비즈니스 룰이 제약 조건(constraint)과 트리거로 구성되어 있다면 사용자들이 어떠한 툴로 접속하든 상관없으므로 걱정할 필요가 없다. 그러나, 실제 상황에서는 이와 같은 사전 준비가 철저하지 못한 경우가 많다.

SQL Server 7.0은 응용 프로그램 롤(Application Roles)과 사용자 롤(User Roles)의 개념을 도입하였다. 만약 SQL Server 7.0을 사용중이라면 응용 프로그램 롤을 사용하면 된다.

만약 아직 6.5 이하의 버전을 사용중이라면 다음의 몇 가지 방법 가운데 하나를 사용해 보면 된다(그러나 이들 모두는 임시 방편에 지나지 않는다).

사용자가 직접 ODS 게이트웨이를 만들어서 클라이언트로 하여금 SQL Server 대신 ODS 게이트웨이를 가리키도록 한다. 그러면 ODS 게이트웨이가 먼저 클라이언트를 점검하는 방식이다. 그러나 클라이언트가 SQL Server로 직접 접속하는 것을 막을 방법이 없다. 마이크로소프트의 웹 사이트에서 무료로 다운로드 받을 수 있는 SQL Programmers Toolkit에 보면 ODS 코드에 대한 예가 나와 있다.

스토어드 프로시저를 항상 띄워놓거나 스케줄링 하여 sysprocesses에 있는 관련 컬럼(program_name)을 점검하여 실행 중이면 안될 프로세스를 KILL 시키도록 한다. 그러나 사용자들은 발견되어서 KILL 당하기 전까지는 작업을 할 수 있다.

서버가 표준 보안을 사용하도록 변경한다. 응용 프로그램에서 접속할 때 트러스티드 커넥션을 요청하게 되고 트러스트된 계정이 접속할 수 있는 유일한 데이터베이스로 접속된다. 이 데이터베이스에는 사용자의 NT 계정과 로그인 명, 패스워드(암호화됨), 그리고 사용할 서버와 데이터베이스 이름을 서로 참조할 수 있는 테이블이 들어 있다. 실행 가능한 유일한 객체는 이 테이블에서 사용자의 레코드를 반환하는 스토어드 프로시저이다. 프론트 엔드에서 이 스토어드 프로시저를 실행하면 패스워드를 해독해서 다시 서버로 접속한다. 이 방법의 단점은 사용자의 실제 이름과 패스워드가 ODBC 트레이스나 네트워크 스니퍼를 통해서 유출될 수 있다는 것이다(몇 가지 ODBC SDK 툴을 이용하면 패스워드를 볼 수 있다).

방화벽을 통해서 SQL Server로 접속하는 방법은?

SQL Server가 사용하는 포트를 열어놓아야 한다. 만약 그렇게 했음에도 접속할 수 없다면 방화벽이 어떠한 패킷을 거부하고 있는지 로그를 확인하거나 방화벽의 양 쪽에서 네트워크를 트레이스 해 본다(테스트 중에는 어떤 패킷이 통과할 수 있는 지를 확인하기 위해서 방화벽을 모든 패킷이 통과하거나 또는 전혀 통과하지 못하도록 변경하게 된다)

어떤 포트를 열어야 하느냐는 사용중인Net-Lib에 따라 다르다.

TCP/IP 소켓의 경우에는 SQL Server가 디폴트로 사용하는 포트는 1433 이다.

멀티 프로토콜(rpc)의 경우에는 포트는 가변적이지만 이를 고칠 수 있다. 보다 자세한 내용은 마이크로소프트의 Knowledgebase Q164667을 참조하면 된다.

IP를 통한 네임드 파이프의 경우(Named Pipe over IP)는 137/138/139 포트가 사용된다. 그런데 이것은 ‘파일/프린트’에서 사용하는 포트와 동일하기 때문에 방화벽에서 이 포트를 여는 것은 그다지 권장되지 않는다.

개발

SQL Server에서 하나의 레코드/페이지에 얼마나 많은 바이트를 저장할 수 있으며 그 이유는 무엇인가? 최대 값을 늘릴 수 있는 방법은 없는가? : SQL Server에서 하나의 레코드/페이지에 얼마나 많은 바이트를 저장할 수 있으며 그 이유는 무엇인가? 최대 값을 늘릴 수 있는 방법은 없는가?

레코드는 페이지 경계를 넘을 수가 없다 – 6.5 이하 버전에서의 페이지 크기는 2K 이며 7.0에서는 8K이다.

6.5이하 버전의 경우 :

2048 바이트 크기의 페이지에서 헤더가 32 바이트를 차지하므로 데이터를 위한 공간은 2016 바이트가 남게 된다. 따라서 1008 바이트 크기의 레코드 2개 또는 201 바이트 크기의 레코드 10개를 한 페이지에 넣을 수 있다. 레코드의 크기에는 데이터 영역 외에 수 바이트 정도의 오버헤드가 포함된다. 길이가 가변적인 컬럼이 있는 경우에는 오버헤드가 좀 더 크다.

하나의 레코드가 2016 바이트가 될 수는 없다. 레코드를 삽입/갱신/삭제 할 때에 전체 레코드가 트랜잭션 로그에 기록이 되기 때문이다. 로그 페이지의 사용 가능한 크기도 2016 바이트인데 트랜잭션 로그와 관련된 정보를 기록하기 위해서 50바이트를 사용하므로 한 레코드의 최대 크기는 1962 바이트가 된다.

길이가 가변적인 컬럼 이나 널 컬럼을 포함한 경우에는 레코드의 크기를 이 값보다 크게 테이블을 만들 수 있다. 그러나 실제로 이보다 큰 값을 삽입/갱신 하려고 하면 실패하게 된다.

이 문제를 해결할 수 있는 방법은 없다. 하나의 레코드가 감당할 수 있는 크기보다 더 많은 데이터를 필요로 하는 경우에는 한 테이블을 여러 개로 나누어서 사용해야 한다.

SQL Server의 TSQL 스크립트나 스토어드 프로시저, 트리거 등에서 레코드나 메시지를 파일로 출력하는 방법은?

SQL Server는 오라클과 같이 간편한 SPOOL 명령을 가지고 있지는 않지만 처리할 수 있는 몇 가지 방법이 있다.

1. xp_cmdshell과 ECHO 명령을 사용한다. > 또는 >> 리다이렉션 기호를 사용하여 파일을 생성하거나 파일 뒤에 추가한다.

xp_cmdshell "@ECHO test message >> C:\file.fil"

2. 원하는 데이터를 테이블(전역 임시 테이블 사용)에 추가한 다음 xp_cmdshell을 이용해서 bcp를 실행하여 파일로 출력한다.

xp_cmdshell "bcp .. out c:\file.fil -Usa -P -c"

3. 직접 확장 스토어드 프로시저를 작성한다. 이것은 C 프로그램이므로 원하는 작업을 위해서 표준 파일 처리 명령을 사용할 수 있다.

4. xp_cmdshell을 이용해서 ISQL로 SELECT 명령을 실행할 때 –o 파라미터를 지정하여 결과를 파일에 출력한다. 아래의 예에서는userid를 직접 적어 넣지 않기 위해서 –E 파라미터를 사용하였다.

declare @str varchar(255)
   select @str = "isql -Q"select * from " -E -oc:\file.fil"
   exec master..xp_cmdshell @str 

SQL Server의 컬럼을 추가/수정/삭제 하는 방법은? 예를 들어 Int에서 Char로, char(5)에서 char(15)로, 또는 NULL을 NOT NULL로 등

SQL Server 7.0에서는 위의 모든 작업을 GUI 또는 제공되는 스토어드 프로시저를 이용해서 쉽게 처리할 수 있다.

SQL Server 6.5 이하 버전에서는 NOT NULL이 아닌 컬럼을 추가하는 것만 가능하다. 그 외의 변경작업이 필요하다면 새로운 테이블을 만들어서 데이터를 부어 넣은 다음 원래의 테이블 이름으로 바꾸는 과정을 거쳐야 한다.

어떤 툴은 이와 같은 작업을 GUI 인터페이스를 통해서 처리하는데 겉에서 보기와는 달리 내부적으로는 앞에서 얘기한 방법대로 작업을 하는 것이므로 크기가 큰 테이블을 변경하면 시간이 오래 걸리게 된다.

이와 같은 기능을 가진 툴로는 마이크로소프트의 비주얼 데이터베이스 툴(비주얼 인터데브에 포함), Sylvain Faust의 SQL-Programmer, 그리고 Embarcadero의 DBArtisan 등이 있다.

마이크로소프트의 비주얼 데이터베이스 툴은 컬럼을 변경할 때 SQL Server가 작업하는 데 있어서 필요한 만큼의 여유 공간이 없을 경우 데이터가 유실되는 문제가 보고된 적이 있으므로 주의해야 한다.

SQL Server에서 케스케이드(cascade) 수정/삭제 함수는 어디에 있는가?

그런 함수는 제공되지 않는다. 원래는 SQL Server 7에서 제공될 것으로 예상되었으나 포함되지 않았으며 SQL Server의 향후 버전에서 추가될 것이다.

트리거를 사용해서 직접 케스케이드 기능을 구현해야 한다. 마이크로소프트의 Knowledgebase Q142480을 보면 이에 관한 자세한 정보와 함께 트리거가 작동하는 방식 때문에 발생할 수 있는 포린 키 문제를 해결하는 방법이 나와 있다.

SQL Server의 필드를 암호화 하는 방법은?

공식적으로는 지원하지 않으며 사용자 정의 함수를 작성할 수도 없으므로 다음과 같은 방법을 사용해야 한다.

1. 직접 확장 스토어드 프로시저(XP)를 작성하여 구현한다. 그러나 함수처럼 사용할 수 없기 때문에 적용하기가 번거롭다 – 컬럼 별로 XP를 실행한 다음 반환된 값을 사용해서 수정해야 한다.

2. VB, C++ 등의 응용 프로그램 레벨에서 처리한다. 대부분의 사람들이 이 방법을 사용하고 있으며 권장되는 방법이기도 하다.

3. ** 단지 자료의 완벽을 기하기 위해서 소개함 **

비공식적으로 포함되어 있는 pwdencrypt()와 pwdcompare() 함수가 있다. – 마이크로소프트에서 내부적으로만 사용되는 함수이기 때문에 앞으로 변경되거나 없어질 수 있다. - 충고를 무시하고 이 함수들을 6.x 버전에서 사용했던 사람들은 SQL Server 7.0 베타버전에서 문제를 경험하였다는 보고가 있었다.

이제 공공연한 비밀이 된 관계로 자료의 완벽함을 기하기 위해서 언급하였지만 이 함수를 사용하면 마이크로소프트로부터 지원을 받을 수 없기 때문에 새로운 스토어드 프로시저/버전에서 문제가 발생하면 사용자 스스로 해결해야 한다.

4. SQL Server 7.1/8.0 등의 다음 버전에서 UDF(사용자 정의 함수)를 지원할 때 까지 기다린다.

5. DB/2, 오라클 등과 같이 UDF를 지원하는 DBMS를 사용한다.

SQL Server를 위해서 ER 다이어그램을 생성해 주는 툴은 어떤 것이 있는가?

ER/Studio (http://www.embarcadero.com)

Case Wise (http://www.casewise.com)

Erwin (http://www.platinum.com)

PowerDesigner (http://www.sybase.com)

InfoModeler (http://www.infomodelers.com)

Visio Professional (http://www.visio.com)

또한 마이크로소프트의 제품에 내장되어 있는 툴도 있다. 단, 이들은 다이어그램만을 그려주는 반면 위에서 소개한 툴들은 광범위한 프로젝트 라이프 싸이클, 리버스 엔지니어링 등의 기능도 가지고 있다.

비주얼 데이터베이스 툴의 ER-다이어그램 기능

SQL Server 7.0에 ER 다이어그램 내장

MS-Access(먼저 모든 SQL 테이블을 연결해야 함)

테이블 명, 컬럼 명 등을 변수로 사용하는 SQL 문은 어떻게 실행해야 하는가?

EXEC 명령을 참조한다.

하나의 컬럼을 SELECT 하는 간단한 예제 :

USE pubs
go
DECLARE @str varchar(255)
DECLARE @columnname varchar(30)
SELECT @columnname="au_lname"
SELECT @str = "SELECT " + @columnname + " FROM authors"
EXEC (@str)

Books Online에 나와 있는 또 다른 예

EXECUTE가 변수를 이용해서 동적으로 구성된 문장을 어떻게 처리하는 지를 보여 준다. 모든 사용자 정의 테이블(타입="U")의 목록을 담고 있는 커서(table_cursor)를 생성한다.

DECLARE tables_cursor CURSOR
    FOR
    SELECT name FROM sysobjects WHERE type = "U"
OPEN tables_cursor
DECLARE @tablename varchar(30)
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
/* 

@@fetch_status가 –2 이면 로우가 삭제되었음을 의미한다이 루프는 모든 사용자 정의 테이블을 삭제하는 것이므로 이를 테스트 할 필요는 없다

*/
    EXEC ("DROP TABLE " @tablename)
    FETCH NEXT FROM tables_cursor INTO @tablename
END
PRINT "All user-defined tables have been dropped from the database."
DEALLOCATE tables_cursor 

SQL Server에서 로우 레벨 락킹(row-level locking)을 걸려면 어떻게 해야 하는가?

SQL Server 7.0에서만 완전한 로우 레벨 락킹을 지원한다.

SQL Server 6.5에서는 "sp_tableoption "테이블 명", "insert row lock", true"가 설정되어 있을 때 테이블의 가장 마지막 페이지에 데이터를 추가하는 경우에 대해서만 로우 레벨 락킹이 사용된다. 자세한 정보는 6.5의 Books Online을 참조하기 바란다.

SQL 6.0 이하의 버전에서는 로우 레벨 락킹이 전혀 지원되지 않는다.

그러나 6.5 이하의 버전에서도 각 로우가 페이지 전체를 차지하고 있다면 페이지에 대한 락킹이 곧 로우에 대한 락킹과 같기 때문에 로우 레벨 락킹을 구현할 수 있다. 레코드의 길이가 1024 바이트 이상이 될 때까지 CHAR NOT NULL 필드로 채움으로써 구현할 수 있다.(로우는 여러 페이지에 걸쳐 있을 수가 없기 때문에 이렇게 하면 한 페이지에 하나의 로우가 존재하게 된다).

그러나 마지막 데이터 페이지에 있는 레코드가 로우 레벨 락킹이 걸려 있는 상태라고 하더라도 관련된 넌클러스터드 인덱스 페이지는 그렇지 못하다. 이로 인해서 경쟁 문제가 발생하거나 심지어는 두 개의 논리적으로 별개인 트랜잭션에서 하나 또는 그 이상의 인덱스 페이지에 서로 다른 순서로 락을 걸 필요가 있을 때 데드락까지 발생할 수 있다.

객체의 소유자를 바꿀 수 있는 방법은?

SQL ㅤServer 7.0에는 이와 같은 기능을 하는 스토어드 프로시저가 있다. 그러나 SQL Server 6.5 이하의 버전에서는 공시적으로 지원되는 방법은 없다.

다만 직접 관련된 시스템 테이블을 수정함으로써 구현할 수는 있다.

시스템 테이블을 수정할 수 있도록 서버를 설정한다.

트랜잭션을 시작한다.

sysobjects 테이블에서 변경하고자 하는 객체의 uid 컬럼을 원하는 값으로 바꾼다.

정확한 개수의 레코드가 변경되었는지 확인한다.

결과에 따라서 트랜잭션을 커밋하거나 롤백시킨다.

시스템 테이블을 수정할 수 없도록 서버를 설정한다.

SQL Server를 재기동 한다.

(시스템 테이블의 일부는 메모리에 존재하기 때문에 이를 갱신하려면 SQL Server를 다시 띄워주어야 한다)

쿼리에서 사용할 수 있는 테이블의 최대 개수는?

SQL Server 6.5 이하의 버전에서는 SQL Server의 종류와 상관 없이(예를 들면 엔터프라이즈 에디션 등) 최대 개수가 16개이다. SQL Server 7.0에서는 최대 값이 256개 이다. 이 수치는 SQL Server의 커널에 직접 규정되어 있으며 마이크로소프트의 개발자들이 나름대로의 이유로 결정한 값이다. 테이블의 개수가 많을수록 쿼리를 적절하게 최적화하는데 많은 시간이 걸리게 된다. 쿼리의 실행 속도와 최적화 속도에는 반비례 관계가 있다.

SQL Server 6.5에서 트레이스 플래그 T105를 설정하면 16개로 지정된 최대 값을 올릴 수 있다. 이 트레이스 플래그는 공식적으로 알려진 것은 아니기 때문에 마이크로소프트에서는 이 트레이스 플래그를 사용하는 시스템에서 발생하는 문제에 대해서는 지원하지 않을 것이다.

그러나 Peoplesoft의 복잡한 쿼리가 실행될 수 있도록 추가된 기능인 만큼 어느 정도의 테스트는 거쳤을 것임에 틀림 없다.

일반적으로 쿼리 시에 16개 이상의 테이블을 사용한다면 쿼리나 데이터베이스의 디자인이 매우 잘못된 것이다. 가장 좋은 방법은 쿼리를 작은 부분으로 분할한 다음 임시 테이블을 사용하여 중간 결과를 보관하도록 하는 것이다. 이렇게 하면 쿼리를 이해하기도 쉬울 뿐 아니라 옵티마이저가 보다 정확한 실행 계획과 인덱스를 선택할 수 있는 가능성이 높기 때문에 성능도 향상될 것이다.

네트워크 속도가 느린 경우 SQL Server 응용 프로그램의 속도를 올릴 수 있는 방법은?

먼저 "느리다"는 의미를 정의할 필요가 있다. 일반적으로는 네트워크가 "느리다"는 것은 64Kbps 이하의 속도를 말한다. 이 정도의 속도에서는 결과 셋의 크기와 오고 가는 네트워크 패킷의 숫자가 전체적인 응답 시간에 큰 영향을 끼치게 된다.

먼저 네트워크를 트레이스 해 보거나 SQL Trace를 사용하여 일반적인 클라이언트 세션에서 정확하게 어떤 것들이 전송되는고 있는지를 확인한 다음 아래의 내용대로 작업한다.

만약 중간 단계의 결과 셋이 반환된다면 로직을 스토어드 프로시저로 작성하여 최종 결과만 반환되도록 처리할 수 없는지 검토한다. 스토어드 프로시저를 가능한 많이 사용하여 SQL 문이 오고 가는 횟수를 줄여야 한다.

ODBC를 사용하는데 있어서 sp_serverinfo, sp_cursor, 임시 스토어드 프로시저 등을 수행하면서 발생하는 오버헤드가 문제를 일으킨다면 가능한 패스쓰루 쿼리를 사용하고 ODBC DSN 속성에서 임시 스토어드 프로시저 생성을 OFF 시킨다.

DB-Lib/ODBC 커넥션이 TCP/IP 소켓 Net-lib를 사용하도록 설정한다. 이렇게 하는 것이 느린 네트워크 환경에서 가장 좋으며 현저한 속도 향상을 가져올 수 있다.

응용 프로그램에서 클라이언트 쪽의 커서를 사용하고 있다면 투명한(transparent) 서버 쪽 커서를 제공하는 ODBC v3 이상의 버전을 사용해 본다.

클라이언트에서 스크린에 보이고자 하는 내용이 단지 앞의 20개인데 1000개의 레코드를 모두 보내는 우를 범하지 말도록 한다.

읽어 들여야 하는 정적인 데이터가 많이 있다면 클라이언트의 Access, SQL Server 6.5 워크스테이션 또는 SQL Server 7.0 등으로 리플리케이션 할 것을 고려해 본다. 네트워크가 느린 경우 이것은 오직 정적인 데이터만을 위해서 사용되어야 한다.

네트워크를 통해서는 어떠한 SQL 문도 보내지 않는다. Citrix 또는 NT 터미널 에디션을 사용하여 응용 프로그램을 중앙에서 실행하도록 하고 리모트 컴퓨터에는 ICA/RDP 클라이언트를 인스톨 한다. 이렇게 하면 용용 프로그램이 SQL Server 바로 옆의 로컬 컴퓨터 내에서 실행된다(동일한 컴퓨터에서 사용하는 것은 바람직하지 않음). 네트워크를 통해서 전달되는 것은 변경된 화면 내용 뿐이며 이것은 최적화되어 압축되었기 때문에 14.4kbps의 모뎀을 통해서도 만족스럽게 동작한다. 리모트 쪽에서 관리해야 하는 클라이언트 코드가 필요 없다는 장점도 있다. Citrix, 마이크로소프트, 컴팩의 사이트에는 이와 같은 방식으로 동작하는 데 필요한 컴퓨터의 성능을 결정하는 것과 관련된 화이트페이퍼가 있다.

SQL Server에서 텍스트/이미지 데이터를 저장하고 조회하는 방법은?

TSQL 스크립트에서 이와 같은 종류의 데이터를 저장하고 조회하려면 표준 INSERT/SELECT 명령 대신 WRITETEXT와 READTEXT를 사용해야 한다. 이것은 Books Online에 예제와 함께 설명되어 있지만 사용하기가 쉽지는 않다. 관련된 프로그래밍 언어 별로 보다 사용하기 쉬운 명령이 있다 - 예를 들면 VB/C++에서 RDO/ADO를 사용할 때에는 GetChunk와 AppendChunk 명령을 사용하면 된다. 그러나 여전히 이미지/텍스트 데이터의 조각/블럭을 한 번에 관리해야 한다. 이와 같은 데이터를 SQL Server에 저장함으로 해서 얻는 유일한 이점은 다른 데이터와 함께 트랜잭션의 일관성을 유지할 수 있다는 것이다.

단지 이미지/텍스트 데이터 전체를 삽입/조회하고자 한다면 \BINN 디렉토리에 있는 TEXTCOPY 프로그램을 사용하면 된다(파라미터를 보려면 textcopy /? 와 같이 하면 됨). 이것은 BCP와 같은 콘솔 프로그램이다.

만약 트랜잭션의 일관성이 중요하지 않거나 프로그램상에서 처리할 수 있다면 데이터베이스 외부에 일반적인 파일 형태로 데이터를 보관하는 것이 처리하기가 간편하다. 데이터베이스에는 파일의 위치에 대한 UNC 포인터만을 저장해 두면 된다. 이렇게 하면 어떠한 툴로 작업을 하건 간에 이름만을 전달해 주면 되므로 데이터를 보여주고 편집하기가 훨씬 용이해진다.

두 개의 SQL Server 데이터베이스에 걸쳐 있는 데이터를 사용하는 방법은?

SQL Server 6.5 이하의 버전에서는 두 데이터베이스가 동일한 서버에 존재한다면 간단히 처리할 수 있다. 테이블의 이름 전체를 지정하면 된다. :

select * from ..

select * from db1..tab1, db2..tab1 where ...........................

데이터베이스가 별개의 서버상에 존재하는 경우에는 6.5 이하의 버전에서 제공하는 몇가지 원격 스토어드 프로시저를 사용하면 된다(관련 문서 참조). 그러나 이것은 서버 측의 선택 사항으로 직접 관련된 테이블을 사용할 수 있는 방법은 없다. 직접 테이블을 사용해야 한다면 각 데이터베이스에 별도로 접속한 다음 클라이언트에서 데이터를 처리해야 한다. JET 엔진과 같은 것을 이용하면 이 작업을 좀 더 간편하게 할 수 있는데 두 개의 서버를 마치 하나처럼 보이게 할 수 있다.

SQL Server 7.0에서는 객체의 이름을 지정할 때 서버까지 포함시킬 수 있기 때문에 하나의 커넥션만을 맺어도 여러 서버간의 테이블들을 사용할 수 있다. 다음과 같이 하면 된다.

select * from server1.db1..tab1, server2.db1..tab1 where ...............

SELECT를 이용하여 테이블 생성하기

오라클은 테이블을 데이터와 함께 복사할 수 있는 다음과 같은 구문을 제공한다 이를 사용하면 각종 테스트와 비교 작업을 매우 수월하게 할 수 있다.

CREATE TABLE tab_dup AS SELECT * FROM tab_orig

이와 같은 기능을 SQL Server에서도 사용할 수 없는가?

SELECT의 결과에 기초하여 테이블을 만드는 방법은 아래의 구문을 사용하면 된다.

SELECTㅤINTO FROM

이를 사용하기 위해서는 "select into/bcp" 데이터베이스 옵션이 설정되어 있어야 한다. 또한 스토어드 프로시저를 만들 때 테이블 이름의 맨 앞에 파운드 기호(#)를 붙이면 이 테이블은 임시 테이블로 생성된다. 이것은 두 가지를 의미한다. 첫째는 이 테이블은 TEMPDB에 생성된다(공간이 부족한 문제가 발생하면 할당한 크기를 점검할 것). 둘째는 스토어드 프로시저의 수행이 끝나면 이 테이블은 자동으로 삭제된다. 또는 단지 첫 번째 테이블과 같은 구조를 갖는 두 번째 테이블을 만들고자 한다면 아래와 같이 WHERE 조건이 결코 만족될 수 없는 SELECT 문을 사용하면 된다.

select * into NewTable from ExistingTable where 1 = 2

기존의 테이블에서 컬럼을 삭제하는 방법은?

테이블을 삭제하고 다시 생성해야 한다(물론 먼저 백업한 다음). 기존의 테이블에 대한 스크립트를 엔터프라이즈 매니저에서 자동으로 생성시킨 다음 이 스크립트를 수정해서 실행하면 된다. 그 다음에 SELECT 시에 컬럼 명을 지정하여 기존 테이블의 내용을 새로운 테이블로 복사하면 된다.

테이블의 정의(definition)를 구할 수 있는 방법은?

테이블의 정의를 보여주는 SQL 쿼리의 포맷은 어떻게 되는가? 예를 들어 다음과 같이 정의되어 있는 CUSTOMERS 테이블이 있다고 하자.

CREATE TABLE CUSTOMERS ( CUSTID varchar(20), NAME varcar(50) )

SQL문을 어떻게 사용하면 위의 정보를 보여줄 수 있을까? 몇 가지 방법이 있겠지만 가장 간단한 방법은 아래와 같다:

sphelp CUSTOMERS

이런 기능을 보유한 몇 가지 제품들이 있는데 그 중의 하나는 SQL-Programmer이며 http://www.sfi-software.com에서 구할 수 있다. 이 제품에 대한 자료는 http://www.sfi-software.com/products.htm에 나와 있다.

왜 DELETE 트리거가 DRI와 함께 동작하지 않는가?

"A.pk"라는 프라이머리 키를 가진 "A" 테이블과 각각 "B.fk"와 "C.fk"라는 포린 키를 가지고 있는 "B","C" 테이블이 있을 때 "A" 테이블에 다음과 같은 DELETE 트리거를 설정하였다.

delete B

from deleted, B

where deleted.pk = B.fk

delete C

from deleted, C

where deleted.pk = C.fk

이 트리거는 메인 테이블의 레코드를 삭제한 다음 다른 테이블의 관련된 레코드를 삭제하게 되어 있는데 다음과 같은 에러가 발생한다.

[Microsoft][ODBC_SQL Server Driver][SQL Server] DELETE Statement conflicted with COLUMN

REFERENCE constraint "B.fk". The conflict

occured in database "...", table "B", column "..." (#547)

[Microsoft][ODBC SQL Server Driver] Command has been aborted (#3621)

이유는 항상 트리거를 유발시킨 SQL 명령이 실행된 다음에 트리거가 수행되기 때문이다. 이를 해결하려면 BEFORE 트리거가 필요하나 아직 이 기능은 지원되지 않는다. 위에서 발생한 에러는 트리거가 아닌 DELETE 문에서 발생한 것으로 B 테이블의 FK에 의해서 참조되고 있는 A 테이블의 PK를 삭제하려고 하기 때문이다. 이것이야말로 바로 DRI가 금지하고 있는 것으로 당연히 에러가 발생하는 것이다.

두 가지 해결방법이 있다.

B와 C의 DRI 제약 조건을 삭제한다. 그러면 트리거는 의도한 대로 동작하게 된다. 또한 B와 C 테이블에 INSERT 트리거를 설정하여 A 테이블에 연관되는 PK가 존재하도록 확실히 해야 한다.

트리거 대신에 스토어드 프로시저를 사용해서 삭제 작업을 처리한다. 그러면 DRI 제약 조건에 부합하도록 DELETE 작업을 정확한 순서대로 진행할 수 있다. 즉, B와 C 테이블에서 먼저 삭제한 다음 A에서 삭제한다. A 테이블의 DELETE 트리거는 이제 불필요하므로 삭제한다. 이것은 DRI와 호환되는 방법이다.

DRI를 사용할 때 DELETE 트리거는 주로 삭제된 레코드를 다른 테이블로 복사하여 백업하는 데 이용된다. 케스케이드 삭제가 직관적으로는 옮게 느껴지겠지만 실제로는 동작하지 않는다. 이것은 자주 문의 되는 내용이기 때문에 FAQ에 포함되어야 한다.

트리거를 이용한 케스케이드 효과는 중첩(nesting)이 OFF되어 있지 않은 경우(sp_configure 사용)에 한해서 16단계 이내의 깊이까지만 허용된다. 또한 트리거는 자신을 재귀적으로 호출할 수 없다. 만약 트리거가 동일한 테이블의 다른 레코드에 영향을 주게 되면 다음의 레코드부터는 트리거가 실행되지 않는다. 대조적으로 스토어드 프로시저에는 이와 같은 제약사항이 없다.

확장 스토어드 프로시저(Extended Stored Procedure)

확장 스토어드 프로시저는 보통의 스토어드 프로시저와 같은 규칙을 이용해서 SQㅤL 코드 내에서 실행할 수 있는 DLL이다. DLL이기 때문에 운영 체제와 다른 DLL 및 파일 등을 사용할 수 있다. 이들은 SQL Server의 프로세스/어드레스 공간에서 실행되기 때문에 SQL Server를 다운 시킬 위험도 내포하고 있다.