▼ 샘플코드

--테이블변수
DECLARE @temp_REPORT_SCD_0415_PORTMATCH_HIERARCHY_COLLECT_SAVE Table
            (CenterCode CHAR(6), 
	     ModelID int, 
	     TotalCount int,
	     EffectedRows int,
	     StartTime DateTime,
	     EndTime DateTime
);

--임시테이블 (주로사용)
CREATE TABLE dbo.#TmpTable (
           [UID] IDENTITY(1,1),
           ItemName VARCHAR(150),
           InsertDate DateTime,
           UpdateDate DateTime
);

▼ 상세설명

[MSSQL] 임시 테이블 vs 테이블 변수 2016.01

SQL Management Studio 쿼리창에서

쿼리편집기에서 자꾸 한글키로 바뀌어서 짜증날때.. -_-^ 

정보출처: https://chachahoya.tistory.com/72

긴가민가하며 따라하니.. 되네?

홀.. 버그였나? @_@a

2020.11.19 ADD,

종종 다시 증상이 나타나서 계속 다시 설정해야한다 T_T

-- 테이블 주석 추가/수정/삭제
EXEC sp_addextendedproperty 'MS_Description', '테이블주석', 'SCHEMA', dbo, 'TABLE', '테이블명';
EXEC sp_updateextendedproperty 'MS_Description', '테이블주석', 'SCHEMA', dbo, 'TABLE', '테이블명';
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', dbo, 'TABLE', '테이블명'

-- 컬럼 주석 추가/수정/삭제
EXEC sp_addextendedproperty 'MS_Description', '컬럼주석', 'SCHEMA', dbo, 'TABLE', '테이블명', 'COLUMN','컬럼명';
EXEC sp_updateextendedproperty'MS_Description', '컬럼주석', 'SCHEMA', dbo, 'TABLE','테이블명', 'COLUMN','컬럼명';
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', dbo, 'TABLE', '테이블명', 'COLUMN','컬럼명';

https://docs.microsoft.com/ko-kr/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

-- 1) 테이블 생성 및 데이터 복사
SELECT * INTO 생성할_테이블명
FROM 원본_테이블명
WHERE 복사할_데이터_조건 
--└ 응용: 1=2 조건으로 실행하면 테이블 스키마만 복사된 새 테이블이 생성된다.

-- 2) 새 테이블을 이미 생성한 경우 데이터만 복사
INSERT INTO 생성할_테이블명
SELECT *
FROM 원본_테이블명
WHERE 복사할_데이터_조건 

SELECT실행시 아래 에러 발생.

equal to 작업에서의 "Korean_Wansung_CI_AS"과(와) "Korean_Wansung_CS_AS" 간의 데이터 정렬 충돌을 해결할 수 없습니다.

원인:) 비교하는 두 컬럼의 데이터 정렬값이 달라서 발생.

해결1:) 비교컬럼에 강제캐스팅해서 비교 COLLATE

  SELECT A.UsrID, A.UsrName, B.UsrGroupCd
    FROM dbo.USR_USER A, dbo.USR_GROUP B    
  WHERE A.UsrIDCOLLATE Korean_Wansung_CI_AS 
                      = B.userid  COLLATE Korean_Wansung_CI_AS

해결2:) 데이터 정렬값을 일치 시킨다. ALTER

-- DB별 디폴트Collation조회
SELECT NAME, COLLATION_NAME, *
  FROM sys.databases

-- 컬럼의 Collation 설정값 조회
SELECT * 
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_NAME = 'TABLE_NAME'

-- 컬럼의 Collation설정값 변경
ALTER TABLE FORMS_USERGROUP_RANGE_TBL
ALTER COLUMN userid varchar(30)
COLLATE Korean_Wansung_CS_AS NOT NULL

Korean_Wansung_CS_AS vs Korean_Wansung_CI_AS

Korean_Wansung_CS_AS(대소문자 구분O): 양쪽 비교항에 UPPER() 사용하면 구분안함.
Korean_Wansung_CI_AS(대소문자 구분X)

