일부 데이터 형식 및 일반적이지 않은 작업 처리의 SQL Server 및 Azure SQL 데이터베이스 개선 사항

아티클
06/20/2023

이 문서에서는 업그레이드 호환성 수준의 일부로 SQL Server 데이터베이스의 지속형 구조의 유효성을 검사하는 방법과 호환성 수준을 업그레이드한 후 영향을 받는 구조를 다시 빌드하는 방법을 소개합니다.

원래 제품 버전: SQL Server 2017, SQL Server 2016
원래 KB 번호: 4010261

Microsoft SQL Server 2016 및 Azure SQL Database의 데이터베이스 엔진에는 데이터 형식 변환 및 기타 여러 작업이 개선되었습니다. 이러한 개선 사항의 대부분은 부동 소수점 형식과 클래식 날짜/시간 형식으로 작업할 때 향상된 정밀도를 제공합니다.

이러한 개선 사항은 130개 이상의 데이터베이스 호환성 수준을 사용할 때 모두 사용할 수 있습니다. 즉, 일부(대부분 일반적이지 않은) 식의 경우 데이터베이스를 호환성 수준 130 이상으로 업그레이드한 후 일부 입력 값에 대해 다른 결과를 볼 수 있습니다. 이러한 결과는 다음 사항에 반영될 수 있습니다.

데이터베이스의 지속형 구조체
제약 조건이 적용되는 CHECK 포함된 테이블 데이터
지속형 계산 열
계산 열을 참조하는 인덱스
필터링된 인덱스 및 인덱싱된 뷰.

이전 버전의 SQL Server 만든 데이터베이스가 있는 경우 SQL Server 2016 이상으로 업그레이드한 후 데이터베이스 호환성 수준을 변경하기 전에 추가 유효성 검사를 수행하는 것이 좋습니다.

데이터베이스의 지속형 구조가 이러한 변경의 영향을 받는 경우 데이터베이스 호환성 수준을 업그레이드한 후 영향을 받는 구조를 다시 빌드하는 것이 좋습니다. 이렇게 하면 SQL Server 2016 이상에서 이러한 개선 사항을 활용할 수 있습니다.

이 문서에서는 호환성 수준 130 이상으로 업그레이드의 일부로 데이터베이스의 지속형 구조의 유효성을 검사하는 방법과 호환성 수준을 변경한 후 영향을 받는 구조를 다시 빌드하는 방법을 설명합니다.

데이터베이스 호환성 수준으로 업그레이드하는 동안 유효성 검사 단계

SQL Server 2016부터 SQL Server 데이터베이스와 Azure SQL 데이터베이스 모두 다음 작업의 전체 자릿수를 개선했습니다.

일반적이지 않은 데이터 형식 변환. 이러한 경계 및 제한은 다음과 같습니다.
- float/integer to/from datetime/smalldatetime
- 숫자/돈/smallmoney에서 실제/부동 소수 자릿수
- Float to real
및 의 DATEPART/DATEDIFF 일부 경우 DEGREES
CONVERT스타일을 사용하는 NULL

이러한 향상된 식 평가를 애플리케이션에서 사용하려면 데이터베이스의 호환성 수준을 130(SQL Server 2016의 경우) 또는 140(SQL Server 2017 및 Azure SQL Database의 경우)으로 변경합니다. 모든 변경 내용 및 변경 내용을 보여 주는 몇 가지 예제에 대한 자세한 내용은 부록 A 섹션을 참조하세요.

데이터베이스의 다음 구조는 식의 결과를 유지할 수 있습니다.

제약 조건이 적용되는 CHECK 테이블 데이터
지속형 계산 열
키 또는 포함된 열에서 계산 열을 사용하는 인덱스
필터링된 인덱스
인덱싱된 뷰

다음과 같은 경우를 생각해볼 수 있습니다.

이전 버전의 SQL Server 만든 데이터베이스가 있거나 SQL Server 2016 이상 버전에서 이미 만들어졌지만 호환성 수준 120 또는 이전 수준에서 만든 데이터베이스가 있습니다.
데이터베이스의 지속형 구조 정의의 일부로 전체 자릿수가 개선된 모든 식을 사용합니다.

이 시나리오에서는 호환성 수준 130 이상을 사용하여 구현되는 정밀도 개선의 영향을 받는 지속형 구조가 있을 수 있습니다. 이 경우 지속형 구조체의 유효성을 검사하고 영향을 받는 구조를 다시 빌드하는 것이 좋습니다.

구조에 영향을 미쳤으며 호환성 수준을 변경한 후 구조체를 다시 빌드하지 않으면 약간 다른 쿼리 결과가 발생할 수 있습니다. 결과는 특정 인덱스, 계산 열 또는 뷰가 사용되는지 여부와 테이블의 데이터를 제약 조건 위반으로 간주할 수 있는지 여부에 따라 달라집니다.

참고

SQL Server 추적 플래그 139

전역 추적 플래그 139는 SQL Server 2016 CU3 및 SP(서비스 팩) 1에 도입되어 DBCC 검사 명령DBCC CHECKDB의 scope 올바른 변환 의미 체계를 DBCC CHECKTABLE강제 적용하고DBCC CHECKCONSTRAINTS, 이전 호환성 수준이 있는 데이터베이스에서 호환성 수준 130으로 도입된 향상된 정밀도 및 변환 논리를 분석할 때 도입되었습니다.

