An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

0
votes
0answers
15 views

Any idea why this seemingly-simple query is really slow?

Here's the query SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL WHERE r.event_id = "102577"; It seems pretty ...
0
votes
1answer
20 views

How do I use a temporary table to generate a template of results and then fill it?

So let's say I have a table, call it 'orders' that looks like this: fields: customer|order_type|price data: "Bob" |"Delivery"|100 "Nancy" |"Delivery"|200 Where customer can be any name, ...
0
votes
1answer
14 views

How can I apply heuristics to optimise an expression tree?

I've converted the following SQL query: SELECT name FROM dog d, works_at w WHERE (d.owner_name = w.person_name) AND (w.kennal_name = "Breeders"); into the following expression tree: My main ...
0
votes
0answers
17 views

Optimistic Paging and separate query for COUNT?

I am using SQL SERVER 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a Total count. There are 2 ways to achieve this. 1) Paging ...
0
votes
4answers
25 views

SQL Server 2005 database log size increasing drastically?

My friend works in a company (pretty small in size) that is running SagePro ERP which has a SQL server 2005 database. Now the problem is, the database size (over the period of 7 years) has increased ...
1
vote
1answer
51 views

Can comments in stored procedures cause problems

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state ...
0
votes
0answers
15 views

Adding a new partition to the partitioned table is skipping the last filegroup

I have created one partitioned table and trying to add a new partition to that. I ran the following scripts to create a new filegroup and add a new partition to the table. But when I run the alter ...
1
vote
1answer
32 views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
0
votes
0answers
17 views

how to use joins in hierarchical queries

how to use joins in hierarchical queries my try SELECT cust_id AS child_id FROM customer c,cust_assoc_dnorm d on d.dsend_id=c.cust_id WHERE connect_by_isleaf=1 and ...
1
vote
2answers
39 views

Loop through all databases and remove all users

We are doing a conversion from one system to another which is requiring backup/restores. I am trying to create a process that will remove all users after the restore on the new servers as no user ...
1
vote
1answer
26 views

Translating a query from MS Access to SQL Server

I have a legacy query from years ago that was written for an MS Access backend: DELETE category.* FROM category LEFT JOIN product_category ON category.id = product_category.categoryid ...
0
votes
0answers
22 views

Multiple login mappings for linked server

Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB. ServerB has two databases on it and both using ...
0
votes
0answers
22 views

MySQL Query for combobox

I have two tables in my database.namely books and bookidofid. where bookidofid from books table is referenced to bookidofid from bookidofid table Table books ...
0
votes
1answer
22 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
0
votes
0answers
23 views

How to use Select query just right after Insert on master in a query.?

I have a question on mysql replication. As usual, I deployed my db structure like the Update on master db, the select on slave. My questions. If I use SELECT query just right after INSERT in a query, ...
0
votes
1answer
29 views

Postgresql condition on subquery results

This is a new question based on the solution to my previous question I have the following tables: users int (pk) user_id date last_visit int (fk) local_site sites int (pk) site_id site_stats ...
1
vote
1answer
32 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
0
votes
1answer
50 views

Select one single row from table b for each row from table a

I have two tables: A(name VARCHAR2(20), type VARCHAR2(20)) and B (type VARCHAR2(20), ... (20 fields more)) I need to select a single row from B for each distinct name,type from A taking into ...
2
votes
1answer
28 views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
-3
votes
1answer
28 views

How to avoid repeating values in a column in this case [on hold]

Dear Friends help to solve this problem The query is given bellow SELECT inv_val,ind_invno , ind_invdte, ind_csm_cd, csm_name, ind_fgm_cd FROM soh_invoice, sod_invoice, som_party WHERE inh_orm_cd = ...
-3
votes
0answers
36 views

SQL Server 2014 v/s NoSQL(MongoDb, RavenDb, CouchDb) [on hold]

Now SQL Server 2014 is out and there is lot of excitement regarding SQL Server 2014. How can we compare SQL Server 2014 v/s NoSQL(MongoDb, RavenDb, CouchDb)?
2
votes
2answers
87 views

How slow is slow in postgres?

I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now ...
1
vote
1answer
74 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
0
votes
0answers
22 views

Can't connect SQL Server through firewall [duplicate]

I know this has been asked before however I've tried all the solutions presented and it still does not work. I am attempting to connect to a SQL Server 2008 running on a Windows8.1 machine through ...
1
vote
2answers
28 views

Can I get OSQL if I install SQL Server Client Tools?

We have some legacy .bat scripts full of osql commands to existing SQL Server instances / dbs on our network. We've tried running the .bat scripts from our app server (Win Server 2008) but get the ...
2
votes
1answer
45 views

Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...
0
votes
1answer
27 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
1
vote
1answer
35 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
-3
votes
0answers
53 views

Fastest way to Restore 50TB DB [closed]

