Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
41
votes
1 answer

Changing mysql table comment

I know that mysql table comment can be defined at the creation with: create table (...)comment='table_comment'; And you can display comments by: show table status where name='table_name'; How do you change (alter?) table comment after it's been…
v14t
  • 513
  • 1
  • 4
  • 6
41
votes
1 answer

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references through a FK to the second table's primary key. On…
stUrb
  • 747
  • 2
  • 9
  • 15
41
votes
3 answers

difference in execution plans on UAT and PROD server

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs). Both UAT and PROD are having exactly data and indexes. QUERY: set statistics io on; set statistics time…
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
41
votes
4 answers

How do I find out how far along my PostgreSQL query is?

I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize). I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or…
Mark Elliot
  • 511
  • 1
  • 4
  • 5
41
votes
3 answers

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally installed it. I have googled and found a post…
AKIWEB
  • 605
  • 2
  • 6
  • 8
41
votes
2 answers

Using SELECT in the WHERE clause of another SELECT

I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, it happens that I call something like 40 select…
Stephane Rolland
  • 8,641
  • 10
  • 32
  • 40
41
votes
5 answers

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST VALUES (2); Using my favorite JDBC query tool…
DaveyBob
  • 551
  • 2
  • 5
  • 4
41
votes
5 answers

How to select distinct for one column and any in another column?

I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column. For example, consider the following table with two columns, key and value: key value === ===== one test one …
WilliamKF
  • 521
  • 2
  • 6
  • 6
41
votes
10 answers

"lock request time out period exceeded" Error When Trying to See DB Hierarchies

I'm having problems with a database. I can run basic queries, albeit much slower than normal. When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I get lock request time out period exceeded. My…
Lloyd Banks
  • 972
  • 3
  • 10
  • 15
41
votes
2 answers

Is MySql's LAST_INSERT_ID() function guaranteed to be correct?

When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row. As many Microsoft SQL Server devs and admins no doubt are aware…
Kev
  • 1,336
  • 5
  • 14
  • 24
41
votes
4 answers

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an element of good style. That is clear. What is not…
Nick Chammas
  • 14,670
  • 17
  • 75
  • 121
41
votes
2 answers

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific differences will be between Always Encrypted & the…
RoastBeast
  • 679
  • 2
  • 7
  • 13
41
votes
3 answers

Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert at all to find out immediately, whether these…
shabunc
  • 747
  • 2
  • 6
  • 10
41
votes
4 answers

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post…
Rocky Singh
41
votes
6 answers

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ON dc.DOCUMENT_ID = cj.DOCUMENT_ID WHERE…
Kenneth Fisher
  • 24,127
  • 12
  • 61
  • 114
1 2 3
…
99 100