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)

3
votes
0answers
268 views

To NULL or not to NULL? [duplicate]

Possible Duplicate: Why shouldn't we allow NULLs? I've heard that it's a bad idea to do database design such that nullable fields exist. However, eliminating nullables often cause much ...
1
vote
1answer
364 views

What is SQL/PSM and how does it differ from other versions of SQL?

I have searched SQL/PSM and I have learned that it stands for 'Structured Query Language/Persistent Stored Modules', but what I want to understand is what that really is and how it differs from T-SQL ...
0
votes
1answer
149 views

Add parent_id dynamically based on another field

I have a version table using a field to track order: id, owner_id, order, version 5, 1, 1, 1.1 6, 1, 4, 1.2 7, 1, 7, 1.2.1 8, 1, 10, 1.3 I want to ALTER TABLE version ADD COLUMN parent_id and have ...
2
votes
3answers
5k views

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
18
votes
5answers
775 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 ...
3
votes
3answers
3k views

How? SQL Cluster with multiple active nodes (not failover)

I'm building a new SQL 2008 R2 (Enterprise) Cluster that is backed by an EqualLogic iSCSI SAN. Instead of building a failover cluster (which I have done), I would instead like to setup several nodes ...
6
votes
3answers
404 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, ...
5
votes
3answers
816 views

Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my ...
5
votes
6answers
492 views

Check if DISTINCT is really necessary

I have a query in mysql as follows. SELECT DISTINCT `tableA`.`ColA`, `tableA`.`ColB`, `tableB`.`ColA`, ... FROM `tableA` LEFT JOIN `tableB` ON `tableA`.`colC` = `tableB`.`colA` WHERE ...
3
votes
2answers
1k views

Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement?

I have this table: SELECT * FROM items id | item | position ---|-----------|---------- 1 | USB cable | 0 2 | SD card | 4 3 | Mouse | 2 4 | Keyboard | 0 5 ...
9
votes
3answers
12k 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 ...
2
votes
2answers
408 views

Full Name search query

We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please. We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and ...
6
votes
2answers
1k views

'Conversion failed' error with ORDER BY CASE expression

I have the following Stored Procedure that works great EXCEPT when I set the 'OrderBy' to 'OrderNumber'. I get the following error: Conversion failed when converting the nvarchar value 'SK11270' to ...
2
votes
1answer
4k views

How to display a number with fixed number of digits before the decimal?

I am looking for result 0123.56 and the following query should have worked. SQL> select to_number(123.56,'0999.99') from dual; select to_number(123.56,'0999.99') from dual * ERROR ...
8
votes
4answers
1k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
2
votes
2answers
169 views

MySQL Query Optimization : Bets and Averages

