Most Popular
1500 questions
21
votes
5 answers
Getting SELECT to return a constant value even if zero rows match
Consider this select statement:
SELECT *,
1 AS query_id
FROM players
WHERE username='foobar';
It returns the column query_id with value 1 along with a player's other columns.
How would one make the above SQL return at least the query_id…
Nathanael Weiss
- 375
- 2
- 3
- 8
21
votes
2 answers
On duplicate key do nothing
I am inserting into the following table using LuaSQL with PtokaX API.
CREATE TABLE `requests` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`ctg` VARCHAR(15) NOT NULL,
`msg` VARCHAR(250) NOT NULL,
`nick` VARCHAR(32) NOT NULL,
…
hjpotter92
- 516
- 2
- 10
- 24
21
votes
2 answers
Is it possible to get execution call stack in a trigger?
I have 10 stored procedures and each of them does INSERTs into one tableX.
Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ?
Thank you.
garik
- 6,782
- 10
- 44
- 56
21
votes
6 answers
Find "n" consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ASSIGNED
1 000004 FREE
1 000005 FREE
1 …
boobiq
- 827
- 3
- 9
- 13
21
votes
3 answers
Column name naming conventions and best practices
I would like some expert opinion on best practices when it comes to column naming.
The background is that according to Wikipedia, the following syntax,
SELECT ... FROM Employees JOIN Timesheets USING (EmployeeID);
is more efficient than
SELECT ...…
Kerrek SB
- 313
- 2
- 6
21
votes
2 answers
What are the differences between leaf and non-leaf pages?
I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page Allocations. I really don't know what it means, or if one…
meltdownmonk
- 377
- 1
- 3
- 8
21
votes
3 answers
SQL Server Restore a SINGLE table from a backup
on SQL Server 2005, we do a weekly full backup with nightly incremental backups.
I want to know if it is possible to restore a single table from a backup, either to the source database or a different one.
I can not find any clear answer online.…
GWR
- 2,847
- 9
- 35
- 42
21
votes
4 answers
Good, Bad or Indifferent: WHERE 1=1
Given this question on reddit, I cleaned up the query to point out where the issue was in the query. I use comma first and WHERE 1=1 to make modifying queries easier, so my queries generally end up like this:
SELECT
C.CompanyName
…
WernerCD
- 1,245
- 3
- 11
- 19
21
votes
4 answers
Can I change the SID of an Oracle database?
The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Is there an easy & quick way to change the SIDs of the test databases on the server?
Dropping & recreating of the database is an option for me. But I'm looking for…
bernd_k
- 12,369
- 24
- 79
- 111
21
votes
1 answer
Why is my ORDER BY in STRING_AGG not always working?
I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.
CREATE TABLE HashTable(
RecordID VARCHAR(255),
GroupIdentifier VARCHAR(255),
Hash VARCHAR (255),
…
Rebecca
- 321
- 1
- 2
- 4
21
votes
3 answers
PostgreSQL: Can I do pg_start_backup() on a live, running db under load?
Our established replication has broken ("requested WAL segment has already been removed" during downtime)
We cannot easily stop the master again.
Can we do
pg_start_backup(),
rsync ${PGDATA}/ master to slave,
pg_stop_backup()
... while the…
Daniel
- 313
- 1
- 2
- 5
21
votes
2 answers
Duplicate an Oracle database user
We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the database and grab information from it.
We have…
Gommel
- 213
- 1
- 2
- 4
21
votes
3 answers
Can I use SQL Server Management Studio 2012 on a 2008 server?
My school is using SQL Server 2008. Last time I used it I only installed (at home) SQL Server Management Studio 2008 part, and used VPN to connect to school's server.
Now I want to install SQL Server Management Studio again, but this time the 2012…
user1121487
21
votes
3 answers
Why is an OR statement slower than UNION?
Database version: PostgreSQL 12.6
I have a table with 600,000 records.
The table has the columns:
name (varchar)
location_type (int) enum values: (1,2,3)
ancestry (varchar)
Indexes:
ancestry (btree)
The ancestry column is a way to build a tree…
Mohamed Hussein
- 321
- 2
- 9
21
votes
1 answer
Anyone use SUMA, trace flag 8048, or trace flag 8015?
Recently included SQL Server startup Trace Flag 8048 to resolve a serious spinlock contention issue in a SQL Server 2008 R2 system.
Interested to hear from others who have found usage cases where performance value was delivered by trace flag 8048…
sql_handle
- 1,001
- 6
- 8