How to formulate index_name in SQL?

Question

I am trying to create an index on one of my tables with an accurate label. Here is how I am trying 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])

After asking over 100 questions you must know how to format code? — Martin Smith
– Martin Smith, Commented May 11, 2011 at 16:05
I know how to format code. But I don't know how to label indexes according to my description above. — salvationishere
– salvationishere, Commented May 11, 2011 at 16:11
If you know how to format code please do so so your question is more readable to potential answerers. — Martin Smith
– Martin Smith, Commented May 11, 2011 at 16:14
@Martin, I see what you are saying now! Thanks! I updated my description now so that everything is readable! — salvationishere
– salvationishere, Commented May 11, 2011 at 16:24

M.R. · Accepted Answer · 2011-05-12 03:30:50Z

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

Collectives™ on Stack Overflow

How to formulate index_name in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related