I have an auditing trigger that automatically places the time something was updated and the user that updated in fields in all my tables. I have another set of triggers that write event information from updates to an events table. The issue is when someone updates something, the event information is fired twice because of the first trigg...
I have a database where one of the common queries is has a "where blobCol is null", I think that this is getting bad performance (as in a full table scan). I have no need to index the contents of the blobCol. What indexes would improve this? Can an index be built on an expression (blobCol is not null) rather than just a column? ...
Hi, I want to create a history table to track field changes across a number of tables in DB2. I know history is usually done with copying an entire table's structure and giving it a suffixed name (e.g. user --> user_history). Then you can use a pretty simple trigger to copy the old record into the history table on an UPDATE. However, ...
So I am wondering if there is a definitive answer to this question. Also, does it matter if the index is clustered vs. non-clustered. Is it the same in all RDBMS implementations or is the exact behavior going to be proprietary? ...
I have a group by clause in a sql statement and need to use an aggregate function to minus all the values in each group instead of adding like the Sum() function. i.e. SELECT Sum(A) FROM ( SELECT 2 AS A UNION SELECT 1) AS t1 ..so will evaluate 2+1 and return 3. I need some way of doing 2-1 to return 1. Hope this makes s...
Hi, I need to wite a query that will retrieve the records from Table A , provided that the key in Table A does not exist in Table B. Any help will be appreciated. Thanks ...
Hello, I would like to know if it is possible, to select certain columns from one table, and another column from a second table, which would relate to a non imported column in the first table. I have to obtain this data from access, and do not know if this is possible with Access, or SQL in general. ...
Hi all, I am not that hot at regular expressions and it has made my little mind melt so what. I am trying to find all the tables names in a query. So say I have the query: SELECT one, two, three FROM table1, table2 WHERE X=Y I would like to pull out "table1, table2" or "table1" and "table2" But what if there is no where statement....
Hi to all, I've got a particular SQL statement which takes about 30 seconds to perform, and I'm wondering if anyone can see a problem with it, or where I need additional indexing. The code is on a subform in Access, which shows results dependent on the content of five fields in the master form. There are nearly 5000 records in the table...
We have scalar functions in our database for returning things like "number of tasks for a customer" or "total invoice amount for a customer". We are experimenting and looking to try to do this w/o stored procedures ... normally we would just call this function in our stored procedure and return it as a single value. Is there a way t...
I am attempting to insert a copy of a row from one table into another table with the same schema, with the addition of one column (a timestamp) to provide a "history" of the first table in MS Sql Server 2005. So, my query, without the additional column would be: "SELECT INTO [WebsiteHistory] FROM [Website]" I want to populate the tim...
Often you need to show a list of database items and certain aggregate numbers about each item. For instance, when you type the title text on Stack Overflow, the Related Questions list appears. The list shows the titles of related entries and the single aggregated number of quantity of responses for each title. I have a similar problem b...
Suppose I have a SELECT statement that returns some set of results. Is there some way I can number my results in the following way: SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes would give me... Fido Rover Freddy Krueger ...but I want... 1, Fido 2, Rover 3, Freddy Krueger where of course ...
.NET 3.5, C# I have a web app with a "search" feature. Some of the fields that are searchable are first-class columns in the table, but some of them are in fact nested fields inside an XML data type. Previously, I built a system for dynamically constructing the SQL for my search. I had a nice class hierarchy that built SQL expressio...
I have a .net transaction with a SQL insert to a MS SQL 2005 DB. The table has an identity primary key. When an error occurs within the transaction, Rollback() is called. The row inserts are rolled back correctly, however the next time I insert data to the table, the identity is incremented as if the rollback never occurred. So essenti...
I'm learning traditional Relational Databases (with PostgreSQL) and doing some research I've come across some new types of databases. CouchDB, Drizzle, and Scalaris to name a few, what is going to be the next database technologies to deal with? ...
DDL for Database Tables: Users: id - int - identity name - varchar - unique PCs: id - int - idnetity name - varchar - unique userid - FK to Users Apps: id - int - identity name - varchar pcid - FK to PCs I created a DataContext using the Linq To SQL designer in Visual Studio 2008. I want ...
Does anyone know of a tool that can verify that a given SQL statement is valid across different databases (oracle, db2, mssql)? ...
I have used an update command to update the whole table in an Sql Server 2000 database by mistake. I was actually meaning to update just one row. Now all the 2000 rows contain the update. Is there a way to roll back this change? ...
I am struggling with a creating a query. It is related to a large and complicated database but for the sake of this post I have boiled the problem down to something simpler. I have three tables X, Y, Z defined as CREATE TABLE [dbo].[X]( [ID] [bigint] NOT NULL ) CREATE TABLE [dbo].[Y]( [ID] [nchar](10) NOT NULL ) CREATE TABLE [dbo...