correct practices with SQL Server

I am hoping someone can give some advice on the following things:

I have read a few times about a data access layer in an n-tier application. I am assuming that this should be done

using sprocs. Is there an advantage of using sprocs instead of views ( in situations where the same thing could

be accomplished using either)? Will a sproc run faster than a view? Can any share any info?

Are sprocs best suited for data access and to enforce business rules?

I know SQL Server has reserved words that shouldn't be used. I am wondering what the best thing to do is

in the following situation? What is the best way to handle storing a customer or clients address? I am working from a book that shows the name of a column as "Address". I have found that with SQL Server 2005

Express that this is a reserved word(it is shown in blue in the query window). I want to keep my names short. I am trying to avoid a name like "StreetAddress". Is my book teaching bad habits?

...........................................thanks...........................................................

Views are generally better than stored procedures where they could be used interchangeably, especially considering that you can create indexes on views.

While it makes sense to want to use address for a customer's address, the meaning (in a computer) of the word is too general. It could mean a customer's home address, delivery address, the data address of some memory block on the client side, the IP address of another server, really almost anything. Ideally, you would want to follow naming conventions that enable other developers to immediately draw as much information as they can out of a table (e.g. customer name, customer address, customer ID. This will make it much easier to join tables (e.g. joining customer table with business table might have 2 address fields that need to be resolved). One possible naming convention is to consider the table name (e.g. customers) as an abstract object or schema for a data set and name everything by "<schema> <object name>" you could use underscores if you were more comfortable with unspaced column names. Ideally, this will make it much easier to read complex joins as the project grows and will make maintenance much less of a headache -- since there will be fewer questions like "which address do you mean: the one in customer data, business data, the address of the pointer, or the shipping address of the order in question?".

In general, all books teach both good and bad habits, unfortunately. Most SQL books published over the past 10 years have SQL Injection attacks available against all their examples, for instance. Like grade school -> high school -> college, we have to accept that some percentage of the information we learn in books or from teachers is just wrong. That doesn't mean it isn't practical to use as a learning tool, it is just not perfect in practice. This is sort of the scientific method, work with what you know until you learn something better, then paradigm shift and continue. In the long run, there is no perfect book for any given domain space. The only way to grow is to continuously seek out information, as you are doing now.

Hope that helps,

John

|||

So, it seems like the advantage of using a view with an index would be to increase performance. Should stored procedures be used only when there is a need to write to the database? Are these deductions correct?

I ended up doing what you suggested and changed it to CustAddress and EmpAddress.

........................................Thanks for the advice