18
votes
3answers
30k views

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
18
votes
5answers
18k views

SQL server suspect database?

What do you do when you have a database that is marked as 'suspect'? Restore from the last backup? Please advise. Thank you!
18
votes
2answers
743 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
780 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
5answers
25k views

How do you tune MySQL for a heavy InnoDB workload?

Assuming a production OLTP system with predominantly InnoDB tables What are the common symptoms of a mistuned/misconfigured system? What configuration parameters do you most commonly change from ...
18
votes
1answer
321 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
2answers
4k 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?
18
votes
1answer
4k 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
4answers
11k 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 ...
18
votes
3answers
13k 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
3k 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
14k views

How does the Oracle DUAL table work?

SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY ...
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
5answers
4k 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
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
3answers
4k 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
10k 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
4answers
9k 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
10answers
30k 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
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
2answers
24k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
17
votes
4answers
564 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
601 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
3answers
22k 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 ...
17
votes
3answers
686 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 ...
17
votes
5answers
4k 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
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 ...
17
votes
2answers
1k views

CouchDB vs MongoDB [closed]

Evaluating document-oriented storage, what are the pros and cons of CouchDB vs MongoDB?
17
votes
2answers
418 views

Is there a complete list of Online operations for each SQL Server version?

As we are about to change some of the large tables in our 5TB database I found myself needing a list of the operations that can be performed online and the which require full locks to be held while ...
17
votes
2answers
581 views

Get and Put performance testing on Google BigTables (and other integrated DBs)

What are some effective ways to perform programmatic performance testing on database operations, especially in environments where the databases themselves do not offer dedicated tools? For example, ...
16
votes
7answers
8k views

How can I learn to become a DBA?

Question for you DBAs and such: How do you go about learning the skills to become a DBA without having the 'on hands' or on-the-job training? My experience in DB work has been messing with MySQL via ...
16
votes
4answers
2k views

Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns. The database development started in ...
16
votes
7answers
5k views

Does a DBA need to know how to program in a system language besides SQL?

To what extent does a Database Administrator need to know system or application level programming languages (for example .NET or PHP) besides "just SQL"? For the purposes of this question, no ...
16
votes
11answers
6k 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 ...
16
votes
7answers
591 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 ...
16
votes
2answers
2k views

Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

I have been getting myself very confused. Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE? My guess is that I would use it if I wanted to conditionally ...
16
votes
4answers
998 views

How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?

is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to ...
16
votes
2answers
1k views

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
16
votes
3answers
1k views

Is it OK to blindly add missing indexes?

I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
16
votes
3answers
2k views

Justify NOT using (nolock) hint in every query

Have you ever had to justify NOT using a query hint? I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on ...
16
votes
4answers
4k views

Using SQL Profiler on a database that's in production

As a developer, I use SQL Profiler quite often. It's a good debugging tool, both to track what my code is doing and to analyse performance problems. But I've always used it on my development ...
16
votes
4answers
8k views

Task scheduler for SQL Server Express

I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database. Unfortunately the ...
16
votes
3answers
4k views

Running total with count?

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count: sum(count (distinct (customers))) Say if I ran the count ...
16
votes
3answers
9k 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), constraint ...
16
votes
6answers
4k views

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
16
votes
8answers
9k 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 ...
16
votes
3answers
3k views

Managing concurrency when using SELECT-UPDATE pattern

Let's say you have the following code (please ignore that it's awful): BEGIN TRAN; DECLARE @id int SELECT @id = id + 1 FROM TableA; UPDATE TableA SET id = @id; --TableA must have only one row, ...
16
votes
1answer
935 views

24x7 vs Night Time Window

Where can I find resources on how to better move to a 24x7 operation? How do the big companies with big databases accomplish this? Our nightly jobs such as purge old data reindex update ...
16
votes
3answers
30k views

How to change SQL Server collation

How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database? Is there a way to do it using visual interface of SQL Server Management Studio? In ...

15 30 50 per page