반응형 MS-SQL70 [미완] 페이징 쿼리 TOP 세번 사용하기 Select * from ( Select Top (@TOP) * from ( Select Top (@nowPage * @PageSize) * from Table1 order by a desc, date desc ) A Order by aasc, date asc ) B Order By adesc, date DESC 2015. 7. 28. 문자열로 구성된 고유 시퀸스 코드 만들 때 SET @SEQ1 = '' SET @SEQ2 = '' SELECT @SEQ1 = 'W' + CONVERT(CHAR(6), GETDATE(), 112) SELECT @SEQ2= ISNULL(MAX(SUBSTRING(WRITERNUM, 8, 11)), '0000') FROM DBO.Table1 WHERE WRITERNUM like 'W' + @SEQ1 + '%' SELECT @SEQ2 = @SEQ2 + 1 SET @WRITERNUM = @SEQ1 + @SEQ2 W2012060001로 구성되어 뒤에 숫자만 증가 시켜야 할 때, 시퀸스에 해당되는 문자열 만 잘라내어서. 문자열 + 1(숫자)로 하면 자동으로 1을 증가시킨다. 2015. 7. 28. [오류] 운영 체제 오류 5(액세스가 거부되었습니다.) 운영 체제 오류 5(액세스가 거부되었습니다.) 발생시 해당 MDF파일의 사용자 권한을 로그인한 사용자로 준다. 아래는 검색해서 나온 것. http://blog.naver.com/neosdh?Redirect=Log&logNo=40158574219 2015. 7. 28. MDF가 손상되었을 경우 ▧ MDF 파일이 손상이 되어(깨졌을 경우) Database 가 정상적으로 작동하지 않을때의 방법 EX)) MDF 파일이 손상이 되어 Attach 작업이 되지 않을 경우 1. 먼저 문제가 된 MDF 파일을 다른 경로로 백업을 해놓는다 그런다음 문제가 된 Database 를 새로 생성한다. 이때 MDF 파일을 문제가 된 MDF 파일의 사이즈와 같게하여 생성한다. 이 경우 LDF 파일은 1MB 로 설정을 한다. 2. SQL SERVICE 종료하고 문제가 된 MDF파일을 새로 생성한 MDF에 덮어씌운다 3. SQL SERVICE 재시작 4. -- 이 경우 해당 DB가 응급모드 상태로 올라올 것이다. --시스템 테이블 값을 업데이트 할 수 있도록 해당 작업을 수행한다 EXEC sp_configure 'allow.. 2015. 7. 28. 내장함수 -- 숫자 함수 -- 값 , 근사치 select ABS(-1); select ABS(8-9); select CEILING(-9.5), CEILING(9.2); --매개변수와 가까운 큰 정수 출력 select FLOOR(-9.5), FLOOR(9.2); -- 매개변수와 가까운 작은 정수 출력 select POWER(2,3); -- 2에 3승을 출력 select RAND() * 1000; -- 0 과 1 사이의 난수 발생 select ROUND(RAND() * 100,0); select ROUND(3.141592, 1),round(3.141592,2),round(3.141592, 3); --(실수 , 소수점반올림자리수) select ROUND(173.141592,0),round(173.141592,-1),r.. 2015. 7. 28. 세로를 가로로 출력하기 SELECT AA.ACCTUNIT_CD, AA.ACCT_CTG_CD , ISNULL(MAX(Case ROWNUMBER When 1 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_1, ISNULL(MAX(Case ROWNUMBER When 2 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_2, ISNULL(MAX(Case ROWNUMBER When 3 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_3, ISNULL(MAX(Case ROWNUMBER When 4 Then AA.MNG_ITEM_CD end),'') MNG_ITEM_CD_4, ISNULL(MAX(Case ROWNUMBER When 5 Then AA.MNG_ITEM_.. 2015. 7. 28. 누계 샘플 WITH CTE (PAY_DT, DEPO_CLS_CD,PAY_YM ,DETL_CTG_CD, PAY_CHR_CD,PAY_AMT ,SUMM) AS( SELECT A.PAY_DT AS PAY_DT ,dbo. FN_FA_GET_CODE_NM('6016' ,MAX( B.DEPO_CLS_CD )) AS DEPO_CLS_CD , MAX (LEFT(B. PAY_YM,4 )) AS PAY_YM , dbo.FN_FA_GET_DETL_CTG_NM (A. ACCTUNIT_CD,MAX (B. ACCT_CTG_CD),MAX (B. DETL_CTG_CD)) AS DETL_CTG_CD , dbo .FN_FA_GET_ORG_NM( MAX(B .PAY_CHR_CD)) AS PAY_CHR_CD , SUM (B. PAY_AMT) AS PA.. 2015. 7. 28. JOIN 걸어 UPDATE UPDATE Table1 SET a = B.a , b = @b , c = @c , d = @d FROM Table1 A JOIN ( SELECT PSNL_NO , DDC_PAYM_YM , DEPT_CD , PAYM_TARG_AMT FROM Table1 WHERE DDC_PAYM_YM = @DDC_PAYM_DT ) B ON A.a = B.a AND A.b = B.b AND A.c = B.c WHERE A.a = @DDC_PAYM_DT AND A.b =@b 2015. 7. 28. 테이블 코멘트 조회 쿼리 SELECT * FROM INFORMATION_SCHEMA.COLUMNS T1 LEFT OUTER JOIN ( SELECT T.NAME TABLE_NAME, TD.VALUE TABLE_DESC , C.NAME COLUMN_NAME, CD.VALUE COLUMN_DESC FROM SYSOBJECTS T INNER JOIN SYSUSERS U ON U.UID = T.UID LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES TD ON TD.MAJOR_ID = T.ID AND TD.MINOR_ID = 0 AND TD.NAME = 'MS_Description' INNER JOIN SYSCOLUMNS C ON C.ID = T.ID LEFT OUTER JOIN SYS.EXTENDED_PROPER.. 2015. 7. 27. 일괄 insert시 자동 순번 순번의 마지막 번호를 구한 다음에 ROW_NUMBER()를 사용하여 insert를 한다. --순번 따기 SELECT @SEQ = MAX(SEQ)FROM DBO.TB_INSERT IF @SEQ IS NULL SET @SEQ = 0 INSERT TB_INSERT ( seq , a , b , c ) SELECT ROW_NUMBER()OVER (ORDER BY a ASC ) + @SEQ , a , b , c FROM TB_TARGET 2015. 7. 27. [MS 발췌] SQL Server for Developer: 개발자를 위한 튜닝 가이드 SQL Server for Developer: 개발자를 위한 튜닝 가이드 본 튜닝 가이드는 경험 있는 SQL관리자와 초보자 모두에게 도움이 되도록 기초부터 고급까지 튜닝에 꼭 필요한 내용을 최대한 쉬운 예제를 통해 기술했습니다. 예제는 쉽더라도 그 내용은 무척 중요하므로 꼭 따라 하면서 본 튜닝 가이드를 실습하여 봅시다. 쿼리 디자인 수칙1. SELECT는 필요한 결과값만을 요구하는가? 수칙2. 적절한 WHERE조건을 사용하는가? 수칙3. COUNT(컬럼명) 대신 COUNT(*)을 사용하는가? 수칙4. 커서 및 임시 테이블의 내용을 최대한 자제하는가? 수칙5. VIEW의 총 사용을 줄였는가? 수칙6. 저장 프로시저를 사용하는가? 수칙7. 저장 프로시저를 적절하게 리컴 파일 하며 사용하는가 ? 수칙8. 작.. 2015. 7. 27. Merge Using(데이터 인서트 할 때 테이블에 있는지 비교) 단일 업데이트및 수정MERGE INTO dbo.TBTable AS A USING ( SELECT @a AS a ,@b AS b ,@c AS c ,@d AS d ) B ON A.a = B.a WHEN MATCHED AND @SaveFlag = 'D' THEN DELETE WHEN MATCHED AND @SaveFlag = 'U' THEN UPDATE SET a = @b , a = @b WHEN NOT MATCHED THEN INSERT (a, c, b, d) VALUES (@a, @c, @b, @d) ; 일괄 업데이트및 수정 MERGE INTO dbo.TABLE T USING( SELECT aa ,ss ,dd ,gg FROM TABLE ) S ON T.gg = S.gg WHEN MATCHED THEN UP.. 2015. 7. 27. PIVOT SELECT NATION , [M] MAN , [W] WAMEN , [X] X , [M] + [W] + [X] TOTAL FROM ( SELECT NATION , GENDER_TYPE FROM DBO.Table1 ) P PIVOT ( COUNT(GENDER_TYPE) FOR GENDER_TYPE IN ([M], [W], [X]) ) PIVOTTABLE ORDER BY NATION NATION MAN WAMEN X TOTAL korea 0 6 3 9 japna 1 2 3 6 http://msdn.microsoft.com/ko-kr/library/ms177410(v=sql.105).aspx ----------------------------------------------------------------- .. 2015. 7. 27. [설정] 배경 검은색 저장 파일 sql2008용 CurrentSettings-2013-05-20 sql2012용 Exported-2013-06-06 2015. 7. 27. 구분값으로 구분된 키값을 테이블로 변환 *2016부터는 펑션을 만들어서 사용할 필요가 없음. 아래와 같이 하면 됨.2016부터 추가된 함수SELECT * FROM String_Split('56940840542,56940150020' ,',') ALTER FUNCTION [dbo].[FN_Split] ( @Separator CHAR(1), @List VARCHAR(1000) ) RETURNS @RTN_TABLE TABLE(Item VARCHAR(30)) AS BEGIN DECLARE @IDX INT; DECLARE @vNewText VARCHAR(4000); IF @List = null RETURN; SET @IDX = CHARINDEX(@Separator, @List); WHILE NOT(@IDX = 0) BEGIN SET @vNewText .. 2015. 7. 27. 숫자로 구성된 문자열 시퀸스에 특정 자리를 특정 문자로 채우기(REPLICATE 함수를 사용해서 특정 문자로 채움) REPLICATE 함수를 사용해서 특정 문자로 채웁니다. * 특정 문자 : '0' * 대상 문자 : '4321' * 자리수 : 7자리 * 출력 결과 : '0004321' (좌측으로 붙이기), '4321000' (우측으로 붙이기) 좌측으로 붙이기 SELECT REPLICATE(특정 문자, 자리수 - LEN(대상 문자)) + 대상문자 SELECT REPLICATE('0', 7 - LEN('4321')) + '4321' 우측으로 붙이기 SELECT 대상문자 + REPLICATE(특정 문자, 자리수 - LEN(대상 문자)) SELECT '4321' + REPLICATE('0', 7 - LEN('4321')) 2015. 7. 27. sp적용시 기존 sp 삭제하고 생성 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.UP_SPNAME) AND type in (N'P', N'PC')) DROP PROCEDURE dbo.UP_SPNAME GO 2015. 7. 27. 마이그레이션 관련 쿼리 예 DateTime이 아닌 String으로 들어가 있는 데이터를 DateTime로 변환 할 때 예)날짜시간(20120720153559)->DateTime(2013-10-01 17:03:20.987) update TBL_PLACE set INPUT_DATE = convert(datetime, substring(INPUT_DT, 1, 4) + '-' + substring(INPUT_DT, 5, 2) + '-' + substring(INPUT_DT, 7, 2) + ' ' + substring(INPUT_DT, 9, 2) + ':' + substring(INPUT_DT, 11, 2) + ':' + substring(INPUT_DT, 13, 2)) , CHANGE_DATE = convert(datetime, subs.. 2015. 7. 27. 채번 리딩제로 -- 연 단위 MAX COUNT + 1 = 시퀀스 SELECT @SeqOfYear = COUNT(*) + 1 FROM BMS.dbo.TB_BW_WorkMgmt WHERE SUBSTRING(WorkNo,1,4) = YEAR(GETDATE()) -- 리딩제로 적용(세자리) SELECT @SeqLeadingZero = RIGHT('000'+ CONVERT(VARCHAR,@SeqOfYear),3) SELECT @WorkNo = CONVERT(char(8),GETDATE(),112) + @SeqLeadingZero -- WorkNo = 등록일자 + 리딩제로 적용된 일년 치 카운트 + 1 (ex 20130101+001) SELECT @WorkNo --채번 DECLARE @seq CHAR(4) , @FileID CH.. 2015. 7. 27. [프로파일러] 내 것만 조회해서 보기 Evfents Selection 탭에서 Column Filter 버튼 클릭 후 ClientProcessID에 Equal 속성에 자신의 ID를 준다. ID는 작업 관리자에서 W3WP를의 ID를 주면 된다. 2015. 7. 27. [설정] Table 변경 수정시 에러 메시지 테이블에 컬럼및 속성등이 추가되거나 변경될 대 발생하는 에러 대처법. SQL Server Management Studio (SSMS) 옵션 ->디자이너 -> 테이블 다시 생성 해야 하는 변경 내용을 저장 안 함 옵션을 비활성화 2015. 7. 27. [SELECT] 함수등을 이용한 데이터 조회 SELECT --시작일부터 지금까지 경과일 구하기, DATEDIFF(DD, HIRE_DT, GETDATE()) WORKING_DAY --금액(숫자)데이터에 콤마찍기, REPLACE(CONVERT(VARCHAR(50), CAST(20000 AS MONEY), 1) , '.00', '') --문자인지 숫자인지 판단해야될 때SELECT ISNUMERIC('sdfs') --문자인지, 숫자인지 판단 문자는 0SELECT ISNUMERIC('1111') --문자인지, 숫자인지 판단 숫자는 1을 반환한다. 2015. 7. 27. WHERE 밑으로 쓰는 조건 모음 WHERE 1=1 --CASE WHEN 조건 AND (a.JobGrpCode = CASE WHEN @IsSearchType= '1' THEN '99' ELSE a.JobGrpCode END ) AND (@BelongCode = '' OR CASE WHEN @IsSearchType = '1' THEN b.CoCode ELSE a.DeptCode END = @BelongCode AND (a.JobGrpCode = CASE WHEN @IsSearchType= '1' THEN '99' ELSE a.JobGrpCode END ) --게시판등의 검색 타입 등에 따른 검색이 달라지는 경우 OR검색일 경우 앞이 참이면 뒤는 검색 안함. AND (@Keyword ='' OR @LikeSearchType IN (1, 2,.. 2015. 7. 27. 열로 구성된 값은 한 컬럼으로 받을 때 1 2 3 형태로 된 데이터를 한열, 한행 으로 조회시 -> 1,2,3 DECLARE @TEST NVARCHAR(2000) SELECT @TEST = CONCAT(CD , ',' , @TEST) FROM TEST SET @TEST = LEFT(@TEST, LEN(@TEST) - 1) SELECT @TEST 2015. 7. 27. 저장프로시저 목록 조회 SELECT A.name , A.id , A.crdate , B.[text] FROM SYSOBJECTS A JOIN SYSCOMMENTS B ON A.ID = B.ID WHERE XTYPE = 'P' 2015. 7. 27. [Tool] 설정 Ctrl + R 등 단추키가 안먹힐 경우 Ctrl + R 등 단추키가 안먹힐 경우 ->도구->옵션->환경->키보드->다시설정 버튼 2015. 7. 27. MS-SQL 단축키 F5 : 쿼리 실행 CTRL + F5 : 문법 검사 CTRL + SHIFT + DEL : 모두 지우기 CTRL + T : 실행결과 텍스트 형태로 보기 CTRL + D : 실행결과 표 형태로 보기 CTRL + R : 결과를 없애고 질의만 볼 수 있다. CTRL+SHIFT+F2 : 책갈피: 모든 책갈피 지우기 CTRL+F2 : 책갈피: 책갈피를 삽입하거나 제거(토글) F2 : 책갈피: 다음 책갈피로 이동 SHIFT+F2 : 책갈피: 이전 책갈피로 이동 ALT+BREAK : 쿼리 취소 CTRL+O : 연결: 연결 CTRL+F4 : 연결: 연결 끊기 CTRL+F4 : 연결: 연결 끊기 및 하위 창 닫기 ALT+F1 : 데이터베이스 개체 정보 CTRL+SHIFT+DEL : 편집: 활성 편집기 창 지우기 CTRL+.. 2015. 7. 27. 페이지 쿼리 DECLARE @PageNumber AS INT , @RowsPage AS INT SET @PageNumber = 3 SET @RowsPage = 5 SELECT * FROM TABLE_NAME ORDER BY COLUMN_Name ASC OFFSET((@PageNumber - 1) * @RowsPage) ROWS FETCH NEXT @RowsPage ROWS ONLY 관련 URL http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx 2015. 7. 27. DB 용량 줄이기 ALTER DATABASE table_name SET RECOVERY SIMPLE DBCC SHRINKFILE(table_name, 10) tempdb는 줄이려면 SQL 서비스 재시작 tempdb 줄이는 방법 이하 참조. http://dbadiaries.com/how-to-shrink-tempdb There may come a time when you might want to shrink tempdb because it has become too large. There are a few ways you can do this and I have listed them below but please read to the end of the post before making a decision on which .. 2015. 7. 27. Migration Tip insert into 로 마이그레이션시 SELECT에 의해서 tempdb에 임시로 데이터를 저장한다. 그래서 용량은 많이 차지 한다. 하지만 select into를 사용하면 바로 집어넣기 때문에 별로의 temp공간이 필요하지 않는다. 입력시간도 많이 차이나면 2배까지 차이간다. 하지만, 스키마까지는 들어가지 못한다. 2015. 7. 27. 이전 1 2 3 다음 반응형
