data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?
Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>
|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon
|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>

data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>sql

data reorganization

We are experiencing a situation where the data in our database gets
fragmented and we have to load the data into a flat file delete the data in
the tables and load it back. We have tried rebuilding the indexes with the
DBCC DBREINDEX command but it does seem to help. Any ideas how this could be
resolved?Hi,
Execute the command DBCC SHOWCONTIG('table_name') and verify the scan
density. Using this you
can identify how fragmented is your table. If the table is highly
fragmentred jut run a DBCC DBREINDEX on the Tables--clustered index. This
will reorganize all the data pages and indexes. After the reindexing you can
execute the DBCC SHOWCONTIG and confirm, whether your fragmentation is
removed or not.
Thanks
Hari
MCDBA
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>|||make sure that your tables have a Clustred Index and Look into the usage of
Fill Factor Settings.
Greg Jackson
PDX, Oregon|||You should read the whitepaper on fragmentation and when and how to address
it below:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Any questions after reading this, let me know.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:#PTj6CVVEHA.3512@.TK2MSFTNGP12.phx.gbl...
> We are experiencing a situation where the data in our database gets
> fragmented and we have to load the data into a flat file delete the data
in
> the tables and load it back. We have tried rebuilding the indexes with the
> DBCC DBREINDEX command but it does seem to help. Any ideas how this could
be
> resolved?
>

Data Removal Real?

I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.

The data is deleted.

However, depending upon your Recovery model, it may be 'hanging' around in the TRANSACTION LOG file for awhile, and anyone with access (and permissions) to the TRANSACTION LOG file, and one of several third party tools, may be able to inspect the deleted data.

