Determine updated and inserted rows

I'm working on an algorithm to backup data from pocket pc sql server 2.0
database involved in an merge replication with sql server 2000 sp3 with rda
in case that replication fails from whatever reason. My question is if I can
determine on a sql server ce 2.0 table involved in merge replication what
rows have been updated or inserted form last replication in order to
transfer only those rows on backup database. I've noticed that inserted rows
have s_Generation field 0 but I have no clue about updated rows.
Any changed (inserted or updated) row in ce database which is part of a
replicated table will have the s_generation column set to zero until the
next sync. So this is just an indicator of changed rows between two
successive syncs.
thanks
gopal

Determine next order id

I'm working on a sproc that determines the next order id for a specified customer. The table has

custid int,

ordernum varchar(10)

Data is:

1000, 1000-001

1000, 1000-002

1001, 1001-001

1000, 1000-003

I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).

I honestly don't know where to begin.

Can someone please help?

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(OderNumber,3) + 1 AS VARCHAR(6)),3)
FROm Orders
Order by OrderNumer
Where CustId = @.CustId

SELECT @.LASTOrdernNum = ISNULL(@.LASTOrdernNum,'001')

SELECT CAST(CustId as VARCHAR(4)) + '-' + LASTOrdernNum

Should b something like the above (untested).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

That was very close. Thanks very much.

Is there a way to return a string value? I won't be calling this SP with ExecuteScalar, so I'd like to RETURN the value instead of just selecting it.

Here's the working version:

Code Snippet

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

I can use RETURN on the last line instead of select, but my test code errors with:

Conversion failed when converting the varchar value '10052-002' to data type int.

|||

RETURN allows for only an integer operand, which is generally used as a status or return code.

You may want to consider creating a function rather than a procedure.

|||

ALTER PROCEDURE GetNextOrderID

