'개발/MS-SQL'에 해당되는 글 12건

--첫번째 줄 일자는 시작 일자

--마지막 줄 일자는 시작일자와 종료일자

SELECT CONVERT(char(10),DATEADD(d,number,'2018-01-01'),120) DT, CONVERT(char(8),DATEADD(d,number,'2017-12-01'),112) DT2
FROM master..spt_values
WHERE type = 'P' and number <= DATEDIFF(d,'2018-01-01','2018-12-31')

mssql db복원할때 이미 db명이 있을때

"복원 데이터베이스가 사용 중이어서 배타적으로 액세스할 수 없습니다." 라는 경고창이 뜬다.

이럴때 블로그나 그런데 올린글 보면 대부분 db속성가서 엑세스 제한을 바꾸라는 글뿐이다.....

잘못 update했거나 delete해서 복구 할려고 매일 백업된 파일을 백업할려는데 계속 위와 같이 배타적 액세스 할수 없다고만 한다..

이럴때

1.

restore filelistonly from disk = 'E:\DB_Backup\DB_backup.bak'  <-백업파일

2.

Restore database 생성할DB명  <--여기에 DB명을 정하면 그 DB명으로 생성된다.

from disk = 'E:\DB_Backup\DB_backup.bak' 
 with move 'LogicalName' to 'D:\DB_Backup\dbTemp_full.mdf',   <- 앞부분은 1번 실행시 컬럼명이 LogicalName 첫번째 복사해서 붙여 넣어주고 뒷부분은 mdf파일 위치를 넣어준다.
 move 'LogicalName_log' to 'D:\DB_Backup\dbTemp_full_log.ldf'  <- 앞부분은 1번 실행시 컬럼명이 LogicalName 두번째 log부분 복사해서 붙여 넣어주고 뒷부분은 ldf파일 위치를 넣어준다.
 

 BACKUP DATABASE [db명] TO DISK= 'E:\DB_Backup\DB_backup.bak' 

use db명

go

declare @sql as nvarchar(100);
declare @tbname as nvarchar(100);
declare c cursor fast_forward for
select [name] from sysobjects where xtype ='u' or xtype ='p' order by name;
open c;
fetch next from c into @tbname;

while @@fetch_status=0
begin
 set @sql='alter schema dbo transfer 스키마명.'+@tbname;
 exec(@sql);
 fetch next from c into @tbname;
end
close c;
deallocate c;

* 커서 (Cursor)

가능한한 SQL Server 커서의 사용을 피한다.
SQL Server 커서는 select문에 비해 성능상에 좋지 않다. 행단위의 처리가 필요하다면 상관질의나 유도된 테이블을 사용하도록 노력하자.

-- 결과를입력할임시테이블

create table #temp( 

   문항 int, 

    답변자 nvarchar(10),

    답변내용 nvARCHAR(100) 

) 

-- 커서

DECLARE @문항 int, @답변자 nvarchar(10), @답변내용 int

DECLARE @CNT INT, @복합답변 nVARCHAR(100)

DECLARE myCur CURSOR FOR

    SELECT 문항,답변자,답변내용 FROM TEST ORDER BY 문항,답변자,답변내용

OPEN myCur

FETCH NEXT FROM myCur INTO @문항, @답변자, @답변내용

WHILE (@@FETCH_STATUS=0) 

