0
votes
1answer
6 views

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
0
votes
0answers
3 views

Are CTEs multithreaded?

Here and here seem to suggest that they are, but I've also been told (source unlocatable) that a multi-row single-statement insert will only use one thread for all writing, that to multithread it, the ...
1
vote
1answer
16 views

Performance of single vs multiple column unique constraint

I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique ...
1
vote
0answers
20 views

New Index on table cause table to be locked really often

Here are my tables : CREATE TABLE [dbo].[Trackers] ( [IdTracker] INT IDENTITY (1, 1) NOT NULL, [IMEI] NVARCHAR (16) NULL, [CreationDate] ...
0
votes
0answers
10 views

Optimal Table Index for hourly data

I have a table that contains ~40,000,000 records and consisting of the following fields: idName - foreign key relationship to another table Date Hour Type (two possible types) Value Currently I ...
0
votes
1answer
19 views

Need help in incrementing the date in oracle

Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this. select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' ...
1
vote
1answer
17 views

How to join fixed row table against mapping table and reveal unmapped records

I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem 1. The first table has a fixed set of values/rows; ...
2
votes
1answer
40 views

Why doesn't a delete increase unused space in a table?

If I populate the following table: IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL DROP TABLE dbo.CleanTableTest; GO CREATE TABLE dbo.CleanTableTest (FileName nvarchar(4000), ...
2
votes
1answer
104 views

How do I identify the program that called a stored procedure?

As part of an application database design spec, we've been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they ...
0
votes
1answer
20 views

MySQL dump - how to order tables?

It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running: show tables; I would like to reverse that order - is it possible to do without getting ...
0
votes
0answers
6 views

Insert data from a table and with parameters

I have 2 tables, one feeds data for another but I must add external value too Is this query does the job ? MySqlCommand cmd1 = new MySqlCommand("INSERT INTO tableA (dt1, dt2) SELECT (dt1, dt2) FROM ...
0
votes
1answer
11 views

using foreign key as primary key

MySQL: If I have a user table and multiple other tables like seller, buyer etc, is it okay to use user_id foreign key as primary key inside tables like seller, buyer? That'll leave gaps in primary ...
0
votes
0answers
7 views

SSIS Execute SQL Task fails to login

I want to execute a SQL query in SSIS but when I try to build the query it says "login failed for user user". I executed the query in SSMS and it works fine and it also parses successfully . the ...
1
vote
1answer
17 views

Table-based custom type does not verify check constraints

Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table? Assume the following table that serves as a source for the ...
0
votes
1answer
15 views

Why storing product fees in another table is preferred?

MySql: What is the benefit of storing several types of fees in another table like Product Table -> (productid...), ProductFee Table -> (productid, feetypeid, feeamount), ProductFeeType -> ...
0
votes
0answers
8 views

Tuning SQL Query which returns REF CURSOR

I am using reference cursor in function to return values. This function is getting called recursively for different input values. My package Definition is as follows. create or replace PACKAGE ...
0
votes
0answers
12 views

MySQL Cluster, memcached process at 100%

I am running 4 nodes. All separate servers. 2 mgmt nodes. 2 data/sql nodes. The problem I have is this. When all the nodes are running. I telnet into memcached and do the following: [root@db ~]# ...
0
votes
1answer
34 views

Turning multiple fields in column to row

I have an issue that has really stumped me in SQL Server. I have recently had the need to insert a row in to a table, made up from a number of columns. I want to make the one row up from something ...
-1
votes
0answers
35 views

Please help me in resolving the error: The definition of object 'trigger name' has changed since it was compiled

I'm trying to upgrade SQL Server 2000 to 2008 R2. After the upgrade I'm getting the below error when trying to execute the stored procedure.This is executing in SQL Server 2000 without any issues, but ...
-1
votes
0answers
16 views

Can I add dummy rows to a crosstab query in Access? [on hold]

Is it possible to add dummy row headings to a crosstab query in MS Access when the data doesn't exist in the source table/query? I'd like to indicate that I'm aware that a category exists, even though ...
1
vote
2answers
27 views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
1
vote
2answers
36 views

New to databases - does my ERD reflect good database design?

I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields: {filePath,artist,title,album,track_number,length,date,artworkUrl} ...
0
votes
0answers
11 views

What are the Change data capture solution [on hold]

What are the change data capture available solution that are free and or open source. I found databus and flexview, is there anything else out there that would be near free ? Many thanks, M
2
votes
0answers
32 views

Exclude a User From an Audit

I need to set up a SQL Server audit to capture all database activity for all logins except one - the login our application runs under. In this case the login I want to exclude is CPSDOM\cpointedb. In ...
0
votes
0answers
12 views

Efficient settings for linked server to SSAS database

We are using OPENQUERY scripts to run MDX from sql-server. We're experiencing efficiency issues when we first run these queries each morning. The linked server objects which link to our SSAS ...
0
votes
0answers
36 views

Oracle - Slow queries without prepending schema name or providing hint

I am logged in as the schema owner in Oracle 11gR2 on Linux. I have 55 million rows in table. select max(col) from table; - takes 300 seconds (index full scan) select max(col) from schema.table; - ...
0
votes
0answers
59 views

Can we migrate oracle database 10gR2 to cross platform

I want to know can we migrate Linux based oracle database 10gR2 (10.2.0.1.0) to windows server 2003 with oracle database 10gR2 (10.2.0.3.0). I have seen the documentation related to database migration ...
-3
votes
1answer
15 views

Query required to get data from same column [on hold]

I trying get data from table having customer colums and details column details columns having several values like, Pen,Pencil, notepad,Pins etc. I want to query customer having pen and pencil. Pls ...
0
votes
0answers
8 views

PostgreSQL query slow when using tsvector and ORDER BY despite having index

Running on a large database (around 1.5G) The following query takes about 70 seconds to run, SELECT x FROM DBText x WHERE x.status = 2 AND x.publishedTime < 4574590406397 AND x.service = ...
2
votes
1answer
41 views

Monitoring the Postgres query parser

As I understand it, when you send a query to Postgres like: SELECT id FROM table1 WHERE name = $1; Postgres will create a query plan. The plan will be cached for the same query in the same ...
0
votes
0answers
18 views

Oracle SQL Statement alter index and add partition

I have an index in which I have to remove one column and reindex back for rebuild: ALTER INDEX <index_name> REBUILD; Is it possible to add partition when I rebuild an index. Partition will ...
0
votes
0answers
18 views

Get columns from NEW when execute update on postgresql [on hold]

I need know if determide fields are into result of update execution, e.g. IF NEW.niveope is null THEN _new_nivope := 0; ELSE _new_nivope := NEW.nivope; END IF; When I execute: EXPLAIN ...
3
votes
1answer
64 views

How to identify unused databases on my sql server

I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server. I went through many articles, which say possibly to go for ...
6
votes
2answers
543 views

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
0
votes
1answer
13 views

Design for periodic data cuts

I've been building an application that has a 6-monthly update schedule, i.e. a cut of the data for H1 & H2 of each year. The previous version of this application implemented this by making a copy ...
0
votes
0answers
39 views

How to dynamically alias subquery column [on hold]

I found this How can I dynamically alias columns to be very useful however is there anyway around columns created from subquery Such as SELECT COL1 AS "Column1" (SELECT TOP 1 (COL2) FROM ...
0
votes
0answers
15 views

Advice on MYSQL DELETE on CASCADE, UPDATE on CASCADE features

I have 2 tables called copyrigihtinfo and sourceinfo and many to many to relationship table cp_src_junction with following data. copyrightinfo table cp_pk cp_info 1 copyright (c) 1991-2000, ...
0
votes
2answers
14 views

How can I make MySQL client read password from mylogin.cnf?

I'm trying to make the mysql client connect to a mysql server without requiring the password to be given interactively. Steps taken: 1) First create a mylogin.cnf file $ mysql_config_editor set ...
0
votes
1answer
16 views

Automating SQL Server Merge Replication Processes [on hold]

Hi all is there anyway I can automate the entire process of the Merge Replication?
0
votes
0answers
23 views

BLOB storage relocation

Hi all is there any considerations for relocating a BLOB storage to free up some space on SQL server 2012 R2? Also what are the processes that should be followed?
0
votes
0answers
14 views

What are the considerations for relocating BLOB storage [duplicate]

I'm just wondering if there are any considerations for relocating a BLOB storage to free up some space in SQL Server 2008 R2
0
votes
0answers
19 views

Stored Procedure Issue in Simple Select Statement

Here is my Fiddle Here is My Table : CREATE TABLE IF NOT EXISTS `vehicle` ( `AutoID` int(11) NOT NULL AUTO_INCREMENT, `VehicleCode` varchar(20) NOT NULL, `NumberSeats` int(3) NOT NULL, ...
1
vote
1answer
13 views

Shrinking old partitions

I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
0
votes
0answers
10 views

Does MariaDB Support Materialized views?

Before i even try to experiment with MariaDB, could anyone tell me if MariaDB support Materialized views? i can't seem to find it in their documentation. This is one of my major issues with Mysql ...
-3
votes
0answers
37 views

sql query self outer join [on hold]

I have one table name lease there is field lsid,month,amount the lsid is every month same suppose like this lsid lsmonth amount 1 30-nov-2014 50 1 31-oct-2014 100 2 ...
0
votes
1answer
28 views

How to delete row from table later than date?

I have a simple table: MyTable: -Id int primary key -Name varchar(MAX) -Deleted bit Now i want delete rows created later that 15.12.2014. But how can you see i don't have a any date columns in my ...
0
votes
1answer
14 views

Dynamic Condition Creation function

I have a function which needs to create a dynamic where clause by reading the columns from the temporary table . CREATE FUNCTION [dbo].[GetWhereClause] ( @TempTableName nvarchar(600) ) RETURNS ...
0
votes
1answer
21 views

Can we protect data form Database Administrator by encrypting the data?

How can we protect data on my database which is MySQL form database administrator stealing it? Will encrypting it prevent it from being stolen? If yes, how as he already has access to the database ...
2
votes
1answer
60 views

A table with 50 columns

I have a table with 50 column and one of my friend has told me to split the table in 4 different table to save 12 column in each table. He proposed a design to have Master table and refer the PK of ...
0
votes
1answer
23 views

What Is the Next Best Ingredient to Add?

I am building a program that allows users to optimize their grocery shopping so they can make the most recipes using the fewest ingredients. One of the features of this program is a function I’m ...

15 30 50 per page