Removing a Clustered Index

[MSAccessRookie]

Is it possible to remove a Clustered Index from a table when you are using SQL Server 2005? I have tried and SQL server prevents me from doing it because it is associated with the Primary Key.

I have a Table tblPeople that has a Clustered Index on Person_ID, and I have a query that seems to be having problems because it wants to sort the table according to a calculated Last Name/First Name Field. The query is having random locking issues that prevent users from using the application.

 

[SQL_Hell]

Hi there

No you cant remove a clustered index on a primary key, it's the default for sql server.

Are there any other indexes on this table?
Have you considered creating a non-clustered index on the calculated last name / first name field?

Are the indexes fragmented? which is leading to the locking you have mentioned?

 

[MSAccessRookie]

Hi there

No you cant remove a clustered index on a primary key, it's the default for sql server.

Are there any other indexes on this table?
Have you considered creating a non-clustered index on the calculated last name / first name field?

Are the indexes fragmented? which is leading to the locking you have mentioned?

I did not know that was possible. I will have to try it. I wonder if it will accept my calculation.

IIf(IsNull(L_Name),"",L_Name) & 
IIf(IsNull(L_Name),"",IIf(IsNull(F_Name),"",", ")) & 
IIf(IsNull(F_Name),"",F_Name)

NOTE: It appears that an index may contain only one or more columns.

Where does that leave me now?

 

[SQL_Hell]

sorry I misunderstood you, I thought you actually had a field that was populated like:

John Smith

IIF statements aren't supported in sql server so where is this query being run? I can show you how to do the same thing in t-sql if you need

 

[MSAccessRookie]

sorry I misunderstood you, I thought you actually had a field that was populated like:

John Smith

IIF statements aren't supported in sql server so where is this query being run?

This post was a result of a previous post of mine in the queries section of the Access Forum. I have since determined that the property called OrderBy is a result of the table tblPeople having the Cluster Index, so I started a new question here regarding the table properties. Take a look at the thread if you need any more information

I am having a problem with locking in my database when I am adding new contacts, and have been trying to determine which database objects access the table tblPeople by using the tool ReplaceWiz 2003.

I have determined that there is a query named qryPeopleAlphabeticRW, (which is currently supposed to be the only query in the application that writes to the table tblPeople), has a property called OrderBy that I have not seen before.

I have seen a property like this in Forms and Reports, but never in a query. I have been unable to identify this property and remove it, since the OrderBy Property is not the correct order. It is OrderBy PersonID, while the query qryPeopleAlphabeticRW is ordered by the name of the person.

Has anyone else encountered this?

Thanks in advance.

 

[SQL_Hell]

Ok there are a lot of things here that dont make sense....

Order by should never be used in an Insert Into query, it's simply not needed.

SQL server tables should never be poplulated by access append queries, this is probably why you are getting locking problems, use a stored procedure.

If you want to order things then use order by in your select queries, sql server likes to store things in it's own order.

 

[MSAccessRookie]

Ok there are a lot of things here that dont make sense....

Order by should never be used in an Insert Into query, it's simply not needed.

SQL server tables should never be poplulated by access append queries, this is probably why you are getting locking problems, use a stored procedure.

If you want to order things then use order by in your select queries, sql server likes to store things in it's own order.

Order by should never be used in an Insert Into query, it's simply not needed.

qryPeopleRW is used as a recordsource for a form that is used to enter new contacts, or modify existing contacts. Records are written or modified whenever a new record is entered, or the form is closed, like the rest of the Access application.

SQL server tables should never be poplulated by access append queries, this is probably why you are getting locking problems, use a stored procedure.

This is one of over a dozen similar update queries, and is the only one that locks. I am convinced that there is some kind of table related aspect to the problem.

If you want to order things then use order by in your select queries, sql server likes to store things in it's own order.

I am aware of this fact, and this query is a Select query that allows the user to decide which record to modify (or add at the end)

 

[tokoloshi]

Is it possible to remove a Clustered Index from a table when you are using SQL Server 2005?

No, you can't remove a clustered index. You can choose to have the clustered index on any column, the default is the primary key.

The term clustered refers to the fact that the data in the table is physically stored in the pages on the hard drive in the order of the chosen clustered index.

The choices for the sort order should take the frequency of change of the data into consideration. So, clustering on a surname can cause a lot of disk activity when ever new names are entered into the table because the table needs to be restored and resorted every time that new data gets added in - unless you can guarantee that the names will always be consecutive - as in you can guarantee that for the next year you will only input surnames beginning with the A and then B's will follow the year thereafter ...

