Hello Devrishi

These are a few guidelines that will help in reducing trouble down the road. Ofcourse every application has its own demands and needs, but consider these points as IDEALLY you should do.

1.
Every table must have an ID. That ID must be the primary key of that table. That ID must be IDENTITY. All the foreign keys should be linked with these ID's. You can name that ID field what ever you want, but the good practice is to name that ID as TableName+ID i.e.
CUSTOMERID, VENDORID etc...

2.
If possible, and your application doesnt have specific needs, You should always avoid the given Transact-SQL Statements in your executable code, the statements are SELECT, INSERT, UPDATE, DELETE.
2.1 When you embed these Transact-SQL statments in the EXE, they are difficult to maintain.
2.2 When you embed these Transact-SQL statments in the EXE, when ever there is a need to to change the TRANSACT-SQL, you need to recompile your executable.
2.3 Its very difficult to control the Business Process Flow, as all the executables are executing their own statements, that results in duplication of code, duplication of effort evertime you need to change or fix some bug.
2.4 In a team every user has its way of writting the code, so if the Transact-SQL are embedded in the EXE, its difficult to do QA.
2.5 SQL Server doesnt have the pre-compiled optimized plan to execute these statements.


4. All the object names of sql server i.e. tables, procedures, functions, views etc.. should ideally be singular and not plural. for example PO_DETAILS should be PO_DETAIL. The general idea is to STANDARDIZE the names so that they easy to remember. At least it should not mix it for example, PO_DETAILS and SO_DETAIL. This creates confusion everytime. This may not be true with a SEASONED programmer to commit such mistakes, but beginners dont realize the imporatance of naming convention and indentation of the code and they do these silly mistakes.


6. Try to AVOID transactions from the FRONT END, if possible.
Transactions, ideally should be at back end. Try to keep the duration of the transaction as short as possible.

The problem of generating invoice number that you are having can have several solutions.

You should always try to AVOID making primary key a field which has the potential to change. Most of the time (Not Always) Primary key is also your CLUSTERED index.

I am assuming you have invoice_number as numeric, if varchar then you just need to write the programme accordingly but basics will be the same..

EXAMPLE 1.

Begin Transaction
Select
@xInvoice_Number = Coalesce(Max(Invoice_Number),0)+1
From
Invoice

Insert invoice
Commit Transaction

In above example the sql server will do the locking for you and no user will be given the access to invoice table unless untill you commit or rollback transaction.

EXAMPLE 2 - Improved version..
The above example has a small problem, it doesnt handle the ERROR


Begin Transaction
Select
@xInvoice_Number = Coalesce(Max(Invoice_Number),0)+1
From
Invoice

Insert invoice

If @@Error <> 0
begin
Rollback Transaction
end
else
begin
Commit Transaction
end



NOW ADO
======
You should understand ADO and especially the cursors created by ADO.

I will just give you hints here for Cursors

Try not to execute a query with SELECT * From Invoice. Always try to use the Field names if you know, and ofcourse write stored procedure as much as possible. Design, Divide different phases of your application in such a way that you can avoide fetching all the records from a table, especially Transaction Tables.

The General rule of Thumb is Data should be Fetched on DEMAND.

If you want to fetch empty record set then USE
Select Fieldname, Fieldname from invoice where 1=0

You should understand very well two things

1. CLIENT side cursor
2. SERVER side cursor

You should put 500,000 records in your table, and open them once as client side cursor and once as server side, you should see the difference

But at some places you need client side cursors and at some places you need server side cursor:)


GratPeaceMaker
[email protected]

MrPeaceMaker

There are so many wrong-headed statements in that last post, I don't know where to begin. I guess I must have gotten up on the wrong side this morning, but I can't resist commenting when I it. Please forgive any excessive sarcasm, but it's hard to avoid...

I am impressed with your confidence. That you can, on your very first post here, weigh in with several statements of absolutes in broad areas of database analysis, design and performance is truly profound. In all my years in this business, I've found it generally prudent to avoid terms like 'guarantee 100% success...for 10 years', 'NEVER EVER EVER EVER', and '...you will never have any trouble...'. But that's just me... :D

Maybe it is me, but I find every situation is unique, with its own conditions and parameters, and while certain rules of thumb may be useful in broad situations, an experienced developer will realize when those rules of thumb apply, and when they don't.

Let's examine a few of your statements.

Indeed, every table must have a primary key, and all tables should implement referential integrity on their foreign keys. But, what difference does it make what I name the primary key column? Instead of some vague name like ID which conveys virtually no useful information, why not a more descriptive term, like 'CustomerNumber' or 'UserName'? Or, if you insist on using an artificial key, 'CustomerID'?

