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
15k views

Using a Select in a Where clause for another Select

I have made a draft remote application on the top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functionning of the application. For each final business result that I ...
2
votes
1answer
59 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 ...
2
votes
1answer
891 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 ...
1
vote
1answer
172 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========= ...
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) ...
3
votes
2answers
3k 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
1answer
152 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)?
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 ...
1
vote
1answer
70 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 ======== ...
7
votes
2answers
6k 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 ...
2
votes
1answer
4k 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
399 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 ...
6
votes
2answers
3k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
4
votes
2answers
4k views

SELECT TOP in MySQL

How can I do this in MySQL? SELECT TOP 50 PERCENT * FROM table What's the simplest way to do it without running to separate queries (if possible)?
3
votes
3answers
694 views

SQL Server 2008 R2 Restore a back up to a new database

I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records. What is the best way to do this? ...
1
vote
1answer
24 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 ...
6
votes
3answers
211 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, ...
5
votes
3answers
124 views

Filtering data that could have more than one category

I have a table like the following: Annotation ( document, term, category ) Where document and term are some ID, while category is an integer. The couple document - term is not unique, ...
1
vote
1answer
1k views

Error creating scalar function “Invalid Object Name dbo.fnMyFunctionName'”

I am using SQL Server 2008 R2. I tried creating a new scalar function. The function gets created and I see it in the list of scalar functions. When I try to modify function, function name is ...
0
votes
1answer
166 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 ...
1
vote
6answers
99 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
2answers
548 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
406 views

Migrating data in SQL Server from source to target with schema change at target

We have a strange scenario. We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2. The source database table has 5 columns. The ...
0
votes
2answers
143 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 ...
1
vote
1answer
3k 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 ...
0
votes
1answer
724 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. ...
5
votes
2answers
718 views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
5
votes
2answers
713 views

T-SQL Purpose of MAX in this Group By Query

I have run into some pre-existing SQL that I'm having a hard time uderstanding. SELECT MAX(I.Symbol) Symbol , MAX(I.Ticker) CUSIP , MAX(I.Name) Name , SUM(H.Quantity) TotalQuantity , ...
3
votes
1answer
228 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 ...
1
vote
3answers
404 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 ...
2
votes
1answer
268 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 ...
2
votes
2answers
106 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 ...
5
votes
2answers
7k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
3
votes
3answers
2k views

Restore one table from SQL Server data file (.mdf)

I stupidly deleted most of the contents of a table in my database. I have a backup of the server which has the .mdf on it. I assume I can make a new db and attach the backup .mdf. Then I can select ...
2
votes
3answers
975 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 ...
8
votes
6answers
4k 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 ...
3
votes
1answer
278 views

How to recover data around corrupted data in table? [closed]

I have a SQL Server database with some corrupted data unrepairable via DBCC CHECKDB in a table. I can get many of the rows before the corruption out - but how do I get the rows after the corruption ...
6
votes
4answers
502 views

Find the first gap in an aggregation of integers in SQL Server

Let's say I have a table called dbo.GroupAssignment. GroupID | Rank ------------------ 1 1 1 2 1 3 2 1 2 3 2 4 3 2 3 4 3 5 The PK is GroupID, Rank. Normally, the ranks ...
2
votes
1answer
1k 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
2answers
2k views

Materialized View does not import properly when importing a db for a second time in another schema

When I import a database with materialized view mv_mt in just one schema I get no errors. create materialized view mv_mt refresh complete next trunc( sysdate ) + 1 as SELECT sysdate, media_type.* ...
0
votes
2answers
140 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?
5
votes
3answers
7k views

How to model inheritance of two tables mysql

I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal). ...
1
vote
0answers
511 views

Collect CPU, reads and writes when replaying SQL Profiler Trace

I'm trying to replay a trace on a test environment with SQL Profiler in order to benchmark a database prior to making some performance tweaks. I intend to replay the same trace at various stages to ...
2
votes
1answer
16k 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 ...
1
vote
1answer
537 views

Installing Full Text Search on SQL Server 2008 R2 Express

I can't for the life of me install full text search on my SQL Server 2008 R2 instance. Every installer I have tried shows me this: I've tried: SQLEXPRADV_x64_ENU.exe SQLEXPRWT_x64_ENU.exe ...
1
vote
3answers
97 views

Live monitoring SQL statements on SQL Server

How can i monitor running SQL Statements for my SQL-Server, is it possible using only SQL Server without 3rd part tools? As an alternative can anybody recommend any free tool which can monitor ...
3
votes
1answer
96 views

Adding column from SSMS wizard in SQL Server

Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process: Create a New ...
4
votes
2answers
144 views

SQL Server 2005 Database move and rationalisation - Recommended maintenance processes

We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct. I inherited this database and it came to ...
1
vote
1answer
281 views

Database Relational Design for Multiple Categories

I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants ...
-4
votes
0answers
59 views

Can I move up rows in the memory itself? [closed]

Lets say i have the following table : id col2 col3 1 1 1 2 2 2 3 3 3 4 4 4 And I'm trying to move rows to different ...