The primary key is not supposed to be used for business critical information since its primary purpose is to facilitate the referential integrity of your schema design. In Access you find that the AutoNumber field will lose a number that is not used, so you cannot rely on it for business purposes. You will often write a routine to create your own invoice numbers, quote numbers etc. And this is better column on which to create your clustered index.

I have tried and SQL server prevents me from doing it

As soon as you disable a clustered index you have done the equavilent of disabling the Master Boot Record of your computer. The only reason you can "see" any files on your computer is because the OS looks up the files details in the MBR. The only reason you can see any data in a table is because of the fact that the Clustered Index is what you are in fact reading.

I have a Table tblPeople that has a Clustered Index on Person_ID
, and I have a query that seems to be having problems because it wants to sort the table according to a calculated Last Name/First Name Field.

You can create a full text index on the last name & first name fields,but maybe a better approach is to create an index view, which would perform faster and can include a concatenated column along with a few other commonly searched for columns such as Social Security or Province

The query is having random locking issues that prevent users from using the application.

The best thing to do with random locking issues to monitor SQL Server using both SQL Profiler and SQL Server Management Studio (<InstanceName>-Management-Activity Monitor).

You will be able to see what process is causing the lock as well which user is busy at the time.

SQL Profiler will actually give you the full SQL code that caused the lock, giving you sufficient information to be able to trace the problematic queries.

 

[MSAccessRookie]

tokoloshi,

Thanks for the response. I appreciate your taking time to break out your observations and provide the comments in that way.

The term clustered refers to the fact that the data in the table is physically stored in the pages on the hard drive in the order of the chosen clustered index.

The choices for the sort order should take the frequency of change of the data into consideration. So, clustering on a surname can cause a lot of disk activity when ever new names are entered into the table because the table needs to be restored and resorted every time that new data gets added in - unless you can guarantee that the names will always be consecutive - as in you can guarantee that for the next year you will only input surnames beginning with the A and then B's will follow the year thereafter ...

The primary key is not supposed to be used for business critical information since its primary purpose is to facilitate the referential integrity of your schema design. In Access you find that the AutoNumber field will lose a number that is not used, so you cannot rely on it for business purposes. You will often write a routine to create your own invoice numbers, quote numbers etc. And this is better column on which to create your clustered index.

I have read these things before, but this explanation puts them into terms that are easy to understand.

As soon as you disable a clustered index you have done the equavilent of disabling the Master Boot Record of your computer. The only reason you can "see" any files on your computer is because the OS looks up the files details in the MBR. The only reason you can see any data in a table is because of the fact that the Clustered Index is what you are in fact reading.

I was unaware of this fact, and now understand why I cannot get rid of it.

You can create a full text index on the last name & first name fields,but maybe a better approach is to create an index view, which would perform faster and can include a concatenated column along with a few other commonly searched for columns such as Social Security or Province

I wish I could. This query needs to have Read/Write ability and linking it to a View would make it Read Only.

The best thing to do with random locking issues to monitor SQL Server using both SQL Profiler and SQL Server Management Studio (<InstanceName>-Management-Activity Monitor).

You will be able to see what process is causing the lock as well which user is busy at the time.

SQL Profiler will actually give you the full SQL code that caused the lock, giving you sufficient information to be able to trace the problematic queries.

I am going to have to learn more about these two tools. We have a test of the application tomorrow morning. Do you have any tips or ideas?

 

[Banana]

I wish I could. This query needs to have Read/Write ability and linking it to a View would make it Read Only.

Disclaimer: I know nothing about MS SQL!

I believe that any views works just like Access queries- if the server can see one-to-one relationship between the row in table and the row in view, then it's updateable. Therefore, like any Access queries, if you have any aggregate functions, complex JOINs, subqueries or anything like that, it may not be able to be update the base row.

Keep in mind that SQL server may be more strict or lenient than Access; you would have to check the documentation to know how it determine if a view can be updateable. In case of MySQL, I must always include the keys if it is to be updateable.

With MySQL, I make a point to explicitly specify the algorithm when I define the view to ensure that it will be in fact updateable (or in some case, non updateable). I do not know if MS SQL has anything similar to that, so you may also want to check into that.

HTH.

 

[MSAccessRookie]

Disclaimer: I know nothing about MS SQL!

I believe that any views works just like Access queries- if the server can see one-to-one relationship between the row in table and the row in view, then it's updateable. Therefore, like any Access queries, if you have any aggregate functions, complex JOINs, subqueries or anything like that, it may not be able to be update the base row.

Keep in mind that SQL server may be more strict or lenient than Access; you would have to check the documentation to know how it determine if a view can be updateable. In case of MySQL, I must always include the keys if it is to be updateable.

