SET NOCOUNT ON
USE SqlCredit
GO

IF EXISTS (SELECT * FROM sys.tables WHERE [name] = 'LimitTest1')
	DROP TABLE dbo.LimitTest1
GO
IF EXISTS (SELECT * FROM sys.tables WHERE [name] = 'LimitTest2')
	DROP TABLE dbo.LimitTest2
GO


---- =================================================================================
---- Table
---- =================================================================================
--PRINT 'Create Table LimitTest1'
--CREATE TABLE dbo.LimitTest1 (
--	ColID			int			IDENTITY	PRIMARY KEY,
--	ColInt01		int,
--	ColInt02		int,
--	ColInt03		int,
--	ColInt04		int,
--	ColInt05		int,
--	ColInt06		int,
--	ColInt07		int,
--	ColInt08		int,
--	ColInt09		int,
--	ColInt10		int,
--	ColInt11		int,
--	ColInt12		int,
--	ColInt13		int,
--	ColInt14		int,
--	ColInt15		int,
--	ColInt16		int,
--	ColInt17		int,
--	ColAscii900		varchar(900),
--	ColAscii901		varchar(901),
--	ColUnicode450	nvarchar(450),
--	ColUnicode451	nvarchar(451),
--	ColAsciiMax		varchar(MAX),
--	ColUnicodeMax	nvarchar(MAX)
--)
--GO
--
---- =================================================================================
---- 16-Column Limit Tests
---- =================================================================================
--PRINT 'Create Index IX_LimitTest1_Valid16Column'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_Valid16Column
--ON dbo.LimitTest1 (
--	ColInt01,
--	ColInt02,
--	ColInt03,
--	ColInt04,
--	ColInt05,
--	ColInt06,
--	ColInt07,
--	ColInt08,
--	ColInt09,
--	ColInt10,
--	ColInt11,
--	ColInt12,
--	ColInt13,
--	ColInt14,
--	ColInt15,
--	ColInt16
--)
--GO
--
--PRINT 'Create Index IX_LimitTest1_Fail17Column'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_Fail17Column
--ON dbo.LimitTest1 (
--	ColInt01,
--	ColInt02,
--	ColInt03,
--	ColInt04,
--	ColInt05,
--	ColInt06,
--	ColInt07,
--	ColInt08,
--	ColInt09,
--	ColInt10,
--	ColInt11,
--	ColInt12,
--	ColInt13,
--	ColInt14,
--	ColInt15,
--	ColInt16,
--	ColInt17
--)
--GO
---- Message: The index 'IX_LimitTest1_Fail17Column' on table 'dbo.LimitTest1' has 17 column names in index key list. The maximum limit for index or statistics key column list is 16.
--
---- =================================================================================
---- 900-byte Limit Tests - ASCII
---- =================================================================================
--PRINT 'Create Index IX_LimitTest1_ValidAscii900'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_ValidAscii900
--ON dbo.LimitTest1 (
--	ColAscii900
--)
--GO
--
--PRINT 'Create Index IX_LimitTest1_WarningAscii901'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_WarningAscii901
--ON dbo.LimitTest1 (
--	ColAscii901
--)
--GO
---- Message: Warning! The maximum key length is 900 bytes. The index 'IX_LimitTest1_ValidAscii901' has maximum length of 901 bytes. For some combination of large values, the insert/update operation will fail.
--
--PRINT 'Create Index IX_LimitTest1_WarningAsciiMax'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_FailAsciiMax
--ON dbo.LimitTest1 (
--	ColAsciiMax
--)
--GO
---- Message: Column 'ColAsciiMax' in table 'dbo.LimitTest1' is of a type that is invalid for use as a key column in an index.
--
---- =================================================================================
---- INSERTs - ASCII
---- =================================================================================
--PRINT 'Insert a 900-character string into the indexed column ColAscii900'
--INSERT INTO dbo.LimitTest1 (ColAscii900) SELECT REPLICATE('x', 900)
--GO
--PRINT 'Insert a 901-character string into the indexed column ColAscii900'
--INSERT INTO dbo.LimitTest1 (ColAscii900) SELECT REPLICATE('x', 901)
--GO
---- Message: String or binary data would be truncated.
---- Notes: String is truncated but INSERT operation succeeds.
--
--PRINT 'Insert a 900-character string into the indexed column ColAscii901'
--INSERT INTO dbo.LimitTest1 (ColAscii901) SELECT REPLICATE('x', 900)
--GO
--PRINT 'Insert a 901-character string into the indexed column ColAscii901'
--INSERT INTO dbo.LimitTest1 (ColAscii901) SELECT REPLICATE('x', 901)
--GO
---- Message: Operation failed. The index entry of length 901 bytes for the index 'IX_LimitTest1_ValidAscii901' exceeds the maximum length of 900 bytes.
---- Notes: INSERT operation fails.
--
---- =================================================================================
---- 900-byte Limit Tests - UNICODE
---- =================================================================================
--PRINT 'Create Index IX_LimitTest1_ValidUnicode450'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_ValidUnicode450
--ON dbo.LimitTest1 (
--	ColUnicode450
--)
--GO
--
--PRINT 'Create Index IX_LimitTest1_WarningUnicode451'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_WarningUnicode451
--ON dbo.LimitTest1 (
--	ColUnicode451
--)
--GO
---- Message: Warning! The maximum key length is 900 bytes. The index 'IX_LimitTest1_WarningUnicode451' has maximum length of 902 bytes. For some combination of large values, the insert/update operation will fail.
--
--PRINT 'Create Index IX_LimitTest1_FailUnicodeMax'
--CREATE NONCLUSTERED INDEX IX_LimitTest1_FailUnicodeMax
--ON dbo.LimitTest1 (
--	ColUnicodeMax
--)
--GO
---- Message: Column 'ColUnicodeMax' in table 'dbo.LimitTest1' is of a type that is invalid for use as a key column in an index.
--
---- =================================================================================
---- INSERTs - UNICODE
---- =================================================================================
--PRINT 'Insert a 450-character string into the indexed column ColUnicode450'
--INSERT INTO dbo.LimitTest1 (ColUnicode450) SELECT REPLICATE(N'x', 450)
--GO
--PRINT 'Insert a 451-character string into the indexed column ColUnicode450'
--INSERT INTO dbo.LimitTest1 (ColUnicode450) SELECT REPLICATE(N'x', 451)
--GO
---- Message: String or binary data would be truncated.
---- Notes: String is truncated but INSERT operation succeeds.
--
--PRINT 'Insert a 450-character string into the indexed column ColUnicode451'
--INSERT INTO dbo.LimitTest1 (ColUnicode451) SELECT REPLICATE(N'x', 450)
--GO
--PRINT 'Insert a 451-character string into the indexed column ColUnicode451'
--INSERT INTO dbo.LimitTest1 (ColUnicode451) SELECT REPLICATE(N'x', 451)
--GO
---- Message: Operation failed. The index entry of length 902 bytes for the index 'IX_LimitTest1_WarningUnicode451' exceeds the maximum length of 900 bytes.
---- Notes: INSERT operation fails.
--
---- =================================================================================
---- Records
---- =================================================================================
--SELECT ColID, ColAscii900, ColAscii901
--FROM dbo.LimitTest1
--GO
--SELECT ColID, ColUnicode450, ColUnicode451
--FROM dbo.LimitTest1
--GO

