Opinion Requested: Multi-Table Design

I have about a 35000 record table. There are about 14 entries in this table that relate to "sections". Each of these sections can have up to 20 values. This lends itself to a design like:

big_table [one-to-one] big_table_has_sections [many-to-many] sections

or, more concretely:

BuildingConstructionType
 relates to
ConstructionHasTypes
 relates to
Types

Where BuildingConstructionType is one of 14 fields in the 35000 record "big" table, ConstructionHasTypes is the one-to-one intermediary relation that relates many-to-many with Types (the sections).

Unfortunately, with 35000 records, this big_table_has_sections seems like it might bloat. Is this a good solution?In order to facilitate what I think you are trying to accomplish, I don't know of any better way. Presumably the cross reference table is smallish in nature (i.e. composed of 2-3 key fields) and therefore should be indexed for quicked retrieval.

The obvious difficulty lies in how to retrieve the data. Your concerns for bloat are well founded as you will be using joins to return the appropriate dataset. If you user the query analyzer you will see that even simple joins between 2 tables using indexed keys are typically the most expensive (time and resource) operation in getting the data back. The reason for this is that sql does a table scan each and every time!

For this reason when dealing with tables of any sgnificant size I always use a stored procedure that employs one or more temporary tables which are populated using the appropriate where clause(s). Then I perfrom any joins using the temporary tables previously generated. That way the table scan will only go through the 100 or whatever number of BuildingConstructionType records in the temporary table instead of all 35000.

Granted, doing this takes a little more time and effort but my clients have tables containing 2.5 -3 million records with joins up the kazoo and I can still kick out result set in under a second.

Hope this is helpful and feel free to ask for any additional assistance if required.

Cheers

Gary|||Thanks... that's what I feared. It would be great if I could represent a vector effectively, but that doesn't appear to be consistent with the declarative nature of SQL.

Opinion Requested on Developer Permissions

We are trying to restrict developer permissions in our development environment. One thought is to add developers to db_datareader, db_datawriter, db_ddladmin, db_securityadmin and then revoke various permissions from ddladmin and securityadmin. The goal is to allow developer to create stored procedures and assign permissions to the stored procedures.

Another option is to place all developers in the same role and ask them to create all procedures using that role name (ex: dev_role.sp_procedurename). By doing this each developer will be able to run stored procedures created by another developer. The down side is the permissions do not match Model Office/User Test and Production.

Any suggestions on how to handle this situation?

Thanks, DaveI have 3 instances on 2 boxes..

DEV, QA and PROD...

Let the developers knock themsleves out in DEV...hopefully using some kind of version control

Promote all the code and YOU execute it in QA..they're not allowed in there

QA tests the code

QA gets signed off

Code is the promoted to prod on another box...|||We have the same thing here as Brett does there. Developers get added to the db_owners group in development, and are barred from using anything but the application logins in QA and prod (too hard to police, anyway). No one ever gets sysadmin on anything (and oh, how they ask).

opinion re: xp_fileexist

Hi all,

Sorry for spamming you with this, I thought it might be the best way to get your opinion on the topic.

OVERVIEW:

We should not use MS internal procedures, since MS has the right to change/remove them at will. Given that, should the following code be allowed, or not. If not, how does anyone suggest, handling checking for the existance of a file that has been given as a parameter to some T-SQL.

--<this code is just an example, hence not neat etc.>

SET NOCOUNT ON

DECLARE @.FilePath varchar(1024)

SET @.FilePath = 'D:\Sample Data\Files Processing URL\PromotionRulesActive.txt'

--substitute a local file for testing locally

CREATE TABLE #FileExistance ([File Exists] smallint, [File is a Directory] smallint, [Parent Directory Exists] smallint)

insert into #FileExistance exec xp_fileexist @.FilePath

select * from #FileExistance

drop table #FileExistance

GethWho,
Why don't you check that from your calling code?
TSQL is not intended to be used for such purposes, and when a functionality as simple as checking whether a file exists or not is missing, that simply means you are not supposed to need such funtionality. I am sure Microsoft has "hidden" that SP intentionally, because when you do such things from SQL it almost certainly means that something is going wrong.

