RAID stripe size confusion

setting up a new ibm xserve server.
The SCSI serveRAID controller says to use 16kb stripe for generic
"transaction processing database" (not sql server specific). but the
IBM SQL server 2005 on xserve guide says to use 64kb stripes.
posts I have seen say to use scsi controller defaults unless a reason.
which should we choose?this is for RAID10 data drives.
THe redpaper 64kb setting up with a san which we do not have.
but 16k stripe size seems odd for sql server.sql

RAID stripe size confusion

setting up a new ibm xserve server.
The SCSI serveRAID controller says to use 16kb stripe for generic
"transaction processing database" (not sql server specific). but the
IBM SQL server 2005 on xserve guide says to use 64kb stripes.
posts I have seen say to use scsi controller defaults unless a reason.
which should we choose?this is for RAID10 data drives.
THe redpaper 64kb setting up with a san which we do not have.
but 16k stripe size seems odd for sql server.

QUOTED_IDENTIFIER & ANSI_NULLS

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.
Ted
I'm affraid you cannot. What is your concern?
"Ted Theo" <tedtheo@.gmail.com> wrote in message
news:4b68c64c-e5ab-4024-878b-dbeec155abee@.d21g2000prf.googlegroups.com...
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
|||Ted Theo (tedtheo@.gmail.com) writes:
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
There does not seem to be an option for this.
The reason they are there, is that these to set options are saved with
the procedure. I can understand that it is a bit of a nuisance. But since
Enterprise Manager incorrectly has these two off by default, it's
probably a good thing that QA includes them with the right setting. (But
it's not good that there is a SET OFF for one of them at the end.)
Personally, I don't find this a hassle, since I keep my code under source
control, and rarely have reason to script it from the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Dec 2, 5:58 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Ted Theo (tedt...@.gmail.com) writes:
> There does not seem to be an option for this.
>
it's just a bit of a nuisance like you said. i have more projects
that don't use source control (single dev projects) than ones that do
so i encounter it frequently. i have a high level understanding of
what both options accomplish and i haven't found a case where setting
them at the individual object level has been advantageous. maybe i'm
just missing that part.
it seems sql server management studio just turns these settings on
when scripting an object and doesn't turn them off. is there a way to
turn this behavior off in mgmt studio? is there a reason i wouldn't
want to do this?

> The reason they are there, is that these to set options are saved with
> the procedure. I can understand that it is a bit of a nuisance. But since
> Enterprise Manager incorrectly has these two off by default, it's
> probably a good thing that QA includes them with the right setting. (But
> it's not good that there is a SET OFF for one of them at the end.)
> Personally, I don't find this a hassle, since I keep my code under source
> control, and rarely have reason to script it from the database.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||>> is there a reason I wouldn't want to do this? <<
Conformance to ANSI/ISO Standards should be a goal in any shop, so you
would not turn off options that bring you to that goal. Why would you
want to write your own database language?
|||Ted Theo (tedtheo@.gmail.com) writes:
> it's just a bit of a nuisance like you said. i have more projects
> that don't use source control (single dev projects) than ones that do
> so i encounter it frequently. i have a high level understanding of
> what both options accomplish and i haven't found a case where setting
> them at the individual object level has been advantageous. maybe i'm
> just missing that part.
Like it or not, the settings of the two *are* saved with each procedure.
This is in difference from, say, ANSI_WARNINGS, where the run-time setting
of the two apply.
As for which of the two settings to use, keep in mind that there are
features in SQL Server that are not available if any of ANSI_NULLS
or QUOTED_IDENTIFIER are off:
o Indexed views and index on computed columns.
o Xquery.
o Queries involving linked servers (ANSI_NULLS only).
Of course, if you use default settings etc, there should never be any
reason to include these in the script, because it should be a rare
exception that you deliberately would create a procedure with any of
them off. (The only half-good reason I can think of is that you work
with dynamic SQL in several layers and nesting quotes is driving you
crazy. Turning off QUOTED_IDENTIFIERS permits you to use " as a string
delimiter as well to save your sanity.)

> it seems sql server management studio just turns these settings on
> when scripting an object and doesn't turn them off. is there a way to
> turn this behavior off in mgmt studio? is there a reason i wouldn't
> want to do this?
The fact that SSMS do not set them OFF, is probably my fault. I bitched
about that during the beta of SQL 2005.
No, neither SSMS appears to have an option for this, just like QA there
is only an option for controlling whether ANSI_PADDING should be
scripted tables.
The best I can suggest is that you file an suggestion to add such an
option on https://connect.microsoft.com/SQLServer/feedback/. If you do,
please post the URL. I may vote for it. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:29789bc2-91b0-4f7a-a593-547d76942965@.b15g2000hsa.googlegroups.com...
> Conformance to ANSI/ISO Standards should be a goal in any shop, so you
> would not turn off options that bring you to that goal. Why would you
> want to write your own database language?
The goal here is helping people with their queries, and NOT telling them
they should be a "BY THE BOOK" kind of STIFF like yourself.
Just FYI
sql

QUOTED_IDENTIFIER & ANSI_NULLS

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.
Ted
I'm affraid you cannot. What is your concern?
"Ted Theo" <tedtheo@.gmail.com> wrote in message
news:4b68c64c-e5ab-4024-878b-dbeec155abee@.d21g2000prf.googlegroups.com...
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
|||Ted Theo (tedtheo@.gmail.com) writes:
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
There does not seem to be an option for this.
The reason they are there, is that these to set options are saved with
the procedure. I can understand that it is a bit of a nuisance. But since
Enterprise Manager incorrectly has these two off by default, it's
probably a good thing that QA includes them with the right setting. (But
it's not good that there is a SET OFF for one of them at the end.)
Personally, I don't find this a hassle, since I keep my code under source
control, and rarely have reason to script it from the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Dec 2, 5:58 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Ted Theo (tedt...@.gmail.com) writes:
> There does not seem to be an option for this.
>
it's just a bit of a nuisance like you said. i have more projects
that don't use source control (single dev projects) than ones that do
so i encounter it frequently. i have a high level understanding of
what both options accomplish and i haven't found a case where setting
them at the individual object level has been advantageous. maybe i'm
just missing that part.
it seems sql server management studio just turns these settings on
when scripting an object and doesn't turn them off. is there a way to
turn this behavior off in mgmt studio? is there a reason i wouldn't
want to do this?

> The reason they are there, is that these to set options are saved with
> the procedure. I can understand that it is a bit of a nuisance. But since
> Enterprise Manager incorrectly has these two off by default, it's
> probably a good thing that QA includes them with the right setting. (But
> it's not good that there is a SET OFF for one of them at the end.)
> Personally, I don't find this a hassle, since I keep my code under source
> control, and rarely have reason to script it from the database.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||>> is there a reason I wouldn't want to do this? <<
Conformance to ANSI/ISO Standards should be a goal in any shop, so you
would not turn off options that bring you to that goal. Why would you
want to write your own database language?
|||Ted Theo (tedtheo@.gmail.com) writes:
> it's just a bit of a nuisance like you said. i have more projects
> that don't use source control (single dev projects) than ones that do
> so i encounter it frequently. i have a high level understanding of
> what both options accomplish and i haven't found a case where setting
> them at the individual object level has been advantageous. maybe i'm
> just missing that part.
Like it or not, the settings of the two *are* saved with each procedure.
This is in difference from, say, ANSI_WARNINGS, where the run-time setting
of the two apply.
As for which of the two settings to use, keep in mind that there are
features in SQL Server that are not available if any of ANSI_NULLS
or QUOTED_IDENTIFIER are off:
o Indexed views and index on computed columns.
o Xquery.
o Queries involving linked servers (ANSI_NULLS only).
Of course, if you use default settings etc, there should never be any
reason to include these in the script, because it should be a rare
exception that you deliberately would create a procedure with any of
them off. (The only half-good reason I can think of is that you work
with dynamic SQL in several layers and nesting quotes is driving you
crazy. Turning off QUOTED_IDENTIFIERS permits you to use " as a string
delimiter as well to save your sanity.)

> it seems sql server management studio just turns these settings on
> when scripting an object and doesn't turn them off. is there a way to
> turn this behavior off in mgmt studio? is there a reason i wouldn't
> want to do this?
The fact that SSMS do not set them OFF, is probably my fault. I bitched
about that during the beta of SQL 2005.
No, neither SSMS appears to have an option for this, just like QA there
is only an option for controlling whether ANSI_PADDING should be
scripted tables.
The best I can suggest is that you file an suggestion to add such an
option on https://connect.microsoft.com/SQLServer/feedback/. If you do,
please post the URL. I may vote for it. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:29789bc2-91b0-4f7a-a593-547d76942965@.b15g2000hsa.googlegroups.com...
> Conformance to ANSI/ISO Standards should be a goal in any shop, so you
> would not turn off options that bring you to that goal. Why would you
> want to write your own database language?
The goal here is helping people with their queries, and NOT telling them
they should be a "BY THE BOOK" kind of STIFF like yourself.
Just FYI

QUOTED_IDENTIFIER & ANSI_NULLS

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.Ted (tedtheo@.gmail.com) writes:

Quote:

Originally Posted by

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.

I repeat the answer I posted in another newsgroup in reponse to the same
question:

There does not seem to be an option for this.

The reason they are there, is that these to set options are saved with
the procedure. I can understand that it is a bit of a nuisance. But since
Enterprise Manager incorrectly has these two off by default, it's
probably a good thing that QA includes them with the right setting. (But
it's not good that there is a SET OFF for one of them at the end.)

Personally, I don't find this a hassle, since I keep my code under source
control, and rarely have reason to script it from the database.

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

Quoted Identifiers wrong while scripting

Hi all,
I am scripting out my database and it is setting Quoted Identifiers to ON in
the script just before my stored procs. I have Quoted Identifiers set to
off at the database level.
Is there some Stored Proc level script I have to run to have them turned off
so the script turns out correct?
Can't find anything in help.
?From the doc
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
ANSI_NULLS settings are captured and used for subsequent invocations of that
stored procedure.
Apparently it was created with the setting ON
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O$2%23p%23CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am scripting out my database and it is setting Quoted Identifiers to ON
in
> the script just before my stored procs. I have Quoted Identifiers set to
> off at the database level.
> Is there some Stored Proc level script I have to run to have them turned
off
> so the script turns out correct?
> Can't find anything in help.
> ?
>|||Grab the code from the proc, drop it, and re-create it manually adding SET
QUOTED_IDENTIFIER OFF to the beginning. Then when you script it, it should
be correct.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O$2#p#CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I am scripting out my database and it is setting Quoted Identifiers to ON
in
> the script just before my stored procs. I have Quoted Identifiers set to
> off at the database level.
> Is there some Stored Proc level script I have to run to have them turned
off
> so the script turns out correct?
> Can't find anything in help.
> ?
>|||Thanks,
Unfortunately, my stored procs were created two years ago, I routinely
script them out for use in an MSDE database and they seemed to have changed
somehow to use that setting and so I have no clue how they were recreated
with a setting that was never on. I just tried to recreate then to no avail
either.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uBpibIDuDHA.2132@.TK2MSFTNGP10.phx.gbl...
> From the doc
> When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
> ANSI_NULLS settings are captured and used for subsequent invocations of
that
> stored procedure.
> Apparently it was created with the setting ON
> "A" <agarrettbNOSPAM@.hotmail.com> wrote in message
> news:O$2%23p%23CuDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > I am scripting out my database and it is setting Quoted Identifiers to
ON
> in
> > the script just before my stored procs. I have Quoted Identifiers set
to
> > off at the database level.
> >
> > Is there some Stored Proc level script I have to run to have them turned
> off
> > so the script turns out correct?
> >
> > Can't find anything in help.
> >
> > ?
> >
> >
>

Quorum Disk Question.

We will be setting up a Windows 2000 SQL server on a two node 2003 Cluster
connected to an HP SAN.
Reading through the whitepapers and watching the webcast on how to setup
everything says to keep the data files on a seperate disk from the quorum
drive.
This will not be a problem, however we currently run 2 two node file and
print clusters one 2000 and one 2003 where we have the quorum drive the same
as the shared data and the cluster works fine and failsover correctly.
Question:
Why is it nessary or even best practice to seperate the quroum from the
data? I have searched high and low for an ansewer but the only thing I can
find is do not do it on SQL.
That is because if the controlling node cannot access the quorum drive in a
timely fashion, it will fail over to the other node. If this happens too
often, the entire cluster will go offline. With Windows 2003, Microsoft
changed its recommendations for MSDTC from allowing it on the quorum disk to
advising it be put on a separate set of physical disks. This is after
observing some configurations where very high DTC activity created timeout
issues that did force some clusters offline.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"DRay" <DavidRay@.discussions.microsoft.com> wrote in message
news:68868872-9EF0-4317-96A0-825B6F76C387@.microsoft.com...
> We will be setting up a Windows 2000 SQL server on a two node 2003 Cluster
> connected to an HP SAN.
> Reading through the whitepapers and watching the webcast on how to setup
> everything says to keep the data files on a seperate disk from the quorum
> drive.
> This will not be a problem, however we currently run 2 two node file and
> print clusters one 2000 and one 2003 where we have the quorum drive the
> same
> as the shared data and the cluster works fine and failsover correctly.
> Question:
> Why is it nessary or even best practice to seperate the quroum from the
> data? I have searched high and low for an ansewer but the only thing I can
> find is do not do it on SQL.
|||Thank you sir that answers my question.
David Ray
Systems Administrator
"Geoff N. Hiten" wrote:

> That is because if the controlling node cannot access the quorum drive in a
> timely fashion, it will fail over to the other node. If this happens too
> often, the entire cluster will go offline. With Windows 2003, Microsoft
> changed its recommendations for MSDTC from allowing it on the quorum disk to
> advising it be put on a separate set of physical disks. This is after
> observing some configurations where very high DTC activity created timeout
> issues that did force some clusters offline.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "DRay" <DavidRay@.discussions.microsoft.com> wrote in message
> news:68868872-9EF0-4317-96A0-825B6F76C387@.microsoft.com...
>
>

Quick question on 2005 relationship diagrams

sql 2005 management studio 2005
When building and setting up a table diagram with the sql 2005 tools, is
there a way to Distinguish between a left join, and inner join?
For the most part from a technical point of view, the two enforced
relationships are NOT different, but from a *developers* point of view, I
can thus infer that the designer of the database application will assume
that a child record needs to be created when you setup the relationship as
a (inner join).
And, of course a child record does NOT need to be created by the application
when the relationship is setup as a left join. For the most part here the
distinction between the two different joins is really ONLY for documentation
purposes to the developer(s), and does not change the actual referential
integrity enforced by the data engine.
Is there any means in the database diagramming tools to distinguish between
the above two types of joins?
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
The diagramming tool doesn't document joins; it documents constraints. You
would check the nullability of the referencing column to determine if you
would use an inner or outer join when retrieving data.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:OUPaEk8cIHA.4172@.TK2MSFTNGP02.phx.gbl...
sql 2005 management studio 2005
When building and setting up a table diagram with the sql 2005 tools, is
there a way to Distinguish between a left join, and inner join?
For the most part from a technical point of view, the two enforced
relationships are NOT different, but from a *developers* point of view, I
can thus infer that the designer of the database application will assume
that a child record needs to be created when you setup the relationship as
a (inner join).
And, of course a child record does NOT need to be created by the application
when the relationship is setup as a left join. For the most part here the
distinction between the two different joins is really ONLY for documentation
purposes to the developer(s), and does not change the actual referential
integrity enforced by the data engine.
Is there any means in the database diagramming tools to distinguish between
the above two types of joins?
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message

> One thing you can do is to add comments to the diagram. That would not
> cause the joins to be "automagically created" in any particular way, but
> it might assist the programmer.
>
Yes, the assisting of the developers at design time is the goal here. You
well grasp my reasons for wanting to note the type of join. And, by the way,
I am likely going to adopt your suggestion of placing comments in the
diagram.
Coming from a ms-access background, the Relationship designer *did*
distinguish between the two type of joins. (left join, and inner join). As I
said, this does nothing to the actual behavior of the data engine, but you
get 2 nice benefits.
1) when you do build joins, the query builder can create the join based on
your original design assumptions. (left, or inner)
2) Any other developer can *note* the design assumptions I made by simply
looking at the ER diagram. The big question this answers is does the
code/application assume that I have to create a child record or not when a
parent record is created?
Note that in a typical application, only about 10%, or even less of the
relationships assume that a child record must be created when a parent
record is made.
So, I just wanted a way to document and covey this issue to developers, and
comments in the diagram will have to do.
Thanks a bunch for the input...
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com

Quick question on 2005 relationship diagrams

sql 2005 management studio 2005
When building and setting up a table diagram with the sql 2005 tools, is
there a way to Distinguish between a left join, and inner join?
For the most part from a technical point of view, the two enforced
relationships are NOT different, but from a *developers* point of view, I
can thus infer that the designer of the database application will assume
that a child record needs to be created when you setup the relationship as
a (inner join).
And, of course a child record does NOT need to be created by the application
when the relationship is setup as a left join. For the most part here the
distinction between the two different joins is really ONLY for documentation
purposes to the developer(s), and does not change the actual referential
integrity enforced by the data engine.
Is there any means in the database diagramming tools to distinguish between
the above two types of joins?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.comShort answer: No.
Long answer:
The diagram is a representation of your tables and constraint. A foreign key (FK) constraint say
that a row in the referencing table cannot exist unless there's a value in the referenced table for
the FK column. A FK constraint do not say that you need a row in the referencing table in order for
a row in the referenced table to exist. That would be difficult, one row has to come before the
other!
One thing you can do is to add comments to the diagram. That would not cause the joins to be
"automagically created" in any particular way, but it might assist the programmer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:OUPaEk8cIHA.4172@.TK2MSFTNGP02.phx.gbl...
> sql 2005 management studio 2005
> When building and setting up a table diagram with the sql 2005 tools, is there a way to
> Distinguish between a left join, and inner join?
> For the most part from a technical point of view, the two enforced relationships are NOT
> different, but from a *developers* point of view, I can thus infer that the designer of the
> database application will assume that a child record needs to be created when you setup the
> relationship as a (inner join).
> And, of course a child record does NOT need to be created by the application when the relationship
> is setup as a left join. For the most part here the distinction between the two different joins is
> really ONLY for documentation purposes to the developer(s), and does not change the actual
> referential integrity enforced by the data engine.
> Is there any means in the database diagramming tools to distinguish between the above two types of
> joins?
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>|||The diagramming tool doesn't document joins; it documents constraints. You
would check the nullability of the referencing column to determine if you
would use an inner or outer join when retrieving data.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:OUPaEk8cIHA.4172@.TK2MSFTNGP02.phx.gbl...
sql 2005 management studio 2005
When building and setting up a table diagram with the sql 2005 tools, is
there a way to Distinguish between a left join, and inner join?
For the most part from a technical point of view, the two enforced
relationships are NOT different, but from a *developers* point of view, I
can thus infer that the designer of the database application will assume
that a child record needs to be created when you setup the relationship as
a (inner join).
And, of course a child record does NOT need to be created by the application
when the relationship is setup as a left join. For the most part here the
distinction between the two different joins is really ONLY for documentation
purposes to the developer(s), and does not change the actual referential
integrity enforced by the data engine.
Is there any means in the database diagramming tools to distinguish between
the above two types of joins?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
> One thing you can do is to add comments to the diagram. That would not
> cause the joins to be "automagically created" in any particular way, but
> it might assist the programmer.
>
Yes, the assisting of the developers at design time is the goal here. You
well grasp my reasons for wanting to note the type of join. And, by the way,
I am likely going to adopt your suggestion of placing comments in the
diagram.
Coming from a ms-access background, the Relationship designer *did*
distinguish between the two type of joins. (left join, and inner join). As I
said, this does nothing to the actual behavior of the data engine, but you
get 2 nice benefits.
1) when you do build joins, the query builder can create the join based on
your original design assumptions. (left, or inner)
2) Any other developer can *note* the design assumptions I made by simply
looking at the ER diagram. The big question this answers is does the
code/application assume that I have to create a child record or not when a
parent record is created?
Note that in a typical application, only about 10%, or even less of the
relationships assume that a child record must be created when a parent
record is made.
So, I just wanted a way to document and covey this issue to developers, and
comments in the diagram will have to do.
Thanks a bunch for the input...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com|||> Coming from a ms-access background, the Relationship designer *did*
> distinguish between the two type of joins. (left join, and inner join). As I
> said, this does nothing to the actual behavior of the data engine
It is an interesting concept to add meta-data for this type of information so that tools like
query-builder can be smarter (guess what type of join to perform). The place for this would be the
system tables, exposed through catalog views. Possibly as extended properties, assuming that tool
vendors could agree on the structure...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:%23taEL4%23cIHA.3736@.TK2MSFTNGP04.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message
>
>> One thing you can do is to add comments to the diagram. That would not
>> cause the joins to be "automagically created" in any particular way, but
>> it might assist the programmer.
> Yes, the assisting of the developers at design time is the goal here. You
> well grasp my reasons for wanting to note the type of join. And, by the way,
> I am likely going to adopt your suggestion of placing comments in the
> diagram.
> Coming from a ms-access background, the Relationship designer *did*
> distinguish between the two type of joins. (left join, and inner join). As I
> said, this does nothing to the actual behavior of the data engine, but you
> get 2 nice benefits.
> 1) when you do build joins, the query builder can create the join based on
> your original design assumptions. (left, or inner)
> 2) Any other developer can *note* the design assumptions I made by simply
> looking at the ER diagram. The big question this answers is does the
> code/application assume that I have to create a child record or not when a
> parent record is created?
> Note that in a typical application, only about 10%, or even less of the
> relationships assume that a child record must be created when a parent
> record is made.
> So, I just wanted a way to document and covey this issue to developers, and
> comments in the diagram will have to do.
> Thanks a bunch for the input...
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>
>

