DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

1
vote
1answer
54 views

Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

I have a table market_trades with a timestamp column: api_production=# \d market_trades; Table "public.market_trades" Column | Type ...
1
vote
1answer
298 views

Beginner questions about SQL and relational algebra

I'm doing some exercices waiting for the exam. I have some doubt. RELATIONAL ALGEBRA These are the table: Newspaper (Cod_n, Name_n, Publisher) Article (Cod_a, Title, Topic, ...
1
vote
1answer
141 views

Reducing execution time for this gnarly SQL

I'm creating an order system of some kind. There is a function that I use to get orders (in PHP) and retrieve the required information about the order. However, it seems that I am pulling a lot of ...
1
vote
1answer
69 views

how to update the value of a period contained inside a bigger range

I have a tsrange like '[2010-01-01 14:00, 2010-01-01 15:00]',20 And want to update the 20 value to 15 in a range inside that, like '[2010-01-01 14:10, 2010-01-01 14:30]',15 My expected outcome ...
1
vote
1answer
54 views

Does PostgreSQL allow multiple grantees in GRANT commands?

Looking at the PostgreSQL docs it seems that the GRANT command only grants to a single user or role. In fact it doesn't seem to be PostgreSQL alone. Isn't possible to do something like GRANT SELECT ...
1
vote
1answer
39 views

Combine 2 tables

I am wanting to combine 2 tables. until recently the column names were different , and one had fewer columns than the other, however it was deemed unnecessary to have them as two tables. I have ...
1
vote
1answer
105 views

MYSQL Query - Unexpected result in text messaging database [closed]

On a text messaging database I have built, I am trying to return the total amount of messages a user has sent and the total number of messages a user has received. I have the following code: SELECT ...
1
vote
1answer
293 views

PostgreSQL 9.3 - Performance Issue: Counting table entries slower with jdbc postgres driver

I'm using PostgreSQL 9.3 and want to ask if it is possible to improve the performance of my queries based on the following database schema: CREATE TABLE "entities" ( "id" BIGSERIAL, "type" INT ...
1
vote
3answers
135 views

Text Storage and Database Design Optimization in a SQL Server Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
1
vote
3answers
187 views

How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in ...
1
vote
1answer
50 views

Restarting a Publisher involved in Merge replication

Our hosting provider would like restart the SQL Server that a publisher in a merge replication topology. I'm not able to clearly understand best practice for doing this from the Microsoft docs. Our ...
1
vote
1answer
71 views

Troubleshooting Resource Governor / Classification Function [closed]

I've been trying to configure / troubleshoot the resource governor on a sql server 2014 instance. I can't get any work into anything but the default resource pool. I've been following this ...
1
vote
2answers
97 views

Optimize rows to columns conversion

I have a big Oracle table (around 40 million rows) that looks like this: ID Name Question Answer Reason 3 Name1 1 Yes blah blah 3 Name1 2 No NA 3 Name1 3 ...
1
vote
1answer
65 views

SPARSE Column in SQL Server

I have just learned about SPARSE columns in SQL Server. I have never used them; I've just read about it on the Internet. Can I ALTER an existing Nullable column in large transactional tables to ...
1
vote
2answers
109 views

Inner Join or what else?

What I have Table A Table B ID ID_Fruit ,,, so on ID ID_Fruit ID_CRATE DateTime 1 1002 i_1 ...
1
vote
2answers
54 views

What subquery do I need to calculate this report?

I have two tables: tickets and comments. I need to run a report that shows me which ticket is "neglected" meaning that there has not been a comment in X amount of time. If I was to do this in PHP, I ...
1
vote
2answers
642 views

Optimizing large MySQL SELECT WHERE IN clauses

How can the following SELECT WHERE IN case be optimized? I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE ...
1
vote
1answer
85 views

Calculate value differences of rows before and after bounded by multiple referenced rows

Supposed I have a user travelling table like this: id | start_time | end_time | location | ----+--------------+------------+------------+ 1 | 1 | 2 | Loc_A | 1 | ...
1
vote
2answers
629 views

SQL - query multiplying records returned - Cartesian Product

I am using the following query which is returning a huge number of multiplied records - i.e. a cartesian product: SELECT * FROM specimen, topography_index, morphology, functions WHERE ...
1
vote
1answer
142 views

Print the execution time on messages Pane?

How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on.. Select * from tbl1 Go Select ...
1
vote
1answer
132 views

set date value based on other row values

Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling :) ... Here my problem: I've got a table with the following ...
1
vote
2answers
34 views

SQL view, how to append results from tableB with the results from tableA

I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations ...
1
vote
1answer
39 views

SQL Server: affect other transactions?

I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example: BEGIN TRY BEGIN TRAN ...
1
vote
1answer
117 views

Get data per Customer and per Date

How will I get the all the date even though there is no order between two dates? Sample Table OrderID | Date | CusID 33942 | 6-21-2014 | 6005 34059 | 6-20-2014 | 4003 53333 | ...
1
vote
1answer
33 views

What is the generally accepted method and tools for profiling MySQL queries?

Profiling database queries is difficult, and dependent on database technology. In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query? For example: Run the ...
1
vote
1answer
227 views

Running queries directly on DB2 databases

Just have a general query, if it is really fine or safe to run long select queries on RDBMS production Databases specifically DB2? If NO, what are the major risks and issues in executing select ...
1
vote
1answer
446 views

Insert with different key values to avoid contraint violation

