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
2answers
159 views

SQL Server Change Tracking

I work for a company of about 80 employees and they all seem to need access to our database on a regular basis. I don't have time to write applications to meet all of their needs so I'm just going to ...
1
vote
1answer
3k views

How to use array variable in query in PostgreSQL

Create table t1 ( xcheck varchar[], name text ); CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[]) RETURNS record AS DECLARE xrc as record; execute 'select name from t1 where xcheck @> ...
3
votes
1answer
882 views

Automatic database file creation based on database file size in SQL Server 2008 R2

Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. ...
0
votes
1answer
266 views

Extracting Multiple Values from Single Field

I'm an SQL beginner and I'm having trouble searching correctly for the answer to this question, although I'm sure that it's been asked before. I have an column in an SQL table that looks like this: ...
0
votes
2answers
469 views

Missing data after attaching .mdf file

Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that). ...
0
votes
1answer
134 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
61 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
448 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
508 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
1k 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
73 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
161 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
323 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 ...
1
vote
2answers
875 views

SQL Server 2012, restore database

I am using MS SQL Management Studio 2012 to restore the database from a .bak file. (That file is from the backup of the database on server) In my new database, the triggers are missing. Why did I ...
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
180 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
416 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
255 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
266 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." ...
1
vote
0answers
2k views

Achieve PostgreSQL hstore functionality in MySQL

Please suggest any MySQL 5.5+ Engine that can support collection of key-value pairs be stored in any column of a table. I don't know if this feature is available out of the box in MySQL like ...
-2
votes
1answer
375 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
183 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
219 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
162 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
605 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
237 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
884 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
551 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
766 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
129 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
109 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
300 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
155 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
550 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 ...