For each of the following queries indicate whether a use of an index
would be helpful or not. If so, specify which tables and columns an index should be created on and the
best choice between a clustered or unclustered index. You can assume
that the system provides that choice:
A. SELECT * FROM Users;
B. SELECT * FROM Posters WHERE userid = 3;
C. SELECT name FROM Publisher WHERE name = ‘New York Times’;
D. SELECT userid,COUNT(*) FROM Has_read GROUP BY userid;
The Index is mainly used to speed up the performancof the queries it will do by decering the number of database datapages that have tobe visitedor scanned.in the SQL server the clustered index mainly determines the physicalorder of data in the table
CREATE INDEX INDEX.NAME ON TABLE.NAME(COLUMN);
The clustered index mainly determines the physicalorder of data in the table .As it’sa physical ordering of data any a column of given table so we can have only one clustered index on any table and by default primary keys of the table have clustered index. The clustered index can improve the performance of the data retrival.
un clustered index or non clustered index:
The un clustered index or non clustered index are logical ordering of the data of the column and it is more like a pointer to data that can have any number of non clustered index on the table.un clustered index or non clustered index can used with unique constraint on the table acting as a composite key and it should be created on colunms which is used in joins where and order by clause when we write a create index query it creates a non clustered index on the columns.
Syntax for index:
SELECT *FROM Table WITH(INDEX(Index_Name))