I am trying to find out if there other ideas as what the fastest way is to restore a 50TB DB.
2
votes
2answers
575 views

“Cannot insert the value NULL”, but there is no NULL values!

This question follows the answer of my other question there: Insert with OUTPUT correlated to sub query table The Person.LastName column has a NOT NULL constraint. When I execute this code: CREATE ...
0
votes
0answers
31 views

Retrieve result set with proportion of some column value

I have a table called PORTFOLIOS in which I have PORTFOLIO_ID, PORTFOLIO_NAME and SCORING_RULE columns. The SCORING_RULE column can have two different values (like double_scoring or single_scoring). ...
7
votes
1answer
149 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
0
votes
1answer
66 views

How can I force a bushy tree join in Oracle SQL?

As far as I know the default join is the deep left join. How can I force a bushy tree join on 4 tables?
0
votes
1answer
33 views

How to optimize query

using SQLite3 and the following query, it seems like there should be a better performing way to write this query? Is there? Select Name, Sum(Case When Used Is ...
0
votes
0answers
30 views

SQL query to return a column per date in date range with sum of contract utilisations (value/duration)

My question is similar but more elaborate than another already in stackexchange.com "SQL query to return a column per date in date range?" I have the following data: Customer Start_date ...
-1
votes
2answers
42 views

Select records for a day and a day - 1 day

From one table, I want to get the records from one column position when the created_at = Today and when the created_at = created_at - 1.day COALESCE(array_agg(DISTINCT pr.position)) AS position, ...
2
votes
1answer
26 views

How to search double quotes in fulltext search in sql

I have double quotes in my Keyword. How can I search this in my fulltext search query. I have this query SELECT top 10 K.[KEY], 10, K.[RANK] FROM CONTAINSTABLE(ProductKeywords, Keywords, '("19*") ...
1
vote
1answer
49 views

SQL Server 2012 Compatibility

I'm converting some stuff over to be compatible with SQL Server 2012 and had a couple of questions. I'm taking out all of the sp_dboption commands and making them ALTER commands instead, but there ...
1
vote
1answer
54 views

Insert with OUTPUT correlated to sub query table

I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with ...
0
votes
0answers
13 views

Access 2013 compare changes made to table

I am currently working on an Access 2013 database and trying to find the best solution to a problem I am having creating it (I am fairly new to databases so bear with me). Situation: Data is imported ...
-3
votes
1answer
23 views

Why DBMS_METADATA.GET_DLL not work with EXEC command ?

Using Oracle 11g I just trying to understand the concept here.... Why the command bellow not work? (executed at SQL Developer) execute dbms_metadata.get_ddl('VIEW', 'VW_CARGA','INT') Error ...
0
votes
0answers
38 views

explain plan ,joins and optimization

I have to join 6 tables in sql query and each of these tables have 1 join to each of there master tables . Hence i have defined 5 subqueries within a sql query but i am seeing a lot of performance ...
-3
votes
0answers
41 views

Querying for commentator repo and poster repo for different values of the comment ID (stored in a table) in SEDE [migrated]

How can I modify the following query SELECT CASE WHEN U.Id = P.OwnerUserId THEN 'Poster' ELSE 'Commenter' END AS [User], U.Id AS [User Link], U.Reputation AS [User Rep] FROM Comments AS C INNER ...
0
votes
1answer
32 views

Optimize this query: SQL

I have this query in MySQL: SELECT p.idSentence, p.idPath, p.token, p.posTag, p.ner, p.isTV, r.relationType, t.tag FROM path p LEFT OUTER JOIN relation ...
-1
votes
2answers
54 views

When do I need transactions /locks?

I know that transactions are meant to group several operations as one. But if for example in the same table one transaction does DELETE FROM table where state = 'done' and another does either SELECT ...
1
vote
2answers
58 views

Implementing voting system like reddit/StackExchange?

In creating an application that would require a voting system similar to that of StackExchange or reddit, I want to know how the database tables should optimally be structured. So far, I have ...
1
vote
1answer
31 views

SQL Server 2008 R2 - Limit viewing on database list

In SQL Server 2008 R2 I have user1, he owns 3 db (db1 , db2, db3) and I want he can see only these 3 db when he login by using SSMS. I did REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see ...
2
votes
0answers
62 views

Querying for reputation of the commenter and its addressee using SEDE [closed]

How can I modify this Data Explorer query for the case when user of the comment is same as the poster, then the query should look for the tagged user in the comment and print the tagged user ...
0
votes
0answers
29 views

Why does the delete/update block each other in this case?

The following drives me crazy. I have: session1>begin; session2>begin; session2>update dummy set salary=1000 where id=11; session1>delete from dummy where id=1; id is the PK ...
0
votes
3answers
70 views

SQL Server: CONVERT From ISO8601 To DateTime Within Query

I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a ...