|
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;
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 
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
ya how can again you set the primary key which already have two rows of same key ??? it is not possible...
suchita
|
|
|
|
|
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
|
|
|
|
|
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 
|
|
|
|
|
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.
|
|
|
|
|
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. 
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
I also agree with what David said
|
|
|
|
|
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
|
|
|
|
|
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;
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] ..
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 
I are Troll
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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  ) -- 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.
|
|
|
|
|
I inherited a table with over 250 columns. Many no longer used, several should have been in their own table.
But the table grew just like yours (need this one thing, well now I need this one other thing).
Building normalized to start is the best path.
|
|
|
|
|
PIEBALDconsult wrote: 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.
Not quite sure about the icy cucumbers  but yes, you've hit the nail on the head, that's exactly what it's for. I actually finished college 5 years ago, and this is the first occasion that has presented itself to put into practice what I actually learned, so I'm a bit rusty at the minute and having to rely on lecture notes, assignments and Database Systems (2nd Ed.) by Begg and Connolly, which is heavy going at times to say the least. 
There is only one satisfying way to boot a computer.
|
|
|
|
|
Digital Thunder wrote: heavy going at times
Yeah, the other week I again looked up (Wikipedia and others) third-normal-form and again couldn't make sense of the discussion. I absolutely know how to use third-normal-form, but the terms that are used to describe it are non-sensical (unless there is someone there actually telling you and applying it to some data).
It's kind of like being able to read, write, and speak a language fluently but not understanding linguistics. Really, only cunning linguists need to know what the term "pluperfect" means or even that it exists. And all those hours spent diagramming sentences didn't help us speak betterishly.
|
|
|
|
|
PIEBALDconsult wrote: unless there is someone there actually telling you and applying it to some data
I know EXACTLY what you mean. When the lecturer is explaining examples to drive home a point, it's quite obvious what he is actually getting at. However, I to have looked at a few examples on the web and some of them have only served to confuse me even more than I was! I did however, find a very good article that condensed a lot of detail into relatively few pages and the examples given really did a good job in explaining the detail.
There is only one satisfying way to boot a computer.
|
|
|
|
|
Digital Thunder wrote: confuse me even more
Exactly.
Digital Thunder wrote: a very good article
Got a link?
|
|
|
|
|
|
Query for Getting Max number.
For Example: I have Department and Employee
For DeptId 1: Emp No 12,16,19
and For 2: 18,21,17
For 3: 27,28,16
I have to get Max(EmpNo) For department. If I want to get Max(EmpNo) for Dept(1,2)
output will be 19,21.
How can I get this using sqlserver2005
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
