SQL Server Single composite index Vs Multiple index for a single table

Question

See my table structure

CREATE TABLE [dbo].[tblMaster](  
[ID] [int] IDENTITY(1,1) NOT NULL,  
[TickerID] [varchar](10) NULL,  
[ParentID] [int] NULL,  
[Type] [varchar](30) NULL,  
[SectionID] [int] NULL,  
[LineItemID] [int] NULL,  
[BMID] [varchar](max) NULL,  
[DisplayName] [nvarchar](max) NULL,  
[FontName] [varchar](max) NULL,  
[FontStyle] [varchar](max) NULL,  
[FontSize] [varchar](max) NULL,  
[UnderLine] [varchar](max) NULL,  
[StrikeThrough] [varchar](max) NULL,  
[FGColor] [varchar](max) NULL,  
[BGColor] [varchar](max) NULL,  
[Indent] [int] NULL,  
[Box] [char](1) NULL,  
[HeadingSubHeading] [varchar](1) NULL,  
[CurrencySign] [nvarchar](max) NULL,  
[CurrencyCode] [nvarchar](max) NULL,  
[AllowedDecimalPlace] [int] NULL,  
[AllowPercentageSign] [varchar](5) NULL,  
[AllowComma] [varchar](5) NULL,  
[LineItemComments] [nvarchar](max) NULL,  
[ColOrder] [int] NULL,  
[InsertedOn] [datetime] NULL,  
[UserID] [varchar](20) NULL,  
[Earning] [varchar](50) NULL,  
[PrePost] [varchar](6) NULL,  
[Version] [varchar](50) NULL,  
[RowNumber] [int] NULL,  
[IsWrapText] [char](1) NULL,  
[RowHeight] [decimal](16, 2) NOT NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  
  
ALTER TABLE [dbo].[tblMaster] ADD  CONSTRAINT [DF_tblCSM_Detail_Indent]  DEFAULT ((0)) FOR [Indent]  
GO  
  
ALTER TABLE [dbo].[tblMaster] ADD  CONSTRAINT [DF_tblCSM_Detail_Box]  DEFAULT ('N') FOR [Box]  
GO  
  
ALTER TABLE [dbo].[tblMaster] ADD  CONSTRAINT [DF_tblBBViewBogey_Detail_InsertedOn]  DEFAULT (getdate()) FOR [InsertedOn]  
GO  
  
ALTER TABLE [dbo].[tblMaster] ADD  DEFAULT ((0)) FOR [RowHeight]  
GO  

I saw our DBA created a composite index. here it is

CREATE NONCLUSTERED INDEX [tblMaster_TickerID_SectionID] ON [dbo].[tblMaster]  
(  
[TickerID] ASC,  
[SectionID] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  

GO

Our DBA could create two non cluster index for two column TickerID & SectionID but he created single composite index.

1. please tell me what is the difference between single composite index and multiple indexes on few columns ?
2. what would be difference in terms of performance impact between single composite index and multiple indexes on few columns for a table ?
3. why DBA created NONCLUSTERED index instead of CLUSTERED index ?
4. what would be performance if i drop the above index and create one NONCLUSTERED index on TickerID column and include SectionID as a Cover Index ?

please guide me with good Knowledges that how to know on which columns should be included in index?
suppose i got a table which has many fields and i told to create index. then how do i know on which column i should create indexs....please guide me.

Thanks

Answer 1

1) please tell me what is the difference between single composite index and multiple indexes on few columns ?

Say that for a TickerID there can be 10000 SectionIDs and vice versa.

Say then that you have this query:

 SELECT * FROM tbl WHERE TIckerID = 9999 AND SectionID = 7777  

With a composite index, the matching rows can be found quickly, because for a given TickerID, the rows are sorted on SectionID. But with a separate single-column indexes, the SQL Server will have to read all 10000 rows for TickerID 9999 to find the rows with SectionID = 7777, because the rows will be in no particular order. Furthermore, it has to retrieve the SectionID from the data pages. The same applies, if SQL Server would use the index on SectionID instead.

You may ask: can't it use both indexes? Yes, it can, but it would still have to work with two sets of 10000 rows and try to match them with each other. Whereas with a composite index, it only has to read a handful of rows.

what would be difference in terms of performance impact between single composite index and multiple indexes on few columns for a table ?

That would depend on the queries that goes against the table. For the query above, performance would benefit by having a composite index rather than having two individual indexes.

On the other hand, if you only have the composite index, this query would not perform well:

SELECT * FROM tbl WHERE SectionID = 7777

Because the index is sorted on TickerID first, SQL Server needs to read the index in entirety to find the rows with the matching SectionIDs.

The optimal solution, if you have these queries is obviously to have one composite index on (TickerID, SectionID) and one index on (SectionID) or (SectionID, TickerID).

why DBA created NONCLUSTERED index instead of CLUSTERED index ?

You need to ask the DBA. You can only have one clustered index on a table, so you need to select judiciously which index you make the clustered index. Maybe the DBA thought it was better to have the clustered index on something else.

4) what would be performance if i drop the above index and create one NONCLUSTERED index on TickerID column and include SectionID as a Cover Index ?

Again, this means that for one TickerID, the SectionIDs would come in no particular query, and for the for the query

SELECT * FROM tbl WHERE TIckerID = 9999 AND SectionID = 7777

SQL Server needs to reads all rows for TIckerID = 9999 to find the few rows with SectionID = 7777. But at least it would not have to go to the data pages to locate the rows. But since the query has SELECT *, the index would still not be a covering index.

.........................................

Now, for some reason I get the feeling that you have been asking these questions before. But maybe the penny hasn't dropped.

Therefore, I will give you a task. First go to https://www.sommarskog.se/dynsearch-2008/Northwind.sql.txt and download the script to build the small Northwind database. Your task is to find how many orders that have the combination CustomerID = 'BERGS' and EmployeeID = 8. Not by running a simple count query, but by counting manually.

First do this by running these two queries:

SELECT CustomerID, OrderID FROM Orders ORDER BY CustomerID  
SELECT EmployeeID, OrderID FROM Orders ORDER BY EmployeeID  

Look at the results from the two queries and try to find the answer.

Next run this query

 SELECT CustomerID, EmployeeID, OrderID FROM Orders ORDER BY CustomerID, EmployeeID  

And again, determine the number of rows by looking at the output.

Was there any difference in how hard/easy it was to count the rows in the rows in the two cases? If there was, why was there a difference?