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

learn more… | top users | synonyms (2)

5
votes
2answers
8k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
5
votes
2answers
6k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and ...
32
votes
3answers
3k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
33
votes
9answers
14k views

Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?

While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For ...
23
votes
4answers
9k views

SQL Server “empty table” is slow after deleting all (12 million) records?

I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
15
votes
5answers
1k views

Why do primary keys have names of their own?

From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table ...
16
votes
3answers
4k views

Traversing tree-like data in a relational database using SQL

Is there a way to traverse tree data in SQL? I know about 'connect by' in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using 'connect by' is easier ...
19
votes
1answer
14k views

How do I read Query Cost, and is it always a percentage?

I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric. According to any documentation I could find via ...
14
votes
3answers
12k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
9
votes
4answers
782 views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 ...
3
votes
4answers
4k views

Can I combine the results from multiple columns into a single column without UNION?

I have a table with several columns which I want to SELECT: SELECT his_name , her_name, other_name FROM foo; Bu, I instead I want to combine the results all into a single column. As an example, I ...
12
votes
5answers
4k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
5
votes
3answers
2k views

MySQL Query Optimization : Indexing and Pagination

I'm adding pagination to my 'latest news' php script and running in to an issue. SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit I ...
4
votes
2answers
2k views

Concurrently update with limit mysql

I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db. After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned. I ...
13
votes
5answers
19k views

SELECT DISTINCT on multiple columns

Supposing we have a table with four columns (a,b,c,d) of the same data type. Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
6
votes
2answers
3k views

'Conversion failed' error with ORDER BY CASE expression

I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'. I get the following error: Conversion failed when converting the nvarchar value 'SK11270' to ...
3
votes
1answer
11k views

Joining 3 tables using SQL Query?

I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three ...
3
votes
2answers
360 views

SQL Data aggregation

I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database. select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct me.id) ...
2
votes
6answers
2k views

Need separate columns that calculate percentage

I have the following table: NAME Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage Alex 2 33% Bob 2 33% Roger 1 16.6% Tim 1 ...
1
vote
2answers
1k views

MySQL query log

I wonder if there is an alternative for MySQL general query log. I would like to log (mostly) DDL statements, so I could trace what user DROP database/table etc. I know that enabling general query log ...
0
votes
1answer
388 views

How to get dates between two dates from MySQL table?

I want to get dates between two dates from MySQL table EXAMPLE fromdate(2015-01-01 and so on....), todate(2015-01-10...and so on) are the columns i have to get dates between these two columns. ...
4
votes
1answer
81 views

Order result by count of common array elements

Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection. Something like: ...
4
votes
3answers
4k views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
4
votes
1answer
5k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
4
votes
1answer
304 views

What is the name of this type of query, and what is an efficient example?

The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data: create table ...
4
votes
3answers
4k views

NULLs in a composite primary key - SQL Server

I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
4
votes
1answer
829 views

How to get text for default language

I have so tables: and so data at Language table: and so data at Text table: I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
4
votes
3answers
807 views

MySQL very slow query when changing one WHERE field despite no index/key

This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query: SELECT * FROM hits JOIN stadiums ON stadiums.gameName = hits.gameName JOIN players ON ...
3
votes
2answers
669 views

Optimize a query on two big tables

I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
3
votes
3answers
4k views

How can I select rows from a hierarchical query with the lowest level?

I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent (Level 1) Child (Level 2) Grandchild (Level 3) Child (Level 2) Grandchild (Level 3) Grandchild (Level 3) ...
3
votes
1answer
4k views

sql error 1241 operand should contain 1 column

This is my query select * from players where sport='football' and position='DEF' and pname!='Binoy Dalal' and pname not in (select ...
3
votes
2answers
1k views

Restore a Database into a Current Database and merge

I want to know how, or if it is possible, to backup a database then should the user wish, restore that database at a later date and merge it with the one they are currently using. Both Databases will ...
2
votes
1answer
88 views

identify multiple gaps and islands for n columns

In this previous question, I asked for a way of identifying some gaps and islands. with a table t below; +---------------------+------------+---------------+ | ts | EngOilP_sd | ...
2
votes
4answers
139 views

Make IN clause behave like AND clause

I've asked the same question on stackoverlow, but got no answer, so that's why I'm asking it here (I've read that simple sql questions should be asked at SO, but I have no choice). Suppose I have 3 ...
2
votes
1answer
4k views

Get top most parent by nth child id?

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the ...
2
votes
1answer
219 views

Search within same ID across several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
1
vote
2answers
79 views

Finding the names and the year of the movies that have at least 5 actors

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds the names and the year of the movies that have at least 5 actors. My attempt: The ...
1
vote
3answers
138 views

Need to get identify all tables and columns in a SQL Query

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or ...
22
votes
3answers
10k views

Running total with count?

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count: sum(count (distinct (customers))) Say if I ran the count ...
20
votes
5answers
101k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
19
votes
2answers
930 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 ...
27
votes
8answers
31k views

What is the difference between an INNER JOIN and an OUTER JOIN ?

I am new to SQL and wanted to know what is the difference between those two JOIN types? SELECT * FROM user u INNER JOIN telephone t ON t.user_id = u.id SELECT * FROM user u LEFT OUTER JOIN ...
25
votes
1answer
7k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
24
votes
2answers
4k views

Why does query error with empty result set in SQL Server 2012?

When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail ...
9
votes
2answers
68k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
8
votes
4answers
70k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
6
votes
3answers
3k views

SQL Server Primary key / clustered index design decision

Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database. I've got a product history ...
2
votes
2answers
4k views

MySQL Replication without stopping master

I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then ...
13
votes
3answers
390 views

What is the correct result for this query?

I came across this puzzle in the comments here CREATE TABLE r (b INT); SELECT 1 FROM r HAVING 1=1; SQL Server and PostgreSQL return 1 row. MySQL and Oracle return zero rows. Which is correct? ...
13
votes
1answer
4k views

Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything. ...