question

@chetan If there is nvarchar type column participating in the index then reducing the size to 450 will still give you the same problem of course. Just do the math: nvarchar(450) takes up to 900 bytes (2 bytes per each character), and therefore, if you have another column participating in the index, say, some integer column, then the size of the index is 900 + 4 = 904. You should probably evaluate whether you actually need such a seemingly useless index (the character-based column is just too wide). If you do then you might consider this column as an included column, which does not have a 900 bytes limitation. Please note that 900 bytes is the limitation for a clustered index. Non-clustered index limit is 1700 bytes. This kinda implies that you are trying to create a clustered index (?!?) which includes nvarchar(450) column as part of it's key. This attempt is ill advised.

@chetan If you reduced the size of the column then there is ***no way to get the warning*** unless you are reducing the column size and dropping the index on one table and then try to create the index on the different table (by accident, due to the lack of the proper table name usage when creating index). This can easily happen if the sloppy practice of omitting the schema and spelling out just the table name is used. Suppose your login was created with default configuration, meaning that instead of assigning you a default schema dbo, your login was assigned the default schema created after your login name. Now, say you create table failing to correctly specify the schema name at create time (something like **create table companyinfo** -- etc, this should NEVER happen, the table name should have been schema qualified). Then you create the table properly, try to add the index and receive this error about 904 bytes. After that you reduce the size of the column on one of these 2 tables and try to create the index on another one. This will explain the source of the warning. Run **select \* from sys.tables where name = companyinfo**, see how many tables with the same name you have.

i tried by doing that.i dleted the index.even after dropping the index,it is giving me same error

@Oleg I believe 1700 bytes max limit started with SQL 2016+? My gut feeling is, it is a non-clustered index especially when I see it starts with "IX_" :) @chetan I guess @Oleg has pretty much explained the issue and please do not ignore the invaluable advice for not using this column as the index key.

@Usman Butt Yes, you are correct, this change was introduced in SQL Server 2016, I should have mentioned it. It looks like they increased the columns to 32 from 16, retained 900 limit for clustered, and increased it to 1,700 for nonclustered indexes. Here is the quote from [BOL][1]

Up to 32 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. The limits are 16 columns and 900 bytes for versions before SQL Database V12 and SQL Server 2016

This number (900 for clustered index) is definitely the limit, not the recommendation. It would be totally insane to actually have a clustered index this wide, particularly if the table has some other columns and need to have some other indexes. Even with nonclustered index, the wide columns are probably going to be better of as included, not as part of the key. [1]: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql