none
Database Index

    Question

  • Hi 

    I am design a new database. I would like to know which column to select for index.

    I know index is good for selection but not so good for insert, update etc....

    furthermore, I know cluster and non cluster index

    any advice

    Friday, April 27, 2018 12:20 AM

All replies

  • After you've create primary key and unique constraints (which are implemented as unique indexes), candidate columns for other indexes are those specified in join and where clauses. The choice of the best clustered index depends on how the table is queried. The primary key index is often chosen for the clustered index when queries often select most or all columns by the primary key.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, April 27, 2018 1:36 AM
    Moderator
  • Hi 

    Thanks for response. what we have to keep in mind when we create index .

    I am new to indexing. what should I do if I have to do indexing. what to select and why. (Is there any rules)

    Also, I don't understand what do you mean by this "candidate columns for other indexes are those specified in join and where clauses"

    Friday, April 27, 2018 3:09 AM
  • The remainder of your indexing depends on how you access the data from your applications.  You create indexes to satisfy your application queries - not for some hypothetical design pattern.

    You could simply connect up your application and wait to see what the instance suggests in the way of missing indexes to get a starter - once you see the choices then you might be able to work out more of your own.

    It is also not strictly true that indexes are bad for INSERT/UPDATE/DELETE.  Your query still needs to find the record to update or delete for example and if it does much more work to find the record then the extra writes for the indexes are irrelevant.


    Martin Cairney SQL Server MVP

    Friday, April 27, 2018 4:26 AM
  • https://www.codeproject.com/articles/359654/important-database-designing-rules-which-i-fo

    https://support.office.com/en-us/article/Create-and-use-an-index-to-improve-performance-0A8E2AA6-735C-4C3A-9DDA-38C6C4F1A0CE


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Friday, April 27, 2018 4:34 AM
  • In addition,  you  monitor how frequently your indexes are selected/updated/scanned( Thanks to Greg)

    create procedure usp_helpindexusagestats
      @tablename varchar(1000)
    as

    set nocount on

    if object_id('tempdb..#helpindex') > 0 drop table #helpindex

    create table #helpindex (
       index_name varchar (1000) not null primary key
     , index_description varchar (1000) null
     , index_keys varchar (1000) null
    )

    insert #helpindex
    exec sp_helpindex @tablename

    alter table #helpindex add inccols varchar(1000) null

    declare cr cursor for
    select si.name, sc.name
    from sysobjects so
    join sysindexes si on so.id = si.id
    join sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id
    join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id
    where so.xtype = 'U'
      and so.name = @tablename
      and ic.is_included_column = 1
    order by si.name, ic.index_column_id

    declare @siname varchar(1000), @scname varchar(1000)

    open cr

    fetch next from cr into @siname, @scname

    while @@fetch_status = 0
     begin

      update #helpindex set inccols = isnull(inccols , '') + @scname + ', ' where index_name = @siname

      fetch next from cr into @siname, @scname
     end

    update #helpindex set inccols = left(inccols, datalength(inccols) - 2)
    where right(inccols, 2) = ', '

    close cr
    deallocate cr

    select hi.index_name, hi.index_description, hi.index_keys, hi.inccols as included_columns, ius.index_id, user_seeks, user_scans, user_lookups, user_updates
    , last_user_seek, last_user_scan, last_user_lookup
    from sys.dm_db_index_usage_stats ius
    join sysindexes si on ius.object_id = si.id and ius.index_id = si.indid
    join sysobjects so on si.id = so.id
    join #helpindex hi on si.name = hi.index_name collate database_default
    where ius.database_id = db_id()
      and so.name = @tablename

    drop table #helpindex
    go

    ----Usage
    exec usp_helpindexusagestats 'Transport'

      

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, April 29, 2018 6:35 AM
    Moderator