Help MS SQL Indexing: Table structure & Search criteria provided here

[sam4help]

Dears; Greetings

I am new to Indexing; I need a help in indexing this table for better search
I am using ms sql2005; and this is my table structure;

Table Name: trData
Column Name Data Type Allow Nulls
trID numeric(18, 0) NO
trProfileID int NO
trCategoryName nvarchar(50) NO
trLabelName1 nvarchar(300) YES
trLabelName2 nvarchar(300) YES
trLabelName3 nvarchar(300) YES
trLabelName4 nvarchar(300) YES
trLabelName5 nvarchar(300) YES
trLabelName6 nvarchar(300) YES
trLabelName7 nvarchar(300) YES
trLabelName8 nvarchar(300) YES
trLabelName9 nvarchar(300) YES
trLabelName10 nvarchar(300) YES
trLabelName11 nvarchar(300) YES
trOptionName1 bit YES
trOptionName2 bit YES
trExpiryDate datetime YES
trCreatedDate datetime YES
trModifiedDate datetime YES
trOwner nchar(25) YES
trType char(1) YES
trCode varbinary(256) YES

Now; Data is stored dynamically based on category; any of these field can be NULL based on the Category;
and accordingly the search criteria passed also leaves that NULL fields based on the category; see below eg.;

If data belongs to Category AAA then it may be
Category AAA
Name1 009
Name5 HHJHJ
Name6 HKKA
Name11 001

If data belongs to Category BBB then it may be
Category BBB
Name2 123
Name3 XYD
Name6 LLL
Name7 213121
Name8 HHH
Name10 YHJ
Name11 HJAH

Like this the other fields will be NULL in where data is not present;
Now my Search "Where" Clause will contain any of these fields based on Category selected; it can be any combination

trCategoryName
trLabelName1
trLabelName2
trLabelName3
trLabelName4
trLabelName5
trLabelName6
trLabelName7
trLabelName8
trLabelName9
trLabelName10
trLabelName11

I was wondering what would be the best index for this and what field needs to be included.
Would be highly grateful if someone with experience in indexing come forward with support,

Thanking you in advance,

Best Regards,

Sam

 

[djj55]

Put an index on trCategoryName (lookup CREATE INDEX)

I would also suggest a primary key on an unique ID column.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[sam4help]

Thanks for the reply djj55,
So, I put Primary Key on trID and
trCategoryName needs to be indexed; can you please help with statement to create the required index based on the above field names; because as what I have read is there are 2 types of indexes.

Thanks for coming forward with help;

Best Regards,
Sam

 

[djj55]

The Primary Key is normally the clustered index:

USE yourdatabase
GO

ALTER TABLE trData ADD  CONSTRAINT [PK_trData_trID] PRIMARY KEY CLUSTERED 
(
	[trID] ASC
)
GO

and the index is normally the non-clustered:

CREATE NONCLUSTERED INDEX [IX_trCategoryName] ON trData 
(
	[trCategoryName] ASC
)
GO

Read up on clustered and non clustered but basically they are respectively an index with actual data and an index with pointers. Notice that this is a simple explanation and you should get a more detailed one from your search.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[sam4help]

I do feel Logically with the Data and database setup I have this is the best we can do what you have mentioned above as other than these two fields there is no field which we can say will be present 100% in all records.

Thanks again,

Best Regards,

Sam

 

[sam4help]

One more thing djj; If I create the indexes as you advised above is that enough once and all? or do I need to re-create it quite often as we used to do in Foxpro.. the other thing is does MS Sql will automatically reference to these indexes when we fire search from front end (in my case its vb6); or do i need to specifically tell to use these indexes?

Best Regards,
Sam

 

[djj55]

You may need others indexes, but this should get you started.

Read about Execution Plans.

If it is not a primary key or unique index, you can index on a column that has nulls.
Once created indexes are good to go. Fragmentation will occur over time which can cause the need for a rebuild. Hopefully by then you will have had time to get better acquainted with SQL Server.

Good Luck,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[sam4help]

Great! thank you very much djj; it was really kind of you,
May Almighty Bless you;

All the very best wishes,

Sam

 

[PRPhx]

sam4help
you may also want to google: SQL Server 2005 Performance Dashboard reports. It's a free download from MS. You need SQL Server 2005 SP2 or above. One of the many things it does is suggest indexes. I've used it in the past nad it can be helpful.

 

[sam4help]

Thankx PRPhx will look into it,

Best Regards,

Sam