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

learn more… | top users | synonyms (2)

0
votes
1answer
70 views

Can a SQL Server Subscription Database be unsubscribed and used as standalone?

We've got a new server we want to migrate to and I have setup a Publication on the current production server as a Transactional publication. Since the subscribed database is up to date, I want to ...
0
votes
1answer
33 views

dictionary database relations

I have a dictionary database (french > turkish) which has id, word, caseword, direction and descrblob columns. i don't know how it works this direction column. Because turkish words value is 2, and ...
2
votes
0answers
129 views

Domain name change of SQL servers involved in replication

I have a situation where client wants to change their domain name of servers hosting SQL Servers(e.g. from AB to CD). I have a transactional replication with updatable subscription configured with 1 ...
0
votes
0answers
40 views

Static column based on date condition

I have 2 tables : 1. Table 1 2. Table 2 Both have date parameters say DT1(TABLE1) DT2(TABLE2) The requirement is to create a static column in a report as "TEAE" wherein the value of DT1 is greater ...
2
votes
1answer
174 views

Select the max from two max values

I need to find the airport(departure or arrival) that had the most passengers in the last year. The tables are: PASSENGER(id,name,surname) TAKES(id_passenger,id_flight) ...
1
vote
1answer
57 views

Transaction Rollback

I have a purging process that runs to delete old data from table to avoid table growth. My delete statement would be a simple delete based on join something like below Delete FROM TABLE_A A JOIN ...
0
votes
1answer
80 views

How can I transfer Mysql data to another computer and add it to an exist database

I'm trying to transfer Mysql data to another computer and add it to an exist database Here is the condition: 1. Computer A, A has a database named 'Test'; 2. Computer B, B has a database named ...
0
votes
2answers
183 views

sqlite group_concat error while executing query

I have table: CREATE TABLE www ( Col_1 VARCHAR, Col_2 VARCHAR ); select count(*) from www; --1496 And I have error: "Error while executing query: aggregate function are not allowed in ...
-1
votes
1answer
42 views

Issues with procedures, setting multiple values [closed]

I am trying to set multiple values but I am only able to set one value. This code fails: alter procedure modifyvalue @ID int, @flight varchar(6), @destination char(10) as begin update ...
4
votes
3answers
402 views

Make a Copy of a Microsoft SQL Server 2005 database from a Mirror Copy

I have a SQL mirror database in Microsoft SQL Server 2005. I would like to get a copy of it as a new database so I have the ability to read, write, and backup the database. I cannot break the ...
1
vote
0answers
84 views

Implementing a functionality for processing heavy insertion

I use PostgreSQL 9.1. The scenario is: I receive heavy insertion into Table1 (about 100 rows per sec). For each new row, I have to check the previous and next row of the source table if those are ...
-4
votes
2answers
118 views

Inserting data based on different conditons

Though , i put my efforts in making question easy to understand , but i still dnt know whats going wrong . Anyways , im trying to make it more compact I have below table now only : In column number ...
1
vote
1answer
118 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
44 views

SQL statement that return Component in each Order [closed]

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
1answer
325 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
33 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
1answer
564 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 ...
2
votes
1answer
177 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'): ...
3
votes
2answers
20k views

CREATE DATABASE permission denied in database 'master'. unable to get the permission

I am making a database in sql server and it shows an error that "CREATE DATABASE permission denied in database 'master'" I am using the administrator log in itself. What i need to do? Please suggest a ...
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 ...
4
votes
1answer
9k 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

Need separate columns that calculates percentage [duplicate]

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% ...
2
votes
6answers
3k 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
46 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
3k 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: ...
2
votes
3answers
751 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 ...
3
votes
2answers
3k 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
1answer
53 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
125 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
205 views

UPSERT statement never completes, infinite loop?

Based on some of the stackoverflow answers I wrote the following UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would ...
4
votes
2answers
111 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 ...
1
vote
1answer
891 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
65 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
3answers
374 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 ...
1
vote
2answers
6k 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
43 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. ...
1
vote
4answers
2k 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
792 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 ...
16
votes
3answers
10k 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) ...
8
votes
2answers
14k 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
95 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 ...
1
vote
0answers
81 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 ...
6
votes
3answers
38k 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
66 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
86 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
618 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
107 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 ...
2
votes
1answer
4k 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
2answers
79 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 = ...
3
votes
1answer
325 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...