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)

1
vote
2answers
534 views

Exploring and optimizing SQL performance of your system (PostgreSQL and Hibernate)?

Imagine you have inherited some project with extensive usage of SQL queries. Let us assume that Hibernate is used as ORM and PostgreSQL as database, but thats not the point. A question is: could ...
4
votes
2answers
3k views

SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose ...
0
votes
1answer
125 views

Comparing two tables for a UUID change and fix it

I have two tables which I'm trying to reconcile the differences of in postgresql. Table A is old and needs updating. Table B is an updated, schema identical version of Table A which I have the data ...
1
vote
2answers
239 views

SQL Server database backup on Remote site

We have 4 different sites and each site has a SQL Server. our requirement is to have a SQL Server backup or database(Mdf files) of site 1,2,3 in site 4. In case of disaster on any of the site we ...
0
votes
1answer
891 views

Set field values to newly imported rows in PostgreSQL table with existing data

I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import. Question: For the newly imported ...
5
votes
1answer
456 views

Unexpected Table Scan with Parameterized LIKE

I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
0
votes
2answers
2k views

Estimated Execution Plan SQL Server Sort?

I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything. Anyone got ...
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
0answers
81 views

mysql: how many disk seeks were made for a select query

I will fire a select query. I want to know how many disk seeks does that query take. mysql documentation has given formula for full table scan. ...
3
votes
1answer
16k views

Find out number of active locks on a table

I was wondering if there is a way to find out the number of active locks held on a table in SQL Server?
3
votes
1answer
391 views

sql server partitioned view execution plan questions

I have created a partitioned view for 3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint (respectivly for each table) datepart(year,[Date])=(2010) datepart(year,[Date])=(2011) ...
5
votes
2answers
772 views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
2
votes
1answer
2k views

Limiting number of results in a Partition using OVER(PARTITION BY)

In the following query, why is it that we have to limit the results returned from each Partition by using the clause WHERE foo.row_num < 3 outside of the subquery foo but not from within the ...
4
votes
2answers
2k views

SQL Server 2012 database backup successfully report but no backup file

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must ...
1
vote
1answer
56 views

Retrieving Alternating Values

I have a column with two values in my database, I want to retrieve the two values alternately, for example I have: name itemcode ---------- A 1 B 2 C 2 D 2 E 1 F ...
0
votes
1answer
2k 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 - ...
4
votes
1answer
7k 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') ...
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
844 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
256 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: ...
5
votes
4answers
2k views

SQL Server DB restore fails

I am trying to do a SQL Server database restore using this T-SQL code: ----Make Database to single user Mode ALTER DATABASE xyz SET SINGLE_USER WITH ROLLBACK IMMEDIATE ----Restore Database RESTORE ...
0
votes
2answers
457 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). ...
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 ...
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 ...
0
votes
2answers
20k 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
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 ...
2
votes
1answer
992 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
175 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
159 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
72 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
406 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
760 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
213 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
126 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
177 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
582 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
423 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
156 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
753 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. ...