Create index with a commentary

I would like to add some comments to my indices. So I added some additional information to the index creation script, expecting to see this comment again as soon as I open the script again. But it was gone.

Sample:

CREATE NONCLUSTERED INDEX test3
/* 
 Created on ...
 Reason: ... 
 Improvement: 95 - 99,8% ...
*/
ON [dbo].[T] 
(   
/* equal */
[ID], [Nr], [D], 
/* inequal */
[S], [OD])
INCLUDE 
([A], [C] )

After selection SCRIPT => CREATE SCRIPT in SSMS all comments are gone.

Does anyone know a solution to keep them alive?

There is no way to store text in the definition of an index.

You can use Extended Properties to store descriptive information, however it won't be automatically displayed when you "script" the object.

USE tempdb;

CREATE TABLE dbo.t1
(
    ID INT NOT NULL
);

CREATE INDEX IX_t1
ON dbo.t1(ID);

/*
    This will add a comment named "Comments" to the IX_t1 index
*/
EXEC sys.sp_addextendedproperty @name=N'Comments'
    , @value=N'This is a test index to store comments'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N't1'
    , @level2type = N'INDEX'
    , @level2name = N'IX_t1';

/*
    This displays all comments associated with indices in the current database
*/
SELECT SchemaName = s.name
    , ObjectName = o.name
    , IndexName = i.name
    , PropertyName = xp.name
    , PropertyValue = xp.value
FROM sys.schemas s
    INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    LEFT JOIN sys.indexes i ON o.object_id = i.object_id
CROSS APPLY sys.fn_listextendedproperty(NULL, N'SCHEMA', s.name, N'TABLE',
    o.name, CASE WHEN i.name IS NULL THEN NULL ELSE N'INDEX' END, i.name) xp
ORDER BY s.name
    , o.name
    , xp.name;

Results of above query:

/*
    This provides the ability to delete Extended Properties
*/
EXEC sys.sp_dropextendedproperty @name = N'Comments'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N't1'
    , @level2type = N'INDEX'
    , @level2name = N'IX_t1';

/*
    Clean up my test bed
*/
DROP TABLE dbo.t1;

The best-practices solution for this problem is to store object definitions inside a version-control system.

StackExchange Database Administrators Q#118766, answer score: 8