How to formulate index_name in SQL?

I am trying to create an index on one of my tables with an accurate label. Here is how I am t开发者_如何学运维rying it...expecting "sysname" to resolve to the column or table name. But after I run this command and view it in the Object Explorer, it is listed as

"[<Name of Missing Index, sysname + '_prod',>]".

How do u define index_names in a better descriptive fashion? (I am trying to add the extension "_prod" to the index_name, since INDEX of index_name already exists).

USE [AMDMetrics]
GO


CREATE NONCLUSTERED INDEX 

 [<Name of Missing Index, sysname + '_prod',>]

  ON [SMARTSOLVE].[V_CXP_CUSTOMER_PXP] ([QXP_UDF_STRING_8], [QXP_REPORT_DATE], 
[QXP_XRS_DESCRIPTION]) 
  INCLUDE ([QXP_ID], [QXP_EXCEPTION_NO], [QXP_BASE_EXCEPTION], [QXP_CATEGORY], 
[QXP_OCCURENCE_DATE], [QXP_COORD_ID], [QXP_SHORT_DESC], [QXP_ROOT_CAUSE], 
[QXP_DESCRIPTION], [QXP_QEI_ID], [PXP_LOT_NUMBER], [CXP_ID], [CXP_AWARE_DATE], 
[QXP_XSV_CODE], [QXP_COORD_NAME], [PXP_PRODUCT_CODE], [PXP_PRODUCT_NAME], 
[QXP_ORU_NAME], [QXP_RESOLUTION_DESC], [QXP_CLOSED_DATE], [CXP_CLIENT_CODE], 
[CXP_CLIENT_NAME])     

---

I'm not 100% sure what you are trying to do, but it seems like you are trying to find a way to properly name your index (or find a good naming convention). Conventions are best when they are easy to follow, and make sense to people without having to explain it to them. A lot of different conventions fit this MO, but the one that is most common is this:

Index Type                          Prefix    Complete Index name
-------------------------------------------------------------------
Index (not unique, non clustered)   IDX_      IDX_<name>_<column>  
Index (unique, non clustered)       UDX_      UDX_<name>_<column>  
Index (not unique, clustered)       CIX_      CIX_<name>_<column>  
Index (unique, clustered)           CUX_      CUX_<name>_<column> 

Although on a different note, I have to question why you have so many columns in your INCLUDE list....without knowing the size of those columns, there are some drawbacks to adding so many columns:

Avoid adding unnecessary columns. Adding too many index columns, 
key or nonkey, can have the following performance implications: 

- Fewer index rows will fit on a page. This could create I/O increases 
and reduced cache efficiency.

- More disk space will be required to store the index. In particular, 
adding varchar(max), nvarchar(max), varbinary(max), or xml data types 
as nonkey index columns may significantly increase disk space requirements. 
This is because the column values are copied into the index leaf level. 
Therefore, they reside in both the index and the base table.

- Index maintenance may increase the time that it takes to perform modifications, 
inserts, updates, or deletes, to the underlying table or indexed view.

You will have to determine whether the gains in query performance outweigh 
the affect to performance during data modification and in additional disk 
space requirements.

From here: http://msdn.microsoft.com/en-us/library/ms190806.aspx