Tag Info

Hot answers tagged

82

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


74

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


56

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


49

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


42

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


35

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


31

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


30

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


27

Is a bad practice to create a transaction always? It depends on what context you are talking here. If it is an update, then I would highly recommend using TRANSACTIONS explicitly. If it is a SELECT then NO (explicitly). But wait there is more to understand first : Everything in sql server is contained in a transaction. When the session option ...


25

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


24

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


23

Before answering when to use it and why, it's first paramount in understanding exactly what GO is, and what it isn't. The keyword GO is used by SQL Server Management Studio and SQLCMD in order to signify one thing and only one thing: The end of a batch of statements. In fact, you can even change what you use to terminate batches to something other than ...


20

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


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


16

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


15

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


15

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


15

A SQL statement always runs in a transaction. If you don't start one explicitly, every SQL statement will run in a transaction of itself. The only choice is whether you bundle multiple statements in one transaction. Transactions that span multiple statements leave locks that hurt concurrency. So "always" creating a transactions is not a good idea. You ...


14

Functions with LANGUAGE sql are basically just batch files with plain SQL commands in a function wrapper (and therefore atomic, always run inside a single transaction) accepting parameters. For anything more, as Jack wrote, the most mature language is PL/pgSQL (LANGUAGE plpgsql). It works well and has been improved with every release over the last decade, ...


14

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


14

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


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

First, you have to be able to connect to the database in order to run queries. This can be achieved by REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; GRANT CONNECT ON DATABASE database_name TO user_name; The REVOKE is necessary because The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that ...


13

Two queries - two replies: a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative ...


12

In addition to the differences posted in other answers (including some that I think were unfairly downvoted) there are some key differences between the two. Note: The error messages are from SQL Server 2012. Errors Violation of a unique constraint returns error 2627. Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot ...


12

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


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

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

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


11

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.



Only top voted, non community-wiki answers of a minimum length are eligible