MSSQL6 변수 테이블의 통계 개선 - 1 1. 사전구성 SELECT ROW_NUMBER() OVER (ORDER BY a.number) seq , NEWID() main_code INTO TB_Main01 FROM master..spt_values a , (SELECT number FROM master..spt_values WHERE type = 'P' AND number < 10) b WHERE a.type = 'P' AND a.number BETWEEN 1 AND 1000; SELECT c.sub_code , ASCII(SUBSTRING(sub_code, 1, 1)) * c.number value INTO TB_Sub01 FROM ( SELECT b.number, CONVERT(NVARCHAR(36), main_code) sub_code F.. 2022. 12. 8. EXCEPT 구문 성능 이슈 1. 사전구성 SELECT number custid , NEWID() code , CASE WHEN number 2022. 12. 7. 스칼라 서브 쿼리 성능 이슈 1. 사전구성 SELECT rownum AS seq , NEWID() AS sub_code , DATEADD(ss, rownum, '2020-01-01 00:00:00.000') AS date INTO TB_Sub05 FROM (SELECT ROW_NUMBER() OVER (ORDER BY a.number) AS rownum , a.number FROM master..spt_values a JOIN master..spt_values b ON a.type = 'P' AND b.type = 'P' AND a.number 2022. 12. 1. SPOOL 연산자 성능 개선 - 3 1. 사전구성 SELECT a.number id , NEWID() txt , DATEADD(dd, a.number-1, '2020-01-01') date INTO TB_FSpool04 FROM master..spt_values a WHERE a.type = 'P' AND a.number BETWEEN 1 AND 100 ; CREATE CLUSTERED INDEX CIDX_FSpool04 ON TB_FSpool04 (date); SELECT ROW_NUMBER () OVER (ORDER BY a.number) id , CHAR(LEFT(CONVERT(NVARCHAR(4), high), 1) +64) code , RIGHT(number * high * low, 2) value INTO TB_SSpool04 .. 2022. 11. 30. CORSS APPLY 부분 범위 처리 1. 사전 구성 SELECT number id , CASE WHEN number 2022. 11. 14. [mssql] cheet sheat 컬럼으로 테이블 찾기 특정 컬럼을 갖는 테이블 찾기 컬럼이름으로 테이블 찾기 SELECT T.name AS table_name, C.name AS column_name FROM sys.tables AS T INNER JOIN sys.columns AS C ON T.object_id = C.object_id WHERE C.name = '컬럼명' SQL Server Lock check 락 걸린 디비 서버 정보 확인 DB 디비 교착상태, 디비 락, DB lock, db lock SELECT DB_NAME(tl.resource_database_id) as 'DB Name', tl.resource_type, tl.resource_associated_entity_id, tl.request_mode, tl.reque.. 2021. 10. 1. 이전 1 다음