▼TRY..CATCH 

출처&상세: TRY...CATCH(Transact-SQL)

-- TRY..CATCH 사용예
BEGIN TRY  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
        -- 프로시저 파라미터 사용 가능
 	DECLARE @ParamInfoStr varchar(1000)
	= '@ResultDate='+CASE WHEN @ResultDate IS NULL THEN 'null' ELSE CONVERT(varchar(19), @ResultDate, 120) END
		+', @ModelID='++CASE WHEN @ModelID IS NULL THEN 'null' ELSE CAST(@ModelID as varchar) END

	-- error retrieval .  
	SELECT  
		 ERROR_NUMBER() AS ErrorNumber  
		,ERROR_SEVERITY() AS ErrorSeverity  
		,ERROR_STATE() AS ErrorState  
		,ERROR_PROCEDURE() AS ErrorProcedure  
		,ERROR_LINE() AS ErrorLine  
		,ERROR_MESSAGE() AS ErrorMessage; 

	--출력
	PRINT
	   'ERROR_NUMBER = ' + CAST(ERROR_NUMBER() AS VARCHAR)
	 +'ERROR_SEVERITY = ' + CAST(ERROR_SEVERITY() AS VARCHAR)
	 +'ERROR_STATE = ' + CAST(ERROR_STATE() AS VARCHAR)
	 +'ERROR_PROCEDURE = ' + ERROR_PROCEDURE()
	 +'ERROR_LINE = ' + CAST(ERROR_LINE() AS VARCHAR)
	 +'ERROR_MESSAGE = ' + ERROR_MESSAGE();
END

예를 들어 스키마를 변경하려는 테이블을 사용하는 모든 스크립트-영향도-를 검색해야할 경우 사용한다.

-- SQL_EXP 스크립트 검색 (VIEW/FUNCTION/PROCEDURE/TRIGGER)
-- 유의사항: 현재 선택된 DB 만 검색
SELECT O.name, O.type_desc
FROM sys.sql_modules M
	JOIN sys.objects O ON M.object_id = O.object_id
	LEFT OUTER JOIN sys.all_objects P ON O.parent_object_id = P.object_id
WHERE 1=1
  AND M.definition LIKE '%_DEL_%'                       -- 검색결과 이상함!
  AND M.definition LIKE '%[_]DEL[_]%'		-- 밑줄(_) 검색방법1: []로 감싸기
  AND M.definition LIKE '%|_DEL|_%' ESCAPE '|'	-- 밑줄(_) 검색방법2: 이스케이프 문자 정의해서 사용
ORDER BY O.name

--2) 메타데이터 테이블 사용 (4000byte이상은 잘리므로 미사용.)
SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%[_]DEL[_]%'

참고: http://sqldbpros.com/2013/01/escaping-from-an-underscore-in-a-sql-server-wildcard-like-search/

ALTER PROCEDURE dbo.INSERT_ACTION_HISTORY
--CREATE PROCEDURE dbo.INSERT_ACTION_HISTORY
        @ResultDate datetime,
	@LOGID bigint,
	@ActionCode int,
	@Description varchar(500) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		INSERT INTO TBL_SAMPLE (LOGID, ActionCode, Description, UpdateDate) 
		VALUES (@LOGID, @ActionCode, @Description, GETDATE());
	END TRY
    BEGIN CATCH
        -- 파라미터 정보도 저장 가능 (스트링 만들때 오류 안나도록 반드시 테스트 후에 적용할 것)
        DECLARE @ParamInfo VARCHAR(1000) = '';
	SET @ParamInfo = 
		  '입력파라미터: '
		+ '@ResultDate=[' + ISNULL(CONVERT(VARCHAR(19), @ResultDate, 120), 'null')
		+ '], @LOGID=['+CASE WHEN @LOGID IS NULL THEN 'null' ELSE CAST(@LOGID AS VARCHAR) END
		+ '], @ActionCode=['+CASE WHEN @ActionCode IS NULL THEN 'null' ELSE CAST(@ActionCode AS VARCHAR) END
		+ '], @Description=['+CASE WHEN @Description IS NULL THEN 'null' ELSE SUBSTRING(@Description, 0, 100) END
		+ ']';
		PRINT @ParamInfo;

	-- 이 부분에서 DB에러 이력 테이블에 저장하거나 출력
      SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
      IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
   END CATCH	
   
   BEGIN
	IF @@TRANCOUNT > 0   
		COMMIT TRANSACTION;
   END

