'팁&테크/MSSQL'에 해당되는 글 15건
- 2018.09.12 DateTime format 변경
- 2018.07.04 DELETE TOP(LIMIT) ORDER
- 2018.06.28 Update JOIN, DELETE JOIN
- 2018.06.15 현재 날짜의 자정(midnight)
- 2017.10.23 MsSQL 관련 메모
2018. 9. 12. 10:16
DateTime format 변경
2018. 9. 12. 10:16 in 팁&테크/MSSQL
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy HH\:mm\:ss');
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy HH\:mm\:ss');
SELECT FORMAT(CAST('2018-09-12 11:22:33' AS DATETIME2), 'MM/dd/yyyy HH\:mm\:ss');
2018. 7. 4. 16:35
DELETE TOP(LIMIT) ORDER
2018. 7. 4. 16:35 in 팁&테크/MSSQL
정렬 후 몇개만 삭제 시
WITH T AS (SELECT TOP 3 * FROM Table1 ORDER BY id DESC) DELETE FROM T
몇개만 삭제
DELETE TOP(1) FROM Table1
2018. 6. 28. 14:09
Update JOIN, DELETE JOIN
2018. 6. 28. 14:09 in 팁&테크/MSSQL
UPDATE TableA (또는 Alias이름) SET foo = B.bar FROM TableA A INNER JOIN TableB B ON A.col1 = B.colx WHERE ...
DELETE TableA (또는 Alias이름)
FROM TableA A
INNER JOIN TableB B ON A.ID=B.IDWHERE ....
2018. 6. 15. 10:15
현재 날짜의 자정(midnight)
2018. 6. 15. 10:15 in 팁&테크/MSSQL
아래 방식이 가장 성능이 좋음
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);
나머지 DATE 로 cast 해서 처리하는 방식은 성능이 떨어짐.
2017. 10. 23. 10:58
MsSQL 관련 메모
2017. 10. 23. 10:58 in 팁&테크/MSSQL
1. 전체 테이블명
SELECT * FROM information_schema.TABLE_CONSTRAINTS;
2. 전체테이블의 컬럼명
SELECT * FROM information_schema.Columns
3. 테이블별 PK 정보
SELECT * FROM information_schema.KEY_COLUMN_USAGE
4. LAST_INSERT_ID
SELECT SCOPE_IDENTITY() AS last_insert_id;
5. index 목록
SELECT TableName = t.name, IndexName = ind.name, IndexId = ind.index_id, ColumnId = ic.index_column_id, ColumnName = col.name, ind.*, ic.*, col.* FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id ORDER BY ind.name, t.name, ind.index_id, ic.index_column_id
6. ???
SELECT SC.DisplayName, SR.SearchCategory, CoverageType = STUFF(( SELECT ',' + SR1.CoverageType FROM tblSearchReq SR1 WHERE SR.SearchCategory=SR1.SearchCategory AND SR1.CoverageType <> 'Nationwide' GROUP BY SR1.SearchCategory, SR1.CoverageType FOR XML PATH(''), TYPE).value('.', 'VARCHAR(100)'), 1, 1, '' ) FROM SearchCategory SC INNER JOIN tblSearchReq SR ON SC.CategoryName=SR.SearchCategory WHERE SR.CoverageType <> 'Nationwide' GROUP BY SC.DisplayName, SR.SearchCategory ORDER BY SC.DisplayName