An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

11
votes
3answers
2k views

Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?

SQL server 2008 R2 query optimizer puzzle We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same. This is fast, 13 seconds, select * from bigtable1 ...
10
votes
4answers
36k 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 ...
10
votes
3answers
435 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 ...
10
votes
5answers
73k 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. ...
10
votes
3answers
4k views

Should I use SQL JOIN or IN Clause?

I have a question about best approach. I am not sure which approach is best when data is considered variable in size. Consider the following 3 TABLES: EMPLOYEE EMPLOYEE_ID, EMP_NAME PROJECT ...
10
votes
3answers
14k views

Notification on long-running query or deadlock in SQL Server 2008 R2?

I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on ...
10
votes
1answer
780 views

How can I make a query take a specific 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 ...
10
votes
2answers
450 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 ...
10
votes
2answers
37k views

Oracle: Quick way to list all database links

The title says it all, is there a way to quickly list all the current database links in oracle? Something along the same lines as this, which lists the current user's tables: select * from ...
10
votes
2answers
845 views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity. One thing the DBA mentioned was I should ...
10
votes
2answers
416 views

Decision criteria on when to use a non-dbo schema vs a new Database

I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
10
votes
3answers
1k views

Updating a table with millions of records, its been 4 days

I am currently updating a table with millions of records, its been 4 days and query is still executing. I checked the activity monitor its shows that query is running. In event log there is no ...
10
votes
1answer
198 views

Rejoining range seek on nullable composite index?

For the following schema and example data CREATE TABLE T ( A INT NULL, B INT NOT NULL IDENTITY, C CHAR(8000) NULL, UNIQUE CLUSTERED (A, B) ) INSERT INTO T (A) ...
10
votes
1answer
6k 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 ...
10
votes
3answers
5k views

Why is an aggregate query significantly faster with a GROUP BY clause than without one?

I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one. For example, this query takes almost 10 seconds to run SELECT MIN(CreatedDate) FROM MyTable WHERE ...
10
votes
1answer
1k views

Generate an exception with a Context

When PostgreSQL throws an exception, there is a line "CONTEXT" like: ERROR: INSERT has more target COLUMNS than expressions LINE 3: ... ...
10
votes
3answers
2k views

Database starts always in recovery mode

Everytime I restart my server, the database is always in recovery mode, and it takes about 20 minutes for it to behave as normal. This always and only happens when I restart the server, so I have a ...
10
votes
4answers
3k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing ...
10
votes
3answers
4k views

Best way to design tournament database

I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase. I have created a mockup ...
10
votes
1answer
10k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
10
votes
1answer
350 views

Resolving deadlock from 2 tables only related through indexed view

I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it. This is on a production environment running SQL Server ...
9
votes
4answers
3k views

Benefits of using backtick (`) in MySQL queries?

In MySQL we can create queries with or without the backtick (`) symbol. Example: SELECT * FROM TEST; SELECT * FROM `TEST`; Both works fine in mysql-console. Is there any technical difference ...
9
votes
4answers
11k 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 ...
9
votes
3answers
438 views

Are these two queries logically equivalent?

Are these two queries logically equivalent? DECLARE @DateTime DATETIME = GETDATE() Query 1 SELECT * FROM MyTable WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7 Query 2 SELECT * FROM ...
9
votes
3answers
6k views

How do you clear out all old query plans from within Microsoft SQL Server?

We have an off the shelf application that uses a Microsoft SQL database. Within this application we pick and choose various selection criteria for each report. This application then runs these ...
9
votes
5answers
3k views

How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
9
votes
3answers
2k views

Used Memory not freed up after a SQL BULK Insert / BCP Export

I have created a very basic SQL table as following CREATE TABLE [dbo].[TickData]( [Date] [varchar](12) NULL, [Time] [varchar](12) NOT NULL, [Symbol] [varchar](12) NOT NULL, [Side] [varchar](2) ...
9
votes
3answers
6k 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 ...
9
votes
5answers
15k 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. ...
9
votes
2answers
2k views

Does MySQL cache queries?

I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice ...
9
votes
2answers
488 views

How many Sproc Parameters is too many?

I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
9
votes
3answers
309 views

Query Performance Tuning

When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters? Do you run the query and view the actual execution plan? If so, what ...
9
votes
2answers
4k 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 ...
9
votes
2answers
5k 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 ...
9
votes
1answer
2k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
9
votes
2answers
1k views

foreign key constraint violation issue

I have identified 3 situations. A student with no enrollments. A student with enrollments but no grades. A student with enrollments and grades. There is a trigger on the enrollments table to ...
9
votes
2answers
3k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
9
votes
2answers
8k views

Slow Performance Inserting Few Rows Into Huge Table

We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
9
votes
5answers
431 views

Efficiently Filter Large Set With Disjunctions

Let's say I have a single table CREATE TABLE Ticket ( TicketId int NOT NULL, InsertDateTime datetime NOT NULL, SiteId int NOT NULL, StatusId tinyint NOT NULL, AssignedId int NULL, ...
9
votes
2answers
1k views

Treating certain Arabic characters as identical

In Arabic we have characters like ا (alef) and أ (alef with hamza). Users write them interchangeably and we want to search them interchangeably. SQL Server treats them as separate characters. How ...
9
votes
5answers
4k views

Group By hour over large dataset

Using MS SQL 2008 I am selecting an averaged field from 2.5 million records. Each record represents one second. MyField is an hourly average of those 1 second records. Of course the server CPU hits ...
9
votes
1answer
189 views

Clustered Columnstore indexes in SQL Serer 2014 for data warehousing

I am performance tuning a DWH using indexes. I Am fairly new to SQL Server 2014. MS describes the following: "We view the clustered columnstore index as the standard for storing large data ...
9
votes
0answers
4k views

Fast SQL query with * but extremely slow with specific columns? [closed]

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER ...
8
votes
5answers
3k views

Why is the SELECT clause listed first? [closed]

I'm not sure if this question is on-topic - if not, feel free to close. Also, if it'd be a better question for another StackExchange site, I can ask it elsewhere. I was wondering why SQL places the ...
8
votes
3answers
865 views

SQLITE: A problem of tags and products

I'm searching for a way to create a query to do the following: Let's consider 3 tables: products: List of products tags: list of tags tag_ties: table used to associate a tag to a product Let's ...
8
votes
6answers
412 views

Backing up SQL DB more frequently

I currently have a scheduled task that fires off each night at 2 AM that calls SQLCMD.exe and passes it a .sql script to run for the backup (shown below). We're a pretty small company with growing ...
8
votes
5answers
3k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
8
votes
6answers
3k 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 ...
8
votes
5answers
7k 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 ...
8
votes
5answers
3k views

Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...