|||thanks for the response!
the reason I've been considering this is as part of design-by-contract. Basically, I have an SP that will get given a file location, and will use the path to do an insert into SQL. Making use of the file to do a bulk insert, or a OPENROWSET open of the file, is a T-SQL activity.
My intention is to ensure that before trying to execute the code, I ensure that the inputs I receive meet my contract - i.e that they identify a valid file location. That's why I've been toying with the xp_fileexist.
I suspect that at the end of the day, initially I will have to leave off doing the file existance checking, and eventually (when I have time to learn some c# and then do the coding) I will create my own up_fileexist code in an assembly, and use that.
Thanks for the interest and response - I welcome more comments/feedback/ideas|||

GethWho, thank You for your resonce too.
Anyhow I insist that checking a file existance is not a T-SQL activity. Indeed, bulk inserting is.
All I am saying that the code that calls your SP should make sure the file it passes exists. Validation such as that should not be done on behalf of data manipulation layer of the application. Even if the source that commits the file location to your SP is untrustworthy, you might consider creating some routine that checks the file location before submitting it to SP.
You should distribute the roles of your application between logical layers. Mixing the roles will make a maintainance nighmare out of nothing. A proper design can never end in a model where SQL needs to validate file locations, text inputs or other things like that. This is business layer responsibility.
Especially-code like that will take you approximately 5 minutes to write(in VB.net or VB6.0), even if you aren't familiar with the language. I can help you on behalf of that, mail me 'andranik at armsoft . am' if you find any difficulties.

|||Hi Andranik,
Thanks for confirming my suspicions. I think I'll try and get the time to write the .Net/C# - from what I've heard from you and some other sources, it may be something simple enough for even me to write :-)
Just a question regards the comment of trustworthiness of code. My impression of design-by-contract is that it is a methodology/design practice that drives one to write robust, resilient code that doesn't necessarily trust the calling code - hence the pre- and post- contract checking to ensure that components are robust enough to all truly robust applications. Surely that applies to all levels, and not just the front and middle tier's?
I'd appreciate some comments/pointers to good articles on design-by-contract (I'll be googling myself, but you/someone may already have a good link), and especially how it applies/doesn't apply to the data access layer.
Thanks again!
|||

Hi GethWho,
I'd reccommend Lhodka's book, "Expert C# Business Objects".

Indeed, you must verify the data at some points. You should check whether the data received from a client browser or a windows form is valid. Meanwhile, doing the same validation on all tiers is indeed redundant-you will end up writing the same code for UI, application and maybe even data access layer!
The right design choice is to encapsulate all object-related validation into object itself. In this case, using "mobile objects", that is, objects that are passed by value over the network, you can reuse the validation code on UI and application layers. Use of mobile objects has many other benefits and several drawbacks.
Data manipulation code is usually embedded into object code too. You should indeed separate the methods that read from/write into database from other methods. In this case, when a data manipulation code is called, you can make sure that the object to be persisted is valid. Indeed, no SQL-side validation is needed-the code calling SQL server-side code guarantees that the object data is valid and consistent. Otherwise, you will end doing business-layer oriented validation is SQL SP, which is...not the right choice :)
I think we should make the further discussion private because this is getting out of the topic of this forum. Feel free to mail me,
'andranik at armsoft . am'

opinion question - permanantly deleting records

I have a database "philosophy" question. What are some opinions on
permanant record deletion? This would apply to personnel tables,
inventory tables, or whatever. From an accountant's point of view
records of important information such as this should not be
permanently deleted (e.g. you have 1,500 widgets of a particular SKU
in inventory, wouldn't you want to know every transaction in and out
of inventory from which this figure were derived?). Another case: You
have a total of 1,000 current and ex-employees who have various merit
and/or discipline actions taken based on performance. A record in the
performance files for all employees is generated each time an action
of significance is taken. For legal reasons I would think these
records should reside in the database perpetually. Some users are
concerned that former employees may show up on reports. My proposed
solution is simply to have a field that indicates whether or not an
employee is active or inactive.
Any thoughts on these issues? We don't have a storage problem that is
presenting this, we have rather hefty server space.
Any "White Papers" anyone could recommend would be appreciated.
Thanks!
Slinky
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
I think you are on the right way..
"slinky" <campbellbrian2001@.yahoo.com> wrote in message
news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>I have a database "philosophy" question. What are some opinions on
> permanant record deletion? This would apply to personnel tables,
> inventory tables, or whatever. From an accountant's point of view
> records of important information such as this should not be
> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> in inventory, wouldn't you want to know every transaction in and out
> of inventory from which this figure were derived?). Another case: You
> have a total of 1,000 current and ex-employees who have various merit
> and/or discipline actions taken based on performance. A record in the
> performance files for all employees is generated each time an action
> of significance is taken. For legal reasons I would think these
> records should reside in the database perpetually. Some users are
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
> Any thoughts on these issues? We don't have a storage problem that is
> presenting this, we have rather hefty server space.
> Any "White Papers" anyone could recommend would be appreciated.
> Thanks!
|||Thanks! Do you know of any MS or Oracle documentation that addresses
this issue?
On Feb 18, 10:18Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Slinky
>
> I think Xyou are on the right way..
> "slinky" <campbellbrian2...@.yahoo.com> wrote in message
> news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>
>
>
> - Show quoted text -
|||I prefer to model this with pairs of dates to indicate a period. For
example, with employees you can have a table that records employee start and
end date for employment. When employee is hired record the start date and
end date is NULL. When employee leaves the company, update the end date to
record that. And having this in a separate table will give you the option to
record multiple events for employee (for example, if rehired). Based on that
you can easily select current active employees, those are employees that
have end date NULL. This will also provide a good trail for auditing if
needed, and it provides much more information that a simple flag.
If you have only a flag for active/inactive, then you can run into some
weird reporting problems, like if somebody asks to run a report of employees
that were active last year. Then if you have an employee that became
inactive in the beginning of this year, you will exclude the employee from
the report because the flag shows inactive, but this is incorrect because
the employee was active last year. Keeping a period of dates will help you
to answer this question correctly.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Does the philosophy include securing the information?
"slinky" wrote:

> I have a database "philosophy" question. What are some opinions on
> permanant record deletion? This would apply to personnel tables,
> inventory tables, or whatever. From an accountant's point of view
> records of important information such as this should not be
> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> in inventory, wouldn't you want to know every transaction in and out
> of inventory from which this figure were derived?). Another case: You
> have a total of 1,000 current and ex-employees who have various merit
> and/or discipline actions taken based on performance. A record in the
> performance files for all employees is generated each time an action
> of significance is taken. For legal reasons I would think these
> records should reside in the database perpetually. Some users are
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
> Any thoughts on these issues? We don't have a storage problem that is
> presenting this, we have rather hefty server space.
> Any "White Papers" anyone could recommend would be appreciated.
> Thanks!
>
|||I think it greatly depends on the type of data and the size.
if you're generating a million+ rows of, say, statistical data a day that
you'll only need a 90 day trending on, you obviously want to archive that
stuff and purge it from the database as a matter of course.
Personnel records, on the other hand, are generally required to be kept by
law for certain periods. I've heard 7 years, but a lot of companies keep
that info in perpetuity, which is not a bad idea. The size of a table
related to personnel does not tend to grow out of control.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23SRQZFkcIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Slinky
> I think you are on the right way..
>
>
> "slinky" <campbellbrian2001@.yahoo.com> wrote in message
> news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>

opinion question - permanantly deleting records

