36
votes
3answers
59k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
35
votes
7answers
45k views

How do I completely disable MySQL replication

I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?
35
votes
4answers
60k views

How large should be mysql innodb_buffer_pool_size?

I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
35
votes
6answers
32k views

What are the main causes of deadlocks and can they be prevented?

Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was ...
35
votes
2answers
86k views

How to the get current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
35
votes
11answers
4k views

How can I track database dependencies?

As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods ...
33
votes
8answers
8k views

Is select * still a big no-no on SQL Server 2012?

Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit. Is this still the case in later versions of SQL ...
33
votes
6answers
1k views

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
33
votes
6answers
6k views

Why do sequential GUID keys perform faster than sequential INT keys in my test case?

After asking this question comparing sequential and non-sequential GUIDs, I tried to compare the INSERT performance on 1) a table with a GUID primary key initialized sequentially with ...
33
votes
2answers
40k views

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
32
votes
3answers
58k views

How do I list all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. It's a 3rd party product, they are slow on answering some questions, and I know the data is lying inside ...
32
votes
3answers
2k views

Is the concept of a clustered index in a DB design sensical when using SSDs?

When designing a SQL server data schema and the subsequent queries, sprocs, views, etc. does the notion of a clustered index and order of data on disk make any sense to consider for DB designs made ...
31
votes
7answers
9k views

Why should I use Visual Studio 2010 over SSMS for my database development?

Visual Studio 2010 introduces database projects and a whole array of related features that supposedly facilitate database development. I've used SQL Server Management Studio (SSMS) for many years to ...
31
votes
2answers
8k views

NULL or NOT NULL by default?

In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter? I know in some DBMSs they ...
30
votes
9answers
66k views

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
30
votes
5answers
17k views

Best practices on common person fields (Name, email, address, gender etc…)

What are the most common best practices on length and data type on common fields like: First Name Last Name Address Email Sex State City Country Phone Number etc....
30
votes
3answers
2k views

Performance of Non Clustered Indexes on Heaps vs Clustered Indexes

This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap ...
30
votes
4answers
23k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
30
votes
4answers
3k views

On a dedicated database server, how much memory to reserve for the OS?

Assuming you have a dedicated server explicitly for database functions -- how much memory should you reserve for the operating system? I realize this will probably vary somewhat depending on the ...
30
votes
7answers
13k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
30
votes
2answers
85 views

SQL Server unpredictable select results (dbms error?)

Below is simple example, which returns strange results, that are unpredictable and we cannot explain it in our team. Are we doing something wrong or is it SQL Server error? After some investigation ...
29
votes
5answers
7k views

What database technologies do big search engines use? [closed]

Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data? What sort of database or technologies do they employ for this? It takes few milliseconds, ...
29
votes
3answers
2k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
29
votes
6answers
28k views

About single threaded versus multithreaded databases performance

H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded. My question is: when does a multi-thread database become more interesting than an ...
29
votes
6answers
3k views

The smallest backup possible … with SQL Server

Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever. We don't mind if our backup process takes a bit longer; as it stands ...
29
votes
7answers
18k views

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * ...
29
votes
5answers
5k views

When is the right time to use MariaDB instead of MySQL, and Why?

What we earn and what we lost with this migration? What should I expect as drawbacks after the migration? Is it really unnecessary to change the applications in any situation?
28
votes
3answers
36k views

How do I get back some deleted records?

I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side. Is there any way to get back these ...
28
votes
5answers
5k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem) After working with ...
28
votes
7answers
891 views

How should deletions be handled in the database?

I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are ...
28
votes
5answers
9k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
28
votes
5answers
2k views

Is it OK to keep a value which updates in a table?

We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc. Up until now we had a Card entity which has a collection of Account entity, ...
28
votes
5answers
13k views

Why not use a table instead of a materialized view?

I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
28
votes
1answer
5k views

What's the point of WHERE 1=2 for SELECT INTO table query

If we want to create a new table from an existing one in SQL Server we can do the following SELECT * into Table2 from Table1 WHERE 1=2 Whats the point of where clause? I tried it without the where ...
27
votes
7answers
6k views

What is the difference between a “record” and a “row” in SQL Server?

There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate. So how do we differentiate between these related terms and how we ...
27
votes
7answers
13k views

Is it better to store images in a BLOB or just the URL? [duplicate]

Possible Duplicate: Files - in the database or not? I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch ...
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 ...
27
votes
8answers
2k views

Security implications of restoring a backup from an unknown source?

Scenario: You're handed a database backup and told to restore it to a server (that's already hosting other databases), but are given no useful information about what the backup contains or whether the ...
27
votes
3answers
50k views

Which is faster, InnoDB or MyISAM?

How can MyISAM be "faster" than InnoDB if MyISAM needs to do disk reads for the data? InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
27
votes
5answers
6k views

Downgrading from SQL Server 2008 to 2005

Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
27
votes
3answers
20k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
27
votes
7answers
581 views

What are the minimum matching criteria recommended for reliable demographic based patient matching?

When matching patients based on demographic data are there any recommendations on what fields should match for the patient to be the "Same Patient"? I know the algorithms will be different for ...
27
votes
9answers
11k views

Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?

While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For ...
27
votes
6answers
19k views

In what data type should I store an email address in database?

I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation ...
27
votes
1answer
2k views

In SQL Server, what is the purpose of grouping stored procedures?

One of the most perplexing issues with which I've had to deal has to do with Stored Procedure groups. Given a stored procedure, usp_DoSomethingAwesome, I can create that proc in another group by ...
26
votes
3answers
27k views

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I ...
26
votes
4answers
3k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
26
votes
3answers
16k views

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
26
votes
5answers
9k views

Why does ORDER BY not belong in a View?

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with) I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
26
votes
3answers
19k views

Most idiomatic way to implement UPSERT in Postgresql nowadays

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 ...

15 30 50 per page