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
1answer
245 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
11
votes
3answers
4k views

Choosing a database for storing large amount of data and allowing for thousands of writes [closed]

I am working on a project that has some serious requirements regarding its database and having hard time choosing the best solution. The details are: Database will easily reach tens of terabytes of ...
1
vote
0answers
24 views

SQL Server Profiler

I had run a SQL trace on SQL server 2008 r2 and had originally saved it to the table format. This table now resides in the database and I was wondering could I export the contents of the table to a ...
0
votes
0answers
16 views

How to fetch two column different row values in sql

I need emp table column name is ename 'Martin' and 'jones' details and in job column who every working as 'clerk' they both want to fetch Martin and jones are working in different jobs
3
votes
1answer
347 views

System.Data.Entity.Infrastructure.CommitFailedException: C# Multithreading & SQL Server 2012

We have a C# multi threaded (100 threads) program which reads the records from the DB and each thread picks up one record (one Entity Framework connection per thread) and update the a single DB table. ...
1
vote
0answers
19 views

Select statements during long update

I've a table MESSAGES id (long) | attach_data (image) | ins_date | flag this table contains all messages of my application. During night, my job cleans old attach_data with query update messages ...
0
votes
0answers
8 views

i have a roll_no,semister_id and marks of semister i want to show result like roll_no semister_1 and semister_2

I have a roll_no,semister_id and marks of semester, and want to show result like roll_no , semester_1 and semester_2 [As shown below]
0
votes
0answers
25 views

How to tune a stored procedure that is fast on test data but slow in production

If you have a database (SQL server 2012) with testdata in development and preproduction phase and the real from the company's server take place only in the production phase. In other words, the real ...
0
votes
1answer
20 views

Deleting near duplicates based on one varchar field

I'm getting around to cleaning a database but I need some help with deleting near duplicates in MySQL database but is only off by one character in a column. I've searched and found ways to remove ...
9
votes
3answers
867 views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
11
votes
2answers
58k views

Oracle: Quick way to list all database links

The title says it all, is there a way to quickly list all the current database links in oracle? Something along the same lines as this, which lists the current user's tables: select * from ...
2
votes
2answers
952 views

Designing database with Category and Tags for Posts - Seeking for guidance/advice

I'm not really a database novice but I'm not an expert either. Problem: I'm having this difficulty on designing my Post, Category and Tags relationship. I've read Normalization many times but could ...
0
votes
0answers
13 views

Compare values between columns in different tables

