none
What should be the naming convention for indexes? RRS feed

  • Dotaz

  • hi,

        I have created some sps and functions, and named them like uspgetreportno,udfgetreportno respectivily.

    please tel me some prevelent prefix for clustered index and non clustered index.

    yours sincerley


    čtvrtek 16. dubna 2015 15:57

Odpovědi

  • I use 

    PK_ for primary keys

    UK_ for unique keys

    IX_ for non clustered non unique indexes

    UX_ for unique indexes

    Also recommended naming conventions

    • PKC_ Primary Key, Clustered
    • PKNC_ Primary Key, Non Clusterd
    • NCAK_ Non Clustered, Unique
    • CAK_ Clustered, Unique
    • NC_ Non Clustered


    Hope this Helps

    Thanks,

    Nihar

    čtvrtek 16. dubna 2015 16:03
  • I would like to draw your attention on an answer from stack overflow about Naming Convention for Index in SQL Server. 

    Generally: 

    • PK_ for primary keys
    • CLIX_ for Clustered Index (non PK)
    • UK_ for unique keys
    • IX_ for non clustered non unique indexes
    • UX_ for unique indexes

    • PK_ Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index. 
    • CLIX_ In my universe, every (non-PK) Clustered Index name is prefixed with CLIX (forCLustered IndeX). Personally, I typically don’t design too many explicitly Unique clustered indexes, but if I do I commonly struggle with whether to call them UCLIX_ or just CLIX_ or not. To me, UCLIX_ looks lame and doesn’t ‘sound cool’ – whereas CLIX has nice ‘aesthetics’. (Yes. I have major issues – but these are my guidelines and they’re what I try to impose order on my part of the universe.)
    • IX_ standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for IndeX). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes.
    • UX_ for unique index.

    Overall structure:

    [Prefix]_TableName_[Description]

    pátek 17. dubna 2015 14:09
  • In AdventureWorks index naming AK stands for Alternate Key:

    SELECT object_name(object_id), name  from sys.indexes order by object_name(object_id);
    /*
    ...
    Product	PK_Product_ProductID
    Product	AK_Product_ProductNumber
    Product	AK_Product_Name
    Product	AK_Product_rowguid
    ...
    */

    Note that definition based naming should include schema name as well to avoid conflicts.




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    pátek 17. dubna 2015 22:22
    Moderátor

Všechny reakce

  • I use 

    PK_ for primary keys

    UK_ for unique keys

    IX_ for non clustered non unique indexes

    UX_ for unique indexes

    Also recommended naming conventions

    • PKC_ Primary Key, Clustered
    • PKNC_ Primary Key, Non Clusterd
    • NCAK_ Non Clustered, Unique
    • CAK_ Clustered, Unique
    • NC_ Non Clustered


    Hope this Helps

    Thanks,

    Nihar

    čtvrtek 16. dubna 2015 16:03
  • I prefer IDX_ and IDXc_ for indices and clustered indices.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    čtvrtek 16. dubna 2015 16:39
  • I think you should follow some naming conventions/standards for your procedures and functions too.

    for example - USP_GetReportNo and udf_getReportNo.

    As for indices -  prefix_TableName_ColumnName (on which the index will be defined)

    for example -  Cluster Index -->  CX_Products_ProductID

          Non-cluster index  -->  IX_Products_Qty

                  non -cluster with include -->  IX_Products_WarehouseID_include_Qty

    Hope it helps.

    thanks!

    pátek 17. dubna 2015 2:37
  • you can use IDX for non-clustered index and CIDX for clustered index.
    pátek 17. dubna 2015 9:59
  • I would like to draw your attention on an answer from stack overflow about Naming Convention for Index in SQL Server. 

    Generally: 

    • PK_ for primary keys
    • CLIX_ for Clustered Index (non PK)
    • UK_ for unique keys
    • IX_ for non clustered non unique indexes
    • UX_ for unique indexes

    • PK_ Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index. 
    • CLIX_ In my universe, every (non-PK) Clustered Index name is prefixed with CLIX (forCLustered IndeX). Personally, I typically don’t design too many explicitly Unique clustered indexes, but if I do I commonly struggle with whether to call them UCLIX_ or just CLIX_ or not. To me, UCLIX_ looks lame and doesn’t ‘sound cool’ – whereas CLIX has nice ‘aesthetics’. (Yes. I have major issues – but these are my guidelines and they’re what I try to impose order on my part of the universe.)
    • IX_ standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for IndeX). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes.
    • UX_ for unique index.

    Overall structure:

    [Prefix]_TableName_[Description]

    pátek 17. dubna 2015 14:09

  • PK_TableName_ColumnNames                                                          for primary keys
    CLIX_TableName_ColumnNames_Inc_IncludeColumnsNames            for Clustered Index
    UK_TableName_ColumnNames_Inc_IncludeColumnsNames               for UniqueKeys
    IX_TableName_ColumnNames_Inc_IncludeColumnsNames                for Non Clustered Index
    UX_TableName_ColumnNames_Inc_IncludeColumnsNames                for Unique Index

    Thanks


    • Upravený SequelMate pátek 17. dubna 2015 14:21
    pátek 17. dubna 2015 14:17
  • In AdventureWorks index naming AK stands for Alternate Key:

    SELECT object_name(object_id), name  from sys.indexes order by object_name(object_id);
    /*
    ...
    Product	PK_Product_ProductID
    Product	AK_Product_ProductNumber
    Product	AK_Product_Name
    Product	AK_Product_rowguid
    ...
    */

    Note that definition based naming should include schema name as well to avoid conflicts.




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    pátek 17. dubna 2015 22:22
    Moderátor
  • I can add some interesting ones you may consider:

    CL_TableName - stands for Clustered index (if different from Primary Key)

    UC_TableName_ColumnName - for unique constraints

    it may be useful to actually follow some naming conventions, because you may decide to use the index name in hints, and since it is in the SQL code, it's better to see some predictable/consistent names:

    SELECT * FROM Products WITH(INDEX(CL_Products)) WHERE ProductName='Car' AND ProductPlu='13521'

    sobota 18. dubna 2015 23:11