What cases I can use Index on table ?

ahmed salah 3,216 Reputation points
2021-02-09T22:42:24.96+00:00

I work on SQL server 2012 I face issue I can't know what cases I use index on it
i mean non cluster index

so when I use non luster index and what criteria that require create index

are create non cluster index to table have huge data will affect on data or not affect on data

with another meaning create non cluster index to table have huge data can delete or remove data
or not
create index must be before fill data on table or after ?
so please answer me

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,068 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Ronen Ariely 15,101 Reputation points
    2021-02-10T01:34:53.757+00:00

    Good day,

    I can't know what cases I use index on it
    i mean non cluster index

    Not clear to me if you ask "when to use NONCLUSTERED INDEX" or you ask "how to know if the server use a NONCLUSTERED INDEX which you have"

    The answer for the first one is that you should understand what is index and how they work and you should have the table structure and the relevant queries - we do not have the information since you did not provide it.

    The answer to the second question is that you can check the Actual Execution Plan. This will tell you what exactly the server used. This document will help you to do the task:
    https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15

    so when I use non luster index and what criteria that require create index

    Again, your question is not clear to me. Please try to rephrase it.

    What do you mean by criteria require to create the index?!? Don't you mean to ask when the index is useful?

    are create non cluster index to table have huge data will affect on data or not affect on data

    If your question is: Does NONCLUSTERED INDEX have a big impact on tables with a lot of data

    Then the answer is that it depend on what you do with the data. For example, NONCLUSTERED INDEX can improve the performance of SELECT queries dramatically (when it fits) but at the same time it can reduce the performance on INSERT queries dramatically.

    with another meaning create non cluster index to table have huge data can delete or remove data or not

    Please try to rephrase it.

    create index must be before fill data on table or after ?

    If you have a lot of data that you want to insert to a new table, then usually it is best to first fill the table and only after this to create the indexes. This way the server will be able to sort the data once when creating the index and it will be able to store the data without the need to split the pages where the data is stored when a new row is inserted.

    Note: You can also disable the index. Disabling a NONCLUSTERED INDEX will deallocate the index pages, and when you enable it the index will be re-build.

    -- This way you disable index  
    ALTER INDEX MyIndex ON MyTable DISABLE;    
    -- and to re-enable the index you need to re-build it    
    ALTER INDEX MyIndex on MyTable REBUILD  
    
    0 comments No comments

  2. EchoLiu-MSFT 14,576 Reputation points
    2021-02-10T06:54:25.223+00:00

    Hi @ahmed salah ,

    Index is used only when the first column in the index is related to the query.Non-clustered index will take up space.Indexes have as many negative effects as they have positive ones, create only needed indexes.

    Clustered Index usually be faster than nonclustered Index.
    Querying can benefit from index ,but insert/update/delete probably cannot.

    If your questions above are all about non-clustered indexes, then my answer is as follows:

    The storage in the non-clustered index is not the real data, but the location information of the data. So only after inserting the data, it makes sense to create a nonclustered index.

    Non-clustered index does not change the physical storage state of the table, so creating a non-clustered index has no effect on the data in the table, but the clustered index will change the physical storage order of the table, because after the clustered index is created, the table will be stored in the order of the clustered index.

    Clustered index or nonclustered index:

    Clustered index :
    primary key column
    Columns are often grouped and sorted
    Returns a range of values

    nonclustered index:
    Conditional column after where

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.