sql

How to update a column value incrementally

Hi, I'm experimenting with a personal finance application, and I'm thinking about what approach to take to update running balances when entering a transaction in an account. Currently the way I'm using involves retrieving all records more recent than the inserted/modified one, and go one by one incrementing their running balance. For ...

SQL: How do I SELECT only the rows with a unique value on certain column?

Thanks a million everyone for everyone's response. Unfortunately, none of the solutions appear to be working on my end, and my guess is that the example I've provided is messed up. So let me try again. My table looks like this: contract project activity row1 1000 8000 10 row2 1000 8000 20 row3 1000 8001 10 row4 2000 9000 49 row5 200...

Storing money in a decimal column - what precision and scale?

Hi, I'm using a decimal column to store money values on a database, and today I was wondering what precision and scale to use. Since supposedly char columns of a fixed width are more efficient, I was thinking the same could be true for decimal columns. Is it? And what precision and scale should I use? I was thinking precision 24/8. Is...

Why is my SQL Server cursor very slow?

I am using a Cursor in my stored procedure. It works on a database that has a huge number of data. for every item in the cursor i do a update operation. This is taking a huge amount of time to complete. Almost 25min. :( .. Is there anyway i can reduce the time consumed for this? ...

What is Full Text Search vs LIKE

I just read a post mentioning "full text search" in SQL. I was just wondering what the difference between FTS and LIKE are. I did read a couple of articles but couldn't find anything that explained it well. Thanks. ...

SQL update from one Table to another based on a ID match

Hi All I realy hope someone can help me with this. I have a databse with Account Numbers and card Numbers that I match to a file to update any card numbers to account number so I only work with account numbers. I created a view linking the Table to the Account/Card db to return the Table ID and the related Account number. Now I need to ...

What is the most optimized way to write a paged query in SQL Server 2005?

I know several ways of writing paged query in SQL Server 2005. But I have no idea about their performance. Which one is the best and most optimized way for writing paging SQL queries? Using Row_Number() Using Partition by Using Temporary tables Using Nest Top N with Order by Some other way? ...

Profiler for Sql CE

hi there, i wonder if there is something similar to Sql Profiler for Sql Server Compact Edition? i use SqlCE as backend for a desktop application and it would be really great to have something like sql profiler for this embedded database. or at least something simliar to the NHibernate show_sql feature... any ideas? thanks j. ...

Is it better to write a more targeted stored procedure with fewer parameters?

Say I have a stored procedure that returns data from a SELECT query. I would like to get a slightly different cut on those results depending on what parameters I pass through. I'm wondering whether it is better design to have multiple stored procedures that take one or no parameters to do this (for example, GetXByDate or GetXByUser), or ...

Building a view column out of separate columns

In our database, we have a system set up to keep track of applications. We have a bool column that indicates whether or not the application is approved. Then there's another column that indicates whether or not the application is denied. If neither column is true, then the application is considered to be pending. Is there any easy wa...

How do the Postgres foreign key 'on update' and 'on delete' options work?

Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them? ...

Semi-Tricky SQL Query

I am trying to write a query for SQL Server 2005 but I can't figure out how to do it. I have a table with the following fields: MessageID int CategoryID int Priority tinyint MessageText NVARCHAR(MAX) I need a query that will return * for each row that has the highest priority within a Category. For example, if I had the following dat...

cron script to act as a queue OR a queue for cron ?

I'm betting that someone has already solved this and maybe I'm using the wrong search terms for google to tell me the answer, but here is my situation. I have a script that I want to run, but I want it to run only when scheduled and only one at a time. (can't run the script simultaneously) Now the sticky part is that say I have a ta...

Resetting the time part of a timestamp in Java

In Java, given a timestamp, how to reset the time part alone to 00:00:00 so that the timestamp represents the midnight of that particular day ? In T-SQL, this query will do to achieve the same, but I don't know how to do this in Java. SELECT CAST( FLOOR( CAST(GETDATE() AS FLOAT ) ) AS DATETIME) AS 'DateTimeAtMidnight'; ...

Can I get better performance using a JOIN or using EXISTS?

I have two tables Institutions and Results and I want to see if there are any results for institutions that way I can exclude the ones that don't have results. Can I get better performance using a JOIN or using EXISTS? Thank you, -Nimesh ...

Group by when joining the same table twice

I'm writing a query to summarize some data. I have a flag in the table that is basically boolean, so I need some sums and counts based on one value of it, and then the same thing for the other value, like so: select location ,count(*) ,sum(duration) from my.table where type = 'X' and location = @location and date(some_tstamp...

Using SQL Server DTS Package to Conditionally Insert / Update Rows in Destination Table

I want to create a DTS Package to pull data from an Oracle table into a SQL2K table. How can I insert rows that are not already in the SQL2K table and update rows that already exist in the SQL2K table? I guess I could truncate and repopulate the entire table or create a temporary table and then do updates/inserts from the temp table...

SQL stored procedure temporary table memory problem

We have the following simple Stored Procedure that runs as an overnight SQL server agent job. Usually it runs in 20 minutes, but recently the MatchEvent and MatchResult tables have grown to over 9 million rows each. This has resulted in the store procedure taking over 2 hours to run, with all 8GB of memory on our SQL box being used up. T...

How can I insert multiple rows into oracle with a sequence value?

I know that I can insert multiple rows using a single statement, if I use the syntax in this answer. However, one of the values I am inserting is taken from a sequence, i.e. insert into TABLE_NAME (COL1,COL2) select MY_SEQ.nextval,'some value' from dual union all select MY_SEQ.nextval,'another value' from dual ; If I try to run it,...

Weird many to many and one to many relationship

I know I'm gonna get down votes, but I have to make sure if this is logical or not. I have three tables A, B, C. B is a table used to make a many-many relationship between A and C. But the thing is that A and C are also related directly in a 1-many relationship A customer added the following requirement: Obtain the information from th...