BEFORE Triggers: table

Showing posts with label table. Show all posts

Tuesday, March 27, 2012

Best solution, iterate over millions records and call extended sp

Hi,
I need to iterate over millions rows in a table and I need call an extended
stored procedure (written in C++ and not possible be written in TSQL) using
the columns of each row as parameters and write the return values to an new
table. The current script open a cursor.
What's the best way to implement it? BCP to a text file and external program
parse and write the text file and BCP back? sp_cmdshell an executible for
each row? (so needn't worry about C++ memory leak issu). XML?....
Thanks,"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,
I'd say the "best" solution would be to rethink your architecture and
implement it differently if you plan to do this on a regular basis. This
doesn't sound like a very scalable or desirable way to use a client-server
database. Have you considered using SQL Server 2005, where you can implement
.NET code in the database? Or implementing your code in ADO rather than wit
h
an XP?
If it's just a one-off requirement then you just need to test which approach
works best for you. It isn't really a SQL question since, for the purposes
of this exercise, you are just using SQL Server as a file dump rather than
what it was designed for. Why not just loop in TSQL and call the proc for
each row?
David Portas
SQL Server MVP
--|||If you have a situation that calls for looping through a cursor, then it's
better to implement the cursor on the client side than on the server. Open a
read-only, forward only ADO recordset and Command.Execute the stored
procedure for each row.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,sql

Best solution

Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.

ThanksNope, it's not making sense :-) Can you provide a small example with data to illustrate what you are trying to do?

Terri|||If I understand correctly, can you do several UNIONs and get them in turn?

Table1 join table 2 on col2 UNION
Table1 join table 2 on col3 UNION
Table1 join table 2 on col4 UNION
etc.

What it sounds like is that you should have a third table that contains a record for each possible combination of keys between table1 and table2. It sounds correcting the database structure is the best bet if you are able to do that.|||okay let me try :)

Let say I have a row that consist of the following:

TABLE 1:
key|ele1|ele2|ele3|ele4|ele5
1 6 2 5 null null

key column contains the rowID

ele1 - ele5 columns contain row IDs from the same table. ele1 is not nullable but the rest is nullable. I think if I use JOINS I will get an "ambigious error."

Table 2 ( ele ):
key|name |value
1 | "first" | 1
2 | "second" | 2
3 | "third" | 3 and so on.|||You should be able to accomplish what you need using JOINs with aliases.


SELECT
 table1.key,
 table2key.name,
 table2key.value,
 table1.ele1,
 table2ele1.name,
 table2ele1.value,
 table1.ele2,
 table2ele2.name,
 table2ele2.value,
 table1.ele3,
 table2ele3.name,
 table2ele3.value,
 table1.ele4,
 table2ele4.name,
 table2ele4.value,
 table1.ele5,
 table2ele5.name,
 table2ele5.value
FROM
 table1
LEFT OUTER JOIN
 table2 AS table2key ON table1.key = table2key.key
LEFT OUTER JOIN
 table2 AS table2ele1 ON table1.ele1 = table2ele1.key
LEFT OUTER JOIN
 table2 AS table2ele2 ON table1.ele2 = table2ele2.key
LEFT OUTER JOIN
 table2 AS table2ele3 ON table1.ele3 = table2ele3.key
LEFT OUTER JOIN
 table2 AS table2ele4 ON table1.ele4 = table2ele4.key
LEFT OUTER JOIN
 table2 AS table2ele5 ON table1.ele5 = table2ele5.key

Terri|||Thanks so much for all your help Terri!

Best SAN configuration for partitioned table

Hi,
I wondered if anyone had any thoughts on the best way to go about
splitting up a san array for a 500gb fact table. Its going to need to
be partitioned to allow for the overnight processing to complete on
time but what is the best way to split the san array for it.
I have about 10-13 san disks available for the table which leaves me
enough space on the other disks for the other database objects and
tempdb, logs etc.
The table will be partitioned into 13 logical weeks but would it be
best to allocate one disk per partition or have a 13 disk raid group
and put all 13 partitions on that and have it striped?
Any thoughts?
Thanks
Ian.
I am *far* from an expert, but here's my thoughts...
For having each partition on a separate disk or spindle, the question is are
you going to be using that partitioned table in parallel? Meaning, are you
going to be accessing or modifying several, if not all 13 weeks, at the same
time? If so, then it would make sense (depending on the processing power of
the SAN - EMC's DMX would be able to handle this all in parallel) to
separate the partitions into distinct spindles.
If you're not sure about parallel operations, then I would probably through
them into a JBOD (Just a Bunch Of Disks) set up like your second
alternative.
Here's my school of thought on this: You separate your TEMPDB (Very
important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
separate sets of spindles. That's usually a good starting point for an EMC
type of SAN. From there, you would then get into further tuning to see if
you would receive any additional benefit of separating objects (Tables,
Partitions, Indexes, etc) onto distinct spindles... remember you're
increasing management of the disk system when you do that, so it's helpful
to determine the benefit, if any.
FYI, I originated from the Oracle School of Thought, so it might clash with
some SQL Server admins ;)
JASON
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:1186730967.614276.138730@.z24g2000prh.googlegr oups.com...
> Hi,
> I wondered if anyone had any thoughts on the best way to go about
> splitting up a san array for a 500gb fact table. Its going to need to
> be partitioned to allow for the overnight processing to complete on
> time but what is the best way to split the san array for it.
> I have about 10-13 san disks available for the table which leaves me
> enough space on the other disks for the other database objects and
> tempdb, logs etc.
> The table will be partitioned into 13 logical weeks but would it be
> best to allocate one disk per partition or have a 13 disk raid group
> and put all 13 partitions on that and have it striped?
> Any thoughts?
> Thanks
> Ian.
>
|||Hi Ian,
thanks for your question around distributing data across spindles on a SAN.
Based on our experiences with many SQL 2005 data warehousing customers, I
would encourage you to distribute your data across all spindles. This should
give you good IO parallelism in case your query touches only one partition,
and it should also give you similarly high parallelism for a query that
touches many partitions. Depending on how large your data in a single
partition is and how many cores you have in your system, you want to avoid
cases where you exercise only one spindle and have many cores idle waiting
for data from the IO subsystem.
As Jason pointed out, it certainly makes sense to keep log and tempdb on
separate sets of spindles. You might want to experiment with index and data
on the same set of spindles. Usually, this already works sufficiently well as
compared to separate index and data. AS always, further tuning may be needed
depending on the characteristics of your workload.
Hope this makes sense and helps you with your SAN configuration.
Best regards,
Torsten Grabs
Program Manager
Microsoft SQL Server Query Processor
"Jason Fay" wrote:

