<p>I am encountering very infrequent yet annoying SQL deadlocks on a .NET 2.0 webapp running on top of MS SQL Server 2005. In the past, we have been dealing with the SQL deadlocks in the very empirical way - basically tweaking the queries until it work.</p>

<p>Yet, I found this approach very unsatisfactory: time consuming and unreliable. I would highly prefer to follow deterministic query patterns that would ensure <strong>by design</strong> that no SQL deadlock will be encountered - ever.</p>

<p>For example, in C# multithreaded programming, a simple design rule such as <em>the locks must be taken following their lexicographical order</em> ensures that no deadlock will <em>ever</em> happen.</p>

<p>Are there any SQL coding patterns guaranteed to be deadlock-proof?</p>

As you said, always access tables in the same order is a very good way to avoid deadlocks. Furthermore, shorten your transactions as much as possible.
Another cool trick is to combine 2 sql statements in one whenever you can. Single statements are always transactional. For example use "UPDATE ... SELECT" or "INSERT ... SELECT", use "@@ERROR" and "@@ROWCOUNT" instead of "SELECT COUNT" or "IF (EXISTS ...)"
Lastly, make sure that your calling code can handle deadlocks by reposting the query a configurable amount of times. Sometimes it just happens, it's normal behaviour and your application must be able to deal with it.