/* DROP TABLE [WTF_Example] DROP VIEW [NPI_View] */ CREATE TABLE [WTF_Example] ( [ID] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(), [NPI] VARCHAR(10) NOT NULL UNIQUE NONCLUSTERED, [Data] VARCHAR(80) NOT NULL ) GO CREATE VIEW [NPI_View] AS SELECT [ID], [NPI] AS [NPI_Indexed], SUBSTRING([Data], 36, 10) AS [NPI_Substring] FROM [WTF_Example] GO INSERT INTO [WTF_Example] ([NPI], [Data]) SELECT '1000000000', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1000000000aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' WHILE (SELECT COUNT(*) FROM [WTF_Example]) < POWER(2, 20) INSERT INTO [WTF_Example] ([NPI], [Data]) SELECT CAST([NPI] AS BIGINT) + (SELECT COUNT(*) FROM [WTF_Example]) ,'' FROM [WTF_Example] UPDATE [WTF_Example] SET [Data] = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' + [NPI] + 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' DECLARE @i INT, @trials INT, @dummy INT DECLARE @start DATETIME SET @trials = 1000 SET NOCOUNT ON SET @i = 0 SET @start = GETDATE() WHILE @i < @trials BEGIN SELECT @dummy = COUNT(*) FROM [NPI_View] WHERE [NPI_Indexed] = '1000947374' SET @i = @i + 1 END PRINT CAST(@trials AS VARCHAR) + ' selects by indexed NPI: ' + CAST(DATEDIFF(ms, @start, GETDATE()) AS VARCHAR) + 'ms' SET @i = 0 SET @start = GETDATE() WHILE @i < @trials BEGIN SELECT @dummy = COUNT(*) FROM [NPI_View] WHERE [NPI_Substring] = '1000947374' SET @i = @i + 1 END PRINT CAST(@trials AS VARCHAR) + ' selects by substring NPI: ' + CAST(DATEDIFF(ms, @start, GETDATE()) AS VARCHAR) + 'ms' SET NOCOUNT OFF