(It's best, before they show up with the subpoenas, to backup and restore - and delete any saved backups and transaction logs, and the use a DOD approved sector wiping utility and burn the tapes... But of course, that is not a good thing to be caught having done...)

|||

Ok, thanks. So, I should look for a recovery model that doesn't use logging? This implies no recoverable transactions, but that would be ok.

(Side note, I'm not worried about subpoenas. Foreign governments are the perceived risk.)

|||

Simple recovery model has the shortest logging latency.

However, that means no data recovery since the last FULL BACKUP.

It seems that you may need to better secure the server ...

|||

I wasn’t sure about this one, so I asked the experts and this is what they replied back:

all the deletes are only logical … The only way to get rid of the data for sure is to DTS to a new (clean) database.

They mentioned some SQL Server 2005 SP2 feature for Common Criteria (CC) that is used to clear the information on the pages, but again, they mentioned it is only a logical deletion.

Arnie, it may be possible that you were talking about this feature, can you please confirm? Unfortunately I have limited knowledge on this particular area myself.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Data Provider List Missing...very odd SQL 2000

Hey All!

I have an interseting situation with one of my dev machines. (W2k3EE, SQL2k SP4)

All my linked servers stopped working posting this error:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider [enter provider name here, e.g. 'SQLOLEDB'.

So...when attempting to drop and recreate the linked server, the Provider pull down in the GUI is completely empty.

YIKES!

I'm thinking of reinstalling MDAC, but I've never seen this in 10 years of DBA'ing.

Have any of you? Any thoughts out there? Registry issues?

Cheers,
OddNever seen anything like it either. I'm running the same combination in my environment, so if you have any solutions, please post.

Some thoughts and/or suggestions:

1. Was the server recently patched?

2. Was any new software (eg. driver package) recently installed?

3. Any entries in the Application or System error log(s)?

4. Do you back up the registry on this machine? Can it be restored?

Regards,

hmscott|||Thanks for the reply...will definitely post what I find...

1 - yeah, SP4
2 - prolly so...the network guys love to drop agents on my dev servers without mentioning it...Alteris monitor, i think this time
3 - nada
4 - heck no, that would be too easy

:)

Data Overflow

Hi,

I would like to know if we can define an unsigned integer data type in SQL Server 2005. We have a situation where one of the integer columns will reach its 2 billion limit. I want to know if there's any way in which I can extend this to say 4 billion by making the data type unsigned or any other way which doesn't require me to change the data type to bigint.

Any help is highly appreciated.

TIA

Ritesh

Unfortunately, there isn't. If you are able to figure out a way to use the negative numbers then you may be OK, otherwise, it might be time for a bigint.

Data Mining : Drug Trial Question

I have a a simple situation. I am trying to determine for how long (in months) two drugs should be given to a patient. Both the drugs are administered at frequent intervals and their dosage and the condition of the patient is recorded. How do I determine for how long should I continue to adminster the drugs,at what point should I stop? I am dealing with the following variables : Drug 1 dosage,Drug 2 dosage, Period of medication (in months). Can I use SQL Server 2005 data mining capabilites to get the answer? Please let me know.

No you cannot. An MD should evaluate the patient and decide to stop or increase/decrease dosage. Technology is yet far from of replacing an MD.|||

I have to agree with the other response that this decision should be made by a qualified MD. What data mining can do is tell you the relationships between the dosages and the medication periods. Note that since these results are dependent on data, and in this case the data is determined by the recommendations of MD's, essentially what the algorithm is doing is determining patterns in behavior of MD's prescribing drugs for an array of patients. The results could be skewed by a number of factors. For example, if all of the data came from a single MD, you would only be seeing the pattern of that doctor's prescription behavior.

What could the results be used for? I believe in this case, you could use the results for budgeting and forecasting - for example, you see the dosages and you can give an estimate to a patient as to when their treatment would end or to a payment provider as to the total cost of treatment. Another usage would be to present the patterns to MD's - e.g. as a logistic regression scorecard, tree, or some other easily consumable form - that they could use as a guideline to help them make decisions.

However, as the other responder noted, any decisions should rest in the hands of a qualified MD.

|||

Thank you, Jamie for responding to the question. I think you have very well summarized the solution, I am looking for. I am looking to find the dosages so that I can give an estimate to a patient as to when their treatment would end(an interval estimate) or to a payment provider as to the total cost of treatment (an interval estimate). Can I use logistic regression for the purpose ?

|||You could use Logistic Regression, Neural Nets, or Decision Trees. DT will give the most understandable model. I would try all three and use the accuracy chart to see which model does the best job at prediction.

Data Migration

I have an interesting situation. We are attempting to standardize our data
models. Our database is large, with over 100 tables, and 50 Gigs of data.
Our differences between various clients is subtle, but again the volume of
tables makes it challenging.
What I am looking for is a tool that will migrate data from an older version
of a database to a top level version of the same database.
This is SQL Server 2000 SP 3a to SQL Server 2000 SP 3a.
Does anyone know of such a tool?
http://www.aspfaq.com/show.asp?id=2209
Andrew J. Kelly SQL MVP
"Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
news:803B71E8-13B9-4F9B-889F-2A2DE65B7DA7@.microsoft.com...
>I have an interesting situation. We are attempting to standardize our data
> models. Our database is large, with over 100 tables, and 50 Gigs of data.
> Our differences between various clients is subtle, but again the volume of
> tables makes it challenging.
> What I am looking for is a tool that will migrate data from an older
> version
> of a database to a top level version of the same database.
> This is SQL Server 2000 SP 3a to SQL Server 2000 SP 3a.
> Does anyone know of such a tool?
|||This does not help. This only, unless I missed it, will give me the
difference in structure......What about moving the data?
"Andrew J. Kelly" wrote:

> http://www.aspfaq.com/show.asp?id=2209
>
> --
> Andrew J. Kelly SQL MVP
>
> "Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
> news:803B71E8-13B9-4F9B-889F-2A2DE65B7DA7@.microsoft.com...
>
>
|||Michael @. SGMS wrote:
> This does not help. This only, unless I missed it, will give me the
> difference in structure......What about moving the data?
>
Couldn't you script these changes using T-SQL and modify as needed per
client? Another option is to use DTS.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||My first thought was to use a linked server (where the data I want to migrate
is) and then so something like:
Insert into newdb..TableA
Select * from olddb..TableA
BUT, there are contraints on the new database that cause these to fail. I
was hoping there was something out there that would script the dropping on
the indexes and keys on the new db, then the data move, then the creation of
the keys and constraints on the new db.
I already have a script that will make the old server structure like the
new...
"David Gugick" wrote:

> Michael @. SGMS wrote:
> Couldn't you script these changes using T-SQL and modify as needed per
> client? Another option is to use DTS.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||There isn't anything that I know of that will do all of that automatically.
The tool package from www.red-gate.com has a tool to compare the schema and
one to compare data. I know the schema tool will generate change scripts
but I don't know if it can generate data change scripts. That's usually
highly dependant on the schema and business rules.
Andrew J. Kelly SQL MVP
"Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
news:82853B5B-571D-4F94-A967-CC0DD2BBA510@.microsoft.com...[vbcol=seagreen]
> My first thought was to use a linked server (where the data I want to
> migrate
> is) and then so something like:
> Insert into newdb..TableA
> Select * from olddb..TableA
> BUT, there are contraints on the new database that cause these to fail. I
> was hoping there was something out there that would script the dropping on
> the indexes and keys on the new db, then the data move, then the creation
> of
> the keys and constraints on the new db.
> I already have a script that will make the old server structure like the
> new...
> "David Gugick" wrote:
|||DB Ghost will do data and schema in a single process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Andrew J. Kelly" wrote:

> There isn't anything that I know of that will do all of that automatically.
> The tool package from www.red-gate.com has a tool to compare the schema and
> one to compare data. I know the schema tool will generate change scripts
> but I don't know if it can generate data change scripts. That's usually
> highly dependant on the schema and business rules.
> --
> Andrew J. Kelly SQL MVP
>
> "Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
> news:82853B5B-571D-4F94-A967-CC0DD2BBA510@.microsoft.com...
>
>
|||Mark,
Will it generate a script to reconcile the data differences?
Andrew J. Kelly SQL MVP
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:69ED6392-ECEC-49B6-BEF3-C4A3C0E9E75A@.microsoft.com...[vbcol=seagreen]
> DB Ghost will do data and schema in a single process.
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database
> change
> management for SQL Server
>
>
> "Andrew J. Kelly" wrote:
|||yes.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Andrew J. Kelly" wrote:

> Mark,
> Will it generate a script to reconcile the data differences?
> --
> Andrew J. Kelly SQL MVP
>
> "mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
> news:69ED6392-ECEC-49B6-BEF3-C4A3C0E9E75A@.microsoft.com...
>
>
|||Check out http://www.agileinfollc.com DataStudio, it has a multiple thread
migration engine (20 thgreads) to migrate data.
"Michael @. SGMS" <MichaelSGMS@.discussions.microsoft.com> wrote in message
news:803B71E8-13B9-4F9B-889F-2A2DE65B7DA7@.microsoft.com...
>I have an interesting situation. We are attempting to standardize our data
> models. Our database is large, with over 100 tables, and 50 Gigs of data.
> Our differences between various clients is subtle, but again the volume of
> tables makes it challenging.
> What I am looking for is a tool that will migrate data from an older
> version
> of a database to a top level version of the same database.
> This is SQL Server 2000 SP 3a to SQL Server 2000 SP 3a.
> Does anyone know of such a tool?

Data loss

We have a situation in here were one of my collegue has accidently
uninstalled a Delphi app.....how will I recover data that is lost?Are
there any tools to recover the data?"andz" <anand411@.gmail.com> wrote in news:1153300202.168067.257380
@.s13g2000cwa.googlegroups.com:

> We have a situation in here were one of my collegue has accidently
> uninstalled a Delphi app.....how will I recover data that is lost?Are
> there any tools to recover the data?
What exactly is the problem regarding the data? They should still be in the
database, or does the uninstall program delete the database as well? If so,
I guess you'll have to find the latest backup, and do a restore of the
data.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Many Thanks for your reply...
Yes, the application uninstalled the dbs aswell...we have recovered
some of the tables via a recovery tool...but some tables give the
following error - corrupt table or index header while opening.
When trying to repair using paradox it comes with error number 30704 -
u have tried to use an unassigned variable.A variable must be assigned
a value before you can use it..
Andz
Ole Kristian Bang=E5s wrote:

> "andz" <anand411@.gmail.com> wrote in news:1153300202.168067.257380
> @.s13g2000cwa.googlegroups.com:
>
> What exactly is the problem regarding the data? They should still be in t=
he
> database, or does the uninstall program delete the database as well? If s=
o,
> I guess you'll have to find the latest backup, and do a restore of the
> data.
> --
> Ole Kristian Bang=E5s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||"andz" <anand411@.gmail.com> wrote in
news:1153310037.438389.188180@.m73g2000cwd.googlegroups.com:

> Many Thanks for your reply...
> Yes, the application uninstalled the dbs aswell...we have recovered
> some of the tables via a recovery tool...but some tables give the
> following error - corrupt table or index header while opening.
> When trying to repair using paradox it comes with error number 30704 -
> u have tried to use an unassigned variable.A variable must be assigned
> a value before you can use it..
You'll either need the database files (flat file backup) or some other kind
of backup, otherwise I guess there is very little you can do. (That I know
of at least).
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Data loss

We have a situation in here were one of my collegue has accidently
uninstalled a Delphi app.....how will I recover data that is lost?Are
there any tools to recover the data?"andz" <anand411@.gmail.com> wrote in news:1153300202.168067.257380
@.s13g2000cwa.googlegroups.com:
> We have a situation in here were one of my collegue has accidently
> uninstalled a Delphi app.....how will I recover data that is lost?Are
> there any tools to recover the data?
What exactly is the problem regarding the data? They should still be in the
database, or does the uninstall program delete the database as well? If so,
I guess you'll have to find the latest backup, and do a restore of the
data.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||Many Thanks for your reply...
Yes, the application uninstalled the dbs aswell...we have recovered
some of the tables via a recovery tool...but some tables give the
following error - corrupt table or index header while opening.
When trying to repair using paradox it comes with error number 30704 -
u have tried to use an unassigned variable.A variable must be assigned
a value before you can use it..
Andz
Ole Kristian Bang=E5s wrote:
> "andz" <anand411@.gmail.com> wrote in news:1153300202.168067.257380
> @.s13g2000cwa.googlegroups.com:
> > We have a situation in here were one of my collegue has accidently
> > uninstalled a Delphi app.....how will I recover data that is lost?Are
> > there any tools to recover the data?
> What exactly is the problem regarding the data? They should still be in t=he
> database, or does the uninstall program delete the database as well? If s=o,
> I guess you'll have to find the latest backup, and do a restore of the
> data.
> --
> Ole Kristian Bang=E5s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||"andz" <anand411@.gmail.com> wrote in
news:1153310037.438389.188180@.m73g2000cwd.googlegroups.com:
> Many Thanks for your reply...
> Yes, the application uninstalled the dbs aswell...we have recovered
> some of the tables via a recovery tool...but some tables give the
> following error - corrupt table or index header while opening.
> When trying to repair using paradox it comes with error number 30704 -
> u have tried to use an unassigned variable.A variable must be assigned
> a value before you can use it..
You'll either need the database files (flat file backup) or some other kind
of backup, otherwise I guess there is very little you can do. (That I know
of at least).
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Data Integrity Across Tables - Please evaluate.

Hi,

I have the following situation...

Products with attributes. e.g. colour, size.

The attributes have options. e.g. green, red.

The options form combinations which can be priced seperately.

e.g. red/green = £3

I figure that a good design would put each entity in its own table.

A table for products, attributes, options and combinations.

The problem is that combinations directly derive/correspond from options. So if a stored procedure is reading options and then a concurrent process changes the options and thus the combinations, the stored procedure doing the reading will then go on to read combinations which are out of synch with the options it read.

So my idea is to put all the data into a single table and then, using a locking hint, place a shared lock on this table when the data is being read.

Each row in this table is capable of storing all of the different types of data - attributes, options and combinations. There is also a flag which marks what type of data is being stored in the row - much like variables in a scripting language.

What do you think of this design?

Also, if there are many processes reading from this table and locking it, will an update be able to get to the data?

Cheers,

I.Go for seperate tables. Better design and better for locking.|||Hi Perre.

Thanks for your reply.

When the data is in seperate tables, its the locking that's the problem.

" if a stored procedure is reading options and then a concurrent process changes the options and thus the combinations, the stored procedure doing the reading will then go on to read combinations which are out of synch with the options it read. "

WT.|||Hi WT,

Separating entities into different tables is invariably better database design : your database is much more scalable this way - for example it makes it much easier to add another colour to your list, and also it saves you from having to store the same data in multiple places, allows you to refer to rows of data by index etc etc which will speed up your database.

If you need to guarantee read consistency across your data then you can either gather all your data in a single select statement using READ UNCOMMITTED transaction isolation level in SQL server (this is the default - so you don't need any code beyond your select)

If you need more than one database select within your procedure you can start a transaction at the start of the procedure and then set the transaction isolation level to REPEATABLE READ. This will prevent any rows which you have read from any tables during your transaction being updated until after you have finished your transaction.
in SQL server you do this with the command
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

If a concurrent Insert into one of your tables whilst reading could cause problems you will need a trans isolation level of SERIALIZABLE.

The downside to these forms of locking is that they reduce concurrency - ie less people can access the same resources at the same time which may or may not be a problem in your particular application - you have to aim for a balance between concurrency and data consistency.

Maracatu|||Don't let some locking issues put you off from using better DB design. Correctness of your design is always your first priority. It's easy to get around performance / locking issues later if your design is correct and normalised. You can for example build indexes, cache datasets or build trigger-updated help tables. But if your design is bad your options are considerably reduced.|||Hey, thanks for the replies.

I agree that putting things in seperate tables is better design. However...

"it makes it much easier to add another colour to your list, and also it saves you from having to store the same data in multiple places, allows you to refer to rows of data by index etc etc which will speed up your database. "

Putting all the entities here in the same table as I described doesn't stop any of this happening although some rows do actually have lots of columns with null values when the row type doesn't require anything in those columns.

"gather all your data in a single select statement"

How do you read from 3 different tables in a single select? I suppose one could use a union but then the rows from some of the tables would have to be padded with nulls to give all of the results rows the same form. More importantly, couldn't a concurrent update occur within the middle of the union read? If so, then from a locking point of view, this is no different to 3 seperate selects. Also, if all the data is requested into the same results set, then it makes it harder to bind the data to different ASP.net web controls because they need distinct data sources. This last point is a problem with my design.

READ UNCOMMITTED is not the default locking. The default is READ COMMITED.

"If you need more than one database select within your procedure you can start a transaction at the start of the procedure and then set the transaction isolation level to REPEATABLE READ. This will prevent any rows which you have read from any tables during your transaction being updated until after you have finished your transaction."

Having things changed which have already been read isn't the problem. The problem is having things changed which have NOT yet been read and then having those things being out of synch with what has been read.

"If a concurrent Insert into one of your tables whilst reading could cause problems you will need a trans isolation level of SERIALIZABLE. "

I don't understand how this will help.

"Don't let some locking issues put you off from using better DB design."

OK, so assuming that I have data normalized in seperate tables, how does one read data in table A then table B and then table C without a concurrent update modfiying what's in table B or C before the read has completed?

I can think of a way but it involves using a transaction on a common read - surely this isn't good database design?

Have the read of a product contained by a transaction and make it repeatable read. The first thing that the read reads is the main product record in the products table - then it proceeds to all the combinations data etc. Then make sure that any updates always start by attempting to update the main product row. This way, the updates won't be able to proceed to any futher updates until the read has finished its transaction.

Finally, if you were a user, would you rather have integrated, consistent data on a web page that you requested or know that a web page with garbled and senseless data on it is stored in the 'correct' format. Perhaps the theory is imperfect.

WT.|||By the way, in order to get the different types of data from the single table in my unconventional/bad design into different result sets so that they can be bound to different web controls, I selected all the relevant data from the single table into a table variable and then did 3 seperate selects from this table variable - one for each type of data.

Is this ugly or what?

But at least the data will be consistent because the read form the single table uses a table lock hint.|||Please don't do this - you say 'at least the data will be consistent ...' This is stupid. You can get consistent reads from your normalised tables just as easily using the same locking hints if you wish. Also in your case there's no reason to lock the entire table. Just lock the rows you are reading, that's good enough in your case and will allow other process to read other data from the table at the same time.

About your other post. I sense that you have some misconceptions about database design. I'm not going to give you DB Design 101 here, but you say'How do you read from 3 different tables in a single select?' WHAT? Are you serious? Ever heard of 'join' or 'union'?

'More importantly, couldn't a concurrent update occur within the middle of the union read?' This has NOTHING to do with normalising your data-structure. This is a non-issue either way.

'The problem is having things changed which have NOT yet been read and then having those things being out of synch with what has been read.' So you want to bind several controls on your page with data from your tables. Right? And you want to make sure that your data on your page at the end is consistent. Right? So instead of messing up your DB design, simply write a stored proc to return the resultsets (all 3 or 4 or how many you need) inside a transaction with some row-level locking hints. I'll repeat: Do NOT screw up your database design!

I can think of a way but it involves using a transaction on a common read - surely this isn't good database design? And shoving several entities into one table is? I give up. Do whatever you feel like.

Perhaps the theory is imperfect. Perhaps you should study some of the theory before you make quick judgements on the life work of some of the most brilliant mathematicians of our time.|||Hi Pierre,

Thanks for your reply.

"I can think of a way but it involves using a transaction on a common read - surely this isn't good database design? And shoving several entities into one table is? I give up. Do whatever you feel like."

Using a transaction on a read seemed to be what Maracatu was suggesting I do - that's why I was questioning its traditional design merit. It also seems to be what you're suggesting...

"simply write a stored proc to return the resultsets (all 3 or 4 or how many you need) inside a transaction with some row-level locking hints"

My point is that if you're going to resist my badly designed database and, assuming that using transactons for common reads (e.g. a shopper browsing a catalog ) is bad design, why are you proposing it as an alternative to my design? Is it just unfortunate that a transaction is needed in this situation?

"You can get consistent reads from your normalised tables just as easily using the same locking hints if you wish."

Well this is exactly what I'm after!

Actually I think I get it now. Even if you only lock a fraction of the records which need to be consistent using repeatable read, an update can't make data further down in the read inconsistent even though those records are unlocked, because it can't get to the records which are locked and so finish its transaction. Aha!

The only way an update could leave the database in an inconsistent state would be if you let it.

OK, so just use a transaction during the reading of the attributes and their derivations! In fact, I'd have to include the reading of the main product row in the transaction because it contains a field saying where to get the price from - in the main row, in price bands or in the combinations.

"Just lock the rows you are reading, that's good enough in your case and will allow other process to read other data from the table at the same time."

Other processes can read from a locked table if its a shared lock can't they? I think locking hint TABLOCK puts a shared lock on the whole table so it would only be modifiations which were locked out.

"I give up."

Please dont do that! I've been banging on about this for ages and you're the first person who has given me a decent response.

WT|||Look, you're worrying too much about this issue. Remember that even if you lock the tables during the read you will still have this much more real problem: What if you do all the selects, populate the screen and display it to the user. Then the user changes some dropdown boxes or whatever and saves the changes. BUT while h'e looking at the screen some other user changes that record in the background, so that when you do the the update the data in the database does not match what was on the screen anymore. This is a much more real problem than updates during the few milliseconds when the actual read takes place. The net result, however, is the same. Notice that no amount of locking or whatever will prevent this new problem. Therefore I suggest you do not worry about those few milliseconds of the actual read. It pales comparison to this problem. The correct way to address both of these problems at the same time is to check your data before you do updates.

Let me give you an example: Let's say you have a screen that displays a single product and a list of it's attributes. User 1 requests this screen, and at the same time User 2 deletes an attribute.

Now let's say you spent 2 months perfecting the locking of those tables and thus User 1's select starts before 2's delete. 2 will wait for the select to complete, and then it will delete the attribute. Now user1 is looking at a screen that does not match what's in the database anymore - he's looking at an attribute that does not exists on that product. The other possibility is that the 2's delete started before the select of 1, and in that case the user will be looking at what's actually in the database.

Now let's say you dropped this sillyness and left the locking. Now the results are like this: Either the select will include the deleted row (if the delete happened too late) or it will exclude it (if the delete happened early enough).

So... What do you see? The results are EXACTLY THE SAME! Bottom line is that you're worrying about nonsense. Leave the locking and transactions using the reads alone. Check your data on updates.|||Hey Pierre,

Thanks again for your reply. I think I'm beginning to get some bearing.

"Notice that no amount of locking or whatever will prevent this new problem."

Hmmm, surely you could have Pessimistic Concurrency if you saved the open database connection in session state? Of course, I'm not going to do that.

But sure, I think I have dealt with the problem you are talking about. I added a time stamp field to the main product row. At the start of the update transaction, it reads the current time stamp value using a repeatable read and compares it with the one gathered with the rest of the data sent to the update page.

If they're different, it sends a message to the user suggesting that they refresh/rebind the page else it continues with the update. So all updates of a product - even if its just to change a single option name - need to update the main product record in order to register a change. I'm not sure if this is lazy or not.

I figured that this was a decent way of dealing with the problem because if another user has added or removed an attribute ( which is quite hard to detect anyway ) , all of the first user's combinations are now invalid - which would make completing the update more work that it seems worth doing.

There's another design issue that I wanted some feedback on...

That is, how to identify a combination given the primary keys of the options which comprise it.

At the moment, I've given each combination an id string. So if a combination is made up of option 0 (pk = 356) from attribute 0 and option 0 (pk = 234) of attribute 1, the id string would be - "356_234". The column also has an index on it.

So when the shopper adds the product to the cart, one can look up the combination's price and code using..

SELECT * FROM productCombos
WHERE idStr = "356" + "_" + "234";

(The primary key values of the options would come from the drop down lists in the 'add to cart' form.)

I figure that this is much better than doing an indefinite number of self joins ( one for each attribute ) on the table which joins the options table to the combinations table like so.. ( this would be valid if there were just 2 attributes )

SELECT * FROM productCombos pC
inner join productVarDetails_Combos pVDC ON pVDC.productComboID = pC.productComboID
inner join productVarDetails_Combos pVDC2 ON pVDC2.productComboID = pVDC.productComboID
WHERE pVDC2.varDetailID = 356 and pVDC.varDetailID = 234

In fact, this join table doesn't really seem worth having.

Any thoughts?

WT.

Data from two databases

Hello.

I have situation here, whitch comfises me a bit. I have to IDENTICAL databases. Diference between then is that they where "filled in" at different times, one till 1st of January 2000, the other after 1st January 2000. Is it possibe to get data out of two databases into one query (resultset) covering periods, included in both databases (let's say from 1st January 1999 till 1st January 2003)

Thanks in advanceSELECT * FROM Server.DatabaseBefore.Owner.TableName
UNION
SELECT * FROM Server.DatabaseAfter.Owner.TableName|||Make sure to use a UNION ALL in this instance, not just a UNION. If you just say UNION then SQL will implicitly do a DISTINCT, which will take longer to process and is not at all necessary in this case.

Terri