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)

2
votes
1answer
1k views

Error restoring SQL differential database files

Occasionally I'm having problems restoring DB differentials. I'm getting the error "the differential backup cannot be restored because the database has not been restored to correct earlier state." ...
2
votes
1answer
797 views

Table Variables - Scope Error - Must declare the variable 'X'

This is my first post and not sure how to fix this auto format draft? Using SQL Server 2000. The table variable that is giving me this one error is '@SBLH' The following is the multi-select function ...
7
votes
3answers
1k views

Deleting all duplicates

I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. ...
4
votes
2answers
255 views

What is the consensus on using NOT NULL constraints on all text-based fields in a table?

Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying. I'm curious what the feelings are with using something like NOT NULL ...
0
votes
3answers
522 views

SQL Query Declaration problem

I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem? Incorrect syntax near the keyword 'Declare' SQL Query: create ...
0
votes
1answer
205 views

How do I correctly design this database?

I am not a database designer by any means. I am a desktop programmer, but I am currently looking at implementing a website idea which will need a (possibly very large) back-end database. In my site ...
1
vote
3answers
323 views

Select only neighbour rows who pass a where-test

I want to apply a selection process to my data, that will not include rows which should be accepted by my WHEREclause but who follow after a row which fails the WHERE. Example. I have a list of gps ...
1
vote
1answer
3k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
0
votes
1answer
165 views

Simply SQL Query problem

I have been pulling to the total stock within the first 6 months.My question is:How can I pull to total stock within the last 6 months in the same query? Also,I want to be to the result of the query ...
4
votes
4answers
305 views

How to measure the number of database writes on SQL Server 2008 R2

How would I measure the number of database writes on SQL Server 2008 R2 For example, how many updated rows and how many new rows in a database over a period of time (1 day, 1 week or 1 month)?
2
votes
1answer
4k views

SQL query to convert columns into rows

I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005. my input Data ONAY_ID sip_RECno KULLANICI ONAY 1 1 ...
5
votes
1answer
6k views

How to get the foreign and primary key details for a table in SQL Server 2008

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys. Is ...
0
votes
1answer
582 views

How to create a backup schema script including System Tables in Sql Server 2008 R2?

I need to back up my entire DB including one table that's allocated in "Tables -> System Tables". When I try to generate the sql scripts to back up my DB this table isn't mapped in this scripts. Do I ...
13
votes
2answers
5k 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) ...
5
votes
1answer
282 views

DBA's Scope in datawarehousing Projects

As a DBA trainee, what needs to be known in order to be part of datawarehousing projects? I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used ...
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
378 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 ...
18
votes
5answers
791 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
421 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
903 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
506 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
13k 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
421 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
2k 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
172 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
159 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
3k 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
3k 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
164 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
274 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
306 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
282 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
800 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
354 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
819 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
304 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
101 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
417 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
122 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
330 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 ...