Database

FirstPrevNext

Multiple tables for same category in database? kbalias15-Jun-05 2:39 kbalias 15-Jun-05 2:39  Hi all A database question. Say for example I have a Suppliers table and a Products table in my database. If theoretically each supplier can supply millions of products what would be the most efficient and convenient? Having a different Products table for each supplier, or a single Products table holding products of all suppliers. If the first option is better would it not be very tricky querying the data for reports and so on? Thanks again. Kobus Re: Multiple tables for same category in database? Colin Angus Mackay15-Jun-05 2:53 Colin Angus Mackay 15-Jun-05 2:53  kbalias wrote: what would be the most efficient and convenient? That depends on how you are going to use the data. You can split the data by supplier and have a partitioned view to stich it back as if it were one big table - you can then query through the view. Personally, I would keep it as one supplier table and one products table unless you run into performance problems then do work on eliminating the performance issues. I find that second guessing SQL Server on performance is often counter productive because the usage patterns are often not what was originally envisaged. My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More Sql Query Required! Zishan Haider15-Jun-05 2:28 Zishan Haider 15-Jun-05 2:28  I have a column with type datetime in an employee's table. I want to delete the records that older than 3 working days. The problem is I can't use getdate()-3 in where clause because I don't know about the holidays and all.. Can anyone help me out ? Regards, Zishan Re: Sql Query Required! Colin Angus Mackay15-Jun-05 2:41 Colin Angus Mackay 15-Jun-05 2:41  Zishan Haider wrote: The problem is I can't use getdate()-3 in where clause because I don't know about the holidays and all.. Can anyone help me out ? You will need information on the holidays if you are to achieve this. My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More Re: Sql Query Required! Zishan Haider15-Jun-05 2:46 Zishan Haider 15-Jun-05 2:46  Can't I use some kind of loop in the SQL Re: Sql Query Required! Colin Angus Mackay15-Jun-05 2:49 Colin Angus Mackay 15-Jun-05 2:49  You can use loops in SQL but they are very inefficient. However, you've already said that you don't know the holiday information so I don't see how a loop is going to be useful. My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More Re: Sql Query Required! Colin Angus Mackay15-Jun-05 3:01 Colin Angus Mackay 15-Jun-05 3:01  Assuming you have a table called Holidays with the a column containing the date of each holiday you could write a query like this: DECLARE @days int; SELECT @days = COUNT(*)+3 FROM Holidays WHERE HolidayDate <= getdate() AND HolidayDate >= getdate()-3 You can then use the @days variable to count back the number of days. For example: DELETE FROM MyTable WHERE somedate < getdate-@days Does this help? My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More Re: Sql Query Required! Zishan Haider15-Jun-05 21:36 Zishan Haider 15-Jun-05 21:36  Well.. you're right couldn't do it in SQL without a holidays table. However I did it in my C# program. I selected distinct dates from table ordered by Date Desc. Then I deleted every record with the date from my select query ignoring the first three.. Yes I know its poor performance, but this chunk has to run once every morning so I don't need any major performance here. And I had to do it without A holidays table. Thanks for your help Regards Zishan Questions OleDB JET4 Saksida Bojan14-Jun-05 23:07 Saksida Bojan 14-Jun-05 23:07  1. How to look witch tables are in the database. 2. Is possible to create database with my application. And How? Is MSDE overkill for single computer? Nathan Holt at EMOM14-Jun-05 12:11 Nathan Holt at EMOM 14-Jun-05 12:11  I work on several programs that require ordinary users to be able to change settings system wide (specific to the programs). However, Windows security is evolving toward forbiding any of the obvious aproaches to sharing data in this way (such as HKEY_LOCAL_MACHINE, and files in the same directory that the program is installed in) The simplest solution that I've been able to find is to use an MSDE database to store shared settings. However, a database server intended to be used over a network seems to be overkill to me. Does anyone have some ideas for my problem? Nathan Holt Re: Is MSDE overkill for single computer? Christian Graus14-Jun-05 14:00 Christian Graus 14-Jun-05 14:00  MSDE is actually limited in how many network connections it can take, however, it sounds like Access would do what you want. I have deployed MSDE for use on single machines, but I was building a database that Access just wasn't good enough for. Christian Graus - Microsoft MVP - C++ Re: Is MSDE overkill for single computer? Nathan Holt at EMOM15-Jun-05 6:15 Nathan Holt at EMOM 15-Jun-05 6:15  Christian Graus wrote: MSDE is actually limited in how many network connections it can take, however, it sounds like Access would do what you want. I have deployed MSDE for use on single machines, but I was building a database that Access just wasn't good enough for. Thanks. I finally found info yesterday that one is allowed to have shared files in the All Users/Application Data provided that one makes a subdirectory and sets its permisions so that all users can write to them. That makes most of the code I have easily fixable. Nathan Holt Database Event Log irwtla14-Jun-05 6:22 irwtla 14-Jun-05 6:22  Are the effects of DDL events like droping or altering an object stored anywhere in SQL Server 2000? For example, if a stored procedure is altered is there some way of getting this information. The SQL server logs only seem to contain info on database level events (start, stop, backup, dbcc check, etc). Is there something similar that would allow me to find the date modified of a table or when a stored procedure was dropped? Make COUNT(*) return 0 Luis Alonso Ramos14-Jun-05 5:48 Luis Alonso Ramos 14-Jun-05 5:48  Hello, I'm having a little problem with the COUNT function. I have a table with payments for customers (say 42 or 54 each), and each payment is marked as paid whenever the customer comes and pays it. I want to make a query that gives me all the customers with the number of paid payments (including those customers new for this week). The total number of customers is 328, but if I add the COUNT(*) the resulting table only contains 291, all with 1 or more payments (meaning 37 customers have 0 payments.) Looking in the web I found that COUNT(column) returns the number of rows in which that column is not NULL. In this case, I'm using COUNT(*) which should return the count of all rows. I'm also using a WHERE clause to limit the counted payments to only those that have been paid. I tried a LEFT JOIN, so that at least the resultset would include the rows, even if the count column is NULL, but it didn't work. Any ideas? -- LuisR Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog! Re: Make COUNT(*) return 0 Daniel Turini14-Jun-05 6:43 Daniel Turini 14-Jun-05 6:43  SELECT ISNULL(COUNT(*), 0) ... I see dead pixels Yes, even I am blogging now! Re: Make COUNT(*) return 0 Luis Alonso Ramos14-Jun-05 3:55 Luis Alonso Ramos 14-Jun-05 3:55  Not exactly the answer I needed, but that'll help too. I think my problem was that I was counting all the records from a resultset matching a criteria instead of counting some records matching a criteria from resultset from all records. I split the query into embedded subqueries and the results came as expected, except I was getting NULL instead of zeros. Your suggestion fixed it. Thanks! -- LuisR Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog! Re: Make COUNT(*) return 0 toxcct14-Jun-05 20:52 toxcct 14-Jun-05 20:52  do you group by the right columns ? how is your select written please ? TOXCCT >>> GEII power [toxcct][VisualCalc] Re: Make COUNT(*) return 0 Luis Alonso Ramos15-Jun-05 6:53 Luis Alonso Ramos 15-Jun-05 6:53  I fixed my problem. See this post[^]. Thanks anyway! -- LuisR Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog! OUTER JOIN, Crystal Reports V.13-Jun-05 23:40 V. 13-Jun-05 23:40  Hi, Is there a reason why sometimes some of the link options in Crystal Reports are disabled? eg the FULL OUTER JOIN. I can't think of a reason why a full outer join wouldn't work? Hope somebody can help me. tnx! No hurries, no worries. SQL queries with adapter scchan198413-Jun-05 18:02 scchan1984 13-Jun-05 18:02  Hi, I found that there are two practices for writing queries for adapter. 1. one adapter queries for one table in the database e.g. select * from customer 2. one adapter queries for joinned table in the database e.g. select * from customer c1, country c2 where c1.country = c2.country Are the update/insert/delete command of an adapter work only for one table only? Which practice is encouraged? If the two tables are filled into the dataset, are there any SQL queries method to do the "join" operation to the two tables? anymore operations can be done? Thanks Re: SQL queries with adapter Colin Angus Mackay13-Jun-05 22:34 Colin Angus Mackay 13-Jun-05 22:34  scchan1984 wrote: Are the update/insert/delete command of an adapter work only for one table only? Personally, I would never rely on the auto generated code for the INSERT/UPDATE/DELETE commands in an Adapter - because it cannot assume anything about the database it creates some awful SQL to ensure that it works. scchan1984 wrote: Which practice is encouraged? I would encourage you not to use any Wizard generated adapters. Also, never do a SELECT * if the underlying table changes then the results back to your application may be different and your application may not expect that. My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More SQL Server distribution Tom Archer13-Jun-05 14:08 Tom Archer 13-Jun-05 14:08  Is there a way to package and deploy only the engine and any runtime support for a SQL Server database? If not and I have to tell the user that SQL Server is required, what is the easiest way to install my application's schema and data into an existing SQL Server database? In previous situations, I've exported the data and used the bcp command-line app to import the data. Is that the best way or is there an alternative? Cheers, Tom Archer - Archer Consulting Group "So look up ahead at times to come, despair is not for us. We have a world and more to see, while this remains behind." - James N. Rowe Re: SQL Server distribution oykica13-Jun-05 19:48 oykica 13-Jun-05 19:48  If you are referring to MSDE you can include the MSDE Merge Module in your Windows Installer. Check out this link for more information. http://www.sqljunkies.com/Article/484612DD-0416-42C8-9695-9572CA762ED6.scuk There a many different approaches to creating databases and uploading data. This SQL Server Magazine article may point you in the right direction. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/scriptingacustomdatabaseinstallation.asp Stored procedure table parameter adilson05uk12-Jun-05 23:25 adilson05uk 12-Jun-05 23:25  I have a DataTable (a table in ASP.NET) in memory which I need to pass into an SQL stored procedure. How can I achieve this? Is there any to pass the contents of the table into my stored procedure given that the size (number of rows) of the table is unknown? Re: Stored procedure table parameter Christian Graus13-Jun-05 14:07 Christian Graus 13-Jun-05 14:07  Convert it to an XML document and use OpenXML in your stored procedure to turn it into a table. Christian Graus - Microsoft MVP - C++

Last Visit: 31-Dec-99 18:00     Last Update: 21-May-24 10:54 Refresh ᐊ Prev1...2089209020912092209320942095209620972098 Next ᐅ

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.