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)

0
votes
1answer
135 views

Advise On SQL Server Data Replication

We have a huge enterprise system, with geographically seperated SQL servers. We have some requirements related with data replication between these servers. We have considered the Sync Framework ...
2
votes
2answers
2k views

How does ORDER BY really work? [duplicate]

I have one question that confusing me. By logical query processing, the SELECT clause comes before the ORDER BY clause, and in theory, every step in a query creates a virtual table. So, how is it ...
2
votes
1answer
62 views

What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?

Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this? SELECT id FROM table WHERE table.int_col > 1; I'm specifically interested in ...
3
votes
1answer
5k views

Oracle insert through dblink performance issue

I'm trying to do an insert as select to a table via dblink. The query looks like this: Insert into MARS_SUPERVISORS_PUB@EDHPUB select {...} from edhper a, edhper b where ...
0
votes
1answer
3k views

ORA-01435: user does not exist

I am trying to run the SQL queries coded on my friend's laptop onto mine. I have imported the .sql file into SQL developer and I'm trying to run it.However, I keep getting the error: 01435. 00000 - ...
0
votes
1answer
457 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
2
votes
1answer
1k views

How to make postgresql SUM to be more accurate on large amount of floating point data?

I'm trying to SUM 8 million floating point (REAL) values with simple query like this: SELECT SUM(metric) FROM metrics; However, it returns very inaccurate result. It should return 137,586.77, but ...
2
votes
3answers
541 views

How can I improve my table design for different types of an entity?

Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables ...
3
votes
2answers
4k views

Identifying which values do NOT match a table row

I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query. To better explain, here's what I would do now, to check which IDs of the list ...
2
votes
2answers
2k views

Postgres RIGHT JOIN with custom array

I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following: SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9) ...
1
vote
1answer
77 views

Merge multiple “is exists” queries into one statement

I'm trying to figure out how to check if multiple rows exist in multiple tables in one statement. Given the following tables: table table_one ======== id 'a' 'b' 'c' table table_two ======== ...
2
votes
1answer
175 views

Would I lose data if I change a column from varbinary(8000) to varbinary(max)?

Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?
7
votes
2answers
7k views

Oracle 11g: performance improvements of inserts

I have a table of 500 millions of rows (and growing) I did the following to improve performance of inserts: On database side: dropped all indexes and constraints disabled logging On application ...
6
votes
1answer
330 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
2
votes
1answer
5k views

Error on creating database in SSMS

I am using SQL Server Management Studio 2008. I am connect to localhost and I want to create a database. When I click on New Database, type the name and click ok, I get the message: Index was ...
1
vote
1answer
181 views

SQL Server 2008 search joining query

I have two table as one is the message table and another one is messageUser table. Now i need to check before insert a new row. for example, M_MessageMessageId========= ...
1
vote
1answer
434 views

Best way to query huge data

First let me describe my case: I need to store around 200extra detail of each user. I am using wordpress. Now there is a default function to save data associated with user. But i am not sure what ...
3
votes
3answers
266 views

why would command text not execute at all?

in my report i have:: <CommandText>SELECT column1, column2, 'poop' from mytable </CommandText> i am using sql server profiler to see exactly what statement is ...
1
vote
1answer
2k views

Cascading Inserts in MySql

I have a users table that has a one to one relationship to a user_preferences table (primary foreign key user_id). When a new row is added to the users table (new id added), is there a way to setup ...
1
vote
2answers
274 views

How to migrate data from SQL database to MySQL including flat files?

one of my friend has asked some asp developer to make online application where user can upload files also. jpg, doc, pdf and all these files are saved in SQL database as flat file. Now my friend asked ...
2
votes
1answer
1k views

Modeling a ticket system based on a set of disparate kinds of tickets?

I'm working on a project that allows for the creation of "support tickets." These are not confined to something that needs fixing, however, so in a way they could more accurately be called "jobs." ...
-2
votes
1answer
384 views

Second time query execution using different constants makes faster? [closed]

Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
0
votes
1answer
196 views

Database is read-only

I got this error. How can I resolve it? Failed to update database "msdb" because the database is read-only. Could not insert a backup or restore history/detail record in the msdb database. This ...
6
votes
3answers
222 views

Primary Key efficiency

