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

[MSSQL] STRING_AGG(Transact-SQL) 문자열 식의 값을 연결하고 그 사이에 구분 기호 값을 추가합니다. 구분 기호는 문자열 끝에 추가되지 않습니다.

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16 

[MSSQL]  STRING_SPLIT(Transact-SQL)

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16 

지정된 구분 기호 문자에 따라 문자열을 부분 문자열의 행으로 분할하는 테이블 반환 함수입니다.

SELECT ProductId, Name, Tags  
FROM Product  
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

MSSQL 링크드서버,  linked server

MSSQL 은 연결된서버 기능을 제공하는데 이를 이용하면 다른 네트워크의 데이터베이스를 원격으로 접속하여

   사용할 수 있도록 해줍니다. 

-- MSSQL 연결된 서버 생성

EXEC sp_addlinkedserver
      @server = '[연결된 서버별칭]',
      @srvproduct = '',
      @provider = 'SQLOLEDB',
      @datasrc = '[서버 아이피]',
      @catalog = '[데이터 베이스명]'



-- MSSQL 연결계정 생성

EXEC sp_addlinkedsrvlogin
      @rmtsrvname= '[연결된 서버별칭]',
      @useself= 'false',
      @rmtuser = '[사용자 이름]',
      @rmtpassword = '[사용자 암호]'
      
      
-- MSSQL 연결된 서버 확인
   SELECT * FROM master.dbo.sysservers WHERE srvname = '[연결된 서버별칭]'
   

-- MSSQL 연결계정 확인
   SELECT * FROM master.sys.linked_logins WHERE remote_name = '[사용자 이름]'
   
   
-- MSSQL 연결된 서버 이용방법 
   /*연결된 서버를 등록한 후 사용하려면 [연결된 서버별칭].[데이터 베이스명].[데이터베이스 소유자명].[테이블명]
   형태로 호출하여 사용할 수 있습니다.
   SELECT 쿼리를 예로 들면 아래와 같습니다. */

 -- MSSQL 일반서버에 SELECT 쿼리시
   SELECT [컬럼명] FROM [테이블명] WHERE [조건절]

-- MSSQL 연결된 서버에 SELECT 쿼리시
   SELECT [컬럼명] FROM [연결된 서버별칭].[데이터 베이스명].[데이터베이스 소유자명].[테이블명] WHERE [조건절]

-- MSSQL 연결된 서버 삭제
  EXEC sp_dropserver
      @server = '[연결된 서버별칭]'

-- MSSQL 연결계정 삭제
   EXEC sp_droplinkedsrvlogin
      @rmtsrvname= '[연결된 서버별칭]',
      @locallogin = NULL

SQL Server 모든 테이블 크기를 조회하는 쿼리

테이블의 건수와, 테이블에 구성된 인덱스들의 합도 같이 확인할 수 있습니다.

SELECT
    OBJECT_SCHEMA_NAME(a2.object_id) AS SchemaName,
    a2.name AS TableName,
    a1.rows as [RowCount],
    CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
    CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
    CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
    CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB
