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)

8
votes
6answers
3k views

How do you submit a List of items to a Stored Procedure to limit results? [duplicate]

Possible Duplicate: Passing array parameters to a stored procedure Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read: WHERE ...
3
votes
1answer
365 views

How can I alter the output for the sql desc command?

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a ...
8
votes
2answers
2k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
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
134 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
126 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
629 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 ...
8
votes
6answers
421 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
84 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
702 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 | ...
6
votes
2answers
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." ...
2
votes
3answers
7k 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
969 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 ...
15
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 ...
56
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
295 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
725 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
237 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
6k 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
4k 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
2k 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
398 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
159 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
647 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
251 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
5k 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
8k 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
166 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
299 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
510 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
324 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)?
13
votes
2answers
7k 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) ...
3
votes
0answers
275 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 ...
0
votes
1answer
153 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
475 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 ...
4
votes
3answers
14k 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
2k 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
838 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 ...
0
votes
3answers
2k views

Figuring out database timezone

Does anybody know of a way to alter a time zone on a SQL database? Is there any tool that could find the objects using the date and time?
3
votes
2answers
2k 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
528 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
15k 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 ...