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
3answers
301 views

Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server

SELECT A.Field1 FROM Table1 A INNER JOIN Table1 B ON A.Field2 = B.Field2 AND A.Field3 = B.Field3 AND A.Field4 = B.Field4 AND B.Field1 = "string1" AND B.Field2 = "string2" AND A.Field5 = (SELECT ...
4
votes
2answers
579 views

How should I best design the tables and relationships, given the following rules?

In a Pawnshop business, customers pawn, sell or buy items. A contract which specifies the customers information, the items, and the terms and conditions is created whenever customers pawn, sell or buy ...
3
votes
2answers
208 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
5
votes
3answers
1k views

Many-to-Too-Many Relationship

I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question): tblBook: ID, Title tblPerson: ID, Name Note that the tblPerson table contains ...
0
votes
3answers
60 views

Query Optimizing Request

I am very sorry if this question is structured in not a very helpful manner or the question itself is not a very good one! I need to update a MSSQL table call component every 10 minutes based on ...
1
vote
1answer
157 views

Stored procedures and network congestion

When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it ...
1
vote
2answers
100 views

Easy SQL, embedded select

I have a table of zip codes with latitudes / longitudes. Unfortunately it doesn't seem to have a few zip codes. I would like to try to approximate the latitude / longitude based on other zip codes ...
2
votes
1answer
4k views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
4
votes
1answer
1k views

Does UPDATE INDEXES work for local indexes?

