Hot answers tagged best-practices
64
It really depends on whether the developer has any support responsibilities. If they are on the hook for third line support then they will probably need to look at the production database to do this.
Generally it's a bad idea to do anything on a production server unless it's really necessary to do it there.
For most development purposes, mirrors or ...
61
No.
Developers should not have access to production database systems for the following reasons:
Availability and Performance: Having read-only rights to a database is not harmless. A poorly written query can:
Lock tables, blocking other critical processes.
Trash your data cache, forcing other processes to re-read data from disk.
Tax your storage layer, ...
45
I've been a MySQL DBA for the past 6.5 years. I've also spent some 16 years as a developer and have interacted with many DBAs. Many of them pragmatic. Some of them obnoxious. A few have no idea what it means to be a DBA.
I have come to this conclusion:
Technically speaking, DBAs who have one or more of the following qualities are the best to work with:
...
45
Performance would be a BIG reason.
Just because they can't change the data doesn't mean they can't affect the server. A poorly written query could bring the production environment to its knees, and potentially cause other issues (like tempdb overflows):
SELECT *
FROM BigTable A, OtherBigTable B
ORDER BY Somecolumn
That's a recipe for disaster. Notice ...
31
I would tend to be very suspicious of any set of universal best practices because, for most of these fields, the devil is in the details. Just because the information is relatively common doesn't mean that your application uses the data in exactly the same way that other applications use it. That means your data model may need to be slightly different.
...
29
Table aliasing is a common and helpful practice.
It saves you keystrokes when referencing columns anywhere in your query.
It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used.
It is even required when you join a table to itself or when ...
29
Put the foreign keys on the database. Even if you validate the data in the application before you save it the FK's are a good piece QA backup. For a first approximation, applications always have data issues. Leaving controls like this out of the system just invites failure modes where data gets corrupted silently.
There's nothing like working in data ...
27
Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.
So for a database ...
23
Having teams sit in different sections/floors somehow seems to encourage "us vs them" mentality.
Sitting a DBA right in the middle of the development team is a great way to tear down the programmer/DBA wall. Both the DBA and the programmers will benefit from this, if they remain open minded and put their egos aside.
Face to face communication, ...
18
As a programmer understanding the database better made me a better programmer. When I became a database administrator this became even more important, therefore I believe education is the key.
DBAs should patiently guide developers treating them as competent professionals. Few programmers when shown the difference between a set operation and a row by ...
18
From a Programmer standpoint, I would say the thing we want most is consistent, well defined and implemented standards for how the data layer will be designed and built. I am willing to play the way you want in your sandbox, you just need to tell me what you want, and not change the rules all the time. It should be implemented the same for everyone, even ...
16
We've been doing this for almost five years, and we think that explicitly testing modifications is definitely doable, but it is quite slow.
Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, ...
14
There is no difference in the underlying functionality of the two types of aliasing (as opposed to =). What it boils down to is exactly what you mentioned: Readability and maintainability.
In my opinion the former (<Expression> as <Alias>) is much more readable as it is self explanatory. When you have SELECT ColumnName = 1 I think it'd be ...
13
This sort of thing varies hugely from place to place. At my current site, the line between developers and DBAs is very blurred indeed - we (DBAs) write PL/SQL too, and they (developers) dissect query plans. We all see ourselves as peers, merely with different skillsets and responsibilities. This is very amusing: recently the company has jumped on-board the ...
13
The principle is "least privilege" and "need to know": do developers pass this test?Especially when Auditors or Sarbannes-Oxley come knocking.
Then, my next assumption: developers are stupid. So if they do need say for 3rd line support, who then needs it? Web monkeys typically don't but database types yes if they are expected to support it.
Then, is access ...
11
It depends. When looking at a data warehouse, if you don't have a specific design in mind, automatic storage management may be an excellent route.
Consider the discussion at AskTom, OTN Forums, OTN Forums 2, and OTN Forums 3.
There is no one right way to deal with things, and the answers change based on a host of hardware and network factors. In order to ...
11
No matter what infrastructure we support, we have to support the users of it. A lot of users are developers, so we support the developers to enable them to make the best possible use of that infrastructure. To be able to do this we need to understand each other, with the different ideas and points of views in mind. Having insight to the views from both sides ...
11
You may as well guess based on sample data and expected audience. It depends on your location.
Some notes:
Addresses:
I don't have a state and it irritates me when I have to pick "Outside USA"
UK postal counties bear no resemblance top local government regions: see "Column type and size for international country subdivisions (states, provinces, ...
11
It may be a good practice because when you have other users using the database you want to be able to limit their access with schemas. For example in a database you have the following tables.
HR.Payhist
HR.Payscale
HR.Jobdesc
IT.username
IT.useraccesslevel
ENG.jobsite
ENG.trainings
As the HR director I am able to access anything in the HR schema, as the ...
11
Referential Integrity should be handled on the lowest possible level, which would be the underlying database. Relational Database Management Systems are optimized to handle this. It doesn't make sense to reinvent the proverbial wheel.
It is acceptable to define domain logic in the application code to prevent the DML statement to even cause an RI exception, ...
10
Alex Kuznetsov has a great chapter in his book Defensive Database Programming (Chapter 8) that covers T-SQL TRY...CATCH, T-SQL transactions & SET XACT_ABORT settings, and using client-side error handling. It will help you a lot in deciding which of the options makes the most sense for what you need to accomplish.
It is available for free at this site. I ...
10
Security: There might be sensitive information that is sanitized when they make it available to developers.
Paranoia: Some might think you could still mess up data with just select access.
Performance: A query takes some resources to perform, and you can't tell me your developers are perfect when they write code.
10
You almost have your answer already:
Create the new structure in parallel
Start writing to both structures
Migrate old data to the new structure
Only write and read new structure
Delete old columns
As for step 3, use something like this (in one transaction):
Insert what is not there yet:
INSERT INTO new_tbl (old_id, data)
SELECT old_id, data
FROM ...
10
I'm going to go out on a limb here fully expecting this to get down-voted since this is a DBA-focused group.
I agree that using strict foreign keys is the best decision in most scenarios. However, there are some cases where foreign keys cause more problems than they solve.
When you are dealing with very highly concurrent environment such as a high ...
9
Josh,
This is a very common task for all DBA's and the right answer is NOT the same for every one and for each server. As lot of other things, it depends on what you need.
Most definitely you don't want to run "Shrink Database" as already suggested. Its EVIL to performance and the below ref will show you why? It causes disk and as well as index ...
9
For modern RDBMS there is no difference between "explicit JOIN" and "JOIN-in-the-WHERE" (if all JOINS are INNER) regards performance and query plan.
The explicit JOIN syntax is clearer and less ambiguous (see links below)
Now, the JOIN-before-WHERE is logical processing not actual processing and the modern optimisers are clever enough to realise this.
...
9
On an usual 24/7 OLTP environment a normal developer shouldn't be allowed in production. Period! If, from time to time, a particular reason appears, than permissions could be granted upon request. But on a usual basis no.
I've seen many reasons for this:
SELECT * from a big table leading to:
performance issues (cartesian products);
blocking issues that ...
9
I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it ...
9
Email is a particularly bad choice for any PK whether composite or single. See my answer on this question on Stack Overflow for why:
http://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174
8
I think using aliases helps the readability of a query if the table names are long or so similar to each other that someone reading it quickly might mistake them. Do you think that this...
SELECT Really_long_table_name.ID, Even_longer_table_name_than_before.Name, Even_longer_table_name_than_before.Description, Even_longer_table_name_than_before.State
FROM ...
Only top voted, non community-wiki answers of a minimum length are eligible