> I am *far* from an expert, but here's my thoughts...
> For having each partition on a separate disk or spindle, the question is are
> you going to be using that partitioned table in parallel? Meaning, are you
> going to be accessing or modifying several, if not all 13 weeks, at the same
> time? If so, then it would make sense (depending on the processing power of
> the SAN - EMC's DMX would be able to handle this all in parallel) to
> separate the partitions into distinct spindles.
> If you're not sure about parallel operations, then I would probably through
> them into a JBOD (Just a Bunch Of Disks) set up like your second
> alternative.
> Here's my school of thought on this: You separate your TEMPDB (Very
> important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
> separate sets of spindles. That's usually a good starting point for an EMC
> type of SAN. From there, you would then get into further tuning to see if
> you would receive any additional benefit of separating objects (Tables,
> Partitions, Indexes, etc) onto distinct spindles... remember you're
> increasing management of the disk system when you do that, so it's helpful
> to determine the benefit, if any.
> FYI, I originated from the Oracle School of Thought, so it might clash with
> some SQL Server admins ;)
> --
> JASON
>
> "ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
> news:1186730967.614276.138730@.z24g2000prh.googlegr oups.com...
>
>

Best SAN configuration for partitioned table

Hi,
I wondered if anyone had any thoughts on the best way to go about
splitting up a san array for a 500gb fact table. Its going to need to
be partitioned to allow for the overnight processing to complete on
time but what is the best way to split the san array for it.
I have about 10-13 san disks available for the table which leaves me
enough space on the other disks for the other database objects and
tempdb, logs etc.
The table will be partitioned into 13 logical weeks but would it be
best to allocate one disk per partition or have a 13 disk raid group
and put all 13 partitions on that and have it striped?
Any thoughts?
Thanks
Ian.I am *far* from an expert, but here's my thoughts...
For having each partition on a separate disk or spindle, the question is are
you going to be using that partitioned table in parallel? Meaning, are you
going to be accessing or modifying several, if not all 13 weeks, at the same
time? If so, then it would make sense (depending on the processing power of
the SAN - EMC's DMX would be able to handle this all in parallel) to
separate the partitions into distinct spindles.
If you're not sure about parallel operations, then I would probably through
them into a JBOD (Just a Bunch Of Disks) set up like your second
alternative.
Here's my school of thought on this: You separate your TEMPDB (Very
important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
separate sets of spindles. That's usually a good starting point for an EMC
type of SAN. From there, you would then get into further tuning to see if
you would receive any additional benefit of separating objects (Tables,
Partitions, Indexes, etc) onto distinct spindles... remember you're
increasing management of the disk system when you do that, so it's helpful
to determine the benefit, if any.
FYI, I originated from the Oracle School of Thought, so it might clash with
some SQL Server admins ;)
JASON
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
> Hi,
> I wondered if anyone had any thoughts on the best way to go about
> splitting up a san array for a 500gb fact table. Its going to need to
> be partitioned to allow for the overnight processing to complete on
> time but what is the best way to split the san array for it.
> I have about 10-13 san disks available for the table which leaves me
> enough space on the other disks for the other database objects and
> tempdb, logs etc.
> The table will be partitioned into 13 logical weeks but would it be
> best to allocate one disk per partition or have a 13 disk raid group
> and put all 13 partitions on that and have it striped?
> Any thoughts?
> Thanks
> Ian.
>|||Hi Ian,
thanks for your question around distributing data across spindles on a SAN.
Based on our experiences with many SQL 2005 data warehousing customers, I
would encourage you to distribute your data across all spindles. This should
give you good IO parallelism in case your query touches only one partition,
and it should also give you similarly high parallelism for a query that
touches many partitions. Depending on how large your data in a single
partition is and how many cores you have in your system, you want to avoid
cases where you exercise only one spindle and have many cores idle waiting
for data from the IO subsystem.
As Jason pointed out, it certainly makes sense to keep log and tempdb on
separate sets of spindles. You might want to experiment with index and data
on the same set of spindles. Usually, this already works sufficiently well a
s
compared to separate index and data. AS always, further tuning may be needed
depending on the characteristics of your workload.
Hope this makes sense and helps you with your SAN configuration.
Best regards,
Torsten Grabs
Program Manager
Microsoft SQL Server Query Processor
"Jason Fay" wrote:

> I am *far* from an expert, but here's my thoughts...
> For having each partition on a separate disk or spindle, the question is a
re
> you going to be using that partitioned table in parallel? Meaning, are yo
u
> going to be accessing or modifying several, if not all 13 weeks, at the sa
me
> time? If so, then it would make sense (depending on the processing power
of
> the SAN - EMC's DMX would be able to handle this all in parallel) to
> separate the partitions into distinct spindles.
> If you're not sure about parallel operations, then I would probably throug
h
> them into a JBOD (Just a Bunch Of Disks) set up like your second
> alternative.
> Here's my school of thought on this: You separate your TEMPDB (Very
> important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES on
to
> separate sets of spindles. That's usually a good starting point for an EM
C
> type of SAN. From there, you would then get into further tuning to see if
> you would receive any additional benefit of separating objects (Tables,
> Partitions, Indexes, etc) onto distinct spindles... remember you're
> increasing management of the disk system when you do that, so it's helpful
> to determine the benefit, if any.
> FYI, I originated from the Oracle School of Thought, so it might clash wi
th
> some SQL Server admins ;)
> --
> JASON
>
> "ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
> news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
>
>sql

Best SAN configuration for partitioned table

