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

learn more… | top users | synonyms (2)

314
votes
3answers
148k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
227
votes
15answers
98k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
85
votes
8answers
22k views

Where are some useful SQL puzzles to teach SQL in a workplace?

I'm looking for beginner and intermediate level SQL puzzles, that I can point trainees at for practice. I'm aware of http://sqlzoo.net/ which is a great resource - is there anything else out there ...
80
votes
4answers
20k views

Why use both TRUNCATE and DROP?

In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them. Many of my colleagues (almost ...
66
votes
8answers
8k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
60
votes
1answer
153k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
51
votes
3answers
42k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
50
votes
4answers
25k views

What is the difference between select count(*) and select count(any_non_null_column)?

I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table. What are the differences between these ...
47
votes
3answers
30k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
47
votes
3answers
2k views

Huge slowdown to SQL Server query on adding wildcard (or top)

I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not ...
36
votes
3answers
41k views

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...
35
votes
3answers
2k views

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...
35
votes
9answers
16k 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 ...
34
votes
1answer
64k views

How to force drop database in SQL Server 2008

I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error cannot create file C:\Program Files.....[databasename].mdf because ...
34
votes
3answers
40k views

N prefix before string in Transact-SQL query

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this SELECT * FROM ...
34
votes
2answers
690 views

SQL Server unpredictable select results (dbms error?)

Below is simple example, which returns strange results, that are unpredictable and we cannot explain it in our team. Are we doing something wrong or is it SQL Server error? After some investigation ...
33
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 ) ...
32
votes
6answers
60k views

How do I efficiently get “the most recent corresponding row”?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
32
votes
7answers
20k views

Why does ORDER BY not belong in a View?

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with) I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
31
votes
4answers
12k views

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
30
votes
13answers
82k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
30
votes
5answers
67k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
28
votes
8answers
37k 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 ...
28
votes
1answer
12k views

Postgres UPDATE … LIMIT 1

I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...
27
votes
4answers
22k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
26
votes
7answers
15k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
26
votes
1answer
8k 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 ...
26
votes
2answers
17k views

Timeseries: SQL or NoSQL?

I don't care about the general differences between SQL and NoSQL (or their traditional differences). I am currently looking at altering the storage of our internal time series. They all contain ...
25
votes
5answers
121k 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 ...
25
votes
2answers
5k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
25
votes
2answers
7k views

Check existence with EXISTS outperform COUNT! … Not?

I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT. Yet in several recent scenarios I've measured a performance improvement when using ...
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 ...
24
votes
1answer
14k views

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
24
votes
1answer
24k views

Difference between Hash, Merge and Loop join?

In SQL Server you can specify the join hints: HASH JOIN MERGE JOIN LOOP JOIN What is the definition of those three join hints, and when should each be used?
23
votes
4answers
10k 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 ...
23
votes
3answers
113k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
23
votes
3answers
11k 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 ...
23
votes
5answers
20k views

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
23
votes
4answers
9k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
23
votes
2answers
6k views

Index not making execution faster, and in some cases is slowing down the query. Why is it so?

I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down. The query to create test ...
22
votes
2answers
67k views

How do you stop, take offline, and delete a SQL Server database in recovery?

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so. I need to either stop the recovery, or kill it in some way. I don't care ...
22
votes
3answers
2k views

Are RANK() and DENSE_RANK() deterministic or non-deterministic?

According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". ...
22
votes
3answers
5k views

Why does ANSI SQL define SUM(no rows) as NULL?

The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets: COUNT(...) = 0 AVG(...) = NULL MIN(...) = NULL MAX(...) = ...
21
votes
4answers
24k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? TL;...
20
votes
8answers
9k views

Why should an application not use the sa account

My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc. I have an absolute belief that ...
20
votes
8answers
16k views

How to query a database for empty tables

Due to some 'developers' we had working on our system we have had issues with empty tables. We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't....
20
votes
7answers
44k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...
20
votes
5answers
933 views

Should Query Tuning be Proactive or Reactive?

As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server). I make the best ...
20
votes
2answers
55k views

Connect to SQL Server with Windows Authentication in a different domain

I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed ...
20
votes
3answers
13k views

What is the best way to get a random ordering?

I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How ...