IDENTITY 컬럼이 있는 테이블에 임의의 데이터 INSERT를 위해

IDENTITY_INSERT ON 을 해야하면 오류발생.

'테이블 'TABLE_NAME'에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다.'

IDENTITY_INSERT가 OFF로 설정되면 테이블 'TBL2'의 ID 열에 명시적 값을 삽입할 수 없습니다.

SET IDENTITY_INSERT ORG2 ON
INSERT INTO TBL2 SELECT * FROM TBL2; -- ◀동일 오류발생
INSERT INTO TBL2(id, name) SELECT id, name FROM TBL2;
SET IDENTITY_INSERT ORG2 OFF

--일부 컬럼을 분리해서 JOIN - CROSS APPLY
SELECT A.ModelID, E.VALUE AS EquipmentID
  FROM  dbo.MODEL_INFO A WITH(NOLOCK)
	    CROSS APPLY dbo.SP_SPLIT(A.EquipmentID, ',') E
            LEFT OUTER JOIN MODEL_HISTORY B WITH(NOLOCK) ON  A.ModelID = B.ModelID AND B.Apply = 1
WHERE B.ModelID IS NOT NULL

-- SP_SPLIT(str,seperator) 는 구분자로 분리해서 테이블을 반환하는 함수
CREATE FUNCTION dbo.SP_SPLIT
(
    @StrValue VARCHAR(6000),   -- 분리할 문자열
    @SplitChar VARCHAR(1)         -- 구분할 문자
) 
RETURNS @SPLIT_TEMP TABLE  ( VALUE VARCHAR(1000) )
AS 
BEGIN   
    DECLARE @oPos INT, @nPos INT
    DECLARE @TmpVar VARCHAR(1000) -- 분리된 문자열 임시 저장변수

    SET @oPos = 1 -- 구분문자 검색을 시작할 위치
    SET @nPos = 1 -- 구분문자 위치

    WHILE (@nPos > 0)
    BEGIN 
        SET @nPos = CHARINDEX(@SplitChar, @StrValue, @oPos ) 

        IF @nPos = 0 
            SET @TmpVar = RIGHT(@StrValue, LEN(@StrValue)-@oPos+1 )
        ELSE
            SET @TmpVar = SUBSTRING(@StrValue, @oPos, @nPos-@oPos)

        IF LEN(@TmpVar)>0
            INSERT INTO @SPLIT_TEMP VALUES( @TmpVar )

        SET @oPos = @nPos +1 
    END 
END

Microsoft SQL Server 2008 - Express버전은 무료. 단 DB용량이 10G이하

▼ S/W 다운로드

Microsoft SQL Server 2008 R2 RTM - Express with Management Tools

(Window Live Login 필요)

정보출처: http://devx.tistory.com/entry/최신-SQL-Server-2008-R2-Express-version

▼ Database Backup/Restore

1. backup 파일 만들기
BACKUP DATABASE DBNAME TO disk='c:\YSWL_BOOKING.bak' with init;

-- 2. DB 변경하여 restore 하기
RESTORE DATABASE DBNAME FROM DISK='c:\YSWL_BOOKING.bak'
WITH NORECOVERY,
MOVE 'DBNAME' TO 'C:\Program Files\Microsoft SQL\..\MSSQL\DATA\DBFILE.mdf',

MOVE 'DBNAME_log' TO 'C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\DBFILE_log.ldf';

