Database

Re: Table Optimization Problem - SQL Server

23-Jul-10 5:06

Jacobus01 wrote:
Will it help if I modify all the select statements in these apps to not have locks eg "select [columns] from [tablename] with (nolock) where..."?.

It feels faster, doesn't it? The documentation on MSDN reveals why it's faster;

MSDN[^]:
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

That means that you could be reading data that's not committed. I'd be very carefull if the database contains a lot of stored procedures and utilizes transactions a lot.

How are the indexes, and what does the profiler say?

I are Troll Suspicious | :suss:

Mycroft Holmes23-Jul-10 14:00

Mycroft Holmes

23-Jul-10 14:00

I assume that CutID is the FK to the tables, just wondering why it is not the primary on one of the table? Totally irrelevant to your problem.

I would first put in place a nightly maintenance job that cleaned up the indexes.

As to Eddys issue with uncommitted data, I would assume that queries are filtered and therefore this dodgy data should not be an issue except where you are specifically getting the latest information. There tends to be a small window of data that is subject to change, the last few records in the table. If you give your users a caveat that it may be dodgy I see no reason (nolock) should not be used.

CAVEAT - note I have never used (nolock) so take this cautiously.

Never underestimate the power of human stupidity
RAH

primary key

Neha_Gupta23-Jul-10 2:28

Neha_Gupta

23-Jul-10 2:28

If a primary key is dropped from a table and some records are duplicated after dropping primary key, then is it possible to apply the primary key on the same column. If yes, then how??

NEHA GUPTA

R. Giskard Reventlov23-Jul-10 2:34

R. Giskard Reventlov

23-Jul-10 2:34

No, not really. The whole point of a primary key is that it is unique for each row (be it a surrogate key or a key made up from several columns). If you have duplicate rows then, by definition, you will be breaking the rule. Remove the duplicate rows and then apply a primary key.

Why would you deliberately remove the primary key and add duplicate rows?

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Dhyanga23-Jul-10 3:01

Dhyanga

23-Jul-10 3:01

ya how can again you set the primary key which already have two rows of same key ??? it is not possible...

suchita

Neha_Gupta23-Jul-10 3:37

Neha_Gupta

23-Jul-10 3:37

but there is some option when we alter the table, (when we add constraint) with an option "with check" and "with nocheck". What is that option? Please clarify..

NEHA GUPTA

Eddy Vluggen23-Jul-10 4:55

23-Jul-10 4:55

If you add a new constraint, Sql Server will test whether or not all of the records that are currently in the table pass that constraint.

Adding the NOCHECK option will skip that test.

You'd have to remove any double entries to recreate your primary key.

I are Troll Suspicious | :suss:

Database Normalisation

Euhemerus22-Jul-10 9:00

22-Jul-10 9:00

I'm currently in the process of creating a service records database for storing information on machine servicing.

In the un-normalised customer table I have three attributes for recording telephone contact details: LandlineNumber, MobileNumber and FaxNumber.

Ordinarily, these attributes should be decomposed through the normal forms; 1NF, 2NF and 3NF.

TELEPHONE_1NF(TelNumber, CustomerNumber, TelNumberType)

TELEPHONE_2NF(TelNumber, CustomerNumber)
TELEPHONE_TYPE_2NF(TelNumber, TelType)

TELEPHONE_3NF(TelNumber, CustomerNumber)
TELEPHONE_TYPE_3NF(TelNumber, TelType)

My question is, in the real world, do you database designers out there ACTUALLY do this, or would you leave the three different telephone attributes in the customer table and invite null entries? Or, do you use a different strategy completely?

There is only one satisfying way to boot a computer.

David Mujica22-Jul-10 10:56

David Mujica

22-Jul-10 10:56

My vote is to leave the 3 telephone attributes in the Customer Record and allow for blank/Null numbers.
There is an argument for keeping things simple and in this case I would go for simplicity. Cool | :cool:

Euhemerus22-Jul-10 11:05

22-Jul-10 11:05

Thanks for the advice. My college lecturer would be spitting in his beer if he even knew I was asking the question! However, having been taught the 'correct way' I'm wondering what real database designers ACTUALLY do; whether they stick to the theory or compromise.

There is only one satisfying way to boot a computer.

Mycroft Holmes22-Jul-10 14:21

Mycroft Holmes

22-Jul-10 14:21