I have a database "philosophy" question. What are some opinions on
permanant record deletion? This would apply to personnel tables,
inventory tables, or whatever. From an accountant's point of view
records of important information such as this should not be
permanently deleted (e.g. you have 1,500 widgets of a particular SKU
in inventory, wouldn't you want to know every transaction in and out
of inventory from which this figure were derived?). Another case: You
have a total of 1,000 current and ex-employees who have various merit
and/or discipline actions taken based on performance. A record in the
performance files for all employees is generated each time an action
of significance is taken. For legal reasons I would think these
records should reside in the database perpetually. Some users are
concerned that former employees may show up on reports. My proposed
solution is simply to have a field that indicates whether or not an
employee is active or inactive.
Any thoughts on these issues? We don't have a storage problem that is
presenting this, we have rather hefty server space.
Any "White Papers" anyone could recommend would be appreciated.
Thanks!Slinky
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
I think you are on the right way..
"slinky" <campbellbrian2001@.yahoo.com> wrote in message
news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>I have a database "philosophy" question. What are some opinions on
> permanant record deletion? This would apply to personnel tables,
> inventory tables, or whatever. From an accountant's point of view
> records of important information such as this should not be
> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> in inventory, wouldn't you want to know every transaction in and out
> of inventory from which this figure were derived?). Another case: You
> have a total of 1,000 current and ex-employees who have various merit
> and/or discipline actions taken based on performance. A record in the
> performance files for all employees is generated each time an action
> of significance is taken. For legal reasons I would think these
> records should reside in the database perpetually. Some users are
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
> Any thoughts on these issues? We don't have a storage problem that is
> presenting this, we have rather hefty server space.
> Any "White Papers" anyone could recommend would be appreciated.
> Thanks!|||Thanks! Do you know of any MS or Oracle documentation that addresses
this issue?
On Feb 18, 10:18=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Slinky
> > concerned that former employees may show up on reports. My proposed
> > solution is simply to have a field that indicates whether or not an
> > employee is active or inactive.
> I think =A0you are on the right way..
> "slinky" <campbellbrian2...@.yahoo.com> wrote in message
> news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>
> >I have a database "philosophy" question. What are some opinions on
> > permanant record deletion? This would apply to personnel tables,
> > inventory tables, or whatever. From an accountant's point of view
> > records of important information such as this should not be
> > permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> > in inventory, wouldn't you want to know every transaction in and out
> > of inventory from which this figure were derived?). Another case: You
> > have a total of 1,000 current and ex-employees who have various merit
> > and/or discipline actions taken based on performance. A record in the
> > performance files for all employees is generated each time an action
> > of significance is taken. For legal reasons I would think these
> > records should reside in the database perpetually. Some users are
> > concerned that former employees may show up on reports. My proposed
> > solution is simply to have a field that indicates whether or not an
> > employee is active or inactive.
> > Any thoughts on these issues? We don't have a storage problem that is
> > presenting this, we have rather hefty server space.
> > Any "White Papers" anyone could recommend would be appreciated.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||I prefer to model this with pairs of dates to indicate a period. For
example, with employees you can have a table that records employee start and
end date for employment. When employee is hired record the start date and
end date is NULL. When employee leaves the company, update the end date to
record that. And having this in a separate table will give you the option to
record multiple events for employee (for example, if rehired). Based on that
you can easily select current active employees, those are employees that
have end date NULL. This will also provide a good trail for auditing if
needed, and it provides much more information that a simple flag.
If you have only a flag for active/inactive, then you can run into some
weird reporting problems, like if somebody asks to run a report of employees
that were active last year. Then if you have an employee that became
inactive in the beginning of this year, you will exclude the employee from
the report because the flag shows inactive, but this is incorrect because
the employee was active last year. Keeping a period of dates will help you
to answer this question correctly.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Does the philosophy include securing the information?
"slinky" wrote:
> I have a database "philosophy" question. What are some opinions on
> permanant record deletion? This would apply to personnel tables,
> inventory tables, or whatever. From an accountant's point of view
> records of important information such as this should not be
> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
> in inventory, wouldn't you want to know every transaction in and out
> of inventory from which this figure were derived?). Another case: You
> have a total of 1,000 current and ex-employees who have various merit
> and/or discipline actions taken based on performance. A record in the
> performance files for all employees is generated each time an action
> of significance is taken. For legal reasons I would think these
> records should reside in the database perpetually. Some users are
> concerned that former employees may show up on reports. My proposed
> solution is simply to have a field that indicates whether or not an
> employee is active or inactive.
> Any thoughts on these issues? We don't have a storage problem that is
> presenting this, we have rather hefty server space.
> Any "White Papers" anyone could recommend would be appreciated.
> Thanks!
>|||I think it greatly depends on the type of data and the size.
if you're generating a million+ rows of, say, statistical data a day that
you'll only need a 90 day trending on, you obviously want to archive that
stuff and purge it from the database as a matter of course.
Personnel records, on the other hand, are generally required to be kept by
law for certain periods. I've heard 7 years, but a lot of companies keep
that info in perpetuity, which is not a bad idea. The size of a table
related to personnel does not tend to grow out of control.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23SRQZFkcIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Slinky
>> concerned that former employees may show up on reports. My proposed
>> solution is simply to have a field that indicates whether or not an
>> employee is active or inactive.
> I think you are on the right way..
>
>
> "slinky" <campbellbrian2001@.yahoo.com> wrote in message
> news:389b26e9-8097-4df7-86a9-407a8a8fa3ee@.c33g2000hsd.googlegroups.com...
>>I have a database "philosophy" question. What are some opinions on
>> permanant record deletion? This would apply to personnel tables,
>> inventory tables, or whatever. From an accountant's point of view
>> records of important information such as this should not be
>> permanently deleted (e.g. you have 1,500 widgets of a particular SKU
>> in inventory, wouldn't you want to know every transaction in and out
>> of inventory from which this figure were derived?). Another case: You
>> have a total of 1,000 current and ex-employees who have various merit
>> and/or discipline actions taken based on performance. A record in the
>> performance files for all employees is generated each time an action
>> of significance is taken. For legal reasons I would think these
>> records should reside in the database perpetually. Some users are
>> concerned that former employees may show up on reports. My proposed
>> solution is simply to have a field that indicates whether or not an
>> employee is active or inactive.
>> Any thoughts on these issues? We don't have a storage problem that is
>> presenting this, we have rather hefty server space.
>> Any "White Papers" anyone could recommend would be appreciated.
>> Thanks!
>

Opinion on SQL Stored Procedure Syntax

