긍정적 사고, 음식의 절제, 규칙적인 운동

[MSSQL] Sub Query 서브쿼리에서 정렬하기

SELECT 에서 중첩 서브쿼리 또는 스칼라 서브 쿼리에서는 order by 를 사용하지 못한다. 

하지만, TOP-N  ~ ORDER BY 는 사용가능하다. 

SELECT *
  FROM (SELECT TOP 10 
               USER_ID
             , USER_NAME
          FROM USERS
         ORDER BY UP_DATE DESC 
       ) A

그냥 order by 는 사용할 수 없다고 나오지만, TOP-N을 사용하면 order by  가 가능하다. 

그래서, 전체를 호출하고 싶다면 전체 개수 만큼 TOP-N 을 지정해주면 된다. 

SSMS(SQL Server Management Studio) 다운로드 : https://docs.microsoft.com/ko-kr/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16 

SSMS 다운로드

SSMS 19 미리 보기 2를 다운로드하려면 SSMS 19 다운로드를 방문하세요.

SSMS(SQL Server Management Studio ) 18.12.1 무료 다운로드

SSMS 18.12.1은 최신 GA(일반 공급) 버전입니다. 이전 GA 버전의 SSMS 18이 설치된 경우 SSMS 18.12.1을 설치하면 이 버전이 18.12.1로 업그레이드됩니다.

• 릴리스 번호: 18.12.1
• 빌드 번호: 15.0.18424.0
• 릴리스 날짜: 2022년 6월 21일

-- with(nolock)

MSSQL 은 기본적으로 SELECT 시 공유잠금(S Lock) 이 걸린다.
즉, DML(INSERT, UPDATE, DELETE) 작업중인 ROW 또는 TABLE 에 SELECT 할 경우,
해당 작업이 끝나야 SELECT 할 수 있다는 의미이다.  
이를 방지하기 위해 WITH(NOLOCK) 을 사용할 수 있다.
WITH(NOLOCK) 의 격리수준(Isolation Level)은 Read Uncommitted 와 같다고 보면 된다.
즉, SELECT 할 ROW 또는 TABLE 이 잠겨있어도(작업중이여도) 기다리지 않고 조회 (Dirty Read) 하겠다라는 의미이다.
이를 통해 조회 성능이 올라가고, 데드락(Deadlock) 을 방지할 수 있다.
 

MS-SQL에서 내부적으로 지원하는 문서화되지 않은 문자열 함수인 PWDENCRYPT와 PWDCOMPARE를 통해 암호화 기능을 구현할수 있다.

<사용예제>

' 테이블을 생성한다, 암호화할 컬럼은 VARBINARY 타입으로 한다. 

CREATE TABLE MEMBER
(
  UserId VARCHAR(25),
  UserPwd VARBINARY(100)
)

' 데이터를 입력한다. 암호화할 컬럼은 PWDENCRYPT메소드를 이용해서 넣는다.

INSERT INTO MEMBER (UserId, UserPwd)

       VALUES ('smith', PWDENCRYPT('1234')) 

' 해당아이디를 가진 회원의 암호화된 비밀번호 컬럼과 입력한 문자열 비밀번호를

' PWDCOMPARE(입력문자열, 컬럼명) 메소드를 이용해 비교한다.

' 둘이 같으면 1 (true), 틀리면 0 (false)를 리턴하게 된다.

' 그리고 원문이나 암호문 둘 중 하나가 NULL 이면 NULL을 리턴하게 된다.

SELECT PWDCOMPARE('1234', UserPwd)

       FROM MEMBER WHERE UserId='smith'  ' 결과 : 1

결과를 살펴보면 대소문자는 구분하지 않음을 알 수 있다. 암호문의 경우 위에서 살펴본대로 대소문자를 분명 다르게 비교하였으나, PWDCOMPARE 함수에서는 대소문자를 무시하였다.

또한, 실행 결과를 보면 형태는 원문의 길이에 상관없이 30~35자 사이의 거의 일정한 길이의 암호문을 출력하는 것을 볼 수 있다.  테스트 결과 원문의 길이가 varchar 기준으로 128자를 넘었을 경우 에러를 발생 하였다.

구문

JSON_MODIFY ( expression , path , newValue )  

기본 쿼리

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')

PRINT @info

-- Insert surname  

SET @info=JSON_MODIFY(@info,'$.surname','Smith')

PRINT @info

-- Set name NULL 

SET @info=JSON_MODIFY(@info,'strict $.name',NULL)

PRINT @info

-- Delete name  

SET @info=JSON_MODIFY(@info,'$.name',NULL)

PRINT @info

-- Add skill  

SET @info=JSON_MODIFY(@info,'append $.skills','Azure')

PRINT @info

결과

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

https://docs.microsoft.com/ko-kr/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-ver15 

[MSSQL] 특정 기간에 해당하는 모든 날짜 (MASTER..SPT_VALUES) 

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20170213'), 112) AS 'DATE' 
FROM MASTER..SPT_VALUES 
WHERE TYPE = 'P' AND NUMBER <= DATEDIFF(D, '20170213', '20170730')