Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

2
votes
0answers
54 views

Emulating WITH clause in MySQL/query perfomance review

I have never really worked with MySQL queries till this last week. I was tasked with making a simple tool for my department that tracks contacts. Here are the 2 tables and the type of data they ...
2
votes
0answers
44 views

How to cope with SQL Server 2012 vs SQL (Azure) Database sql files

I'm trying to port my system to SQL Database (Azure instance) from SQL Server 2012. I'm using Visual Studio 2013 and I have my .sqlproj with all the definition of my database inside. Given that a ...
2
votes
0answers
231 views

Is it recommended to have redundant foreign key columns?

Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs: Without redundance A | id | ... | ------------ | ... | ... | B | id | a_id | ... | ...
2
votes
0answers
77 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
2
votes
0answers
70 views

Network and memory waits after massive ALTER

We had to do a massive ALTER to our DB, changing one central table's PK to BIGINT due to approaching ID's generated in the 2 billion range. Due to this, several hundred referencing keys and indexes ...
2
votes
0answers
825 views

Best practice - SQL Schema to store Timerecords for a Time and Attendance module

I'm starting a time and attendance module in C# and SQL. There will be many time rules depending on every shift. For example: Shift 7:00 - 14:00 for Administratives: 15 min grace time, but if ...
2
votes
0answers
121 views

SQL Server 2012 High availability Groups for OLTP and Reporting environments

I would like to seek your advice in designing a high availability architecture at my current work place. We are moving to SQL Server 2012 platform and we currently have a set of OLTP databases ...
2
votes
0answers
340 views

Empty Space in buffer pool

I was doing some reading and came across this article from Paul Randal: http://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/ Using the queries from that article, I ...
2
votes
0answers
66 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
2
votes
0answers
156 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
2
votes
0answers
1k views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
2
votes
0answers
92 views

SQL Server 2012 Replication

Please help me out with a problem I am facing. I have a table T. I altered the table to alter the column C from varchar (10) to varchar (100). Now, there is a view V that is schema bound and selects ...
2
votes
0answers
2k views

MySQL Queries taking too long to execute

I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% ...
2
votes
0answers
1k views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
2
votes
0answers
1k views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
2
votes
0answers
183 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 ...
2
votes
0answers
651 views

SQLite Performance Tuning

I have the following query, which fetches data from only one table. It is a query which is supposed to return data for an autocomplete function. Autocomplete data can be in text1 or text2. The ...
2
votes
0answers
215 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
2
votes
0answers
1k views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
2
votes
0answers
378 views

What is causing all of the msrepl_tran_version columns to become out-of-sync between locations in transactional replication setup?

I have been having a recurring issue with transactional replication setups at the firm I work for. We use SQL 2008 R2. Seemingly without warning or any replication errors, all of the ...
2
votes
0answers
137 views

sql data to a csv-file via cobol

I 'm using the following code : string 'insert into ' delimited by size 'tgamGameTeams ' delimited by size '(Id,Name,RemainingBudget,TeamGamePoints,GameId) ' ...
1
vote
0answers
26 views

Is this database in 1NF, 2NF and 3NF?

I'm doing a software development course and I have to normalise my tables as part of it. Can anyone double check that my data is normalised correctly? The database is for a gym. It has these tables ...
1
vote
0answers
41 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
1
vote
0answers
12 views

Transactional replication using articles filters and delete operations

The problem with transactional replication using articles filters and delete operations. I have a problem with my transactional replication when I use articles filters and delete operation. When I ...
1
vote
0answers
31 views

SQL Server dynamic inserts based on another table's rows

I've derived the following pseudo-coded script as a generic insert with variables that I want to replace for each row in another table (I can assume that the columns are always the same for the insert ...
1
vote
0answers
30 views

use IFNULL in laravel

I have a query in laravel which is working fine. $subjects = $app->db->table('subjects')->LeftJoin('downloads', 'subjects.subjectID', '=', 'downloads.subject_id') ...
1
vote
0answers
33 views

Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library

I am writing a PostgreSQL extension using the C language. I would like to do the following tasks: Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in ...
1
vote
0answers
19 views

Clustered index on last_update_at timestamp

