|
Hi,
I'm a little confused with my table relationships. I have a Customer table and Title table. Title is something like Mr., or Miss., etc.
I have a foreign key reference in the Customer table to the Title. How would I read this relation.
...for each customer there is only 1 title, and for each title there is only one customer, or
...for each customer there is only 1 title, and for each title there is one or many customers??
This is a 1:1 relation, or 1:M?
Please help.
Regards
|
|
|
|
|
This is how you read:
For each customer you can only have one title, and for each title you can have one or more customers.
|
|
|
|
|
As John says, a foreign key provides a one-to-many relationship. A one-to-one relationship doesn't make much sense as you may as well combine the tables into one. I'm not sure you can even create such a relationship because you'd have to simultaneously insert into both tables in order not to break the constraint. Anyone?
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: A one-to-one relationship doesn't make much sense as you may as well combine the tables into one. I'm not sure you can even create such a relationship because you'd have to simultaneously insert into both tables in order not to break the constraint. Anyone?
1-to-1 relationships can make sense in some circumstances when dealing with optimisation of very large rows, especially if some of the columns are only very rarely neaded. It is also useful when dealing with joining separately maintained systems that need to keep in sync (e.g. if you buy in a third party system and want to extend it without touching its database tables)
Typically it would actually be a zero-to-one because you insert one row then they other.
Parent
------
ID PK
Col1
Col2
Col3
Child
-----
ID PK FK
Col4
Col5
Col6
The Child row is optional, but there can be only one as its primary key is also the foreign key (which forces the oneness of the relationship)
|
|
|
|
|
This is a 1:M relationship. The reason being that you can have many Mr. or Mrs. Customers but a customer can only be ONE either Mr. or Mrs.
Anyone???
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
Skanless wrote: Anyone???
Do you mean other than the two people who answered the question before you?
|
|
|
|
|
I want to compare 2 tables of different sql databases. How can i acomplish this task?
I request u to guide me for the same.
Gurudatta B. Shelke
|
|
|
|
|
if the databases are on the same server, you can just prepend the database name to the table in your query. eg. database1.dbo.Table1, database2.dbo.Table2.
To see what's different you probably want a full outer join.
Regards,
Rob Philpott.
|
|
|
|
|
Am not aware of any database/table comparison tool that is bundled together with the commercial/non-commercial SQL Server but some third party tools exist. You can Google for 'ApexSQL Diff', 'SQL Delta', or 'SQL Compare' among many others. They are commercial but you first get a chance to evaluate.
|
|
|
|
|
|
Hi,
I am confused as when to use varchar and nvarchar. Can there be a performance issue when maybe choosing nvarchar above varchar and vice versa? Please provide examples when you will use both??
Thanks
|
|
|
|
|
Put simply nvarchar is unicode - 2 bytes/character, varchar is ASCII - 1 byte/character.
so nvarchar takes twice the space of varchar and consequentally won't be as quick. If you need multilanguage support go for unicode as this can hold just about all character sets on the planet. If its a plain english app and always will be stick with varchar.
Regards,
Rob Philpott.
|
|
|
|
|
You never know when Mr Gütenheim needs to register on your site, or when you add the new product "übermaster 10000".
English only is not always so cut and dried. Use unicode unless theres a real performance or storage reason not to.
|
|
|
|
|
What is the best way to write an SQL stored procedure: by using temporary tables (e.g #tmpSomething or @tmpSomething) or by using multiple selects? How is this affecting the speed of execution and the memory usage? Thank you in advance.
I am fighting against the Universe...
Reference-Rick Cook
|
|
|
|
|
Albu Marius wrote: What is the best way to write an SQL stored procedure: by using temporary tables (e.g #tmpSomething or @tmpSomething) or by using multiple selects? How is this affecting the speed of execution and the memory usage? Thank you in advance.
It depends on what query you are running. There is no simple answer. Experiment, see what is better. Then 6 months later when your database schema has changed expect the performance metrics to have changed that your initial findings no longer hold true.
|
|
|
|
|
I'm sorry, I should have been more specific in my post. The situation : 2 selects (not very large)each returning about 3000 records . One select is saved in a temporary table (#tmpSomething) and the other one is joined with the first and the result is outputed (duration - few miliseconds). I was advised to never use temporary tables because of the performance issues. I know this is the case of @ tables which store all the data in memory and for a large number of records (tens of thousands) the SQL Server hangs for a time before returning the result. But my problem is why can 't I use # table (which are stored on disk)? Is the impact to performance so big due to the I/O operations? As I said I was advised to always use a select contained in the bigger select instead of temporary tables. This seems to me as a bad practice because this way the SQL code if pretty hard to understand and I'm not sure this resolves the performance issues. If someone can shed some light on this dilema of mine , please do.
I am fighting against the Universe...
Reference-Rick Cook
|
|
|
|
|
Albu Marius wrote: I was advised to never use temporary tables because of the performance issues.
That is rubbish. A few years ago I went from taking 20 minutes for a query down to 7 seconds by introducing a couple of temporary tables. (The source table was growing at a gigabyte per week, so there was A LOT of data in there)
Albu Marius wrote: I know this is the case of @ tables which store all the data in memory and for a large number of records (tens of thousands) the SQL Server hangs for a time before returning the result
That is also incorrect. Table variables (or @ tables as you put it) are just anothe form of temp table. They will get flushed to the TEMPDB if it needs to, but typically you'd use them in situations where the dataset was small enough that it wouldn't go there.
Albu Marius wrote: But my problem is why can 't I use # table (which are stored on disk)?
Again, incorrect. If they are small enough and used quickly enough they will be created, populated and dropped without needing to be flushed to disk. Of course the log will still be updated.
Albu Marius wrote: Is the impact to performance so big due to the I/O operations?
Seriously, you need to experiment. There is so much going on that you can't know otherwise. I don't know, you don't know and anyone that says they do know is lying.
The myths you have picked up are a result of someone at ONE time seeing that it operates in a particular way. The next query with the same technique make operate differently because the query optimiser recons it will be better.
Albu Marius wrote: As I said I was advised to always use a select contained in the bigger select instead of temporary tables. This seems to me as a bad practice because this way the SQL code if pretty hard to understand and I'm not sure this resolves the performance issues. If someone can shed some light on this dilema of mine , please do.
You are right it is bad practice. But the bad practice is to have a rule that doesn't take in to account the current environment your query is operating in.
Temp tables can improve performance (as I demonstrated above) and quite significantly. They can also reduce performance. Which is why you need to experiment with different ways of doing things.
|
|
|
|
|
Thank you for your advice. Your explanations cleared this issue for me.
I am fighting against the Universe...
Reference-Rick Cook
|
|
|
|
|
how can i block the data in Data base (SQL server 2000)
by variable in the data base like Year
please some definition and detailes
ahmed eldeghedy
|
|
|
|
|
ahmed eldeghedy wrote: please some definition and detailes
Yes, that would be nice.
ahmed eldeghedy wrote: how can i block the data in Data base (SQL server 2000)
by variable in the data base like Year
What do you mean by "block"?
Do you mean filter out?
SELECT * FROM SomeTable<br />
WHERE Year = 2007
|
|
|
|
|
Colin Angus Mackay wrote: What do you mean by "block"?
What does he mean?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Do not email me - If I respond it is for everyones benefit, not just yours. Other people make have the same problem and are searching the forums for an answer, if I email a response those people will not benefit.
ahmed eldeghedy wrote: what i mean
when i take instans form the object (tabel database)
i whant the data bases retrun table by the data not block
not filter
ahmed eldeghedy
That still does not make sense. What do you mean by "block"? How do you want data blocked? What sort of data do you want blocked?
Perhaps you want to revoke access to the tables and have everything done through views and grant access to the views based on the role the user has. Perhaps you want to do everything through stored procedures which will only do what the stored procedure has been programmed to do and nothing more. But I just don't know from your description.
|
|
|
|
|
Colin Angus Mackay wrote: That still does not make sense.
It certainly still makes no sense 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hello Everybody
Table Name dbo.Books where I kept Books Information. At First You need to put All information on dbo.Books table. Now this book is not Active because I need to make a Copy of this book which will be stored at dbo.CopyBooks table.
dbo.Books table
ISBN BookName
1001 Sql Server 2000
1002 Sql Server 2005
Now I want to make Copy of those book
dbo.CopyBooks
ISBN Book_ID
1001 1001101
1001 1001102
1001 1001103
1002 1002101
1002 1002102
1002 1002103
1002 1002104
First four digit is ISBN number and last three digit indicate book copy number 101 is the First Copy, 102 is the Second Copy.
How will I do it?
Thanks
Sarfarj Ahmed
|
|
|
|
|
You could add another column to CopyBooks called CopyID. This would be a sequence no. uniquely identifying a copy of each book. For example:
ISBN CopyID Book_ID
1001 101 1001101
1001 102 1001102
1001 103 1001103
1002 101 1002101
1002 102 1002102
1002 103 1002103
1002 104 1002104
Book_ID could then be changed to a computed column using the formula (CONVERT([varchar],[ISBN],0)+CONVERT([varchar],[CopyID],0)).
To find the next CopyID for a particular ISBN, all you have to do is find the current highest CopyID for that ISBN and add 1.
Paul Marfleet
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
