Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
62
votes
4 answers

SQL Server: How to track progress of CREATE INDEX command?

SQL Server 2014, Std Ed I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help. I currently use the method below, which at least shows me movement…
Jonesome Reinstate Monica
  • 3,379
  • 10
  • 37
  • 55
62
votes
5 answers

How should I index a UUID in Postgres?

I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless there's already something built-in that it uses…
sudo
  • 1,011
  • 1
  • 10
  • 18
61
votes
6 answers

Why use WHERE 1 or WHERE 1=1?

Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I've seen a WHERE 1 clause being used in many places, even where other conditions are not present. Why is this done? Are there specific benefits to…
ursitesion
  • 2,071
  • 7
  • 32
  • 45
61
votes
3 answers

How to make sqlplus output appear in one line?

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read. What I'd rather like is either a horizontal scroll bar to appear or somehow send the output to less I run following statements in SQLPlus…
Kshitiz Sharma
  • 3,237
  • 9
  • 31
  • 35
61
votes
1 answer

How to get a working and complete PostgreSQL DB backup and test

I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users. From what I've read, and it could be wrong, finding a good PostgreSQL blog has…
Ali Razeghi - AWS
  • 7,518
  • 1
  • 25
  • 38
60
votes
3 answers

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when using it. However, ISNULL seems easier to read…
Richard
  • 6,393
  • 8
  • 42
  • 62
60
votes
6 answers

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure you don't run out of room?
BenV
  • 4,893
  • 7
  • 39
  • 38
60
votes
3 answers

Is it possible to have passwords configured per database or per host in .my.cnf

I have the following in my ~/.my.cnf [client] password="somepass" but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different passwords for different things so I don't have to…
xenoterracide
  • 2,851
  • 5
  • 30
  • 33
60
votes
2 answers

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); But I get this error: ERROR: data type integer has no…
Ian Timothy
  • 885
  • 1
  • 7
  • 10
60
votes
5 answers

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-type Datetimeoffset(0). I then UPDATED this column…
PapillonUK
  • 703
  • 1
  • 5
  • 5
60
votes
4 answers

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
Kilhoffer
60
votes
2 answers

Are WHERE clauses applied in the order they are written?

I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query. select 1 from workdays day where day.date_day >= '2014-10-01' and day.date_day <=…
Jorge Vega Sánchez
  • 973
  • 3
  • 15
  • 22
59
votes
7 answers

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the JSON object? I have read about ROW, but it returns a…
Yanick Rochon
  • 1,591
  • 4
  • 19
  • 28
59
votes
5 answers

I can't connect to my servers SQL database via an IP Address

I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed. I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS. However when we come to connecting through any software or…
Shaun
  • 693
  • 1
  • 6
  • 6
59
votes
6 answers

Getting last modification date of a PostgreSQL database table

I'm trying to get when my table was modified by checking its file modification date as it is described in this answer. But the result is not always correct. The file modification date updates in several minute after I update my table. Is it correct…
hank
  • 889
  • 1
  • 8
  • 11
1 2 3
…
99 100