In most business cases I would go for the denormalised structure as David suggested.

However I have seen this type on Nth level normalisation work quite well, especially if it is a commercial product and must be extensible to meet user requirements. It also requires a GREAT support team and constant retention of the IP in the development team.

Corporate products can accept greater restrictions on their boundaries. If you want 4 phone number then stick it in Outlook, bugger off.

Never underestimate the power of human stupidity
RAH

Goutam Patra22-Jul-10 22:00

Goutam Patra

22-Jul-10 22:00

I also agree with what David said

R. Giskard Reventlov22-Jul-10 22:43

R. Giskard Reventlov

22-Jul-10 22:43

I would take the view that people may have more than 3 contact numbers (attributes) and by normalising out you are allowing for any number of numbers, so to speak, and building in the ability to add as many as required in the future. On the other hand if 3 numbers is set in stone (as if anything ever really is) then leave as is.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Eddy Vluggen22-Jul-10 23:16

22-Jul-10 23:16

Your teacher is right, 3NF would be the minimum requirement before I'd call it a well-designed database. You are right too; in the real world, databases are created when needed, and grow over time - not all of them are designed upfront.

Whenever I can, I try and normalize. Your example would make it very hard to get the complete address-details of the customer, since you'd be seeing a similar construction with ZipCodes and streets.

Digital Thunder wrote:
would you leave the three different telephone attributes in the customer table and invite null entries?

You don't want to do this;

SQL

SELECT ([phone1], [phone2], [phone3]) from ..

Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines.

That would lead to a

SQL

SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..

Might not sound very useful in this particular example, but I'm sure that most of us had to update a similar database at least once in their career. Imagine you have a table that all of a sudden would receive four or five more attributes Smile | :)

I are Troll Suspicious | :suss:

Euhemerus23-Jul-10 3:09

23-Jul-10 3:09

Eddy Vluggen wrote:
You don't want to do this;

SELECT ([phone1], [phone2], [phone3]) from ..

Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines.

That would lead to a

SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..

Hmmm, I see what your saying. So, I suppose the answer to my original question is "it depends" judging by the range of replies that the question solicited. It depends on what the database is actually designed to achieve in the first place and what are the consequences moving from a theoretically correct design to a design that has compromises which fly in the face of conventional theory.

My thanks go out to all of you that took the trouble to reply to the original question.

There is only one satisfying way to boot a computer.

Eddy Vluggen23-Jul-10 9:13

23-Jul-10 9:13

Digital Thunder wrote:
s moving from a theoretically correct design to a design that has compromises

Stick to the theoretically correct design, and make sure that you can explain why you denormalized that particular table.

Your teach has prolly given some good arguments why you should apply at least 3NF. A good reason for denormalization would be partitioning - if the table were too large, and you'd like to divide it over two databases.

IRL you'll find lots of denormalized databases, simply because there's lots of people creating databases who never heard of Codd, or decided to take a shortcut.

I are Troll Suspicious | :suss:

Euhemerus23-Jul-10 10:08

23-Jul-10 10:08

Eddy Vluggen wrote:
Your teach has prolly given some good arguments why you should apply at least 3NF.

He certainly did. Basically, we were told, without normalising to 3NF we wouldn't be able to get the database to do what we want it to do and that it would be rather error prone and that if we wanted to make any money out of database design then we had better normalise to 3NF otherwise we would be penniless! The latter is a very convincing argument.

There is only one satisfying way to boot a computer.

PIEBALDconsult23-Jul-10 7:34

PIEBALDconsult

23-Jul-10 7:34

I have learned through experience to do what your instructor describes -- things will become more complex over time. Do it correctly now.

"It's better to have it and not need it than to need it and not have it."
"A stitch in time saves nine."

At one place I worked, we needed to hold a credit card number for each account (actually, account-holders also had the option not to provide one) -- great, it (and its type; Master Card, Visa, etc.) was in the Account table. Then we had a customer who wanted to store two credit card numbers (so there would be a backup in case the other was declined Roll eyes | :rolleyes:

) -- OK, the DBAs added it to the Account table with another field to indicate which one had been charged most recently. Then we had another customer who also wanted us to support ACH transactions against bank accounts -- oh, crap -- the DBAs added yet more fields to the Account table. I left the company after writing a document explaining that I thought that all that data should be moved to a separate table before things got completely out of hand.

I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.