Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

1
vote
1answer
173 views

Merge left outer join column

I have a query that use left outer join. something like that : select issues.id, journal.notes from issues left outer join on (issues.id = journal.issue_id and journal.notes != '') So far it ...
0
votes
1answer
2k views

What data will be lost with DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)?

I have a corrupt database. The last known good backup was over 2 weeks ago. I've run a CHECKDB and have the page numbers where SQL Server located the corruption. I've looked at the data on the ...
6
votes
1answer
3k views

Avoiding entitized characters when using FOR XML PATH for string concatenation

I have this query: SELECT DISTINCT f1.CourseEventKey, STUFF ( ( SELECT '; ' + Title FROM ( SELECT DISTINCT ...
1
vote
1answer
298 views

Profiling parameters

I was trying to fine the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed. Is anyone able ...
1
vote
3answers
427 views

New SQL Server 2008 instance, load old instance DB's

BACK STORY: I'm a developer with a local instance of SQL Server 2008 on my workstation. I just recently upgraded my motherboard and found out (much to my dismay) the old motherboard's RAID drivers ...
4
votes
1answer
230 views

sql indexing and performance

I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities. There are 8 columns in this ...
1
vote
1answer
88 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
40 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
208 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 ...
1
vote
1answer
407 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
552 views

SQL Server 2005 database log size increasing drastically? [duplicate]

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 ...
2
votes
1answer
208 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 ...
1
vote
2answers
3k 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, ...
4
votes
2answers
3k 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
86 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 ...
1
vote
0answers
182 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
1answer
246 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
1answer
341 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 ...
3
votes
2answers
181 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 ...
1
vote
1answer
532 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 ...
4
votes
1answer
3k 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
2answers
498 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 ...
2
votes
1answer
330 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 ...
4
votes
1answer
2k 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
160 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 ...
2
votes
1answer
606 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, ...
6
votes
1answer
19k views

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

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 ...
1
vote
0answers
81 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). ...
10
votes
1answer
7k 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 ...
3
votes
2answers
248 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
2answers
180 views

How to optimize query

I'm 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 ...
0
votes
0answers
711 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 ...
-2
votes
2answers
402 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
1k 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*") ...
9
votes
1answer
7k 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 ...
-3
votes
1answer
303 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
89 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 ...
0
votes
1answer
60 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 ...
0
votes
2answers
229 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
1k 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
459 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
72 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
94 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 ...
1
vote
3answers
2k 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 ...
0
votes
1answer
191 views

Question on relation schema questions

Assume the following relation schema: S(A,B) T(B,C) Rewrite the following expressions without using join or outer join operators explicitly: S LEFT OUTER JOIN T S FULL OUTER JOIN T How do I do ...
12
votes
3answers
2k views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
0
votes
2answers
158 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
4
votes
2answers
668 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
0
votes
0answers
59 views

How control 27 locations and 27 databases? [closed]

We have 27 locations and 27 databases (all are SQL Server 2012 R2) All of them are the same (same tables,views,...), with different data for each location and separated from each other (in each town) ...
19
votes
2answers
1k views

SQL INDEX - how it works?

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases. I have read few books and I have ...