Well, that would naturally depend on all the other factors.
But, changing the order of the sort on the index shouldn't affect the data access, as the index is a B-Tree.
It will run the chain in which ever path is needed. Reversing the order will just send it to the left instead of the right.
What fields you have in your index in regards to meeting the needs of the query are more important.
I have a xml field in my member table, it stores data like
<field name="n1">v1</field><field name="n2">v2</field> ....
When I retrieve 10 fields from the xml field like
select exfield.value('(field[@.name="n1"]/.)[1]','nvarchar(max)'),exfield.value('(field[@.name="n2"]/.)[1]','nvarchar(max)')..... from members
when returns 2200 records, it takes 37 seconds,
I tried to add different xml index on the xml field, but none of them help.
What do you think if there is an index can help to arthieve that?
thanks
I highly suggest that you read the excellent whitepaper at http://msdn2.microsoft.com/en-us/library/ms345118.aspx. You need to taylor your indexes and queries to your needs and this paper can help you make those decisions.
Regards,
Galex
1) Does a 'normal' index on the entire fields get used in a search like this?
2) Can you build an index on just the left 4?
3) I could use an indexed view, but the table wasn't built with the right settings and I don't want to rebuild and repopulate the table if I don't have to
Thanks for the insight.
PeteNo, you cannot index a portion of a field.
If you did a search like " WHERE field LIKE 'abcd%' and field2 LIKE 'efgh%' " then I believe you will find that SQL Server will use the index (use Query Analyzer to make sure). SQL Server changes these kinds queries into SEARCHABLE ARGUMENTS (SARGS) that can use indexes (note that this is NOT true of things like " LIKE '%abcd%' )|||If this is something that you really need (indexing on only a prtion of a filed), you could create a computed field that just contains the portion you want to index on.
Does SQL Server 2000 support indexes on bit fields and doesn't Enterprise Manager support it, or doesn't SQL Server 2000 support indexes on bit fields and is it a 'bug' of the SQL Server Management Studio?
Thanks.
an index on a bit field gives absolutely no benefit due to the way that sql server handles bit fields.|||My experience tells me that this is not true. When the SQL Server only has to access an index instead of the real table, this can increase performance. In the past we've converted Bit fields to TinyInt, so they could be added to an index (while the only values where 0 and 1). This sometimes resulted in queries that executed more than 10 times as fast as without that field in the index.
|||
but thats not a bit field. . . that is a tinyint.
read as to how bit fields are managed. if you have one bit field it might help. . . but if you have more than one in a table it won't.
I contend, a need for an index on a bit field 'smells' of an unnormalized schema (not in all cases, but 99%)
|||Maybe your right about the unnormalized schema, but creating an extra table for each bit field just for the sake of normalizing makes reading the schema a lot more complicated.I've been searching, but can't find any information on managing bit fields in SQL Server. Have you got a reference for me were I can find more information?
Thanks for your help.
|||
http://msdn2.microsoft.com/en-us/library/ms177603(SQL.90).aspx
see how bit fields are groupd together as bytes. . . bit fields 1 - 8 are in one byte, bit fields 9 - 16 are in another. . . . and so on? It was the same in SQL 2000.
as far as the 'smell', and I don't mean that as disrespectful, often when there is a bit field there is some other piece of data that can be used or should be tracked.
for example, instead of an 'IsSubscribed' bit field, have a nullable field SubscriptionDate, then selecting 'IsSubscribed' = -1 equates to
select p.id from person where not SubscriptionDate is null
In this case SubscriptionDate contains much more information.
And often times you need to track Subscription information and that should be in another table. Then selecting IsSubscribed = -1 transforms to:
select p.id from Person p inner join Subscription s on p.id = s.personId
|||Well you actually can create indexes on bit fields in SQL Server 2000, but you can't do it through the Design Table Interface. You could either use T-SQL to do it or if you want to do it Visually, then you could also right-click on the table choose All Tasks->Manage Indexes and you can select bit fields here to create the index.
Now Microsoft discloses the storage implementation of bit field data types, but that doesn't mean that they don't store bit field data types differently for Indexes. Maybe if a bit field is Indexed they store it in the B-Trees in their own byte field instead of in the concatenated fashion that they store the data itself. Doubtful, but possible. Your best bet would be to use Query Analyzer with Show Execution Plan enabled and look at the difference between querying with a Indexed Bit field and without it. If it works I would imagine there are situations where it could be helpful. There are definitely appropriate times when you can/should utilize bit fields (e.g Male/Female), etc...
If you had a very large database and a bit field and you wanted to search your data then an index on that field would work. The question is "Does Microsoft do some unpublished Magic" to take advantage of it, as in store the Bit Field Data differently for Indexes as opposed to data, but the best way to test it would be to setup an appropriate scenario and evaluate the Execution Plan and Performance.
Sam
