What cases I can use Index on table ?

Question

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

Answer 1

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  

Answer 2

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.