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)

4
votes
1answer
2k views

MySQL failing and very slow on importing a large file

I have a 47 GB MySQL dump of a single table: http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz I ultimately want it into PostgreSQL, but since I didn't figure out an easy ...
2
votes
1answer
284 views

How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I ...
0
votes
0answers
136 views

Linked Server for MultiSubnet AG Listener

How do I create a linked server for an AG Listener that is in a multi-subnet configuration? I have a couple clusters that need to be able to query the listener at hand but the node that is on the ...
7
votes
3answers
5k views

SQL Calculated Field in both SELECT and GROUP BY clause

Often in querying my MS SQL Server databases, I need to create a calculated field, such as this (CASE WHEN A.type = 'Workover' THEN 'Workover' ELSE (CASE WHEN substring(C.category, 2, 1) = 'D' ...
0
votes
1answer
631 views

Partition tables based on month and year in SQL Server

I have a sales table which I need to partition based on month and year of sales table structure S_ID numeric(18,0) S_CustID numeric(18,0) S_TotalCash numeric(18,0) S_Paid ...
1
vote
1answer
112 views

Is it possible to restrict SQL Server DB_Owner when connecting using SQL Server Mgt Studio? [duplicate]

Here is the back story. A developer created an EXE program. The exe program has the ability to access a SQL Servre database. In order to do that we create an Active Directory group. Let's call this ...
2
votes
0answers
148 views

Use Matlab or SQL Server to do mathematical computations? [closed]

Current situtation I am working with a dozen of CSV files containing financial simulations in form of 100'000x100 matrices. Each file is around 100Mb large. Until now, only analyse these files with ...
0
votes
0answers
96 views

Multiple update of records in the same table

I have an update query. E.g. UPDATE table SET name = ? WHERE id = ? Now I have all the relevant ids collected. What I am doing is looping over the ids and execute the update query for each one. E.g ...
1
vote
1answer
98 views

DB2 LUW Query Optimization - IXSCAN timerons greater than total timerons

Sorry - I feel like this is a newb question, but I can't find anything on it. In optimizing a query, I was able to apparently reduce the total timerons from 3,700 to 38. However, on closer examination ...
2
votes
2answers
8k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
4
votes
2answers
1k views

if-then-else gives a boolean headache

I have three statements to combine but I cannot find the correct solution for SQL Server 2008 R2: I need to concatenate three columns into one but in three different ways depending on the result in ...
3
votes
1answer
820 views

sql server service broker queue pass parameter to stored procedure

Stored Procedure Create PROCEDURE [dbo].[InitServiceLog] ( @name varchar(max)=null ) AS BEGIN SET NOCOUNT ON; insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) ...
0
votes
0answers
275 views

PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

I am working on a financial software where we have huge table of ticks with more than 800M rows. A single row: symbol_id, broker_id, date, time, bid, ask The problem is the table has many ticks ...
1
vote
2answers
144 views

MySQL server wont' start. Reason: Internal error

The very vague error message Reason: Internal error I've had this happen before, and it usually fixes itself after I restart my machine, but after the 4th time now it still is showing the error :( ...
0
votes
1answer
174 views

Attributes from an hstore-column as separate columns in a view?

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs. You could query that column as such: SELECT name, attributes->'device' as ...
1
vote
1answer
52 views

What are the SQL standards [closed]

Some job descriptions mention required knowledge of SQL standards. What do they mean by that? I found something on wiki http://en.wikipedia.org/wiki/SQL#Standardization but I don't really know what ...
0
votes
1answer
288 views

What is the Difference between IN and Not IN performance wise?

I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise. When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
5
votes
1answer
96 views

Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012

Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev ...
0
votes
2answers
97 views

Subquerying results in no row returned although records exist

I have two tables, namely Employees and Payments. Below is the contents of the two: The Employees table EmpID |EmpName ------ -------- 113 Harry The Payments table EmpID | Amount | Period ...
1
vote
0answers
94 views

How to join the tables in mysql to get wall messages and comments with name of person ? Only user and his friends messages

I have four tables in my mysql database as shown in the image url provided. I want to select data from the tables without duplication. I want to show messages and comments on wall according to the ...
1
vote
1answer
96 views

The meaning of the Roles in SQL Server 2008 R2 [closed]

Just because I’m confused around this issue I’d like to ask the following questions, and please I would like to receive clear and easy understandable answers. Not deep analyze. 1.What is the Roles ...
1
vote
3answers
6k views

How to calculate exact month (in days) difference b/w two dates

I am trying to get all the rows where the visit date was exact before five months from to date but somehow DateDiff function is including 26th Sept 2013 record which is not right as five month has ...
0
votes
0answers
171 views

SQL Server Mirroring send/receive ack time not normal

I have a problem that arises during the performance test by microsoft team in my client. The report that is given to us provide information about whether or not a parameter is good based on the Object ...
0
votes
0answers
95 views

Attendance Database using sql in MS Access

I'm currently working with a simple database for the attendance of the professor in our college. I'm using MS Access 2010 with a Main table that includes the Attendance log of all the professor with ...
0
votes
1answer
236 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
0
votes
1answer
105 views

“Table or view does not exist” on synonym

I have a database and a USER administrating it. Then I have an additional USER_APP to whom I granted CRUD access to all tables in my oracle data modeler (and then applied these via generated script ) ...
0
votes
1answer
49 views

Create Table with Int-attribute which can only use values between 6 and 36

Hello I'm learning for my databases examination and in one exercise they asking for creating a table with a attribute which can only use values between 6 and 36. How do I solve it? We used in the ...
3
votes
4answers
594 views

innodb DEPENDENT SUBQUERY very slow

Here is some innodb table. It insert,update,query very frequently. tn is product id col is product sub id tindex is product description.(store by each word) date is product add date stored by ...
0
votes
3answers
123 views

How to query for 'First showed up as'

Given the following table: Date Trd (INT(10)) Code (VARCHAR(10)) T-Date 1-Jan-14 1 B 1-Nov-13 1-Jan-14 2 B 3-Oct-13 ...
1
vote
4answers
529 views

How to handle table design with variable columns

I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable. Say you are asked to record information on houses for a metro area, starting with a small ...
2
votes
1answer
2k views

“No row was updated error” in SQL 2012 - worked fine in SQL 2005 and 2008

I have a database that currently lives in SQL Server 2005. We are currently looking at upgrading to SQL 2012. I installed 2012 in a test environment (Windows Server 2012 virtual machine). Backed ...
1
vote
0answers
221 views

Can not ping Always On listener from outside primary node

I just setup SQL Server 2012 (on Windows Server 2012) Always On feature and have a problem with connecting to SQL Server Listener from non-primary node (it works from primary node though). Here is ...
1
vote
2answers
104 views

sql custom calendar question

I need to create a custom report for my PMO where they want allocation to be calcualted by multiplying working days for each month * total hours For example January 2014 should have 21 working days ...
5
votes
1answer
182 views

Numeric data type with variable significant digits

I need to implement a data type which should preserve the last zeros in the decimal points in SQL Server 2008 R2. Example of the data are 1.002 1.210 0.00100 0.1401 My clients get lab measurements ...
1
vote
1answer
44 views

What indexes would I create for a query like this?

Here's a query I've got: SELECT "balance_transactions"."fee" FROM "balance_transactions" JOIN charges ON balance_transactions.source = charges.balance_id WHERE "balance_transactions"."account_id" = ...
0
votes
1answer
82 views

Using inner join on a specific record?

So we have 2 tables users: id | username | email 1 | testuser | test@test.com 2 | dirk | test2@test2.com data: id | data | username 1 | somedata | dirk 3 | somemoredata | ...
0
votes
1answer
125 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
0
votes
1answer
150 views

What will be the best mysql configuration for 12 core processor 128 gb ram ssd 160gb hdd

What will be the best mysql configuration for 12 core processor, 128 GB ram sized 160gb HDD. Mysqltuner.pl result! -------- General Statistics -------------------------------------------------- [--] ...
-2
votes
1answer
99 views

can exec work with while loop of cursor? [duplicate]

Declare @var varchar(200) set @var='select max(val) from floattable' declare c1 cursor for select id from floattable declare @val int Fetch next from c1 into @val open c1 while @@Fetch_Status=0 ...
1
vote
2answers
151 views

Postgres slave max_connections = master's max_connections?

I am using binary replication for Postgres. Is it true that the max_connections has to be the SAME for both the master and slave in postgresql.conf? Can they be different?
0
votes
0answers
64 views

Filemaker to Firebird data exchange

I want to connect from filemaker to a firebird 2.x DB. Firebird ODBC is not supported, only MYSQL, MS SQL etc. Is there any way to have a translator in between or maybe to connect through a PHP ...
1
vote
2answers
312 views

Is it possible to Install two (2) Firebird instances of different names & ports (gds_db port 3050 & fbsvc port 3051) running as service?

Is it possible to Install two (2) Firebird instances of different names & ports (gds_db port 3050 & fbsvc port 3051) running as service !? I mean the two running as service not application ...
3
votes
1answer
49 views

Unknown _TransactionIndex table in Azure SQL Database

Just noticed today that there is a random table in my Azure SQL Database that is named "_TransactionIndex_{guid}" It has 1 column, Id, that contains 8 GUIDs. Couldn't find any information about this ...
1
vote
1answer
350 views

System table missing in SQL Server 2012

I restored a SQL Server 2000 database to 2005 then to 2012. I am not able to see system tables that existed in SQL Server 2000 now. Can somebody please let me know what is the reason. Thank you
0
votes
0answers
31 views

sql performance query [duplicate]

I have table with columns like AutoID, Number, Name, City, State, Country. What I wanted is the maximum number entered in the "Number" column with the combination of Name, City, State and Country. ...
1
vote
1answer
103 views

Get maximum number with combination of columns [closed]

I have table with columns like AutoID, Number, Name, City, State, Country. What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country. ...
0
votes
2answers
55 views

Help with query statement

EDIT This query seems to work best so far. select host,plugin_id,month,year, row_number() over (partition by plugin_id order by plugin_id) as plugin_split from table where risk in ('High') group ...
2
votes
1answer
27 views

Running members of a job subset at different intervals

So I'm wanting to batch a number of stored procedures into one job and would like a number of the procedures to run on a schedule different than that of the main job without setting up a new job for ...
1
vote
1answer
95 views

Size of DB files

I am trying to optimize some settings on a SQL Server that talks to inhouse programs. I have read to size all DB files the same. I have a server with a 20GB data file, with the other 9 files being ...
1
vote
1answer
90 views

Check entry based on entry in another column

Still fairly new to databases and SQL, so bear with me. I'm trying to check whether or not data can be inserted depending on data in another column, create table Table1 (PaymentType varchar(5) ...