I'm trying to improve the quality of life of this SP
CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
smalldatetime
as
Set Nocount on
delete ttemp_data
Insert into ttemp_data
SELECT appeal_codes, appeal_type, appeal_code,
SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
AS total_yes_amount, SUM(total_cc) total_cc,
SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
AS total_no, SUM(call_resolution) AS total_resolves, leads
FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
CASE WHEN o.call_resolution LIKE 1 THEN COUNT
(o.call_resolution)
END AS 'total_yes', SUM(o.Pledge_Amount)
AS Pledge_Amount,
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
COUNT(o.if_yes_pledge_or_credit_card)
END AS 'total_cc',
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
o.pledge_amount
END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
CASE WHEN o.call_resolution LIKE 3 THEN
COUNT(o.call_resolution)
END AS 'total_wc',
CASE WHEN o.call_resolution LIKE 2 THEN
COUNT(o.call_resolution)
END AS 'total_no', COUNT(o.call_resolution)
AS call_resolution, c.lead as 'leads'
FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
o.appeal_codes = c.appeal_code
GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
o.if_yes_pledge_or_credit_card, o.pledge_amount,
o.date_fld, o.last_gift, o.appeal_type, c.lead
HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
AS tbl
GROUP BY appeal_codes, appeal_code, appeal_type, leads
Thanks for the helpWhat does "quality of life" mean? Do you want it to be happier? Look
prettier? Make more money? Be more efficient? All of the above? None of
the above?
<vncntj@.hotmail.com> wrote in message
news:1148417269.627636.34530@.u72g2000cwu.googlegroups.com...
> I'm trying to improve the quality of life of this SP
> CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
> smalldatetime
> as
> Set Nocount on
> delete ttemp_data
> Insert into ttemp_data
> SELECT appeal_codes, appeal_type, appeal_code,
> SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
> AS total_yes_amount, SUM(total_cc) total_cc,
> SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
> AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
> AS total_no, SUM(call_resolution) AS total_resolves, leads
> FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
> CASE WHEN o.call_resolution LIKE 1 THEN COUNT
> (o.call_resolution)
> END AS 'total_yes', SUM(o.Pledge_Amount)
> AS Pledge_Amount,
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
> COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
> o.pledge_amount
> END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
> CASE WHEN o.call_resolution LIKE 3 THEN
> COUNT(o.call_resolution)
> END AS 'total_wc',
> CASE WHEN o.call_resolution LIKE 2 THEN
> COUNT(o.call_resolution)
> END AS 'total_no', COUNT(o.call_resolution)
> AS call_resolution, c.lead as 'leads'
> FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
> o.appeal_codes = c.appeal_code
> GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
> o.if_yes_pledge_or_credit_card, o.pledge_amount,
> o.date_fld, o.last_gift, o.appeal_type, c.lead
> HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
> AS tbl
> GROUP BY appeal_codes, appeal_code, appeal_type, leads
> Thanks for the help
>|||The assignment of colum aliases:
AS 'total_cc'
uses single quotes. Why use quotes? total_cc is a perfectly valid
column name, and anyway if a column name is in quotes it should be
"double quotes".
Looking at the construct:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN COUNT(o.if_yes_pledge_or_credit_card)
END AS 'total_cc',
I am not sure that will work. The more common way to code this -
assuming I understand what it is supposed to return - is:
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN 1 ELSE 0
END) AS 'total_cc',
Looking at the test:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
I forsee problems with the LIKE. LIKE is for strings, not numbers.
The HAVING test in the inner query looks like it should be a WHERE
test.
I suspect that the use of a derived table is avoidable. Take the
expression from the inner query that is SUMed in the outer:
CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
END AS 'total_cc_amount',
Consider
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
ELSE 0
END AS total_cc_amount,
I reworked the whole thing into a single query that I THINK would do
what you are trying to do. I did not try to fix the LIKE tests and
such. It will be easier to read using a fixed pitch font.
SELECT o.appeal_codes,
o.appeal_type,
o.appeal_code,
SUM(CASE WHEN o.call_resolution LIKE 1
THEN COUNT (o.call_resolution)
ELSE 0
END) AS total_yes,
SUM(o.Pledge_Amount) AS total_yes_amount,
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN 1
ELSE 0
END) as total_cc,
SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
THEN o.pledge_amount
ELSE 0
END) AS total_cc_amount,
SUM(o.last_gift) AS last_gift_total,
SUM(CASE WHEN o.call_resolution LIKE 3
THEN 1
ELSE 0
END) AS total_wc,
SUM(CASE WHEN o.call_resolution LIKE 2
THEN 1
ELSE 0
END) AS total_no,
COUNT(o.call_resolution) AS total_resolves,
C.leads
FROM tlead_sheets AS o
JOIN tappeal_codes AS c
ON o.appeal_codes = c.appeal_code
WHERE o.date_fld BETWEEN @.StartDate AND @.EndDate
GROUP BY o.appeal_codes, o.appeal_code, o.appeal_type, c.leads
Hope that helps some.
Roy Harvey
Beacon Falls, CT
On 23 May 2006 13:47:49 -0700, vncntj@.hotmail.com wrote:

