Thursday, August 25, 2005
Constraints vs Rules
Most organisations take data integrity very seriously. Indeed, it is Codd's 10th rule of relational data management. So the question isn't whether or not to manage the integrity of your data, but rather "how".
In most situations, I advocate managing the integrity of your data at the server level, as opposed to the application or procedure level. Using the server's data integrity is generally the most efficient and reliable way to maintain the integrity of your data.
In Sybase ASE, there are at least two good ways to manage the integrity of your data: Constraints and Rules. Usage for both are covered in the Transact-SQL User's Guide.
Constraints are covered in Chapter 7: Creating Databases and Tables
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=19279?target=%25N%15_20741_START_RESTART_N%25
Rules are covered in Chapter 12: Defining Defaults and Rules for Data
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=19279?target=%25N%15_37302_START_RESTART_N%25
Let's look first at an example of a column-level constraint.
create table atable (a int check (a > 0), b int constraint b_cons check (a > b))
insert into atable values (0, 0)
Check constraint violation occurred, dbname = 'test1', table name = 'atable', constraint name = 'atable_a_1771473076'.
Since we didn't assign a constraint name, we got an rather uninteresting name for our constraint. Normally we'd have to run a query like the following along with sp_helpconstraint or a look at syscomments to figure out what the constraint does.
select o1.name constraint_name, o2.name table_name, col.name column_name
from sysobjects o1, sysobjects o2, sysconstraints c, syscolumns col
where o1.id = c.constrid
and o2.id = c.tableid
and col.id = c.colid
That's why I prefer naming the constraints, like we did with the second one.
insert into atable values (1, 1)
Check constraint violation occurred, dbname = 'test1', table name = 'atable', constraint name = 'b_cons'.
If you're still not happy with this message, you can write your own and bind it to the constraint.
sp_addmessage 25001, "The b value must be greater than the a value"
sp_bindmsg b_cons, 25001
In the Reference Manual, Volume 4: Tables, you will see that you can also use: in, between, like and standard comparisons (<>=)
Now we saw that we can compare two columns together, let's try something more sophisticated.
For slightly more advanced constraints over multiple columns, we can use table-level constraints instead.
create table atable (a int, b int, constraint atable_cons check ((a > 0) and (a > b)))
Check constraint violation occurred, dbname = 'test1', table name = 'atable', constraint name = 'atable_cons'.
The two primary advantages to using Constraints over Rules are better performance and better portability.
Since constraints are binded directly to the table, the constraint itself is generally in memory. A rule may have to get fetched from the cache or, worse yet, the disk.
The reason constraints have better portability is that they are ANSI SQL standard. Every ANSI-compliant database should support constraints. Rules are an add-on.
Let's take a closer look at rules. The syntax and description for rules can be found in Reference Manual, Volume 2: Commands.
Rules are stand-alone objects. Once they are created, we can bind them to as many tables, columns, and procedures as we like. Here's an example.
create rule a_rule as @a > 0
create table atable (a int, b int)
sp_bindrule a_rule, 'atable.a'
A column insert or update conflicts with a rule bound to the column. The command is aborted. The conflict occured in database 'test1', table 'atable', rule 'a_rule', column 'a'.
One of the disadvantages of rules is that they can't reference multiple columns in a table like our constraint could.
create rule b_rule as @b > @a
Rule contains more than one variable.
So when should you use rules instead of constraints? The main reason is when you have an identical restriction on many tables, it might make sense to share a single rule rather than create separate constraints. If the rule changes, you only need to update it in one place, and you're assured that consistency.
There are other ways to maintain your data integrity. The other method to keep foremost in your mind is by using triggers. Triggers can handle much more advanced cases, because they can contain subqueries and reference other tables. So for advanced constraints, look to triggers.
In Summary:
- Constraints generally perform faster than rules
- Constraints can access multiple columns in a table
- Constraints are ANSI-SQL standard
- A single rule can be applied to multiple tables to assure consistency
- Triggers are most effective for very advanced data integrity conditions
Note: There are two new posts, the other one is on Compute
In most situations, I advocate managing the integrity of your data at the server level, as opposed to the application or procedure level. Using the server's data integrity is generally the most efficient and reliable way to maintain the integrity of your data.
In Sybase ASE, there are at least two good ways to manage the integrity of your data: Constraints and Rules. Usage for both are covered in the Transact-SQL User's Guide.
Constraints are covered in Chapter 7: Creating Databases and Tables
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=19279?target=%25N%15_20741_START_RESTART_N%25
Rules are covered in Chapter 12: Defining Defaults and Rules for Data
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=19279?target=%25N%15_37302_START_RESTART_N%25
Let's look first at an example of a column-level constraint.
create table atable (a int check (a > 0), b int constraint b_cons check (a > b))
insert into atable values (0, 0)
Check constraint violation occurred, dbname = 'test1', table name = 'atable', constraint name = 'atable_a_1771473076'.
Since we didn't assign a constraint name, we got an rather uninteresting name for our constraint. Normally we'd have to run a query like the following along with sp_helpconstraint or a look at syscomments to figure out what the constraint does.
select o1.name constraint_name, o2.name table_name, col.name column_name
from sysobjects o1, sysobjects o2, sysconstraints c, syscolumns col
where o1.id = c.constrid
and o2.id = c.tableid
and col.id = c.colid
That's why I prefer naming the constraints, like we did with the second one.
insert into atable values (1, 1)
Check constraint violation occurred, dbname = 'test1', table name = 'atable', constraint name = 'b_cons'.
If you're still not happy with this message, you can write your own and bind it to the constraint.
sp_addmessage 25001, "The b value must be greater than the a value"
sp_bindmsg b_cons, 25001
In the Reference Manual, Volume 4: Tables, you will see that you can also use: in, between, like and standard comparisons (<>=)
Now we saw that we can compare two columns together, let's try something more sophisticated.
For slightly more advanced constraints over multiple columns, we can use table-level constraints instead.
create table atable (a int, b int, constraint atable_cons check ((a > 0) and (a > b)))
Check constraint violation occurred, dbname = 'test1', table name = 'atable', constraint name = 'atable_cons'.
The two primary advantages to using Constraints over Rules are better performance and better portability.
Since constraints are binded directly to the table, the constraint itself is generally in memory. A rule may have to get fetched from the cache or, worse yet, the disk.
The reason constraints have better portability is that they are ANSI SQL standard. Every ANSI-compliant database should support constraints. Rules are an add-on.
Let's take a closer look at rules. The syntax and description for rules can be found in Reference Manual, Volume 2: Commands.
Rules are stand-alone objects. Once they are created, we can bind them to as many tables, columns, and procedures as we like. Here's an example.
create rule a_rule as @a > 0
create table atable (a int, b int)
sp_bindrule a_rule, 'atable.a'
A column insert or update conflicts with a rule bound to the column. The command is aborted. The conflict occured in database 'test1', table 'atable', rule 'a_rule', column 'a'.
One of the disadvantages of rules is that they can't reference multiple columns in a table like our constraint could.
create rule b_rule as @b > @a
Rule contains more than one variable.
So when should you use rules instead of constraints? The main reason is when you have an identical restriction on many tables, it might make sense to share a single rule rather than create separate constraints. If the rule changes, you only need to update it in one place, and you're assured that consistency.
There are other ways to maintain your data integrity. The other method to keep foremost in your mind is by using triggers. Triggers can handle much more advanced cases, because they can contain subqueries and reference other tables. So for advanced constraints, look to triggers.
In Summary:
- Constraints generally perform faster than rules
- Constraints can access multiple columns in a table
- Constraints are ANSI-SQL standard
- A single rule can be applied to multiple tables to assure consistency
- Triggers are most effective for very advanced data integrity conditions
Note: There are two new posts, the other one is on Compute
Wednesday, August 24, 2005
Compute
You have a table of client contracts and you want to know the sum of the value of those contracts by customer.
First let's set up our sample data.
create table ClientContracts (client_name varchar(32), work_detail varchar(32), amount money)
insert into ClientContracts values ("Johnson Autobody", "04AB Engine", 4000.00)
insert into ClientContracts values ("Johnson Autobody", "TL7 Axel", 325.00)
insert into ClientContracts values ("Libreville Engines", "L99X Transmission", 850.00)
insert into ClientContracts values ("Libreville Engines", "04AB Engine", 3250.00)
insert into ClientContracts values ("Libreville Engines", "Body Work", 298.95)
Ok, so you want to know the sum of the value of these contracts by customer? No problem, right? 'group by', right?
select client_name, sum(amount) from ClientContracts group by client_name
go
That does the job. But what if we wanted all the detail along with it? That's where 'compute' and 'compute by' come in handy. Observe:
select client_name, amount from ClientContracts order by client_name compute sum(amount) compute sum(amount) by client_name
go
Awesome!
So what is compute?
Quoting the Transact-SQL User's Guide, we can see from the example above that compute "Generates summary values that appear as additional rows in the query results."
So basically you can see the details AND the summaries in a single query. If you just want the aggregate results, you can use 'group by', which will show the aggregate as a new column. But 'group by' will not show all the detail like compute will.
'Compute by', incidentally, is for subgroups. In the example above, it gave us the sub-totals whereas 'compute' gave us the grand total. 'Compute by' has an implicit 'group by.'
Here are some other notes and features of 'compute':
- You can have multiple computes
- You can compute expressions
- Computes have to on a column among those selected in the query
- Computes are often used in what are called 'control-break reports'. So named because the results are 'broken up' by the sub-totals and totals.
- You can use compute on any of these aggregate functions: sum, avg, min, max, count
For more information, consult the following two Sybase manuals:
Reference Manual Volume 2: Chapter 7: Commands
- Includes 7 good examples
http://download.sybase.com/pdfdocs/asg1250e/refman2.pdf
Transact-SQL User's Guide: Chapter 3 Using Aggregates, Grouping and Sorting
- Includes example of group by vs compute
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=2930?target=%25N%14_7889_START_RESTART_N%25
First let's set up our sample data.
create table ClientContracts (client_name varchar(32), work_detail varchar(32), amount money)
insert into ClientContracts values ("Johnson Autobody", "04AB Engine", 4000.00)
insert into ClientContracts values ("Johnson Autobody", "TL7 Axel", 325.00)
insert into ClientContracts values ("Libreville Engines", "L99X Transmission", 850.00)
insert into ClientContracts values ("Libreville Engines", "04AB Engine", 3250.00)
insert into ClientContracts values ("Libreville Engines", "Body Work", 298.95)
Ok, so you want to know the sum of the value of these contracts by customer? No problem, right? 'group by', right?
select client_name, sum(amount) from ClientContracts group by client_name
go
client_name
----------- ------------------------
Johnson Autobody 4,325.00
Libreville Engines 4,398.95
That does the job. But what if we wanted all the detail along with it? That's where 'compute' and 'compute by' come in handy. Observe:
select client_name, amount from ClientContracts order by client_name compute sum(amount) compute sum(amount) by client_name
go
client_name amount
----------- ------------------------
Johnson Autobody 325.00
Johnson Autobody 4,000.00
sum
------------------------
4,325.00
Libreville Engines 298.95
Libreville Engines 850.00
Libreville Engines 3,250.00
sum
------------------------
4,398.95
sum
========================
8,723.95
Awesome!
So what is compute?
Quoting the Transact-SQL User's Guide, we can see from the example above that compute "Generates summary values that appear as additional rows in the query results."
So basically you can see the details AND the summaries in a single query. If you just want the aggregate results, you can use 'group by', which will show the aggregate as a new column. But 'group by' will not show all the detail like compute will.
'Compute by', incidentally, is for subgroups. In the example above, it gave us the sub-totals whereas 'compute' gave us the grand total. 'Compute by' has an implicit 'group by.'
Here are some other notes and features of 'compute':
- You can have multiple computes
- You can compute expressions
- Computes have to on a column among those selected in the query
- Computes are often used in what are called 'control-break reports'. So named because the results are 'broken up' by the sub-totals and totals.
- You can use compute on any of these aggregate functions: sum, avg, min, max, count
For more information, consult the following two Sybase manuals:
Reference Manual Volume 2: Chapter 7: Commands
- Includes 7 good examples
http://download.sybase.com/pdfdocs/asg1250e/refman2.pdf
Transact-SQL User's Guide: Chapter 3 Using Aggregates, Grouping and Sorting
- Includes example of group by vs compute
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=2930?target=%25N%14_7889_START_RESTART_N%25
Tuesday, August 09, 2005
Getting Help with Sybase ASE
In a previous blog, I mentioned some of the key documents and tools that many Sybase ASE professionals would have available.
http://sybasease.blogspot.com/2005/07/sybase-survival-pack.html
In this post, I am going to expand on this somewhat and talk about where you can go to find the solutions to your Sybase ASE problems.
Understandably, the very first place you should look is with Sybase's Documentation. Here are the three key links:
1. Search Sybase's Documentation using Sybooks
http://sybooks.sybase.com/
2. Alternatively, this link will take you directly to a page where you can view (or download) the manuals on-line.
http://sybooks.sybase.com/asg1250e.html
3. Sybase TechDoc searches through whitepapers, articles, release notes and bulletins.
http://www.sybase.com/support/techdocs/
If you can't find what you want in Sybase's documentation, chances are you are not alone. If so, you should see if someone has already solved your problem or answered your question before. There is a good Sybase FAQ maintained by ISUG:
Sybase FAQ from ISUG
http://www.isug.com/Sybase_FAQ/ASE/index.html
http://www.faqs.org/faqs/databases/sybase-faq/
Beyond that, you can search some private web sites for articles on the same topic. Here are my four favourite links, which you should also see on my list of links:
1. Rob Verschoor's site is probably the best. Tips, tricks, articles, lots of great stuff.
http://www.sypron.nl/
2. Todd Boss has some GREAT articles, a wealth of technical info, and lots of useful scripts.
http://www.bossconsulting.com/sybase_dba/
3. Ed Barlow hasn't updated his site for awhile, but he has a good vault of information. This link goes directly to that vault.
http://www.edbarlow.com/documents.htm
4. Michael Peppler's is the best site for SybPerl (Perl library for Sybase). Not surprising since he's the author of SybPerl.
http://www.peppler.org/~mpeppler/
If you can't find what you're looking for anywhere, then you can ask for help. While there are others, here are the two newsgroups I recommend:
sybase.public.ase.general
comp.databases.sybase
These newsgroups are relatively low-traffic, but there are still a fair number of people that will read the articles, including the experts in TeamSybase. So you may have to wait awhile to get a response. Obviously, you should search the forums first to see if your question has already come up. Use TeamSybase's tool to search the sybase forums:
http://www.teamsybase.com/ngsform.html
If your question is not quick, concise and clear, you may not get a response at all. Try to narrow down your problem to make it quick and easy for someone to help you.
I don't have any book recommendations, partly because you aren't going to do much better than the Sybase manuals. I really recommend you read some of them. But the two books I use most often are Sybase Unleashed, and Rob Verschoor's Quick Reference Guide.
http://sybasease.blogspot.com/2005/07/sybase-survival-pack.html
In this post, I am going to expand on this somewhat and talk about where you can go to find the solutions to your Sybase ASE problems.
Understandably, the very first place you should look is with Sybase's Documentation. Here are the three key links:
1. Search Sybase's Documentation using Sybooks
http://sybooks.sybase.com/
2. Alternatively, this link will take you directly to a page where you can view (or download) the manuals on-line.
http://sybooks.sybase.com/asg1250e.html
3. Sybase TechDoc searches through whitepapers, articles, release notes and bulletins.
http://www.sybase.com/support/techdocs/
If you can't find what you want in Sybase's documentation, chances are you are not alone. If so, you should see if someone has already solved your problem or answered your question before. There is a good Sybase FAQ maintained by ISUG:
Sybase FAQ from ISUG
http://www.isug.com/Sybase_FAQ/ASE/index.html
http://www.faqs.org/faqs/databases/sybase-faq/
Beyond that, you can search some private web sites for articles on the same topic. Here are my four favourite links, which you should also see on my list of links:
1. Rob Verschoor's site is probably the best. Tips, tricks, articles, lots of great stuff.
http://www.sypron.nl/
2. Todd Boss has some GREAT articles, a wealth of technical info, and lots of useful scripts.
http://www.bossconsulting.com/sybase_dba/
3. Ed Barlow hasn't updated his site for awhile, but he has a good vault of information. This link goes directly to that vault.
http://www.edbarlow.com/documents.htm
4. Michael Peppler's is the best site for SybPerl (Perl library for Sybase). Not surprising since he's the author of SybPerl.
http://www.peppler.org/~mpeppler/
If you can't find what you're looking for anywhere, then you can ask for help. While there are others, here are the two newsgroups I recommend:
sybase.public.ase.general
comp.databases.sybase
These newsgroups are relatively low-traffic, but there are still a fair number of people that will read the articles, including the experts in TeamSybase. So you may have to wait awhile to get a response. Obviously, you should search the forums first to see if your question has already come up. Use TeamSybase's tool to search the sybase forums:
http://www.teamsybase.com/ngsform.html
If your question is not quick, concise and clear, you may not get a response at all. Try to narrow down your problem to make it quick and easy for someone to help you.
I don't have any book recommendations, partly because you aren't going to do much better than the Sybase manuals. I really recommend you read some of them. But the two books I use most often are Sybase Unleashed, and Rob Verschoor's Quick Reference Guide.