If I need a table to hold Point of Sales transactions, and am told I need to store: Country ID Store Number POS Terminal Number Transaction Date Item Code Teller ID Another Field More Fields Now, ...
3
votes
2answers
1k views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
3
votes
2answers
2k views

SQLite writing a query where you select only rows nearest to the hour

I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the ...
0
votes
2answers
168 views

Find the average number of n:m connections of two tables?

I want to know to how many lists a user is assigned in average. User ==== id name Lists_Users =========== user_id list_id List ==== id title Is there a good way of receiving this number via ...
3
votes
2answers
632 views

pessimistic locking and client death

We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
1
vote
1answer
4k views

Backup stored procedure for full, differential and log backup

I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure: Alter PROCEDURE BackupDB ( @BaseLocation varchar(1024), @BackupType ...
1
vote
6answers
101 views

Performance about this simplified where-condition query

I have a query in SQL Server 2008 like this: SELECT a.type, a.name, a.startDate, a.endDate, b.key AS key, b.is_locked AS is_locked, ...
3
votes
1answer
247 views

Avoiding repetition without creating a view

Suppose that I have a query Q1 and I need to run a query like the following: Q1 union select * from (some query that uses Q1 outcome) I would like to do that: Without creating any view Without ...
2
votes
2answers
902 views

Mysql notifier Error

i'm getting error when opening Mysql notifier High Severity Error Element 'UpdateCheck' was not found. Line 3, position 2. My mysql server is running. why it's showing this error.
1
vote
3answers
582 views

help with simple sql queries [closed]

Can I use some help please with the following query Consider the following schema: Suppliers(sid,sname,saddress) Parts(pid,pname,color) Catalog(sid->Suppliers,pid->Parts,cost)sid and pid ...
0
votes
1answer
784 views

SQL Server 2008 SP2 randomly slow down query

I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query. It is taking 4 sec to 4 min in execution. Please suggest the possibilities. There is no scope of query optimization. ...
0
votes
1answer
131 views

Looking for performance improvements for this query

This query is taking a long time to execute. We are expecting results in 0 sec, if possible. Please help me to do any other following: Rewrite the query Suggest any indexes Any other optimization ...
2
votes
2answers
111 views

How do I write backup procedure

I've read that a decent backup strategy would be: 1) Full backup weekly 2) Differential backups daily 3) Log backups every 5 minutes. Now question is how do I write this stored procedure and create ...
2
votes
1answer
316 views

How to Model / Quickly test for set membership in SQL?

Each row in a table needs to be protected a list of application specific roles lets say something that looks like this example below Desired Table Structure pkey | data | roles ...
1
vote
1answer
25 views

Reserving dates with quantities, table schema wrong?

Okay I have a table like so. ID Productname Quantity Image QuantityOut LastDateReserved 1 productOne 5 'image.jpg' 3 2013-03-15 2 ...
3
votes
1answer
156 views

Custom sp_who/sp_whoUsers

I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential ...
8
votes
6answers
5k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
1
vote
3answers
1k views

How to generate TPC-DS query for sql-server from templates

I know that dsqgen is used to transform the query templates into executable SQL. But when I type in the command line: "dsqgen /directory query_templates /template query1.tpl /dialect sqlserver /scale ...
2
votes
3answers
1k views

Database Design Relationship for Patient Visit in MS Access

I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have. There are a total of 6 ...
5
votes
2answers
569 views

Defining constraints in `CREATE TABLE` statements

Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
2
votes
1answer
2k views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
2
votes
1answer
179 views

Search within same ID across several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
0
votes
2answers
155 views

Changing passphrase for EncryptByPassPhrase

I am using EncryptByPassPhrase to encrypting some columns. If I want to change the passphrase in the future, how can I change it?
0
votes
1answer
29 views

content types of insertion

I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?
1
vote
1answer
497 views

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server

I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is ...
2
votes
1answer
24k views

IF NOT NULL then UPDATE else keep the value of the field

I think I am somehow close to get it work, but for some reason I still get errors. I have the following UPDATE query: UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN ...
2
votes
4answers
5k views

How do I determine if a database is the principal in a mirroring setup?

I have two database servers Server1 and Server2, configured with mirroring. A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on ...