Queued Updating Subscribers Question

When I looked into setting this up I received a message that an identity column would be added to all my tables for this type of replication.
Wouldn't this result in my having to change all code that touches these tables to take the new column into account?
Queued Updating is new to me, I am trying to learn the best replication option for our reporting database, but am a little confused.
Any/All help is appreciated!
Thanx!
JLS,
if the identity column is already there on the publisher, it is transferred
to the subscriber but no new identity columns will be created. On each
indetity column the column is designated as Identity Yes (Not for
Replication). This ensures that the replication process can insert values
into the column, and for an insert on the subscriber itself SQL Server can
have values allocated as per normal. To avoid clashes, identity ranges are
allocated to publisher and each subscriber, each node having different
seeds; these ranges and the allocating of new ranges is configurable at the
publication level.
HTH,
Paul Ibison
|||I'm sorry Paul, I don't follow. I have been setting up and tearing down
replication every which way from Sunday, so everything is sort of running
together.
I changed all my identity columns on the Subscriber to Yes(Not for
Replication), I don't really have any issue here. It is my understanding
that what happens here is the value from the Publisher is popped into this
field on the Subscriber, and that's the way I would want it to work as I
don't intend to have any updates occurring on the Subscriber.
When I selected Queued Updating as an option, in the Identity warning screen
I saw a new warning that stated a new column would be added, and every table
I am replicating was listed, and the new column is a replication column.
The warning also stated that this may cause INSERT to fail & cause the table
to become larger.
Can you explain this in "For Dummies who haven't had enough coffee yet this
morning" terms?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23dXpbRuTEHA.1548@.TK2MSFTNGP11.phx.gbl...
> JLS,
> if the identity column is already there on the publisher, it is
transferred
> to the subscriber but no new identity columns will be created. On each
> indetity column the column is designated as Identity Yes (Not for
> Replication). This ensures that the replication process can insert values
> into the column, and for an insert on the subscriber itself SQL Server can
> have values allocated as per normal. To avoid clashes, identity ranges are
> allocated to publisher and each subscriber, each node having different
> seeds; these ranges and the allocating of new ranges is configurable at
the
> publication level.
> HTH,
> Paul Ibison
>
|||JLS,
the column you are referring to is not an identity column -
it is a GUID. This is added and may cause tsql to fail
when it doesn't have an explicit column list eg
insert into table1
select * from replicatetable
If you are using Queued Updating Subscribers and are
letting replication do the initialization for you, you
don't need to alter identity columns on the subscriber -
they'll be set correctly for you.
HTH,
Paul Ibison
ps if you don't intend having subscribers update the data,
then why not use standard transactional replication?
|||Ah, ok I get it now. Thanx!!!!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1ae5301c44eee$b71dc120$a001280a@.phx.gbl...
> JLS,
> the column you are referring to is not an identity column -
> it is a GUID. This is added and may cause tsql to fail
> when it doesn't have an explicit column list eg
> insert into table1
> select * from replicatetable
> If you are using Queued Updating Subscribers and are
> letting replication do the initialization for you, you
> don't need to alter identity columns on the subscriber -
> they'll be set correctly for you.
> HTH,
> Paul Ibison
> ps if you don't intend having subscribers update the data,
> then why not use standard transactional replication?
>