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

learn more… | top users | synonyms (1)

4
votes
2answers
4k views

Way to do MERGE with update source when target not match?

I am trying to write a stored procedure that will take new values from a temp table, merge them into an actual table, giving me the ids of the inserted rows, and the rows that already exist matching ...
4
votes
2answers
1k views

Is it possible for SQL Server to miss rows on a select * where PK = “value” query

We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment. I have a very strange error in our application and I was ...
5
votes
1answer
326 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
3answers
10k views

How to import Sybase .db file into SQL Server database?

I've been given a Sybase .db file by a client and I need to import that into a new SQL Server database so that I may see the data as well as the tables and their relationships, any ideas on how that ...
1
vote
0answers
55 views

Database Structure Definiton

This is a question more so on the table definition vs actual programming. I have 6 sets of potentially 255 unique fields. I have a potential of getting 40 million records per year of these. For the ...
0
votes
1answer
5k views

How to INNER JOIN and OUTER JOIN within the same table?

I am using PostgreSQL and I have the following tables: Product ID | Name | Price Receipts ID | Month | Quantity | Product_ID (FK) Sales ID | Month | Quantity | Product_ID (FK) I am trying to ...
0
votes
2answers
241 views

Difference Between SELECT and select in oracle sql

The algorithm used to find SQL in the library cache is based on the ASCII values of the characters that make up the statement. Means The SELECT instead of select statement will be parsed again ...
0
votes
2answers
118 views

Query Cache Implementation Details

I am new to DBA stack exchange & this is my first question. Kindly help me in finding a low level documentation for Query Cache module in MySQL since I am planning to work with the source code of ...
1
vote
1answer
23 views

Select the last logged change before a specified time from a change list

I want to get the last change for all issues issue, that was made before a specified end time. I have 3 tables. projects +---+------------+ |ID |project_key | +---+------------+ |1 |bug_tracker | ...
11
votes
1answer
4k views

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details ...
0
votes
1answer
110 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
5
votes
1answer
377 views

Query to find optimal filegroups for objects

I have been assigned a task to create four file groups (one per volume) locate tables so that they are located in a manner that during an ETL process no filegroup is being read and written to at the ...
2
votes
2answers
88 views

I need help optimizing a not that complicated query that returns unexpedcted performance problems

I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the ...
0
votes
2answers
192 views

Database search with multi joins

I have a MySQL database and I want to perform a little bigger search. I have about 10k records in one of the tables and It's expected to grow, but slowly. The biggest problem is that to perform the ...
2
votes
1answer
42 views

Limit balancing results by a column

I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed. I also have a function that returns me the ...
0
votes
1answer
89 views

How can I control the order of a query regarding to inner join

I have 3 tables. They have table_a 5m records table_b 20m records table_c 20m records I'm performing the query below. SELECT * FROM table_a a INNER JOIN table_b b ON a.a_id = b.a_id AND ...
1
vote
3answers
9k views

Select query to retrieve values even if are null

I need some help in a query. Need to retrieve data from 1 table, even if one of the field has null value. Here's an example: Select name, SUM(credit) as credit From expenses Where name like ...
1
vote
2answers
263 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
0
votes
2answers
208 views

Are these linked server queries susceptible to SQL injection?

I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the ...
0
votes
1answer
43 views

Problem with foreign keys

I do ALTER TABLE bdt.auth ADD CONSTRAINT `depositor no.` FOREIGN KEY ( `depositor no.` ) REFERENCES bdt.depositors( `depositor no.` ) ON DELETE CASCADE ...
1
vote
1answer
249 views

How to make the SQL query more efficient?

I use mysql under Linux. My table is about 8000000 rows. The table has index(advertiser_id,activity_date),(activity_date,advertiser_id,activity_type). The table is looks as following: activity_date ...
1
vote
2answers
1k views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
0
votes
1answer
22 views

Creation of a simple field failed in MySql [duplicate]

