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

learn more… | top users | synonyms (2)

3
votes
1answer
79 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...
2
votes
2answers
155 views

Query to find Maximum friends not giving correct resuts [closed]

Table Schema for the Likes table: id id1 1 2 1 3 2 1 I count the total number of Likes for each student with this: select id,count(*) from friends group by id I have to find the students with ...
1
vote
1answer
250 views

Mysql setting a record as deleted or archive

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is ...
0
votes
0answers
538 views

Understanding the time difference between data grid initial display and export/full display using Toad for Oracle

I am trying to run a query on a local Oracle DB and output the results to a .CSV file. The query runs in a decent amount of time (usually less than 5 minutes), but returns tens of thousands (possibly ...
1
vote
1answer
3k views

Strange behaviour of a query with order by and rownum on Oracle's Exadata server

I am executing the following query on two different database servers: SELECT start_date, end_date, resort_id FROM employee_activity WHERE employee_id = 27 AND start_date < ...
4
votes
1answer
952 views

Query records that chain together based on date gaps

I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a ...
5
votes
3answers
32k views

Selecting data from multiple rows into a single row

I have a table that has columns like following. Batch number is a database design thing like row number demand_id batch_number debit status customer 34 1 ...
0
votes
2answers
3k views

Does MYSQL join before LIMIT?

Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker. Now imagine ...
4
votes
2answers
989 views

How to sort category by path and sort order?

first of all I am sory for my english. My category structure is shown below. My purpose is to get category structure with single query. I sort my table by path and I get category tree. sort_order : ...
0
votes
1answer
47 views

Need Some SQL help [closed]

I need to pass the following SQL statement: UPDATE table_products SET shipping = "a:5:{s:16:"min_items_in_box"; i:0;s:16:"max_items_in_box"; ...
2
votes
2answers
2k views

how does SQL Server 2008 handle joins internally?

for example, I have tables: User and Order User Table: UserId, Name, Address, Phone, Age Order Table: OrderId, UserId, Date, Item, Quantity SQL: select User.Name, Order.Date From Order JOIN User ...
1
vote
1answer
65 views

Correct data model for users with clients

I have an application where users are granted access. Part of the system allows users to have their own 'clients'. I have a users table ******************************** users ...
1
vote
1answer
221 views

Multiple primary keys

I am new to database design and have been looking at some example data models here. One thing I don't understand is if you have a table like client_addresses from the link above: ...
1
vote
3answers
128 views

Live monitoring SQL statements on SQL Server

How can I monitor running SQL Statements for my SQL Server, is it possible using only SQL Server without 3rd party tools? As an alternative can anybody recommend any free tool which can monitor ...
1
vote
1answer
922 views

Query is not using indexes on third table in left join

My query is not using indexes on third table(pci promotion_coupon_images) . i was tried with using index and force index in query but there is no change in result. any body please suggest me how ...
8
votes
3answers
3k views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
3
votes
2answers
12k views

ORDER BY clause is allowed over column that is not in SELECT list?

In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created ...
0
votes
1answer
374 views

Create a VIEW that spans 3 tables

Sorry if the title is not clear - feel free to improve. Here are 3 tables (not all data is relevant to the question): CREATE TABLE `content` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
50 views

simple query is not using indexes

My query is not using indexes. it was taking around 5 seconds. output is 500 rows only. it is a simple query Please find the explain plan and table structure here. Explain Plan mysql> explain ...
4
votes
2answers
911 views

Restore SQL Server database from .sql files

So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git The ...
2
votes
1answer
146 views

index doesn't work properly

I have this query SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count FROM single GROUP BY user_id, stage table engine is innodb, plus I haven't use any indexes except one key ...
0
votes
1answer
462 views

Postgresql: Return Unique Rows

I'm using postgresql and I have a large database. A simplified version of the datastructure for my table looks like this: id (primary unique key), data, userID 1, foo, 4 2, foobar, 4 3, lala, 5 4, ...
2
votes
2answers
655 views

Query design for tracking order status

I have the following tables in MySQL `orders` ( `orderID` int(10) unsigned NOT NULL auto_increment, `agentID` int(10) unsigned NOT NULL, `order_date` datetime NOT NULL, `senderID` int(10) ...
1
vote
1answer
311 views

MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

Need MySQL query Help. Please find schema below: Table1 PK_id Table1_Title Table1_Description 1 Test1 This is Test1 2 Test2 This is Test2 Table2 FK_id ...
1
vote
2answers
3k views

Oracle insert max from two date

I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like Insert into MYTABLE (DATE_INSERT) VALUE ...
0
votes
0answers
144 views

Performance Optimising MySQL query

I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5) I was wondering if there is any way to enhance this query so ...
1
vote
1answer
229 views

Mysql query - find out 1000th row from a table for each user

Lets say I have a table where we log activities of user, I need to find out created_at of the 1000th activity of each user.. Following query works fine for one user.. select `created_at` from ...
0
votes
1answer
2k views

Does TRUNCATE TABLE affects isolation level in Sql Server?

As I am working on the project which has SQL SERVER as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project.... As I have to make some ...
1
vote
1answer
138 views

Does a transaction cover the subroutines?

When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the ...
5
votes
1answer
417 views

Logical reads amount difference with almost identical indexes

I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server. The mentioned query is quite simple, ...
1
vote
3answers
119 views

Getting the exact edited data from a column in SQL Server

I have two Tables: Articles(artID, artContents, artPublishDate, artCategoryID, publisherID). ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, ...
1
vote
1answer
2k views

Installing Full Text Search on SQL Server 2008 R2 Express

I can't for the life of me install full text search on my SQL Server 2008 R2 instance. Every installer I have tried shows me this: I've tried: SQLEXPRADV_x64_ENU.exe SQLEXPRWT_x64_ENU.exe ...
0
votes
1answer
214 views

Changing data type from DATE to CHAR(10) with a query

I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query. I ...
0
votes
1answer
556 views

Permission Denied Copy [closed]

I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres. I am using postgres 8.4 on MAC OS. copy tname from '/Users/Shared/folder/file.cxv' ...
2
votes
1answer
96 views

Synchronize Queries Retrieving Sessions And Locks From DMVs

I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" ...
4
votes
2answers
4k views

SQL as a Storage Definition Language (SDL)

In my Databases lecture, the teacher mentioned that SQL is not just a data definition language (DDL), data manipulation language (DML) and view definition language (VDL), but also a storage definition ...
4
votes
1answer
4k views

Indexing strategy when using the between operator SQL Server 2008

I have a large table ~25 million rows with the structure CREATE TABLE [dbo].[rx]( [pat_id] [int] NOT NULL, [fill_Date] [date] NOT NULL, [script_End_Date] AS ...
0
votes
1answer
134 views

Query taking long time

My query is taking around 540 seconds to get result. Please help me to rewrite it. Query: SELECT pd.domain, fd.passkeyid FROM portfolio.domains AS pd, fabulousdomains.domains AS fd WHERE ...
0
votes
1answer
1k views

Delete with subquery with no unique columns

I have a table LIKES (ID1, ID2). Both id1 and id2 are non unique columns. Database is SQLite delete from LIKES where ID1 = 10 /// Wrong there more then one record with ID1=10 delete from LIKES ...
3
votes
2answers
2k views

DB2 error - SQL Statement too long or complex

Getting this error in a DB2 production system: ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0101 - SQL statement too long or complex. The statement has a large number of OR ...
8
votes
1answer
565 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
1
vote
1answer
615 views

Struggling with complicated date query

So I have been tasked to write a query involving dates for a contract management system we have. The database stores the date the contract's primary terms expired, the frequency that the contract ...
7
votes
1answer
1k views

selecting more fields causes catastrophic failure

I have a connection to a linked server from sql server 2012 to pervasive sql. When I do select field1, field2, field3 from mytable everything works! However, when I do select field1, field2, field3, ...
2
votes
1answer
92 views

Best Practices. Do you append to the existing backup setup when doing a Transaction Log backup?

When doing transaction log backups, is it best practice to just keep appending to the same file or creating a new .trn file for each backup? Thanks.
4
votes
3answers
13k views

Detecting SQL Server Utilization with a query

My current project will send queries to an sql server constantly and It may use 100% of the memory or CPU. How can I check if the server is nearing full utilization in a stored procedure so that I ...
0
votes
1answer
4k views

Copy files using script to a network share

Is there a simple script I can use to copy files in a particular folder to a network share? This is for use with SQL Server Agent so I can run it as a step after backups. Thanks
2
votes
3answers
106 views

SQL Update Efficiency and Blocking Concern

Say I have some SQL similar to the below, which is an update with many joins. The number of rows actually being updated may be 1000's or potentially only a few. The actual join takes a long time as ...
5
votes
2answers
233 views

MySQL | Complex (for me) SQL Statement, how to?

I have a table that contains posts and another table that contains meta options for each post in first table. The meta options table is a key value pare table. Lets say I have the posts tables that ...
0
votes
3answers
613 views

Transaction in SQL Server

Consider a scenario, when 2 Stored Procedure called inside a Stored procedure like follows. Note: All 3 SP’s are implemented with TRANSACTION. CREATE PROC [dbo].[SP3] AS BEGIN BEGIN TRAN EXEC ...
2
votes
1answer
639 views

Any issue in referencing a unique index from a foreign key table?

Is there any concern in referencing a unique index from a foreign key table? Using SQL Server 2008 R2 Please advise thanks