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)

-1
votes
0answers
23 views

inserting data based on different conditons - SQLServer

I have a table TN with 3 columns: Name School id maul 800 10001 maul 800 10002 paul 800 20000 ...
0
votes
1answer
28 views

Best way to add Foreign Key to massive table?

Say I have 4 tables: arrival (~100 million rows), departure (~100 million rows), airport (~30 million rows), airplane (small). arrival and departure each have 2 ForeignKeys: one to airport, one to ...
-2
votes
1answer
36 views

SQL statement that return Component in each Order [on hold]

I have a product detail table that contain the component used in each product Product | Component ---------------------- Product1 | A Product2 | A Product3 | B Product1 | C Product2 | B ...
1
vote
0answers
22 views

Sql Server 2012 Change Data Capture weird behaviour

In my Sql Server 2012 I have the following table in a database CREATE TABLE [dbo].[HumanResource]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [IsHired] [bit] ...
0
votes
3answers
23 views

Column Concatenation

Is it possible to concatenate the rows from two/more different columns into one new column ? Example: C1 C2 C3 ---- + ---- => ---- a d a b e b c f c ...
2
votes
2answers
94 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
1
vote
0answers
44 views

Query does not use index (according to EXPLAIN SELECT)

Can anyone give me an indication, why this query does not use the Key on the table 'gtg'): ...
2
votes
0answers
34 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
1answer
293 views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
0
votes
0answers
17 views

How to populate Oracle 10g tables related via foreign key?

As the title says, how do i fill random data in tables, which have foreign key references within each other. I want to then practice all the SQL commands, like, SELECT commands, JOINS, SET FUNCTIONS ...
2
votes
2answers
39 views

Need separate columns that calculate percentage

I have the following table: NAME Alex Bob Bob Tim Alex Roger I need to produce the following: Name Count Percentage Alex 2 33% Bob 2 33% Roger 1 16.6% Tim 1 ...
1
vote
0answers
36 views

Low batch Batchrequests/sec

I am running this query to do massive deletion in my database. I am looking at Batchrequests/sec and it is quite low (like 15% of what my server can handle) Is there any way to rewrite this query to ...
2
votes
3answers
127 views

Can I use PHP function strtotime() in SQL queries?

I have this table, and I need to use a PHP function in the query. id time lastTime 1 08:02 2 08:04 3 08:30 I am using this query with the strtotime(), and it's not working: ...
1
vote
3answers
49 views

Removing chat history for a particular user from database

I'm currently developing a web chat application using SignleR and SQL server this is my database schema so far I've added an option where the user can Clear Chat history for a particular ...
-2
votes
0answers
25 views

Database Timeout expiring when tables fill up

Once my database starts to fill up with the tables being used, my stored procedures are taking longer to execute. If I truncate the data and execute again, execution time usually takes 3 minutes. Once ...
1
vote
1answer
37 views

PostGIS vs. SQL Server for GIS data

So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue ...
0
votes
0answers
36 views

How I can monitor SQL Server 2012 databases to determine the security privileges that are called by different processes

Is there any tool that can identify the SQL privileges or roles that are being called on query by query basis. We need to know which ones are being used at each time on a Microsoft SQL Server 2012.
0
votes
0answers
26 views

What is the difference between Row and Table Expressions in MySQL

Can anybody explain with example what is a Row and Table expression and what is the difference between them. Any example for clarification will be appreciated.
1
vote
1answer
32 views

Upsert statement never completes, infinite loop?

Edit: The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a ...
0
votes
1answer
41 views

JOIN on 3 tables being slow

everyone. Can't figure out what indices are missing or how to rephrase my query. I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is ...
0
votes
1answer
21 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
0
votes
0answers
41 views

table check constraint, greater than > not accepting

CONSTRAINT CK_BABY_YOUTH_CHILD CHECK (case when Passenger_Type='Baby' then Reference_ID >=0) This is my table check constraint, i have no idea why sql server is not accepting ">". Please ...
0
votes
1answer
40 views

What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

What's best performance wise, two separate UPDATEs: UPDATE table1 SET enabled = 0 WHERE code NOT IN ( SELECT id FROM table2 ); UPDATE table1 SET enabled = 1 WHERE ...
0
votes
2answers
54 views

Dynamic Table partitioning on a daily basis

I have a SQL Server database which contains two tables -- Acks and Logs. These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved ...
1
vote
1answer
35 views

Should I drop this column or is there a query that can achieve a results table without duplicate columns?

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information. ...
0
votes
3answers
41 views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
0
votes
1answer
46 views

