Performance issue in Sql Server
Short answer: No, you need more indexes.

If you're going to create a significant number of queries involving [brand] and [colour], you should index both columns:

CREATE INDEX products_colour_idx ON dbo.products (colour);  
CREATE INDEX products_brand_idx ON dbo.products (brand);  


If you mostly query using both columns, and there are not queries involving colour that do not also involve a brand, a multi-column index would be better, because by using just one index, the database can retrieve all the relevant rows. You would create it this way:

CREATE INDEX products_brand_colours_idx ON dbo.products (brand, colour);  


The order of the columns [(brand, colour) vs. (colour, brand)] should be normally chosen in such a way that the most selective one comes first (basically: the one with more different values, first).

If you happen to have a significant number of queries with brand, colour and both brand and colour, you should have at least the two first indexes; if you need the fastest possible speed, have one index for (brand, colour) and another one for (colour). You don't need an index on purpose for (brand), the (brand, colour) one is good for looking for brands.



[product_code] will be indexed by the database automatically to enforce the UNIQUE constraint on it. See Create Unique Constraints and Unique Constraints and Unique Indexes. You don't need to create an index explicitly even if query_2 is frequent.