SQL Server - Warning! The maximum key length is 900 bytes……

Perhaps, if someone from the team can write more on covering index. That would be just great to understand this content in greater detail.

Hi Raunak,

Actually here i just want to explain the warning, in my next blog i will explain Covering Indexes. 

CREATE TABLE [dbo].[Cities]
(
   [CityId] [int] IDENTITY, 
   [CityName] [varchar](400)
)

CREATE TABLE [dbo].[xtTest]
(
   [id] [int] NOT NULL, 
   [name] [varchar](50) NOT NULL, 
   [fkCityId] [int] NOT NULL, 
   [description] [varchar](500) NOT NULL, 
   CONSTRAINT [FK_dbo_xtTest___fkCityId] FOREIGN KEY ([fkCityId]) REFERENCES [dbo].[Cities]([CityId])
)

CREATE VIEW [dbo].[vxtText]
(
   SELECT
      [xt].[id], 
      [xt].[name], 
      [c].[CityName] as [City], 
      [xt].[description]
   FROM
      [dbo].[xtTest] [xt] INNER JOIN [dbo].[Cities] [c] ON [c].[CityId] = [xt].[fkCityId]
)

How about teaching basic normalization?

By breaking out City into a separate table, you gain the following benefits:

1. Data Quality.  If a city doesn't exist in [dbo].[Cities], it can't be referenced in [dbo].[xtTest].  This way you aren't storing typos and mistakes and wind up with several misspellings of the same city.
2. Storage Efficiency.  A city name takes up space exactly once.  After that, each reference to the City takes up the space required to store an [int] (4 bytes)
3. Index efficience.  Any time a column refering to a city is referenced, the index entry takes up 4 bytes, not the length of the name of the city
4. Processing Efficiency.  An [int] is a native processor type, it doesn't require any pre-processing to be evaluated.  String types must first be cast to a byte array of integers; something that happens fairly quickly, but not as fast as a processor evaluating an integer directly.

You need to do just a few more things to make this as fast as a native table:

Create an index on the view.  That gives the view its own clustered index.  All values are read from the clustered index, just like a native table.

Create an INSTEAD OF trigger on the view that redirects INSERT, UPDATE and DELETE statements on the view.  This way the view looks and acts exactly the same as the table you originally described.  This is easy to generate directly out of system tables like sys.foreign_keys and sys.foreign_key_columns.

The warning is exactly that... a warning that you have likely done something wrong.  In this case, it was the table design, which led to the requirement of an index which exceeds SQL Server's ability to handle.

When you cover INCLUDES be sure to cover the storage impact of having a table filled with varchar columns and an index that includes all of them.  While the INCLUDEd columns are not counted towards the 900 byte limit, the INCLUDEd columns are still included in the index storage... which means you are storing the values twice.  You've now doubled your storage requirement.