With MySQL, I make a point to explicitly specify the algorithm when I define the view to ensure that it will be in fact updateable (or in some case, non updateable). I do not know if MS SQL has anything similar to that, so you may also want to check into that.

HTH.

VERY Interesting! I will need to look into this, since I have always been led to believe that all SQL Server Views were Read-Only. If I could make some to them Read/Write, it would eliminate the need for the Access queries that perform that functionality.

UPDATE:

As of 5:00 PM, I have been unable to locate any references to writeable SQL Server Views. I have found articles and posts regarding Stored Procedures and Functions, but that would not be not the first preference for how to resolve this issue for a number of reasons. Anyone have any other observations to share? I am continuing to look.

 

[tokoloshi]

Writeable Views

The reason you will not find a reference to a "Writeable View" is because a view is exactly what it says. Another way of lookin at your table. You can write to it or read from it n exactly the same way that you would to a table.

The thing that makes a query or a view non-updateable, irrespective of the DB, is when you start including columns into your view that require referentia integrity contraints to be adhered to or where you are agregating your coumns. It starts getting messy to want to update data like tht anyway.

The trick is always to see if you cannot do the update via a set of DOCMD.RUNSQL statements.

That way you can wrap several of these statements within a single transaction and you can test to see if all statements have been run or roll-back all of the updates.

SQL Server's books on-line are brilliant as a reference guide. just do a search for SQL Profiler and Locks and you will get some decent starting points.

Good luck for the app test in the morning.

 

[datAdrenaline]

Hello SQL_h..

>> SQL server tables should never be poplulated by access append queries ... <<

I disagree whole heartedly (respectfully of course ) ... With Access's ability to create/use linked tables, you can easily use append queries without issue, its one of the huge advantages of MS Access ... you can build and entire application using linked tables as your source for data, then your application's BACK END is virtually independant with respect to the database engine powering it. There may be few tweaks here and there, but by and large, its pretty handy to have that capability.

 

[datAdrenaline]

MSAccessRookie ...

>> This is one of over a dozen similar update queries, and is the only one that locks. <<

Can you reveal your destination table structure? How you are running the Append query. I assume the Append query is an Access Query Object that is using Linked Tables. What is the SQL statement for the Append Query Object?

On your Form Object, do you have recordset type set to Dynaset (Inconsistent Updates)? ... If so, I would change it!!

... Aside from all this, I would advise you to add a column to tbl_People. The new columns data type should "TimeStamp". This new column is managed by the db engine. After you add this column, re-link your table. I recommend that you add a "TimeStamp" (aka: sometimes called rowversion) column to EVERY table in the Back End, at a minimum, I would add the column to all tables that are commonly interfaced with.

