Best Practices Analyzer Tool for MSSQL 2000

Hi!
The tool is ver useful, but I need the document in which are explained the
Best Practices for developing in SQL Server and how you coul implementate
them in data bases...
Thanks,
Sandra
http://www.extremeexperts.com/SQL/Ar...Practices.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Sandra" <special_sand@.hotmail.com-do not spam-> wrote in message
news:1069B09E-B4BE-4E80-B083-D0E0199E28AA@.microsoft.com...
> Hi!
> The tool is ver useful, but I need the document in which are explained the
> Best Practices for developing in SQL Server and how you coul implementate
> them in data bases...
> --
> Thanks,
> Sandra

Best Practice for Structuring XML?

I've created a large XML document from a relational database (using AUTO,
EXPLICIT, etc.) with many elements, attributes, and subelements but now
wonder if there is a "best practice" for designing the structure for going
the other way, XML -> relational. Since I have not yet worked on the data
extraction side, maybe what I've put together makes data extraction awkward
(requiring many lines of T-SQL vs. one or two). But I definitely cannot
stomach the 'all attribute' or 'all element' practices. Between the two
examples below, which is better/easier/more efficient/flexible for
retrieving information (e.g. with OPENXML). I like the first example
theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
ELEMENTS). Thanks for any tips or insights.
<entities>
<entity>
<entityAttribute>nameOfThisEntity</entityAttribute>
<entityValue>valueOfThisEntity</entityValue>
</entity>
<entity>
<entityAttribute>nameOfNextEntity</entityAttribute>
<entityValue>valueOfNextEntity</entityValue>
</entity>
<entity>
...
</entity>
</entities>
vs.
<entities>
<nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
<nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
...
</entities>
Hi Don,
I preferred to the first one, although I do not think there will be much
performance difference between the following two XML structures. The fist
XML structure will be more readable and efficient for search. The following
article will tell you how to optimize SQLXML performance for databases,
including SQL Server 2000.
SQLXML best practice paper on MSDN
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sqlxml_optimperformance.asp?frame=true
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||If you plan on using OpenXML, then size and ability to query structure
instead of values will most likely make your second format perform better.
Best regards
Michael
"Don Miller" <nospam@.nospam.com> wrote in message
news:es8k9YfLEHA.2396@.TK2MSFTNGP12.phx.gbl...
> I've created a large XML document from a relational database (using AUTO,
> EXPLICIT, etc.) with many elements, attributes, and subelements but now
> wonder if there is a "best practice" for designing the structure for going
> the other way, XML -> relational. Since I have not yet worked on the data
> extraction side, maybe what I've put together makes data extraction
> awkward
> (requiring many lines of T-SQL vs. one or two). But I definitely cannot
> stomach the 'all attribute' or 'all element' practices. Between the two
> examples below, which is better/easier/more efficient/flexible for
> retrieving information (e.g. with OPENXML). I like the first example
> theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
> ELEMENTS). Thanks for any tips or insights.
> <entities>
> <entity>
> <entityAttribute>nameOfThisEntity</entityAttribute>
> <entityValue>valueOfThisEntity</entityValue>
> </entity>
> <entity>
> <entityAttribute>nameOfNextEntity</entityAttribute>
> <entityValue>valueOfNextEntity</entityValue>
> </entity>
> <entity>
> ...
> </entity>
> </entities>
> vs.
> <entities>
> <nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
> <nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
> ...
> </entities>
>

Best Method to Document Database?

I have SQL Server 2005 Developer edition. What is the best way to create a
data dictionary for an existing database? Does reporting services or other
tools provide canned reports that include all objects including keys,
constraints etc? Do I need to write my own queries to generate the meta
data or do I need to use another vendor's custom app?
txOn Wed, 22 Feb 2006 12:03:34 -0800, Simon Shutter wrote:

> I have SQL Server 2005 Developer edition. What is the best way to create
a
> data dictionary for an existing database? Does reporting services or othe
r
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
Personally, I'd use another vendors too... ApexSQL Doc is what I'm
currently evaluating|||Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create
a
> data dictionary for an existing database? Does reporting services or othe
r
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
You might want to try SchemaToDoc at http://www.schematodoc.com. It's
output is a Word document that includes information about your tables
(primary keys), fields (type, size, defaults, nullable), indexes, check
constraints, foreign key constraints, stored procedures, and views. It
also lets you annotate your tables and fields, and include those
annotations in the Word output.

Best Method to Document Database?

I have SQL Server 2005 Developer edition. What is the best way to create a
data dictionary for an existing database? Does reporting services or other
tools provide canned reports that include all objects including keys,
constraints etc? Do I need to write my own queries to generate the meta
data or do I need to use another vendor's custom app?
txOn Wed, 22 Feb 2006 12:03:34 -0800, Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
Personally, I'd use another vendors too... ApexSQL Doc is what I'm
currently evaluating|||Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
You might want to try SchemaToDoc at http://www.schematodoc.com. It's
output is a Word document that includes information about your tables
(primary keys), fields (type, size, defaults, nullable), indexes, check
constraints, foreign key constraints, stored procedures, and views. It
also lets you annotate your tables and fields, and include those
annotations in the Word output.

Best Method to Document Database?

I have SQL Server 2005 Developer edition. What is the best way to create a
data dictionary for an existing database? Does reporting services or other
tools provide canned reports that include all objects including keys,
constraints etc? Do I need to write my own queries to generate the meta
data or do I need to use another vendor's custom app?
tx
On Wed, 22 Feb 2006 12:03:34 -0800, Simon Shutter wrote:

> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
Personally, I'd use another vendors too... ApexSQL Doc is what I'm
currently evaluating
|||Simon Shutter wrote:
> I have SQL Server 2005 Developer edition. What is the best way to create a
> data dictionary for an existing database? Does reporting services or other
> tools provide canned reports that include all objects including keys,
> constraints etc? Do I need to write my own queries to generate the meta
> data or do I need to use another vendor's custom app?
> tx
You might want to try SchemaToDoc at http://www.schematodoc.com. It's
output is a Word document that includes information about your tables
(primary keys), fields (type, size, defaults, nullable), indexes, check
constraints, foreign key constraints, stored procedures, and views. It
also lets you annotate your tables and fields, and include those
annotations in the Word output.

BEGINNER: simple Delete trigger

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END

Unfortunatelly I receive errors which I don't understand:

Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 12
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 13
The multi-part identifier "Documents.Numer" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 15
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 16
The multi-part identifier "Documents.Number" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 18
The multi-part identifier "Documents.Movement" could not be bound.
Msg 4104, Level 16, State 1, Procedure DeleteDocument, Line 19
The multi-part identifier "Dokuments.Number" could not be bound.

Please help to correct the code.
Thank you very much!
/RAM/How to forbid deleting Positions if Documents.WasDeleted bit is not
set?
Please help.
/RAM/|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Hello,
I am trying to learn SQL Server. I need to write a trigger which
deletes positions of the document depending on the movement type.
Here's my code:
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
>
CREATE TRIGGER [DeleteDocument]
ON [dbo].[Documents]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Movement = 'WZ' OR Documents.Movement = 'RW'
DELETE FROM PositionsWZRW
WHERE Documents.Number IN (SELECT Number FROM deleted);
IF Documents.Ruch = 'MM'
DELETE FROM PositionsMM
WHERE Documents.Number IN (SELECT Number FROM deleted);
END
>
Unfortunatelly I receive errors which I don't understand:

I understand the errors, but I understand about as little of your
trigger that SQL Server does. You seem to be making things up out of
thin air. When you say:

IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

What are Documenty and Documents supposed to be? Maybe you mean

IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

The same goes for

DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);

This would compile if you have a column Documents in PositionsPZZW,
and this columns is of a CLR UDT and had an attribute named Number.
What this really should be, I don't even want to guess, since I know
nothing about PositiosnPZZW.

The standarad recommendation is that you post:

o CREATE TABLE statements for your tables.
o INSERT statments with sample data.
o In this case: a sample DELETE statement.
o The desired result given the sample.

It also helps to give a little more detailed description of the problem.

By the way, why are there three Positions tables? Maybe there is a good
reason for this, but I have a suspicion that one should do.

--
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|||On Thu, 6 Jul 2006 08:25:27 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>I understand the errors, but I understand about as little of your
>trigger that SQL Server does. You seem to be making things up out of
>thin air. When you say:
>
IF Documenty.Movement = 'PZ' OR Documents.Movement = 'ZW'

I meant Documents.Movement

Quote:

Originally Posted by

>
>What are Documenty and Documents supposed to be? Maybe you mean
>
IF EXISTS (SELECT *
FROM deleted
WHERE movement IN ('PZ', 'ZW'))

Exactly

Quote:

Originally Posted by

>
>
>The same goes for
>
DELETE FROM PositionsPZZW
WHERE Documents.Number IN (SELECT Number FROM deleted);
>
>This would compile if you have a column Documents in PositionsPZZW,
>and this columns is of a CLR UDT and had an attribute named Number.
>What this really should be, I don't even want to guess, since I know
>nothing about PositiosnPZZW.

I need:
IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
DELETE FROM PositionsPZZW
WHERE Number IN (SELECT Number FROM deleted);

Quote:

Originally Posted by

>By the way, why are there three Positions tables? Maybe there is a good
>reason for this, but I have a suspicion that one should do.

They have different columns describing items.

Thank you, you have helped me... Problem closed
Could you help me with post "one more question"? Thank you!
/RAM/|||Sorry, too short problem description.
Anyway, I solved.
/RAM/|||R.A.M.,
What was the solution you found? Please post as others might have a
simular problem.
TIA
Rob

R.A.M. wrote:

Quote:

Originally Posted by

Sorry, too short problem description.
Anyway, I solved.
/RAM/

|||On Thu, 06 Jul 2006 10:46:50 +0200, R.A.M. <r_ahimsa_m@.poczta.onet.pl>
wrote:

Quote:

Originally Posted by

>IF EXISTS (SELECT * FROM deleted WHERE Movement IN ('PZ', 'ZW'))
>DELETE FROM PositionsPZZW
>WHERE Number IN (SELECT Number FROM deleted);

That looks dangerous. If one row in DELETED has a 'PZ' value, all
rows in PositionsPZZW that match DELETED will be dropped, even those
that do NOT have 'PZ' or 'ZW'.

How about this alternative:

DELETE FROM PositionsPZZW
WHERE Number IN
(SELECT Number FROM deleted WHERE Movement IN ('PZ', 'ZW'));

It does not require the IF test at all, as if there are no matches it
will do nothing.

Roy Harvey
Beacon Falls, CT|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

Could you help me with post "one more question"? Thank you!

If you repost it, and clarify what you mean. I understood very little
of it.

--
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|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

But the code implies some design problems. What are the logical
differences among
PositionsPZZW, PositionsWZRW and PositionsMM ? This looks like
attribute splitting.

Why are you using triggers instead of DRI actions?|||On 6 Jul 2006 04:39:13 -0700, "rcamarda" <robc390@.hotmail.comwrote:

Quote:

Originally Posted by

>What was the solution you found? Please post as others might have a
>simular problem.
>TIA
>Rob

I decided not to use WasDeleted flag in Documents, so it was enough to
set Delete Rule in FK_Positions_Documents to "No Action".
/RAM/|||BEGINNER: simple Delete trigger