DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

1
vote
1answer
8k views

Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue. issue is: when I wanted to integrate OWASP ...
1
vote
1answer
224 views

Optimistic Paging and separate query for COUNT?

I am using SQL SERVER 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a Total count. There are 2 ways to achieve this. 1) Paging ...
1
vote
1answer
511 views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
1
vote
1answer
66 views

Oracle - Date of Selection on table

how to get last date when someone perform selection on table? Last accessed in terms of select statement only not last updated date
1
vote
4answers
122 views

Backup from Server1 and restore it to Server2?

IF we have two Servers installed with SQL Server 2008 instances, Server1 has three drives C, D and E with 100, 500 and 500 GB respectively and Server2 has three drives C, D and E with 100, 300 and ...
1
vote
1answer
121 views

Get maximum number with combination of columns [closed]

I have table with columns like AutoID, Number, Name, City, State, Country. What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country. ...
1
vote
3answers
147 views

Creating a copy of primary database and attaching the copy database in the same sql instance

I just got into a project in which we have only one SQL Server 2012 copy. We have 12 databases attached to the primary instance of SQL Server 2012. As I don't have SQL Server 2012 or supporting OS, I ...
1
vote
3answers
5k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
1
vote
3answers
2k views

SQL services wont start due to incorrect drive letter after windows restore

SQL services wont start due to incorrect drive letter after windows restore Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services ...
1
vote
2answers
5k views

Sql Server agent can not start

I can not start Sql Server agent to create job. Can anyone help me? . I'm using Windows 7 Ultimate , Sql server 2008 Rc2 See this images:
0
votes
2answers
35 views

Counting unique records across 2 columns and computing a score based on boolean values

I have a table that looks like this in Google Big Query: custID InteractionDate Purchased? Sales 1 20150312 F 0 1 20150312 T 200 1 20150314 ...
0
votes
2answers
70 views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
0
votes
0answers
37 views

Auditing SQL Server 2012 [closed]

I work for the State of California. I am working on a project setting up a Data Warehouse sharing data between 3 state agencies. The Data Warehouse will be encrypted. I would like to know what are the ...
0
votes
2answers
42 views

MySQL Case problem

I am trying to run a SQL code (I use MySQL). What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The ...
0
votes
1answer
134 views

Splitting Columns

I want to ask a complicated question and I think its advanced a little. I have a table has this design, its an attendance system. I need to split the time if the attendance count per day was not 2: ...
0
votes
1answer
232 views

How do I count the number of different child id's belonging to a parent record?

For instance I have two tables in a music database. Table album and table track. Each track record has (amongst other things), an album_id and an artist_id Each album record has a unique album_id ...
0
votes
2answers
99 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
0
votes
1answer
205 views

How to rollback last executed sql script fired as sysdba

I have fired one sql script as sysdba which I have to fire using other oracle user. I have already created some user and each application running on each oracle user. By mistake I had fired a script ...
0
votes
3answers
4k views

How to select the first 10 records for each table in the same database using sql

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select * from {table_name} So, is it possible to display the first 10 records for each table ...
-1
votes
1answer
133 views

The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?)

The "Log" folder SIZE in the SQL server root directory (X:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log) becomes too big , ~80 GB. When I checked it, I see that there are a lot of ...
-1
votes
1answer
195 views

Validate each parent intermediary is also a parent to itself

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT if table has any. I need to do is first of all get all parents ...
-5
votes
1answer
8k views

how to query multiple row data in single column in oracle?

i have a table in which the values are id value ---------------- 1 10 1 20 1 30 2 33 2 34 I want the output given below using sql: id value ...
13
votes
5answers
64k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
22
votes
3answers
36k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
21
votes
4answers
7k views

SQL Server “empty table” is slow after deleting all (12 million) records?

I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
13
votes
3answers
8k views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
22
votes
2answers
8k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
11
votes
2answers
904 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) ...
11
votes
5answers
10k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
10
votes
3answers
6k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
24
votes
3answers
10k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
7
votes
3answers
3k views

CHECKPOINT or COMMIT writes to disk?

Let's say, for SQLServer2008R2 and higher, with full recovery mode databases. I always thought : When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM. ...
7
votes
4answers
53k views

Combine column from multiple rows into single row

I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something ...
7
votes
2answers
29k views

Returning a result set with multiple rows based on max date

I have a child table that is something like this: [Cust Date Table] | Customer ID | Some Date | Balance | +-------------+------------+---------+ | 1 | 2012-04-30 | 20.00 | | 1 ...
27
votes
8answers
24k views

What is the difference between an INNER JOIN and an OUTER JOIN ?

I am new to SQL and wanted to know what is the difference between those two JOIN types? SELECT * FROM user u INNER JOIN telephone t ON t.user_id = u.id SELECT * FROM user u LEFT OUTER JOIN ...
8
votes
3answers
3k views

Is there a standard implementation of a Users Database?

I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
7
votes
2answers
6k views

Database behind a Multilingual User Interface

This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another: Suggestion for database ...
18
votes
5answers
8k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
4
votes
2answers
509 views

Open source and commercial tools to build dynamic queries of fact tables in star schema [closed]

What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)? So for example, if we have X fact tables, and Y dimension tables, and Z ...
2
votes
2answers
2k views

MySQL Replication without stopping master

I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then ...
16
votes
2answers
43k views

Sql Server database in recovery: How to stop, Take ofline or delete?

I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so. I need to either stop the recovery, or kill it in some way. I dont care one ...
11
votes
3answers
472 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
11
votes
2answers
8k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
10
votes
2answers
913 views

Optimization: Moving variable declarations to the top of your procedure

While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity. One thing the DBA mentioned was I should ...
8
votes
2answers
949 views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
8
votes
3answers
7k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? ...
8
votes
3answers
2k views

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find ...
7
votes
2answers
6k views

Oracle 11g: performance improvements of inserts

I have a table of 500 millions of rows (and growing) I did the following to improve performance of inserts: On database side: dropped all indexes and constraints disabled logging On application ...
7
votes
1answer
592 views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
6
votes
2answers
4k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...