SQL Server가 저장 프로시저에서 varchar를 자동으로 잘라냅니다.

SQL Server가 저장 프로시저에서 varchar를 자동으로 잘라냅니다.

이 포럼 토론에 따르면 SQL Server(2005를 사용하고 있지만 이는 2000 및 2008에도 적용됨)는 자동으로 모든 데이터를 잘라냅니다.varchar를 사용하여 직접 문자열을 삽입하는 경우에도 막대 길이에 대한 저장 프로시저 매개 변수로 지정합니다.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행이 영향을 받습니다.이 있는 됩니다.testStringField으로 합니다. 프로시저의 Server를 .varchar매개 변수

이러한 행동은 때때로 편리할 수 있지만, 저는 그것을 끌 방법이 없다고 생각합니다.저장 프로시저에 문자열을 너무 길게 전달하면 오류가 발생하기 때문에 매우 성가신 작업입니다.이것을 처리하는 방법은 두 가지가 있는 것 같습니다.

먼저저프저를선언니다를 합니다.@testStringField변수를 6 크기로 지정하고 길이가 5를 초과하는지 확인합니다.이것은 약간의 해킹처럼 보이며 짜증나는 양의 상용어구 코드를 포함합니다.

두 번째로 를 째둘, 모저저시프로varchar로 .varchar(max)그리고 나서 내버려 둬.INSERT저장 프로시저 내의 문이 실패합니다.

후자는 잘 작동하는 것 같습니다. 그래서 제 질문은: 사용하는 것이 좋은 생각인가요?varchar(max)SQL Server 저장 프로시저의 문자열에 대해 항상 너무 긴 문자열이 전달될 때 실제로 저장된 proc가 실패하도록 하려면 어떻게 해야 합니까?베스트 프랙티스가 될 수 있을까요?무력화될 수 없는 침묵의 절단은 나에게 바보처럼 보입니다.

그냥 그래요.

하지만 제가 확인한 검사 중 하나는 매개 변수가 테이블 열 길이와 일치하는지 확인하는 것이기 때문에 문제를 발견한 적이 없습니다.고객 코드에도 있습니다.개인적으로 SQL이 너무 긴 데이터를 보지 못할 것으로 예상합니다.제가 잘린 데이터를 보았다면, 그것은 무엇이 그것을 초래했는지 명백한 출혈일 것입니다.

varchar(최대)의 필요성을 느낀다면 데이터 유형 우선 순위로 인해 엄청난 성능 문제가 발생해야 합니다.varchar(max)가 varchar(n)보다 우선 순위가 높습니다(가장 높음).따라서 이러한 유형의 쿼리에서는 검색이 아닌 검색이 제공되며 모든 varchar(100) 값은 CAST to varchar(최대)입니다.

UPDATE ...WHERE varchar100column = @varcharmaxvalue

편집:

이 문제와 관련하여 열려 있는 Microsoft Connect 항목이 있습니다.

그리고 Erland Sommarkog의 Strict 설정(및 일치하는 Connect 항목)에 포함할 가치가 있을 것입니다.

마틴스가 논평한 후 편집 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;

StackOverflow에 항상 감사드립니다. 이런 종류의 심층적인 논의를 이끌어내 주셔서 감사합니다.저는 최근에 표준 트랜잭션 및 시도/획득 블록에 대한 접근 방식을 사용하여 저장 프로시저를 더욱 견고하게 만들기 위해 저장 프로시저를 살펴보고 있습니다.저는 "애플리케이션 측면에 책임을 지도록 하는 것이 제 제안"이라는 Joe Stefanelli의 의견에 동의하지 않으며, "SQL Server가 문자열 길이를 확인하는 것이 훨씬 더 좋습니다."라는 Jez의 의견에 전적으로 동의합니다.저장 프로시저를 사용할 때 중요한 점은 데이터베이스에 고유한 언어로 작성되어 있으며 마지막 방어선 역할을 해야 한다는 것입니다.응용 프로그램 측에서는 255와 256 사이의 차이가 무의미한 숫자에 불과하지만 데이터베이스 환경에서는 최대 크기가 255인 필드에 256자를 사용할 수 없습니다.애플리케이션 검증 메커니즘은 백엔드 데이터베이스를 최대한 반영해야 하지만, 유지보수가 어렵기 때문에 애플리케이션이 잘못된 데이터를 허용할 경우 데이터베이스에서 좋은 피드백을 주셨으면 합니다.그래서 CSV나 JSON 같은 텍스트 파일을 많이 사용하는 대신 데이터베이스를 사용하고 있습니다.

저는 왜 제 SP 중 하나가 8152 오류를 던지고 다른 SP는 소리 없이 잘렸는지 혼란스러웠습니다.결국은 꿈틀거렸습니다.8152 오류를 발생시킨 SP에는 관련 테이블 열보다 하나 이상의 문자를 허용하는 매개 변수가 있습니다.테이블 열이 nvarchar(255)로 설정되었지만 매개 변수는 nvarchar(256)였습니다.그렇다면 제 "실수"가 gbn의 우려인 "대규모 성능 문제"를 해결하지 않을까요?max를 사용하는 대신 테이블 열 크기를 255로 일관되게 설정하고 SP 매개 변수를 256으로 한 문자만 더 길게 설정할 수 있습니다.이는 자동 절단 문제를 해결하고 성능 저하를 초래하지 않습니다.아마 제가 생각하지 못한 다른 단점이 있을 것입니다만, 저에게는 좋은 타협점인 것 같습니다.

업데이트: 유감스럽게도 이 기술은 일관되지 않습니다.추가 테스트를 통해 때때로 8152 오류를 트리거할 수 있으며 때로는 데이터가 자동으로 잘립니다.좀 더 신뢰할 수 있는 방법을 찾는 데 도움을 주시면 감사하겠습니다.