Hi,
I wondered if anyone had any thoughts on the best way to go about
splitting up a san array for a 500gb fact table. Its going to need to
be partitioned to allow for the overnight processing to complete on
time but what is the best way to split the san array for it.
I have about 10-13 san disks available for the table which leaves me
enough space on the other disks for the other database objects and
tempdb, logs etc.
The table will be partitioned into 13 logical weeks but would it be
best to allocate one disk per partition or have a 13 disk raid group
and put all 13 partitions on that and have it striped?
Any thoughts?
Thanks
Ian.I am *far* from an expert, but here's my thoughts...
For having each partition on a separate disk or spindle, the question is are
you going to be using that partitioned table in parallel? Meaning, are you
going to be accessing or modifying several, if not all 13 weeks, at the same
time? If so, then it would make sense (depending on the processing power of
the SAN - EMC's DMX would be able to handle this all in parallel) to
separate the partitions into distinct spindles.
If you're not sure about parallel operations, then I would probably through
them into a JBOD (Just a Bunch Of Disks) set up like your second
alternative.
Here's my school of thought on this: You separate your TEMPDB (Very
important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
separate sets of spindles. That's usually a good starting point for an EMC
type of SAN. From there, you would then get into further tuning to see if
you would receive any additional benefit of separating objects (Tables,
Partitions, Indexes, etc) onto distinct spindles... remember you're
increasing management of the disk system when you do that, so it's helpful
to determine the benefit, if any.
FYI, I originated from the Oracle School of Thought, so it might clash with
some SQL Server admins ;)
--
JASON
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
> Hi,
> I wondered if anyone had any thoughts on the best way to go about
> splitting up a san array for a 500gb fact table. Its going to need to
> be partitioned to allow for the overnight processing to complete on
> time but what is the best way to split the san array for it.
> I have about 10-13 san disks available for the table which leaves me
> enough space on the other disks for the other database objects and
> tempdb, logs etc.
> The table will be partitioned into 13 logical weeks but would it be
> best to allocate one disk per partition or have a 13 disk raid group
> and put all 13 partitions on that and have it striped?
> Any thoughts?
> Thanks
> Ian.
>|||Hi Ian,
thanks for your question around distributing data across spindles on a SAN.
Based on our experiences with many SQL 2005 data warehousing customers, I
would encourage you to distribute your data across all spindles. This should
give you good IO parallelism in case your query touches only one partition,
and it should also give you similarly high parallelism for a query that
touches many partitions. Depending on how large your data in a single
partition is and how many cores you have in your system, you want to avoid
cases where you exercise only one spindle and have many cores idle waiting
for data from the IO subsystem.
As Jason pointed out, it certainly makes sense to keep log and tempdb on
separate sets of spindles. You might want to experiment with index and data
on the same set of spindles. Usually, this already works sufficiently well as
compared to separate index and data. AS always, further tuning may be needed
depending on the characteristics of your workload.
Hope this makes sense and helps you with your SAN configuration.
Best regards,
Torsten Grabs
Program Manager
Microsoft SQL Server Query Processor
"Jason Fay" wrote:
> I am *far* from an expert, but here's my thoughts...
> For having each partition on a separate disk or spindle, the question is are
> you going to be using that partitioned table in parallel? Meaning, are you
> going to be accessing or modifying several, if not all 13 weeks, at the same
> time? If so, then it would make sense (depending on the processing power of
> the SAN - EMC's DMX would be able to handle this all in parallel) to
> separate the partitions into distinct spindles.
> If you're not sure about parallel operations, then I would probably through
> them into a JBOD (Just a Bunch Of Disks) set up like your second
> alternative.
> Here's my school of thought on this: You separate your TEMPDB (Very
> important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
> separate sets of spindles. That's usually a good starting point for an EMC
> type of SAN. From there, you would then get into further tuning to see if
> you would receive any additional benefit of separating objects (Tables,
> Partitions, Indexes, etc) onto distinct spindles... remember you're
> increasing management of the disk system when you do that, so it's helpful
> to determine the benefit, if any.
> FYI, I originated from the Oracle School of Thought, so it might clash with
> some SQL Server admins ;)
> --
> JASON
>
> "ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
> news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
> > Hi,
> >
> > I wondered if anyone had any thoughts on the best way to go about
> > splitting up a san array for a 500gb fact table. Its going to need to
> > be partitioned to allow for the overnight processing to complete on
> > time but what is the best way to split the san array for it.
> >
> > I have about 10-13 san disks available for the table which leaves me
> > enough space on the other disks for the other database objects and
> > tempdb, logs etc.
> >
> > The table will be partitioned into 13 logical weeks but would it be
> > best to allocate one disk per partition or have a 13 disk raid group
> > and put all 13 partitions on that and have it striped?
> >
> > Any thoughts?
> >
> > Thanks
> >
> > Ian.
> >
>
>

Sunday, March 25, 2012

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 t
o
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via droptable.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via droptable.com wrote:

>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10
to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 to
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via droptable.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1
|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via droptable.com wrote:

>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10 to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

Best Query Strategy

I am faced with the need to query the price of parts from a 3 table join.
The problem is the number of parts that need to be queried at one time; 10 to
100 parts. That would make for a very messy WHERE clause. I am wonder if
there is a better strategy?
If it matters, I am using VB.NET.
Thanks
--Rob
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robin,
Are the parts PART OF a larger organizational unit i.e., a project or a
company or...?
HTH
Jerry
"Robin H via SQLMonster.com" <u4108@.uwe> wrote in message
news:5655fd7d2e20a@.uwe...
>I am faced with the need to query the price of parts from a 3 table join.
> The problem is the number of parts that need to be queried at one time; 10
> to
> 100 parts. That would make for a very messy WHERE clause. I am wonder if
> there is a better strategy?
> If it matters, I am using VB.NET.
> Thanks
> --Rob
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1|||Robin,
Is it possible you could do a VIEW for this as long as the query is not
dynamic?
Shahryar
Robin H via SQLMonster.com wrote:
>I am faced with the need to query the price of parts from a 3 table join.
>The problem is the number of parts that need to be queried at one time; 10 to
>100 parts. That would make for a very messy WHERE clause. I am wonder if
>there is a better strategy?
>If it matters, I am using VB.NET.
>Thanks
>--Rob
>
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.

Thursday, March 22, 2012

Best practices: changing values

What is the best way to design your tables in cases where field values change?

Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);

CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);

Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)

is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:

CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);

and have the Purchase table reference the ProductPrice table instead of the products table?

I have used both methods in the past, but I was wanted to get other peoples' take on it.

ThanksBecause price can change for many reasons, I always keep it in the actual transaction row. For example, you might have different prices for a given product based on quantity purchased (for example buying 100 units gets a price break). There might be reasons for different prices based on the customer (one price for wholesale, one for sub-contractors, another price for retail). These differences could be either discreet or cumulative. In short, the price in the inventory table might only be a starting point, the price in the transaction table is the authoritive price for a transaction.

-PatP|||If you want to be able to track historical prices, such as how much a price has changed over time, then you need to add a time dimension to your price table.
But for a financial application such as this there is no substitute to storing the actual price paid in the transation table.|||(which kind of de-normalizes it)

No, it doesn't. :) It's an attribute of the purchase.

The purchase table should have the price paid at time of purchase.

There should be a ProductPrice table that holds the price historically for each price. If you want to avoid duplicating data, you can put the ProductPriceID in the Purchase table so you have the exact price at the time purchase was made.

Tuesday, March 20, 2012

Best Practice: Procedures: (Insert And Update) OR JUST (Save)

I have a Product Table.

And now I have to create its Stored Procedures.

I am asking the best practice regarding the methods Insert And Update.

There are two options.

1. Create separate 2 procedures like InsertProduct and UpdateProduct.

2. Create just 1 procedure like ModifyProduct. In which programmatically check that either the record is present or not. If present then update and if not then insert. Just like Imar has done in his articlehttp://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419

Can any one explain the better one.

Waiting for helpful replies.

http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419

There's no "best practice" for this one. Imar presumably likes his "Save" approach because whether you are adding a new record or amending an existing one, generally software applications ask you to click the Save button - so he likes to make his programming logic analogous.

