Dumb SQLCE question.

Not sure how to bing this, so, I am asking here. I have Primary Key of three columns. If I only query against one of the column, will primary key be used?

Like for example,

Primary Key = OrderID, LineItemID.

And I just say, SELET * FROM Items WHERE OrderID = 0001, will this be fast? Or do I need to create a separate index? I kind of think the index with more columns is capable of being used when subset of columns are queried?

That's a good question!

Normal index rules applies, primairy key is just a unique clustered index, so I think it will use part of the index, but not very efficiently.

If you query constantly on OrderID you could add a seperate index on that column.

If you really really want to know, just make a SQL2008 table and inspect the queryplan.

@Maddus Mattus:

Do you have any suggested tool for SQLCE? I have SQL Server Management Studio 2008, but, it doesn't work with my version of SQLCE

You might try this;

http://channel9.msdn.com/Shows/Visual-Studio-Toolbox/Visual-Studio-Toolbox-SQL-Server-Compact-Toolbox

Tools support for SQLCE sux0r to the max0r!

You need to install all kinds of scheisse to get it to work.

1 hour ago, magicalclick wrote

@Maddus Mattus:

Do you have any suggested tool for SQLCE? I have SQL Server Management Studio 2008, but, it doesn't work with my version of SQLCE

Try http://exportsqlce.codeplex.com/

3 hours ago, Maddus Mattus wrote

That's a good question!

Normal index rules applies, primairy key is just a unique clustered index, so I think it will use part of the index, but not very efficiently.

If you query constantly on OrderID you could add a seperate index on that column.

If you really really want to know, just make a SQL2008 table and inspect the queryplan.

that may not be a valid way to test.

depending on the sql ce version the result may be very different.  that i know of there are two very different engines for sqlce  the old one that was used with windows CE 4.xxx

and the new one that was re-written (as i recall what was said when it came out)

on top of that sqlce has different sets of rules for how it runs than std sql server.

things like amount of ram it can use and how to handlle multiple connections and so on.

@figuerres: So, just add a index to the column, what's the harm?

Every db engine I've ever worked with would use the index.  The requirement is that the WHERE clause provides the first column of the index (and the next column of the index if wanted, and the next column of the index if wanted, ...).

If the WHERE clause were "WHERE LineItemID = 0001" (i.e. OrderID was skipped) then all bets are off.

NOTE: whether the index is used or not is still affected by many other factors (i.e. is the dataset so small it is faster to tablescan for example).

@Maddus Mattus: Resource constraints need to be factored in the decision.  Indexes have a spacial cost.

I can confirm that Oracle behaves as davewill states above. If you query for the second part of the key, it may or may not (usually not, in my experience) use the index, depending on what it decides.

If the table is small and you're interested in columns that aren't in the index, Oracle may do a full table scan anyway since it's faster than looking up the row IDs on the index and then going to the table.

@davewill: Not on the client machine, where the client is the only user, he can't possibly be using another app,.. can he?

5 hours ago, davewill wrote

Every db engine I've ever worked with would use the index.  The requirement is that the WHERE clause provides the first column of the index (and the next column of the index if wanted, and the next column of the index if wanted, ...).

If the WHERE clause were "WHERE LineItemID = 0001" (i.e. OrderID was skipped) then all bets are off.

Thank you so much. I was expecting such behavior as well. I just didn't know if this is true. Even though this may not apply to latest SQLCE, but, it is very likely to be the case. At least I can expect this in other environment as well. May have to test it out, but, at least I am comfortable that it is very possible to be true based on your experience.

Also thanks for @figuerres: comment on SQLCE, because mine is newer and it doesn't work with SSMS 2008. It wouldn't be accurate if I used older SQLCE to test when I am actually using newer version.

@Maddus Mattus:

That toolbox seems to be what I need. Installing it now.

Hi all,

It does use PK, although it is strange, it says my PK is a non-clustered index, wuuuuutttt......

@magicalclick: I don't think SQL CE supports clustered indexes. It doesn't really make sense in a file-based DB anyway, since you don't gain anything by reordering the rows.

@spivonious: why wouldnt it make sense? It makes perfect sense, all I wish for in a db is that it can store it's information logically so I can retrieve it swiftly. That's what a database does! You get huge performance benefits ordering rows on the disk, this is key for any db! Otherwise, I could just use a big excel file.

I've used SQL CE extensively in the past, if you combine it with Lucene.NET, you wonder why you need the server product!

@spivonious:

Oh haha, I see. I thought if you reorder rows, you can do hdd seek? Although I suppose sqlce file is sparse by nature.

@Maddus Mattus: There are also (potentially) performance concerns with adding indexes. Especially if something gets updated often, as you're significantly adding to the amount of work that needs to be done with each change.

Why not setup your tables like this:

create table header(id int not null identity(1,1) primary key, ... )

create table line(id int not null identity(1,1) primary key, headerId int not null, ...)

create index ix0 on line(headerId)

?

@JoshRoss:

because you can only get int.MAX for the id? Also you didn't setup foreign key? It will be messy when you remove an order.

I care mostly about int.MAX limitation because if I am going things like comment and messages (which I did), I want to make sure each post / conversations can have as many comments / message as possible. My example is with orders, but, actually I am building a social site.

On the side note, I actually stopped using identity because of backup/restore concerns. I am not an expert on backup/restore, so, using identity is not a safe action for me. Also, I am using SQLCE, so, finding proper tools is not as easy as big daddy SQL Server.