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
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