Personally, I prefer theKISS principal, and create 2 separate procedures. It's clear from the interface which one to call as a result of user action. I also see the decision as to whether to Insert or Update as being a business logic decision, and I'm uncomfortable about putting business logic in a stored procedure. The reason for this is that the business logic may not be transferable to another database platform.

|||

I do not understand your last point regardgin Business Logic.

I understand that it should be better in your opinion to create 2 separate procedures.

But what about Business Logic Methods.

|||

zeeshanuddinkhan@.hotmail.com:
I do not understand your last point regardgin Business Logic.

Well, I suppose it depends on how you define "Business Logic". And this illustrates one of the problems with layering an application. The reason why there are so many books and theories on architecture is because there is no "right" way to do it, and definitions of what belongs in which layer are different. Some things so obviously belong in certain layers, but other things might or might not - depending on what you are used to, how you think, what you are told to do by your team leader etc. There is for example, a huge debate about whether stored procedures are a bad thing altogether, because they can be viewed as placing business logic in a database and not in the BLL.

It also depends on how atomic (how much you like to break functionality down into discrete parts - methods, classes, procedures etc) you want your application. Imar would no doubt suggest that the action of the user defines that a Save() method be called, and that while the Save() method can include two alternative actions (Insert or Update), both lead to a row being saved to the database, so it's essentially the same action. The procedure decides whether an existing row is updated or a new one created. I see the difference between Insert and Update as being too different to be combined into one method. Consequently, I break the procedures apart into separate atomic constructs. I view the difference between the 2 as a business logic thing - because I can - and something in my gut tell me it is.

That's purely my view and is neither right or wrong. Others may not agree, and they will no doubt have valid justification for their view. It's right for me but wrong for Imar. And that's why I said at the beginning that there is no Best Practice for Insert or Update v Save. It's purely down to your personal preference. Imar's solution has a certain appeal, in that it contains a certain "cleverness". Some people like that. Nothing wrong with that at all.

Quite often the difference between two alternatives is purely philosophical, and has nothing to do with performance, maintainability or re-useability, which are the three items that Best Practice should be concerned with.

[Edit]

Just re-read my first response and having rambled on above, I see I may have missed your point. If you were asking about transferable business logic, it may be that you have to move the application to a different database system which doesn't support stored procedures, but may support basic INSERT, UPDATE, SELECT and DELETE saved queries. In this case, it wouldn't be too difficult to copy and paste the SQL form each part of the proc, but if you make procs do too much in terms of massaging data, or deciding on a course of action, you will create a load more work in your migration.

You are also perfectly free to ignore this on the basis that "it will never happen". Only you know best.

Best Practice: Primary key in joing table

hi there,

i have the following joining table (many-to-many relationship)...

CREATE TABLE [dbo].[products_to_products_swatch] (
[products_to_products_swatch_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[product_id] [int] NOT NULL ,
[products_swatch_id] [int] NOT NULL
) ON [PRIMARY]
GO

question: do i need to include a primary key in this table - being that it is a joing table?

thanks
mikejoing table ? i mean joining table :o)|||If this is simply implementing a many-to-many join, then there is no need for a surrogate key. Just declare a composite primary key consisting of the foreign keys to both tables.
If you are storing additional information regarding the relationship (timestamp, notes, modifier, whatever) you may want to include a surrogate key for developmental consistency with your other tables, but it is not required.

Monday, March 19, 2012

Best Practice when copy table from srv to srv

Hi!

My first post in this great forum. :)
Here goes:

I need some feedback on best practice (or just possible practice!) on creating a copy of a table from one SQLserver to another SQLserver.

I have a stored proc that loops some srv/databases/table-names and need to copy a specific table out to them all.

It works ok on the local server, but when i want to go across to another server trouble starts.

I have tried various approaches.

1) Linked server followed by "Insert into remotesrv.remotedb.dbo.tabel..."
result: cant run ALTER query in remote srv. SELECT statements works fine though.

2) Replication/Subscription
result: Works in general, but it only syncronizes alike tabels. Cant alter structure of table on remote.

3) DTS
result: Works fine, but not generic enough (variable tablenames needed).

What do you guys use in these situations?Ok no replys :)

For future reference I chose the following:

If fact 2) Replication/Subscription are open for alterations of tabel stucture.(I just needed to refresh my snapshot-file in the test)

The copy of tables are therefore done via replication triggerede by a stored procedure.

Best practice to pull data from sql server 2000 to sql server 2005 with dynamic queries

Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

If you need to query sql server 2000 database to migrate data, then sql server 2005 import/export wizard might be a good one. Please take a look at http://msdn2.microsoft.com/en-us/library/ms141209.aspx. If you don't need to do query, bcp utility might be a good candidate. Please take a look at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

Thanks,

Junfeng

|||

First, set up the SQL 2000 server as a 'Linked Server' for the SQL 2005 server (See Books Online for details about Linked Servers.)

Then, using 'four-part naming conventions', you can just query between the two servers.

This example, when executed on the SQL 2005 server, would take data from the SQL 2000 server and insert it into the SQL 2005 server:

Code Snippet

INSERT INTO MyTable (Col1, Col2, Col3, etc.)
SELECT Col1, Col2, Col3, etc.
FROM MySQL2000Server.MyDatabase.dbo.MyTable
WHERE MyID IN ( 1, 2, 5, 10, 25 )

|||

Hi There,

Here's my requirements...

I have to run a query B against database B with results (list of ids) from a query A run against database A and then push the results back to database A. Query B is constructed dynamically from the results obtained from Query A. So I wouldlike to know what would be the best way to achive this?

Thanks a lot!!

best practice retrieveing current identity value

HI all,
I have a claim table. An insert trigger, in that insert trigger I want to
retrieve the current identity value of that insert, to be used elswhere
which is best scope_identity(), ident_current(), or @.@.identity
Thanks
RobertRobert,
All three will (in theory) work, and each has it's own values for a given
situation.
I tend to use @.@.identity, though, as BoL explains, this is not limited to
the current scope, and so they would suggest using SCOPE_IDENTITY which woul
d
be more exacting.
Hope this assists,
Tony
"Robert Bravery" wrote:

> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>
>|||Robert Bravery wrote:
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
Did you read about the differences between those functions in Books
Online? Either may be appropriate depending on requirements.
BUT - a big but - they are all unlikely to be useful in a trigger.
That's because well-written trigger code should always assume that more
than one row may be updated in the table that invokes the trigger.
Triggers fire once per statement, NOT once per row, so usually in a
trigger if you want to retrieve the inserted IDENTITY values you do so
by referencing the virtual table called INSERTED. That table could
contain 0,1,2 or any number of rows.
The IDENTITY functions would probably only be useful in a trigger if
your trigger contained a single row INSERT to another table regardless
of how many rows were updated by the statement that prompted the
trigger. In that case you would probably use SCOPE_IDENTITY.
Hope this helps.
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/ms130214(en-US,SQL.90).aspx
--|||David,
Interesting point. Where would that leave us when using either
SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
correct identity was always returned from that thread used. Would you say
then that a trigger could be less precise than a SProc for retrieving the
correct Identity for a given inert transaction?
Any further insight would be useful,
Thanks,
Tony
"David Portas" wrote:

> Robert Bravery wrote:
> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
> The IDENTITY functions would probably only be useful in a trigger if
> your trigger contained a single row INSERT to another table regardless
> of how many rows were updated by the statement that prompted the
> trigger. In that case you would probably use SCOPE_IDENTITY.
> Hope this helps.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||Tony Scott wrote:
> David,
> Interesting point. Where would that leave us when using either
> SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
> correct identity was always returned from that thread used. Would you say
> then that a trigger could be less precise than a SProc for retrieving the
> correct Identity for a given inert transaction?
> Any further insight would be useful,
> Thanks,
> Tony
>
SCOPE_IDENTITY does always have local scope but so does the INSERTED
virtual table in a trigger. In that respect a trigger is no less
precise in the way IDENTITY is retrieved - it's just that you have to
allow for multiple-row inserts. Example:
CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
NULL UNIQUE)
GO
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT SCOPE_IDENTITY() AS [scope_identity] ;
SELECT @.@.IDENTITY AS [@.@.identity] ;
SELECT IDENT_CURRENT('T1') AS [ident_current] ;
GO
INSERT INTO T1 (z)
SELECT 1 UNION ALL
SELECT 2 ;
scope_identity
---
NULL
(1 row(s) affected)
@.@.identity
---
2
(1 row(s) affected)
ident_current
---
2
(1 row(s) affected)
The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
so it doesn't see the INSERT at all.
The second is wrong because although it returns one of the IDENTITY
values there were actually two rows inserted. We can't predict the row
for which the IDENTITY will be returned - it will just be the highest
numbered IDENTITY value. This isn't good in a trigger because we can
only use this method to reference a single row and triggers should
always assume multiple rows may be updated.
The third result may be wrong if other connections also update the
table because IDENT_CURRENT is scoped to the table and not to the
session.
The reliable method uses the INSERTED table:
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT x FROM inserted ;
GO
If your procs insert multiple rows you also need to think about the
same issue in procs when you need the IDENTITY value. Don't assume the
values inserted will be contiguous with the value returned by
SCOPE_IDENTITY. There are at least some conditions where they may not
be.
In SQL Server 2005 you can address things slightly differently using
the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
could eliminate the need for some triggers.
In SQL Server 2000 you should also be able to retrieve multiple
IDENTITY values using an alternate key of the table. There should
always be another candidate key. If you don't have such a key then you
have a significant design flaw which you should fix.
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/ms130214(en-US,SQL.90).aspx
--|||David,
Thank you for an excellent answer, very informative.
Tony
"David Portas" wrote:

> Tony Scott wrote:
> SCOPE_IDENTITY does always have local scope but so does the INSERTED
> virtual table in a trigger. In that respect a trigger is no less
> precise in the way IDENTITY is retrieved - it's just that you have to
> allow for multiple-row inserts. Example:
> CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
> NULL UNIQUE)
> GO
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT SCOPE_IDENTITY() AS [scope_identity] ;
> SELECT @.@.IDENTITY AS [@.@.identity] ;
> SELECT IDENT_CURRENT('T1') AS [ident_current] ;
> GO
> INSERT INTO T1 (z)
> SELECT 1 UNION ALL
> SELECT 2 ;
> scope_identity
> ---
> NULL
> (1 row(s) affected)
> @.@.identity
> ---
> 2
> (1 row(s) affected)
> ident_current
> ---
> 2
> (1 row(s) affected)
>
> The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
> so it doesn't see the INSERT at all.
> The second is wrong because although it returns one of the IDENTITY
> values there were actually two rows inserted. We can't predict the row
> for which the IDENTITY will be returned - it will just be the highest
> numbered IDENTITY value. This isn't good in a trigger because we can
> only use this method to reference a single row and triggers should
> always assume multiple rows may be updated.
> The third result may be wrong if other connections also update the
> table because IDENT_CURRENT is scoped to the table and not to the
> session.
> The reliable method uses the INSERTED table:
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT x FROM inserted ;
> GO
> If your procs insert multiple rows you also need to think about the
> same issue in procs when you need the IDENTITY value. Don't assume the
> values inserted will be contiguous with the value returned by
> SCOPE_IDENTITY. There are at least some conditions where they may not
> be.
> In SQL Server 2005 you can address things slightly differently using
> the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
> could eliminate the need for some triggers.
> In SQL Server 2000 you should also be able to retrieve multiple
> IDENTITY values using an alternate key of the table. There should
> always be another candidate key. If you don't have such a key then you
> have a significant design flaw which you should fix.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||Hi,
Thanks for the reply David.

> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
Yes I did. And I also got the idea that they all might be appropiate. But no
quite understanding the exact process of a insert trigger (as you explained)
I was

as to what was the more correct way

> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
At this point I am assumning a single row insert transaction. The table
involved is a claims table, needing a single input for each claim
as in:
INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
[cause], [resource], [fault], [DOL], [DREP])
VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
Would this be considered as a single row insert, on a single statement,
single transaction
I then issued:
Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
ident_current('claim') [ident_current()]
But got back all the same value.
But if I put the above statement into a trigger, I receive the dame values
from the @.@.identity and ident_current() functions. Scope_identity() returns
null. I would have thought that the insert trigger is within scope here
Hopefully I have explained correctly
Thankls
Robert|||Robert Bravery wrote:
> At this point I am assumning a single row insert transaction.
Why would you bother to assume that? It doesn't help you. It just means
the DBA will curse you one day when he needs to do some ad-hoc
maintenance... or integrate some external data... or the user needs an
enhancement to the application, or...

