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

learn more… | top users | synonyms (2)

15
votes
3answers
14k 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 ...
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
1answer
2k 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 ...
15
votes
2answers
2k 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
3answers
11k 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 ...
15
votes
5answers
23k 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 ...
15
votes
3answers
445 views

Slow update on large table with subquery

With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4. UPDATE [SourceTable] SET Bad_Count= ...
15
votes
2answers
614 views

Does any DBMS have a collation that is both case-sensitive and accent-insensitive?

Note this question is vendor/version agnostic It seems to me, as a speaker (typist, writer) of English, reasonable to expect words to be properly cased but not necessarily have the correct accents ...
15
votes
1answer
615 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 B....
15
votes
2answers
9k views

Create View in specified database with dynamic sql?

I am writting a dynamic sql to drop and create view in different database. So I wrote: set @CreateViewStatement = ' USE ['+ @DB +']; CREATE VIEW [dbo]...
14
votes
9answers
1k views

Alternative to WHERE clause [closed]

Is there a way to only SELECT rows with certain data in a column, without using WHERE? e.g. If I had this: SELECT * FROM Users WHERE town = 'Townsville' is there a way to implement the WHERE ...
14
votes
4answers
2k views

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value ...
14
votes
5answers
3k 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
3answers
38k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
14
votes
4answers
12k views

Why are NULLs sorted first?

Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first? select 1 as test union all select 2 union all select NULL union all select 3 ...
14
votes
4answers
5k 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 ...
14
votes
1answer
775 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
399 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? ...
14
votes
1answer
6k 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. ...
14
votes
1answer
889 views

SQL Server UniqueIdentifier / GUID internal representation

A colleague of mine sent me an interesting question, that I can't entirely explain. He ran some code (included below) and got somewhat unexpected results from it. Essentially, when converting a ...
14
votes
2answers
6k views

Reasons for occasionally slow queries?

We are running MySQL 5.1 on Windows Server 2008 R2. We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
14
votes
2answers
15k views

Subqueries' aliases same as main queries' aliases

I have an SQL query whose aliases are the same as some of its subquery's aliases. For example: select * from ROOM r where ... ( select * from ROAD r ...
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 ...
14
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 ...
13
votes
7answers
2k views

Capitalize only the first letter of each word of each sentence in SQL Server

I want to capitalize only the first letter of each word of each sentence in a SQL column. For example, if the sentence is: 'I like movies' then I need the output: 'I Like Movies' Query: ...
13
votes
1answer
2k 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] [nvarchar](...
13
votes
3answers
272 views

Database stored procedure with a “preview mode”

A fairly common pattern in the database application I work with is a need to create a stored procedure for a report or utility that has a "preview mode". When such a procedure does updates, this ...
13
votes
2answers
522 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
5answers
5k 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 ...
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
14k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
13
votes
2answers
5k views

SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT

I was researching the differences between SQL Server's READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource: Choosing Row Versioning-based Isolation Levels ...
13
votes
2answers
7k 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
29k 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 (...
12
votes
6answers
9k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
12
votes
4answers
5k 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
2answers
16k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
12
votes
3answers
697 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
12
votes
3answers
12k 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 ...
12
votes
2answers
1k views

Postgres query plan of a UDF invocation written in pgpsql

It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
12
votes
1answer
3k views

Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?

I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks ...
12
votes
1answer
970 views

How can I design a query to pause / sleep / wait for a specified amount of time?

I am testing a monitoring application against a SQL Server 2000 instance. How can I write a T-SQL query that takes an amount of time that I specify? For example, most scripting languages have ...
12
votes
1answer
16k views

Performance implications of using OPENQUERY in a view

Please see this question on stackoverflow : I am using an EasySoft ODBC driver to link a SQL Server 2008 R2 Express instance to Interbase and I am having some difficulty with getting metadata ...
12
votes
2answers
352 views

How to write a query in SQL Server to find nearest values

Let's say that I have the following integer values in a table 32 11 15 123 55 54 23 43 44 44 56 23 OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a ...
12
votes
1answer
5k views

selecting where two columns are in a set

This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following: SELECT whatever WHERE col1,col2 IN ((...
12
votes
2answers
577 views

How can SQL Delete using a sub query

The following code was added by one of our developers to delete duplicate records from the table: DELETE SubQuery FROM ( SELECT ID ,FK1 ,FK2 ,...
12
votes
2answers
16k views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
12
votes
2answers
81k 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.