>I'm trying to improve the quality of life of this SP
>CREATE Procedure p_generatedata @.StartDate smalldatetime, @.EndDate
>smalldatetime
>as
>Set Nocount on
>delete ttemp_data
>Insert into ttemp_data
>SELECT appeal_codes, appeal_type, appeal_code,
> SUM(total_yes) AS total_yes, SUM(Pledge_Amount)
> AS total_yes_amount, SUM(total_cc) total_cc,
> SUM(total_cc_amount) AS total_cc_amount, SUM(last_gift)
> AS last_gift_total, SUM(total_wc) AS total_wc, SUM(total_no)
> AS total_no, SUM(call_resolution) AS total_resolves, leads
>FROM (SELECT o.appeal_codes, o.appeal_code, o.appeal_type,
> CASE WHEN o.call_resolution LIKE 1 THEN COUNT
>(o.call_resolution)
> END AS 'total_yes', SUM(o.Pledge_Amount)
> AS Pledge_Amount,
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
>COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2 THEN
>o.pledge_amount
> END AS 'total_cc_amount', SUM(o.last_gift) AS last_gift,
> CASE WHEN o.call_resolution LIKE 3 THEN
>COUNT(o.call_resolution)
> END AS 'total_wc',
> CASE WHEN o.call_resolution LIKE 2 THEN
>COUNT(o.call_resolution)
> END AS 'total_no', COUNT(o.call_resolution)
> AS call_resolution, c.lead as 'leads'
> FROM tlead_sheets AS o INNER JOIN tappeal_codes AS c ON
>o.appeal_codes = c.appeal_code
> GROUP BY o.appeal_codes, o.appeal_code, o.call_resolution,
>o.if_yes_pledge_or_credit_card, o.pledge_amount,
> o.date_fld, o.last_gift, o.appeal_type, c.lead
> HAVING o.date_fld BETWEEN @.StartDate AND @.EndDate)
> AS tbl
>GROUP BY appeal_codes, appeal_code, appeal_type, leads
>Thanks for the help|||Thanks..
Roy Harvey wrote:
> The assignment of colum aliases:
> AS 'total_cc'
> uses single quotes. Why use quotes? total_cc is a perfectly valid
> column name, and anyway if a column name is in quotes it should be
> "double quotes".
> Looking at the construct:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN COUNT(o.if_yes_pledge_or_credit_card)
> END AS 'total_cc',
> I am not sure that will work. The more common way to code this -
> assuming I understand what it is supposed to return - is:
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN 1 ELSE 0
> END) AS 'total_cc',
> Looking at the test:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> I forsee problems with the LIKE. LIKE is for strings, not numbers.
> The HAVING test in the inner query looks like it should be a WHERE
> test.
> I suspect that the use of a derived table is avoidable. Take the
> expression from the inner query that is SUMed in the outer:
> CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> END AS 'total_cc_amount',
> Consider
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> ELSE 0
> END AS total_cc_amount,
> I reworked the whole thing into a single query that I THINK would do
> what you are trying to do. I did not try to fix the LIKE tests and
> such. It will be easier to read using a fixed pitch font.
> SELECT o.appeal_codes,
> o.appeal_type,
> o.appeal_code,
> SUM(CASE WHEN o.call_resolution LIKE 1
> THEN COUNT (o.call_resolution)
> ELSE 0
> END) AS total_yes,
> SUM(o.Pledge_Amount) AS total_yes_amount,
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN 1
> ELSE 0
> END) as total_cc,
> SUM(CASE WHEN o.if_yes_pledge_or_credit_card LIKE 2
> THEN o.pledge_amount
> ELSE 0
> END) AS total_cc_amount,
> SUM(o.last_gift) AS last_gift_total,
> SUM(CASE WHEN o.call_resolution LIKE 3
> THEN 1
> ELSE 0
> END) AS total_wc,
> SUM(CASE WHEN o.call_resolution LIKE 2
> THEN 1
> ELSE 0
> END) AS total_no,
> COUNT(o.call_resolution) AS total_resolves,
> C.leads
> FROM tlead_sheets AS o
> JOIN tappeal_codes AS c
> ON o.appeal_codes = c.appeal_code
> WHERE o.date_fld BETWEEN @.StartDate AND @.EndDate
> GROUP BY o.appeal_codes, o.appeal_code, o.appeal_type, c.leads
> Hope that helps some.
> Roy Harvey
> Beacon Falls, CT
>
> On 23 May 2006 13:47:49 -0700, vncntj@.hotmail.com wrote:
>

