|
Hello, I am creating a desktop database application with C# and MS SQL SERVER 2008 EXPRESS,
and I have a few Q's about it.
Let's suppose we have the table customers, with structure like this:
Customers:
|ID|Nmae |SecondName | Adres |ShipID|Promoted |
------------------------------------------------------
|1 |John |axxx | ytutyt | 5 | false |
|2 |Adam |bxxx | qwefsd 18 | 23 | false |
|3 |Eddie |cxxx | fsdfssf 7 | 28 | true |
|4 |Eddie |dxxx | fsadsdf 42| 14 | true |
|5 |Mark |exxx | hdgdssf 2 | 33 | true |
|6 |Mathew|fxxx | xcvxzx 75 | 45 | false |
I am new in the Database programming, but knowing the goal of my application,
I suppose in the future I will experience at least 2 kinds of problems:
1) I guess every month I will have even 100 000 new records but there are necessary
only for two months. After that time I would like to move the unncessessarty records
somewhere else than in the customers table in order to avoid performance loss, but
I'd like to have access to them on demand.
Whats the best way to do it? To create other table with schema like Customers,
and every two months move data from Customers to that new table,
so that customers will always contain data maximally 2 months old?
2) I would like a new logical table PromotedCustomers containing
every record from Customers that has "Promoted" value set to true. What is essential here,
is that I would like searching in this new table faster than searching in Cusotmers table.
For example, supposing I have Customers table like above:
SELECT Name FROM Customers WHERE Promoted = true AND Name = Eddie
returns 2 records, but has to search 6 rewcords (whole Customers table)
If I had PromotedCustomers table the way I want, I could write:
SELECT Name FROM PromotedCustomers WHERE Name = Eddie
It also returns 2 records, query is a little bit shorter, but whats most important for me:
ONLY 3 RECORDS HAS TO BE SEARCHED, and performance is important in my app.
Is it possible to create such a table?
Thanx for any help 
|
|
|
|
|
I think your data design may be flawed (I don't know your business so I cannot be more definite), the ShipID on your customer table makes me suspicious that you are creating a new customer per order. You should have at least 3 tables to support an order processing system. Customer, Order and OrderLines. 100k customers per month also makes me suspicious of your structure.
100k records per month is not a large volume (for SQL Server, I don't use express) and I would look to archiving annually rather than monthly. Then I'd use an OLAP cube to retain reporting infrastructure.
Promoted is a flag/attribute of the Customer table, not a separate table. Remember every field does not need to be filled in the table. I would make the promoted field a date field giving you the option of making time based queries.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
i have a little problem. I have a table "customers" and a table "partnerships". In the customer database i have stored a few customer with a ID as primary key. In my partnership database i have two columns (partner1, partner2) where i want to store partnerships between them. So now the problem, it should be unnessecary if partner1=1 and partner2=2 or partner1=2 and partner2=1. And one partnership shouldn't be stored two times. So how can i realize this? In my selects it is easy, i easily have:
select * from partnerships where (partner1='1' and partner2='2') or (partner1='2' and partner2='1')
But how to make the right constraints, that this isn't saved to times, so i don't want to have something like this:
partner1 partner2
1 2
2 1
Maybe someone has a solution for me?
|
|
|
|
|
I cant see a simple way to do this using relationships, you could set up a complex structure that concatenates the IDs in order of size and faff about with that but it would be ugly as sin. I'd just have a proc that checked for existence before inserting.
OR make it a convention that the highest is always Parent 1, this does not help with constraints though it would simplify your query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi
I want to restore mdf file in my sqlserver database. i am using:
EXEC sp_attach_single_file_db @dbname = 'testerDB',
@physname = 'F:\DB.mdf'
to restore that mdf file
but it gives the error:
ERROR: ERROR: Unable to open the physical file "F:\DB.mdf". Operating system error 5: "5(Access is denied.)".
I am working with SQL Server 2005 express edition
and Os is Vista Ultimate.
Please Help
Thanks in advance
|
|
|
|
|
There are about 1.2^10 reasons why this would occur and none of them have anyting to with your OS being Vista.
“If you think you are worth what you know, you are very wrong. Your knowledge today does not have much value beyond a couple of years. Your value is what you can learn and how easily you can adapt to the changes this profession brings so often.”-– Jose M. Aguilar
|
|
|
|
|
If u don't know the answer then its ok...
|
|
|
|
|
Ok, smarty pants, see Eddy's post to understand why your problem had absolutely nothing to do with the OS you are using.
“If you think you are worth what you know, you are very wrong. Your knowledge today does not have much value beyond a couple of years. Your value is what you can learn and how easily you can adapt to the changes this profession brings so often.”-– Jose M. Aguilar
|
|
|
|
|
A file that ends in ".mdf" is a database, not a backup. You can restore backups (*.bak), but not the "*.mdf".
You can, however, attach the database to your local server by right-clicking on the "databases"-node and choosing "attach" from the SQL Server Management Studio.
Good luck with your venture
|
|
|
|
|
hello i want to make a project which is a web site for hospital
when a user log in to the web site he/she must can know some info about the hospital such as : doctors , beds empty ...
dotor in this hospital also must they know every thing about their patiant when they log in
please help me quickly with the er diagram so i can start my projec.
|
|
|
|
|
Can you tell us which hospital it is, so we can avoid it
If you have no idea about even doing the er diagram what chance have you when it comes to coding.
Anyway, sounds like a piece of coursework, so do it yourself and actually EARN your pass.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I'm scared, very very scared! 
|
|
|
|
|
Paging Doctor Pain, paging Doctor Pain...
“If you think you are worth what you know, you are very wrong. Your knowledge today does not have much value beyond a couple of years. Your value is what you can learn and how easily you can adapt to the changes this profession brings so often.”-– Jose M. Aguilar
|
|
|
|
|
|
Hello guys,
I would like to create a log of what is happening in my database. What i exactly mean is that i need to log every update, insert and delete statement that executes in my database. for example when a delete happens i want to keep the values that have been delete, when an update happens i want to keep the old values and the new ones etc.
Another important thing is that i want to achieve this without the use of triggers is possible.
How can this be done?
|
|
|
|
|
cdpace wrote: Another important thing is that i want to achieve this without the use of triggers is possible
Can't see an alternative to triggers myself. How else do you effectively capture the insert/update/delete events.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
ok got your point can i ask you one more thing what are the column names for
the the following tables that are used in triggers
Table names:
inserted
deleted
|
|
|
|
|
Check this.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
The column names for inserted and deleted are the same as the table the trigger is for. F|or inserts just inserted is poulated, for deletes just deleted is populated and for an update, guess what, they are both populated.
I often select the inserted & deleted as xml and store them in a songle audit record.
Hope this helps
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
This helps allot thank you very much. Can i ask you one last thing have any links that show how i can retrieve the data as xml? thank you very much
|
|
|
|
|
Just take a look at BOL or google, is not difficult.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Triggers are hell slow don't use it use C2 audit
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
softdev_sup wrote: Triggers are hell
True, if badly written, but we use them regularly for auditing on tables with upwards of 20 million transactions a month withu problems.
softdev_sup wrote: use C2 audit
Never used it, does it acually record before and after images, user etc? Also I would be worried about it bringing the system down - from MSDN
Important:
C2 audit mode saves a large amount of event information to the log file, which can grow quickly. If the data directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag (which bypasses auditing), or free up additional disk space for the audit log.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
well i tried one software ApexSQLAudit for the auditing sql and it uses triggers and the system performance after using it was so bad almost dead while beofre using this ApexSQLAudit the system was efficient but after this it was very slow and c2 and sql profiler trace did the job
i am confused now because that system is running now more then 2 years and is not crashed but by using the triggers we had to remove ApexSQLAudit within one week because of slowness
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
So you used a generic logging system and it slowed your system. No big suprise there. As I said, we use triggers for auditing and have never had performance issues, but we write them ourselves so they are optimised for our system, with the audit tables properly located and indexed etc.
You may have had a bad experience with triggers, but do not assume they are bad, its just a case of using them properly - I have no experience of ApexSQLAudit, but I wouldn't expect some generic product to be as performant as one written specifically for your database by someone who knows what they are doing.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
