An acronym for "Structured Query Language", 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 access privileges.
2
votes
3answers
403 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
106 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
969 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
313 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
504 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
224 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
994 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 ...
3
votes
1answer
411 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
177 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 ...
2
votes
2answers
1k 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 = ...
3
votes
2answers
2k 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
180 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
289 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
49 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
242 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 ...
3
votes
1answer
518 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
365 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
240 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
574 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
171 views
SQL Server database mirroring on different servers
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
469 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
326 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
88 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
3k 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
601 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
819 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 ...
10
votes
1answer
2k 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 ...
5
votes
2answers
2k 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
341 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
344 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
57 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 ...
0
votes
1answer
2k 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
554 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
271 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 ...
1
vote
3answers
663 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 ...
1
vote
1answer
410 views
SQL database restore failed [duplicate]
Possible Duplicate:
SQL database restore failed
I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the ...
2
votes
1answer
140 views
SQL database restore failed
I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that ...
5
votes
4answers
264 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 ...
9
votes
3answers
4k views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
constraint ...
3
votes
3answers
992 views
NULLs in a composite primary key - SQL Server
I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the ...
1
vote
2answers
443 views
Update Large Table
I have these two tables:
user_profile:
user_profile_id_int int primary key
user_profile_id varchar
user_activity:
user_profile_id_int int key
user_profile_id varchar
both fields in user_profile ...
3
votes
2answers
2k views
SQL Server 2008 T-SQL select hanging, but not dead locked
This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
1
vote
1answer
89 views
Migrate Data and/or Schema from DBv1 to DBv2
I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null ...
3
votes
3answers
605 views
SQL Database size doesn't match with the total table sizes in the database
I have a database that is size about 104GB.
When I calculate the total table sizes in the database it's only about 24GB. This includes Data+index+unused spaces.
Why the DB size is too large?
1
vote
2answers
67 views
How can I return the number of values in each row which match a criterion?
I have a table "trial_volts" like this in an oracle database (I think it's 10g):
trial_index NUMBER
voltage_1 NUMBER
voltage_2 NUMBER
voltage_3 NUMBER
voltage_4 NUMBER
voltage_5 NUMBER
And I want to ...
16
votes
2answers
1k views
Using EXCEPT in a recursive common table expression
Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion..
with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) ...
3
votes
2answers
1k views
How to restore SQL Server database just by dbName and physical path
I have several database backups and I want to restore all of them to a server.
Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and ...
1
vote
2answers
85 views
How does an alter work?
I have a table with a lot of records (more than 15M) and i am executing an alter query to add 2 columns to the table.
the query is taking around 1hr to finish executing.
So my question is: How does an ...
0
votes
2answers
120 views
Need help merging numerous databases into one. Same primary keys, tables, and relationships
I am not the smartest person at all when it comes to databases. It took me and Google a lot of time to create what I have created, and here's what I need.
I built a database for a local charity ...
8
votes
2answers
794 views
How to delete a procedure which name is ambigous?
I am using informix...
I dont know how i did it, but there are two procedures with the same name in my database. When i try to remove them with a
DROP PROCEDURE myProc;
then i get a error message
...
