Set-Based Logic 3

[jordanking]

Hello,

I am relatively new to T-SQL and come from a VB background. As a result I have handled recordsets through looping in VB(VBA) and looked for similar techniques in T-SQL. I found the "cursor", which through the members/posts here I have come to realize is not a good solution. What is preffered is set based logic processing.

I could not find any FAQ's specifically about this topic, and I have ready through some of the posts by searching for "set-based logic"

I say all that to ask if anyone has good suggestions of websites/books/articles that deal with set based logic. I am trying to learn this as it is becoming increasingly necessary in my DB solution.

Or, if you feel inspired, can share your own tips and tricks

Thanks in advance

.....
I'd rather be surfing

 

[genomon]

Here is a start:

http://articles.techrepublic.com.com/5100-6228-5532304.html

All I ask is a chance to prove that money can't make me happy.

 

[SQLSister]

The first suggestion I would make is to look in Books on line at the Insert, Update, and delete statements. Using the insert stament with a select clause will replace a cursor using the valuses clause. Simply use the select from the cursor directly in the insert. If you have values you want for every record, you can add them as fields in the select as well.

Update and delete are a little more complicated, but read the syntax for using joins with them and I think you will see how to use a set-based solution there as well.

"NOTHING is more important in a database than integrity." ESquared

 

[jordanking]

Thanks both,

That got me going enough to be able to use sets with an insert and update statement, although they were simple.

What do you do to check for data integrity, based on your own business rules. Lets say you want to insert some records, through sets, but there are values that are not acceptable to your busniness logic, but acceptable as a data type, maybe a date field can only be within a given range.

Do you handle all such data verification with the client application, or do you use SQL server to test and return errors back to the user.

.....
I'd rather be surfing

 

[ESquared]

Look into check constraints (best and fastest) and triggers (require more knowledge, easier to mess up, but the only way to enforce complex rules at the database level).

You should still have business rules that operate outside of the database. You probably wouldn't want to implement complicated business logic in the database. But anything that you would consider basic data integrity or was very simple then let the database engine do it (though your application logic should be such that theoretically the database will never object to the data presented to it).

ALTER TABLE SomeTable ADD CONSTRAINT CK_SomeTable_ShipDateIsValid CHECK (ShipDate > OrderDate AND ShipDate - 365 < OrderDate AND ShipDate < GetDate() + 100)

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[jordanking]

thanks esquared,

How does a constraint work. If you attempt to enter a value that violates a given constraint, is an error thrown, or can you check a record or set to see if it violates a constraint before you attempt an update/insert etc?

And yes, chesse should be considered its own food group

.....
I'd rather be surfing

 

[SQLSister]

Here's my take on it. All data integrity issues should have database code to prevent bad data from being inserted. This is because there are multiple ways to insert data into tables and you want to make sure therule is consistently enforced. This is a last line of protection so that the person who opens Enterprise manager or runs a query in QA and does a "Quick fix" to a record still cannot get around the data integrity rules. Particularly as that person may not even be aware of them.

If data is being inserted from the user interface, then I would check rules there before sending it to prevent wasting resources on data that won't go in.

If I am bulk inserting a lot of records how you handle those that don't meet the criteria should be defined. We have some customers we send bad data back to and some that we simply delete the bad records in our pre-import process and some processes that fix common data issues. In any event, I would prefer to set up a holding table and pre-process the data before inserting it into the database and hitting the constraints or triggers.

"NOTHING is more important in a database than integrity." ESquared

 

[jordanking]

thanks SQLsister

the idea of a using a holding table to check data previous to a batch processing is great.

.....
I'd rather be surfing

 

[ESquared]

And let me clarify... some things are business logic and don't need to be enforced in the database. For example, you might say that after a customer has ordered more than $100,000 of product and has a B-rating payment confidence level and is in sales regions X, Y, or Z, and isn't attested to by one of your allied business partners, then don't let him order items which require factory reconfiguration that costs more than $2000.

Now that's something that programming into the database, while possible, may not be necessary. You'd have to design your database with a whole series of tables to describe complicated constraints like these.

However, at a database integrity level, if you let this customer place more orders than they are "allowed" you don't have bad data. The orders were really placed and the data is right.

That's what I meant by business rules not completely being in the database. On the other hand, an order with a shipdate earlier than orderdate doesn't make sense at all.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]