Yeonu's eins, zwei, drei

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 -- ===== [CPU] ===== -- CPU 점유율 높은 쿼리 확인 ;WITH cte AS (      SELECT   stat.[sql_handle]           , stat.statement_start_offset           , stat.statement_end_offset           , COUNT( * ) AS NumExecutionPlans           , SUM(stat.execution_count) AS TotalExecutions           , ((SUM(stat.total_logical_reads)  *   1. 0 )  /  SUM(stat.execution_count)) AS AvgLogicalReads           , ((SUM(stat.total_worker_time)  *   1. 0 )  / ...

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 -- ===== [MEMORY] ===== -- SQL MAX memory 확인 EXEC sp_configure  'max server memory' ;   -- SQL MAX memory 설정 --DECLARE @maxMem INT = 3000 -- MAX. MB 단위로 입력 --EXEC sp_configure 'show advanced options', 1 --RECONFIGURE   --EXEC sp_configure 'max server memory', @maxMem; --RECONFIGURE   -- SQL MIN memory 확인 EXEC sp_configure  'min server memory' ;   -- SQL MIN memory 설정 --DECLARE @minMem INT = 3000 -- MAX. MB 단위로 입력 --EXEC sp_configure 'show advanced options', 1 --RECONFIGURE   --EXEC sp_configure 'min server memory', @minMem; --RECONFIGURE   -- O...

유저에 대한 권한이 부여가 안된 리스트 출력 Version: MS-SQL 2017 1 2 3 4 5 6 7 8 9 10 DECLARE @GranteeName NVARCHAR( 50 )  =  N 'username'         ,@PermissionType  CHAR ( 2 )  =   ''         ,@ObjectType       CHAR ( 2 )  =   '' SELECT  SCHEMA_NAME(o.schema_id)  +   '.'   +  o.name AS ObjectName  FROM  sys.objects AS o LEFT JOIN sys.database_permissions AS dp  ON  o.object_id  =  dp.major_id AND dp.type  =  @PermissionType LEFT JOIN  sys.database_principals AS dpr  ON  dp.grantee_principal_id  =  dpr.principal_id     AND dpr.name  =  @GranteeName WHERE  o.type  =  @ObjectType     AND dpr.name IS  NULL

SELECT DISTINCT Type FROM sys.objects; type char(2) Object type: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF = SQL inline table-valued function IT = Internal table P = SQL Stored Procedure PC = Assembly (CLR) stored-procedure PG = Plan guide PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) RF = Replication-filter-procedure S = System base table SN = Synonym SO = Sequence object U = Table (user-defined) V = View EC = Edge constraint Applies to: SQL Server 2012 (11.x) and later. SQ = Service queue TA = Assembly (CLR) DML trigger TF = SQL table-valued-function TR = SQL DML trigger TT = Table type UQ = UNIQUE constraint X = Extended stored procedure Applies to: SQL Server 2014 (12.x) and later, Azure SQL Database, Azure Synapse Analytics, Analytics Platform System (PDW). ST = STATS_TREE Applies to: S...

 버전: SQL Server 2017 SQL Server에서 연결된 Mysql 링크드 서버에서 가끔 Openquery를 사용해서 데이터를 조회한다. '(quotation) 표기도 헷갈리고 Where절에 파라미터를 넘기는 것도 복잡해서 찾아보니 Openquery 자체를 view로 만들수 있었다. 1 2 3 4 5 6 7 8 -- View 만들기 Create  View ViewName AS SELECT   *   FROM  OPENQUERY( LinkedserverName,  'SELECT * FROM MysqlTable'  ) Query   -- View 실행 DECLARE @id  INT   =   1   SELECT   *   FROM  ViewName  WHERE  id  =  @id; 참고링크

SQL Server version: SQL Server 2017 standards 증상: PC 교체 후 드라이브 명 변경된 뒤 SQL Server 서비스 실행 안됨 이벤트 로그 예시:  2010-03-27 14:21:34.58 spid52 FCB::Open 하지 못했습니다: 파일이 c:\Program 서식 SQL Server\MSSQL10을 열 수 없습니다. 파일 번호 1에 대 한 SQL2008\MSSQL\data\MyDB_Prm.mdf입니다. OS 오류: 5 (액세스가 거부 되었습니다.). 액세스 거부는 권한 문제일 가능성이 높다. 서비스 실행을 위해 필요한 파일들이 있는 폴더에 권한을 넣어줬다. 권한 설정 참조 권한확인 및 관련 에러 확인 파일에 일일이 주기 귀찮아서 폴더에 권한 주고 상속함 서비스 재실행 해보니 정상 작동!! 이렇게 또 해결!

SQL ver: SQL Server 2017 standard 보통은 테이블에 PK를 두지만 세상 일이란게 꼭 그렇지 않고, 또 누군가의 업무를 받았을 때 해당 DB 파악을 위해 확인하는 것이 좋다. 내 경우는 INDEX는 서비스 중에 거는 것을 선호하지만 PK는 반드시 걸려고 한다. SELECT  t.TABLE_SCHEMA, t.TABLE_NAME, tc.CONSTRAINT_NAME FROM  INFORMATION_SCHEMA. TABLES  AS t LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc  ON  t.TABLE_SCHEMA  =  tc.CONSTRAINT_SCHEMA AND t.TABLE_NAME  =  tc.TABLE_NAME AND tc.CONSTRAINT_TYPE  =   'PRIMARY KEY'  AND tc.CONSTRAINT_SCHEMA  < >   'sys' ORDER   BY  t.TABLE_SCHEMA, t.TABLE_NAME 간단하게 Information_schema 정보들을 활용해서 리스트를 확인했다. tc.COONSTRAINT_NAME의 값이 NULL이라면 해당 테이블에 PRIMARY KEY가 없는 것이므로 설정해주는 것이 좋다.

SQL ver: SQL Server 2017 Standard 스키마 관리를 위해 프로시저의 파라미터 리스트를 확인하고 싶은 경우 수행한다. SELECT  s.name AS schema_name, pp.name AS procedure_name,  p.name AS parameter_name, p.parameter_id AS parmeter_order, p.is_nullable, t.name AS type, p.max_length FROM  sys.procedures AS pp  INNER JOIN sys.schemas AS s  ON  pp.schema_id  =  s.schema_id LEFT JOIN sys.parameters AS p  ON  pp.object_id  =  p.object_id LEFT JOIN sys.types AS t  ON  p.system_type_id  =  t.system_type_id AND p.user_type_id  =  t.user_type_id parameter_name이 Null인경우 해당 프로시저는 파라미터를 갖지 않는것이다. 아이디어 출처 출처는 위와 같으며 내가 활용하기 편하게 쿼리를 변경했다.

MSSQL 버전: SQL 2017 float 데이터 타입을 nvarchar로 변경하는데 문제가 발생했다. 다음 예제를 입력 했을때 결과는 예상한대로 102184235가 나온다. DECLARE @float_ex float  =   102184235 SELECT  @float_ex 그러나 아래와 같이 문자형으로 해당 데이터를 변경하면 SELECT  convert(nvarchar, @float_ex) 결과는  1.02184e+008 를 출력한다. 해결방법 다음과 같이 float 타입을 int로 변환 후 nvarchar로 변환하면 원하는 결과를 얻을 수 있다. SELECT  convert(nvarchar,convert( int , @float_ex)) 결과는 102184235 이 출력된다.

업데이트 문 사용 시 1행만 업데이트 해야 하는 경우가 있다. 그런데 조건에 맞는 Row가 여러 개라면 참 어렵다. UPDATE  Top ( 1 ) tablename SET  x  =  y WHERE  z  =   'condition' 간단하게 MS SQL에서는 UPDATE  다음에 TOP (1) 을 써넣으면 가능하다. 단점이라면 어떤 행이 업데이트 될 지 알 수 없다. 추측하자면 조건에 맞는 것 중 가장 먼저 만나는 행인것 같다. 인덱스를 사용한 테이블이라면 인덱스 정렬 순서에 따라 달라질 것 같다. 오늘도 이렇게 새로운 팁을 하나 얻었다.

정말 사소한 팁이지만 아침부터 거의 2시간 동안 에러를 찾느라고 고생했다. 다시 고생하고 싶지 않기에 이렇게 기록으로 남긴다. 상황: 엑셀 VBA를 활용하여 엑셀 데이터를 DB로 넣는데 연결을 못한다.  명령창에서 포트 리스닝을 확인했더니 기본 포트인 1433이 아닌 1434를 리스닝 하고 있었다. 혹시 서비스가 올라오지 않은 것인가 확인 했더니 아니었다. 기본포트도 바꿔보고 서비스 재시작도 했지만 되지 않았다. 해결 방법: 해결방법 링크 원인은 SQL Server Configuration Manager의 SQL Server Network Configuration 의 Protocol for MSSQLSERVER  TCP/IP 의 status 가 Disabled상태였기 때문이다. 변경 전 상태 변경 전 1433 기본 포트를 조회 했으나 조회 되지 않는다. 변경 후 변경 후 서비스 재시작을 하면 다음과 같이 포트를 리스닝하고 있음을 확인 할 수 있다. 작지만 사실 발생하지 않으면 모르고 지나가는 게 너무 많다. 오늘도 하나 배웠다.

다른 언어도 마찬가지 겠지만 수정을 하면 영향 받는 범위를 확인해야 한다. 처음부터 설계를 잘하면 되지... 만 말처럼 쉽지 않다.  그래서 프로시저나 테이블을 수정할 때 참조하는 정보를 확인한다. 그런데 지금까지 내가 사용하던 것은 프로시저 내에서만 찾는 쿼리를 사용했다. 잘 사용하고 있었는데 이건 프로시저한정적이라서 이번에 함수내용도 확인할 수 있는 쿼리를 조사해봤다. 참고링크 SELECT  o.name as FuncName, sm.definition as Def, O.object_id FROM  sys.objects as o INNER JOIN sys.sql_modules as sm      ON  o.object_id  =  sm.object_id WHERE  o.type IN ( 'FN' ,  'IF' ,  'TF' )  sys.sql_modules에서 함수의 내용을 가지고 있어서 그 부분을 활용한 것이다. where 절에 type은 FN = SQL 스칼라 함수, IF = SQL 인라인 테이블 반환 함수, TF = SQL 테이블 반환 함수를 의미한다. 그러면 특정 개체를 참조하는 지는 어떻게 바꿀 수 있을까 SELECT  o.name as FuncName FROM  sys.objects as o INNER JOIN sys.sql_modules as sm      ON  o.object_id  =  sm.object_id WHERE  o.type I...

OS: Windows 8.1(x64) SQL: Microsoft SQL Server 2017(RTM) - 14.0.1000.169 Visual  Studio: Microsoft SQL Server Data Tools for Visual Studio 2017 TFS로 소스배포를 하고 있는데 컬럼의 옵션의 Null -> Not Null로 변경하는데 문제가 생겼다. 보통 TFS를 사용하지 않았다면 바로 Alter table $table ALter column $column INT NOT NULL ; 로 수정했을 테지만 TFS를 사용하여 배포를 하며 에러가 발생했다. 에러메시지에 아래와 같은 옵션 수정에 대한 메시지가 나왔음 you must add values to this column for all or mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option. or 이후의 문장에 중점을 두고 구글링을 찾아 봤다. 관련링크 근데 해당 옵션 창이 어디서 나타나는지 모르겠다. 이렇게 또 영어의 벽에 부딪힘 결론은 publish 버튼을 클릭하면 뜨는 Publish database 창에 Advanced... 버튼을 클릭하면 아래와 같이 해당 화면이 나온다. 30분 정도 몰라서 계속 구글링 했었다. 그래서 Generate smart defaults, when applicable 에 체크해주면 정상적으로 처리 된다. 정상적으로 처리된 내용의 쿼리를 봤더니 update  $table set  $column  =   0 WHERE  $coulumn IS  NULL ; Alter   table  $table  ALter  column $column...

가끔 해당 테이블을 쓰고 있는지 궁금할 때가 있다. 예를 들면 서비스를 접고자 할 때, 해당 DB로 데이터가 들어오는지 궁금할 때 이용했다. 그럴때 유용한 쿼리문이다. SELECT  o.name as ObjectName, MAX(last_user_update) FROM  sys.dm_db_index_usage_stats s join dbname.sys.objects o  on  s.object_id  =  o.object_id WHERE  database_id  =  DB_ID(  'dbname' ) GROUP  BY  o.name 단순히 해당 last_user_seek라는 컬럼도 있음. 내 경우 데이터가 insert되는지가 궁금해서 last_user_update 컬럼을 조회했다. 가끔 사용하는 쿼리 이므로 참고용으로 보관한다.

SQL Server에서 인덱스를 설정하려고 할 때 혹은 설정하고 있을 때 block 상태를 확인 하면서 걸어줘야 한다. 인덱스 생성 전, 중간, 후에 block 상태를 보는 쿼리는 아래와 같다. SELECT   * FROM  sys.sysprocesses WHERE  blocked  >   0 간단하게 사용하기 좋은 쿼리이다.  참고용으로 좋다. sys.sysprocesses 내용 참고 링크

SQL Server 장애 발생 시 원인 파악을 위해 wait_type을 확인 합니다. 관련 리스트를 정리해 둔 사이트를 링크해 둡니다. https://www.sqlshack.com/sql-server-wait-types/ 장애가 발생한 시점의 wait_type 중 가장 높은 부분을 파악하여 원인을 찾아 봅니다. 관련해서 내용을 계속 정리해 갈 예정입니다.

DB에서 테이블을 생성했을 때 컬럼네임을 틀리는 경우가 종종 있다. 꼼꼼하게 본다고 해도 간혹 틀리는 경우가 있는데 바로 변경해보자. GameName으로 해야 하는데 GaemName으로 해버렸다... 무의식적으로 GameName으로 하다가 낭패를 볼 것 같아서 바로 수정했다. 초반에는 이렇게 수정하는 것이 쉬울 수 있으나 나중에 연관된 프로시저, 만약 기본키나 외래키가 설정되어 있다면 모두 수정해야 할 수 있다. 결론은 처음부터 꼼꼼히 확인하고, 초반에 발견되었다면 바로 수정을 하자. USE  DBNAME GO EXEC sp_rename   '스키마.테이블이름.기존컬럼이름' ,  '새로운컬럼이름' ,  'COLUMN' 코드는 다음과 같다. 실행하면 성공 시 다음과 같은 주의 메시지가 나온다. Caution: Changing any part of an object name could break scripts and stored procedures. 그렇다. 혹시 연관된 object가 있으면 반드시 수정하자. 작은 실수가 여러분의 소중한 시간을 빼앗아 갈 수 있다!!! 출저는 마이크로소프트 사이트

SQL 서버 작업을 하면서 많이 하는 작업 중의 하나인 DATE Convert 작업 입니다. 국내 블로거들은 주로 YYYY MM DD에 많이 정리 되어있지만 외국에서는 월을 문자로도 표현하는 경우가 많아 해당 내용에 대해서도 알고 있으면 좋을 것 같아서 공유 합니다. 참고해서 활용하시기 바랍니다. 아래 바로가기를 눌러서 확인합니다. SQL DATE 변환 정리

누군가의 쿼리 실수로 테이블이나 데이터 베이스가 지워졌을 경우.. 이런일이 발생하면 안되지만 어떻게 될 일일지 모르니... 언제 어떻게 실행이 되었는지를 알 수 있는 쿼리가 있다. USE  dbname GO    SELECT  t.[ text ], s.last_execution_time FROM  sys.dm_exec_cached_plans AS p INNER JOIN sys.dm_exec_query_stats AS s     ON  p.plan_handle  =  s.plan_handle CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t WHERE  t.[ text ]  LIKE  N '%something unique about your query%' ORDER   BY  s.last_execution_time DESC; something unique about your query 부분에 검색하고자 하는 내용을 넣어서 실행 시키면 해당 쿼리가 실행 되었는지 그리고 언제 실행되었는지 등을 알 수 있다. 제발 이 쿼리를 실행하는 일이 없도록 항상 신중하고 또 신중하게 실행하자 아래 쿼리 출처는 다음과 같다. 출처:  스택오버플로우

SQL 서버에서 랭킹 함수를 써야 하는 경우가 있다. RANK, DENSE_RANK, ROWNUMBER, NTILE 등이 순위 함수로 되어있는데 각각의 차이점을 정리해본다. SQL SERVER 2008부터 사용가능한 함수들이다. 사용법:   함수명() OVER ( [PARTITION 컬럼] ORDER BY 컬럼 혹은 식)  ROWNUMBER의 경우 중복을 허용하지 않는다. RANK와 DENSE_RANK의 경우 중복이 허용된다. 다만 둘의 랭킹 표기 방식이 조금 다르다.  RANK의 경우 중복이 되면 그 다음 바로 수가 아니라 중복된 수 만큼 처리된 후 다음 순위가 나온다. 예를 들면 1,1,3,4 1이 두번 중복되므로 2순위가 없고 3순위가 나오는 것이다.  DENSE_RANK의 경우 중복이 되면 중복된 수와 관계없이 +1을 한 값이 나온다. 예를 들면 1,1,2,3,4 와 같이 1이 중복 되었어도 다음 순위는 RANK와 달리 2가 나오는 것이다. NTILE의 경우는 NTILE(n)과 같이 행들을 n개씩 묶음으로 보이도록 한다. 비슷한 함수들이지만 특성이 다르므로 잘 사용하면 좋을 것 같다. 아래 링크를 참조하자!!! SQL SERVER 순위함수