opinion on preventing duplicate record insertion

Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this articlehttp://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005

I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit...thanks ..

You essentially have two points of validation for the duplication.. at the code level, and at the database level.
You should employ both to good effect so that you do not get any and more importantly you control what happens when it already exists.

Unique constraints work - if you've set the correct ones, you will never get a duplicate entry in the database.
You should make sure you have them set.

Next, on a stored procedure that inserts the record, you can either try to insert and an error will occur because of the constraint and a SqlException will be generated that will bubble back to your code.
OR, you can use the IF EXISTS code that is suggested in the link you sent.
This checks if those valuse already exists in the database and doesn't try the insert.
I would advise that you also apply this to your logic.

On the code level, you can store Session or Viewstate Variables to flag that the update has happened as suggested in the fourth page of that article.

So you can incorporate all three so that

1. It checks the session variable and doesn't allow for duplicates
2. If it "slips the net" here, OR you later change your UI code, the stored procedure IF EXISTS catches and handles
3. If logic later changes, different sp used, your Unique Constraint will eventualy catch and throw an exception.

You are ensuing at all points that the problem cannot occur.

|||

"unique constraints" are reliable - the problem is handling the duplication condition. In my opinion the first method in the article is the preferred method - that of detecting the duplicate before the constraint does.

Opinion about design needed (splitting string data)

Hi to everyone,

My problem is, that I'm not so quite sure, which way should I go.

The user is inputing by second part application a long string (let's
say 128 characters), which are separated by semiclon.
Example:

A20;BU;AC40;MA50;E;E;IC;GREEN

Now: each from this position, is already defined in any other table, as
a separate record. These are the keys lets say. It means, a have some
properities for A20, BU, aso.

Because this long inputed string, is a property of device (whih also
has a lot of different properities) I could do two different ways of
storing data:

1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:

Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 ... any comment text
124 MZD13 XY55 ... any other comment

String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
....
123 8 GREEN

The device table, contains also a pointer (position), which might
change, to "hglight" specified position.

Then, I can very easly find all necessary data. The problem is, I need
to move the device record data (from other table) very often into other
history table (by each update). That will mean, that I also need to
move all these records from 1 -8 for example to a separate history
table, holding the index for a history device dataset. This is a little
inconvinience in this, and in my opinion, it will use to much storage
data, and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.

2. Table will be build nearly in this way:

Major device data table
ID AnyData1 AnyData2 ... AnyData3 stringProperty pointer
123 MZD12 XX77 ... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3
124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2

By writng into device table, there will be just a additional field for
this string, and I will have a function, which according to specified
pointer, will get me the string part on the fly, while I need it.
This will not require the other table, and will reduce the amout of
data, not a lot ... but always.
This solution, has a inconvinance, that it will be not so fast doing a
search over the part of this strings, while there will be no real index
on this.
If I woould like to search all devices, by which the curent pointer
value is equal GREEN, then I need to use function for getting the
value, and this one will be not indexed, means, by a lot amount of
data, might be slow.

I would like to know Your opinion about booth solutions.
Also, if you might point me the other problems with any of this
solution, I might not have noticed.

With Best Regards

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:
>
Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 ... any comment text
124 MZD13 XY55 ... any other comment
>
String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
...
123 8 GREEN
>
The device table, contains also a pointer (position), which might
change, to "hglight" specified position.

This is the normal design in this situation.

Quote:

Originally Posted by

Major device data table
ID AnyData1 AnyData2 ... AnyData3 stringProperty pointer
123 MZD12 XX77 ... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3
124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2

This design violates a basic principle in relational design: no repeating
groups.

Every rule is made to break, and I have occasionally put repeating groups in
the database I maintain, but this is a clearcut case: don't even think
about it. This sort of data is very difficult to work with in a
relational database, simply because it's not meant that you should
store data in this way.

Quote:

Originally Posted by

Then, I can very easly find all necessary data. The problem is, I need
to move the device record data (from other table) very often into other
history table (by each update). That will mean, that I also need to
move all these records from 1 -8 for example to a separate history
table, holding the index for a history device dataset. This is a little
inconvinience in this, and in my opinion, it will use to much storage
data,

With a sub-table you need to repeat the ID. There will also be a cost
of two bytes for the length of each column. There is also the cost for
the field number, but since you don't have any semi-colon, this is a
net cost of one byte. There is also some overhead for each row. But
all and all, I would say that the overhead is about neglible.

Quote:

Originally Posted by

and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.

Don't really know what you mean here.