BEGIN 

     SET @CNT= ISNULL((SELECT COUNT(*) FROM #temp WHERE 문항=@문항 AND 답변자=@답변자),0)

    IF @CNT=0 

    BEGIN

      INSERT INTO #temp(문항,답변자,답변내용) 

      values (@문항, @답변자, convert(nvarchar,@답변내용))

    END

    ELSE

    BEGIN

      UPDATE #temp

      SET 답변내용=답변내용+','+convert(nvarchar,@답변내용)

      WHERE 문항=@문항 AND 답변자=@답변자

    END

    FETCH NEXT FROM myCur INTO @문항, @답변자, @답변내용 

END 

CLOSE myCur

DEALLOCATE myCur 

DROP TABLE #TEMP

1. 유명한 NOT IN 기법.

초반 페이지는 큰 문제가 없으나, 뒤쪽으로 갈수록 많은 데이터를 NOT IN 처리 해야 하기때문에 문제가 많아진다.

SELECT TOP (@PAGESIZE) SEQ, THREAD, DEPTH, WRITER, TITLE, READCOUNT, WRITEDATE FROM BLUE_BOARD
WHERE THREAD NOT IN
(SELECT TOP (@PAGESIZE * (@PAGE-1)) THREAD FROM BLUE_BOARD ORDER BY THREAD DESC)
ORDER BY THREAD DESC

기본형태

첫페이지 끝페이지(50000)

4 ????(엄청나게 느림)

검색과 정렬 추가시

첫페이지 끝페이지(50000)

50 35000

정적으로 검색,정렬 하였을때

첫페이지 끝페이지

10 8000 (검색실패시 3000)

글을 수정하였다 역시 잘못되었다.

무지무지무지 느리다.

----------> 해당 쿼리를 동적. 쿼리로 제작하였을 경우 마지막 페이지가 1초가 걸리는 괴?상한 현상이 계속 목격되고 있다.

----------> 검색시 4초 가량이 소요되었고 검색 실패시에도 2초 가량이 소요되었다 -----> 동적쿼리는 일반적으론 나쁘다고 하는데(물론 좋은 경우도 있겠지만) 100만건 기준으로 정말로 1~2초라면.... 사용해도 되지 않을까

2. IN 기법.

NOT IN의 문제를 개선했다고 한다.

하지만 역시나 후반페이지에 문제가 많다고 한다. 테스트 해보진 않았다.

3. MIN 기법

MIN을 이용한 기법.

SELECT TOP (@PAGESIZE) SEQ, THREAD, DEPTH, WRITER, TITLE, READCOUNT, WRITEDATE
FROM BLUE_BOARD
WHERE THREAD <=
(
SELECT MIN(THREAD)
FROM
(
SELECT TOP (((@page-1)*20)+1) THREAD
FROM BLUE_BOARD
ORDER BY THREAD DESC
) AS A
)
ORDER BY THREAD DESC

기본형태

첫페이지 끝페이지(50000)

5 200

검색과 정렬 추가시엔 테스트를 해보지 않았다.

4. 인덱스를 이용한 기법

빠르다고 한다. 그러나 페이징의 헛점이 많다고 한다.

5. ROW_NUMBER()를 이용한 기법중. 하나

2005부터 사용 가능하다.

특이사항으로는 총 카운트 T2.CNT 와, 라스트페이지를 CEILING(T2.CNT/@PAGESIZE) AS 'LASTPAGE'

테이블에 같이 집어넣는다는것이다. 좀더 개선할수 있을듯 하다.

속도는 좋을것같아 보인다.

SELECT * FROM
(
SELECT
(
ROW_NUMBER() OVER(ORDER BY THREAD DESC)
) AS ROWNUM,*
FROM BLUE_BOARD
) AS A
WHERE A.ROWNUM BETWEEN ((@PAGE-1)*@PAGESIZE)+1 AND @PAGE*@PAGESIZE

기본형태

첫페이지 끝페이지

5 1400

검색과 정렬 추가시

첫페이지 끝페이지

80000 80000

유명한 ROWNUM 기법 치고는 생각보다 느리다. 어째성?;;;;

뭐지? 내 테스트 쿼리가 잘못된것인가? ㅋㅋㅋㅋㅋ

정적으로 검색,정렬 하였을때

첫페이지 끝페이지

8000 12000 (검색 실패시 3000)

정도가 소요되었다.

생각보다 안좋다-_-;

6. MAX,MIN 조합기법

위의 3번 쿼리인 MIN쿼리와 흡사하다

해당 방법은 쿼리가 길다는 문제이다.(쿼리가 복잡해지면 아래 쿼리도 그만큼 복잡해진다)

DECLARE @MAXCNT INT
,@MINCNT INT

SELECT @MINCNT = (ISNULL(MIN(THREAD),0)),
@MAXCNT = (ISNULL(MAX(THREAD),0))
FROM
(
SELECT TOP(@PAGE * @PAGESIZE) THREAD
FROM BLUE_BOARD
ORDER BY THREAD DESC
) T

SELECT @MAXCNT = MIN(THREAD)
FROM
(
SELECT TOP(((@PAGE-1)*@PAGESIZE)+1) THREAD
FROM BLUE_BOARD
WHERE THREAD BETWEEN @MINCNT AND @MAXCNT
ORDER BY THREAD DESC
) T

SELECT TOP(@PAGESIZE) * FROM BLUE_BOARD
WHERE THREAD BETWEEN @MINCNT AND @MAXCNT
ORDER BY THREAD DESC

기본형태

첫페이지 끝페이지(50000)

10 290

꽤나 빠른데? 하지만 검색과 정렬을 넣는다면 좀 복잡해지는 단점이....

7. SELECT TOP 11 * BLUE_BOARD WHERE ID <= 마지막 번호 (ORDER BY DESC)

페이징이라고 하기엔 약간 꼼수?틱한 방법인데

TOP 11은 사실 글을 10개만 보여주는것이다 그리고 1은 다음 페이지의 첫 결과가 되는것이다

계속 11개씩 불러서 10개만 페이지에 뿌려주는 ㅎㅎㅎ

SELECT TOP 11 * FROM BLUE_BOARD ORDER BY THREAD DESC
SELECT TOP 11 * FROM BLUE_BOARD WHERE SEQ <= 11 ORDER BY THREAD DESC

동접사수 많고 글수가 어마어마 하다면 이방법이 제일일듯 하다.

그러나 이전페이지는... 어떻게 구현을 해야할지

8. CTE

이녀석도 결국은 ROW_NUMBER를 의존하는데

일종의 뷰? 같은것을 생성하여 그것으로 페이징한다고 생각하면 될듯하다.(물론 이게 아니지만...)

Set nocount on을 빼먹으면 아예 작동조차 하지 않는다 와이?

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH PAGELIST AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY THREAD DESC) AS ROWNUM
FROM BLUE_BOARD
)
SELECT * FROM PAGELIST
WHERE ROWNUM BETWEEN ((@PAGE-1)*@PAGESIZE)+1 AND @PAGE*@PAGESIZE

