Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dchst
Starting Member
12 Posts |
 Posted - 2011-10-25 : 15:31:12
|
| HiI recently acquired a some software from a developer that is written in VB6 with SQL Express 2005 as the database server. One of my new clients called to complain about errors generated when running queries because of time-out problems.Upon investigation I found that there are only one or two of the 50-odd tables that have keys, and I think one or two indexes. Horrified, I immediately started to add keys and indexes where I logically thought they should be ran a re-index (code I downloaded from somewhere) and left it at that.The next day I was considering what I had done and how I could automate the procedure for doing a similar process for all the clients. I then realised that SQL would not automatically rebuild the primary keys I had created and panic stricken have since been trying to find a method to fix this, expecting a frantic call at any minute - this did not arrive thankfully.My questions are: 1. How do I script (automate) a process to correctly arrange the data on the database where I put the primary/clustered keys. 2. I need to update the tables (column order, primary keys and indexes) in a new script to update the other clients on the same basis and also run the first query script. 3. What is the impact of changing primary keys on a live database in this scenario?All help and advice gratefully received.ThanksDavid |
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-10-25 : 18:03:14
|
| 1. How do I script (automate) a process to correctly arrange the data on the database where I put the primary/clustered keys. - I do not understand this. Is this a one time effort or a recurring effort? If you create a clustered index on a particular column, it persists until you explicitly drop it. If you are referring to some kind of scheduled task to do something else,Express edition of SQL Server does not come with SQL Agent. You will have to use windows task scheduler or any third party tool for scheduling.2. I need to update the tables (column order, primary keys and indexes) in a new script to update the other clients on the same basis and also run the first query script. - Updating column order is not at all required. If you need to add a column, it'll be added at the end. There is no performance impact due to this. However, if you have written queries selecting all columns (select * from mytable), you will have to consider the newly added column in the application. If you have explicitly mentioned column names in the query (select col1, col2... from mytable), there should not be any problem. Addition/removal of an index does not change any data in the underlying tables so this should not be a problem.3. What is the impact of changing primary keys on a live database in this scenario?Yes - only if the column you chose for primary key contains duplicate values. You should test it out on a development db and then roll out changes to your clients. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-25 : 19:23:15
|
| 3) Anything from performance improvements to fatal errors that bring the application down. Don't make any changes without testing ever, especially not something like primary keys.When you added indexes, did you test that they helped? If not, do you know whether those indexes were helpful or not?If you need urgent tuning and are willing to consider a 3rd party, drop me a mail through the forum, I do this kind of thing for a lot of clients, big and small.--Gail ShawSQL Server MVP |
|
|
|
dchst
Starting Member
12 Posts |
Posted - 2011-10-26 : 02:54:20
|
| Thank you for the responses.1. How do I script (automate) a process to correctly arrange the data on the database where I put the primary/clustered keys. - I do not understand this. Is this a one time effort or a recurring effort? If you create a clustered index on a particular column, it persists until you explicitly drop it. If you are referring to some kind of scheduled task to do something else,Express edition of SQL Server does not come with SQL Agent. You will have to use windows task scheduler or any third party tool for scheduling.This would be a one time effort, but repeated on many client different databases. It would not need to be scheduled, but could possibly included as a one-time process during the course of a software upgrade.2. I need to update the tables (column order, primary keys and indexes) in a new script to update the other clients on the same basis and also run the first query script. - Updating column order is not at all required. If you need to add a column, it'll be added at the end. There is no performance impact due to this. However, if you have written queries selecting all columns (select * from mytable), you will have to consider the newly added column in the application. If you have explicitly mentioned column names in the query (select col1, col2... from mytable), there should not be any problem. Addition/removal of an index does not change any data in the underlying tables so this should not be a problem.I was under the impression that putting primary keys at the front of the database, then other columns based on frequency of use, size, etc would lead to more efficiency, but this is fine tuning, not relevant now.3. What is the impact of changing primary keys on a live database in this scenario?Yes - only if the column you chose for primary key contains duplicate values. You should test it out on a development db and then roll out changes to your clients.3) Anything from performance improvements to fatal errors that bring the application down. Don't make any changes without testing ever, especially not something like primary keys.When you added indexes, did you test that they helped? If not, do you know whether those indexes were helpful or not?Unfortunately wise after the event for me. I was under pressure to make changes to performance and only then discovered the lack of keys and indexes. Struck me that it is pointless having indexes with no keys, so I created the keys and only realised afterwards there might be a consequence. Will be much more circumspect in the future! The client actually expressed thanks, but would be difficult for me to say that there was any performance improvement.I am concerned that something will go wrong while it is not attended to and do regard this as urgent. Unfortunately I cant afford to engage a third party at this point.To be more specific, I have tried various methods (on test systems!) to copy the data from a table to a new temporary table and in that way get the data in primary key sequence on disk. Then simply drop the source table and rename the temporary table to the original source name. I have not managed to get this to work as the final result loses the primary key. This script also needs to run on each table in the database, so I probably need to use the cursor.ThanksDavid |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-26 : 03:28:43
|
quote:
Originally posted by dchst
I was under the impression that putting primary keys at the front of the database, then other columns based on frequency of use, size, etc would lead to more efficiency, but this is fine tuning, not relevant now.
Nope. Not at all.quote:
I have tried various methods (on test systems!) to copy the data from a table to a new temporary table and in that way get the data in primary key sequence on disk. Then simply drop the source table and rename the temporary table to the original source name.
Huh??????What are you trying to do here?I'll be blunt, if you don't have much familiarity with SQL indexing and performance tuning (and it does sound that way), you could easily spend a whole lot of time and effort for little to no gain. It's a complex area.[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
|
|
|
dchst
Starting Member
12 Posts |
Posted - 2011-10-26 : 05:37:05
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by dchst
I was under the impression that putting primary keys at the front of the database, then other columns based on frequency of use, size, etc would lead to more efficiency, but this is fine tuning, not relevant now.
Nope. Not at all.quote:
I have tried various methods (on test systems!) to copy the data from a table to a new temporary table and in that way get the data in primary key sequence on disk. Then simply drop the source table and rename the temporary table to the original source name.
Huh??????What are you trying to do here?I'll be blunt, if you don't have much familiarity with SQL indexing and performance tuning (and it does sound that way), you could easily spend a whole lot of time and effort for little to no gain. It's a complex area.[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP
Hi Gail,Thank you so much for your responses. You are quite right, and I have wasted a lot of time in trying to achieve what I initially thought of as a relatively simple exercise. I read your links and am in awe of your knowledge.The situation is that I am concerned that I have a potential nightmare awaiting. My belief is that each and every table should have a primary key unless the table has very few rows or some other reason. Further, that indexing is not adding value unless there are primary keys. By merely going into each table and manually creating primary keys does not make them functional for existing data. An update process is required in order to reorganise the existing data in the tables to allow the primary keys to function properly.It is this update procedure that I am looking for help with, unless my assumptions outlined are incorrect.ThanksDavid |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-26 : 09:39:30
|
quote:
Originally posted by dchstThe situation is that I am concerned that I have a potential nightmare awaiting. My belief is that each and every table should have a primary key unless the table has very few rows or some other reason.
All tables should have a primary key. All tables should have a clustered index, though whether it's on the pk column or not is a complex topic. Most tables should also have nonclustered indexes on them.quote:
Further, that indexing is not adding value unless there are primary keys.
No, not at all. Primary keys are primary keys, they're the column or set of columns that identify the row. You can index without pks (I have a lovely example of that I'm tuning at the moment, not a single primary key in the entire database).A primary key is always enforced by a unique index.quote:
By merely going into each table and manually creating primary keys does not make them functional for existing data.
Sure it does.quote:
An update process is required in order to reorganise the existing data in the tables to allow the primary keys to function properly.
Not in the slightest. You may be confusing this with reindexing, which is ongoing maintenance that has to be done to keep indexes in good working order.--Gail ShawSQL Server MVP |
|
|
|
dchst
Starting Member
12 Posts |
Posted - 2011-10-26 : 11:10:23
|
Whew! Thanks for this. My fears were essentially groundless, but there may still be other issues, but for now they will remain less urgent.quote:
All tables should have a primary key. All tables should have a clustered index, though whether it's on the pk column or not is a complex topic. Most tables should also have nonclustered indexes on them.
The first and last part of this was my understanding. The middle bit about a clustered index not being part of the pk is beyond me at this point.quote:
No, not at all. Primary keys are primary keys, they're the column or set of columns that identify the row. You can index without pks (I have a lovely example of that I'm tuning at the moment, not a single primary key in the entire database).A primary key is always enforced by a unique index.
My understanding was that the pk is clustered, or that the physical data on disk is sequenced by the pk. An index would hold the index and a pointer to the pk in order to optimize access. What you are saying is that this is not necessarily true. Could you explain a bit more or point me to somewhere for info on this?quote:
By merely going into each table and manually creating primary keys does not make them functional for existing data.Sure it does.
This is the source of my relief!quote:
An update process is required in order to reorganise the existing data in the tables to allow the primary keys to function properly.Not in the slightest.
I was underestimating what the db engine was doing.quote:
You may be confusing this with reindexing, which is ongoing maintenance that has to be done to keep indexes in good working order.
This bit I understand. I am really intrigued to understand how the db without primary keys works. Are there some special or unique characteristics which make this possible, or could any db be designed in this way? Also, is it ultimately more efficient or is there perhaps a trade off with the level of maintenance? I have no ambitions of becoming an SQL guru, but would like to understand the fundamentals of this so that I am aware of other possibilities. I am more comfortable with a clustered pk and indexes at this point and will stick with that for now!Thanks very much for your assistance.David |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-26 : 11:43:01
|
quote:
Originally posted by dchst
My understanding was that the pk is clustered
Not necessarily. The Primary key does not have to be enforced by a clustered index, a nonclustered is equally valid. A PK's clustered by default, but only by default.quote:
or that the physical data on disk is sequenced by the pk.
How the data is arranged within the data file is utterly irrelevant. That's the whole point of the relational database model, that the physical implementation doesn't need to be known in order to use the DB.quote:
An index would hold the index and a pointer to the pk in order to optimize access.
Huh? I think you're mixing up primary keys (logical database constructs) and clustered indexes (a physical database structure)quote:
I am really intrigued to understand how the db without primary keys works.
Much the same way a database with primary keys works. SQL takes your query, figures out whether it can use an index to satisfy the query or if it has to scan the table and then goes off and does so (the 10000km view of it)quote:
Also, is it ultimately more efficient or is there perhaps a trade off with the level of maintenance?
Again, primary keys are about data integrity, they are logical database design constructs, they are the column or set of columns which uniquely identify a row. Indexes are about efficiency, performance and the trade off with maintenance.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Re-build of SQL 2005 Express Database