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
0answers
42 views

Resource to learn writing most efficient and highly optimized sql queries [duplicate]

Possible Duplicate: Learning to Optimize SQL Queries and Understand Execution Plans - Resources? Can some one point me to a great book/great books or some really solid reference/references ...
1
vote
2answers
682 views

LIMIT to one row per ID

This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid? 'select DISTINCT v.id, v.purchasedate, ...
3
votes
2answers
674 views

Optimizing real-time Database table in Microsoft SQL server 2008

We are using a web interface to monitor our jobs state and its started and completed timestamps. We have >200,000 job instances running on each day for different job run-users. We maintain the state ...
2
votes
2answers
2k views

difference between having many non clustered index with single columns and with combination of many columns

Folks I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed many non clustered index with single columns and non ...
3
votes
3answers
2k views

Cannot write to mysql database while select query in process

Running Mysql 5.1.57 on InnoDB, and have a database locking issue. I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
1
vote
1answer
457 views

Help with getters & setters in PL/SQL

I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question: I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
4
votes
2answers
139 views

why SELECT is still using CPU & DiskIO?

I have a large database, about 30GB. In order to delete some data from a table (most data is in this table, simply because the # of rows), I have to do delete in batch. However, if I delete in too ...
2
votes
1answer
838 views

MySQL: AVG of defined variable per month

I need help with the following code: SELECT DISTINCT userid , count( userid ) as login_count FROM ( SELECT DISTINCT userid , ...
4
votes
2answers
526 views

Does a cluster index provide more benefits than pre-sorting the load file and creating non-cluster index?

Informix 11.70.TC4DE: CREATE TABLE cluster_tbl ( fk_id INT, data CHAR(2048) ); LOAD FROM "presorted.ld" INSERT INTO cluster_tbl; CREATE UNIQUE CLUSTER INDEX cl_idx ON cluster_tbl(fk_id); ...
2
votes
1answer
259 views

Best approach to design a database model for this situation? Clients and records

We need to store the following information for each client: Three types of record per file, for example (obviously, with real data): HDR2007022399999900000000000Soft/XX 0.0 XXX ...
3
votes
1answer
225 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
1
vote
1answer
870 views

Oracle 10g: query takes long time and adding index does not help [closed]

I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process. What I do is: Get "explain plan" for the query I noticed a "table scan" in ...
3
votes
1answer
4k views

The server principal “<mydbuser>” is not able to access the database “<mydatabase>” under the current security context

When I try to install a new database, I'm getting this error from SQL Server 2008. The server principal "<mydbuser>" is not able to access the database "<mydatabase>" under the current ...
3
votes
1answer
535 views

Why am I getting Table scan on a partitioned table query?

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table. When I run the following: SELECT * FROM dbo.INVENTORY_BREAK WHERE IR_ID ...
5
votes
1answer
26k views

CASE statement with IS NULL and NOT NULL

Is there any better way to write the lines below in SQL Server 2005? CASE WHEN (ID IS NULL) THEN 'YES' WHEN (ID IS NOT NULL) THEN 'NO' END AS ID_Value,
2
votes
2answers
1k views

sql bulk delete is much slower?

I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete DELETE TOP (n) syntax, then ...
0
votes
1answer
4k views

How to give SQL Server Job Owner permission to Proxy a Credential

I've created a Credential Cred1 (DOMAIN\USER1 with the secret = domain password) I've created a SQL Agent job with owner to NT AUTHORITY\SYSTEM The job needs to proxy as credential Cred1 So when I ...
0
votes
3answers
5k views

Database is not accessible after 'taking it offline' process failed

I was trying to detach the DB when it gave me an error that it is currently in use. Hence, I tried to take my DB offline but it failed saying 'an exception occured while executing a transact SQL ...
6
votes
2answers
9k views

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They ...
2
votes
2answers
206 views

MySQL : UNION script help

I have two tables which have roughly the same data but we could not mix them together due to the nature of the data input and presentation and to keep the table size small. We run the followign ...
3
votes
2answers
1k views

SQL Server 2008 Query Saved in Logs?

Every time I create a query and run it to create a report I save the query in my Projects folder in case I have to rerun the query in the future or if I have to modify it for any reason but it seems ...
11
votes
3answers
2k views

Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?

SQL server 2008 R2 query optimizer puzzle We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same. This is fast, 13 seconds, select * from bigtable1 ...
4
votes
2answers
86 views

what replication option should I choose?

Just looking for some feedback here, there are lots of replication options out there, and I'd like to try something that those more expert than I have found to be successful. Here's my scenario: 1 ...
3
votes
1answer
3k views

How to check connection string in SSMS2012?

I'm connected to database. I use db by Management Studio 2012 Express. Can I check connection string by click something in Management Studio?
4
votes
3answers
966 views

Restore a SQL Server database to an offline copy

I have SQL Server Management Studio. I need to access a backup file (*.bak). How do I create a new database engine that is OFFLINE (running from my PC not the server) and then restore the database ...
5
votes
3answers
830 views

How to transfer files at remote location using sql server job?

I have a job configured in which it transfers the file at remote location. My command is running absolutely fine from the command prompt but when I configured it in sql server job it gives log on ...
4
votes
2answers
187 views

Checking a wide table for nulls [duplicate]

Possible Duplicate: Test if any fields are NULL I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
8
votes
3answers
733 views

SQL Server query slow when paginated

I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012: select Id FROM dbo.Person WHERE CONTAINS(Name, '"John" AND "Smith"') order by Name Executing this query alone ...
0
votes
6answers
10k views

How to recover deleted/updated record in sql server or mysql?

I want to know that is there any way that I can can recover the deleted or updated record in the sqlserver and mysql as well?
2
votes
1answer
668 views

Help with a complicated MySQL Query inserting data using select from 2 tables

I am stuck with a case and unable to get the desired result from my query.. I have to insert into a table some records based on multiple records from 2 different tables.. Tables: Target: ...
-1
votes
1answer
509 views

Very slow MySQL query. Index problem?

I have a query like this: SELECT BE.NAME as NAME, BE.PREVIEW_TEXT as PREVIEW_TEXT, BE.ID as ID, FPV1.VALUE as PROPERTY_COUNTRY_VALUE, FPV1.ID as PROPERTY_COUNTRY_VALUE_ID, ...
0
votes
1answer
152 views

Updating a table based on condition

I have a database having a staging_table. From this table, entries are added to other table named Profile. Whenever staging_table is updated, it checks whether all its entries are present in Profile, ...
1
vote
1answer
1k views

Unable to add records to query in Access 2007

I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it. There are three tables. Let's call them ...
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.* ...
4
votes
2answers
358 views

What is the primary key on a temporal table?

If I use the following table design to capture history...: CREATE TABLE MyTable ( insertion_timestamp TIMESTAMP, deleted_flag BOOLEAN, natural_key INT, attribute VARCHAR ); ... then ...
186
votes
15answers
70k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
5
votes
2answers
347 views

Create function in central database or repeat in each database?

One of my developers has written a SQL function that works like the VB.Net function (LastIndexOf) and wants to publish it. My question is what would be the reason to put this in a central database ...
5
votes
2answers
446 views

Solutions for reporting off of an EAV structured database?

Existing setup SQL Server 2005 with standard EAV table that users want to slice and dice with a BI tool (Cognos). Is there any hope that we can transform this data into a format that we can report ...
10
votes
4answers
3k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing ...
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)?
1
vote
1answer
44 views

What is this type of query called, and how can I formulate it?

I have following tables: events(event_id, name, description) groups(group_id, event_id, title, description) event_group(event_id, group_id) users(user_id, name, email) ...
4
votes
1answer
1k views

Oracle subquery column aliasing syntax?

I have something like: SELECT a, b INTO v_a, v_b FROM ( SELECT x, y FROM whatever UNION SELECT z, w FROM something ) AS subquery (a, b) Which of course doesn't work in Oracle ...
9
votes
2answers
9k views

Slow Performance Inserting Few Rows Into Huge Table

We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
5
votes
1answer
2k views

Using FLOATs with RAISERROR

I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but ...
15
votes
2answers
1k views

T-SQL query using completely different plan depending on number of rows I'm updating

I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
3
votes
1answer
273 views

All-or-none exclusive lock on 2 SQL tables

My simplistic testing suggested that I can acquire an exclusive lock on 2 tables or postpone acquiring the lock on either until both can be acquired at once by using a query like this: SELECT ...
5
votes
4answers
2k views

Script to generate insert statements

I am trying to write an SQL script to create an insert statement for each row of table TEST which has field A (int) and field B varchar(30), in my MS SQL database. I would like to save those insert ...
-8
votes
1answer
288 views

What are the edges of typical (or specific) paid databases like oracle of mysql compared to the free oness? [closed]

Note: I am building a startup. I am currently using mysql. I am pretty much stuck. Others recommend other free databases like mongodb. I am seriously considering paid one IF and only IF it gets better ...
-1
votes
1answer
996 views

What exactly is SOLR? [closed]

It seems like a magical technology that can Do spatial search do full text search The problem is, It doesn't say where does it store all the data. Does it have it's own proprietary database If it ...
8
votes
3answers
3k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...