> The table
> involved is a claims table, needing a single input for each claim
> as in:
> INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
> [cause], [resource], [fault], [DOL], [DREP])
> VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
> Would this be considered as a single row insert, on a single statement,
> single transaction
> I then issued:
> Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
> ident_current('claim') [ident_current()]
> But got back all the same value.
> But if I put the above statement into a trigger, I receive the dame values
> from the @.@.identity and ident_current() functions. Scope_identity() return
s
> null. I would have thought that the insert trigger is within scope here
>
No. The trigger runs in its own scope.
The solution is:
SELECT id FROM inserted ;
"Inserted" is a virtual table only visible in triggers.
This isn't a good example because you don't usually want to return
results from triggers. The exact solution of course depends on what you
want to do with the IDENTITY value(s) after you retrieve them. For
example you can insert them to another table:
INSERT INTO other_table (id, ...)
SELECT id
FROM inserted ;
Hope this helps.
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/ms130214(en-US,SQL.90).aspx
--|||Unless you're using an INSTEAD OF INSERT trigger, then you shouldn't rely on
the value returned by @.@.IDENTITY; under no circumstances should you rely on
the value returned by IDENT_CURRENT()--at least not for what you're looking
for. There can be more than one FOR or AFTER INSERT trigger on a table, and
any one of them could affect @.@.IDENTITY. IDENT_CURRENT() changes whenever
any INSERT occurs on any connection, so it could change between the time
that you read it and the time that you're ready to use it, or even worse: it
could change during whatever modification you're trying to make. In a FOR
or AFTER trigger, the only reliable ways to retrieve the generated IDENTITY
values is to use the inserted pseudotable, or to use another candidate key.
An INSTEAD OF trigger fires instead of the action specified, so new IDENTITY
values haven't yet been generated at the time that the inserted pseudotable
is populated. You can use SCOPE_IDENTITY() if you use a cursor to process
the contents of the inserted pseudotable, but I would recommend against it.
Using cursors in triggers is like publishing uncomplimentary caricatures of
Mohammed: you're bound to get burned. In an INSTEAD OF INSERT trigger,
you're best bet is to use another candidate key to obtain the IDENTITY
values.
"Robert Bravery" <me@.u.com> wrote in message
news:u07T2i9KGHA.1424@.TK2MSFTNGP12.phx.gbl...
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>

Best practice for storing long text fields

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
Any advise will be well appreciated.
TzankoTzanko wrote:

Quote:

Originally Posted by

I hear that VARCHAR(MAX) is the new TEXT, but it's only available
in SQL 2005.|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

As we all know, there is a 8060 bytes size limit on SQL Server rows.

Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.

Quote:

Originally Posted by

I have a table which requires a number of text fields (5 or 6).

Do these text fields hold the same text that spans fields, or are
they different texts?

Quote:

Originally Posted by

I am given to review a design, which esentially suggests moving the text
columns to a separate TextFields table. The TextFields table will have
two columns - a unique reference and a VARCHAR (4000) column, thus
allowing us to crossreference with the original record.

If they are different texts they should be in different columns, or you
should have some type column telling them apatt.

Quote:

Originally Posted by

My first impresion is that I'd rather use the SQL Server 'text' DB type
instead, which would allow me the same functionality with much less
effort and possibly better performance.

Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.

But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.

If the columns are different texts, I see little point to use the
text data type.

--
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|||Are you saying that in SQL 2000 you can Span VarChar's into multiple columns
automatically? If so how?

Cheers, @.sh

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983CEED4849AEYazorman@.127.0.0.1...

Quote:

Originally Posted by

Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

>As we all know, there is a 8060 bytes size limit on SQL Server rows.

>
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
>

Quote:

Originally Posted by

>I have a table which requires a number of text fields (5 or 6).

>
Do these text fields hold the same text that spans fields, or are
they different texts?
>

Quote:

Originally Posted by

>I am given to review a design, which esentially suggests moving the text
>columns to a separate TextFields table. The TextFields table will have
>two columns - a unique reference and a VARCHAR (4000) column, thus
>allowing us to crossreference with the original record.

>
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
>

Quote:

Originally Posted by

>My first impresion is that I'd rather use the SQL Server 'text' DB type
>instead, which would allow me the same functionality with much less
>effort and possibly better performance.

>
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
>
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
>
If the columns are different texts, I see little point to use the
text data type.
>
>
>
--
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

|||@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

--
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|||Cool!

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

>Are you saying that in SQL 2000 you can Span VarChar's into multiple
>columns automatically? If so how?

>
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
>
>
--
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

|||Many thnaks for your replies.

Just to clarify the issue:
The requirement is to create a table that has say 6 columns which store
strings (such as Description, Notes, etc.) Each of these 6 columns
should store a char string of max length of 4000 characters. The
problem is that SQL Server 2000 will not work if I simply defined the
columns as varchar(4000) as at some point the row size reaches the page
size of 8060 and this generates an error. There is a 8060 bytes limit
on SQL Server 2000 rows. Note that I am not trying to store the same
string into 6 different columns spanning from column to column. I have
a separate string to store in each column.

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

Second question is what is the situation with SQL Server 2005. I
understand I can simply define the columns as varchar(max) and do not
have to do anything special. Has someone used this successfully and can
you confirm it ste case?

Thanks for your help.

Tzanko

@.sh wrote:

Quote:

Originally Posted by

Cool!
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.

If the key of the table is (cola, colb) the new tables should also have
the keys (cola, colb). Simply, what you do is that you split the columns
over several tables.

You should consider text or varchar(max) if you really need to fit more
than 8000 characters.
--
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

Thursday, March 8, 2012

Best practice BIT or SET('no','yes') ?

