19
votes
2answers
5k views

What is the difference between a connection and a session?

What is the difference between a connection and a session and how they are related?
19
votes
1answer
2k views

Is there a good way to run a trigger for each record in a postgres table?

I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the ...
19
votes
1answer
19k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
19
votes
3answers
6k views

Which PowerShell technique should I use to talk to SQL Server?

I'd ultimately like to use PowerShell to replace the old KornShell scripts we use for SQL instance monitors. I'm having a hard time, though, getting my brain around all the different ways that ...
18
votes
6answers
2k views

Why don't databases create their own indexes automatically?

I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly ...
18
votes
4answers
14k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
18
votes
5answers
795 views

Should Query Tuning be Proactive or Reactive?

As a software developer and an aspiring DBA, I try to encorporate best practices when I design my SQL Server databases (99% of the time my software sits on top of SQL Server). I make the best ...
18
votes
3answers
9k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
18
votes
3answers
3k views

Efficency of stored procedures vs raw queries

I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
18
votes
9answers
38k views

PostgreSQL is running locally but I cannot connect. Why?

Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
18
votes
6answers
809 views

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
18
votes
3answers
725 views

Are RANK() and DENSE_RANK() deterministic or non-deterministic?

According to official Microsoft BOL DENSE_RANK is nondeterministic (RANK()). But according to Ranking Functions by Itzik Ben-Gan "... the RANK() and DENSE_RANK() functions are always deterministic". ...
18
votes
2answers
839 views

Do I need separate indexes for each type of query, or will one multi-column index work?

I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic. My basic understanding is that generally speaking, a single index that ...
18
votes
1answer
970 views

Cast to date is sargable but is it a good idea?

In SQL Server 2008 the date datatype was added. In this Connect item you can see that casting a datetime column to date is sargable and may use an index on the datetime column. select * from T where ...
18
votes
4answers
6k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
18
votes
3answers
26k views

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be ...
18
votes
4answers
9k views

How should I design a relationship table for friendship?

If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods. Here is my observation: If I keep both then I have to ...
18
votes
1answer
335 views

Weird SQL Server instance crash on casting to numeric

While working with C# Entity Framework I noticed a crash of my SQL Server instance. I was able to track it down to this statement: SELECT * FROM dbo.[TestTable] where mpnr in (1099059904, ...
18
votes
3answers
1k views

Index on Persisted Computed column needs key lookup to get columns in the computed expression

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A ...
18
votes
2answers
4k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
18
votes
1answer
5k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
18
votes
3answers
14k views

How do you calculate mysql max_connections variable?

How do you calculate mysql max_connections ? What do you take into consideration ?
18
votes
0answers
2k views

Guide to reading, comparing, and interpretting Execution Plans in SQL Server [closed]

I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others. If you want ...
17
votes
8answers
4k views

Why is it a bad practice to allow everyone to use the sa login?

Even Microsoft discourages the use of SQL Server authentication mode, but our applications require it. I've read that it's a best practice to not let users use the sa login directly, instead using ...
17
votes
9answers
16k views

How does the Oracle DUAL table work?

SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY ...
17
votes
11answers
7k views

Why aren't primary key / foreign key matches used for joins?

As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to ...
17
votes
7answers
615 views

Where can I find some guidance on index strategies?

Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded. As a general rule, which fields should be indexed? Which fields should ...
17
votes
2answers
4k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
17
votes
3answers
2k views

Should every table have a single-field surrogate/artificial primary key?

I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are ...
17
votes
8answers
11k views

How do you version your Oracle database changes?

I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc. Do you use flat files ...
17
votes
2answers
3k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
17
votes
5answers
6k views

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?

I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ Why ...
17
votes
3answers
145k views

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database local_SID = (DESCRIPTION = (ADDRESS ...
17
votes
3answers
5k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
17
votes
5answers
11k views

SQL Server load balancing

What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programatic access. Is this built in or does a solution have to be purchased? I'm trying to ...
17
votes
10answers
36k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
17
votes
4answers
11k views

When should I rebuild indexes?

When should I rebuild the indexes in my relational database (e.g. SQL Server)? Is there a case for rebuilding indexes on a regular basis?
17
votes
4answers
2k views

What are the consequences of setting varchar(8000)?

Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server? On create table, if ...
17
votes
4answers
575 views

What is your workflow for planning a data migration?

So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be ...
17
votes
4answers
623 views

Is "CREATE INDEX` in MySQL a Linear Operation?

What I mean is the following: If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time. What I'm trying to achieve ...
17
votes
6answers
11k views

How do I efficiently get “the most recent corresponding row”?

I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
17
votes
2answers
2k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ...
17
votes
4answers
16k views

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
17
votes
1answer
628 views

What is the reasoning behind the CAP theorem?

http://en.wikipedia.org/wiki/CAP_theorem http://www.cs.berkeley.edu/~brewer/cs262b-2004/PODC-keynote.pdf I think it is not very straightforward why only two of Consistency Availability Partition ...
17
votes
3answers
5k views

Is it a good idea/approach to index a VARCHAR column?

We're using PostgreSQL v8.2.3. There are tables involved: EMPLOYEE and EMAILLIST. Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email) 2 tables are ...
17
votes
2answers
1k views

Which collation should I choose for a muiti-language website?

Does a collation have any influence over a query speed? Does the size of a table change depending of the collation? If I want to build a website that must support all possible languages (lets take ...
17
votes
2answers
1k views

Index not making execution faster, and in some cases is slowing down the query. Why is it so?

I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down. The query to create test ...
17
votes
5answers
5k views

SQL: What is slowing down INSERTs if not CPU or IO?

We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower ...
17
votes
2answers
664 views

SQL INDEX - how it works?

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases. I have read few books and I have ...
17
votes
1answer
4k views

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...

15 30 50 per page