====== MS SqlServer basic ======
* [[:mssqlserver|MS SQL Server]] basic
===== Create Database with collation / Unicode =====
* [[https://docs.microsoft.com/ko-kr/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-2017|데이터베이스 데이터 정렬 설정 또는 변경]]
* [[https://docs.microsoft.com/ko-kr/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017|데이터 정렬 및 유니코드 지원]]
CREATE DATABASE MyOptionsTest
COLLATE Korean_Wansung_CI_AS;
GO
USE master;
GO
ALTER DATABASE MyOptionsTest
COLLATE Korean_Wansung_CI_AS;
GO
====== Table/Databases ======
exec sp_databases
go
===== Table 목록 보기 =====
* [[https://www.tsql.info/system-stored-procedures/sp_tables.php|Sp_tables example - Transact SQL language - System Stored Procedures]]
exec sp_tables
go
GO
EXEC sp_tables
@table_name = '%',
@table_owner = 'dbo',
@table_qualifier = 'model';
GO
-- 원하는 테이블 검색
select name
from DBname.sys.tables
where name like '%xxx%'
and is_ms_shipped = 0; -- << 불필요하면 제거
===== Table Description =====
-- 테이블 정보 보기
EXEC sp_help tablename
go
select COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = 'tablename';
go
===== EXISTS =====
* 데이터의 존재 여부 검사. ''count(*)''를 사용하면 조회 조건을 만족하는 모든 것들 훑어야 하지만, ''exists''를 사용하면 첫번째 데이터에서 바로 반환한다.
-- 조건을 만족하는 첫번째 것에서 바로 중단하고 1 혹은 0 반환
select count(1) where exists (select * from books where book_type='IT')
* [[https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017|EXISTS(Transact-SQL) | Microsoft Docs]]
* [[https://www.w3schools.com/sql/sql_exists.asp|SQL EXISTS Operator]]
===== UPSERT =====
* [[https://dba.stackexchange.com/questions/89696/how-to-insert-or-update-using-single-query|sql server - How to insert or update using single query? - Database Administrators Stack Exchange]]
-- 이 방법은 SQLServer 2008 이후로는 사용하지 말라고 함.
update test set name='john' where id=3012
IF @@ROWCOUNT=0
insert into test(name) values('john');
* [[http://purumae.tistory.com/118|MERGE 를 이용한 UPSERT]] SQLServer 2008 이후 추천 방법
-- TEST 가 UPDATE/INSERT 대상 테이블일 때
MERGE dbo.TEST AS T
USING (SELECT 3, 300) AS S (userSN, col1) -- update or insert 할 데이터 테이블. SELECT로 가상 테이블 만듬
ON T.userSN = S.userSN
WHEN MATCHED THEN
UPDATE SET col1 = S.col1
WHEN NOT MATCHED BY TARGET THEN
INSERT (userSN, col1) VALUES (S.userSN, S.col1);
===== Offset/Limit Paging =====
* [[https://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/|SQL SERVER - MySQL - LIMIT and OFFSET - Skip and Return Only Next Few Rows - Paging Solution - SQL Authority with Pinal Dave]] SQLServer 2012 부터 추가된 Offset/limit
* [[https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017|ORDER BY Clause (Transact-SQL) | Microsoft Docs]]
* ''OFFSET'' 은 **''0''** 부터 시작한다.
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
GO
===== DATE, TIME 컬럼 합쳐서 DATETIME 만들기 =====
* ''DATE''와 ''TIME'' 컬럼이 따로 존재할 경우 이 둘을 합쳐서 ''DATETIME''을 만들려면, [[https://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server|How to combine date from one field with time from another field - MS SQL Server]]
-- modifyDate : DATE, modifyTime : TIME 컬럼일 경우
CAST(modifyDate as DATETIME) + CAST(modifyTime as DATETIME) modifiedAt
===== 공백제거 =====
* [[https://stackoverflow.com/questions/951518/replace-a-newline-in-tsql|Replace a newline in TSQL - Stack Overflow]]
* ''CR'', ''LF'',, ''CRLF'' 모두 제거해야 한다.
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
===== FORMAT =====
* [[https://docs.microsoft.com/ko-kr/sql/t-sql/functions/format-transact-sql?view=sql-server-2017|FORMAT(Transact-SQL) | Microsoft Docs]]
* 시간 포맷시 ''.'' 혹은 '':'' 을 사용하려면 ''\.'' ''\:'' 형태로 Escape하지 않으면 null 리턴된다.
* 시간을 나타내는 포맷 문자열은 ''hh''이다. *대문자 아님*.
SELECT FORMAT(cast('07:35' as time), N'hh\.mm'); --> returns 07.35
SELECT FORMAT(cast('07:35' as time), N'hh\:mm'); --> returns 07:35
===== String to DATETIME =====
* 정형화 돼 있는 날짜형식 문자열을 DATETIME 타입으로 변경하는 것은 ''CAST''를 사용한다.
* [[https://docs.microsoft.com/ko-kr/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15|CAST 및 CONVERT]]
* 그렇지 못한 custom type 은 문자열을 모두 쪼개서 [[https://docs.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver15|DATETIMEFROMPARTS]]/[[https://docs.microsoft.com/en-us/sql/t-sql/functions/datetime2fromparts-transact-sql?view=sql-server-ver15|DATETIME2FROMPARTS]]를 사용한다.
* [[https://rextester.com/LTBUB91960|임의의 문자열을 DATETIME, DATETIME2 로 변경하기, Sql Server - rextester]]
-- 2019-09-20 18:51:47
DECLARE @d NVARCHAR(14)='20170920185147';
SELECT
DATETIMEFROMPARTS (LEFT(@d, 4), SUBSTRING(@d, 5,2), SUBSTRING(@d,7 ,2), SUBSTRING(@d, 9, 2) , SUBSTRING(@d, 11, 2), RIGHT(@d, 2), 0) as "with_var",
DATETIMEFROMPARTS (LEFT('20170920185147', 4), SUBSTRING('20170920185147', 5,2), SUBSTRING('20170920185147',7 ,2), SUBSTRING('20170920185147', 9, 2) , SUBSTRING('20170920185147', 11, 2), RIGHT('20170920185147', 2), 0) as "with_literal",
DATETIME2FROMPARTS (LEFT('20170920185147', 4), SUBSTRING('20170920185147', 5,2), SUBSTRING('20170920185147',7 ,2), SUBSTRING('20170920185147', 9, 2) , SUBSTRING('20170920185147', 11, 2), RIGHT('20170920185147', 2), 0, 0) as "dt2"
;