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)

5
votes
4answers
7k views

Building Dynamic Oracle Where Clause

I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
5
votes
1answer
620 views

Case Behavior Changed Between 11.2.0.1 and 11.2.0.2

I have a case open with Oracle and they say they can't reproduce this. Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query ...
3
votes
2answers
6k views

Emulate REGEXP like behaviour in SQL

I posted the same on stackoverflow (please, let me know if I have to delete one). I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries). So I ...
6
votes
1answer
664 views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) ...
4
votes
1answer
362 views

Execution plan issue requires reset on SQL Server 2005, how to determine cause?

We have a web application that delivers training to thousands of corporate students running on top of SQL Server 2005. Recently, we started seeing that a single specific query in the application went ...
4
votes
2answers
159 views

Randomizing table contents and storing them back in the table

I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read ...
0
votes
1answer
614 views

One record, multiple updates in one query (MySQL)

I run into a problem for which I didn't find an answer anywhere: If I have a table in which one specific value(here amount) from multiple records can add into a target record(from the same table), can ...
9
votes
3answers
5k 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 ...
13
votes
4answers
2k 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 ...
6
votes
3answers
411 views

Online resource for writing Access SQL statements

I am looking for an online resource for writing Access SQL statements. I write queries using SQL because the UI puts limitations on what can be queried. I have been using w3schools for reference, ...
7
votes
4answers
2k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
1
vote
1answer
451 views

What does HSJOIN mean in an Explain Plan?

I have the following EXPLAIN PLAN results from a query on my DB2 database. 0 SELECT STATEMENT ( Estimated Costs = 5.928E+02 [timerons] ) 1 RETURN 2 HSJOIN 3 [O] ...
26
votes
8answers
19k 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 ...
6
votes
1answer
3k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
13
votes
3answers
21k 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.
4
votes
1answer
4k views

How can I query names using regular expressions?

How could I search for a specific pattern in a column? For example, I like to get all user names that start with either letter A or B. (btw: this is tagged oracle, but it might be interesting in ...
15
votes
3answers
3k 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 ...
63
votes
7answers
9k 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 ...
13
votes
2answers
31k views

Sql Server database in recovery: How to stop, Take ofline or delete?

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 dont care one ...
4
votes
1answer
299 views

Query Execution Time

I am querying a SQL server 2008 database table having more than 500 million records. The query I fired to move around 10 million records to temp table took 9 hours. Similar query I fired which has ...
12
votes
2answers
135 views

What programming language is this?

:#PCA=Campaign//CampId//CampDesc//CampBusId = 2 :#PE=PubEdition//EdId//EdCode//EdCampId = :#PCA :#PCT=ContactType//ContTypeCd//ContTypeDesc//ContTypeUsrId in (21, 25) ...
0
votes
2answers
3k views

SQL Server 2008 DBCC Problems

We have a database Ms DBCC CHECKDB (MS) Or ALTER DATABASE MS SET SINGLE_USER DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE MS SET MULTI_USER Error message Msg 0, Level 11, State 0, ...
12
votes
2answers
21k views

How to pass in parameters to a SQL Server script called with sqlcmd? [closed]

Is it possible to pass parameters to a SQL Server script? I have a script that creates a database. It is called from a batch file using sqlcmd. Part of that SQL script is as follows: CREATE DATABASE ...
1
vote
1answer
392 views

Exclude .LOG file while restoring a database

I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but ...
5
votes
1answer
155 views

Table partitioning in SQL 2008 - Why?

I understand how it works, but the book I'm studying doesn't tell me why you'd want to partition. Could someone shed some light? I can't understand any concept unless I see how it's useful.
6
votes
1answer
74 views

Is there a performance hit by breaking out tables into different schemas in SQL Server?

Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server. For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...
1
vote
3answers
275 views

Is there an equivalent of “OPTION(RECOMPILE)” or “WITH RECOMPILE” for an entire connection?

I'm curious. Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration? Can I run a command that basically says, "use OPTION(RECOMPILE) for all ...
1
vote
4answers
438 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...
6
votes
6answers
2k views

attach/detach v.s. backup/restore

I am using SQL Server 2008 Enterprise. I need to transfer the database (as a whole) to another server (to make a duplicate database to setup another test environment). I have two choices, (1) making ...
8
votes
4answers
167 views

SQL Server 2005/8 Query Optimisation Hints

I'm looking at educating a team on writing better SQL Server queries and was wondering what people's best hints were for improving performance. For instance I once had a DBA who insisted that ...
18
votes
0answers
2k views

Guide to reading, comparing, and interpretting Execution Plans in SQL Server [closed]

I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others. If you want ...
25
votes
3answers
20k 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 ...
27
votes
9answers
8k 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 ...