-- =================================================================================
-- =================================================================================
PRINT '    INCLUDE'
-- =================================================================================
-- =================================================================================

-- =================================================================================
-- Table
-- =================================================================================
PRINT 'Create Table LimitTest2 (schema matches LimitTest1)'
CREATE TABLE dbo.LimitTest2 (
	ColID			int			IDENTITY	PRIMARY KEY,
	ColInt01		int,
	ColInt02		int,
	ColInt03		int,
	ColInt04		int,
	ColInt05		int,
	ColInt06		int,
	ColInt07		int,
	ColInt08		int,
	ColInt09		int,
	ColInt10		int,
	ColInt11		int,
	ColInt12		int,
	ColInt13		int,
	ColInt14		int,
	ColInt15		int,
	ColInt16		int,
	ColInt17		int,
	ColAscii900		varchar(900),
	ColAscii901		varchar(901),
	ColUnicode450	nvarchar(450),
	ColUnicode451	nvarchar(451),
	ColAsciiMax		varchar(MAX),
	ColUnicodeMax	nvarchar(MAX)
)
GO

-- =================================================================================
-- Indexes with INCLUDEs
-- =================================================================================
PRINT 'Create Index IX_LimitTest2_Include'
CREATE NONCLUSTERED INDEX IX_LimitTest2_Include
ON dbo.LimitTest2 (
	ColID
)
INCLUDE (
	ColInt01,		-- int
	ColInt02,		-- int
	ColInt03,		-- int
	ColInt04,		-- int
	ColInt05,		-- int
	ColInt06,		-- int
	ColInt07,		-- int
	ColInt08,		-- int
	ColInt09,		-- int
	ColInt10,		-- int
	ColInt11,		-- int
	ColInt12,		-- int
	ColInt13,		-- int
	ColInt14,		-- int
	ColInt15,		-- int
	ColInt16,		-- int
	ColInt17,		-- int
	ColAscii900,	-- varchar(900)
	ColAscii901,	-- varchar(901)
	ColUnicode450,	-- nvarchar(450)
	ColUnicode451,	-- nvarchar(451)
	ColAsciiMax,	-- varchar(MAX)
	ColUnicodeMax	-- nvarchar(MAX)
)
GO

