[MSSQL] Achteraf primary keys toevoegen

[Yoozer]

Ik ben momenteeel bezig met het in kaart brengen en documenteren van de database van een CRM-systeem. Dit is eerder nog niet gebeurd. Ik ben met Enterprise Manager per tabel dus de relaties tussen primary en foreign keys aan het maken. Het CRM systeem draait op ASP Classic.

Vrijwel alle tabellen hebben keurig een primary key veld (met een identity eraan). Echter, er zitten er een paar bij waarvan het id-veld als identity is ingesteld, maar niet als primary key. Hier klaagt EM over; deze kunnen in een relationship dus niet worden gebruikt, omdat die een primary key vereist.

Is het problematisch als achteraf op dit ID-veld (met identity dus) een PK wordt gezet? Ik heb dit geprobeerd bij een klein tabelletje en geen gekke dingen gemerkt, maar ik weet dat SQL Server (2000, overigens) bepaalde dingen uitvoert door een kopie aan te leggen en dan onder water even alle data te kopiëren.

Eventueel zeuren over prutser-database-design mag, maar ik kan dat in ieder geval niet meer oplossen in de korte tijd dat ik hier zit .

[Varienaja]

Achteraf primary keys toevoegen mag geen probleem zijn. Behalve natuurlijk als het veld waarop je die key wilt leggen niet-unieke waarden bevat, maar dan klaagt het DBMS natuurlijk wel bij het aanmaken!

[whoami]

Dat is normaal geen probleem nee.
Ik denk wel dat Sql Server er automatisch een clustered index zal van maken, en een tabel kan slechts 1 clustered index hebben, maar daar zal hij dan wel een fout over geven.
Ga ook na of het nodig is om de PK als clustered te definieren, of als je niet beter een andere index als clustered definieert.

Varienaja schreef op vrijdag 01 december 2006 @ 12:12:
Achteraf primary keys toevoegen mag geen probleem zijn. Behalve natuurlijk als het veld waarop je die key wilt leggen niet-unieke waarden bevat, maar dan klaagt het DBMS natuurlijk wel bij het aanmaken!

Idd, maar aangezien het blijkbaar identity fields zijn, zou dat niet mogen voorkomen .

[ Voor 37% gewijzigd door whoami op 01-12-2006 12:14 ]

[Yoozer]

Bedankt voor de antwoorden .

whoami schreef op vrijdag 01 december 2006 @ 12:13:
Ga ook na of het nodig is om de PK als clustered te definieren, of als je niet beter een andere index als clustered definieert.

Ik heb even opgezocht wat dit precies inhoudt. De tabellen in de db zijn vrij klein - een van die waar de wijzigingen op doorgevoerd moeten worden (de bedrijven-tabel) bevat 19000 records of zo en wordt echt sporadisch geupdate. Selects vinden vaak maar op 1 record plaats.

Als ik het goed begrepen heb; een clustered index zorgt ervoor dat als er een groot aantal INSERTs wordt toegepast de snelheid wordt geoptimaliseerd omdat alles dan in de goede volgorde staat in plaats van over de schijf verspreid.

Aan optimalisatie is echter nooit wat gedaan; een tabel krijgt een PK en een identity, het ontwerp wordt zover het kan genormaliseerd, en that's it.

In hoeverre is dit dan van toepassing?

edit: net even gekeken, en the plot thickens. In de tabel met de bedrijven zit ook nog niet eens een identity: er wordt gewoon een SELECT MAX gedaan om het laatst gemaakte ID op te halen en dit wordt met 1 opgehoogd. Jeuj . De CRM-applicatie heeft eerst een jaar of 2 op een Access DB gedraaid. Toen ik echter van het veld een Primary Key maakte gaf hij geen commentaar dat 'ie dubbele had gevonden, en een query bevestigt dit.

[ Voor 18% gewijzigd door Yoozer op 01-12-2006 12:30 ]

[whoami]

Nee, een clustered index bepaalt de fysieke opslagvolgorde van je records. Dat betekent dat, als je geen goede clustered idnex hebt (bv op een veld dat veel wijzigt, of een veld dat niet 'incrementeel groeit', je dus heel wat meer overhead kunt hebben bij inserts, omdat de records in een bepaalde volgorde moeten komen. (Al vraag ik me nu eigenlijk af of dit daadwerkelijk at 'insert time' gebeurd, of als dit bij 'DB maintenance gebeurd').
Clustered indexes zijn zeer interessant voor range searches, en eigenlijk is het best practice dat iedere tabel in SQL SErver een clustered index heeft, op die manier wordt de data optimaal opgeslagen, anders is je tabel een 'heap'.

[Verwijderd]

whoami schreef op vrijdag 01 december 2006 @ 13:38:
Nee, een clustered index bepaalt de fysieke opslagvolgorde van je records. Dat betekent dat, als je geen goede clustered idnex hebt (bv op een veld dat veel wijzigt, of een veld dat niet 'incrementeel groeit', je dus heel wat meer overhead kunt hebben bij inserts, omdat de records in een bepaalde volgorde moeten komen. (Al vraag ik me nu eigenlijk af of dit daadwerkelijk at 'insert time' gebeurd, of als dit bij 'DB maintenance gebeurd').

Een clustered index wordt bij het inserten altijd op volgorde gezet. Echter, alle data in MSSQL wordt opgeslagen in pagina's van 8KB. Indien bij het inserten een rij niet meer 'past' in die 8KB, dan komt er een split. 50% van de data (de eerste helft) blijft staan en de andere helft wordt in een nieuwe pagina gezet (de laatste helft). De fysieke volgorde van de rijen op de pagina is dus altijd gegarandeerd, echter de pagina's zelf kunnen dus verspreid over de database staan.

De pagina-splits kunnen dus performance problemen veroorzaken. Niet alleen de splits, maar ook de fragmentatie van de pagina's over de database.

Er is overigens ook een verschil tussen het achter elkaar zetten van pagina's (is dacht ik een optie bij het shrinken) en opnieuw opbouwen van de indexen zodat de pagina's weer 100% gevuld worden.
(dbcc reindex bij 2000 en alter index rebuild bij 2005).

Als laatste, als je verwacht dat een rij in een (geclusterde) index incidenteel wordt ingevoegd, dan kan je bij het aanmaken van een index een FillFactor (en Padding) meegeven. Dan wordt de index niet opgevuld tot 100% van ee pagina, maar bv 80%. Kost meer pagina's, maar levert minder splits op...