[MSSQL] SQL 합집합, 교칩합, 차집합 구하기


* SELECT되는 컬럼의 갯수는 일치해야함


SELECT [Column1],[Column2],[Column3],[Column4],[Column5]

FROM [Table1]

WHERE 조건


-- UNION -- 중복을 삭제한 합집합

-- UNION ALL -- 중복을 포함한 합집합

-- EXCEPT -- 차집합

-- INTERSECT -- 교집합


SELECT [Column1],[Column2],[Column3],[Column4],[Column5]

FROM [Table2]

WHERE 조건

[MSSQL] 프로시저(stored procedure)내 문자열 검색, 문자열 찾기

How to find specific text in stored procedure



프로시저내에서 특정 문구를 검색 할 경우 사용


RIGHT WAY)


--1) 

SELECT DISTINCT a.name

FROM sysobjects AS a

LEFT JOIN syscomments AS b ON a.id = b.id

WHERE a.xtype = 'P'

AND b.text LIKE '%[TEXT]%'


--2)

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) 

FROM sys.procedures 

WHERE OBJECT_DEFINITION(object_id) LIKE '%[TEXT]%'



아래와 같은 방법은 ROUTINE_DEFINITION이 nvarchar(4000)의 한계를 가지고 있어서 제대로 된 검색결과가 나오지 않음


WRONG WAY)


SELECT * FROM INFORMATION_SCHEMA.ROUTINES 

WHERE ROUTINE_TYPE='PROCEDURE' 

AND ROUTINE_DEFINITION LIKE '%[TEXT]%'



[MSSQL] 사용자 정의 테이블 형식 지정 및 사용


--생성 CREATE


프로그래밍 기능 > 유형 > 사용자정의테이블형식 > CREATE


CREATE TYPE [MYTABLETYPE] AS TABLE(

column1 int(10),

column2 varchar(10)

)

GO


-- 삭제 DROP


DROP TYPE [MYTABLETYPE] 

GO


-- 사용 USE


프로시저에서 부를 경우 

CREATE PROCEDURE sp_AAA

@count int,

[@변수명] [사용자정의테이블형식] READONLY //READONLY필수

AS


How to use in procedure 

CREATE PROCEDURE sp_AAA

@count int,

[@param] [userType] READONLY //READONLY is mandatory

AS


ex)

CREATE PROCEDURE sp_AAA

@count int,

@T_테이블 MYTABLETYPE READONLY

AS


--인덱스 생성


CREATE INDEX [INDEX NAME]

 ON [TABLE NAME](COLUMN NAME)


--인덱스 정보 확인

--인덱스명(index_name), 인덱스 설명 (index_description), 인덱스 키(index_keys)


EXEC sp_helpindex


--인덱스 정보조회 (단편화)


DBCC showcontig [TABLE NAME]


--인덱스 통계보기


DBCC show_statistics ([TABLE NAME], [INDEX NAME])


*참고

Updated : 통계가 최종 업데이트 된 날짜

Rows: 총 행수

Rows Sampled : 샘플링된 행 수 (통계 정보를 구하기 위해 샘플링된 행 수로 행 수가 많을 경우 샘플링함)

Steps : Histogram 단계수(MAX 200단계, 고유 인덱스가 아닐 경우 단계수 많아짐)

Average Key Length : 컬럼 값의 평균 길이 

All density : 밀도 (최대 1)

RANGE_HI_KEY : 각 Step에서 샘플링된 최대 값.

RANGE_ROWS : 각 Step에서 RANGE_HI_KEY값을 제외한 값의 개수.

EQ_ROWS : 최대값 개수.

DISTINCT_RANGE_ROWS : RANGE_ROWS 컬럼 값들중 중복되지 않는 값의 개수.


--인덱스 조각 모음 (*인덱스 다시 잡음, 시간걸림)


DBCC dbreindex ([TABLE NAME])


--모든 인덱스 다시 잡기 (*엄청난 시간 걸릴 수 있음. 주의!!!)


DBCC dbreindexall('[DB NAME]')


--인덱스 테이블 통계 업데이트


UPDATE STATISTICS [TABLE NAME]


참고 : https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-helpindex-transact-sql?view=sql-server-2017



[MSSQL] NUMERIC 선언 방법


NUMERIC([최대 자릿수], [소수점 아래 자릿수]) 로 

소수점 아래 자리를 선언하지 않아도 실행에는 상관 없으나 

받은 값에 소수점 아래 자리가 있을 경우 전체가 표기되지 않음


--SELECT Result : 55000

DECLARE @MONEY NUMERIC(10)


--SELECT Result : 55000.5

--DECLARE @MONEY NUMERIC(10,1)


--SELECT Result : 55000.56

--DECLARE @MONEY NUMERIC(10,2)


--SELECT Result : 55000.568

--DECLARE @MONEY NUMERIC(10,3)


SET @MONEY = '55000.568'

SELECT @MONEY


커밋되지 않은 데이터를 읽을 수 있는 수준으로

두가지 방법이 존재함.


실제로 UPDATE되지 않고 ROLLBACK될 경우 

기존데이터와 다른 값을 SELECT하게 됨


즉, 참조 무결성을 깨뜨릴 수 있다는 단점이 있으나

그럴 경우가 극히 드문 경우 자주 사용


-- 1. 해당 SELECT 문장에만 적용


SELECT * FROM [TABLE NAME] WITH (NOLOCK) WHERE ~~~



-- 2. 해당 SESSION에 모두 적용 


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SELECT ~~~

SET ~~~

INSERT~~~




When you checking column format. 


sp_columns [table Name]


SELECT * FROM information_schema.columns WHERE table_name = '[table Name]'



SQL)

sp_columns [table Name]


Result)




SQL)

SELECT * FROM information_schema.columns WHERE table_name = '[table Name]'


Result)


데이터 출력시 탭, 엔터, 개행 제거

replace Tab, Line feed(LF), Carriage return(CR)



 Tab(탭)

 char(9)

 Line feed(LF, 개행)

 char(10)

 Carriage return(CR)

 char(13)

 Enter

 char(10) + char(13)


example)


SELECT REPLACE(REPLACE(REPLACE('text',char (9), ''), char(10), ''), char(13),'')

MSSQL

프로시져 업데이트 기록 보기


프로시져 업데이트 시 최종 적용 날짜가 궁금할 경우


If you want to check procedure update or modify date,


SQL)

SELECT ao.name, modify_date as modify, *

FROM  sys.all_objects ao

WHERE ao.type ='P'

ORDER BY modify_date DESC


Result)


[MSSQL] 두개 이상 필드 값 비교 ORDER BY


두개 이상 필드값을 비교하여 정렬 해야할 경우


SELECT seq, addDt, moddt, * FROM [tableName]



각각 ORDER BY를 하면 첫 필드 부터 정렬 됨

normal order by sorting

SELECT * FROM [tableName] ORDER BY modDt DESC, addDt DESC


Example)



CASE문으로 조건을 주어 두 필드 이상의 값을 합하여 비교 가능

using case with mssql


SELECT * FROM [tableName] ORDER BY (CASE WHEN modDt <> '' Then modDt ELSE addDt END) DESC


Example)

+ Recent posts