Can you elaborate on this: "For performance reasons we suggest using Foreign Keys instead of relationships."

My understanding is that Relationships will create the foreign keys (and the relationship table) for you similar as you would do in the relational world:

TableA

AID

TableB

BID

TableABRelationship

AID

BID

FK: AID

FK: BID

You can manage without. You have plenty of Object Oriented options to get to the items.  Or you could use $O over the global to get the IDs (similar to what Extent would do)

$ORDER would do. Not the cleanest (or preferred) but the point being you can do without SQL.

Cache doesn't provide this "out of the box" but you can always create a class that works as a Collection (a collection of IDs if you will) and provides an Iterator interface/functionality for to you iterate over the values.  You can always expose and access data through Objects as you need to.

Yeah, that's what I was referring to :-(

Can you provide more details on what exactly you're trying to accomplish? As you said, the last query works. What exactly is missing? Are you trying to get a prompt (outside of Cache I'm guessing) with the parameter name?

The only "caution" here is that referencing the property by name only works with properties where the SqlFieldName corresponds to a valid Cache property name:

1. Property myproperty As %String;
2. Property myproperty As %String [SqlFieldName = "mysqlname"];
3. Property myproperty As %String [SqlFieldName="my_property"];

You can access #1 & #2 by property name, however #3 you can only access by using %Get("my_property");

You're totally right. I forgot about this syntax.

Can we infer form this that sharding can be applied to globals that have been mapped to classes (thus providing SQL access)?

Alex, it's all based on your design. The decision of whether a method is an instance or class method is totally independent of whether that method can be private or not. Your design, and patterns or best practices you want to follow, will dictate which way to go. The benefits are in what you want to accomplish with the design: ease of testing, ease of change, etc. 

Let's say I have an orders global with the following structure:

^ORD(<ID>)=customerId~locationId.....

And I create a mapping class for this global: MyPackage.Order

Can I use sharding over this table?

I understand the accessing part but by creating a class mapping I'm enabling SQL access to the existing global. I guess that the question is more in line on whether sharding will be able to properly partition (shard) SQL tables that are the result of global mapping? Are there any constraints on how the %Persistent class (and the storage) is defined in order for it to work with sharding? Should they all use %CacheStorage or can they use %CacheSQLStorage (as with mappings)? 

Agree with brace always. This really helps with new developers as the syntax is something they've seen before in other languages. 

If you want to check on the result of a LOCK command for example 

LOCK +<SOMERESOURCE>:<timeout>

IF $T {do something}

So I wouldn't call it bad practice just yet.

Understand. I haven't tried this myself yet but in theory you should be able to initialize the Reader with an XML.Document object. This Document can be initialized (%OnNew) with a global of your choice in the same manner that TextReader can. 

Correct. The alternative using TextReader was to "avoid" increasing memory but if you have that option then the answers provided by Timothy are the way to go.

Code placement depends on what you're trying to do and when: if processing the event from javascript (ajax call) then your code must be separate from OnPreHTTP(); if processing on the server (after page submit) then your code should be in OnPreHTTP() or called from it (this last part depends on your coding style - jam everything in a single method or separate things in multiple smaller methods)

In simple terms (as Robert pointed out) , OnPreHTTP needs to handle any processing the server must perform prior to "displaying"/"printing" the page on the browser.

Is ClassDefinition available even if you deploy without the source code e.g. OBJ version?

Besides dynamically changing the maxlen what else are you trying to accomplish/avoid? These values are used at compile time to generate the SQL catalog and become part of your contract (for clients calling them). If you were to dynamically change the values 1) you'll be breaking the contract (e.g. you could change the values to a more restrictive size) and 2) you would still need to compile the class holding the stored proc/query. 

The benefit I can think of this approach right now is avoiding pushing code to prod (assuming that process is cumbersome), however you still need the compile part.  It'll be better to define and modify the stored proc using DDLs (maybe  a faster change in prod depending your company's rules).  Since this change is a database object change it should be treated as code and thus (hopefully) kept in source control (for audit and replication purposes). 

Even SQL operations are captured in the journals as far as my understanding goes. But I'd agree any solution would have to check on any scenarios such as data not being journaled since processes can disable journaling "at will".  Journaling can also be disabled at the db level.

Going under the assumption that "everything" is captured in the journals it should be possible to write a process that reads the journal file and convert its entries to another format.

I'm not sure there's a Cache specific document for designs like this. There are multiple ways to separate data in any database (schema, instance, namespaces, etc). Cache provides extra features such as namespace mappings that makes sharing simpler but before you get to Cache specific implementation you need a high level design of your application. Regardless of database and programming language, how should my application work?  How do you plan to sell your product? How flexible you want it to be? As Dmitry pointed out, are there any regulations/limitations around sharing data within customers? All this should be based on your  business model and has nothing to do with technology stack.