An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

7
votes
3answers
1k views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
1
vote
0answers
66 views

What is the best way to find object dependancies in SQL Server [duplicate]

Possible Duplicate: How can I track database dependencies? How can I know which store procedure or trigger is using a table on SQL Server 2008 R2? I have been using the following SQL on ...
1
vote
1answer
119 views

Can a Database request automatically remove itself from the queue after a delay?

I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made ...
1
vote
0answers
465 views

time interval period in sql [closed]

How to display the time period of the most widely in use in ms sql server I have purchase data, such as: hours the number of purchases 10:00 2 12:00 4 13:00 5 14:00 1 ...
0
votes
2answers
2k views

Access ODBC reserved error 7711

I have user who is trying to access some tables from our Microsoft SQL Server 2008 R2 system (which very recently got moved to a brand new hardware/software stack) using Access 2007 via an ODBC ...
0
votes
1answer
255 views

FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and ...
1
vote
3answers
401 views

Should I split a single table with an AUTO_INCREMENT PRIMARY KEY into two tables that can be joined on that PRIMARY KEY?

I have a table that has 46 fields with an AUTO_INCREMENT PRIMARY KEY. It's hard to look at a table that has this many columns, so I frequently use a SELECT statement to look at 34 of the fields that ...
6
votes
1answer
575 views

Loyalty reward - record for every day of a week

I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week. So, the real question, how do I get every ...
4
votes
4answers
21k views

Call a stored procedure from a trigger

I have created a stored procedure in mysql using the following syntax. DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- ...
4
votes
1answer
217 views

DB Master Key Encrypted By *Correct* Service Master Key?

My client has frequently run process where we move databases from a prod environment to lower environments (refresh QA with prod database, etc). These DBs have database master keys on them. We have ...
8
votes
1answer
3k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
2
votes
1answer
289 views

Rewriting a query

I have a database with the following structure (star schema): facttable: pros integer mena integer me integer vahet double precision slutttp timestamp without time zone Structure of each ...
6
votes
3answers
2k views

Why performance degradation with query hint FIRST_ROWS(1000)? [closed]

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input. In case the user supplies very unselective arguments, the query takes a lot of ...
2
votes
1answer
571 views

SQL Server 2008 r2: What events/queries hit tempdb

We had a slowdown on a db server this morning. Checking the logs, the only thing that happened during the 2-minute slowdown worth noting was an object being created and deleted in tempdb over the span ...
2
votes
3answers
844 views

View Design Best Practices

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables the desired description field obtained through the FK id relationship both? ...
2
votes
2answers
160 views

strange behavior with Turkish language setting

I have database with Collation Arabic_CI_AS when I have windows with English settings I can do select Column from Table where Column= 'A' and can do select column from table where column= 'A' ...
3
votes
1answer
2k views

Postgres PgAdmin III Query Builder crash

I've been using Postgres and pgAdmin III for some years, but this morning I had a very BIG BIG problem. I'm using pgAdmin III 9.0 on Windows Xp operating system, on my laptop Dell Latitude E4300. ...
2
votes
1answer
803 views

Delete rows in a table that has multiple FKs

I need to delete all the rows in the ContentData table that have the word 'DV95' in the htmldata column. Here is what I am using: BEGIN TRANSACTION DELETE Contentdata FROM Contentlinks INNER JOIN ...
3
votes
2answers
2k views

Why Sqlcode 100 does not come with exception

I have following query in a PL\SQL procedure on Oracle 10.2: This is the code LOOP BEGIN SELECT a.poid_id0 into v_acc_account_poidid0 FROM account_t a WHERE a.poid_id0=i_acct_id0 ...
4
votes
4answers
331 views

DB Optimization with 2 to the power of N - Approach clarification

This is a request for analysis and better performance approaches, one of my colleague has come up with an approach in optimizing the DB and it looks very impressive too. Can someone please let us know ...
1
vote
3answers
2k views

Inventory Database Design Issue

G'day, I own a jewellery retail store alongside an online store, and am in the midst of building a completely custom system to track all of my inventory. I also have a number of stockists (other ...
4
votes
1answer
449 views

database design issue

This is my first time on this beautiful content rich site. I have a sql server 2008 database, but am not too experienced with good relational database design. My project is this: I have a school ...
6
votes
1answer
277 views

in postgres, can one optimize a table across partitions?

in a database with many companies, segregating them using schema and search_path almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting ...
3
votes
2answers
3k views

“on duplicate key update” all the non-key fields mentioned in the insert

How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion? insert into tablename set keyname = 'keyvalue', fieldname = ...
4
votes
2answers
3k views

MySql - How can I speed up this query