I have a Votes table , which can be considered a wrapper. I have a sub_items table, which holds one votable item. One Vote has many sub_items. On sub_item is owned by only one Vote. I have a ...
1
vote
0answers
42 views

Multiple conditional counts in the select clause - what's the best way?

(Bit of a long one, sorry) To set the scene: N.b. This is on SQL SERVER 2012 We have a table [CardLog] in an access control system database. Every time an ID card is printed, a row is added to this ...
1
vote
0answers
39 views

Convert specific column into row as output in sql server

There are two tables Eligibility Claim In eligibility table if employee has categoryId like MED_COPAY, MED_COINS, RX_DED, RX_COPAY then Dependent should also eligible for these categories. So ...
1
vote
0answers
57 views

Configure Oracle database (first time user)

I am trying to learn how to use Oracle Database. I downloaded the Standard Edition version 12c from here: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html. I ...
1
vote
0answers
28 views

Joining and combining MySQL tables from Informix

I previously asked this in StackOverflow, But I couldn't find a solution, so I thought I'd share my problem here too. I have an old system that uses Informix for data storage, and I'm working on ...
1
vote
0answers
66 views

High CPU usage by only 4 users

New as DBA and I feel really confused with this... 4 users working, doing stuff with macros, commited_snapshot ON, 90% cpu ran sp_blitz and found that 1 query is using more than 50 plan caches... ...
1
vote
0answers
7 views

Is it possible to index serial8 type column for BTS in Informix

Trying to Index an Informix Database for Basic Text Search. One of the columns is of type Serial8. Is there a way or is it possible to index serial8 column for BTS, without changing the type of the ...
1
vote
0answers
16 views

ROW_NUMBER() window_agg rows estimation

PostgreSQL 9.4 Consider the following pretty simlpe query: EXPLAIN ANALYZE SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY date DESC NULLS LAST) rn ...
1
vote
0answers
18 views

Question on comparing two fields in Access

I'm not sure if the title is fitting as I am not 100% sure if what I am doing is correct but here goes. Here is my enrolment table, I have added a field called pass/fail as you can see which is ...
1
vote
0answers
42 views

Change login name for all objects on server

Example, I have a create a windows login DOMAIN\GROUP01, and mapped that login to a database DATABASE01 with lets say DB_OWNER permissions I want to change the login name, I went into Active ...
1
vote
0answers
97 views

Unexpected SYSDATE behavior

I have a SQL trigger which adds the old value ,new value and time of update for column based on update, insert, update or delete. To update the time we used SYSDATEand inserted record from trigger. ...
1
vote
0answers
42 views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC ...
1
vote
0answers
43 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
1
vote
0answers
33 views

Merge databases with sql only: firefox places and visits

I have two tables in each of two dbs: places: id, url (unique), visits: id, place_id, timestamp, type. I need to merge those databases: There are one main database and one "to-merge". The unique ...
1
vote
0answers
81 views

High load on server because of one query

I have one query running from last stable version of wordpress on causing high load on my centos 6.7 server I enabled slow query log thought /etc/my.cnf file to record which sql cause this problem ...
1
vote
0answers
28 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
1
vote
0answers
70 views

Improve lookup of 1.5 million rows

I have this table with data but we need to get all the (unique) rdx and rdy values: CREATE TABLE public.positions ( datetime timestamp without time zone NOT NULL, unitid bigint NOT NULL, rdx ...
1
vote
0answers
62 views

SQL query causes server gone away

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
1
vote
0answers
97 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
1
vote
0answers
49 views

How to find consecutive count last 3 months count and last 6 months counts

Column names: Site_key. Billing_date ,Reason_type Things to Find: Consecutive count for last 5 months: Last 3 month Count Last 6 month Count I have to find Consecutive count for last 5 months for ...
1
vote
0answers
27 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
1
vote
0answers
21 views

Why does SemiJoin node receive so bad statistic even after runnning ANALYZE

PosgtreSQL 9.4 I have two tables: CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer ); CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL ...
1
vote
0answers
93 views

Backing up / restoring a 20GB database with images stored as binary code

Please excuse me as I am a beginner. I am trying to backup and restore a 20GB database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues: The developers ...