경고

추적 플래그 139는 프로덕션 환경에서 지속적으로 사용하도록 설정되지 않으며 이 문서에 설명된 데이터베이스 유효성 검사만 수행할 목적으로 사용해야 합니다. 따라서 유효성 검사가 완료된 후 동일한 세션에서 를 사용하여 dbcc traceoff (139, -1) 사용하지 않도록 설정해야 합니다.

추적 플래그 139는 SQL Server 2016 CU3 및 SQL Server 2016 SP1부터 지원됩니다.

호환성 수준을 업그레이드하려면 다음 단계를 수행합니다.

유효성 검사를 수행하여 영향을 받는 지속형 구조를 식별합니다.
1. 를 실행 DBCC TRACEON(139, -1)하여 추적 플래그 139를 사용하도록 설정합니다.
2. 및 CHECKCONSTRAINTS 명령을 실행 DBCC CHECKDB/TABLE 합니다.
3. 를 실행 DBCC TRACEOFF(139, -1)하여 추적 플래그 139를 사용하지 않도록 설정합니다.
데이터베이스 호환성 수준을 130(SQL Server 2016의 경우) 또는 140(SQL Server 2017 및 Azure SQL 데이터베이스의 경우)으로 변경합니다.
1단계에서 식별한 모든 구조를 다시 빌드합니다.

참고

Azure SQL 데이터베이스 설정 추적 플래그의 추적 플래그는 Azure SQL Database에서 지원되지 않습니다. 따라서 유효성 검사를 수행하기 전에 호환성 수준을 변경해야 합니다.

데이터베이스 호환성 수준을 140으로 업그레이드합니다.
영향을 받은 지속형 구조를 식별하도록 유효성을 검사합니다.
2단계에서 식별한 구조를 다시 빌드합니다.

부록 A 에는 모든 정밀도 개선 사항에 대한 자세한 목록이 포함되어 있으며 각각에 대한 예제를 제공합니다.
부록 B 에는 유효성 검사를 수행하고 영향을 받는 구조를 다시 빌드하는 자세한 단계별 프로세스가 포함되어 있습니다.
부록 C 및 부록 D 에는 데이터베이스에서 잠재적으로 영향을 받을 수 있는 개체를 찾아내는 데 도움이 되는 스크립트가 포함되어 있습니다. 따라서 유효성 검사를 scope 해당 스크립트를 생성하여 검사를 실행할 수 있습니다. 데이터베이스의 지속형 구조가 호환성 수준 130의 정밀도 향상에 영향을 받는지 여부를 가장 쉽게 확인하려면 부 록 D 에서 스크립트를 실행하여 올바른 유효성 검사를 생성한 다음, 이 스크립트를 실행하여 유효성 검사를 수행합니다.

부록 A: 호환성 수준 130의 변경 내용

이 부록은 호환성 수준 130의 식 평가 개선 사항에 대한 자세한 목록을 제공합니다. 각 변경에는 연결된 예제 쿼리가 포함됩니다. 쿼리를 사용하여 호환성 수준 130을 사용하는 데이터베이스와 비교하여 130 이전 호환성 수준을 사용하는 데이터베이스에서 실행 간의 차이점을 표시할 수 있습니다.

다음 표에는 데이터 형식 변환 및 추가 작업이 나와 있습니다.

데이터 형식 변환

시작	받는 사람	변경 사항	예제 쿼리	호환성 수준 < 130에 대한 결과	호환성 수준에 대한 결과 = 130
`float`, `real`, , `decimalnumeric`, `money`, 또는`smallmoney`	`datetime` 또는 `smalldatetime`	반올림 정밀도를 높입니다. 이전에는 날짜와 시간이 별도로 변환되었고 결과를 결합하기 전에 잘렸습니다.	`DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT)`	1.19999996141975	1.2
`datetime`	`bigint, int, or smallint`	시간 부분이 정확히 반나절 또는 반나절의 틱에 있는 음의 날짜/시간이 잘못 반올림됩니다(결과는 1로 해제됨).	`DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT)`	0	-1
`datetime` 또는 `smalldatetime`	`float, real, numeric, money, or smallmoney`	경우에 따라 마지막 8비트 정밀도의 정밀도를 개선했습니다.	`DECLARE @p0 DATETIME = '1899-12-31 23:58:00.470' DECLARE @f FLOAT = CONVERT(FLOAT, @p0) SELECT @f, CAST(@f AS VARBINARY(8))`	-0.00138344907407406, 0xBF56AA9B21D85800	-0.00138344907407407407, 0xBF56AA9B21D8583B
`float`	`real`	경계 검사는 덜 엄격합니다.	`SELECT CAST (3.40282347000E+038 AS REAL)`	산술 오버플로	3.402823E+38
`numeric`, `money` 및 `smallmoney`	`float`	입력 눈금이 0이면 숫자의 네 부분을 결합하면 반올림이 불가능합니다.	`DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f)`	0x4720000000000000	0x4720000000000001
`numeric`, `money` 및 `smallmoney`	`float`	입력 눈금이 0이 아닌 경우 10^scale으로 나눌 때 반올림 중단이 발생합니다.	`DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8))`	0x41678C29C06522C4	0x41678C29C06522C3
`real` 또는 `float`	숫자	경우에 따라 반올림 정밀도를 개선했습니다.	`DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1))`	0.2	0.1
`real` 또는 `float`	숫자	경우에 따라 16자리 이상으로 반올림할 때 전체 자릿수가 향상되었습니다.	`DECLARE @v decimal(38, 18) = 1E-18 SELECT @v`	0.000000000000000000	0.000000000000000001
`real` 또는 `float`	`money` 또는 `smallmoney`	경우에 따라 큰 숫자를 변환할 때 정확도가 향상되었습니다.	`DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money)`	562949953421312.2048	562949953421312.25
`(n)(var)char`	`numeric`	39자를 초과하는 입력은 더 이상 산술 오버플로를 트리거하지 않습니다.	`DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1))`	산술 오버플로	1.1
`(n)(var)char`	`bit`	선행 공백 및 기호를 지원합니다.	`DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit)`	값 '1'을 데이터 형식 비트로 `nvarchar` 변환할 때 변환에 실패했습니다.	1
`datetime`	`time` 또는 `datetime2`	전체 자릿수가 더 높은 날짜/시간 형식으로 변환할 때 정밀도를 개선했습니다. datetime 값은 1/300초의 틱으로 저장됩니다. 최신 시간 및 datetime2 형식은 불연속 숫자 수를 저장합니다. 여기서 숫자 수는 전체 자릿수와 일치합니다.	`DECLARE @value datetime = '1900-01-01 00:00:00.003' SELECT CAST(@value AS time(7))`	00:00:00.0030000	00:00:00.0033333
`time` 또는 `datetime2`	`datetime`	경우에 따라 반올림이 향상되었습니다.	`DECLARE @value time(4) = '00:00:00.0045' SELECT CAST(@value AS datetime)`	1900-01-01 00:00:00.007	1900-01-01 00:00:00.003