I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed): CREATE TABLE table1 (col1 INT, col2 INT) PARTITION BY LIST(col1) ( PARTITION p1 VALUES(1), PARTITION ...
2
votes
3answers
235 views

MySQL data view

I have a table that looks like this: +----------------------------------------+ |Name | kode | jum | +----------------------------------------+ | aman |kode1 ...
0
votes
1answer
98 views

Logging of incorrect row in SQL Server Integration Services

I newbie to SQL Server Integration Services and I have a problem: Data loaded from file looks like this: 1 'a' 'b' 2 'a' 'b' 1 'c' 'd' And this rows would be loaded in table like this: CREATE ...
2
votes
1answer
439 views

Recovering from a stuck query

I have a big DELETE query (deleting 20M rows from a 600M row table with condition according to indexed column) that is stuck - running already for 17hrs. Because it's an InnoDB table, killing it will ...
3
votes
1answer
120 views

query discrepancies on very large postgres table

I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem. these two queries although similar are taking massively ...
1
vote
1answer
96 views

sqlite database: advanced SQL or redesign

I've been programming for several years, but I'm pretty new to SQL(ite). I'm designing a database for a sort of a bookkeeping program. What I've got so far: orders (o INTEGER PRIMARY KEY, ...
0
votes
1answer
1k views

Access query to select multiple records with matching field

I have a table with a list of people ([FirstName], [Surname], [Age]). Some of these people are in the same family. I'd like to return a list of people who have another family member in the table (same ...
5
votes
2answers
3k views

Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

E.g. consider the SQL query: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC) FROM [FooTable] AS A Here I observe the results being returned sorted by A.[Name]. If I change the ...
2
votes
1answer
645 views

Populating an empty table using loops, conditionals, and accessing multiple tables

This question pertains to a MySQL database, although, in the end, this may be database neutral. I am trying to populate an empty table (let's call it NewTable) that I created. The data population is ...
7
votes
1answer
635 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
2
votes
3answers
1k views

Is there a way to tell SQLCMD not to output rows to text or a file?

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows ...
1
vote
0answers
164 views

Pass in dynamic value to Oracle job scheduler

How does one pass a dynamic value into the oracle job scheduler when the underlying command is a stored procedure? example procedure: create procedure foo (bar varchar2) ... Job creation in ...
4
votes
5answers
278 views

How much does an index need to narrow the results of a search in order to be useful?

How much does an index need to narrow the results of a search in order to be useful in speeding up queries? Some examples all across the spectrum: A column for storing true/false values obviously ...
1
vote
2answers
117 views

How can I append a column to an inner SQL statement?

I have two tables that have the same structure except one has an additional column that should be auto-incremented. Sequence and trigger are defined. I want to copy all data from one table to ...
0
votes
1answer
320 views

how to pass url value to index [closed]

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
1
vote
2answers
231 views

SQL query based on foreign key relationship

I have two tables, a Project and an Action: Project has the following fields: project_name Action has the following fields: action_name project status There is a one-to-many relationship ...
1
vote
0answers
148 views

SQL relationships, one-to-one, many-to-many etc [closed]

I'm trying to understand all relationship types that can be created in SQL. So far I only get the "belongs-to" one - Example: a table with "Blog post" records, which contains an author_id column that ...
2
votes
0answers
414 views

MySQL database structure: more columns or more rows [closed]

I collect how people tag topics with categories in table like: ID | topic_id | votes_Category_1 | votes_Category_2 |.......... | votes_Category_12 This table holds 1 row per topic and dynamically ...
3
votes
2answers
974 views

Blocking drop database command

I created a MySQL database and created a new user dedicated to that database with all permissions. The new user then proceeded of dropping the database in addition to all tables. I need to block that ...
10
votes
1answer
3k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
1
vote
3answers
3k views

Putting SQL Server data files in a different partition

I have a SQL Server and I put the data files (the .mdf) on a different partition. Windows runs from C: and the data files and logs are on D: Is it advantageous or disadvantageous to separate the ...
12
votes
5answers
11k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
2
votes
0answers
114 views

QBE Query By Example. How does it handle OR conditions?

I've been looking at implementing QBE into an application but need more information on how more complex queries work. How does QBE handle "OR" conditions? For example when searching a telephone book ...
1
vote
2answers
288 views

correct security and user setup for SQL Server database

I am currently on setting a sql server database on a database server. A front end web application running off a web server communicates to the database. What is the best approach for setting up users ...
0
votes
1answer
86 views

SQL Query for the following structure

Given the following type of data structure. ID NAME TREELEFT TREERIGHT PARENT 1 XYZ 1 2 0 2 ABC 3 6 0 3 DEF 4 5 ...
9
votes
3answers
485 views

Are these two queries logically equivalent?

Are these two queries logically equivalent? DECLARE @DateTime DATETIME = GETDATE() Query 1 SELECT * FROM MyTable WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7 Query 2 SELECT * FROM ...
5
votes
1answer
172 views

GROUP BY on a dependent field

Let's say I have a SELECT statement as follows: SELECT ..., field1 FROM ... GROUP BY field1 Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent ...
1
vote
1answer
263 views

GROUP_CONCAT with ORDER BY , but results are not ordered

The following query works fine SELECT *, (SELECT GROUP_CONCAT(url SEPARATOR '$$' ) FROM project_photos WHERE project_id = projects.id ORDER BY priority) AS images FROM projects WHERE catID = 2 LIMIT ...
5
votes
1answer
374 views

Does SQL manage foreign key constraints in terms of Insertion?

Say I have a table tbl1 with data A1 and A2, where A1 is the primary key. Then I have another table tbl2 with data A3 and A1, both together being the primary key, referencing A1 as a foreign key from ...
1
vote
2answers
259 views

SQL Server Monitoring Tools [closed]

Can anyone recommend a good SQL server Monitoring Tool? One which will provide info on, server space usage, CPU usage , expensive queries run etc.
1
vote
0answers
171 views

Multi-Table Join to Single Destination

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL. To ...
1
vote
1answer
106 views

Getting the page name from a web URL in Oracle

I have a list of page URL's in my database table under the column heading URL. There are many different prefixes on each page name for example: Test1/Admin/Page7.asp Test1/One/Page3.asp ...
1
vote
1answer
40 views

Order questions depending on answer votes

I'm really curious about how to create an SQL query to order questions depending the votes_up and votes_down. (The votes can be the sum of both, votes_up + votes_down.) Question has_many answers ...
0
votes
3answers
2k views

ORA-01562: failed to extend rollback segment number 11

I´m trying to execute this query into a SP DELETE FROM table SC WHERE SC.evaluationMonth= 'Some month'; COMMIT; But for this month there are something like 10 million records. So I get the ...
0
votes
2answers
194 views

Print variable of same datatype

I am looking for to print two variables of same datatype I have something like this declare @one int ,@two int set @one=1 set @two=2 print @one+@two obviously it will give a result 3 but I need ...
1
vote
1answer
310 views

Index strategy for a Queue Table, getting rid of filesort

I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is ...
0
votes
2answers
94 views

Grouping data checking against top values

I've got data in this form: CallNum Value Accepted -------------------------------------- 971374 81.482204444473609 True 971374 83.783551111089764 False 971374 97.936875555547886 ...
0
votes
1answer
63 views

How to add the aliases to the group by clause

In the below query how to add the aliases assignedTOiti, assignedTOasstAdm, assignedTOhlp, closedDate to the group by clause. I am struggling for the past few hours. SELECT ieor.aper07_req_k ...
8
votes
3answers
4k views

SQL Server 2008 datetime index performance bug

We are using SQL Server 2008 R2, and have a very large (100M+ rows) table with a primary id index, and a datetime column with a nonclustered index. We are seeing some highly unusual client/server ...
3
votes
2answers
2k views

Select query according to date

We have a table to store the Events. Table has got a schema like EventID INT Primary Key EventName NVARCHAR(100) StartDate Datetime EndDate Datetime So in the front end we want to show the events ...
3
votes
1answer
9k views

moving data from one db to another using SSIS

I am very new to SSIS and need to work out how to setup a transfer from table A database A to table B database B. table A contains many more fields than I need in table B, so the process needs SQL ...
6
votes
3answers
940 views

SQL Server 2008 mirrored Database with manual failover, need reporting database

Our current setup is a principal database used in production with a manual failover mirrored database for HA. I need to setup a reporting database as well. What would be the best approach? Shall I ...