Help MS SQL Indexing: Table structure & Search criteria provided here
Help MS SQL Indexing: Table structure & Search criteria provided here
(OP)
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
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
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!
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
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
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
CODE
GO
ALTER TABLE trData ADD CONSTRAINT [PK_trData_trID] PRIMARY KEY CLUSTERED
(
[trID] ASC
)
GO
CODE
(
[trCategoryName] ASC
)
GO
djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
Thanks again,
Best Regards,
Sam
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
Best Regards,
Sam
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
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!
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
May Almighty Bless you;
All the very best wishes,
Sam
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
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.
RE: Help MS SQL Indexing: Table structure & Search criteria provided here
Best Regards,
Sam