There have been religious wars over the use of IDENTITY as a primary key. I'm not going to get into the reasons why there is disagreement here (unless you want me to :)). That you can state without equivocation that it should be the primary key in every table you define is indicative of your ignorance over the valid areas of disagreement in the industry concerning the usage of the IDENTITY attribute.

Consider my favorite example, a State Code table. This table lists state codes and their full names. You apparently would advocate adding and using an identity ID column instead of the unique StateCode column as the primary key. You certainly must add a unique constraint to the State Code, or you risk data corruption, so now there are two unique indexes on this little table, one serving a useful purpose, and the other serving only dogma. What's worse, the ID column now forces an extra JOIN to pick up that code whenever you want to construct an address for an Invoice, say. While JOINs are useful, and an inevitable byproduct of a properly normalized database, why add a useless join which serves no purpose other than to make your database conform to your dictum? Using the 2 character state code as the primary key avoids the JOIN entirely and is much simpler.
I would agree that using stored procedures is preferable to generating the SQL statements on the client. That having been said, though, there certainly are situations where client generated SQL is appropriate. I have written client code which interacts with a user to construct a complex query scenario, consisting of query selection, table JOINs and ordering. To encode all the permutations of that interaction in a stored procedure would result in a huge, hideous procedure which would be a nightmare to maintain. Also, the T/SQL compiler is a one-pass compiler and not very efficient handling complex procedural code. It is way more efficient and much easier for maintenance purposes to construct the query at the client and submit the finished string to be executed.
Since each row of a properly designed database models a business entity or relationship, and a table consists of multiple rows, why shouldn't the table name reflect that fact? I don't have an AUTHOR table, containing just one author, I have a table of information describing a set of AUTHORS. I think a table should be named to describe what it contains, don't you? I use a plural, or better yet, some descriptive collective noun like 'Personnel', if one exists.
You've never had to deal with performance issues resulting from process blocking due to locking as a result of large numbers (hundreds and thousands) of users doing simultaneous inquiries and updates, have you? Do you suppose that TSQL provides for such concepts as locking hints and isolation levels merely for academic completeness?
Where to start? I can envision so many circumstances where client initiation of transactions is the appropriate thing to do. The use of offline updates of disconnected recordsets comes to mind as well as finer control over the situations in which transactions should be committed or rolled back, something that would have to be handled by complex procedural code (read slow) in the stored procedure, if you had your way.

God forbid you should have a transaction open for 10 seconds. How can you possibly place such precise limits (1-10 seconds) on the 'length' of a transaction? I find it interesting that you earlier say you MUST use IDENTITY, but now you only SHOULD never have user input when there is a transaction open. (This is a case where I would use an absolute and state that you should NEVER do this.)
Why? See above.
I note that the default isolation level for a transaction is READ COMMITTED. Thus your query to obtain the maximum invoice number and then use it to insert a new row can fail if a new row is inserted into the table while your process is trying to insert its row. This is because both processes will see only the committed value of the invoice number, and if neither has committed when the other reads the table, they'll both get the same maximum value. This can easily (well, sort of) happen on heavily loaded (or multi-processor) systems.

You must use REPEATABLE READ to insure that the appropriate update locks are held for the length of the transaction including the SELECT to avoid collisions while executing that SELECT statement which assigns the Invoice number.

This is the sort of query performance analysis which must be identified and managed by a knowledgeable individual, and not via dogma.
I don't understand your theories, and I assure you I have been doing this for a lot longer than 3 or 4 years. Have you?

This makes no sense. What if my client process needs all the rows, to fill a list box containing State Codes, for example?
Now that was helpful.

Could you be a bit more precise? Under what conditions would you use a server-side cursor, versus client-side? Do you even know where or when each may be the right thing to do?
Want to bet?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Wow!
Jeff, I am glad you got the chance to reply to that one. I did not get to it until this morning. You beat me to it. Good job.
So much for being peace maker. You woould have war inside or your applications with those principles.

Sal

Well, despite my criticsm, there are several reasonable ideas in there.

What I object to mostly is the absolutes - "do it this way and you'll NEVER have a problem" is just plain naive.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

:)
I appreciate and thanks for your criticism.

I should also take back my words like NEVER.

But i believe you would also agree that if a Beginner starts following the ideas i gave, till the time he actually understands all the nitty-gritties of good and bad, that beginner user will not regret the guidelines he followed, and they will save a lots of time and effort and re-write of the code down-the-road.



MrPeaceMaker

"Peace Maker",

I do not agree that a Beginner should follow the ideas you gave- or that it would benefit them.

Jeff,

I always understood that select * is bad because it first needs to find out the column names- and if I instead did a select with the column names spelled out, even if I wanted all of them, I would get better performance- is that untrue? Also, you giving any classes :)?

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee