SQL RNNR

We are here again after another week and ready for another episode in this series.  Today we get to talk about chapter 6 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

Common Problems with Data Integrity

Alex explains to us that SQL server has built-in tools to help maintain the integrity of the data.  He quickly lists out three areas that can be useful in maintaining Data Integrity, as well.

1. Applications
2. Constraints
3. Triggers

Application

Data integrity can be enforced from the application.  It should be noted that this could be an inefficient method of ensuring the integrity of the data.  If you try to do it from the application, be prepared for a little pain.

Constraints

It is well known that using constraints is the most reliable way to enforce data integrity rules.

Using constraints in the database is helpful and quick.  If data does not meet the constraint criteria, we can prevent it from being added.  There are different kinds of constraints and one should become familiar with the various constraints.  In this section, Alex also demonstrates the differences for the three states of constraints.  It is important to understand the state of the constraint to ensure the integrity of the data is at the level your expect.

There is a section devoted to UDFs in this chapter as well.  The section is within the Constraints section and should be read.

Triggers

The chapter is concluded with the discussion on using Triggers for enforcing data integrity.  Triggers are suitable in some areas where a constraint would not be suitable for enforcing data integrity.  Triggers do have their own problems that should be considered too though.  Some of the problems are with how the trigger was coded and some are inherent with the use of triggers.  It is well worth learning about these limitations by reading this section as well.  Test the samples provided and implement some of the suggestions in your triggers.

Conclusion

Alex concludes this chapter with the following statement:

Data integrity logic in the application layer is too easily bypassed. The only completely

robust way to ensure data integrity is to use a trusted constraint. Even then, we need to

test our constraints, and make sure that they handle nullable columns properly.

We also have a nice set of reminders of what to include in our assumptions to be tested.  The short of it, check your constraints and test as many scenarios as necessary based on your assumptions.

We are here again after another week and ready for another episode in this series.  Today we get to talk about chapter 5 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

The title of the chapter this week is “Reusing T-SQL Code.”  In this chapter, Alex covers the topics of:

• copy and paste code
• proper code reuse
• View use to encapsulate simple queries
• UDFs and parameterized queries
• Performance issues with UDFs
• Business logic through filtered indexes, triggers and constraints

I am going to skip the discussion on copy and paste of code and the potential problems related to that.  I think that should be highly obvious.  I think code reuse should also bring up some obvious ideas of why to do it and how it can make your code better.  Simple queries in views is also pretty straight forward.  For these first three topics, check out what Alex has to say about them in his book.

UDFs and parameterized queries

When it comes to code reuse, it is typically easier to to reuse parameterized queries through an inline UDF.  That’s not to say that it cannot or should not be done through a stored procedure.  For many scenarios and requirements, it would be easier to do a parameterized query through a function.  Alex gives examples of some of the limitations such as sorting, Insert/Exec, and nesting.  There are of course limitations that should be considered with inline UDFs.  Alex has those listed in his book.

Performance issues with UDFs

Alex makes a very good point in this section.  When trying to reuse code, we have to pay attention to performance.  Poorly written UDFs could have serious impacts on the performance of the code and that should be a great concern.  To demonstrate this, Alex has listed out an example covering the bases.  Check out his code samples and test it.

There is also another great statement in this section.

Blanket statements do not belong in database programming.

Keep that in mind.  It is very difficult to make a blanket statement and have it apply in all cases.

The final section is covering the topic of when to use Filtered Indexes, Constraints, triggers and stored procedures.  Proper use of each of these can help to enforce business logic.  It should be noted that the use of these will depend on requirements and database design.

Having a single source of the code will help to prevent unexpected bugs.  It will also help to save time on development and documentation.