CREATE TABLE bdt.transactions ( `transaction no` int UNSIGNED NOT NULL AUTO_INCREMENT, data date , by int NOT NULL , hours int NOT NULL , ...
0
votes
1answer
41 views

Output another word instead of the word in the table in a query

Suppose I have a table where I have a field which can have to values: "enabled" and "disabled". Suppose want my query to output "unblocked" instead of "enabled" and "blocked" instead of "disabled". ...
0
votes
1answer
3k views

How to join 3 tables together given 2 many-to-many relationships tables in between?

I have 3 main tables: Person, Publication and Conference and 2 many-to-many link tables in between: person_publication and person_conference I want to join all 3 main tables together (showing me all ...
2
votes
1answer
190 views

Why is this second query more expensive than the first?

While reading Graph Databases book, I came across this excerpt: I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person. May anyone ...
3
votes
2answers
2k views

Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this SELECT * FROM ( SELECT ...
-1
votes
1answer
292 views

database normalization to 3NF [closed]

I am just at a complete lost. I need help with this. There should be 12 tables (or perhaps 11 since one table has just a single attribute) when you are finished with the normalization and merging of ...
0
votes
2answers
60 views

Design for table with multiple links to other table

I am designing a database to keep track of our network equipment. My db has 2 tables: sites & circuits (circuit = cable) & (site = property location) Each circuit runs between two sites, so ...
1
vote
1answer
396 views

Simple query not returning blank records

Very simple example here to a very frustrating treasure hunt for the mystery character. I have a table with a varchar(1) NOT NULL column - everything default - ANSI_PADDING & NULLS are ON for the ...
0
votes
1answer
523 views

Return Parents With Only One Child

In a Parent and Child relationship, I'm trying to return Parents that only have one Child and a where clause applied to the only child firstname like 'tom' t-sql pseudo: parents with only one child ...
0
votes
0answers
173 views

Retrieve SPID For SSRS Request

Is it possible to determine what SPID was created to execute a SSRS report? I am trying to prevent users from running the same report multiple times. So what I was thinking of doing was retrieve the ...
1
vote
2answers
6k views

How to select only first column dynamically on mysql

I have to select only first column. Because of i dont want to select all columns like SELECT * FROM table. But my first columns may vary like following: id article_id user_id product_id page_id etc.. ...
2
votes
1answer
261 views

How to do the complex nested select SQL?

I have two tables, table1 is as following, id is primary key, table2_id is foreign key id date aid table2_id message 1 2014-03-19 16:21:02 121 1 login from xxxx 2 ...
0
votes
1answer
96 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
0answers
98 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
1
vote
2answers
1k views

SQL - query multiplying records returned - Cartesian Product

I am using the following query which is returning a huge number of multiplied records - i.e. a cartesian product: SELECT * FROM specimen, topography_index, morphology, functions WHERE ...
1
vote
1answer
3k views

SQL Datetime / datetimeoffset(2) comparison & implicit cast wierdness

I discovered a problem when inserting data in our database. My insert statement was checking for the existence of data in the WHERE clause to prevent duplicate data being inserted. None was detected, ...
1
vote
2answers
483 views

Building a Trending query

Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every ...
2
votes
1answer
285 views

Select data based on records created_at column

I have two tables Keywords, ProjectReports: http://sqlfiddle.com/#!15/06ae3/1 As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
0
votes
2answers
70 views

Transfer all tables from a schema to other schema

I have two schema. Schema1 and Schema2 How would I transfer all the tables from schema1 to schema2?
7
votes
1answer
722 views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
11
votes
1answer
711 views

Resolving deadlock from 2 tables only related through indexed view

I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it. This is on a production environment running SQL Server ...
1
vote
1answer
233 views

How to make MySQL join one-to-many with lowest value?

I have following database structure Domain has many URL which has many positions which belongs to phrase which belongs to user which belongs to subscription. In subscriptions table I have period ...
-1
votes
1answer
1k views

Divide single column value into multiple column depending upon the bit value of another column [closed]

I have a table containing following fields. Id varchar(5), TranDate date, TranType bit, TranAmount decimal(15,3) My table looks like this: Id TranDate TranType TranAmount ...
1
vote
1answer
125 views

How to update a single record in a table based of foriegn key ID in another table?

I am still very new to sql. I am having troubles updating one table. I am sending information from a web application into Microsoft SQL 2008 database. I have a table called EmployeePermissions that ...
-2
votes
1answer
2k views

Select Top N rows for Employees in a SQL Server table

I would like to select the TOP 2 activities carried out by all employees from an Employees table "Emp" which has a timestamp column. The regular "Select Top 2 * from Emp...." will only fetch the top ...
1
vote
0answers
231 views

Query takes over 200ms [closed]

So, im using symfony2 with doctrine running in local with WAMP. I have to select from my database : latest shop comments with shop & user informations latest liked shops with shop and user ...
5
votes
3answers
1k views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
0
votes
0answers
72 views

Starting up postgres on Ubuntu for the first time

I've installed postgres on ubuntu, created a node and now I'm trying to start up that node as per the instructions: postgres --coordinator -D /usr/local/pgsql/data -p 1281 But I get the following ...