deleting a table referenced by many foreign keys

I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is ...
0
votes
2answers
50 views

Check Constraint with Case Statement

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of ...
8
votes
2answers
197 views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
0
votes
0answers
55 views

Technical documentation for Replication [closed]

My boss wants me to write a technical paper on replication, I don't know where to start or what to write about. We held a discussion and he talked about: Managed Code (we use VB.NET) Triggers ...
1
vote
0answers
56 views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
1
vote
2answers
50 views

Users Characteristics

I'm really battling an issue where I have a Users table that has a growing number of user characteristics (regligion, smoking preferences, etc). The strategy I've used thus far has been to add a ...
0
votes
0answers
42 views

Double subquery is very slow, but independently they are fine

I have this query: SELECT mac, pid, node_category.name, status, computername, user_agent, detect_date, regdate, unregdate, last_dhcp, node.notes, (SELECT ip FROM iplog WHERE iplog.mac = node.mac ...
1
vote
3answers
34 views

Query to return output column names and data types of a query, table or view

Is there a PostgreSQL query or command that returns the field names and field types of a query, table or view? E.g., a solution if applied to simple SELECT query like SELECT * from person should ...
1
vote
1answer
31 views

Break AG Cluster

I have a 2 node cluster setup and we use AlwaysOn HA with it. Currently we have two AG's and each one has three separate databases in each AG. We want to break the cluster and have three databases ...
1
vote
0answers
43 views

Relational or non-relational database for a system to pass objects on?

hopefully this is the right place to ask this kind of question. I am working on a system that focuses on helping people give away things that they don't use anymore. So, an object typically has the ...
0
votes
2answers
29 views

How to implement a dynamic string into a (prepared) sql statement?

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used ...
0
votes
1answer
35 views

The materialized view is already optimal and cannot be tuned any further

I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error: Error report: ORA-13600: error encountered in Advisor QSM-03116: The ...
-3
votes
0answers
14 views

what is inventory cleansing and data cleaning? [closed]

Can anyone tell me the complete process of inventory cleansing and data cleansing in a data base?
2
votes
1answer
48 views

Fastest way to change column type from varchar2 to number on large table in Oracle

I have a table which contains a VARCHAR2 column that must me changed into NUMBER. The table contains more than 30 millions rows. The table definition : CREATE TABLE EFF ( MOD ...
0
votes
1answer
35 views

Frustrating SQL Server and VS Express 2013 Issue

I recently reinstalled Windows and am trying to set up SQL Server again with an existing database. I've tried installing SQL Server 2012 and 2014, and continuously get the same error. The error says ...
0
votes
2answers
26 views

Adding DATETIME() and Status column with xp_FileExist result

I'm trying to add a two columns in addition to the results of xp_FileExist. Here's my T-SQL: DECLARE @path varchar (100) DECLARE @file varchar (100) SET @file = '<location>\*.txt' SET @path = ...
0
votes
1answer
53 views

CROSS APPLY versus INNER JOIN

I was reading a lot about cross apply versus inner join recently. I tried writing some query and they both work kinda same, execution seem to be same aswell. Maybe someone with a deeper knowledge ...
1
vote
1answer
24 views

mysqldump - select table in database only if it contains a specific string

(I'm new to Database Administrators, and am not a Database Administrator. However I originally asked this question on Stack Overflow (here) but didn't get any answers. Sorry if this is not the right ...
1
vote
0answers
58 views

SQL select top X rows having sum limit

I am using SQL Server 2012, I have a table with a format such as : MyOrders : ID -> PK int Cost -> float Category -> int I want to get the following : Input : category (lets say 2). ...
0
votes
0answers
60 views

Stored Procedure's Commented line causing issues [closed]

We have a stored procedure which works as the sample mentioned below: CREATE PROCEDURE [dbo].[sdp_Test_Update] ( @ID int OUTPUT, @File_ID int, ...
1
vote
3answers
40 views

Can set unique constraint in table have data not unique SQL Server? [duplicate]

I have a table with has many rows, I want to set unique constraint for this table, but the column that must be unique, has data is not unique. Is any way to set unique constraint for this table for ...
2
votes
2answers
78 views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
0
votes
0answers
6 views

Using range types (with GiST index)

Say I have 2 tables with a PostgreSQL range column: CREATE TABLE events ( id serial, during tsrange ); and CREATE TABLE storms ( id serial, during ...
0
votes
1answer
20 views

Row-level subquery caching?

I have a query that uses a CTE which contains millions of rows. I plan to call this query many times, and the CTE returned each time will have most of the same rows. Is it possible for me to cache the ...