[SQL] 왜 (어떻게) master..spt_values를 사용하여 열을 분할하려면?

왜 (어떻게) master..spt_values를 사용하여 열을 분할하려면?

내가 "여러 행으로 분할 한 열"의 질문에 대한 답변 Subquestioning [1] 여기에 다시를 썼다.

유형 = 'P'왜 열 분할에 대한 문서화되지 않은 master..spt_values를 사용하는 방법 (의 의미) 란 무엇입니까? 그것의 이점은 무엇입니까?

[ 1 ]

CREATE TABLE dbo.Table1 
(
    Col1        CHAR(1),
    Col2        CHAR(1),
    Col3        CHAR(1),
    Col4        VARCHAR(50)
)
GO

INSERT INTO dbo.Table1 VALUES ('A','B','C','1,2,3')
GO
INSERT INTO dbo.Table1 VALUES ('D','E','F','6,7,8,9')
GO


SELECT
    T.col1, RIGHT(LEFT(T.col4,Number-1),
    CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
FROM
    master..spt_values,
    table1 T
WHERE
    Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
    (SUBSTRING(T.col4,Number,1) = ','
    -- OR SUBSTRING(T.col4,Number,1)  = '') --this does not work correctly anyway

관련 질문 :

해결법

1. ==============================

   1.왜 문서화되지 않은 master..spt-값을 사용

   왜 문서화되지 않은 master..spt-값을 사용

   사이베이스, 따라서 그 서자 MS SQL은 (서비스로 시작되는 SQLSERVER 같은 바이너리, 반대로) 시스템 프로 시저에 구현되는 제품의 다양한 특징과 기능을 제공합니다. 이 시스템 프로 시저 절차는 SQL 코드로 작성 SP_ % 이름이 지정됩니다. 일부 비밀 내부를 제외하고, 그들은 다른 SQL 코드와 같은 제한과 요구 사항을 가지고있다. 그들은 사이베이스 ASE 또는 SQL Server 제품의 일부입니다. 따라서, 그들은 그것을 문서화 할 필요가 없습니다; 내부 비트 합리적 "문서화"로 표시 될 수 없다.

   master..spt_values는 시스템 절차는 다양한 보고서를 생성, SQL 테이블에 필요 말했다 모든 다양한 비트와 조각이 포함되어 있습니다. SP에서 시스템 절차 수단; SPT 시스템 절차 테이블 수단; 물론 값의 내용이다.

   유형 = 'P'무엇 (의 의미)한다

   사람들은 종종 "드 - 정규화"등의 spt_values을 설명하지만 잘못된 용어입니다. 정확한 용어는 절첩 또는 압축된다. 또한, 각각의 논리 테이블 아름답게 차별화 유형 항목으로, 하나 개의 물리적 테이블 접혀 정규화 26 정도 논리 룩업 테이블이다.

   이제 일반적인 데이터베이스, 즉 심한 오류가 될 것이다 (그냥 "하나의 조회 테이블 또는 여러"에 대한 답변을보고). 그러나 서버 카탈로그에, 그것은 26 개 물리적 테이블을 대체하는 것이 바람직하다.

   spt_values ​​단지 하나의 목적, 그렇지 않으면 접힌 (26)를 포함하는 분리 참조 테이블, 한 프로젝션 테이블에 따라서있다.

   spt_values의 통상의 사용은, 통상의 조회 또는 기준 또는 ENUM 테이블과 같다. 첫째, 조회 값 :

       SELECT *                    -- list Genders
           FROM Gender 
   

   그것은 사람이 확장 될 필요가 GenderCode (매우 확장,이 이상한 일을) 가지고 같은 방법으로 사용된다 :

       SELECT  P.*,                -- list Person
               G.Name              -- expand GenderCode to Name
           FROM Person P
           JOIN Gender G
               ON P.GenderCode = G.GenderCode
   

   예. 는 sp_lock 문자열 이름으로 잠금 유형을 표시하는 활성 잠금의 보고서를 생성합니다. 그러나 master..syslocks는 그 이름을 포함하지 않는, 숫자로 잠금 유형이 포함; 이 그랬다면, 그것은 심하게 denormalised 테이블이 될 것입니다! 쿼리를 실행하면 (사이베이스 ASE 코드, 당신은 변환해야합니다) :

       SELECT *                    -- list LockTypes
           FROM master..spt_values 
           WHERE type = "L"
   

   당신은 조회 테이블에 66 개 집합 LockType 번호와 이름을 알 수 있습니다. 즉는 sp_lock이 사람처럼 간단한 코드를 실행할 :: 성별 위에 수 있습니다 :

       SELECT  spid,               -- list Active Locks
               DB_NAME(dbid),
               OBJECT_NAME(id, dbid),
               v.name,             -- expand lock name
               page,
               row
       FROM master..syslocks   L,
            master..spt_values LT
       WHERE L.type = LT.number    -- 
       AND   type = "L"            -- LockType Lookup table
       ORDER by 1, 2, 3, 4, 5, 6   -- such that perusal is easy
   

   유형 = 'P'(의 의미) 란 무엇입니까?

   무엇 프로젝션이며 어떻게 사용됩니까?

   말은, 예를 들어, 대신 위의 쿼리에 의해 생성 된 활성 잠금, 당신은 활성 잠금 (또는 널)의 수를 보여주는 모든 66 LockTypes의 목록을 원했다. 당신은 커서, 또는 WHILE 루프가 필요하지 않습니다. 우리는 활성 잠금의 수를 통해 집합 LockType 조회 테이블을 로젝트 수 :

       SELECT  LT.name,            -- list LockTypes
               [Count] = (         -- with count
           SELECT COUNT(*)
               FROM master..syslocks
               WHERE type = LT.number
                   )
           FROM master..spt_values LT
           WHERE type = "L"
   

   여러 가지 방법이 있습니다, 그것은 단지 하나입니다. 또 다른 방법은 하위 쿼리 대신 파생 테이블을 사용하는 것입니다. 그러나 당신은 여전히 ​​프로젝션이 필요합니다.

   즉 spt_values를 위해 확장 또는 프로젝션 중 하나를 사용하는 것을 일반적이다. 이제 당신이이 알고, 당신도 사용할 수 있습니다. 이 시스템 절차없이 실행할 수 없습니다 의미 (마스터 데이터베이스) 안전하고 거의 모든 시스템 프로 시저에 의해 사용됩니다.

   열을 분할 하시나요?

   아, 당신은 코드 "여러 행으로 분할 한 CSV 열을"이해가 안 돼요.

   그것의 이점은 무엇입니까?

   나는 고 응답했다 생각합니다. 당신이 그것을 가지고 있지 않은 경우, 번호 목록이 필요한 모든 시스템 절차는 임시 테이블을 만들어야합니다; 그것으로 행을 삽입; 해당 코드를 실행하기 전에. 물론, 이러한 단계를 수행하지 않아도, 훨씬 더 빠른 시스템 프로 시저를 만듭니다.

   이제 때 예를 들면, 프로젝션을 수행해야합니다. 미래, 또는 어떤에서 달력 날짜는 대신 자신의 임시 테이블마다 생성 (또는 개인 소유의 영구 테이블을 생성하고 유지) 할 필요없이, spt_values를 사용할 수 있습니다.

2. ==============================

   2.TSQL에서 분할 문자열에 대한 많은 일반적인 솔루션은 번호 목록이 필요합니다; 이 경우, 누군가를 제공하기 위해 spt_values ​​테이블을 사용하고 있습니다. 검사함으로써,이 쿼리는 2048 연속 정수의 목록을 반환합니다 :

   TSQL에서 분할 문자열에 대한 많은 일반적인 솔루션은 번호 목록이 필요합니다; 이 경우, 누군가를 제공하기 위해 spt_values ​​테이블을 사용하고 있습니다. 검사함으로써,이 쿼리는 2048 연속 정수의 목록을 반환합니다 :

   select number from master..spt_values where type = 'P'
   

   나는 원래 쿼리 작가는 '보장'임) 때문에 정수에 대한 spt_values ​​사용 가능하고, 가정 때문에 쿼리는 항상 작업,와 b)는 정수를 얻을 수있는 다른 방법에 대한 긴 설명을 피할 수 있습니다.

   가장 큰 단점은 테이블이 잠재적으로 혼란도 다소 위험한되어 사용되므로 문서화되어 있다는 점이다 (적어도 원칙적으로 업그레이드 또는 서비스 팩은 테이블 데이터 또는 구조를 변경, 또는 완전히 제거 될 수 있음).

   (I 테이블 반환 함수를 사용) 문서화되지 않은 테이블을 사용하지 않고 숫자의 목록을 얻을 수있는 많은 다른 방법이 있습니다 :

   SQL, 숫자의 보조 테이블

