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)

17
votes
2answers
25k views

How to optimize a query that's running slow on Nested Loops (Inner Join)

TL;DR Since this question keeps getting views, I'll summarize it here so newcomers don't have to suffer the history: JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell ...
16
votes
5answers
80k 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 ...
16
votes
4answers
1k views

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 ...
16
votes
7answers
9k 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 ...
16
votes
1answer
7k 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 ...
15
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 ...
15
votes
2answers
1k views

Why did creating this new index improve performance so much when existing index included all columns in new index?

I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems The query in question is complicated so I'm ...
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 ...
15
votes
1answer
524 views

Is using DISTINCT as a hint in subqueries useful?

Does adding DISTINCT in the following example have any impact on the query running time? Is it wise to use it as a hint sometimes? SELECT * FROM A WHERE A.SomeColumn IN (SELECT DISTINCT ...
15
votes
3answers
903 views

Alternatives to concatenating strings or going procedural to prevent SQL query code repetition?

Disclaimer: Please bear with me as someone who only uses databases a tiny fraction of his work time. (Most of the time I do C++ programming in my job, but every odd month I need to search/fix/add ...
15
votes
1answer
21k views

Varchar(Max) field cutting off data after 8000 characters SQL Server 2008

I have a field to store some data, the field is declared as varchar(Max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
15
votes
1answer
10k 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 ...
15
votes
1answer
4k views

How to get the MAX row

In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability. ...
15
votes
1answer
16k 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?
15
votes
2answers
2k views

T-SQL query using completely different plan depending on number of rows I'm updating

I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
14
votes
4answers
56k views

Can I see Historical Queries run on a SQL Server database?

Someone was running a query on our SQL Server database remotely and their system crashed. They have no backup of that query and want to see what was run on the server. Is it possible to find this ...
14
votes
5answers
2k views

How to recursively find gaps where 90 days passed, between rows

This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this. SELECT ...
14
votes
4answers
40k views

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights). There are no other ...
14
votes
7answers
3k views

Is polling the only way for updating app's data from a database?

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...
14
votes
5answers
27k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...
14
votes
3answers
10k 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 ...
14
votes
5answers
2k views

Needing A Database Design Book

I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
14
votes
2answers
3k views

PostgreSQL procedural languages - differences between PL/pgSQL and SQL

Can anybody please summarize the differences between: http://www.postgresql.org/docs/9.1/static/xfunc-sql.html and http://www.postgresql.org/docs/9.1/static/plpgsql.html ? Main points: ...
14
votes
1answer
523 views

Poor SQL Server performance when management studio closed

I've noticed a strange behaviour of SQL Server 2008 r2 express: usually my query execution time is ~650ms, but if I open Management Studio and run any simple query (for example SELECT * FROM ...
14
votes
3answers
15k views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
14
votes
2answers
8k views

How do I map an IS-A relationship into a database?

Consider the following: entity User { autoincrement uid; string(20) name; int privilegeLevel; } entity DirectLoginUser { inherits User; string(20) username; string(16) ...
14
votes
5answers
3k views

Sequential GUID or bigint for 'huge' database table PK

I know this type of question comes up a lot, but I've yet to read any compelling arguments to help me make this decision. Please bear with me! I have a huge database - it grows by about 10,000,000 ...
13
votes
7answers
118k views

How do I Create Tablespace in Oracle 11g?

I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used: SQL> create ...
13
votes
7answers
25k 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 ...
13
votes
3answers
6k views

Does “WHERE 1=1” usually have an impact on query performance?

I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language). ...
13
votes
5answers
123k views

How to update a table from a another table

I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2) Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. ...
13
votes
4answers
21k views

Why use WHERE 1 or WHERE 1=1?

Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I see a WHERE 1 clause being used in many places, even where other conditions are not present. Why ...
13
votes
5answers
13k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
13
votes
4answers
3k views

What is the significance of a semicolon at the end of SQL*Plus commands?

Some statements like create table, insert into etc take a semicolon at the end: CREATE TABLE employees_demo ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name ...
13
votes
1answer
1k views

“Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060” while altering the table

I am trying to alter a column in a table. The existing table is like this: CREATE TABLE [dbo].[table]( [id1] [int] NOT NULL, [id2] [int] NOT NULL, [id3] [int] NOT NULL, [name] ...
13
votes
3answers
375 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
4answers
37k views

How to find the latest SQL statements within the database?

I like to get the latest executed statements within my database, along with performance indicators. As such, I like to know, which SQL statements were most CPU/DISK intensive.
13
votes
2answers
412 views

Does firing off an indefinite WAITFOR increase the log file size?

In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) The ...
13
votes
2answers
11k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
13
votes
2answers
8k views

How to keep history of SQL Server stored procedure revisions

Note: I am not asking about full version control. Is there any way automatically to keep a history of stored procedures on SQL Server. Similar to how Google Docs automatically keeps a history of ...
13
votes
1answer
1k views

What does collation mean in database parlance?

I am learning sqlite from a book which has mentioned collation and collating sequence multiple times. What does it mean exactly in the database world ?
13
votes
2answers
2k views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
13
votes
2answers
6k views

Performance Tuning for Huge Table (SQL Server 2008 R2)

Background: I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test. Table Features: No of ...
13
votes
2answers
19k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
13
votes
1answer
1k views

Sql Anywhere 11: Restoring incremental backup failure

We want to create remote incremental backups after a full backup. This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible ...
12
votes
4answers
20k views

Methods of speeding up a huge DELETE FROM <table> with no clauses

Using SQL Server 2005. I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
12
votes
4answers
4k views

Is it possible to increase query performance on a narrow table with millions of rows?

I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
12
votes
3answers
8k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
12
votes
1answer
885 views

Best way to write SQL Query that checks a column for non-NULL value or NULL

I have a SP with a parameter that has NULL as default value and then I want to do a query like this: SELECT ... FROM ... WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND ...
12
votes
3answers
6k views

System disk run out of space when running heavy SQL queries on SQL Server 2012

I'm quite new to SQL Server 2012, I would be grateful if someone can help. I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it. I'm trying ...