×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread669-1655687

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

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

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!

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

(OP)
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

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

The Primary Key is normally the clustered index:

CODE

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:

CODE

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!

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

(OP)
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

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

(OP)
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

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

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!

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

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

All the very best wishes,

Sam

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

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.

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

(OP)
Thankx PRPhx will look into it,

Best Regards,

Sam

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close