SQL Server Tacklebox- P17

SQL Server Tacklebox- P17: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 3 The migratory data Guaranteeing unique data Regardless of the data comparison tool you use you will need to guarantee uniqueness in your source and target data sources in order for these techniques to work. Heretofore in a frenzy to load data as fast as possible I have not been concerned with duplicate data rows or indexes. For this next section however I need to start with a clean slate and refactor the SQL_Conn and SQL_Conn_Archive tables in such a way that we can guarantee uniqueness of data. The SISS package that populates the SQL_Conn table runs every 15 minutes and captures the date and time that the package ran as a field value so this run time would be a good candidate for a unique column. However to truly guarantee uniqueness the clustered index will be a composite of the package run date and several other fields. I will also add an identity column id to the tables. Since the tables currently contain duplicate rows in this example I will first need to clean up a bit by truncating both the SQL_Conn and SQL_Conn_Archive. I can then run the package to populate the SQL_Conn table with current process information. Figure shows the new record count for the SQL_Conn table a mere 32 records. aalact Croe 3QL Conn R M t IO Hun. Date Moe ad - r name i iwar pe CPU l t_t ICh Muí 1 200M3X1554 27 213 0 Z. MOJO ksource_wa eup 0 20090325 1 35339197 200903 251353 197 baet pound 2 2 200903X1554 27 213 o fl r-. MISCE LLAME 0U5 0 20090325 1 35339197 20090325 1 353X197 ttaapna 3 3 200MI3 2515 54 27 213 Í fl o 7326669 mpHai FSAGEMT o 20090325 1353X197 200903 251353X197 back pound 200501X155427213 10 o 7276ÉM mvtfrn FSafifNT 0 20090325 1353 39197 20090325 1353 X 197 tael pound 5 5 2Û09O1X 15 54 27 213 11 o 7276669 FSAfiFNT 15 20090325 115139 197 200903251151X197 baclpcand 6 200901X15 54 27 211 12 0 7276669 matfaa FSAGEMT 0 20090325 1 3 53 39 197 20090325 1151X 197 back pound 200901X1554 27 213 0 7276669 maklaa FSAGEMT 0 200901 1353 197 200903251151X197 .