The TimeStamp column (I don't know the gory details as someone as skilled as SQL_Hell or Tokoloshi may know) can be used by ODBC to optimize edits to a record. As I can recall, adding a timestamp column helps prevent extraneous locking issues when interfacing with SQL Server via the Access user interface.

 

[datAdrenaline]

MsAccessRookie ... (and others) ...

As you have found, there are many ways records can be sorted in the Access UI, and Access's Objects!!! ...

- With the SQL Statement that retrieves the data (ORDER BY clause)
- In the table definition, there is an OrderBy property, which sorts the data after retrieval. Note that the OrderBy property is NOT the same as an Index, which can also be used to determine the displayed order of table data.
- A Form Object has an OrderBy property that sorts the retreived data
- A Query Object has an OrderBy property that sorts the retreived data
- A Report has an OrderBy property, plus you have Grouping & Sorting!!

So ... If your SQL Statement of a Query Object has an ORDER BY clause, and subsequently has an OrderBy property set, and that Query Object is the Recordsource of a Form Object that also has an OrderBy property set ... well ... you can see that the order of the records may get screwed up!! ...

Many times I will run code to REMOVE the information in the OrderBy property of All Access Objects that have an OrderBy property...

 

[SQL_Hell]

Hello SQL_h..

>> SQL server tables should never be poplulated by access append queries ... <<

I disagree whole heartedly ... With Access's ability to create linked tables, you can easily use append queries without issue, its one of the huge advatages of MS Access ... you can build and entire application using linked tables as your source for data, then your application is BACK END virtually independant with respect to any the database engine powering it. There may be few tweaks here and there, but by and large, its pretty handy to have that capability.

Hiya,

I see your point and can understand where you are coming from, but however I see things from a different perspective. Ok so we have linked tables and can easily create a updateable form with simple code such as "select * from linkedTable" then the user can click new record on the record selectors and add a new record...great. The query will run once the form has opened...so say for example the user doesn't add a new record and heads off to lunch but leaves his cursor on a field, Access will hold open a lock at sql server end ready for writing data. So say 15 people do exactly the same thing...now we got a problem access is holding locks on objects which are essentially doing nothing, this is the kind of thing that leads to lock escalation in sql server and lock escalation normally leads to deadlocks.

I think unbound forms and paramterised stored procedures are the best way to go, the users types his data into unbound text boxes during this no database connection is being held open, he finishes writing his data and clicks somekind of submit button, this collects all the text box values, opens a database connection and fires the Insert stored procedure the connection is then immediately closed, it's quick no messing around data entry. So it really doesn't matter what the user does after that he could be leaving his cursor on text box and chatting to his colleage for the next 2 hours, the form is unbound.

Granted my method is a lot more complicated to code and design, but i think it pays dividends for the future.

 

[datAdrenaline]

>> Granted my method is a lot more complicated to code and design, but i think it pays dividends for the future <<

And negates one of the HUGE benefits of using Access ... Unbound forms are a great tool, but, IMO, should be limited to situations that require them.

The scenario you describe will only hold a lock if the user begins an edit of a record with a Pessimistic locking strategy. If the user decides to use an Optimisitic locking strategy there are no locks on the record until ODBC tries to commit the record to the table. So 100 users can have 100 records open and ZERO record locks would be present on the table with Optimisitic locking.

With pessimistic locking (as eluded to earlier), and all 100 users start to edit a record, then a lock will be placed on that one record, and 100 records will indeed be locked, which, in all honesty, that is my preferred method ... only one person editing a record at a time!!

Note ... the above is related to EDITING EXISTING records ... for NEW DATA ENTRY, all new records start off in an edit buffer and have no effect on the existing records. Once the record is saved, ODBC takes care of the appending of that record to the table ... and I beleive its with an INSERT INTO statement ... but I don't have the profiler in front of me to verify that claim.

Also, just to be clear, BROWSING records will not lock a record, so if a bound form is viewing data, there is no lock on the displayed record.

 

[MSAccessRookie]

MSAccessRookie ...

>> This is one of over a dozen similar update queries, and is the only one that locks. <<

Can you reveal your destination table structure?

I have included a view of the table structure from MS Access 2003 and SQL Server 2005 in the attachments.

How you are running the Append query. I assume the Append query is an Access Query Object that is using Linked Tables. What is the SQL statement for the Append Query Object?

We are not running an append query. We use an Access query that is bound to the Add Contact Form, to add a new record, or update an existing one.

On your Form Object, do you have recordset type set to Dynaset (Inconsistent Updates)? ... If so, I would change it!!

The form Object is set as just Dynaset. Is that OK?

... Aside from all this, I would advise you to add a column to tbl_People. The new columns data type should "TimeStamp". This new column is managed by the db engine. After you add this column, re-link your table. I recommend that you add a "TimeStamp" (aka: sometimes called rowversion) column to EVERY table in the Back End, at a minimum, I would add the column to all tables that are commonly interfaced with.

The TimeStamp column (I don't know the gory details as someone as skilled as SQL_Hell or Tokoloshi may know) can be used by ODBC to optimize edits to a record. As I can recall, adding a timestamp column helps prevent extraneous locking issues when interfacing with SQL Server via the Access user interface.

The table has a column at the end called SSMA_TimeStamp that the application ignores. It was added by the SQL Services Migration Assistant application when the tables were first migrated to run on the SQL Server.

Thanks for your observations. I have commented above.

 

[SQL_Hell]

The timestamp column is put on a table to help stop the problem of the "another user has changed this record" dialog box coming up, as far as I am aware it serves no other purpose, I could be wrong but so far this is the only use for that column type I have ever seen.

When you say the adding a new record locks the database, you are refering to a sql server deadlock right? not the access dialog box thing mentioned above?

Can you try changing the forms property 'DataEntry' to true, this will stop it retrieving the rest of the records and only allow you to enter new records.
With this property enabled do you still get the same locking problems?

 

[MSAccessRookie]

The timestamp column is put on a table to help stop the problem of the "another user has changed this record" dialog box coming up, as far as I am aware it serves no other purpose, I could be wrong but so far this is the only use for that column type I have ever seen.

When you say the adding a new record locks the database, you are refering to a sql server deadlock right? not the access dialog box thing mentioned above?

We believe that it is a deadlock situation, since all other use of the database where the table is concerned becomes impossible

Can you try changing the forms property 'DataEntry' to true, this will stop it retrieving the rest of the records and only allow you to enter new records.
With this property enabled do you still get the same locking problems?

The DataEntry Property of the form was set to NO, and I changed it to YES. Is that what you were referring to?

Thanks for your observations and comments. I have responded above