기본형태

첫페이지 끝페이지

2 1300

검색과 정렬 추가시

첫페이지 끝페이지

63000 ????

정적 검색과 정렬 추가시

첫페이지 끝페이지

1000 12000

별다른 조건이 없었을때는 제법 나쁘지 않은듯 싶다

// 총 개수 - 리스트 쿼리

DBCC CHECKIDENT('테이블',RESEED,초기화할 숫자)

초기화할 숫자 = 0   0으로하면 1부터 시작하게 된다, 1로 하게되면 2로 시작 하게 된다.

IDENTITY_INSERT가 OFF로 설정되면 테이블 '이름' 열에 명시적 값을 삽입할 수 없습니다. 라는 에러가 발생하면

아래와 같이 실행해주면 됩니다. 아래와 같이 세팅하면 기존에 있던 번호를 지우고 동일한 번호로 추가한다던지,

삭제하여 빈 번호를  재사용 한다던지가 가능! 완전 편해 ㅠ.ㅠ

Ex)

SET IDENTITY_INSERT testTable ON;

insert into testTable (col1, col2) select coll1, coll2 from testTable2
SET IDENTITY_INSERT testTable OFF;

위와 같이 사용하면 되며 insert 후에는 다시 OFF 처리를 해야 한다.

또한 insert select절 인 경우 기존의 insert select와 달리 insert 할 컬럼을 정확히 명시해야 한다.

저장 프로시저 sp_dbcmptlevel을 사용하여 현재 데이터베이스의 호환성 수준을 80 이하로 설정하십시오.

호환성 수준 확인
sp_dbcmptlevel 'db이름'

호환성을 80버전 수정
sp_dbcmptlevel 'db이름', 80