[SQL] SQL 서버 자동으로 자릅니다의 VARCHAR는 저장 프로 시저의

SQL 서버 자동으로 자릅니다의 VARCHAR는 저장 프로 시저의

이 포럼 토론, SQL 서버에 따라 자동으로 어떤 절단이의 VARCHAR의 길이에 저장 프로 시저의 매개 변수로 지정한으로 VARCHAR (I 2005을 사용하고 있지만이 또한 2000 년과 2008 년에 적용을 수집)도 직접 해당 문자열을 삽입주의를 사용하는 경우 INSERT 실제로 오류가 발생합니다. 예를 들면. 나는이 테이블을 작성하는 경우 :

CREATE TABLE testTable(
    [testStringField] [nvarchar](5) NOT NULL
)

그때 나는 다음을 실행할 때 :

INSERT INTO testTable(testStringField) VALUES(N'string which is too long')

오류가 발생합니다 :

String or binary data would be truncated.
The statement has been terminated.

큰. 데이터 무결성은 유지하고, 호출자는 그것에 대해 알고있다. 이제 그를 삽입하는 저장 프로 시저를 정의 할 수 있습니다 :

CREATE PROCEDURE spTestTableInsert
    @testStringField [nvarchar](5)
AS
    INSERT INTO testTable(testStringField) VALUES(@testStringField)
GO

그것을 실행 :

EXEC spTestTableInsert @testStringField = N'string which is too long'

오류는, 1 행은 영향을받지 않습니다. 행은 'strin'로 testStringField 함께 테이블에 삽입된다. SQL Server는 자동으로 저장 프로 시저의 VARCHAR 매개 변수를 잘립니다.

자,이 동작은 시간에 편리 할 수도 있지만 나는 그것을 해제 할 방법이 수집합니다. 내가 저장 프로 시저에 너무 긴 문자열을 전달하면 나는 오류로 일을 원하는대로 이것은 매우 성가신입니다. 이 처리하는 두 가지 방법이있을 것 같다.

첫째, 사이즈 6으로 저장된 프로 시저의 @testStringField 매개 변수를 선언하고이 해킹의 비트처럼 보인다 상용구 코드의 자극 양을 포함 5 이상 길이가 있는지 확인합니다.

둘째, 단지 VARCHAR (최대)로 모든 저장 프로 시저 VARCHAR 매개 변수를 선언 한 다음 저장 프로 시저 내에서 INSERT 문이 실패 할 수 있습니다.

내 질문은 그래서 후자는, 잘 작동 보인다 : 나는 실제로 원하는 경우 긴 문자열이 전달 될 때 너무 항상 SQL Server 저장 프로 시저에서 문자열을 사용 VARCHAR (최대)에 좋은 아이디어이고, 저장된 시저가 실패 하는가? 심지어 가장 좋은 방법이 될 수 있을까요? 비활성화 할 수 없습니다 침묵 잘라내는 나에게 바보 같다.

