will non clust index requires space in sql server
will it increase the size of database
if yes than how much %
thanks

the table is ordered physically in the order of the Clustered index. Therefore, there can be only one clustered index in a table. However, there can be multiple Non Clustered indices in a table. These indeces are maintained as B-Trees by SQL Server. Therefore, Navigation to the nodes of the tree is faster than when no index is specified. This is not going to increase the size of the database.

If you have more indeces in a table, the searching is going to be faster.

Regards
Ganesh

please write the statement for creating the nonclust index on the multiple column
are u sure it will not increase the size of the database then where it store the logical table to map with the physical rows of the table
thanks

Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.
In comparison with a clustered index, which can be only one for each table, each table can have as many as 249 nonclustered indexes. However, you should consider nonclustered index creation as carefully as the clustered index, because each index take up disk space and drag on data modification.

Non-Clustered Index does not physically order the data pages in the disk so the memory required for creating a non-clustered Index is very less. The SQL Server maintains the indeces as B-Trees. In a clustered index, the leaf contains the actual data pages of the table and the data is physically stored in the logical order of the index.

A nonclustered index is analogous to an index in a textbook. The data is stored in one place, the index in another, with pointers to the storage location of the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order (which can be dictated by a clustered index). If no clustered index is created on the table, the rows are not guaranteed to be in any particular order.



You can get more information from this link.
http://msdn.microsoft.com/library/de...es_02_2248.asp

Clustered Indeces are suitable in the following cases
   Columns used in range queries
   Columns used in order by or group by queries
   Columns used in table joins
   Queries returning large result sets

Non-Clustered Indeces are suitable in the following cases
   Columns used in aggregate functions
   Foreign keys
   Queries returning small result sets.l

Regards
Ganesh

I dont think it is necessary to consider the space occupied by indeces because disk space is very cheap these days. You have to consider only the time taken to access the data. Indices will definelty help you in improving your response time. If your table is expecting only updates and inserts, it is best to have only a clustered index and that should be ideally in the primary key column.

If you table is not expcting any updates but expects only selects, then ideally you can have non-clusterd indeces on each column which are involved in the where clause.

Regards
Ganesh

I would suggest you go through the online documentation / books online for indexing. It is not that creating more indices increase the performance. It depends on the data that gets stored in that column too. IMO too many indices/ index on unwanted columns is going to affect the performance. For eg: Index on a column that contains gender(male/female) is not going to help you and is unnecesary to create index on that. Same way for a TRUE/FALSE column. So it depends on the implication of data too. One cannot blindly index all the columns on a table expecting it to have good performance. Too much of anything is good for nothing.

Also make a search on this forum for index, there were many such discussion on this topic and jeff's posts on those thread should put on the right track.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

thanks
let me implement this
then let u know if it works or not

HI,

You can go with Non Clustered index without worrying about space or performance. Here is an Example I use in one of my databases.

My database Name is TRPRO_MANMAN
Table Name is PGLMAS. Colum is PGLNUM. And Yes Its not a Primery Key.

    CREATE NONCLUSTERED INDEX IND_PGLNUM_NCL
    ON PGLMAS (PGLNUM)
    WITH FILLFACTOR = 70

--IND_PGLNUM_NCL is name of Index which will be refered by SQL
--PGLMAS IS table, PGLNUM is colum name.NON PK.

Primery key is not a necessity to inprove performance in search queries. I alsways choose Non Clustered Index.

Blindly speaking, you should create this type of Non Clustered Index for every colum you use for searching. You don't have to worry about Primery Keys.

Hope you got my point.

B. Anant