FROM
    (SELECT 
        ps.object_id,
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (CASE
                WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
        SUM (ps.used_page_count) AS used
    FROM sys.dm_db_partition_stats ps
    GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN 
    (SELECT 
        it.parent_id,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
     WHERE it.internal_type IN (202,204)
     GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY ReservedSize_MB DESC

[MSSQL] DB 복구모델 - 전체(Full) 로 변경

복구 모델을 보려면

1. 데이터베이스 엔진에 연결합니다.
2. 표준 도구 모음에서 새 쿼리를 선택합니다.
3. 다음 예제를 복사하여 쿼리 창에 붙여넣고 실행을 선택합니다. 이 예에서는 모델 데이터베이스의 복구 모델을 배우기 위해 sys.databases 카탈로그 뷰를 쿼리하는 방법을 보여줍니다.

복구 모델을 변경하려면

1. 데이터베이스 엔진에 연결합니다.
2. 표준 도구 모음에서 새 쿼리를 선택합니다.
3. 다음 예제를 복사하여 쿼리 창에 붙여넣고 실행을 선택합니다. 이 예에서는 model ALTER DATABASE FULL 문의 SET RECOVERY 옵션을 사용하여 데이터베이스의 복구 모델을 로 변경하는 방법을 보여 줍니다.

-- 복구 모델을 보려면
SELECT name, recovery_model_desc  
   FROM sys.databases  
      WHERE name = 'model' ;  
 


-- 복구 모델을 변경하려면
USE [master] ;  
ALTER DATABASE [model] SET RECOVERY FULL ;

SSMS에서 하기. 

DB 속성에서 옵션 들어가서 복구모델 선택하기. 

단순(Simple) 데이터베이스 복구 모델을 선택하는 몇 가지 이유는 다음과 같습니다.

• 개발 및 테스트 데이터베이스에 가장 적합합니다.
• 데이터 손실이 허용되는 단순한 보고 또는 애플리케이션 데이터베이스
• 장애 시점 복구는 전체 및 개별 백업 전용입니다.
• 관리 오버헤드 없음

다음을 지원합니다.

1. 전체 백업
2. 차등 백업
3. 복사 전용 백업
4. 파일 백업
5. 부분 백업

장점 : 고성능 대량 복사 작업이 가능하고 로그 공간을 확보하여 공간 요청을 작게 유지합니다.

단점 : 가장 최근의 데이터베이스 또는 불일치 백업을 다시 빌드해야 하므로 변경됩니다.

Full 

전체 복구 모델을 사용하면 SQL Server는 사용자가 백업할 때까지 트랜잭션 로그를 예약합니다. 이 복구 모델에서는 모든 거래(DDL(데이터 정의 언어) + DML(데이터 조작 언어))가 트랜잭션 로그 파일에 완전히 기록됩니다. 로그 순서는 손상되지 않고 데이터베이스가 작업을 복원할 수 있도록 보존됩니다. 단순 복구 모델과 달리 트랜잭션 로그 파일은 CHECKPOINT 작업 중에 자동으로 잘리지 않습니다.

데이터베이스 오류가 발생했을 때 전체 복구 모델을 사용하여 데이터베이스를 복원하는 가장 유연성을 얻을 수 있습니다. 지정 시간 복원, 페이지 복원 및 파일 복원을 포함한 모든 복원 작업이 지원됩니다.

전체 데이터베이스 복구 모델을 선택하는 이유:

• 미션 크리티컬 애플리케이션 지원
• 고가용성 키 설계
• 0 또는 명목 데이터 손실로 모든 데이터 복구를 용이하게 하기 위해
• 데이터베이스가 여러 파일 그룹을 갖도록 설계되었으며 읽기/쓰기 보조 파일 그룹 및 선택적으로 읽기 전용 파일 그룹의 부분적 복원을 수행하려는 경우
• 임의 시점 복원 허용
• 개별 시트 복원
• 높은 관리 오버헤드 유지

다음을 지원합니다.

1. 전체 백업
2. 차등 백업
3. 트랜잭션 로그 백업
4. 복사 전용 백업
5. 파일 및/또는 파일 그룹 백업
6. 부분 백업

장점 : 데이터 파일의 유실 또는 손상으로 인한 작업 오작동이 없습니다. 임의의 시점으로 회복될 수 있습니다.

단점 : 로그가 손상되면 가장 최근의 로그 백업 이후의 변경 사항을 다시 작성해야 합니다.

데이터베이스가 개발 또는 테스트 서버인 경우 단순 복구 모델이 대부분 적합해야 합니다. 그러나 데이터베이스가 프로덕션 데이터베이스인 경우 일반적으로 전체 복구 모델을 사용하는 것이 좋습니다. 전체 복구 모델은 대량 로그 복구 모델로 보완할 수 있습니다. 물론 데이터베이스가 작거나 데이터 웨어하우스의 일부이거나 데이터베이스가 읽기 전용인 경우에도 마찬가지입니다.

[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) 을 방지할 수 있다.