3. ==============================

   3.나는 이것이 이전 게시물입니다 알지만 업데이트를 추가 할 거라고 생각했다. 탈리 테이블 및 cteTally 테이블에 근거하는 스플리터는 모든 주요 문제가있다. 그들은 연결된 구분 기호를 사용하고 요소가 더 넓은 얻고 문자열을 더 이상받을 때 자신의 속도를 죽인다.

   나는 이것이 이전 게시물입니다 알지만 업데이트를 추가 할 거라고 생각했다. 탈리 테이블 및 cteTally 테이블에 근거하는 스플리터는 모든 주요 문제가있다. 그들은 연결된 구분 기호를 사용하고 요소가 더 넓은 얻고 문자열을 더 이상받을 때 자신의 속도를 죽인다.

   나는 그 문제를 해결하고 그 URL을 다음에서 확인할 수 있습니다 그것에 대해 기사를 작성했습니다. http://www.sqlservercentral.com/articles/Tally+Table/72993/

   새로운 방법은 루프, 재귀 CTE 동안 모두 떨어져 문을 불면 및 VARCHAR에 대한 XML 방법 (8000).

   또한 "베드로"의 이름으로 동료 (기사에 대한 토론에) 심지어 코드에 개선을 만든 것을 당신에게 말할 것이다. 이 기사는 여전히 재미 있고 난 다음 하루 이틀 피터의 향상과 함께 첨부 파일을 업데이트 할 수 있습니다. 전공 향상 및 베드로가 만든 형식에 설정을 몇 년 사이에, 나는 당신이 분할 VARCHAR (8000)에 대한 빠른 T-SQL 전용 솔루션을 찾을 수 있습니다 생각하지 않는다. 나는 또한뿐만 아니라, 그에 대한 기사를 작성하는 과정에서이 VARCHAR (MAX)에 대한 스플리터의 유형 및시의 문제를 해결했습니다.

