SQLite3와 SQL Server의 SQL 차이 메모

시간이 갈수록 DBMS 개발사에서 개발자가 사용에 편리하도록 기능을 추가하고 수정하는 경향은 당연한 듯 보이지만 각 DBMS의 특성 때문에 어쩔 수 없이 서로 다른 문법을 유지할 수밖에 없는 것은 개발자가 감수해야 하는 상황으로 보인다. C# 닷넷으로 개발한다면 DbProviderFactories를 이용하여 서로 다른 DBMS를 사용하는 환경 속에서도 응용 프로그램의 변경을 최소화할 수 있는데 이 경우에도 DBMS 간의 SQL 차이 있는 부분은 어쩔 수 없이 서로 다른 SQL을 사용해야 한다. 물론 단순 SELECT, INSERT, UPDATE, DELETE 명령을 사용한다면 하나의 응용 프로그램 로직으로 여러 가지의 DBMS에 대응할 수 있다. 본 포스팅에서는 SQLite3와 SQL Server의 SQL 차이 몇 가지를 메모한다.

 

■ 테이블이 존재하는지 검사하는 SQL

if (dbtype == DBTypes.SQLite)
  cmd.CommandText = "SELECT COUNT(*) cnt FROM sqlite_master WHERE name = '" + 테이블이름 + "'";
else 
  cmd.CommandText = "SELECT CASE WHEN OBJECT_ID(N'" + 테이블이름 + "', N'U') IS NOT NULL THEN 1 ELSE 0 END";

SQL은 서로 다르지만 스키마에  테이블이름이 존재하면 1을 리턴하고 없으면 0을 리턴한다.

■ UPSERT SQL

if (dbtype == DBTypes.SQLite)
  cmd.CommandText = "INSERT OR REPLACE INTO config (cfgname, cfgstr) VALUES ('" + 환경이름 + "', '" + 환경값 + "')";
else
  cmd.CommandText = "MERGE config AS Target USING (SELECT '" + 환경이름 + "', '" + 환경값 + "') AS Source (cfgname, cfgstr) " +
	"ON (Target.cfgname = Source.cfgname) WHEN MATCHED THEN UPDATE SET cfgstr = Source.cfgstr " +
	"WHEN NOT MATCHED THEN INSERT (cfgname, cfgstr) VALUES (Source.cfgname, Source.cfgstr);";

DBMS 종류 별로 지원 여부와 차이가 많이 있는 SQL 구분으로 특정한 키 값이 존재하면 UPDATE를, 존재하지 않으면 INSERT를 수행하는 구문이다. 위의 예제에서는 cfgname 컬럼을 키로 사용한다. SQLite3는 "INSERT OR REPLACE INTO" 구문을 지원해서 비교적 간편하게 사용할 수 있다. SQL Server는 MERGE 구문을 활용한다. MERGE는 두 테이블을 비교해서 병합하는데 사용하는 구문이지만 USING에 SELECT로 단일 행을 가진 가상의 테이블과 병합하는 방식이다. MERGE 구문 끝에는 반드시 세미콜론을 붙여야 한다.

 

■ 쿼리 결과에서 첫행만 추출하는 SQL

if (dbtype == DBTypes.SQLite)
  cmd.CommandText = "SELECT datval FROM histtbl WHERE tagid = @tid ORDER BY dattime DESC LIMIT 1";
else 
  cmd.CommandText = "SELECT TOP 1 datval FROM histtbl WHERE tagid = @tid ORDER BY dattime DESC";

쿼리 결과에서 처음 일부만 추출하는 LIMIT 구문은 다양한 DBMS에서 지원하지만 SQL Server는 TOP 구문을 사용해야만 한다. LIMIT는 문장 후반에 기술하지만 TOP는 SELECT 바로 다음에 사용하는 차이도 있다.

■ 문자열 추출 SQL

SELECT tagid, datval, SUBSTR(dattime, 6, 2) mm FROM pgmrpt;

위와 같이 문자열 추출 함수는 SUBSTR를 사용하는데 SQL Server는 지원하지 않고 SUBSTRING으로 기술해야 한다. 다행히 SQLite3에서는 SUBSTRING도 지원하므로 이 경우에는 SUBSTRING으로 통일해서 사용하면 된다.

 

■ 자동 증분 ID(Auto Increment)

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
//--------------------------------------
CREATE TABLE users (
    id INTEGER IDENTITY(1, 1) PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

컬럼을 Auto Increment 설정하는 방식은 SQL Server와 SQLite3 모두 정수 타입으로 지정하고 Primary key 옵션을 지정하는것 까지는 동일한데, SQL Server는 IDENTITY 구문을 추가해 주어야 한다. 위의 에제는 1부터 1씩 증가시키라는 의미이다. 두가지 DBMS 모두 INSERT 구문에서 자동 증가하는 컬럼을 제외하고 "INSERT INTO users (username) VALUES ('Hong');"처럼 수행하면 id는 자동 증가한 값이 삽입된다.

 

■ SUM, TOTAL 함수

SELECT TOTAL(datval) tot FROM rpthist WHERE tagid = 'TAG1';

위와 같이 SQLite3에서는 TOTAL 함수와 SUM를 별도로 제공한다. SUM은 대상이 정수면 정수형으로 값을 리턴하고 항목에 NULL이 있으면 NULL을 리턴하는 특성이 있다. 그렇지만 SQL Server는 타입과 무관하게 SUM 함수만 지원하므로 주의해야 한다. TOTAL 함수는 해당하는 행이 없어도 0을 리턴하는데 이것을 SQL Server에 적용하고 싶다면 ISNULL(SUM(datval), 0)과 같이 적용할 수 있다.

■ SubQuery에서 임시 테이블 이름 지정

 

SELECT tagid, dd, sum(datval) sumval FROM 
(SELECT tagid, datval, dd FROM rpthist WHERE tagid like 'TAG%') tmptbl GROUP BY tagid, dd;

위와 같은 서브 쿼리 구문은 SQLite3와 SQL Server에서 모두 정상적으로 동작한다. 문제는 SQLite3 에서는 tmptbl로 지정한 임시 테이블 이름을 지정하지 않아도 정상 동작하지만 SQL Server에서는 이름을 기술하지 않으면 오류가 발생하므로 주의해야 한다.