Good article. I'm looking forward to more.

Can you point me to a good resource on maintenance considerations, if any, for Compact databases?

Not knowing the structure or the code, it's hard to make suggestions. If you haven't already, check out this white paper from Microsoft. They outline a number of things you...

I think this is a case of buyer beware, your mileage may vary, some assembly required... You get the drift.

Scalar functions, as Adam very clearly outlines, unless they're very...

Check the connection settings for both Management Studio & the App. You may have differences there that are causing the differences in execution time. Stuff like SET IMPLICIT_TRANSACTIONS, SET ANSI_PADDING.

Do...

True, I forgot about that. Thanks.

That's one ugly debate you're entering into. Short answer is, parameterized queries function the same way as stored procedures. SQL Server will be able to reuse execution plans because it...

You've got someone running transactions, sorts, or creating temporary tables with lots of data. We had users starting an ORDER BY query (no parameters, bad joins, bad search criteria) that,...

In terms of a join, don't adjust anything. If the column is null, it doesn't equal anything and that row won't be returned which is as it should be.

You've already laid out the issues pretty well. You can either save it in the database where:

[highlight]You get a good, clean, synchronized backup

When the user is deleted, you also easily...

You could use a view to combine the data from the four tables into a single select statement. 12,000 rows, depending on how much data is in them, is not...

I have to agree with Steve on this one. If you've had a conflict (and who hasn't) and that comes up in an interview, you need to be honest about...

this is a pretty regular debate. I usually fall back on business requirements to help decide.

The one technical issue that does help decide is backups. If you store the...

Well, there you go. Trigger is the trick then.

You don't have to use a trigger.

You could use the OUTPUT clause from the insert statement:

INSERT INTO TabA (Col1, Col2)

OUTPUT Inserted.Col1, Inserted.Col2 INTO TabB (Col1,Col2)

VALUES ('A','B')

Because of the naming conventions (you're right, they're horrible), it's hard to tell exactly what's happening in the "generic" design. However, looking at your design, it sure seems to me...