For completeness sake I should say that there is a third alternative,
and that is one table, but eight columns. This could also be considered
a repeating group. Then again, if the different fields represents
different attributes, it isn't really an repetition. This solution
is better my opinion than a seprated list, but the pointer you talk
about may be more difficult to implement.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>and by programming, I need always to shift this properities into
>history table, whith indexes to a history table of other properities.

>
Don't really know what you mean here.

Probably something along the lines of: (oversimplified for brevity)

insert into FooHistory select * from CurrentFoo
delete from CurrentFoo|||Then maybe you could create a view for the second table.

Matik wrote:

Quote:

Originally Posted by

Hi to everyone,
>
My problem is, that I'm not so quite sure, which way should I go.
>
The user is inputing by second part application a long string (let's
say 128 characters), which are separated by semiclon.
Example:
>
A20;BU;AC40;MA50;E;E;IC;GREEN
>
Now: each from this position, is already defined in any other table, as
a separate record. These are the keys lets say. It means, a have some
properities for A20, BU, aso.
>
Because this long inputed string, is a property of device (whih also
has a lot of different properities) I could do two different ways of
storing data:
>
1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:
>
Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 ... any comment text
124 MZD13 XY55 ... any other comment
>
String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
...
123 8 GREEN
>
The device table, contains also a pointer (position), which might
change, to "hglight" specified position.
>
Then, I can very easly find all necessary data. The problem is, I need
to move the device record data (from other table) very often into other
history table (by each update). That will mean, that I also need to
move all these records from 1 -8 for example to a separate history
table, holding the index for a history device dataset. This is a little
inconvinience in this, and in my opinion, it will use to much storage
data, and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.
>
2. Table will be build nearly in this way:
>
Major device data table
ID AnyData1 AnyData2 ... AnyData3 stringProperty pointer
123 MZD12 XX77 ... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3
124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2
>
By writng into device table, there will be just a additional field for
this string, and I will have a function, which according to specified
pointer, will get me the string part on the fly, while I need it.
This will not require the other table, and will reduce the amout of
data, not a lot ... but always.
This solution, has a inconvinance, that it will be not so fast doing a
search over the part of this strings, while there will be no real index
on this.
If I woould like to search all devices, by which the curent pointer
value is equal GREEN, then I need to use function for getting the
value, and this one will be not indexed, means, by a lot amount of
data, might be slow.
>
I would like to know Your opinion about booth solutions.
Also, if you might point me the other problems with any of this
solution, I might not have noticed.
>
With Best Regards
>
Matik

|||First of all, thank you for your reply!

Now, some additional explenations maybe:

That was just an example, with 8 positions separated by semicolon as a
one property. The problem is, there number of this is various. That's
why, I couldyn't solve issue with fix number of column.

With shifting data into history, I've ment, that by each change of data
in primary table, whole record should be copied to the history table
(nearly same construction as primary table).
This is than an issue with the second table, storing semicolon
separated field in one column (splitted) in different table. This need
to be shifted then also, to a second historical table.

Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)
Having a big historical table, I will still need to get countinous
joins, to get the newest record, and even having a good indexing and
relation set up, it might be slow while table can be big.

This semicolon devided string, as example was shown pretty simmilar,
but it can be also various:

A10;B13;c20;bubu;lala;GREEN;RED
A13;BUBU;GREEN;YELLOW;mama
C25;YELLOW
BLUE;pleple;B13
aso.

The pointer I was talking about, is just a index, to which position in
this semicolon devided string, is curently activated.

Best regards

Matik|||Matik wrote:

Quote:

Originally Posted by

The problem is, I'm afraid a little of performance,

This has "premature optimization" written all over it. Build the
database cleanly first; then, if you /actually/ have performance
issues, then consider how to improve it (but breaking 1NF with "a;b;c"
type columns should still be a last resort).|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

With shifting data into history, I've ment, that by each change of data
in primary table, whole record should be copied to the history table
(nearly same construction as primary table).
This is than an issue with the second table, storing semicolon
separated field in one column (splitted) in different table. This need
to be shifted then also, to a second historical table.

I'm not sure that I see the problem. With a regular design, you would
have two tables for current data, and two tables for historical data.

Quote:

Originally Posted by

Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)

Like Ed said, get the design right first, and do performance tuning
when everything else is working. But some basic ideas for performance
are good when designing for performance. For instance no repeating
groups (i.e. semicolon-separated lists.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Matik wrote:

Quote:

Originally Posted by

>
Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)
Having a big historical table, I will still need to get countinous
joins, to get the newest record, and even having a good indexing and
relation set up, it might be slow while table can be big.
>

The way to optimise is with good indexes and good query design. You say
"it might be slow" so obviously you haven't reached that stage yet. On
the other hand you know for sure that a redundant copy of the data will
have an additional performance cost, both for updates and queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--