(

@.custid INT,

@.retval varchar(255) output

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @.LastOrderNum VARCHAR(10)

SELECT TOP 1 @.LastOrderNum = RIGHT('00' + CAST(RIGHT(Ordernum,3) + 1 AS VARCHAR(6)),3)

FROM MFOrder

Where mforder.custid = @.custid

Order by Ordernum

SELECT @.LastOrderNum = ISNULL(@.LastOrderNum,'001')

SELECT @.retval = CAST(@.custid as VARCHAR(5)) + '-' + @.LastOrdernum

END

sql

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
T
Tina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:

> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>
|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...[vbcol=seagreen]
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
sql

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
TTina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:
> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
>> I'm developing a SQL Utility program intended to work with multiple
>> brands
>> of database. Right now we just want to get it working with SQL Server.
>> I
>> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
>> etc.
>> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
>> primary key is an Identity column.
>> Where can I look?
>> Thanks,
>> T
>>

detecting Identity in INFORMATION_SCHEMA

I'm developing a SQL Utility program intended to work with multiple brands
of database. Right now we just want to get it working with SQL Server. I
am using INFORMATION_SCHEMA to get information on tables, columns, keys,
etc.
But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
primary key is an Identity column.
Where can I look?
Thanks,
TTina,
Try using function columnproperty.
Example:
use northwind
go
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(quotename(TABLE
_SCHEMA) + '.' +
quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
go
AMB
"Tina" wrote:

> I'm developing a SQL Utility program intended to work with multiple brands
> of database. Right now we just want to get it working with SQL Server. I
> am using INFORMATION_SCHEMA to get information on tables, columns, keys,
> etc.
> But... I can't seem to find where the INFORMATION_SCHEMA tells me if a
> primary key is an Identity column.
> Where can I look?
> Thanks,
> T
>
>|||AMB,
Thanks. That worked great!
T
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6194C838-2DE4-44E0-A895-C99E57E0597A@.microsoft.com...[vbcol=seagreen]
> Tina,
> Try using function columnproperty.
> Example:
> use northwind
> go
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(quotename(TABLE
_SCHEMA) + '.' +
> quotename(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1
> go
>
> AMB
> "Tina" wrote:
>

Designing reports with Reproting Services

I'm no graphic designer but I am really struggling with making a decent looking report.

Does anyone have any ideas or tips when working with reporting services?

Hi,

I'm no expert either, but what works for me is designing a report template which has the basics, and use that as a starting place for all subsequent reports. The template contains a page footer which contains the date the report was run, the name of our office and "page # out of total pages".

Starting with this generic template I add a page header which contains the report name and any selection criteria which is input by the user at run-time. On to content: I output data in the order specified by users and use bold for all headings to increase readability. Where appropriate the Reporting Services' "groupby" feature also increases clarity by allowing you to suppress duplicate values on the print-out.

Hope this helps!

Design Question--partial Date data

Hi everyone,
I am working on an SQL Server 2K application that includes a requirement for
~20 "partial" Date fields (spread through several of the database tables).
By partial Date, I am referring to a Date where the Day portion is not
required, the Month portion or may or may not be required (depending on the
particular field), but the Year is always required. When either Month or Day
is not required, they _may_ be entered if known.
Clear so far? OK. I have considered creating a user-defined datatype
'pDate (int)' wherein the date data would be encoded e.g. 20070904 (today) or
20060000 (Year is 2006, Month and Day are unknown) or 19840500 (May 1984).
My thinking behind this approach is that 1.) ints are relatively compact, and
2.) Sort/compare should be easy, particularly since my client wants to use
the 'filing' rule ("Nothing always comes before something") so 19840430 <
19840500 < 19840501 ("May 1984" occurs just after "April 30, 1984" and just
before "May 1, 1984").
Then, I thought of applying a Rule to allow me to validate the data entry
(yyyy0230 is always invalid, but yyyy0229 is OK only in a leap year, etc.).
The beauty of this idea, at least on intial consideration, is that a
user-defined Rule can be associated with the user-defined pDate custom
datatype, and so I wouldn't have to associate (and, perhaps more importantly,
maintain) the same exact check constraint with each of the 20 instances of my
partial-Date fields.
By now you're probably already thinking "Rules are too simple--that won't
work." or "Rules are being deprecated by Microsoft--good luck when your
client upgrades to SQL Server 2008." If so, I agree...I think...but then I'm
a relative newbie, and so I'm not very certain of that.
Question 1: Do you know of a better way to create such a partial-Date data
type?
Question 2: If my 'pDate(int)' idea is reasonable, is there a way to define
a reusable (i.e. single-place definition) check constraint that can be
applied to each of these fields?
Furthermore, recall that some of these "pDate" fields will require a Month,
some will not. I am considering creating and then setting an extended
property on each such pDate(int) field as a way to identify this requirement
because, it seems to me, this might be a good way for my data entry
validation procedure to retrieve "on the fly" and act on this requirement as
desired.
Question 3: Is there a reason why such an extended property won't work?
Question 4: Can you think of a better way to implement such a
field-instance-dependent data validation requirement?
Based on past experience, I am probably missing something blatantly obvious
to an even slightly expert SQL Server developer, so any advice or suggestions
would be most welcome. Thanks in advance.
Best regards,
EdWhy not store Y (smallint), M (tinyint) and D (tinyint) in separate columns.
When M or D is NULL you can use COALESCE to still generate a valid date when
converting. And a separate column (or existing criteria) could be used to
dictate whether M/D can/should be NULL.
A|||Aaron,
Thanks for the suggestion; I think that's a great idea, not to mention much
simpler than where I was headed, and at no additional storage space cost!
Thank you.
Ed
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why not store Y (smallint), M (tinyint) and D (tinyint) in separate columns.
> When M or D is NULL you can use COALESCE to still generate a valid date when
> converting. And a separate column (or existing criteria) could be used to
> dictate whether M/D can/should be NULL.
> A

Design Issue

I posted this also in the Visual Basic section:

I'm working with: VB6 and SqlServer 2000

I'm creating an application that requires users to answer several questions and based on answers to those questions, certain conditions result, such as the ability to view certain sections of the questionaire and/or more questions appear and/or some questions become irrelavent and/or mathematical formulas work one way or another based on an answer to a question.

Here is an example:
The user has to answer a question. For this particular question, if the user chooses Yes then I must "Activate" all of the questions that are flagged with the code X. If the user chooses No then I must "Activate" all of the questions that are flagged with Y and "Deactivate" all of the questions that are flagged with Z.

I'm coming across a design issue (both with the application itself and the database). What is the best way to store all of this in a database? Should I store the questions along with the list of possible answers and their corresponding conditions in the database as well as the actual data for each record (each questionaire)? Should I be referencing the database to populate all of my questions on my VB form or should I manually put labels and combo boxes containing the questions and possible answers on my form? Or should I be using another technology to store my questions and conditions OR my results, such as XML?

I looking for suggestions. I want to optimize my program as much as possible. I don't want to slow it down by making several calls to the database, but I want to make sure I'm also taking away optimization by hardcoding everything.

Thanks for your time and help!Should I store the questions along with the list of possible answers and their corresponding conditions in the database as well as the actual data for each record (each questionaire)? Should I be referencing the database to populate all of my questions on my VB form or should I manually put labels and combo boxes containing the questions and possible answers on my form?
I would always choose storing reference data in a database over hard-coding in the application.|||blindman,

thanks for responding. i had a feeling that would be the best option. i'm still a bit confused on how to logically put everything in the database.

MORE INFO:

Picture an application with an explorer style view. On the left in folders are different sections of a questionaire. On the right side will be the corresponding questions to that section. Each section, each question and it's corresponding possible answers, the controls that they will be held in (VB) will all need to be in the database. I'm still confused on how to setup tables relating each part...There is the data that will be stored to set up the screen, then there is data that will be stored for each record (each questionaire's answers, so to speak).

Thanks again!|||questionaire
section
question
answers
...but what do you mean , "the controls that they will be held in (VB) will all need to be in the database"? I hope you are just referring to the lookup lists...|||i think i was thinking too much. Seeing your code example has helped me clear up a lot. Thank you!...I was adding in a bunch of tables that didn't need to be there. The structure above makes a lot more sense, and will work with all of the lookup tables that I'll need. Again Thank you!...it always helps to have another set of eyes looking at the same issue.|||Again Thank you!...it always helps to have another set of eyes looking at the same issue. blind ones at that

Design Discussion - How would YOU do this?

I'm working with data collection software and our current SQL solution is
simply not performing... well. I'm not a DB Admin, but I'm hoping to inject
useful suggestions into our next design meeting - hopefully improving things
across the board.
The situation is this:
I need to collect data about entities participating in a scenario. There can
be any number of participants, but a typical run might include 500-1000
unique entities. Each entity is streaming updates (XML) to a central
collector that is then moving the data into SQL server. While the actual
number may vary, we can safely assume that there will be at least one update
every second for each entity involved in the scenario.
The present solution is to examine the data as it is coming to the collector
and if it is new, adding it to a master table to maintain a 'current picture
'
of every entity in the scenario. If the entity is already in the master, we
simply update the record in the master table. Regardless of the action taken
,
all messages go into a history table that basically represents the entire
scenario.
The problems (I've not experienced them yet) described to me is that after
the tables become lare (again, this has yet to be defined) inserts are going
very slow and appear to be related to the size of the tables involved.
Furthermore, a decision was made to remove *ALL* index from the tables to
increase insert speed. This is (obviously) bad for a client application to
look at this data because queries are now resorting to table scans on large
tables.
What should I look to do to increase performance? Obviously it is important
that we capture all of the data and that it be inserted as quickly as
possible, but on the other hand if it takes hours to execute queries then
what good is it to have the data in the first place?
Has anyone seen case studies regarding a similiar scenario?
I've briefly reviewed documentation about partitioned views and such after
previously posting a simliar question, however I'm not sure how well that
would work as I can't know prior to the start of a scenario any data to
logically separate the tables.
Ideas? I'd love to hear them. As a developer for the client portion of this
software, I'm tired of suffering through table scans that can take an
inordinate amount of time to complete. (Indexing would help, but I get shot
down becase it makes inserts slow - or so the admin is telling me.)
Thanks for reading.
ChrisWhile you're busy preparing DDL and sample data, I'd just like to say that
removing indexes is no solution.
Based on what indexes you need to efficiently access your data you should
consider the fact how they are populated to allow for fast inserts (updates)
.
Basically what you need to do is - in your case where changes are propagated
to your tables at a high frequency - allow your index pages to be populated
with as little of repagination as possible.
See PAD_INDEX and FILLFACTOR under CREATE INDEX in Books Online.
Another issue is the issue of the clustered index. IMHO in a database where
inserts are frequent and it is of great importance that insert/update
performance is at its highest - clustered indexes should be created on
columns where any newly inserted value is larger than any previous value, so
that there is no need to repaginate the clustered index pages. I find the
identity column a wonderful candidate for the clustered index when based on
a
single column. If your clustered index (for reasons not known to me) needs t
o
be based on multiple columns try using columns, where at least half of those
have incremental values.
You haven't mentioned whether removing indexes actually resulted in an
increase of insert/update performance. Maybe you simply need new hardware :)
I guess we could say more after we see some DDL.
ML|||Disk space is cheap. Add a BIGINT IDENTITY column on the history table and
put a unique clustered index with a 100% fill factor on it. There is no
need as far as performance goes to partition this table, because inserts
will perform nearly the same if you have a million rows or if you have a
billion rows. Add a second table to keep track of the last history row
processed. Add a third table for reporting that has indexes to improve
query performance. Finally add a stored procedure that periodically
processes the new rows in the history table to populate the reporting
table.
Inserts into the history table with the clustered index will be nearly as
fast as inserts without any indexes because rows are only added to the end
of the table and the B-tree index maintenance is minimal. A new index page
is added when all index pages at the same level are full (which only occurs
every 400 or so rows) An index page is only updated when the last leaf page
fills up. Note that there isn't any page splitting going on if there is
only a clustered index on an IDENTITY column. The index grows up because
both leaf pages and index pages are always appended. There will be a few
more writes because of the BIGINT column and because of the occasional
addition of a new index page. If this is a problem, you could add a new
filegroup consisting of a separate set of mirrored disks just to store the
history table.
There will be a delay between the time that a row is inserted in the history
table and the time the data is available for reporting, but the lack of
indexes on the master table and the consequent table scans will almost
certainly cause query results to be delayed anyway. It takes on average 1.5
seconds on my development box (Dual 3GHz XEON, 4GB RAM, RAID-1) to scan a
typical table with 175,000 rows. Your table will have between 1.8 and 3.6
million rows inserted or updated every hour, so scans will definitely take a
lot more time. In any case, updates will perform abysmally without indexes
as the table grows because a scan is required to find the row to update.
You can make the periodic stored procedure populate the reporting table
using set-based operations to maximize performance. Set based operations
perform much better than row based operations because triggers are only
fired once, writes to the log are minimized, and index maintenance is
optimized.
I would hazard a guess that the delay incurred by caching the inserts and
updates will be far less than the delay caused by the table scans.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:55221D59-F4D9-4BD8-8FE3-41C64128DD45@.microsoft.com...
> I'm working with data collection software and our current SQL solution is
> simply not performing... well. I'm not a DB Admin, but I'm hoping to
inject
> useful suggestions into our next design meeting - hopefully improving
things
> across the board.
> The situation is this:
> I need to collect data about entities participating in a scenario. There
can
> be any number of participants, but a typical run might include 500-1000
> unique entities. Each entity is streaming updates (XML) to a central
> collector that is then moving the data into SQL server. While the actual
> number may vary, we can safely assume that there will be at least one
update
> every second for each entity involved in the scenario.
> The present solution is to examine the data as it is coming to the
collector
> and if it is new, adding it to a master table to maintain a 'current
picture'
> of every entity in the scenario. If the entity is already in the master,
we
> simply update the record in the master table. Regardless of the action
taken,
> all messages go into a history table that basically represents the entire
> scenario.
> The problems (I've not experienced them yet) described to me is that after
> the tables become lare (again, this has yet to be defined) inserts are
going
> very slow and appear to be related to the size of the tables involved.
> Furthermore, a decision was made to remove *ALL* index from the tables to
> increase insert speed. This is (obviously) bad for a client application to
> look at this data because queries are now resorting to table scans on
large
> tables.
> What should I look to do to increase performance? Obviously it is
important
> that we capture all of the data and that it be inserted as quickly as
> possible, but on the other hand if it takes hours to execute queries then
> what good is it to have the data in the first place?
> Has anyone seen case studies regarding a similiar scenario?
> I've briefly reviewed documentation about partitioned views and such after
> previously posting a simliar question, however I'm not sure how well that
> would work as I can't know prior to the start of a scenario any data to
> logically separate the tables.
> Ideas? I'd love to hear them. As a developer for the client portion of
this
> software, I'm tired of suffering through table scans that can take an
> inordinate amount of time to complete. (Indexing would help, but I get
shot
> down becase it makes inserts slow - or so the admin is telling me.)
> Thanks for reading.
> Chris
>|||You might coniser a tool designed for this kind of problem. Kx and
StreamBase are DBMS designed for capturing streaming data and doing
work on it while the data is streaming.|||Hi all
What will be the performance if logshipping is used instead.The Destination
server will be used for reporting purpose. Though log shipping is generally
used in lieu of back up, I feel we can reduce the latency and boost the
performance as the read/writes are done on different machines. Of course
adding another machine is a problem, but we need to do that for back up any
way.
any thoughts on this
Regards
R.D
"--CELKO--" wrote:

> You might coniser a tool designed for this kind of problem. Kx and
> StreamBase are DBMS designed for capturing streaming data and doing
> work on it while the data is streaming.
>|||Apologies for not responding earlier, I've been out of the net for a w or
so.
Thank you for the information - it really is confirming what I had thought
to be the case with how SQL server works. I could not understand why it woul
d
make a difference how many rows are in a table as everything I have read
indicated that it would not matter.
Next w will be interesting as I push back and try to get some of these
things implemented. In the meantime, if anyone has additional input to the
scenario, I'd love to hear from you.
Chris
"Brian Selzer" wrote:

> Disk space is cheap. Add a BIGINT IDENTITY column on the history table an
d
> put a unique clustered index with a 100% fill factor on it. There is no
> need as far as performance goes to partition this table, because inserts
> will perform nearly the same if you have a million rows or if you have a
> billion rows. Add a second table to keep track of the last history row
> processed. Add a third table for reporting that has indexes to improve
> query performance. Finally add a stored procedure that periodically
> processes the new rows in the history table to populate the reporting
> table.
> Inserts into the history table with the clustered index will be nearly as
> fast as inserts without any indexes because rows are only added to the end
> of the table and the B-tree index maintenance is minimal. A new index pag
e
> is added when all index pages at the same level are full (which only occur
s
> every 400 or so rows) An index page is only updated when the last leaf pa
ge
> fills up. Note that there isn't any page splitting going on if there is
> only a clustered index on an IDENTITY column. The index grows up because
> both leaf pages and index pages are always appended. There will be a few
> more writes because of the BIGINT column and because of the occasional
> addition of a new index page. If this is a problem, you could add a new
> filegroup consisting of a separate set of mirrored disks just to store the
> history table.
> There will be a delay between the time that a row is inserted in the histo
ry
> table and the time the data is available for reporting, but the lack of
> indexes on the master table and the consequent table scans will almost
> certainly cause query results to be delayed anyway. It takes on average 1
.5
> seconds on my development box (Dual 3GHz XEON, 4GB RAM, RAID-1) to scan a
> typical table with 175,000 rows. Your table will have between 1.8 and 3.6
> million rows inserted or updated every hour, so scans will definitely take
a
> lot more time. In any case, updates will perform abysmally without index
es
> as the table grows because a scan is required to find the row to update.
> You can make the periodic stored procedure populate the reporting table
> using set-based operations to maximize performance. Set based operations
> perform much better than row based operations because triggers are only
> fired once, writes to the log are minimized, and index maintenance is
> optimized.
> I would hazard a guess that the delay incurred by caching the inserts and
> updates will be far less than the delay caused by the table scans.
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:55221D59-F4D9-4BD8-8FE3-41C64128DD45@.microsoft.com...
> inject
> things
> can
> update
> collector
> picture'
> we
> taken,
> going
> large
> important
> this
> shot
>
>

Design and Performance question

Hi
I'm working now on a project that is Database oriented, and this
Database is so big and has millions of records, my question is:
Where must I put the business logic?
1- Client side: where client is responsible for the business logic.
2- Server Side: exceute the business logic in the server using stored
procedures and triggers...
3- Hybrid schema load balancing on the two sides.
I'm using MS.SQL Server2000
I have many of very complex queries, and many of complex views.
and finally can I depend on Jobs in MS.SQL Server in some tasks with
out impact the perfermance down.
Please reply ASAP
thanks a lot.
I try to put it where it is best suited.
Anything set based which acts on a large amount of data should be in stored
procs or performance will suffer.
Always interface with the db via SPs as you can monitor and correct problems
and change the database tructure without changing anything outside the db.
Anything not set based is up to you and may just depend on the avalability
of development expertise.
You can rely on jobs. Whether they mipact anything depends on what they do.
"Yousef Absee" wrote:

> Hi
> I'm working now on a project that is Database oriented, and this
> Database is so big and has millions of records, my question is:
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
> Please reply ASAP
> thanks a lot.
>
|||"Yousef Absee" <youstec@.gmail.com> wrote in message
news:1131554866.394769.115640@.g47g2000cwa.googlegr oups.com...
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
In the bizClasses that are called by the GUI and directly interact with the
db. If that is via metaData it's even better.

> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
Well sure, These rules may not be business rules but basic data handeling
instead. Triggers that generate data in other tables for necessary reasons
is a good thing.

> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
Depends on what the job is to do and what is happening at the same time?

Design and Performance question

Hi
I'm working now on a project that is Database oriented, and this
Database is so big and has millions of records, my question is:
Where must I put the business logic?
1- Client side: where client is responsible for the business logic.
2- Server Side: exceute the business logic in the server using stored
procedures and triggers...
3- Hybrid schema load balancing on the two sides.
I'm using MS.SQL Server2000
I have many of very complex queries, and many of complex views.
and finally can I depend on Jobs in MS.SQL Server in some tasks with
out impact the perfermance down.
Please reply ASAP
thanks a lot.I try to put it where it is best suited.
Anything set based which acts on a large amount of data should be in stored
procs or performance will suffer.
Always interface with the db via SPs as you can monitor and correct problems
and change the database tructure without changing anything outside the db.
Anything not set based is up to you and may just depend on the avalability
of development expertise.
You can rely on jobs. Whether they mipact anything depends on what they do.
"Yousef Absee" wrote:
> Hi
> I'm working now on a project that is Database oriented, and this
> Database is so big and has millions of records, my question is:
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
> Please reply ASAP
> thanks a lot.
>|||"Yousef Absee" <youstec@.gmail.com> wrote in message
news:1131554866.394769.115640@.g47g2000cwa.googlegroups.com...
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
In the bizClasses that are called by the GUI and directly interact with the
db. If that is via metaData it's even better.
> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
Well sure, These rules may not be business rules but basic data handeling
instead. Triggers that generate data in other tables for necessary reasons
is a good thing.
> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
Depends on what the job is to do and what is happening at the same time?

Design and Performance question

Hi
I'm working now on a project that is Database oriented, and this
Database is so big and has millions of records, my question is:
Where must I put the business logic?
1- Client side: where client is responsible for the business logic.
2- Server Side: exceute the business logic in the server using stored
procedures and triggers...
3- Hybrid schema load balancing on the two sides.
I'm using MS.SQL Server2000
I have many of very complex queries, and many of complex views.
and finally can I depend on Jobs in MS.SQL Server in some tasks with
out impact the perfermance down.
Please reply ASAP
thanks a lot.I try to put it where it is best suited.
Anything set based which acts on a large amount of data should be in stored
procs or performance will suffer.
Always interface with the db via SPs as you can monitor and correct problems
and change the database tructure without changing anything outside the db.
Anything not set based is up to you and may just depend on the avalability
of development expertise.
You can rely on jobs. Whether they mipact anything depends on what they do.
"Yousef Absee" wrote:

> Hi
> I'm working now on a project that is Database oriented, and this
> Database is so big and has millions of records, my question is:
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
> Please reply ASAP
> thanks a lot.
>|||"Yousef Absee" <youstec@.gmail.com> wrote in message
news:1131554866.394769.115640@.g47g2000cwa.googlegroups.com...
> Where must I put the business logic?
> 1- Client side: where client is responsible for the business logic.
In the bizClasses that are called by the GUI and directly interact with the
db. If that is via metaData it's even better.

> 2- Server Side: exceute the business logic in the server using stored
> procedures and triggers...
Well sure, These rules may not be business rules but basic data handeling
instead. Triggers that generate data in other tables for necessary reasons
is a good thing.

> 3- Hybrid schema load balancing on the two sides.
> I'm using MS.SQL Server2000
> I have many of very complex queries, and many of complex views.
> and finally can I depend on Jobs in MS.SQL Server in some tasks with
> out impact the perfermance down.
Depends on what the job is to do and what is happening at the same time?