출처: http://nimba.tistory.com/328

▼ 테이블 수정

1) 컬럼 추가

ALTER TABLE TableName ADD [ColumnName] [datatype] IDENTITY(1,1) NOT NULL;

→ IDENTITY(1,1)는 시퀀스 추가를 원할때 넣을 것, 1부터 1씩 증가라는 의미.

▽NOT NULL + 제약조건 추가

ALTER TABLE TBL_NAME ADD LastUpdateDate DATETIME NOT NULL CONSTRAINT DF_TBLNAME_LastUpdateDate DEFAULT GETDATE() WITH VALUES ;

2) 컬럼 수정

ALTER TABLE TableName ALTER COLUMN [ColumnName] datetime NOT NULL;

예:) ALTER TABLE EquipFileList ALTER COLUMN FileList  nvarchar(250) NOT NULL;

3) 컬럼 삭제

ALTER TABLE TableName  DROP COLUMN [ColumnName];

4) 컬럼명 변경

SP_RENAME 'TableName.ColumnName', 'New_ColumnName',  'COLUMN';

▼ PK/FK 추가/삭제

ALTER TABLE TableName  ADD CONSTRAINT PK명 PRIMARY KEY (컬럼명); 

ALTER TABLE TableName ADD CONSTRAINT FK명 FOREIGN KEY (컬럼명) REFERENCES 테이블명(컬럼명); -- FK추가

ALTER TABLE TableName  DROP CONSTRAINT PK명; -- PK/FK 삭제

▼ 제약조건 추가

--디폴트값 추가

ALTER TABLE TableName  ADD CONSTRAINT DF_xxx DEFAULT 기본값 FOR 컬럼명 ;

▼ 인덱스

CREATE UNIQUE INDEX UX_인덱스명 ON 테이블명 (컬럼1 ASC, 컬럼2 DESC)

WHERE 컬럼2 = 1 --with조건

DROP INDEX UX_인덱스명 ON 테이블명;

▼ MS-SQL의 데이터 정렬(인코딩) 지정 (일본어)

: SQL Server 데이터 정렬 옵션 (URL)

CI
대소 문자를 구별하지 않는다. 전각 알파벳 대소 문자도 구별하지
  예 : "A"와 "a"를 동일시
CS
대소 문자를 구별한다. 전각 알파벳 대문자, 소문자도 구분
예 : "A"와 "a"를 구별하는
AI
악센트, 탁음, 대중 음악을 구별하지 않는다.
반각 카나의 탁음, 대중 음악도 구별하지
예 : "하", "바", "파"를 구분하지 않는
AS
악센트, 탁음, 대중 음악을 구별한다.
예 : "하", "바", "파"를 구별하는
KS
히라가나와 가타가나를 구분한다. 반각로 구분
KS 대해 KI는 지정하지
예 : "은"을 "이"구분
WS
반자 구분한다. 즉 전각, 반각 구별한다.
WS 대해 WI는 지정하지
예 : "a"와 "a"를 구별하는
BIN
이진 비교한다. 즉 모든 구별한다. .

지정 서식
형식 : Japanese_BIN
형식 : Japanese_ (CI | CS) _ (AI | AS) (_KS | _WS | _KS_WS)
예 : Japanese_BIN
예 : Japanese_CI_AS_KS

▼ 컬럼 데이터 정렬(COLLATE) 지정 방법(http://blog.daum.net/z-dream/17280549)

1)DB별 기본값 확인

SELECT name, collation_name FROM sys.databases;

2)컬럼별 정렬값 확인

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 

 FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_CATALOG = 'DB명'

  AND TABLE_NAME IN ('테이블명')

3)컬럼 정렬값 변경

ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 VARCHAR(10) COLLATE 데이터정렬방식 NOT NULL;

ALTER/CREATE DATABASE DB명 COLLATE 데이터정렬방식;

▼ 관련 에러코드 1757