Hi,
I know it's quite common to use BIT fields for boolean values.
CREATE TABLE tblTest (
door BIT DEFAULT 0,
accept BIT DEFAULT 0
)
instead of:
CREATE TABLE tblTest (
door SET('close','open') DEFAULT 'close',
accept SET('no','yes') DEFAULT 'no'
)
(By the way, I use MSSQL and MySQL, not sure if I'm using the right
datatypes for MSSQL)
I reckon the first uses less storage space, but the meaning of the values in
the latter is more unanimous.
So what's best practice? I'm tempted to use the latter, but I almost always
see the first used everywhere.
I'm definitely interested in what CELKO has to say about this.
LisaMy opinion, FWIW
-Comarison operators with bit datatypes will be faster
-You don't have to worry about differences in collations when comparing
-More efficient storage on disk
-You could always add the unanimous-ness (which I'm positive is not a word!)
in a select that extracts the data out of these fields like
select case when door = 0 then 'closed' else 'open' end from tblTest
Proper T-SQL syntax for the character-based table would be
CREATE TABLE tblTest (
door varchar(5) CHECK (door IN ('close','open')) DEFAULT 'close',
accept varchar(5) CHECK (accept IN ('no','yes')) DEFAULT 'no'
)
--
"Lisa Pearlson" wrote:

> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||Here we use the latter one cuz too many different programmers working off
the same database and each programmer has their own programs that they are
responsible for.
I prefer to use the first one to save size.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:O1u6wZDKGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
> in the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost
> always see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>|||Lisa Pearlson wrote:

> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
My main objection to BIT is that its behaviour is too strange and
counter-intuitive. For example, can you predict or explain the results
of the following INSERT statement and the SELECT statement?
CREATE TABLE T (x BIT NOT NULL);
INSERT INTO T VALUES (2);
SELECT MAX(x) FROM T;
In most cases BIT's saving on storage is probably modest. I prefer
concise, readable status codes (CHAR(1) for example) and when I need to
add a third status I don't need to change the datatype I just change a
CHECK constraint.
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/ms130214(en-US,SQL.90).aspx
--|||Like David, I usually use a 1 character flag. In my experience, when I
initially think I have a binary status, it is really a multi-varied
flag. In other words, as soon as I code to tell if the door is open or
closed, someone else wants to know if it's locked.
Payson
Lisa Pearlson wrote:
> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa|||> In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
I love the analogy. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Payson" <payson_b@.hotmail.com> wrote in message
news:1138914380.158598.148590@.f14g2000cwb.googlegroups.com...
> Like David, I usually use a 1 character flag. In my experience, when I
> initially think I have a binary status, it is really a multi-varied
> flag. In other words, as soon as I code to tell if the door is open or
> closed, someone else wants to know if it's locked.
> Payson
> Lisa Pearlson wrote:
>|||Lisa--
Great question. One way of clearing this up is by naming your columns in a
way where it won't be ambiguous. If you know that a Door only has two
states, Open and Closed, the meaning will be clear with a BIT datatype if yo
u
use a convention like "IsDoorOpen". That way, it's obvious what "1" and "0"
mean. If it's possible that a door will have more than 2 states in the
future, use a single character {[O]pen, [C]losed, [A]jar} to represent the
state of the door. In that case, you might call your column DoorStatus, or
DoorState... Likewise, you can name your "Accept" column "IsAccepted" and
clarify the meaning there. At my company, we use "Is" to prefix just about
all of the boolean values that we put in our code for just this reason.
HTH
-Dave Markle
"Lisa Pearlson" wrote:

> Hi,
> I know it's quite common to use BIT fields for boolean values.
> CREATE TABLE tblTest (
> door BIT DEFAULT 0,
> accept BIT DEFAULT 0
> )
> instead of:
> CREATE TABLE tblTest (
> door SET('close','open') DEFAULT 'close',
> accept SET('no','yes') DEFAULT 'no'
> )
> (By the way, I use MSSQL and MySQL, not sure if I'm using the right
> datatypes for MSSQL)
> I reckon the first uses less storage space, but the meaning of the values
in
> the latter is more unanimous.
> So what's best practice? I'm tempted to use the latter, but I almost alway
s
> see the first used everywhere.
> I'm definitely interested in what CELKO has to say about this.
> Lisa
>
>|||>> In other words, as soon as I code to tell if the door is open or closed,
someone else wants to know if it's locked.
I love the analogy. :-) <
Me, too!! I think I will steal it in the next edition of one of my
books! Hey, Imitation is the sincerest form of flattery; Plagiarism is
the sincerest form of imitation!|||Stop writing code as if you were an assembly language programmer in
1957.
Machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.
Bits and Bytes are the lowest units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all!! What about NULLs? To be an
SQL datatype, you have to have NULLs, so what is a NULL bit? By
definition a bit, is on or off and has no NULL.
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently (look at C# and VB from the same vendor)? That means
all the host languages -- present, future and not-yet-defined.
Surely, no good programmer would ever write non-portable code by
getting to such a low level as bit fiddling!!
There are two situations in practice. Either the bits are individual
attributes or they are used as a vector to represent a single
attribute. In the case of a single attribute, the encoding is limited
to two values, which do not port to host languages or other SQLs,
cannot be easily understood by an end user, and which cannot be
expanded. Use CHAR(1) which will move.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young? Bingo!!
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what most newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
encoding schemes.|||An excellent post Celko. I wish more of your posts were like this.
i.e. You focused more on the solution and the reasons behind it than
insulting folks who are doing it wrong.
When you make posts like this one we can all learn a little bit, without
being offended in the process.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139090746.005907.99750@.g14g2000cwa.googlegroups.com...
> Stop writing code as if you were an assembly language programmer in
> 1957.
> Machine level things like a BIT or BYTE datatype have no place in a
> high level language like SQL. SQL is a high level language; it is
> abstract and defined without regard to PHYSICAL implementation. This
> basic principle of data modeling is called data abstraction.
> Bits and Bytes are the lowest units of hardware-specific,
> physical implementation you can get. Are you on a high-end or low-end
> machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
> complement or ones complement math? Hey, the standards allow decimal
> machines, so bits do not exist at all!! What about NULLs? To be an
> SQL datatype, you have to have NULLs, so what is a NULL bit? By
> definition a bit, is on or off and has no NULL.
> What does the implementation of the host languages do with bits? Did
> you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
> consistently (look at C# and VB from the same vendor)? That means
> all the host languages -- present, future and not-yet-defined.
> Surely, no good programmer would ever write non-portable code by
> getting to such a low level as bit fiddling!!
> There are two situations in practice. Either the bits are individual
> attributes or they are used as a vector to represent a single
> attribute. In the case of a single attribute, the encoding is limited
> to two values, which do not port to host languages or other SQLs,
> cannot be easily understood by an end user, and which cannot be
> expanded. Use CHAR(1) which will move.
> In the second case what some Newbies, who are still thinking in terms
> of second and third generation programming languages or even punch
> cards, do is build a vector for a series of "yes/no" status codes,
> failing to see the status vector as a single attribute. Did you ever
> play the children's game "20 Questions" when you were young? Bingo!!
> Imagine you have six components for a loan approval, so you allocate
> bits in your second generation model of the world. You have 64 possible
> vectors, but only 5 of them are valid (i.e. you cannot be rejected for
> bankruptcy and still have good credit). For your data integrity, you
> can:
> 1) Ignore the problem. This is actually what most newbies do.
> 2) Write elaborate CHECK() constraints with user defined functions or
> proprietary bit level library functions that cannot port and that run
> like cold glue.
> Now we add a 7-th condition to the vector -- which end does it go on?
> Why? How did you get it in the right place on all the possible
> hardware that it will ever use? Did all the code that references a bit
> in a word by its position do it right after the change?
> You need to sit down and think about how to design an encoding of the
> data that is high level, general enough to expand, abstract and
> portable. For example, is that loan approval a hierarchical code?
> concatenation code? vector code? etc? Did you provide codes for
> unknown, missing and N/A values? It is not easy to design such things!
> Get a copy of SQL PROGRAMMING STYLE and look at the chapters on design
> encoding schemes.
>

Best Practice - Lookup or SQL from Variable?

Hi,

I am pulling data from FoxPro tables into SQL 2005, and want to only pull new or changed rows. Accordingly each table in Fox has a column LastChangedDateTime, indicating the last time the row was updated, and I have a table in SQL which has one row per Fox table, listing the table name and the most recent data pulled into SQL.

In 2000 DTS I would have pulled the SQL datetime value into a package variable, then used a parameterized SQL statement with ".. WHERE LastChangedDateTime > ? " to select the rows I require.

In SSIS this approach does not seem to be possible, and the options are that I either use a variable for the entire SQL statement or, as the first SSIS tutorial suggests, use a lookup against the SQL table.