PRINT 'Insert ints into all int columns,'
PRINT 'max-length strings into 900/901/451/451-character columns,'
PRINT 'and 1,000,000-character strings into the (MAX) columns'
INSERT INTO dbo.LimitTest2 (
	ColInt01,		-- int
	ColInt02,		-- int
	ColInt03,		-- int
	ColInt04,		-- int
	ColInt05,		-- int
	ColInt06,		-- int
	ColInt07,		-- int
	ColInt08,		-- int
	ColInt09,		-- int
	ColInt10,		-- int
	ColInt11,		-- int
	ColInt12,		-- int
	ColInt13,		-- int
	ColInt14,		-- int
	ColInt15,		-- int
	ColInt16,		-- int
	ColInt17,		-- int
	ColAscii900,	-- varchar(900)
	ColAscii901,	-- varchar(901)
	ColUnicode450,	-- nvarchar(450)
	ColUnicode451,	-- nvarchar(451)
	ColAsciiMax,	-- varchar(MAX)
	ColUnicodeMax	-- nvarchar(MAX)
)
SELECT
	1,
	2,
	3,
	4,
	5,
	6,
	7,
	8,
	9,
	10,
	11,
	12,
	13,
	14,
	15,
	16,
	17,
	REPLICATE('x',    900),
	REPLICATE('x',    901),
	REPLICATE(N'x',   450),
	REPLICATE(N'x',   451),
	'',
	N''
GO

DECLARE @i	int
SET @i = 0
WHILE @i < 1 -- 250
BEGIN
	SET @i = @i + 1
	UPDATE t
	SET
		ColAsciiMax		= ColAsciiMax   + REPLICATE('x',  4000),
		ColUnicodeMax	= ColUnicodeMax + REPLICATE(N'x', 4000)
	FROM dbo.LimitTest2 AS t
	WHERE ColID = 1
END
GO

SELECT *
FROM dbo.LimitTest2
GO

SELECT
	LEN(ColAsciiMax)	AS LenColAsciiMax,
	LEN(ColUnicodeMax)	AS LenColUnicodeMax
FROM dbo.LimitTest2
--WHERE ColID = 1
GO