28
votes
4answers
4k 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 ...
28
votes
7answers
1k 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 ...
28
votes
2answers
8k views

Find Queries that Are Causing Waits

Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server? In this particular case I am wondering about async_network_io.
28
votes
5answers
30k views

When should you denormalize?

I think we are all familiar with database normalization. My question is: What are some guidelines that you use when you want to denormalize the tables?
28
votes
6answers
41k 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 ...
28
votes
2answers
39k views

When to use views in MySQL?

When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table? One reason that I would prefer to use views is that the database schema ...
28
votes
2answers
1k views

Where can I find database tuning exercises for learning?

As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc. I'm looking ...
28
votes
2answers
10k views

I/O requests taking longer than 15 seconds

Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
28
votes
1answer
9k 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 ...
28
votes
1answer
5k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
27
votes
3answers
4k views

At what point does one database per client become unfeasible?

For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system. However, we're developing a new system that ...
27
votes
3answers
4k views

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. ...
27
votes
5answers
59k views

mysql: Show GRANTs for all users

MySQL's SHOW GRANTS shows the permissions of the current user. Is there a way to log in as root and show the permissions of all users?
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
31k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
27
votes
3answers
4k 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 ...
27
votes
2answers
25k views

What is the purpose of the database 'owner'?

Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query ...
27
votes
4answers
13k views

Unit testing of stored procedures

I've been considering this for quite a long time now. The basic question is: how to unit test stored procedures? I see that I can set up unit tests relatively easily for functions in the classic ...
27
votes
4answers
73k 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 ...
27
votes
3answers
20k views

How to execute SQL against all DBs on a Server

I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems: select so.name, so.type, MAX(case when sc.text like '%remote%' then '' ...
27
votes
2answers
5k 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 ...
27
votes
4answers
41k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
27
votes
4answers
57k views

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
27
votes
3answers
8k views

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 ...
27
votes
1answer
928 views

Why is the speed of executing statements dependent on the network connection?

It looks like the speed of executing T-SQL is dependent on the latency of the network connection against the server. I assumed that if SQL Server has nothing to report back to the client about, it ...
27
votes
4answers
7k views

Is it possible to quickly create/restore database snapshots with PostgreSQL?

First of all, I'm a developer, not a DBA or sysadmin; please be gentle :) I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
27
votes
5answers
32k 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 ...
26
votes
7answers
15k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
26
votes
2answers
109k views

SQL server databases stuck in restoring state

I have a Sharepoint server. I guess our network administrator has miss configured his back up tool (I guess he uses Symantec backup exec) and now some of my databases are stuck in restoring state! ...
26
votes
7answers
49k views

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
26
votes
4answers
17k views

What is lock escalation?

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

Is using multiple foreign keys separated by commas wrong, and if so, why?

There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called DealCategories with the following structure: DealCategoryId ...
26
votes
3answers
43k views

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as a administrator using sqlplus: sqlplus sys as sysdba sqlplus system/manager These accounts should be uses for different purposes, I suppose. Which tasks ...
26
votes
3answers
30k views

How to turn json array into postgres array?

I have a field user that holds a json document that roughly looks like that: { name: 'foo', tags: ['foo', 'bar'] } I would like to turn the tags array into a concatenated string (foo, bar). ...
26
votes
11answers
71k 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 ...
26
votes
5answers
16k views

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 ...
26
votes
4answers
14k 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 ...
26
votes
3answers
4k views

Are there disadvantages to contained databases?

SQL Server 2012 introduced the concept of "contained" databases, where everything (well, mostly everything) the database needs is contained within the database itself. This offers big advantages when ...
26
votes
4answers
5k views

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 ...
26
votes
1answer
8k 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 ...
26
votes
3answers
20k views

Is the SQL Server Express memory and CPU limit per instance?

If I have 8GB of RAM in a server and I run 4 instances of SQL Server Express, will the total memory limit used by SQL Server be 1GB or 4GB? Would it be advisable to run multiple instances like this ...
26
votes
2answers
17k views

Timeseries: SQL or NoSQL?

I don't care about the general differences between SQL and NoSQL (or their traditional differences). I am currently looking at altering the storage of our internal time series. They all contain ...
26
votes
1answer
10k views

Designing a platform: one database or multiple databases?

We are building a web platform that incorporates multiple services, each with its own underlying data. These services are being built independently following the principles of Service-Oriented ...
26
votes
1answer
676 views

Is sys.stats_columns incorrect?

Let's say I have a table Foo with columns ID1, ID2 and a composite primary key defined over ID2, ID1. (I'm currently working with a System Center product that has several tables defined this way with ...
25
votes
13answers
13k views

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
25
votes
4answers
65k views

How do I change the DEFINER of a VIEW in Mysql?

When I run mysqldump, I get an error: mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES This makes sense because foobar is a legacy ...
25
votes
2answers
45k views

Is it safe to delete mysql-bin files?

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
25
votes
6answers
57k views

SQL Server Cannot drop database <dbname> because it is currently in use… but no sessions displayed

When I try to drop a database I get the error "Cannot drop database "dbname" because it is currently in use". However, when I run sp_who2, there are definitely no sessions connected to this database. ...
25
votes
2answers
5k 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) ) ...
25
votes
5answers
19k 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 ...

15 30 50 per page