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
254 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
80 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
333 views

Database Mail not sending

I'm trying to send email using the DatabaseMail on SQL Server 2008, I can see my emails sitting in the msdb.dbo.sysmail_unsentitems But they just sit there, I get no error messages. I've checked ...
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
882 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
122 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
353 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
67 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
134 views

WMI event alerts

I am trying to understand WMI event alerts and have implemented a mirror with alerts on DATABASE_MIRRORING_STATE_CHANGE such that all database failover if the state is either 7 or 8 (manual failover ...
2
votes
0answers
160 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
95 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
233 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
2
votes
0answers
201 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
670 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
217 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 friend....
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
379 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
140 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
21 views

Unable to identify matching rows

I am developing a Bus Route Demo application and I am trying to design the UI based on the below table sqlfiddle: PKID BUS_ID STOP_POSITION STOP_ID ------------------------------- 1 1 1 ...
1
vote
0answers
31 views

Sql - sort according to other PK column

I Have this table : table : [SheetGroupRank] ------------------------- | Sheet_ID | int | | Group_ID | int | | Rank | int | ------------------------- Sheet_ID and Group_ID ...
1
vote
0answers
20 views

where to find TPC-C queries

I've been looking around for specific TPC-C queries but haven't found good sources. Does anyone have a pointer? As I'm aware of, TPC-H only has 22 queries which could be easily found here http://www....
1
vote
0answers
27 views

Dynamic table name in Insert query

I have asked a question about dynamic table name in a prepared statement here , but I want to know if Mysql server support dynamic table name like this : SET @tbl = 'Dawin'; INSERT INTO @tbl (col1,...
1
vote
0answers
21 views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
1
vote
0answers
60 views

The query processor ran out of stack space during query optimization. Please simplify the query

I'm using SQL Server 2014. When running this query: DELETE FROM shuser WHERE datecreated > '6/21/2016' AND FkShUserCreatedBy = 1 GO I get an error: Msg 8621, Level 17, State 1, Line 1 ...
1
vote
0answers
30 views

Query Adjustment Issue - going Haywire

I am having difficulty with a query and after a day or researching, etc, I have decided to seek some advice here. My current query runs and looks like the table below: Type Resource Code ...
1
vote
0answers
56 views

Fastest way to shred large xml data into a table

I have a nightly process that shreds XML data from a 17 million row table (120 GB) into another table, it takes about 3 hours and inserts about 1.5 million records. I currently do a complete drop ...
1
vote
0answers
29 views

Can't drop table after creating table with wrong engine

I'm trying to drop a table containing several hundred thousand column-based records. Normally when creating the database I use a column-based engine (infinidb) but in this case I forgot to include the ...
1
vote
0answers
32 views

Determine kind of this SQL Deadlock

I have a Deadlock file & i can't determine the kind of this deadlock. Read-write Write-write Parallel query Index Other I stuck on this issue a week :( Here is: Deadlock File Note: open ...
1
vote
0answers
46 views

Constraint violation in a trusted constraint

I have a table with a constraint on it that has an existing record that is in violation of that constraint. While this is possible if someone applied the constraint without a check and after ...
1
vote
0answers
18 views

Two Dimensional Partition

We have a new application that we anticipate will grow substantially and we're exploring partitioning now. If we have a database that includes information, for example, across continents, and we want ...
1
vote
0answers
36 views

Return Maximum Rolling/Moving Count For Each Person For Any 90-day Period

Basics I have a table of patients with visit dates. There can be an arbitrary number of patients each with an arbitrary number of visits. A visit is recorded by saving the date of the visit. Only one ...
1
vote
0answers
33 views

How to export views and import it in other DB?

I am trying to export the database and everything is fine but I am not able to export the VIEWS. Is it possible to export the views from sql queries and import it?
1
vote
0answers
30 views

Querying pg_buffercache: relation “pg_buffercache” does not exist

I have PostgreSQL 9.4.7 installed in LXC container on Ubuntu 14.04. For monitoring purposes, i try to execute this from my host machine: psql -qAtX -h *** -p *** -U postgres -c "select count(*) from ...
1
vote
0answers
26 views

pub/sub database schema

I am having a hard time creating a pub/sub database schema with stateful metrics. Saying it aloud it sounds easy enough: "There are many organizations that will subscribe to checklists that are ...
1
vote
0answers
29 views

Select where table and column name are from another table

I know I need dynamic sql, but haven't gotten this to work: I want to loop through a table and for each row, return the value of the field that is located by two columns for that row. Master ...
1
vote
0answers
31 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
1
vote
0answers
22 views

How to design for scalable set operations?

This is the requirement for an educational application to teach words to language learners: Learner can declare that he/she knows a given word Learner can memorize the given word (to be managed by ...
1
vote
0answers
78 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a Azure V12 SQL Server query using indexed views. I have one slow element, which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped ...
1
vote
0answers
30 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
46 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
46 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') ->where('...
1
vote
0answers
34 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
54 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
40 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 ...