Forcing Primary Keys

Hi all,

As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.

the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.

Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.

So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.

Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.

the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?

gregCreate a composite primary key of student ID and year number.|||thought so,
ta

greg|||Why do you keep enrollment info (a record for each year of enrollment) in the master table? StudentID should be the only PK in StudentsMaster, and Enrollment should have StudentID as FK.|||Could you create views for each year and put a unique index on each view?|||Yes, you CAN.
No, you SHOULDN'T.|||rdjabarov its not my design, its just the way the company programmed it, its a very bad system, ive alreday had to weed out 400+ tables that werent being used, and it seems instead of introducing foreign keys to child tables they used the studentId and the SetId,

peterlemonjello, i didnt know you could do that, well at least in sql server 2000, thought it was a 2005 feature...ill look into that

blindman, i had read it wasn't a good idea...ill think of an alternative

greg|||Where ever did you read that? Tables need primary keys, and if they don't have a natural unary key then you either create a surrogate key or use a composite key. Creating indexed views would be an odd alternative.|||well this is the thing, im not trying to fix the db so it functions- im just truying to analyse the relationships between tables and see how much faster introducing keys and indexes make my queries run...

as you can imagine the company released the software with no primary keys and expect it to work but im not about to try and fix there mistakes...its purely for my own use...

i really cant believe they have released software like this but i have to work with what i inhereted off my predecessor

greg|||It will run faster if it is indexed, especially clustered indexes as associated with primary keys.
No need to test this concept...

What's more, you can throw indexes on it without affecting the functioning of the operation. You cannot throw constraints on the tables (unique indexes, for example, or primary keys) without potentially causing failures in the crappy code which is doubtless used to access the data.|||Hmmm, really? I wouldn't be so certain, especially without seeing the database, and without knowing what indexes are to be created and what their definitions are. I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).|||Nothing is certain in life except death and taxes, but the benefits of indexing a table come damn close.|||In general that might be true, but then you find a table with 947 indexes, all of which have the first seven columns... Then discover that only the leftmost index column is ever used in queries!

-PatP|||Yeah, yeah,...|||I've seen "index seek" being more expensive than table scan on multiple occasions (of course because of the poor db and/or query design).The only time I've seen this is as a result of parameter sniffing. Are there other reasons this can occur? ... actually thinking about it now I guess a poorly chosen index (e.g. low selectivity) and an equally poor plan on the part of the optimiser might cause this.

BTW - I am probably just being a pedant but if there are no primary keys then there are no relationships. You will not be investigating the relationships of the tables - you will be creating the relationships. I imagine this is not helpful to the issue in hand at all :)|||Hi all,

yes bit of a can of worms here, to summarize it is the relationships im interested in, i wanna see how the tables should be connected by matching up similar indexes so although ill be cretaing the relationships, as most tables only have one index, it should be pretty close to the original design...

the problem is i need to prove to the management that my systems (access mde's,ade's accessing sql backend) are faster than the db we pay for because there is no primary keys or relationships..and was hoping that by recreating the relationships i could run speed tests to compare against...

cheers

greg|||Relationships don't affect the speed of your db directly. Relationships are logical constraints - they merely ensure your data conforms to certain constraints. As such - you are quite likely to find a fair slew of invalid intries in your tables since these contraints have not existed previously.

However - relationships are typically between primary and foreign keys. Both of these should be indexed. It is these indexes that should be likely to improve the speed of your queries.

HTH

for xml path reverse?

just started playing with xml datatype, and I'm running into a performance issue...

I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...

select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x

Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...

Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?

thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?

There are a couple of things you can try doing.

Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]

Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||

Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

Also, as a performance hint: You may want to use

where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')

which can give you better performance than doing the cast into SQL and then the comparison.

Best regards
Michael

|||hi..

rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...

changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...

adding a for path index made very little difference ( < 5%)

CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH

Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

we have generic data audit triggers that look like this:

insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.

the select described above is used to get the audit trail of changes to a row.

we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...

thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?

If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).

Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||

Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.

The optimization can apply to your case:

1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||PugV

Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.

Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!

for xml path reverse?

just started playing with xml datatype, and I'm running into a performance issue...

I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...

select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x

Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...

Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?

thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?

There are a couple of things you can try doing.

Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]

Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||

Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

Also, as a performance hint: You may want to use

where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')

which can give you better performance than doing the cast into SQL and then the comparison.

Best regards
Michael

|||hi..

rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...

changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...

adding a for path index made very little difference ( < 5%)

CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH

Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