Gut feel is that the lookup will perform slower than creating the variable SQL and executing that (given that the source table is 13 million rows and rising, and I only want the last 100,000 or so from today).

What is considered best practice under these circumstances?

Also is it possible to write SSIS scripts in C# rather than VB.NET, as the syntax differences are driving me mad? ;-)

Thanks in advance,

Richard R

I would go with the DTS style method, it should work just fine. An Exec SQL Task can get the date value and store it in a variable. The variable can then be used in a parameterised query, in the same way as you did with DTS, but obviously using a Data Flow task, and the correct source. Saying that I have not tried it with FoxPro, but you will be using the same OLE-DB driver I assume so it should work fine. Parameter support is available in the OLE-DB Source, and the driver should support it if it did in DTS.

Using a lookup would not make sense as you will be doing far more work.
Using a variable for the command (with EvaluateAsExpression = True) is also perfectly valid, and sometimes the better choice, but for a simple query like this and since you have parameter support, I'd go with the former method, but there is nothing in it really.

The Script Task and Script Component both use Visual Studio for Applications (VSA), which means you get the power of .Net rather than a interpreted script language. Unfortunately VSA has only been implemented for VB.Net, there is no C# support. No idea if or when there will be either, but you are certainly not the first to raise the issue.

|||

Thanks Darren,

Sometimes it's good to check out a gut feeling - just in case the whole underlying system architecture has changed.

I'm not sure the FoxPro v9 driver OLEDB actually has parameter support, it didn't seem to work when I tried it, hence the original post. This is the first time I've had to interface to FoxPro, and there are definitely a few oddities about the process...

Regards,

Richard

Best Practice

Hi all,
We have a claims table in out database.
It has A child table which would hold different financial ammount with
regards to the claim loss, eg material dammage, third party, towing etc.
Each catergory of loss is a new row.
I am trying to see which is the best way of showing and storing the total
loss, whihc would be a combined summ of loss types for that particular
claim.
Should I summ theses amounts and store it into a column (perhaps TotalLoss)
of the parent (Claims) table.
Or should I be looking at another stratergy
Thanks
RobertUnless you have a performance issue, I would not store the calculation at
all. Compute it when needed in a select statement.
If you do have a performance problem and have exhausted all other approaches
to fix it, then you can store the sum in the claims table. You would then
have to make sure that it was always correct by using triggers to
re-claculate it every time a change was made to one of the loss rows.
"Robert Bravery" <me@.u.com> wrote in message
news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> We have a claims table in out database.
> It has A child table which would hold different financial ammount with
> regards to the claim loss, eg material dammage, third party, towing etc.
> Each catergory of loss is a new row.
> I am trying to see which is the best way of showing and storing the total
> loss, whihc would be a combined summ of loss types for that particular
> claim.
> Should I summ theses amounts and store it into a column (perhaps
> TotalLoss)
> of the parent (Claims) table.
> Or should I be looking at another stratergy
> Thanks
> Robert
>|||Hi Dave,
Thanks For the response
At the moment I don't have any performance issues, yet.
I have thought of the trigger option, but as I understand, the best option
is to compute it when needed.
Thanks
Robert
"Dave Frommer" <anti@.spam.com> wrote in message
news:OdQViULKGHA.668@.TK2MSFTNGP11.phx.gbl...
> Unless you have a performance issue, I would not store the calculation at
> all. Compute it when needed in a select statement.
> If you do have a performance problem and have exhausted all other
approaches
> to fix it, then you can store the sum in the claims table. You would then
> have to make sure that it was always correct by using triggers to
> re-claculate it every time a change was made to one of the loss rows.
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
total
>|||The only reason I see for storing a total at the claim level would be if
there was a concept of "loss reserve" or that amount which has been
allocated toward a claim. You can implement a 'view' that joins the claim
table with the claim line table and sums a total for all coverages.
"Robert Bravery" <me@.u.com> wrote in message
news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> We have a claims table in out database.
> It has A child table which would hold different financial ammount with
> regards to the claim loss, eg material dammage, third party, towing etc.
> Each catergory of loss is a new row.
> I am trying to see which is the best way of showing and storing the total
> loss, whihc would be a combined summ of loss types for that particular
> claim.
> Should I summ theses amounts and store it into a column (perhaps
> TotalLoss)
> of the parent (Claims) table.
> Or should I be looking at another stratergy
> Thanks
> Robert
>|||HI JT,
Yes youre right in some aspect. I would probably store the first estimate,
but that would be an only one first time thing
The rest of the time, I suppose could be done via a proper formated select
statement.
Thanks for the help in clearing the mind
Robert
"JT" <someone@.microsoft.com> wrote in message
news:u5QMYxMKGHA.1032@.TK2MSFTNGP11.phx.gbl...
> The only reason I see for storing a total at the claim level would be if
> there was a concept of "loss reserve" or that amount which has been
> allocated toward a claim. You can implement a 'view' that joins the claim
> table with the claim line table and sums a total for all coverages.
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23CZkw0KKGHA.2040@.TK2MSFTNGP14.phx.gbl...
total
>

Best practice

i have two databases on two different servers, one which is a live server.
What i want to do is run a query to update a table in the second server wit
h
records from the first database. would this be a select if not exists ?It can be an insert followed by a subquery which is based on a NOT exists, a
ssuming that you want to
add the rows that doesn't exist (based on some key column).
Or, it can be an update based on a JOIN (or an update with a number of corre
lated subqueries in SET,
as well as an EXISTS), if you want to update rows that already exists in the
other table, picking
column values from that other table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:B13DDBFC-4697-4DA6-B9C0-5F7A575BC8DF@.microsoft.com...
>i have two databases on two different servers, one which is a live server.
> What i want to do is run a query to update a table in the second server w
ith
> records from the first database. would this be a select if not exists ?
>

Tuesday, March 27, 2012

Best solution, iterate over millions records and call extended sp

Best solution

Best SAN configuration for partitioned table

Best SAN configuration for partitioned table

Best SAN configuration for partitioned table

Sunday, March 25, 2012

Best Query Strategy

Best Query Strategy

Best Query Strategy

Thursday, March 22, 2012

Best practices: changing values

Tuesday, March 20, 2012

Best Practice: Procedures: (Insert And Update) OR JUST (Save)

Best Practice: Primary key in joing table

Monday, March 19, 2012

Best Practice when copy table from srv to srv

Best practice to pull data from sql server 2000 to sql server 2005 with dynamic queries

best practice retrieveing current identity value

Best practice for storing long text fields

Thursday, March 8, 2012

Best practice BIT or SET('no','yes') ?

Best Practice - Lookup or SQL from Variable?

Best Practice

Best practice

BEFORE Triggers

Blog Archive

About Me