작업

작업	변경 사항	예제 쿼리	호환성 수준 <130에 대한 결과	호환성 수준 130에 대한 결과
숫자 데이터 형식을 `RADIANS` 사용하는 또는 `DEGREES` 기본 제공 함수를 사용합니다.	`DEGREES` 는 pi/180으로 나눕니다. 여기서 이전에 180/pi를 곱했습니다. 의 경우와 유사합니다 `RADIANS`.	`DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1)`	57.295779513082323000	57.295779513082322865
한 피연산자의 크기가 결과의 배율보다 큰 경우 숫자 추가 또는 빼기입니다.	반올림은 항상 추가 또는 빼기 후에 발생하지만 이전에는 때때로 발생할 수 있었습니다.	`DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2`	8.8	8.9
`CONVERT` 스타일이 있습니다 `NULL` .	`CONVERT` 은 `NULL` 대상 형식이 숫자일 때 항상 를 반환 `NULL` 합니다.	`SELECT CONVERT (SMALLINT, '0', NULL);`	0	`NULL`
`DATEPART` datetime 데이터 형식과 함께 마이크로초 또는 나노초 옵션을 사용하는 입니다.	마이크로 또는 나노초로 변환하기 전에 값이 더 이상 밀리초 수준에서 잘리지 않습니다.	`DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt);`	3000	3333
`DATEDIFF` datetime 데이터 형식과 함께 마이크로초 또는 나노초 옵션을 사용하는 입니다.	마이크로 또는 나노초로 변환하기 전에 값이 더 이상 밀리초 수준에서 잘리지 않습니다.	`DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME = '1900-01-01 00:00:00.007' SELECT DATEDIFF(MICROSECOND, @d1, @d2)`	3000	3333
datetime 및 datetime2 값과 0이 아닌 값의 비교(밀리초)입니다.	datetime2 값과의 비교를 실행할 때 Datetime 값은 더 이상 밀리초 수준에서 잘리지 않습니다. 즉, 이전에 같음과 비교한 특정 값은 더 이상 같음과 비교되지 않습니다.	`DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME2(3) = @d1 SELECT CAST(@d1 AS datetime2(7)), @d2SELECT CASE WHEN (@d1=@d2) THEN 'equal' ELSE 'unequal' END`	1900-01-01 00:00:00.003000, 1900-01-01 00:00:00.003 등가	1900-01-01 00:00:00.00.00333, 1900-01-01 00:00:00.003 같지 않음
`ROUND` 데이터 형식을 `float` 사용하는 함수입니다.	반올림 결과는 다릅니다.	`SELECT ROUND(CAST (-0.4175 AS FLOAT), 3)`	-0.418	-0.417

부록 B: 지속형 구조를 확인하고 업데이트하는 단계

데이터베이스에 호환성 수준 130의 변경에 영향을 받는 지속형 구조체가 있는지 여부와 영향을 받는 구조를 다시 빌드하는 것이 좋습니다.

이는 이전 버전의 SQL Server 또는 130보다 낮은 호환성 수준을 사용하여 데이터베이스에서 만든 지속형 구조에만 적용됩니다. 잠재적으로 영향을 받을 수 있는 지속형 구조에는 다음이 포함됩니다.

제약 조건이 적용되는 CHECK 테이블 데이터
지속형 계산 열
키 또는 포함된 열에서 계산 열을 사용하는 인덱스
필터링된 인덱스
인덱싱된 뷰

이 경우 다음 절차를 실행합니다.

1단계: 데이터베이스 호환성 수준 확인

