This is out of my league. I’m hoping to get some good advice from someone experienced in the area. My inquiry is how to best handle large amounts of records, say 500,000 records or so. I am web programming and can’t send all this info from server to client.
Part of the problem is the manner in which the data gets stored. I cannot calculate what records I need to get for a distant page (i.e. if 10 items per page then where is my data getting page #512).
These are the very first five (5) records. First row is the primary key.
14
451
0
V5
2 vials
1
V5
8/10/2007
3
20
451
0
V10
2 vials
2
V5
8/10/2007
3
25
451
0
V5
2 vials
1
V5
8/15/2007
3
26
451
0
V10
2 vials
2
V5
8/15/2007
3
27
451
0
V40
2 vials
8
V5
8/15/2007
3
Because records 1 through thirteen had been deleted, the primary keys for the first (5) are no longer 1, 2, 3, 4, 5. Had this been the case, a person could easily retrieve page 512, by mathematical calculation.
Page 1 would have been Records 1..10
# 2 = > 11..20
# 512=> 5111..5120.
I already have a program that loads the entirety into an arraylist; then picks out the page of data from the arraylist location. I could rewrite things so that a temporary SQL Table is created – but I don’t know is that a good idea?
I'm having problems constructing a query. I need to get a count of emails in my database, but only the emails that appear 2 or more times. Can anyone help?
Scenerio: Its 3pm and a user comes to me and says, she's deleted an invoice with many associated items. I know the affected tables (foreign keys) and I have last nights backup of the db. However I don't want restore the entire db back from last night just the deleted invoice record/s. What is the best practice procedure for accomplishing this?
I am not sure where i should post this question since it falls both in Report Server and T-Sql but here goes...
I currently need to run a Report that has only specified records that the client/user wants by clicking the check in the check box next to the record they want. They can pick as many or a few of the records that want then run a report only with the records they indicated they wanted... i am thinking they will need some kind of t-sql statement either a function or temp table but i am not sure if even that...
if anyone has any ideas please reply...
Thanks, WoFe
EXAMPLE: Instead of running a report on records 1, 2, 3, 4, 5, 6, 7, 8, 9 they would run the report on records: 2, 5, 6, 9
Hi, How to display specific number of records? That means I want to display records starting from 3th row to 5th row. Please send your suggestions or links. Table ----- Name Age ----------------- Raja 23 Kumar 26 Suresh 30 Rani 22 Subha 32 Ganesh 25
The result will be Name Age ----------------- Suresh 30 Rani 22 Subha 32
Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
I am fairly new to transact SQL and I am having difficulty retrieving the set of records I require given the data shown below. I want to be able to filter the records just to return the records that have the minimum securityorder for each unique secsyscode. I suspect I need to use min or group by to achieve the desired affect but cannot seem to get it right
any help would be appreciated
eg in the following secsyscode, securitytypecode and securityorder are integers and securityCode is a char(16).
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.
Hi,I have an Access application with linked tables via ODBC to MSSQLserver 2000.Having a weird problem, probably something i've done while not beingaware of (kinda newbie).the last 20 records (and growing)of a specific table are locked - cantchange them - ("another user is editing these records ... ").I know for a fact that no one is editing records and yet no user canedit these last records in the MDB - including the administrator -while able to add new records.Administrator able to edit records in the ADP (mssql server) where thetables are stored.Please help, the application is renedred inert .Thanks for reading,Oren.
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE TABLE [dbo].[User] ( [UserId] [bigint] NOT NULL ,[country] [int] NULL -- User’s home country ,[name] [nvarchar](15) NULL -- User’s displayable name (‘John’, ‘Bill’) ,[subscriptionTier] [int] NULL ) -- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute) John 2010-10-16 00:21:56 1500 Dave 2010-10-16 00:30:50 3200 Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report. However, I only want to select specific data from the dataset to fill textboxes and others. I cannot change the stored procedure, but the sample of the data is shown below:-
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories. In my first table, I tried putting the cells as follows:- (expressions are highlighted in italics and bold)
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value. Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
and it give me an error The value expression for the textbox textbox5 contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
We have database thats transaction intensive, so we are trying to sepetrare ldf file from mdf file to a different disk array. what raid should I use for the Transactional log file(.ldf).
I would like to know where I can find a senior database architect. Someone who can develop and implement the database and its stored procedures. I am looking for an experienced person.
It is a contract position, in San Franicisco. The pay is good. Could anyone help me? I tried Dice, Monster and it seems all of you are working...
I am replacing the corporate SQL Server at work. The new server will have 6 striped disks of 160G with about 4G of RAM. The current SQL Server currently has two instances which run web applications and a small database warehouse about 6G. Analysis Services is also installed.
Due to a couple of new apps being added to the server and the SQL Server 2000 enterprise license we acquired, i was thinking of adding 2 more instances so that the applications can be independently managed in terms of restarting the SQL Server. I also would like to permanently fix the memory settings on each instance to give more resources to more important applications. The log and data files would also be spilt onto 2 separate hard disks. i understand there are implications on performance such as CPU etc. Is it normally advisable to have more than 1 or 2 instances ? Most of the applications are not very CPU intensive. What other implications or performance issues would l have ?
We will be creating a moderately high-volume OLTP database application that needs 24/7 availability. We are planning to offload OLAP processing to a second copy of the system. We will be using SQL Server 2005.
I originally planned to set the second server up with SQL Server 2005 mirroring to cover the 24/7 availability requirement, with the idea that we could also do OLAP reporting off of the mirrored copy of the database. But I've gotten some indications that a mirror database is offline and not available for querying. So I figured I would use transactional replication to keep the OLAP database current. Now I am wondering if I need to use mirroring at all, or if I should just use transactional replication on the entire database and swap to the replicated database if the production server crashes.
What is everyone's opinion?
Replication only, for both OLAP reporting and failover? Mirroring to one database for failover, with replication to a another database for OLAP reporting?