I have the following tables: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `last_name` varchar(255) COLLATE ...
1
vote
1answer
260 views

What's wrong with this update RANK query?

It was updated only the first time I ran it, after that the users_tbl_rank stays the same. $sql = "SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC"; In ...
0
votes
1answer
403 views

which DBMS should i go for? [closed]

I am interested in learning a DBMS. As a beginner which DBMS should i go for ? I know few DBMS by name like Oracle,Sql Server,My Sql . How are these DBMS different from each other ? As a beginner ...
0
votes
1answer
59 views

Oracle: calling udf that modifies the data. How to do it right?

Say I have a function : create or replace function test_function return integer as PRAGMA AUTONOMOUS_TRANSACTION; t_test integer :=1; begin update test1 set id = id+1 ; // table test1 has, for ...
0
votes
1answer
401 views

SQL Server collation mismatch

I have database server with collation: SQL_Latin1_General_CP1_CI_AS I got database backup file which collation is: Latin1_General_CI_AS When I'm restoring this database and executing some SQL, I'm ...
4
votes
1answer
938 views

Nested queries or temp (#) tables in SQL Server

I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' ...
2
votes
1answer
996 views

How to make it impossible for a postgres user to delete databases?

What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases? Or can I add a rule into some config file?
5
votes
1answer
337 views

Replication of database used by two programs

I have a peer to peer transactional replication set between two identical databases, each one of the databases is being used by a different program on two different servers. Since we implemented the ...
5
votes
7answers
756 views

Database table and NULLs

I know that there have been many opinions/sides concerning NULL values in a database. I have not understood though what is the best practice for this. I.e. if I have a relational table with an ...
1
vote
2answers
285 views

SQL Server database mirroring on different servers [closed]

I have two different Windows Sever 2008R2 instance located at different places such as db1 and db2. I need to make db2 as mirror server of db1 i.e. all the database on db1 will have a mirror copy on ...
2
votes
2answers
960 views

How to select a subset of data from a large ER model?

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
1
vote
1answer
585 views

Insert bulk Parent and Children rows using SQL Query

We receive bulk data from our customers in a spread sheet. I loaded them in a temporary table as of now. I tried to normalize the data and create a parent table and each parent to have 4 or 5 child ...
2
votes
2answers
106 views

Retrieve job Details in SQL Server 2005

How do I retrieve information about a SQL Server Agent job scheduled against databases on SQL server 2005?
3
votes
5answers
6k views

Copying Oracle 11g express Database from one server to another

Basically, I am a MS-SQL developer,and relatively new to Oracle. Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another. In ...
1
vote
3answers
1k views

sql server 2008 - controlling size of log file

I am getting this error. Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc ...
4
votes
1answer
1k views

How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings: FOR COLUMNS SIZE 5 some_column and FOR COLUMNS some_column SIZE 5 both will create a ...
14
votes
1answer
6k views

How do I read Query Cost, and is it always a percentage?

I'm currently studying for SQL 70-433 (the Microsoft Certification exam), and I'm getting very confused about the "query cost" performance metric. According to any documentation I could find via ...
7
votes
2answers
5k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
1
vote
3answers
496 views

Upgrading to SQL Server 2008 R2 but downgrading the edition - will this cause problems?

I am looking at upgrading our SQL Servers from 2005 Enterprise edition to SQL Server 2008 R2 Standard edition. Obviously, the upgrade to 2008 R2 gives us more features even in Standard edition, but I ...
1
vote
3answers
497 views

why locks a table will improve performance during transaction?

I just started to read about table lock in SQL (from this page) It saids: table lock may reduce the overhead of acquiring a large number of row or page locks and save overall locking time ...
1
vote
1answer
66 views

How do I determine if a database restore file should be restored to the database based on backupset?

I have a production database that is fully backed up nightly and incrementally backed up hourly. On our backup machine, we have the differential backups and full backups restored ion a regular basis ...
1
vote
1answer
7k views

In which folder / directory does SQL 2008 store files?

Where can I set the folder to store a database in when I create a new Database in the Microsoft SQL 2008 Server?
1
vote
1answer
1k views

SQL Server job failure

I have a job that executes every 15mins on SQL Server 2008 and it comes up with the following error at least once in two days. But, it runs successfully the next time it runs. The account that is ...
1
vote
3answers
384 views

How to implement superkey sub key method on cascading tables

If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to ...
4
votes
2answers
2k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
1
vote
3answers
1k views

Local user login access in sql server 2008

I have a created a local user called 'MynetDB' with SQL Server Authentication (password) in SQL server 2008. Server roles is public and user mapping to 2 databases as db_owner and public. Status has ...