데이터베이스의 호환성 수준 보기 또는 변경에 설명된 절차를 사용하여 데이터베이스의 호환성 수준을 확인합니다.
데이터베이스 호환성 수준이 130보다 낮은 경우 호환성 수준을 130으로 늘리기 전에 2단계에 설명된 유효성 검사를 수행하는 것이 좋습니다.

2단계: 영향을 받는 지속형 구조 식별

데이터베이스에 다음 방식 중 하나로 호환성 수준 130의 향상된 정밀도 및 변환 논리의 영향을 받는 지속형 구조체가 있는지 확인합니다.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS데이터베이스의 모든 구조체의 유효성을 검사하는 입니다.
DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS- 단일 테이블과 관련된 구조체의 유효성을 검사합니다.

지속형 값이 계산된 값과 비교되는지 확인하고 차이가 있는 사례에 플래그를 지정하려면 옵션이 WITH EXTENDED_LOGICAL_CHECKS 필요합니다. 이러한 검사는 광범위하기 때문에 이 옵션을 사용하는 문의 런타임 DBCC 은 옵션 없이 문을 실행하는 DBCC 것보다 깁니다. 따라서 큰 데이터베이스에 대한 권장 사항은 개별 테이블을 정확히 파악하는 데 사용하는 DBCC CHECKTABLE 것입니다.

DBCC CHECKCONSTRAINTS 는 제약 조건의 유효성을 검사 CHECK 하는 데 사용할 수 있습니다. 이 문은 데이터베이스 또는 테이블 수준에서 사용할 수 있습니다.

DBCC CHECK 온라인 워크로드에 대한 검사의 잠재적 영향 때문에 유지 관리 기간 동안 항상 문을 실행해야 합니다.

데이터베이스 수준 유효성 검사

데이터베이스 수준의 유효성 검사는 작고 적당히 크기가 작은 데이터베이스에 적합합니다. 큰 데이터베이스에 대해 테이블 수준 유효성 검사를 사용합니다.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS 는 데이터베이스의 모든 지속형 구조체의 유효성을 검사하는 데 사용됩니다.

DBCC CHECKCONSTRAINTS 는 데이터베이스의 모든 CHECK 제약 조건의 유효성을 검사하는 데 사용됩니다.

DBCC CHECKCONSTRAINTS 는 제약 조건의 무결성을 확인하는 데 사용됩니다. 다음 스크립트를 사용하여 데이터베이스의 유효성을 검사합니다.

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

추적 플래그를 사용하면 호환성 수준 130에 있는 향상된 정밀도 및 변환 논리를 사용하여 검사를 수행하여 데이터베이스의 호환성 수준이 낮더라도 올바른 변환 의미 체계를 강제로 적용합니다.

CHECKCONSTRAINTS 문이 완료되고 결과 집합을 반환하지 않는 경우 추가 작업이 필요하지 않습니다.

문에서 결과 집합을 반환하는 경우 결과의 각 줄은 제약 조건 위반을 나타내며 제약 조건을 위반하는 값도 포함합니다.

위반된 값(결과 집합의 열)과 함께 테이블 및 제약 조건의 WHERE 이름을 저장합니다.

다음 예제에서는 제약 조건이 있는 CHECK 테이블과 낮은 호환성 수준에서 제약 조건을 충족하지만 호환성 수준 130에서 제약 조건을 위반하는 단일 행을 보여 줍니다.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
    c2 datetime,
    c3 datetime,
    c4 int,
    CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2,c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(
    convert(datetime, '1900-01-01 00:00:00.997'),
    convert(datetime, '1900-01-01 00:00:01'), 3
)
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

명령은 CHECKCONSTRAINT 다음 결과를 반환합니다.

