Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
20
votes
1 answer

Can the 'returning' clause return source columns that are not inserted?

Here's a minimal example of my real-world problem: create table t(id serial primary key, rnd double precision); of course you can return the inserted columns with a returning clause: with w as (insert into t(rnd) values(random()) returning…
Jack Douglas
  • 39,869
  • 15
  • 101
  • 176
20
votes
3 answers

What is the difference between COUNT(*) and COUNT(*) OVER()

Take the following code example: SELECT MaritalStatus, COUNT(*) AS CountResult COUNT(*) OVER() AS CountOverResult FROM (schema).(table) GROUP BY Marital Status COUNT(*) Returns all rows ignoring nulls right? What does…
David Folksman
  • 769
  • 3
  • 8
  • 13
20
votes
2 answers

The value could not be converted because of a potential loss of data

Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of this problem on StackOverflow and DBA Stack…
Mathias Lykkegaard Lorenzen
  • 569
  • 2
  • 5
  • 13
20
votes
1 answer

SQL Server nvarchar(max) vs nvarchar(n) affects performance

This is SQL Server 2008 R2 SP2. I have 2 tables. Both are identical (data and indexing), except the first table has a VALUE column as nvarchar(max) and the second has the same column as nvarchar(800). This column is included in a non-clustered…
Brian Bohl
  • 303
  • 1
  • 2
  • 6
20
votes
6 answers

How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?

I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I'm using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the custom/compress format (.backup) and UTF8 encoding. The…
pedrosanta
  • 3,343
  • 3
  • 16
  • 7
20
votes
4 answers

Execution plan vs STATISTICS IO order

SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON? The following query: SET STATISTICS IO ON; SELECT * FROM Sales.SalesOrderHeader AS soh …
anon
20
votes
2 answers

How to do incremental backup every hour in Postgres?

Trying to do an hourly incremental backup of a single Postgres server (Win7 64). I have the following setup in postgresql.conf: max_wal_senders = 2 wal_level = archive archive_mode = on archive_command = 'copy "%p"…
Neil McGuigan
  • 8,423
  • 4
  • 39
  • 56
20
votes
2 answers

Does MySQL cache queries?

I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice with a short interval in between, the query only takes…
user24821
20
votes
1 answer

Performance difference between MySQL and PostgreSQL for the same schema/queries

I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS. I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests comparing with MySQL too. We observe that MySQL is twice…
Javier Valencia
  • 303
  • 1
  • 3
  • 7
20
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
  • 365
  • 2
  • 3
  • 8
20
votes
6 answers

What is SQL Server "Denali"? What's new?

I found a new title called SQL Server "Denali" in the drop down list on MSDN, but I didn't find much information about it: What's New (this documentation is for preview only as I see) Top 9 New Features of SQL Server "Denali" Does anyone have more…
garik
  • 6,722
  • 10
  • 43
  • 56
20
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
  • 506
  • 2
  • 10
  • 24
20
votes
5 answers

Optimal drive configuration for SQL Server 2008R2

I have a fairly busy database server running SQL Server 2008 R2 that has the following setup: SATA RAID 1 (2 Drives) - OS / Programs SAS RAID 10 (4 Drives) - Sql Database Files (data and logs) SAS RAID 1 (2 Drives) - TempDB (data and…
DanP
  • 255
  • 1
  • 2
  • 9
20
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
  • 807
  • 3
  • 9
  • 13
20
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
  • 303
  • 2
  • 6
1 2 3
…
99 100