Better go with a single table with a bit field for product type.
Maintaining 2 different table for the same identity is not a good dB design practice.
The performance wont be an issue with the size of record that you are saying.
Implement indexes on the columns that you will use in the query conditions.
Now regarding the selection of database, for ASP.Net, better go for MS-SQL Server. As .Net framework provides native driver for MS-SQL Server. This is the managed driver and performance is better.
However, the hosting cost for MS-SQL Server is higher, if budget is a constraint, you can go for mySQL.
I need to query and do a comparison on a versioned data table with a separate table dedicated to the version specific information.
The versioned data is related to pricing and the comparison is based on the date of service for the product sold. I need to find the correct price based on the version "effective date". However, there may be multiple entries for the selected product that are within the Effective date range. In this case, I need to use the record with the highest (or most recent) Version Date.
My product billing information is housed in one table. My referential pricing data is housed in another table and the version ids and dates are located in another table.
Need Query for this requirement. The data in my table is as follows
Zip Blac White green
123 1.76 2.04 1.00
234 3.49 2.39 3.24
Need Columname as output if the column value is > 2 for given zipcode.
For example.if i give zip as "123" the query has to find out the value among the columns which is > 2. i.e White
If i give zip as "234" then the query has to find out the value among the columns which is > 2.
But here all values are > 2. So you have to pick the max value. i.e 3.49 = Blac
Hi,I hope i understood you good enought. Here is TSQL solution which I hope will works for you.
select * from mytable<br />
declare @zip as int<br />
set @zip = 123<br />
declare @white as varchar(100)<br />
set @white =(select white from mytable where zip=@zip)<br />
declare @black as varchar(100)<br />
set @black =(select black from mytable where zip=@zip)<br />
declare @green as varchar(100)<br />
set @green =(select green from mytable where zip=@zip)<br />
create table #temptable (valstr varchar(100),colname varchar(100))<br />
insert into #temptable values (@white,'white')<br />
insert into #temptable values (@black,'black')<br />
insert into #temptable values (@green,'green')<br />
declare @colname as varchar(100) <br />
set @colname=( select top 1 colname from #temptable order by valstr desc )<br />
drop table #temptable<br />
declare @selectquery as nvarchar(max)<br />
set @selectquery ='select '+@colname+' from mytable where zip='+cast(@zip as varchar(10))<br />
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.
I have a table with 2,000,000 records and there is no way for me to filter it by range.
I was able to get the first 1,000,000, my problem is to get the next 1,000,000 without any duplicates from the first 1M records. Any Idea.
Select top 1m records order by ID
Get the last ID in the first set (you do have an ID field)
Select the top 1m records where ID > last ID
You can also look into Row_Number in SQL Server, this will eliminate the detect requirement but is more complex in the select. I would assume you are processing the 1m records and therefore detecting the last ID would be trivial.
Never underestimate the power of human stupidity
Please elaborate a bit more on what exactly are you looking for inside your C# code.
Are you trying to transfer data between different databases having identical schema from inside your C# code or you want to query different database at the same time???
Last Visit: 31-Dec-99 18:00 Last Update: 11-May-21 8:04