해결법

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

   1.그것은 단지입니다.

   그것은 단지입니다.

   내 검사 중 하나가 내 매개 변수 내 테이블 컬럼 길이에 맞게 보장하는 것 때문에 비록 문제를 발견 적이 없다. 클라이언트 코드에서 너무. 개인적으로, 나는 SQL이 너무 깁니다 데이터를 볼 수 없을 것으로 예상 것입니다. 내가 잘릴 데이터를 볼 않은 경우, 그것은 원인을 분명 출혈이 될 것입니다.

   당신은 VARCHAR (최대)에 대한 필요를 느끼지 않으면 때문에 데이터 형식 우선 순위의 엄청난 성능 문제를 조심. (최대) VARCHAR는 VARCHAR (n)은보다 높은 우선 순위 (가장 긴이 가장 높은)이있다. 따라서 이러한 유형의 쿼리에 당신이 추구 스캔하지 모든 VARCHAR를 얻을 것이다 (100) 값은 VARCHAR에 CAST는 (최대)

   UPDATE ...WHERE varchar100column = @varcharmaxvalue
   

   편집하다:

   이 문제에 대한 열린 연결 Microsoft 항목이 있습니다.

   그리고 그것은 ERLAND Sommarkog의 엄격한 설정 (매칭 연결 항목)에 포함 아마도 가치입니다.

   편집 2, 마틴 코멘트 후 :

   DECLARE @sql VARCHAR(MAX), @nsql nVARCHAR(MAX);
   SELECT @sql = 'B', @nsql = 'B'; 
   SELECT 
      LEN(@sql), 
      LEN(@nsql), 
      DATALENGTH(@sql), 
      DATALENGTH(@nsql)
   ;
   
   DECLARE @t table(c varchar(8000));
   INSERT INTO @t values (replicate('A', 7500));
   
   SELECT LEN(c) from @t;
   SELECT 
      LEN(@sql + c), 
      LEN(@nsql + c), 
      DATALENGTH(@sql + c), 
      DATALENGTH(@nsql + c) 
   FROM @t;
   

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

   2.감사합니다, 언제나처럼, 심도있는 토론의이 종류를 도출하기위한 StackOverflow의합니다. 나는 최근에 / catch 블록을 거래 표준 접근 방식을 사용하여 더욱 강력하고 시도하는 내 저장 프로 시저를 수색하고있다. 나는 제즈에 동의 완벽하게 "나의 제안은 응용 프로그램 측이 담당하게 될 것이다"고 조 스테파넬 리에 동의하고, "SQL 서버는 문자열 길이 훨씬 바람직 할 것입니다 확인 갖는". 저장 프로 시저를 사용하는 나를 위해 요점은 데이터베이스에 언어 네이티브로 작성되며 방어의 마지막 줄에 역할을해야한다는 것입니다. 255과 256의 차이 측 응용 프로그램에 불과 meangingless 수 있지만 데이터베이스 환경에서, 255의 최대 크기 필드는 단순히 256 개 문자를 허용하지 않습니다. 응용 프로그램 검증 메커니즘은 그들이 할 수있는 최선와 같은 백엔드 DB를 반영해야하지만 응용 프로그램이 실수로 적합하지 않은 데이터를 허용하는 경우 데이터베이스가 나에게 좋은 피드백을 제공 할 수 있도록 유지 보수가 어렵다. 내가 대신 CSV 또는 JSON이든 텍스트 파일의 무리의 데이터베이스를 사용하고 이유의 그.

   감사합니다, 언제나처럼, 심도있는 토론의이 종류를 도출하기위한 StackOverflow의합니다. 나는 최근에 / catch 블록을 거래 표준 접근 방식을 사용하여 더욱 강력하고 시도하는 내 저장 프로 시저를 수색하고있다. 나는 제즈에 동의 완벽하게 "나의 제안은 응용 프로그램 측이 담당하게 될 것이다"고 조 스테파넬 리에 동의하고, "SQL 서버는 문자열 길이 훨씬 바람직 할 것입니다 확인 갖는". 저장 프로 시저를 사용하는 나를 위해 요점은 데이터베이스에 언어 네이티브로 작성되며 방어의 마지막 줄에 역할을해야한다는 것입니다. 255과 256의 차이 측 응용 프로그램에 불과 meangingless 수 있지만 데이터베이스 환경에서, 255의 최대 크기 필드는 단순히 256 개 문자를 허용하지 않습니다. 응용 프로그램 검증 메커니즘은 그들이 할 수있는 최선와 같은 백엔드 DB를 반영해야하지만 응용 프로그램이 실수로 적합하지 않은 데이터를 허용하는 경우 데이터베이스가 나에게 좋은 피드백을 제공 할 수 있도록 유지 보수가 어렵다. 내가 대신 CSV 또는 JSON이든 텍스트 파일의 무리의 데이터베이스를 사용하고 이유의 그.

   내의 SP 중 하나가 8152 오류가 발생했습니다 다른이 자동으로 잘립니다 이유를 의아해했다. 나는 마지막으로 twigged 다음 8152 오류가 발생했습니다 SP에서 관련 테이블 열보다 한 문자 더 허용 매개 변수를했다. 표 NVARCHAR 컬럼 (255)으로 설정되지만 파라미터 NVARCHAR (256)이었다. 그래서 것없는 내 "실수"주소 GBN의 우려 "엄청난 성능 문제"? 대신에 최대를 사용하는, 아마도 우리는 지속적으로 더 이상, 256이 해결할 수있는 문제 자동 절단 문제를 말한다 (255), 말에 테이블 열 크기와 하나의 문자에 SP 매개 변수를 설정할 수 있습니다 및 성능 저하를 발생하지 않습니다.  아마도 거기에 내가 생각하지 않은 몇 가지 다른 단점은, 그러나 그것은 나에게 좋은 타협을 보인다.

   최신 정보: 나는이 기술이 일치하지 않습니다 두려워 해요. 또한 테스트는 내가 가끔 8152 오류를 트리거 할 수 있습니다 때로는 데이터가 자동으로 잘립니다 것을 알 수있다. 사람이 다루는 더 신뢰할 수있는 방법을 찾을 나를 도울 수 있다면 나는 매우 감사하게 될 것입니다.

   업데이트 2 : 이 페이지에 Pyitoechito의 답변을 참조하시기 바랍니다.

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

   3.같은 동작이 여기에서 볼 수있다 :

   같은 동작이 여기에서 볼 수있다 :

   declare @testStringField [nvarchar](5)
   set @testStringField = N'string which is too long'
   select @testStringField
   

   나의 제안은 저장 프로 시저를 호출하기 전에 입력을 검증하기위한 응용 프로그램 측이 담당하게 될 것이다.

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

   4.문자열의 256 번째 문자가 공백이기 때문에 이것은 아마 발생한다. VARCHAR에 삽입에 공백을 후행 절단 단지 경고를 생성합니다. 그래서 저장 프로 시저가 자동으로 256 개 문자로 문자열을 절단하고, 당신의 삽입 (경고 포함) 후행 공백을 절단한다. 말했다 문자가 공백이 아닌 경우이 오류가 발생합니다.

   문자열의 256 번째 문자가 공백이기 때문에 이것은 아마 발생한다. VARCHAR에 삽입에 공백을 후행 절단 단지 경고를 생성합니다. 그래서 저장 프로 시저가 자동으로 256 개 문자로 문자열을 절단하고, 당신의 삽입 (경고 포함) 후행 공백을 절단한다. 말했다 문자가 공백이 아닌 경우이 오류가 발생합니다.

   아마도 솔루션은 저장 프로 시저 VARCHAR 비 공백 문자를 잡을 수있는 적절한 길이를 확인하는 것입니다. VARCHAR (512) 아마 안전 충분하다.

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

   5.한 가지 해결책은하는 것입니다 :

   한 가지 해결책은하는 것입니다 :

   이 방법은 기존 코드에 대한 수정 사항은 아래의 예제처럼 매우 미미할 것입니다.

   이것은 원래의 코드입니다 :

   create procedure spTest
   (
       @p1 varchar(2),
       @p2 varchar(3)
   )
   

   이 새로운 코드입니다 :

   create procedure spTest
   (
       @p1 varchar(max),
       @p2 varchar(max)
   )
   declare @p1Int varchar(2), @p2Int varchar(3)
   declare @test table (p1 varchar(2), p2 varchar(3)
   insert into @test (p1,p2) varlues (@p1, @p2)
   select @p1Int=p1, @p2Int=p2 from @test
   

   입력 매개 변수의 길이가 제한보다 큰 것입니다 경우 대신 자동으로 문자열을 잘라낸의 SQL 서버가 오류를 Throw합니다.

6. ==============================

   6.그들은 지정된 길이보다있는 거보다가 오류가 발생하는 경우는 항상 그 길이를 확인하여 SP 년대에 문 경우가 발생하고있다. 이것은 비록이 소요 오히려 시간 및 데이터 크기를 업데이트 할 경우 갱신에 통증이있을 것입니다.

   그들은 지정된 길이보다있는 거보다가 오류가 발생하는 경우는 항상 그 길이를 확인하여 SP 년대에 문 경우가 발생하고있다. 이것은 비록이 소요 오히려 시간 및 데이터 크기를 업데이트 할 경우 갱신에 통증이있을 것입니다.

7. ==============================

   7.이것은 오늘의 문제를 해결할거야 대답이 아니라 MSSQL이 문제를 해결 것이라고, 추가하는 것을 고려하는 것이이 기능의 제안이 포함되어 있습니다. 우리가 그것의 인식을 제고하여 해결하는 데 도움이 될 수 있도록, MSSQL의 단점으로 이것을 호출하는 것이 중요하다. 여기에 당신이 투표하려는 경우 공식 제안이다 : https://feedback.azure.com/forums/908035-sql-server/suggestions/38394241-request-for-new-rule-string-truncation-error-for

   이것은 오늘의 문제를 해결할거야 대답이 아니라 MSSQL이 문제를 해결 것이라고, 추가하는 것을 고려하는 것이이 기능의 제안이 포함되어 있습니다. 우리가 그것의 인식을 제고하여 해결하는 데 도움이 될 수 있도록, MSSQL의 단점으로 이것을 호출하는 것이 중요하다. 여기에 당신이 투표하려는 경우 공식 제안이다 : https://feedback.azure.com/forums/908035-sql-server/suggestions/38394241-request-for-new-rule-string-truncation-error-for

   나는 당신의 좌절을 공유 할 수 있습니다. 매개 변수에 문자 크기를 설정하는 요점은 다른 개발자가 즉시 알 수 있도록이다 데이터를 전달 할 때 크기 제한은 (IntelliSense를 통해) 무엇인지. 이것은 바로 SPROC 서명에 구운 당신의 문서를하는 것과 같다.

   이봐, 난, 변수 할당시 암시 - 변환이 범인입니다 얻을. 그럼에도 불구하고, 에너지 싸우는 시나리오의 양을 소비 할 이유가 없다 이 기능을 주위에 작동하도록 강요되는 곳. 당신이 저를 요구하는 경우에, sprocs가 및 기능은 장소에 같은 엔진 규칙이 있어야합니다, 테이블을 채우기 때 할당 매개 변수, 즉 사용된다. 이 너무 많이 물어 정말?

   이러한 모든 제안은 큰 문자-제한을 사용하는 다음 EVERY SPROC에서 각 매개 변수에 대한 유효성 검사를 추가하는 것은 어리 석다. 정말 MSSQL을가 절단이 피할 수 있도록하는 유일한 방법은 알고 있지만? 그것의 ANSI / ISO 표준 또는 무엇이든, 그것은 바보 있다면 난 상관 없어!

   값이 너무 긴 경우 - 내 코드가 침입 할 - 때마다. 그것은해야한다 : 마하지 갈 통과하고, 코드를 수정. 당신은 년 동안 늪지대 여러 절단 버그가 그들을 잡지 못할 수 있습니다. 어떻게 당신의 데이터 무결성을 보장하기 위해 일어 났는가?

   그것은 당신의 SQL 코드는 오직 모든 매개 변수 검증을 한 후 호출됩니다 가정 위험하다. 나는 둘 다 내 웹 사이트에 그것을 호출하는 SPROC에서 동일한 유효성 검사를 추가하려고 나는 여전히 웹 사이트를지나 미끄러 내 SPROC에서 오류를 잡을 수있어. 그것은 위대한 정신 검사입니다! 당신이 원하는 경우 홈페이지 / WebService를도에 대한 귀하의 sproc를 다시 사용하는 것은 그것은 다른 호출 한 sprocs가 / 채용 정보 / 배포 / 애드혹 스크립트 (유효성 검사 매개 변수에는 프런트 엔드가없는 경우)?

   MSSQL은 비 최대 문자열 변수에이를 적용 할 수있는 "NO_TRUNC"옵션 필요 (심지어는 sprocs가에 대한 매개 변수 및 기능으로 사용). 그것은 연결 / 세션 범위 수 : (이하 "트랜잭션 격리 수준 READ UNCOMMITTED"옵션은 모든 쿼리에 미치는 영향 등) 또는 단일 변수에 초점을 맞추고 : (방법 "NOLOCK 것은"단지 1 표에 대한 표 힌트처럼). 아니면 추적 플래그 또는 데이터베이스 속성을 사용하면 모든 SPROC / 데이터베이스에서 함수 매개 변수에이를 적용 할 켭니다.

   내가 레거시 코드의 수십 년을 꺼꾸로을 요청하고 있지 않다. 그냥 더 나은에 옵션에 MS를 요구하는 우리의 데이터베이스를 관리 할 수 ​​있습니다.

from https://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures by cc-by-sa and MIT license