Thursday, June 20, 2013

Compare indexes of tables from SQL Server 2000(source) to 2008(target) and create script to create indexes at target

We are always upgrading from lower version to highest version. There may be chances that indexes are mismatched from our old version(2000) to new version(2008). I have tried to create script which will use of OPENROWSET command to read system tables from SOURCE database and compare it with TARGET database. This script will return difference from SOURCE to TARGET tables which are exists in TARGET database. We are doing that because it’s possible that we don’t want some table from older version. This script also provides create script for TARGET database to sync indexes with SOURCE database.
Below is script to download.

Compare collation of tables from SQL Server 2000(source) to 2008(target) and create script to alter collation at target

We are always upgrading from lower version to highest version. There may be chances that collation is mismatched from our old version(2000) to new version(2008). I have tried to create script which will use of OPENROWSET command to read system tables from SOURCE database and compare it with TARGET database. This script will return difference from SOURCE to TARGET tables which are exists in TARGET database. We are doing that because it’s possible that we don’t want some table from older version. This script also provides alter script for TARGET database to sync collation with SOURCE database.
Below is script to download.

Some good things to learn for "for update” in oracle.

Here, we have tried cover a scenario where one procedure is called by 2 process on same time and they want unique record from table. To understand this scenario, we have created tables Departments, Employee and Contact (Some good things to learn for for update in oracle 0.sql).

We are going to learn 3 different cases which comes in our actual life.

1) Simple way of use “update of column” on table. (Some good things to learn for for update in oracle 2.sql)

I have tried to execute same script in 3 windows which is using for update of column. I see below timeframes. Here, 2nd window’s open cursor is waiting till 1st window is not committed and 3rd window’s open cursor is waiting till 2nd window is not completed. (Query1)

In below output. 1st window is committing at 04:10:41, On same time2nd window open cursor is opening. Same for 3rd window also.

It means we will get unique records from different process if it’s called on same time.

1st window output.                   2nd window output.                   3rd window output.

image

2) Use of order by clause with “update of column” (Some good things to learn for for update in oracle 2.sql)

This will also give unique records to different process. 

3) Use of order by clause with “update of column” and also fetch together column/ columns from different table using inner join (Some good things to learn for for update in oracle 3.sql)

In this case, where current of will not work to update column. We need to write explicit query with where clause to update column. But it will also give unique records. 

4) Use of distinct and order by clause with “update of column” and also fetch together column/ columns from different table using inner and left join (Some good things to learn for for update in oracle 4.sql)

This scenario is always confusing to developer while reading oracle documentation or some other link that “distinct is not supporting for “for_update_clause”. Below is comments from Oracle Document.

image

It doesn’t mean that we can’t use distinct in our query with “for_update_clause”. It means that we can’t use it in outer query. There are always 2 sides of any statement. We all need to understand it properly before assuming anything.

I have taken example of Department to show this scenario. I have 5 departments and each department having more than 2 employees. I tried to create script which will return me employees of top 2 distinct departments from employee table in descending order of department. I tried to execute this script in 3 separate window on same time and it was giving distinct results. It means we can use distinct with “for_update_clause”. Below is screen shot of same.

1st window output.                   2nd window output.                   3rd window output.

image

5) Simple way of use “update skipped locked” on table. (Some good things to learn for for update in oracle 5.sql)

I tried to test 1st case for “for update skip locked” by executing same script in 3 separate window on same time. I noticed that 1st window is returning only 1 record and skipped 2nd record, 2nd window is returning one record which was skipped by 1st window, 3rd query is not returning any records. It means all windows are trying to lock on same rows and skipping which is locked by other session and continue.

Hope this will help all. Gimme some comments if you like it. I will try to make some scenarios which will satisfy our requirements and also can use “for update skip locked”.

Below scripts to download.

Some good things to learn for for update in oracle 0.sql

Some good things to learn for for update in oracle 1.sql

Some good things to learn for for update in oracle 2.sql

Some good things to learn for for update in oracle 3.sql

Some good things to learn for for update in oracle 4.sql

Some good things to learn for for update in oracle 5.sql