I have a set of Inserts traced of a database and I want to issue them into another similar one. The problem is that I'm getting contraint violation errors due the similar database already has the keys ...
1
vote
1answer
407 views

SQL Server Cross Domain Transcational Replication

Has anyone got any documentation for doing/implementing Cross Domain SQL Server 2012/2008R2/2008 transactional replication document or steps (with screenshot or without screenshot) Thanks for your ...
1
vote
1answer
434 views

SQL Server VM in Azure with In Memory

I am running azure and considering deploying a SQL Server 2014 instance due to the in memory feature, however, I was looking through the documentation and couldn't seem to find how to configure ...
1
vote
1answer
68 views

MySQL from table to table data transfer

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create ...
1
vote
2answers
93 views

how to use an index on a mysql query with a where clause and a order by

I have a table CREATE TABLE activity ( id INT(10) UNSIGNED AUTO_INCREMENT, subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0', action VARCHAR(250) NOT NULL DEFAULT '', ip INT(10) UNSIGNED NOT ...
1
vote
1answer
119 views

Mysql 5.6.17 drop large table locks the server for minutes

We run Mysql 5.6.17. Dropping a large InnoDB table 10GB caused the whole server to lock for about 40 seconds. We have 55GB innodb buffer pool size. I read bunch of old bugs that could be the source ...
1
vote
2answers
2k views

join for between multiple tables without common fields

table E : id, title, datetime table L : id, place, xpos, ypos table M : id, title, datetime, contact, agenda table ER : parentid, childid, parenttype, child type data in ER : e.id, l.id, e, l -> E ...
1
vote
1answer
7k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
1
vote
1answer
956 views

update an id to max+1 in current table? (MySQL)

I've got a 'jobs' table where each row has a 'worker_id' that is either zero (job is not being worked on) or some unique ID that needs to be incremental. Basically, when assigning this worker_id, I ...
1
vote
1answer
65 views

Merging a Select query into a view

I am using DB2. So, I have a view which is working fine. It is : Select Atr.Ssn_Tx ,Names.First_Na ,Names.Middle_Na ,Names.Last_Na ,Reff.Ref_Cd_Desc_Tx Suffix_Na ,MBR.PRSN_MBRSH_ID From ...
1
vote
1answer
508 views

INSERT from a SELECT combined with a single value

I have this query that returns several rows of item_id: select item_id from properties where name like 'body'; And I have the second one that returns 1 row of tag_id: select id from tags ...
1
vote
1answer
3k views

DB2 SQL: How to select all days in a date range even if no data exists for some days

I am really really new to the whole programming world. I have found this previously. However I need rolling dates and into a SQL i am not aware what it is. Example of the SQL im using: Select ...
1
vote
1answer
2k views

SQL query to find all parent records where all child records have a given value (not just some)

An Event has many Participants. A Participant has a field of "status". I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'. I ...
1
vote
1answer
60 views

How can I make multiple joins on the same table?

I have a table (signal) which contains references to two other tables (ee and eeav). I want to perform an SQL Query which combines the data from ee and eeav tables. The situation is as follows: The ...
1
vote
1answer
175 views

PostgreSQL Function: How to refer to individual column in record return by function

I have function that returns setoff record type. CREATE TYPE CIR_TYPE AS ( ID integer, path text, cycle boolean ); CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS ...
1
vote
1answer
45 views

How to maintain fixed number of rows per foreign key?

I would like to keep a record of the last N (say, 10) objects a user has viewed. I'm thinking a basic way to store this information might be the following table: CREATE TABLE history ( id ...
1
vote
2answers
47 views

How can I set myself as owner of a database ? just using sql

As I want to view dependencies of just a couple of tables in my clients database I want to set myself as the owner of the database. If the database is called MyDatabase and my userName is ...
1
vote
1answer
135 views

Maintenance plan using external tool to execute query and stored procedures giving off no results

My situation is as follows: I am using a third party tool (VisualCron) to run stored procedures and sql queries on several database servers. The stored procedures are from http://ola.hallengren.com/ ...
1
vote
1answer
209 views

SQL Query Rewrite - Help Needed (Oracle)

Need help in re-writing the below SQL. The Query works fine for smaller database below 400 GB, but spends too much time for larger database above 600 GB. Oracle - 11.2.0.4 Database - Oracle ...
1
vote
1answer
7k views

MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

I have database session enable, when website got thousands of visitor. It gives me error [30-Apr-2014 04:27:12 America/Denver] PHP Fatal error: Uncaught exception 'PDOException' with message ...
1
vote
1answer
32 views

Locating a schema that owns a role

Let's say we've logged on under the A schema. I am able to execute the following statement grant select on wph_pden to LANDING_R; However, the LANDING_R object can not be located in the schema A. ...
1
vote
1answer
65 views

UK Addresses in 3NF

I am in the process of creating a UK only database which holds information for various sets of people i.e. Staff, Clients etc. The tables for each of these sets of people have an Address, City and ...
1
vote
1answer
117 views

Can I estimate the row count of a large mysql table using the disk space?

Sysadmin here (not a developer by trade), so go easy please ;) I have a mysql table, on a linux server that uses innodb_file_per_table. When I try to do select count(foo) from table; It doesn't ...
1
vote
2answers
770 views

Dynamically REVOKE CONNECT FROM guest in SQL Server 2005

I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts: DECLARE @database_id int, @database_name nvarchar(100); DECLARE ...