표	제약 조건	여기서 각 부분이 나타내는 의미는 다음과 같습니다.
[dbo]. [table1]	[chk1]	[c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' 및 [c4] = '3'

이 결과는 'Where'의 열 값 조합에 대한 제약 조건 [chk1]이 위반되었음을 나타냅니다.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS 데이터베이스의 모든 지속형 구조의 유효성을 검사합니다. 단일 문이 데이터베이스의 모든 구조체의 유효성을 검사하기 때문에 가장 편리한 옵션입니다. 그러나 이 옵션은 문의 예상 런타임으로 인해 큰 데이터베이스에 적합하지 않습니다.

다음 스크립트를 사용하여 전체 데이터베이스의 유효성을 검사합니다.

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
GO
DBCC TRACEOFF(139, -1)
GO

CHECKDB 문이 성공적으로 완료되면 추가 작업이 필요하지 않습니다.

명령문이 오류로 완료되면 다음 단계를 수행합니다.

SQL Server Management Studio(SSMS)의 DBCC 메시지 창에 있는 문의 실행 결과를 파일에 저장합니다.
보고된 오류가 지속형 구조와 관련이 있는지 확인합니다.

표 1: 불일치에 대한 지속형 구조 및 해당 오류 메시지

영향을 받는 구조체 형식	관찰된 오류 메시지	기록해 두기
지속형 계산 열	Msg 2537, 수준 16 테이블 오류: 개체 ID <object_id> , 인덱스 ID <index_id> , . 레코드 검사(유효한 계산 열)이 실패했습니다. 값은 입니다.	개체 ID <object_id> 및 인덱스 ID <index_id>
키 또는 포함된 열 필터링된 인덱스의 계산 열을 참조하는 인덱스	Msg 8951 테이블 오류: 테이블 '<table_name>'(ID <object_id>). 데이터 행에는 인덱스 '<index_name'(ID <>index_id>) 및/또는 Msg 8952 테이블 오류: 테이블 '<table_name'(ID <table_name>>)에 일치하는 인덱스 행이 없습니다. 인덱스 ''(ID <index_id>)의 인덱스 행이 데이터 행과 일치하지 않습니다. 또한 보조 오류 8955 및/또는 8956이 있을 수 있습니다. 여기에는 영향을 받은 정확한 행에 대한 세부 정보가 포함됩니다. 이 연습에서는 무시될 수 있습니다.	개체 ID <object_id> 및 인덱스 ID <index_id>
인덱싱된 뷰	Msg 8908 인덱싱된 뷰 '<view_name>'(개체 ID <object_id>)에는 뷰 정의가 생성하는 모든 행이 포함되지 않습니다. 및/또는 Msg 8907 인덱싱된 뷰 '<view_name>'(개체 ID <object_id>)에는 뷰 정의에서 생성되지 않은 행이 포함되어 있습니다.	개체 ID <object_id>

데이터베이스 수준 유효성 검사를 완료한 후 3단계로 이동합니다.

개체 수준 유효성 검사

더 큰 데이터베이스의 경우 유지 관리 기간의 크기를 줄이거나 확장 논리 검사를 잠재적으로 영향을 받는 개체로만 제한하기 위해 한 번에 하나의 테이블 또는 하나의 보기에서 구조 및 제약 조건의 유효성을 검사하는 것이 좋습니다.

부록 C 섹션의 쿼리를 사용하여 잠재적으로 영향을 받는 테이블을 식별합니다. 부록 D 섹션의 스크립트를 사용하여 부록 C 섹션에 나열된 쿼리에 따라 및 CHECKCONSTRAINTS 제약 조건을 생성 CHECKTABLE 할 수 있습니다.

DBCC CHECKCONSTRAINTS

단일 테이블 또는 뷰와 관련된 제약 조건의 유효성을 검사하려면 다음 스크립트를 사용합니다.

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

추적 플래그를 사용하면 호환성 수준 130에 있는 향상된 정밀도 및 변환 논리를 사용하여 검사를 수행하여 데이터베이스의 호환성 수준이 낮더라도 향상된 의미 체계를 강제로 적용합니다.

CHECKCONSTRAINTS 문이 완료되고 결과 집합을 반환하지 않는 경우 추가 작업이 필요하지 않습니다.

문에서 결과 집합을 반환하는 경우 결과의 각 줄은 제약 조건 위반을 나타내며 제약 조건을 위반하는 값도 제공합니다.

위반된 값(결과 집합의 열)과 함께 테이블 및 제약 조건의 WHERE 이름을 저장합니다.

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

단일 테이블 또는 뷰와 관련된 지속형 구조체의 유효성을 검사하려면 다음 스크립트를 사용합니다.

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

CHECKTABLE 문이 성공적으로 완료되면 추가 작업이 필요하지 않습니다.

명령문이 오류로 완료되면 다음 단계를 수행합니다.

SSMS의 DBCC 메시지 창에 있는 문의 실행 결과를 파일에 저장합니다.
보고된 오류가 표 1에 나열된 대로 지속형 구조와 관련이 있는지 확인합니다.
테이블 수준 유효성 검사를 완료한 후 3단계로 이동합니다.

3단계: 호환성 수준 130으로 업그레이드

데이터베이스의 호환성 수준이 이미 130인 경우 이 단계를 건너뛸 수 있습니다.

다음 스크립트를 사용하여 데이터베이스의 호환성 수준을 130으로 변경할 수 있습니다.

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

참고

호환성 수준 130에는 쿼리 최적화 프로그램 변경 내용이 있으므로 호환성 수준을 변경하기 전에 쿼리 저장소를 사용하도록 설정하는 것이 좋습니다. 자세한 내용은 쿼리 저장소 사용 시나리오에서 최신 SQL Server 업그레이드하는 동안 성능 안정성 유지 섹션을 참조하세요.

4단계: 지속형 구조 업데이트

2단계에서 수행된 유효성 검사 중에 불일치가 발견되지 않은 경우 업그레이드를 완료하고 이 단계를 건너뛸 수 있습니다. 2단계에서 불일치가 발견되면 데이터베이스에서 불일치를 제거하기 위해 추가 작업이 필요합니다. 필요한 작업은 영향을 받는 구조의 종류에 따라 달라집니다.

중요

데이터베이스 호환성 수준이 130으로 변경된 후에만 이 단계에서 복구 작업을 수행합니다.

데이터베이스(또는 데이터베이스) 백업

다음 섹션에서 설명하는 작업을 수행하기 전에 전체 데이터베이스 백업을 수행하는 것이 좋습니다. Azure SQL 데이터베이스를 사용하는 경우 백업을 직접 수행할 필요가 없습니다. 업데이트에 문제가 있는 경우 항상 특정 시점 복원 기능을 사용하여 시간을 거슬러 올라갈 수 있습니다.

CHECK 제약 조건

제약 조건 위반을 CHECK 수정하려면 테이블 또는 CHECK 제약 조건 자체의 데이터를 수정해야 합니다.

제약 조건의 이름(2단계에서 가져온)에서 다음과 같이 제약 조건 정의를 가져올 수 있습니다.

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

영향을 받는 테이블 행을 검사하려면 문에서 이전에 반환 DBCC CHECKCONSTRAINTS 한 Where 정보를 사용할 수 있습니다.

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

영향을 받는 행을 업데이트하거나 제약 조건 정의를 변경하여 제약 조건이 위반되지 않도록 해야 합니다.

테이블 데이터 업데이트

데이터를 업데이트하는 방법을 나타내는 하드 규칙은 없습니다. 일반적으로 에서 반환된 각 다른 Where 문에 DBCC CHECKCONSTRAINTS대해 다음 update 문을 실행합니다.

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

제약 조건과 호환성 수준 130의 제약 조건을 위반하는 행이 있는 다음 예제 테이블을 고려합니다.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
c2 datetime,
c3 datetime,
c4 int,
CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2, c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(convert(datetime, '1900-01-01 00:00:00.997'),
convert(datetime, '1900-01-01 00:00:01'), 3)
GO

이 예제에서는 제약 조건이 간단합니다. 열 c4 은 및 와 관련된 c2 식과 c3같아야 합니다. 테이블을 업데이트하려면 이 값을 에 할당합니다 c4.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130
GO
UPDATE dbo.table1 SET c4 = datediff (ms, c2,c3)
WHERE [c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'
GO

update 문에 사용되는 절은 WHERE 에서 반환 DBCC CHECKCONSTRAINTS된 Where 정보에 해당합니다.

CHECK 제약 조건 업데이트

제약 조건을 변경하려면 제약 조건을 CHECK 삭제하고 다시 만들어야 합니다. 업데이트된 제약 조건 정의에 문제가 있는 경우 동일한 트랜잭션에서 둘 다 수행하는 것이 좋습니다. 다음 Transact-SQL을 사용할 수 있습니다.

BEGIN TRANSACTION

ALTER TABLE [schema_name].[table_name]

DROP CONSTRAINT [constraint_name]

ALTER TABLE [schema_name].[table_name]

ADD CONSTRAINT [constraint_name]

CHECK (new_constraint_definition)

COMMIT

GO

The following example updates the constraint chk1 in dbo.table1:

BEGIN TRANSACTION

ALTER TABLE dbo.table1

DROP CONSTRAINT chk1

ALTER TABLE dbo.table1

ADD CONSTRAINT chk1

CHECK (c4 <= DATEDIFF (ms, c2, c3))

COMMIT

GO

지속형 계산 열

지속형 계산 열을 업데이트하는 가장 쉬운 방법은 계산 열에서 참조하는 열 중 하나를 업데이트하는 것입니다. 열의 새 값은 작업에서 사용자 데이터를 변경하지 않도록 이전 값과 같을 수 있습니다.

2단계에서 기록한 계산 열의 불일치와 관련된 모든 object_id 항목에 대해 다음 단계를 수행합니다.

계산 열 식별:

다음 쿼리를 실행하여 테이블 object_id이름과 기록된 에 대한 지속형 계산 열의 이름을 검색합니다.

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS 'table',
QUOTENAME(c1.name) AS 'persisted computed column',
c1.column_id AS 'computed_column_id' ,
definition AS 'computed_column_definition'
FROM sys.tables t
JOIN sys.computed_columns c1 ON t.object_id=c1.object_id
AND c1.is_persisted=1
JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.object_id=object_id

참조된 열 식별:

다음 쿼리를 실행하여 계산 열에서 참조하는 열을 식별합니다. 참조된 열 이름 중 하나를 기록해 둡니다.

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) AS 'referencing object',
o.type_desc AS 'object type', referenced_minor_id AS 'referenced_column_id', c.name AS 'referenced_column_name'
FROM sys.sql_expression_dependencies sed
JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id
JOIN sys.objects o ON sed.referencing_id=o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
JOIN sys.columns c ON o.object_id=c.object_id AND sed.referenced_minor_id=c.column_id
WHERE referencing_class=1 AND referenced_class=1 AND referencing_id=object_id AND referencing_minor_id=computed_column_id

참조된 UPDATE 열 중 하나와 관련된 문을 실행하여 계산 열의 업데이트를 트리거합니다.
- 다음 문은 계산 열에서 참조하는 열의 업데이트를 트리거하고 계산 열의 업데이트를 트리거합니다.
```
UPDATE [schema_name].[table_name]
SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)
```
- 문의 식은 ISNULL DB 호환성 수준 130 식 계산 논리를 사용하여 계산 열이 업데이트되도록 하면서 원래 열의 값이 변경되지 않도록 작성됩니다.
- 매우 큰 테이블의 경우 단일 트랜잭션의 모든 행을 업데이트하지 않을 수 있습니다. 이러한 경우 예를 들어 기본 키에 따라 행 범위를 식별하는 절을 update 문에 추가하여 WHERE 업데이트를 일괄 처리로 실행할 수 있습니다.

계산 열을 참조하는 인덱스를 식별합니다.

SELECT i.name AS [index name]
FROM sys.index_columns ic JOIN sys.indexes i ON ic.object_id=i.object_id AND ic.index_id=i.index_id
WHERE i.object_id=object_id AND ic.column_id=computed_column_id

이 쿼리는 지속형 계산 열을 참조하는 인덱스를 식별합니다. 이러한 인덱스는 다시 작성해야 합니다. 이렇게 하려면 다음 섹션의 단계를 수행합니다.

인덱스, 필터링된 인덱스 및 인덱싱된 뷰

인덱스의 불일치는 2단계의 출력에서 오류 8951 및 8952(테이블의 경우) 또는 8907 및 8908(보기의 경우)에 DBCC CHECK 해당합니다.

이러한 불일치를 복구하려면 를 사용하여 를 실행 DBCC CHECKTABLEREPAIR_REBUILD합니다. 이렇게 하면 데이터 손실 없이 인덱스 구조가 복구됩니다. 그러나 데이터베이스는 단일 사용자 모드여야 하므로 복구가 발생하는 동안 다른 사용자가 사용할 수 없습니다.

영향을 받는 인덱스를 수동으로 다시 작성할 수도 있습니다. 인덱스 다시 작성은 온라인 작업(지원되는 SQL Server 버전)으로 수행할 수 있으므로 워크로드를 오프라인으로 전환할 수 없는 경우 이 옵션을 사용해야 합니다.

인덱스 다시 작성

단일 사용자 모드에서 데이터베이스를 설정하는 것이 옵션이 아닌 경우 2단계에서 식별된 각 인덱스에 대해 를 사용하여 ALTER INDEX REBUILD인덱스를 개별적으로 다시 작성할 수 있습니다.

다음 쿼리를 사용하여 지정된 object_id 및 index_id에 대한 테이블 및 인덱스 이름을 가져옵니다.

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'table', i.name AS 'index_name'

FROM sys.objects o JOIN sys.indexes i ON o.object_id=i.object_id

WHERE o.object_id = object_id AND i.index_id = index_id

다음 문을 사용하여 인덱스 다시 작성:

ALTER INDEX index_name ON [schema_name].[table_name] REBUILD WITH (ONLINE=ON)

참고

Standard, Web 또는 Express 버전을 사용하는 경우 온라인 인덱스 빌드가 지원되지 않습니다. 따라서 문에서 ALTER INDEX 옵션을 WITH (ONLINE=ON) 제거해야 합니다.

다음 예제에서는 필터링된 인덱스의 다시 작성을 보여줍니다.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table2
(
    c2 datetime,
    c3 float
)
GO
INSERT dbo.table2 (c2,c3) VALUES ('1899-12-31 23:58:00.470', -0.00138344907407406)
GO
CREATE INDEX ix_1 ON dbo.table2(c2)
WHERE (c2=-0.00138344907407406)
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130GOALTER INDEX ix_1 ON [dbo].[table2] REBUILD WITH (ONLINE=ON)
GO

정기적인 유지 관리 계획이 있는 경우 예약된 유지 관리의 일부로 이 인덱스 다시 빌드를 포함하는 것이 좋습니다.

DBCC를 사용하여 복구

2단계에서 설명한 불일치가 있는 인덱스와 관련된 각(object_id)에 대해 다음 스크립트를 실행하여 복구를 수행합니다. 이 스크립트는 복구 작업을 위해 데이터베이스를 단일 사용자 모드로 설정합니다. 최악의 경우 복구는 전체 인덱스 다시 작성을 수행합니다.

USE [database_name]

GO

ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKTABLE (object_id, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

ALTER DATABASE CURRENT SET MULTI_USER

GO

부록 C: 후보 테이블을 식별하는 쿼리

다음 스크립트는 호환성 수준 130의 향상된 기능의 영향을 받는 데이터 형식을 사용하는 지속형 구조 및 제약 조건의 존재에 따라 를 사용하여 DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS유효성을 검사할 후보 테이블을 식별합니다.

다음 쿼리 집합은 추가 유효성 검사가 필요한 테이블 및 잠재적으로 영향을 받는 구조에 대한 세부 정보를 나열합니다.

인덱싱된 뷰

다음 쿼리는 영향을 받는 데이터 형식을 사용하거나 영향을 받는 기본 제공 함수를 사용하여 열을 참조하는 모든 인덱싱된 뷰를 반환합니다.

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'view', QUOTENAME(i.name) AS 'index',QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'referenced table', QUOTENAME(c.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the view definition contains a float or datetime value, or a conversion to such value

s.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.objects o ON sed.referencing_id = o.object_id AND o.type=N'V'

JOIN sys.indexes i ON o.object_id=i.object_id

JOIN sys.sql_modules s ON s.object_id=o.object_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_idJOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (c.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR s.[definition] LIKE '%DATEDIFF%'

OR s.[definition] LIKE '%CONVERT%'

OR s.[definition] LIKE '%CAST%'

OR s.[definition] LIKE '%DATEPART%'

OR s.[definition] LIKE '%DEGREES%')

지속형 계산 열

다음 쿼리는 영향을 받는 데이터 형식을 사용하거나 영향을 받는 기본 제공 함수를 사용하여 다른 열을 참조하는 계산 열이 있는 모든 테이블을 반환합니다. 여기서 열은 인덱스에서 유지되거나 참조됩니다.

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with computed column',

QUOTENAME(c1.name) AS 'computed column', c1.is_persisted,QUOTENAME(c2.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the column definition contains a float or datetime value, or a conversion to such value

c1.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id

JOIN sys.columns c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id=c2.column_id

JOIN sys.types t ON c2.system_type_id=t.system_type_idWHERE referencing_class=1 AND referenced_class=1

AND (c2.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR c1.[definition] LIKE '%DATEDIFF%'

OR c1.[definition] LIKE '%CONVERT%'

OR c1.[definition] LIKE '%DATEPART%'

OR c1.[definition] LIKE '%DEGREES%')

AND (

-- the column is persisted

c1.is_persisted=1

-- OR the column is included in an index

OR EXISTS (SELECT 1 FROM sys.index_columns ic WHERE ic.object_id=c1.object_id AND ic.column_id=c1.column_id)

)

필터링된 인덱스

다음 쿼리는 데이터 형식에 영향을 주는 필터 조건의 열을 참조하는 필터링된 인덱스가 있는 모든 테이블을 반환합니다.

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with filtered index',

QUOTENAME(i.name) AS 'referencing index',

QUOTENAME(c.name) AS 'referenced column',

t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is where the filter condition contains a float or datetime value

i.filter_definition AS 'filter condition'

FROM sys.sql_expression_dependencies sed

JOIN sys.indexes i ON sed.referencing_id=i.object_id AND sed.referencing_minor_id=i.index_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_id

JOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=7 AND referenced_class=1 AND i.has_filter=1

AND c.system_type_id IN ( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

)

제약 조건 확인

다음 쿼리는 영향을 받는 데이터 형식 또는 기본 제공 함수를 참조하는 검사 제약 조건이 있는 모든 테이블을 나열합니다.

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with check constraint',

QUOTENAME(c.name) AS 'constraint_name', c.definition AS 'constraint_definition',

QUOTENAME(col.name) AS 'referenced column', t.name AS 'data type'

FROM sys.sql_expression_dependencies sed

JOIN sys.check_constraints c ON sed.referencing_id=c.object_id AND sed.referencing_class=1

JOIN sys.columns col ON sed.referenced_id=col.object_id AND sed.referenced_minor_id=col.column_id

JOIN sys.types t ON col.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (col.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint)

OR c.[definition] LIKE '%DATEDIFF%'

OR c.[definition] LIKE '%CONVERT%'

OR c.[definition] LIKE '%DATEPART%'

OR c.[definition] LIKE '%DEGREES%')

부록 D: CHECK* 문을 만드는 스크립트

다음 스크립트는 이전 부록의 쿼리를 결합하고 테이블 및 뷰 목록을 및 CHECKTABLE 문 형식 CHECKCONSTRAINTS 으로 표시하여 결과를 간소화합니다.

DECLARE @CRLF nvarchar(10) = CHAR(13) + CHAR(10);
DECLARE @sql nvarchar(max) = N'DBCC TRACEON(139,-1); ' + @CRLF ;

SELECT @sql += N'DBCC CHECKTABLE (N''' + object_for_checktable + N''') WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS; ' + @CRLF
FROM
(

--indexed views
SELECT DISTINCT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
 INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id AND o.type = N'V'
 INNER JOIN sys.indexes AS i ON o.object_id = i.object_id
 INNER JOIN sys.sql_modules AS s ON s.object_id = o.object_id
 INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id
 INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id

WHERE referencing_class = 1 AND referenced_class=1 
 AND (c.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR s.[definition] LIKE N'%DATEDIFF%'
 OR s.[definition] LIKE N'%CONVERT%'
 OR s.[definition] LIKE N'%CAST%'
 OR s.[definition] LIKE N'%DATEPART%'
 OR s.[definition] LIKE N'%DEGREES%')

UNION

--persisted computed columns
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.computed_columns AS c1 ON sed.referencing_id = c1.object_id AND sed.referencing_minor_id = c1.column_id
INNER JOIN sys.columns AS c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id = c2.column_id
INNER JOIN sys.types AS t ON c2.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 
 AND (c2.system_type_id IN
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c1.[definition] LIKE N'%DATEDIFF%'
 OR c1.[definition] LIKE N'%CONVERT%'
 OR c1.[definition] LIKE N'%DATEPART%'
 OR c1.[definition] LIKE N'%DEGREES%')
AND (
-- the column is persisted
c1.is_persisted = 1 
-- OR the column is included in an index
OR EXISTS (SELECT 1 FROM sys.index_columns AS ic 
WHERE ic.object_id = c1.object_id AND ic.column_id=c1.column_id)
)

UNION

--indexed views
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.indexes AS i ON sed.referencing_id = i.object_id AND sed.referencing_minor_id = i.index_id
INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id 
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE referencing_class = 7 AND referenced_class = 1 AND i.has_filter = 1
AND c.system_type_id IN ( 
 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
)) AS a

SELECT @sql += N'DBCC CHECKCONSTRAINTS (N''' + object_for_checkconstraints + N'''); ' + @CRLF
FROM
(

SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checkconstraints'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.check_constraints AS c ON sed.referencing_id = c.object_id AND sed.referencing_class = 1
INNER JOIN sys.columns AS col ON sed.referenced_id = col.object_id AND sed.referenced_minor_id = col.column_id
INNER JOIN sys.types AS t ON col.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 AND (col.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c.[definition] LIKE N'%DATEDIFF%'
 OR c.[definition] LIKE N'%CONVERT%'
 OR c.[definition] LIKE N'%DATEPART%'
 OR c.[definition] LIKE N'%DEGREES%')
) a

SET @sql += N'DBCC TRACEOFF(139,-1);';

PRINT @sql;

--to run the script immediately, use the following command:
--EXECUTE sp_executesql @sql;
GO

다음을 통해 공유