업데이트 2: 이 페이지에서 Pytoechito의 답변을 참조하십시오.

다음과 같은 동작을 볼 수 있습니다.

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

저장 프로시저를 호출하기 전에 응용 프로그램 측에서 입력의 유효성을 검사하도록 하는 것이 좋습니다.

업데이트: 유감스럽게도 이 기술은 일관되지 않습니다.추가 테스트를 통해 때때로 8152 오류를 트리거할 수 있으며 때로는 데이터가 자동으로 잘립니다.좀 더 신뢰할 수 있는 방법을 찾는 데 도움을 주시면 감사하겠습니다.

문자열의 256번째 문자가 공백이기 때문에 이 문제가 발생할 수 있습니다. VARCHARs는 삽입 시 후행 공백을 잘라내고 경고만 생성합니다.따라서 저장 프로시저는 문자열을 256자로 자동으로 잘라내고 삽입은 경고와 함께 후행 공백을 잘라냅니다.해당 문자가 공백이 아닌 경우 오류가 발생합니다.

의 아도해저프장만로것것드다입니일는를저시를 만드는 일 것입니다.VARCHAR공백이 아닌 문자를 잡기에 적합한 길이입니다. VARCHAR(512)아마 충분히 안전할 겁니다

한 가지 해결책은 다음과 같습니다.

1. 를 "" " " " " " " 로 합니다.varchar(max)
2. 올바른 데이터 길이의 sp private 변수가 있어야 합니다(단순히 매개 변수에 모두 복사하여 붙여넣고 끝에 "int"를 추가합니다).
3. 열 이름이 변수 이름과 동일한 테이블 변수 선언
4. 각 변수가 같은 이름의 열에 들어갈 행을 표에 삽입합니다.
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 Server에서 문자열을 자동으로 잘라내는 대신 오류가 발생합니다.

항상 SP에 if 문을 던져 길이를 확인하고 지정된 길이보다 크면 오류를 던질 수 있습니다.그러나 이 작업은 시간이 많이 걸리고 데이터 크기를 업데이트할 경우 업데이트하기가 번거롭습니다.

현재 문제를 해결할 수 있는 답변은 아니지만 MSSQL에서 추가를 고려하여 이 문제를 해결할 수 있는 기능 제안이 포함되어 있습니다.
이를 MSSQL의 단점으로 지적하는 것이 중요하므로 이에 대한 인식을 높여 해결을 도울 수 있습니다.
투표에 참여하고 싶다면 다음과 같은 공식 제안이 있습니다.
https://feedback.azure.com/forums/908035-sql-server/suggestions/://feedback.azure.com/forums/908035-sql-server/suggestions/38394241-request-for-new-rule-string-truncation-error-for

당신의 좌절감에 공감합니다.
를 설정하는 알 수 하기 입니다.
(Intellisense를 통해) 데이터를 전달할 때 크기 제한이 무엇인지 확인합니다.
이것은 당신의 문서를 스프록의 서명에 바로 굽는 것과 같습니다.

이봐, 이해해 변수 할당 중 암묵적 변환이 범인이야
에도 불구하고,와 싸우는 데 이를 소비할 좋은 이유는 없습니다.
이 기능을 사용해야 합니다.
묻는다면, 동일한 을 가지고 .
테이블을 채울 때 사용되는 매개 변수 할당에 사용됩니다.이것은 정말 무리한 부탁인가요?

모든 은 더 큰 제한을 것입니다.
모든 저장 프로시저에서 각 매개 변수에 대한 검증을 추가하는 것은 터무니없는 일입니다.
이것이 절단을 방지할 수 있는 유일한 방법이라는 것을 알지만, 정말 MSSQL입니까?
뭐든요! ANSI/ISO 표준요어상관없든뭐이든. 멍청해요!

값이 너무 긴 경우 - 코드가 손상되기를 원합니다.
다음과 같이 해야 합니다. go를 지나치지 말고 코드를 수정하십시오.
여러분은 몇 년 동안 여러 개의 절단 벌레가 곪아서 절대 잡지 못할 수도 있습니다.
데이터 무결성 보장에 어떤 변화가 있었습니까?

SQL 코드가 모든 매개 변수의 유효성을 검사한 후에만 호출된다고 가정하는 것은 위험합니다.
는 제 호출 .
그리고 아직도 웹사이트를 지나쳐버린 내 Sproc에서 오류를 발견합니다.아주 좋은 건강 검진입니다!
으로 Sproc를 하고 다른 해야 ?
Sprocs/Jobs/Deployment/Ad-Hoc 스크립트(파라미터를 검증할 프런트 엔드가 없는 경우)

은 "MSSQL이 필요합니다.NO_TRUNC최대값이 아닌 문자열 변수에 대해 이를 적용하는 옵션
(Sprocs 및 Functions에 대한 매개 변수로 사용되는 경우도 있음).
연결/세션 범위일 수 있습니다.
를 들어 예를들어▁"" "")TRANSACTION ISOLATION LEVEL READ UNCOMMITTED옵션이 모든 쿼리에 영향을 미칩니다.)
또는 단일 변수에 초점을 맞춥니다.
를 들어, "어떻게 "NOLOCK는 표 1개에 대한 표 힌트입니다.
또는 Trace-Flag 또는 Database Property를 켜서 데이터베이스의 모든 저장/함수 매개변수에 적용합니다.

저는 수십 년 동안의 레거시 코드를 뒤집으라고 요구하는 것이 아닙니다.
MS에 데이터베이스를 더 잘 관리할 수 있는 옵션을 요청하는 것뿐입니다.

언급URL : https://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures