CodeBlack

Question :

You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.

Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.

Sample Input

Sample Output

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

Explanation

The example describes following four projects:

• Project 1: Tasks 1, 2 and 3 are completed on consecutive days, so these are part of the project. Thus start date of project is 2015-10-01 and end date is 2015-10-04, so it took 3 days to complete the project.
• Project 2: Tasks 4 and 5 are completed on consecutive days, so these are part of the project. Thus, the start date of project is 2015-10-13 and end date is 2015-10-15, so it took 2 days to complete the project.
• Project 3: Only task 6 is part of the project. Thus, the start date of project is 2015-10-28 and end date is 2015-10-29, so it took 1 day to complete the project.
• Project 4: Only task 7 is part of the project. Thus, the start date of project is 2015-10-30 and end date is 2015-10-31, so it took 1 day to complete the project.

Answer :

select *
from(
    select MIN(val) as startdate, MAX(val) as enddate
    from(
        (select ROW_NUMBER() over(order by (select End_Date)) as ID, End_Date as val
        from Projects
        where End_Date not in (select Start_Date
        from Projects)

        union all

        select ROW_NUMBER() over(order by (select Start_Date)) as ID, Start_Date as val
        from Projects
        where Start_Date not in (select End_Date
        from Projects))
         ) Y
    group by ID
) Z
order by datediff(dd,startdate,enddate), startdate

쿼리 바리로기로 등록하면 편한 쿼리

--단축키 쿼리
/*
도구 > 옵션 > 환경 > 키보드 > 쿼리 바로 가기
*/
--ctrl+3 : stored procedure, function 내용 보기
sp_helptext test_procedure

--ctrl+4 : 해당 문자열을 포함하는 프로시저 찾기
SELECT DISTINCT A.NAME, A.TYPE FROM dbo.SYSOBJECTS AS A INNER JOIN dbo.SYSCOMMENTS AS B ON A.ID = B.ID WHERE (A.TYPE = 'P' OR A.TYPE = 'FN' OR A.TYPE = 'TF') AND B.TEXT LIKE 

--ctrl+5 : 해당 컬럼명을 사용하는 테이블 찾기
SELECT T.name AS table_name, C.name AS column_name FROM sys.tables AS T INNER JOIN sys.columns AS C ON T.object_id = C.object_id WHERE C.name =

매월 마지막날 출력 쿼리

DECLARE @v_YMD CHAR(8)
DECLARE @v_tmp CHAR(8)

SET @v_YMD = '20180201'

WHILE @v_YMD <= '20190601' BEGIN
set @v_tmp = CONVERT(CHAR(8),Dateadd(day, -1, @v_YMD),112)
print @v_tmp
SET @v_YMD = CONVERT(Char(8), DateAdd(month, 1, @v_YMD), 112)
END

SELECT 문 사용할 때 with(nolock)를 사용하는 경우가 종종 있다.

with(nolock)을 사용하는 이유는 트랜잭션 격리성 때문인데 해당 내용에 대해 정리 글

Transaction Isolation Level(트랜잭션 격리 수준)이란?

  - A 트랜잭션이 실행되고 있을 때 다른 B 트랜잭션의 접근 권한에 대한 수준을 이야기 합니다.

  - 트랜잭션 격리 수준 단계가 높아질수록 격리 수준이 높아지며 데이터 일관성은 높아지지만 동시성은 저하 됩니다.

Transaction Isolation 관련 문제점

  - Dirty Read : 커밋되지 않은 데이터를 읽어 해당 트랜잭션이 롤백 되면 오류 데이터를 읽을 수 있는 문제점

  - Non-Repeatable Read : 한 트랜잭션이 반복하여 Read 중일 때 다른 트랜잭션이 접근하여 데이터를 변경하여 커밋하여도 커밋된 결과가 반영 되지 않는 문제점

  - Phantom Read : 한 트랜잭션 안에서 일정 범위 레코드를 두번 이상 읽을 때, 첫번째 쿼리에서 없던 레코드가 두번째 쿼리에서 나타나는 문제점