we have generic data audit triggers that look like this:

insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.

the select described above is used to get the audit trail of changes to a row.

we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...

thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?

If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).

Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||

Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.

The optimization can apply to your case:

1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||PugV

Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.

Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!

FOR XML AUTO returns incomplete xml

I have two SQL tables that are populated based on data in SQL system tables.
When I run the FOR XML AUTO select statement on these tables, certain fields
will be missing end tags AND the data will not all be returned. In some
cases the end tags are there but the data is incomplete. When running the
query against the actual system table I'll get similar results but not
exactly the same. Any suggestions?
Using sql 2000 sp3. SQLXML 3 sp2
--master..sysaltfiles table data. Return only a few db's and an incomplete
select rtrim(filename) filename from mridiag..tbldbfiles for xml auto
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Data.MDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\Dont-Do-This_Log.LDF"/>
<mridiag..tbldbfiles filename="C:\Program Files\Microsoft SQL
Server\MSSQL$CLIENT\data\master.mdf
NT\data\pubs_log.ldf
(24 row(s) affected)
--master..sysprocesses table. Only returns 1 incomplete record
select LASTWAITTYPE test from master..sysprocesses for xml auto
<master..sysprocesses test="SLEEP
(15 row(s) affected)
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
>I have two SQL tables that are populated based on data in SQL system
>tables.
> When I run the FOR XML AUTO select statement on these tables, certain
> fields
> will be missing end tags AND the data will not all be returned. In some
> cases the end tags are there but the data is incomplete. When running the
> query against the actual system table I'll get similar results but not
> exactly the same. Any suggestions?
What client are you using to retrieve the results?
You might also check this FAQ:
http://sqlxml.org/faqs.aspx?faq=76
Bryant
|||Thanks for the response.
I reviewed the link to the FAQ and compared it to how I'm doing it. First,
the information I posted was using Query Analyzer but I get the same results
when executing it from my vb script.
I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
link I based my code on is below. It's basically the same as the "VB
Example" on the faq you pointed me to but the version of XML on the FAQ is
3.0 and the version used in my script is 4.0. Other than that, I can't see
any differences.
Any more suggestions or questions? It really has me puzzled.
Thanks again.
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||Here is the link I mentioned.
http://www.sqlxml.org/faqs.aspx?faq=10
"Bryant Likes" wrote:

> "TMcC" <TMcC@.discussions.microsoft.com> wrote in message
> news:B6CB3F5F-9790-4D97-9FB7-660DC8A8981E@.microsoft.com...
> What client are you using to retrieve the results?
> You might also check this FAQ:
> http://sqlxml.org/faqs.aspx?faq=76
> --
> Bryant
>
>
|||The query analyzer is using ODBC and not the OLEDB stream object and thus
only get junked XML back. Also, unless you increase the number of bytes
displayed per line, it does drop information.
If you are using the SQLOLEDB stream interface, you should get the XML back.
Can you try it with the SQLXML HTTP component to see if the XML is correctly
generated by the FOR XML query?
Thanks
Michael
"TMcC" <TMcC@.discussions.microsoft.com> wrote in message
news:9679959F-BC15-48CC-B4F9-7B521D883DCC@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> I reviewed the link to the FAQ and compared it to how I'm doing it.
> First,
> the information I posted was using Query Analyzer but I get the same
> results
> when executing it from my vb script.
> I am using SQLOLEDB provider and strems. I'm using VB Script not VB. The
> link I based my code on is below. It's basically the same as the "VB
> Example" on the faq you pointed me to but the version of XML on the FAQ is
> 3.0 and the version used in my script is 4.0. Other than that, I can't
> see
> any differences.
> Any more suggestions or questions? It really has me puzzled.
> Thanks again.
> "Bryant Likes" wrote:

For XML -> ADO Recordset