4. ==============================

   4.이제이 작동 잘

   이제이 작동 잘

   SELECT T.col1, RIGHT(LEFT(T.col4,Number-1),CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
   FROM
       master..spt_values,
       table123 T
   WHERE
       Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
       (SUBSTRING(T.col4,Number,1) = ','
     OR SUBSTRING(T.col4,Number,1)  = '')
   

5. ==============================

   5.SQL 서버 2016에서 지금 우리는 우리가 열을 분할하는 데 사용할 수있는 새로운 기능 String_Split 있습니다.

   SQL 서버 2016에서 지금 우리는 우리가 열을 분할하는 데 사용할 수있는 새로운 기능 String_Split 있습니다.

   예를 들어, 다음 스크립트는 다음과 같습니다

   DECLARE @String NVARCHAR(1000) = 'abc,def,ghi,jkl,mno,pqr,stu,vw,xyz';
   SELECT * FROM STRING_SPLIT(@String,',');
   

   위의 스크립트의 실행되면, 그것은 결과 다음과 같은 우리를 반환합니다.

   글쎄, 그건 그것입니다. 여기에 그녀의 성능을 비교했다 Kathi에 의해 자원이다.

from https://stackoverflow.com/questions/4273978/why-and-how-to-split-column-using-master-spt-values by cc-by-sa and MIT license