DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

3
votes
3answers
1k views

Convert Oracle query to MySQL

I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error: You can't specify target table 'animal_table' for update in FROM ...
2
votes
2answers
140 views

How do I configure MySQL '5.1.49-1ubuntu8' to show multibyte characters?

I am using MySQL version 5.1.49 and I have now enabled UTF8 character encoding. The default character-set for MySQL is latin1. How can I change it show UTF8 characters? Even when I query a table ...
1
vote
0answers
157 views

Pulling an Image from a Database vs pulling from a link to a directory [duplicate]

Possible Duplicate: Is it better to store images in a BLOB or just the URL? Files - in the database or not? I was wondering what the benefit is of pulling a varchar out of a database that ...
0
votes
1answer
128 views

SQL Server Data types and operators clarification needed

I would like to confirm the following with respect to the basic SQL Queries: The float and real data types cannot be used with the equals and not equals operator in sql. If so then what else ...
2
votes
1answer
678 views

Estimating query time

Is it possible in MySQL to know beforehand query execution time? I mean how long it will take to execute the query. Generally its worthwhile to know how long any query can take to execute. At the ...
9
votes
6answers
440 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 ...
2
votes
1answer
85 views

Bad Windows Authentication sign on

I do not have install privileges on my machine. Another user who has admin privileges installed it, but told the system to use Windows Authentication for the sign on. Is there a way they can change ...
3
votes
1answer
797 views

Is it possible to compare timestamps between rows?

I have a MySQL table reqs with (this is simplified from actual): | Field | Type | Null | Key | Default | Extra | ...
8
votes
3answers
3k views

How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
2
votes
1answer
2k 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." ...
3
votes
3answers
9k views

Return int value from boolean field in SQL select

Is there a way to return an int value for a boolean field in a sql select statement?
2
votes
1answer
1k 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 ...
16
votes
4answers
1k views

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 ...
58
votes
8answers
5k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
4
votes
2answers
308 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 ...
8
votes
3answers
2k 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. ...
0
votes
3answers
790 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
248 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 ...
4
votes
4answers
7k views

How can I insert if key not exist with PostgreSQL?

I have a table: CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY) Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the ...
4
votes
4answers
2k views

How can I generate an Entity-Relationship diagram and SQL DDL from natural language text?

Good morning, Using LaTeX or another text-macro tool/library, how can I use natural language text to describe relationships, then generate an Entity-Relationship Diagram and generate SQL DDL to ...
1
vote
1answer
5k 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 ...
2
votes
2answers
3k views

Understanding time format of the EXPLAIN command - Postgres

When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals ...
1
vote
3answers
457 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 ...
0
votes
1answer
166 views

sql 2000 trace : sp_releaseschemalock

For a SQL 2000 server, is it bad to see too many exec sp_releaseschemalock 1 events in a SQL trace file?
0
votes
1answer
671 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 ...
1
vote
1answer
294 views

DB Integrity Check Lock Database

Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of ...
4
votes
2answers
1k views

Creating a GroupBy query to include a result for when no results match

Let's say I need a query to see sales results by Month and I want the result-set to include results for each month whether there were any sales in that month or not. And let's say my table "sales" ...
2
votes
1answer
6k 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
9k 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
174 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 ...
5
votes
1answer
310 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 ...
5
votes
1answer
534 views

Mirroring partner timeout - Not working the way advertised?

I'm testing a 2 node windows failover cluster with mirroring in high safety mode and automatic failover. My mirror is a standalone server as is the witness. All instances are 2008 R2 RTM Enterprise ...
4
votes
4answers
350 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)?
14
votes
2answers
8k 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) ...
0
votes
1answer
158 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 ...
1
vote
1answer
543 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 ...
5
votes
3answers
20k 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 ...
4
votes
2answers
3k views

DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

I have data in a java object as data1, data2. data1 and data2 together forms a composite key in myTable where I want to insert the object. The writing is happening as a batch. Like 10 insert ...
19
votes
5answers
870 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
4k 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 ...
5
votes
3answers
1k 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 ...
9
votes
2answers
4k 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 ...
3
votes
2answers
3k 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 ...
5
votes
6answers
540 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 ...
10
votes
3answers
16k 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
507 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 ...
4
votes
1answer
639 views

T-sql: Select most specific match from table

Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem. Here's the scenario: Users can set up service instructions to be attached to a work order ...
2
votes
1answer
10k 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 ...
4
votes
1answer
9k views

oracle format specifiers: to_number vs to_char

SQL> select TO_NUMBER(123.56,'999.9') from dual; select TO_NUMBER(123.56,'999.9') from dual * ERROR at line 1: ORA-01722: invalid number SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
5answers
680 views

EXISTS clause in SQL

I have following requirement: main_set table has all the ids, user_input table has all user entered ids. I want result of ids that exist in user_input table. If nothing in user_input table then I ...