Transaction Isolation Level 단계

  1. Read Uncommitted

        - 트랜잭션 실행 중 다른 트랜잭션이 접근 가능(트랜잭션이 격리되지 않음)

        - A트랜잭션이 데이터를 변경 중 일 때 B트랜잭션이 접근하여 커밋되기전 변경된 데이터를 읽을 수 있음.

        - 만약 커밋되지 않고 A트랜잭션이 롤백되면 B트랜잭션의 결과값은 잘못된 데이터가 됨(Dirty Read 발생)

        - Non-Repeatable Read 발생 가능

        - Phantom Read 발생 가능

  2.  Read Committed

        - 커밋되기 전 바뀐 데이터를 읽을 수 없음.(Dirty Read 발생 X)

        - Non-Repeatable Read 발생 가능

        - Phantom Read 발생 가능

  3.  Repeatable Read

        - 항상 한 트랜잭션의 일관성 있는 데이터 읽기를 보장(Dirty Read, Non-Repeatable Read 발생 X)

        - A트랜잭션 실행 중 B 트랜잭션에서 Insert가 발생 후 동일 조건의 Select문의 A트랜잭션과 B트랜잭션의 결과가 달라짐(Phantom Read 발생)

        - A트랜잭션이 실행 중일 때 다른 트랜잭션이 DB의 내용을 변경해도 서로 영향을 미치지 않는다.

  4.  Serializable

        - 가장 높은 격리 수준

        - 트랜잭션이 완료될 때까지 Select 문장이 사용하는 모든 데이터에 Shared Lock이 걸리므로 다른 트랜잭션이 접근 불가

        - Dirty Read, Non-Repeatable Read, Phantom Read 모두 발생 안함.

위 내용을 아래 표로 정리하였습니다.

* DB별 Default isolation

격리 수준과 with(nolock) 힌트의 관계

    -    With(nolock)힌트는 격리성 제한이 걸린 DataBase의 Read Uncommitted를 허용한다.(격리성 관련 문제점 발생)

    -    격리성 제약조건 있는 DB에 실시간으로 데이터를 확인할 때 주로 사용한다.

    -    데드락이나 교착상태가 발생하지 않는다.

    -     Rollback이 될 수 있는 데이터까지 읽기 때문에 데이터 일관성은 저하된다.

*with(nolock)과 유사한 with(readuncommitted)힌트도 있습니다~!

*본문에 사용된 이미지 출처 : https://lng1982.tistory.com/287

TABLE COLUMN 속성 변경

1. 제약조건이 없는 테이블 컬럼 변경 SQL 쿼리

2. 속성을 변경할 테이블 컬럼에 제약조건이 걸려 있는 경우

1번 쿼리를 실행하면 

Msg 5074, Level 16, State 1, Line 2
개체 'DF_SampleTable_Name'은(는) 열 'Name'에 종속되어 있습니다.
Msg 4922, Level 16, State 9, Line 2
하나 이상의 개체가 이 열에 액세스하므로 ALTER TABLE ALTER COLUMN Name이(가) 실패했습니다

위와 같은 에러메세지가 발생한다.

위 에러 메세지는 속성을 변경할 해당 컬럼에 "Primary key"가 설정되어 있거나 "기본값 또는 바인딩" 또는 "그 외 제약 조건"이 설정되어 있어서 발생하는 경우이다.

위 에러 메세지를 해결하기 위해 해당 컬럼에 적용된 제약조건을 제거하고 컬럼 속성을 변경 또는 삭제하면 된다.

먼제 삭제할 제약 조건의 이름을 확인하기 위해 Table에 걸린 제약 조건 확인 SQL 쿼리를 실행한다.

위 쿼리로 Table에 설정된 Key를 확인할 수 있다.

기본값 또는 바인딩 제약 조건명은 컬럼 변경 SQL문을 실행할 때 발생하는 오류 메세지에서 확인 할 수 있다. 위 오류 메세지에서는 "DF_SampleTable_Name" 이 Name컬럼에 걸린 기본값 또는 바인딩을 설정한 제약 조건의 이름이다.

제약 조건 삭제 SQL 쿼리

위 쿼리로 제약조건을 제거 할 수 있다.

제약 조건을 제거하면 1번에서 소개한 쿼리문을 실행하여 테이블 컬럼 속성을 변경할 수 있다.

* 단 컬럼 내 데이터가 변경할 데이터 타입으로 형식이 아니면 컬럼 데이터 타입 속성 변경이 실패한다.

MS SQL Stored Procedure

저장 프로시저란?

- SQL Query문들을 하나의 함수처럼 실행하기 위한 Query의 집합

장점 

1. 하나의 요청으로 여러 SQL 쿼리를 실행하여 네트워크 부하 감소
2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 함으로 처리 시간 감소
3. 최초 사용한 SP는 캐싱이 되어 재사용이 용이
4. 사용자에게 테이블 접근 권한을 대신 프로지서 접근 권한을 주어 DataBase 보안성 강화
5. 모듈식 프로그래밍 가능

위와 같이 SSMS(2014) 에서 클릭하면 아래와 같은 파일이 생긴다

각 매개 변수칸에 입력 값을 넣어주어 실행하면 SP가 만들어 진다.

예제 SP를 실행하기 위한 table 셋팅

위 쿼리를 실행하면 테이블을 만들고 만들어진 테이블 내용을 확인할 수 있다.

동적쿼리와 sp_executesql을 이용한 입력 변수와 출력변수가 있는 SP 예제

- @input의 입력 값의 유무에 따라 WHERE절을 @Sql String에 추가하여 Sql문이 새로 캐싱 되는 SP