I have the following selection string: SELECT * , AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr, MAX( q1 ) AS qmax, (MAX( q1 ) - AVG( `q1` )) AS diff FROM jos_bet_1x3_best GROUP BY `bet_id` HAVING ( ...
4
votes
3answers
1k views

MySQL Query Optimization : Indexing and Pagination

I'm adding pagination to my 'latest news' php script and running in to an issue. SELECT sid, title, time, bodytext, author, url FROM news WHERE approved=1 ORDER BY time desc LIMIT $start, $limit I ...
4
votes
0answers
156 views

Which SQL datatypes exist since when and in which RDBMS? [closed]

I found a general comparision of different SQL implementations that only covers some data types (BOOLEAN and TIMESTAMP) and a comparision of datat type support in different RDBMS from 2009 that does ...
4
votes
2answers
2k views

What are some common and useful fields for a users table in a database?

I want to begin implementing user functionality on my website, but I'm not sure exactly what kind of information I should be storing. Obviously the basic things like username, password, password hash, ...
4
votes
4answers
2k views

Storing arrays of integers in database (for efficient select)

I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of ...
1
vote
1answer
157 views

Multiple Distinct Columns

I have a table named 'DATAENTRY' with the following columns Title FName MName LName Add1 Add2 Add3 City Now I want to select records which have different (FName, MName ,LName) so I tried SELECT ...
1
vote
4answers
270 views

Is there a DBMS that allows the “= NULL” syntax?

Inspired by a StackOverflow question (Why doesn't SQL support "= null" instead of "is null"?). Is there a DBMS that actually supports the = NULL syntax?
9
votes
3answers
304 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 ...
2
votes
2answers
124 views

Database Agnostic Conditional

I'm writing a Drupal module and I want it to be agnostic between PostgreSQL and MySQL. I have a field that gets its value from a conditional, and I originally wrote it as an IF(). Then I found out ...
2
votes
3answers
271 views

Non-deterministic aggregated result

Why is my query non-deterministic? I have query that do the following: select sum(float1*float2*coalesce(float3,1)) from table When I run this query I get a deterministic result, but it seems not ...
5
votes
5answers
773 views

Advice with date column SQL Server 2005

I have two datetime columns in SQL Server 2005 that I need to query without the time portion of the datetime. Currently, my query looks something resembling this (just an example): WITH Dates AS ( ...
4
votes
3answers
352 views

Database Design Issues

My question consists of three parts: When can I be sure that my database design is perfect? Is returning to the database design to change some issues (i.e, adding new column, deleting a column, ...
0
votes
1answer
2k views

How to get query execution time and query result at the same time? [closed]

Is there any SQL query syntax that returns query execution time and query result at the same time?
4
votes
2answers
775 views

MySQL connection doesn't work anymore

I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can ...
3
votes
1answer
296 views

SQL Server CTE problem

This question is in reference to my last question: SQL Server 08: Union over while I'm writing a CTE to do what was described in that question (find available time slots over a given number of days. ...
3
votes
2answers
100 views

Rankings based on “versus” style matchups? [closed]

Let's say I have an application that, from a pool of items, presents a random two to the user. The user selects one as the "winner" and the result would be recorded like this: INSERT INTO results ...
4
votes
1answer
416 views

Help with Query Tuning

EDIT - drachenstern: see this question for more information on this problem and on the source of his query below: Group data by non-unique keys by distinct time range Can someone please advise ...
6
votes
1answer
3k views

SQL Server 2000 : List and Disable all triggers

I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the ...
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
3answers
119 views

Can I automate the “on” statement from MySQL queries?

Background In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL. I would like to know how (if) I can simplify join statements, e.g. by setting default fields ...
4
votes
4answers
320 views

Do cross-joins get optimised by the client library?

Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ. I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
4
votes
2answers
903 views

Best way to evenly distribute data over partitioned tables

I am working with a database with roughly around 500MM records. We currently use a static partitioning method that partitions the data by an int identifier such at < 150MM goes on partition 1, > ...
2
votes
2answers
1k views

How do I group results in batches of consecutive rows with the same 'module'

I'm looking for a seemingly very simple SELECT. I have a logging table LOGGING of which three columns are of interest to me: MODULE (text), PDATE (date), PTEXT (text). Actually, I care only about ...
-6
votes
1answer
1k views

How do I convert this Access query for SQL Server? [closed]

I have a Query used in Access which does not work in SQL Server as it stands: SELECT mstPatient.PatientID, mstPatient.PatientID AS PKNO, ...
4
votes
2answers
1k views

Estimate average and median efficiently in Postgres?

I have a Postgres database with tables in the billion scale. So any aggregate functions such as count() and avg(), as well as "order by random()" are very time consuming. Postgres has pg_catalog which ...
2
votes
3answers
1k views

when selecting from multiple tables I get a multiplication of the records

When executing this query below it takes too long. And the result is too much records, more than possibly in the database. The query should return around 117.000 records. That is the amount of ...
3
votes
3answers
640 views

Improving slow queries. Slow query log attached

Below is the Slow Query Log when my .NET application was running. Please let me know how to improve the performance of the queries: TCP Port: 3306, Named Pipe: (null) Time Id Command ...
0
votes
1answer
2k views

Minimum user rights for connecting MS Sql database remotedly

I have followed this tutorial for getting connected to a Microsoft SQL Server. I connect with ping functionality of Glassfish Server and thus I have to give some user rights to be able to even ping ...
1
vote
1answer
66 views

SQL query question

I want to find the records of August birthdays in own table.I'm using Datetime Field. Sample record: 01.08.2001 06.06.1985 26.08.1995 etc.. How should I do write a query? Regards,
12
votes
1answer
808 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 ?
2
votes
1answer
847 views

Can't retrieve data of countries and regions

Goal: Improve my sql ability by doing some coding and reviewing existing code. Problem: I have spent lots of hour to find a solution based of this question "3c. Some countries have populations more ...
1
vote
1answer
108 views

I need help migrating data through databases

i've a web page with some data about the users. Recently I made a complete change and start developing a new portal and of course a new DB structure. My question is what's the best way to migrate my ...
2
votes
2answers
554 views

Slow Query logging everything

I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example) Query_time: 0.000237 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: ...
2
votes
1answer
2k views

What is the corresponding MySQL TimeStamp in MS SQL Server?

I need a timestamp to be created automatically when I insert information in a table. mySQL has TimeStamp but MS SQL Server TimeStamp doesn’t correspond mySQL. Is there any other way around?
3
votes
1answer
462 views

A question on MySQL SELECT Algorithm/Function

Let's say I have 100,000 rows in a table, and each of the rows has a boolean value. Only ten of these rows have a value of 1, the rest are 0. How would MySQL perform a SELECT on only the rows with a ...