I have two tables that looks exactly the same: CREATE TABLE `_sync_history` ( `id` int(11) NOT NULL AUTO_INCREMENT, `object_name` varchar(45) DEFAULT NULL, `sync_time` datetime DEFAULT NULL, ...
0
votes
2answers
348 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
1
vote
0answers
40 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is ...
3
votes
4answers
3k views

How to make SSMS upper case keywords

I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper ...
2
votes
1answer
67 views

select n values per group

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 ...
3
votes
2answers
4k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
1answer
550 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
0
votes
2answers
39 views

How many passes of a table are done with UNION ALL? [on hold]

I am trying to run a single query that will make a single pass through a table, and was wondering if by using UNION ALL to make counts of total row unioned with counts of unique rows so I would be ...
4
votes
1answer
150 views

Is this a sane model for a network provisioning system?

I'm sketching out a model for a network setup, where I have the following conditions: There are multiple nodes which contains a number of switches each. Each switch has multiple services, which may ...
0
votes
2answers
3k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
0
votes
1answer
138 views

How to merge two databases, both with primary keys

I have two databases, both with the same tables, but the data is out of sync. I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a ...
2
votes
1answer
126 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
1
vote
2answers
19 views

MYSQL find if a default value has been set for a table (DateTime Specifically)

I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers. ...
2
votes
1answer
24 views

How to write a query that counts rows by date?

I have a database named emp1. There is a table named tx_feeder. I want to get the count of rows from that table. I have used the following query to get the data of single day, select count (*) ...
1
vote
1answer
50 views

Minimum value on most recent date per account MS ACCESS SQL

I have a MS ACCESS DB table Riskscore with the three fields: CHACCTBS, CHRQDATE, CHSCORE and want to find the lowest CHSCORE on the most recent CHRQDATE per CHACCTBS where it is possible to have ...
0
votes
0answers
22 views

Creating a trigger to update field in 1 table based on values in another table using data in multiple rows

I'm new at SQL and need to create 2 triggers between 2 tables. Table 1 has the following columns: tbl1.ContractID char(6) tbl1.ContractFulfilled char(1) Table 2 has the following columns: ...
3
votes
5answers
5k views

How to get the total days per month between two dates?

Given two dates like 20120302 and 20120605 I need to get a list of Months and the total days in those months that fall between those two dates, like so: March 28 April 30 May 31 June 03
-1
votes
1answer
17 views

T-SQL Trying to create a trigger that uses joins across 2 server instances [on hold]

I am trying to complete a trigger on 2 instances of SQL Server. I am running MS Dynamics ERP set CRM & GP. I need to remove data from one server once an update happens to another table. I have ...
-1
votes
0answers
44 views

Design for fast database query over millions of rows [on hold]

I want to query in a database like : Let us consider a student table with the following attributes: (Id, Age, CGPA, Address, Gender). Our table contains record for all the student in the world (so my ...
0
votes
1answer
169 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
1
vote
0answers
124 views

Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many ...
0
votes
3answers
244 views

What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

What's best performance wise, two separate UPDATEs: UPDATE table1 SET enabled = 0 WHERE code NOT IN ( SELECT id FROM table2 ); UPDATE table1 SET enabled = 1 WHERE ...
2
votes
4answers
103 views

Finding all date ranges that overlap a target date range

Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate). Now assume that I have many such ...
1
vote
1answer
28 views

Modulo operator with nonintegers - unexpected results

MySQL 5.5.32, Oracle 11g MySQL returns strange results for queries involving mod() with noninteger moduli. Why is this? I have a column defined thus: ...
2
votes
2answers
34 views

Ensure only one of the several records for a given item has a column with true value

I have a table called Computer with columns: id NIC_name is_primary My primary key is (id, NIC_name) and is_primary is a bit field for a boolean value. I would like to ensure that for all ...
0
votes
0answers
27 views

INSERT INTO SELECT causing data to double

I have strange issue. I am working in a Teradata database. In a procedure I am populating a main table by processing data via multiple intermediate tables. But in the end only one table is populating ...
9
votes
2answers
16k views

SQL Server backup failing. Error: 3041, Severity: 16, State: 1

First of all, I'm very new at stackexchange, so please bear with me. I'm running a SQL Server 9.0.4060. My problem is: my SQL Server backup keeps failing on a lot of the databases. The Maintenance ...
0
votes
1answer
102 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
1
vote
1answer
42 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
1answer
23 views

SQL Server restore database file access error

I am trying to restore a database from a file on a remote machine. The script I am using is as follows: RESTORE FILELISTONLY FROM disk = '\\server\path\to\file.dmp' RESTORE DATABASE MyDB FROM ...
0
votes
1answer
23 views

inner join on true

My table: abc +--------+ | letter | +--------+ | a | | b | +--------+ Query: SELECT * FROM abc t1 INNER JOIN abc t2 ON true Result: +--------+--------+ | letter | letter | ...
1
vote
1answer
14 views

Address Windowing Extensions Overhead

I just found another SQL Server (2008r2) with AWE enabled on a 64-bit OS/SQL combination. Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination? It ...
2
votes
1answer
45 views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
-4
votes
0answers
22 views

what is update cascading exactly in sql? [on hold]

We have three table declarations for P1(A), Q1(B) and R1(C) with referential integrity in SQL. create table P1 (A primary key); create table Q1 (B primary key references P1(A) on update ...
0
votes
1answer
23 views

Virtualized SQL Server Memory Oddity

I've not seen this before, though perhaps it is common. I am looking at a virtualized (VMWare 5.5) SQL Server (2008R2 on Windows 2008 R2). What I am seeing is that in Task Manager, sqlserver.exe is ...
1
vote
3answers
58 views

SSIS: How to check if a record DOESN'T exist in flat file but exists on the database

I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. ...
5
votes
1answer
51 views

Find rows where integer sequence contains a given subsequence

Problem Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL. I have a table representing sequences of integers. The definition are: CREATE TABLE sequences ( id ...
0
votes
3answers
84 views

SSIS - Best way to insert large number of rows

So here's the scenario: I have an XML file, which is in size of 500GB, and with data of around 600 million rows (once on a database table). I'm using SSIS for the operation, and since it consumes a ...