의미) 열 '테이블.컬럼명'의 데이터 정렬이 외래 키 ‘FK__XXX..’에 있는 참조 열 ‘테이블.컬럼명’의 데이터 정렬과 다릅니다.

▼ 데이터 정렬 정보 확인하기

SELECT name, collation_name FROM sys.databases WHERE {DB명};

▼ DateTime ↔ String 변환 [FORMAT 정보] [MSSQL 날짜 변환(GETDATE, CONVERT)]

SELECT CONVERT(Target형,원본컬럼,스트링포맷)

[DateTime to String] SELECT CONVERT(varchar,getdate(),120);

-- 120: yyyy-mm-dd hh:mm:ss(24h)

-- 112: yyyymmdd

-- 111: yyyy/mm/dd

-- 108: hh:mm:ss

[String to DateTime] SELECT CONVERT(datetime,'2013-06-28 17:28:00',120);

SELECT DATEADD(day, -1, GETDATE()), GETDATE() -- 하루전

-- 2017-12-19 10:42:50.780 2017-12-20 10:42:50.780

SELECT DATEADD(hour, -1, GETDATE()), GETDATE() -- 1시간전

--week, year, month, minute, second, millisecond

-- 초/밀리초 제거한 하루전

select GETDATE(), CAST(CONVERT(VARCHAR(16), GETDATE()-1, 120) as DateTime) 

▼ 쿼리가 너무 길어서 보기 힘들때 서브쿼리 메모리에 임시보관하기

WITH VIEW1(임시뷰이름) AS (
  서브쿼리문
)
SELECT * FROM VIEW1; -- 메인쿼리에서 사용

▼ DB프로시저의 시작 코드

--  테이블에 영향을 준 행 수 메시지 출력하지 않도록 한다(∵서버 부하 적게)
-- "(1개 행이 영향을 받음)"와 같은 메시지
SET NOCOUNT ON;  
-- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 행을 읽을 수 있도록 설정한다.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  

▼ 월의 첫날,마지막날짜값 조회

DECLARE @FirstDay DATETIME = CONVERT(DATETIME, '2018-02-01',121); -- 1일
DECLARE @LastDay DATETIME = DATEADD(DD,-1,DATEADD(MM,1,@FirstDay)); -- 말일

SELECT 1 AS StartDay, DATEPART(DD,@LastDay) AS EndDay;

▼ (20.11.19) 페이징 조회 OFFSET (SQL Server 2012버전 이상 지원)

DECLARE @viewCnt int = 50; -- 한 페이지 당 보여줄 ROW 갯수
DECLARE @currentPageNum int = 1; -- 조회할 페이지 번호(1~N)

--▼AS-IS
SELECT ROW_NUMBER() OVER(ORDER BY 컬럼1,컬럼2) as rnum, XXX
   FROM 테이블
WHERE a.rnum BETWEEN ((@currentPageNum-1) * @viewCnt) + 1 AND (@currentPageNum * @viewCnt)
ORDER BY rnum;

--▼TO-BE
SELECT 컬럼1,컬럼2, XXX
   FROM 테이블
ORDER BY 컬럼1,컬럼2
OFFSET (@currentPageNum-1)*@viewCnt ROWS FETCH NEXT @viewCnt ROW ONLY

▼ 연결된 서버 생성

-- 연결된 서버 삭제
EXEC sp_dropserver @server = '연결서버별명';

-- 생성&로그인 정보 등록
EXEC sp_addlinkedserver
      @server = '연결서버별명',
      @srvproduct = '',
      @provider = 'SQLOLEDB',
      @datasrc = 'ip',
      @catalog = 'database명';

EXEC sp_addlinkedsrvlogin
      @rmtsrvname= '연결된DB별명',
      @useself= 'false',
      @rmtuser = 'ID',
      @rmtpassword = '패스워드';

-- 결과 확인
SELECT * FROM master.dbo.sysservers;
SELECT * FROM master.sys.linked_logins WHERE remote_name = 'id'