I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
results of a SELECT...FOR XML statement. The SQL I use is:
http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
FOR XML AUTO,XMLDATA&root=Policies
This returns the Schema and Data, which I capture in strXML, and try to load
as follows:
Dim rs As New ADODB.Recordset
Dim s As New ADODB.Stream
...
s.Open
s.WriteText strXML
s.Position = 0
rs.Open s
I get the following Error on the last line:
"Recordset cannot be created from the Specified source. The source file or
stream must contain recordset data in XML if ADTG format."
I have also tried to save the XML to a file, and then do the following:
rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
This gives me the following error:
Recordset cannot be created. Source XML is incomplete or invalid.
The XML from the file loads into IE without error.
Is what I am trying to do possible without much trouble, or will I need to
use MSXML? Any FAQs or articles you could point me to would be appreciated.
TIA
Mike
Mike see my response to "A Mindboggingly simple question" Below
Basically this should do what you want
Sub SaveXml()
Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=."
oCmd.CommandText = "SQL_First"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "c:\temp\results.xml"
End Sub
Obviously if you dont want to persist it you can simply stream the oDom.xml
Hope this helps
Graham
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
> results of a SELECT...FOR XML statement. The SQL I use is:
> http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
> FOR XML AUTO,XMLDATA&root=Policies
> This returns the Schema and Data, which I capture in strXML, and try to
load
> as follows:
> Dim rs As New ADODB.Recordset
> Dim s As New ADODB.Stream
> ...
> s.Open
> s.WriteText strXML
> s.Position = 0
> rs.Open s
> I get the following Error on the last line:
> "Recordset cannot be created from the Specified source. The source file
or
> stream must contain recordset data in XML if ADTG format."
> I have also tried to save the XML to a file, and then do the following:
> rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
> This gives me the following error:
> Recordset cannot be created. Source XML is incomplete or invalid.
> The XML from the file loads into IE without error.
> Is what I am trying to do possible without much trouble, or will I need to
> use MSXML? Any FAQs or articles you could point me to would be
appreciated.
> TIA
> --
> Mike
>
|||Graham:
I tried it, and am getting an error still. The Code is as follows:
Dim oCmd As Command
Dim oDom As IXMLDOMDocument2
Dim rs As New ADODB.Recordset
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
' Added next line to add a root node
oCmd.Properties("xml root") = "root"
oCmd.Execute , , 1024
oDom.save "f:\junk\results.xml"
' I get error "Recordset cannot be created. Source XML is incomplete or
invalid." on next line (err # -2147467259)
' although the xml loads into IE
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
Employees_sp source:
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees FOR XML AUTO, XMLDATA
I am using ADO 2.8
Any thoughts?
Thanks
Mike
"Graham Shaw" <Graham@.somewhere.com> wrote in message
news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> Mike see my response to "A Mindboggingly simple question" Below
> Basically this should do what you want
> Sub SaveXml()
> Dim oCmd As Command
> Dim oPrm As Parameter
> Dim oDom As IXMLDOMDocument2
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
> Source=."
> oCmd.CommandText = "SQL_First"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> oCmd.Execute , , 1024
> oDom.Save "c:\temp\results.xml"
> End Sub
> Obviously if you dont want to persist it you can simply stream the
oDom.xml[vbcol=seagreen]
> Hope this helps
> Graham
> "Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
> news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
PolicyID='FPHM016182'[vbcol=seagreen]
> load
> or
to
> appreciated.
>
|||Mike,
The xml you are getting is not a persisted recordset it is simply pure xml
therefore you can't load it into a recordset. If all you want is a recordset
then just use a plain sp e.g.
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees
Dim oCmd As Command
Dim rs As New ADODB.Recordset
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
set rs=oCmd.Execute( )
then you can save the resulting recordset as xml with
rs.save "f:\junk\result.xml", 1
rs.close
and later do
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:uR922v%23cEHA.3632@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Graham:
> I tried it, and am getting an error still. The Code is as follows:
> Dim oCmd As Command
> Dim oDom As IXMLDOMDocument2
> Dim rs As New ADODB.Recordset
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
> "Security=SSPI;Persist Security Info=False;Initial
> Catalog=Northwind;Data " & _
> "Source=tcs2003s"
> oCmd.CommandText = "Employees_sp"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> ' Added next line to add a root node
> oCmd.Properties("xml root") = "root"
> oCmd.Execute , , 1024
> oDom.save "f:\junk\results.xml"
> ' I get error "Recordset cannot be created. Source XML is incomplete or
> invalid." on next line (err # -2147467259)
> ' although the xml loads into IE
> rs.Open "f:\junk\results.xml", "Provider=MSPersist"
> Employees_sp source:
> CREATE PROCEDURE Employees_sp
> AS
> SELECT * FROM Employees FOR XML AUTO, XMLDATA
> I am using ADO 2.8
> Any thoughts?
> Thanks
> --
> Mike
> "Graham Shaw" <Graham@.somewhere.com> wrote in message
> news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> oDom.xml
> PolicyID='FPHM016182'
to[vbcol=seagreen]
file[vbcol=seagreen]
following:[vbcol=seagreen]
need
> to
>