216

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it. In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the ...


177

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


152

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


135

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


134

Brent here (the guy you're referring to in the question). The reason I tell you not to add tbl to the front of your table names is the same reason I'd say not to add child to the front of your child's name. You don't call them childJohn and childJane. Not only does it not add any value, they may not be a child later in life - and your objects may later ...


118

This is a very subjective argument, but here is my take: the tbl prefix is useless. How many scenarios are you looking at code and you can't tell if something's a table or something else? What value does tbl add except that when you look at a list of tables in Object Explorer, you have to do more work to find the one(s) you're looking for? Some people ...


103

In addition to the points in other answers, here 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 insert duplicate key in object 'dbo.P1U'. The duplicate key value ...


103

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


78

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


68

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. Which was asinine then and still asinine now. Security through arguably obscurity isn't security at all. Is this advice still relevant IMHO it was never relevant. It was required for some compliance purposes because the ...


54

assuming you want to give them all privileges - do this: grant all privileges on database dbname to dbuser; where dbname is the name of your database and dbuser is the name of the user.


51

Strictly speaking, the term "stored procedures" points to SQL procedures in Postgres, introduced with Postgres 11. Related: When to use stored procedure / user-defined function? There are also functions, doing almost but not quite the same, and those have been there from the beginning. Functions with LANGUAGE sql are basically just batch files with plain ...


51

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


42

It is a terrible practice. tbl tbl_ Table_ t_ ... seen them all in production. One of the products I'm currently working with has half of the tables named tbl_whatever, and the other half named "normally" - They've obviously got developers that are working to different standards. Another one of their bad habits is prefixing column names that are foreign ...


41

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


39

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


36

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


33

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


32

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


31

I generally abhor SELECT * in production code, and I've been in a situation where its use led to massive amounts of rework later. Your case does look like a fair use of it though. The place where I find SELECT * to be a must - and its evil cousin "INSERT INTO tbl" without a column list - is in an archiving situation, where rows are being moved to another ...


28

The argument doesn't make sense. I always want the controls and constraints as close to the data as possible. Putting it in the application layer means it only affects the people using the application layer, and also assumes that the code will be bug-free and the security around those code paths will be bulletproof. Those are big assumptions. If they ...


28

What’s a good use of select * in production? IMO only things like this: eg create table #foo(a int, b int, c int, d int) ... select * from #foo or with q as ( select a, b, c from ... ) select * from q ie when the * is bound to an explicit column list that's declared in the same batch, and is used just to avoid repeating the column list multiple ...


27

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


24

You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization. Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment. If recovery model is simple, ...


23

comDon't verListen prepTo adjThose adjOther nouPeople. proYou auxShould advAlways verUse nouPrefixes prepWith proYour adjTable nouNames. proI auxHave advEven verStarted gerUsing proThem prepIn adjNormal nouWriting. comSee advHow adjEffective proIt verIs? nouPeople auxCan verUnderstand proYour nouWriting adjBetter!


22

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


21

Using a prefix like this is known as Hungarian Notation. It's premise is simple: you can determine what something is by how it's named. This is particularly common in programming languages, especially when developers write monolithic functions that span dozens of pages, either by lack of skill or lack of language features. It's a mnemonic aid that helps ...


20

I think the answer is, like with many things IT, "it depends". A massive ERP database with lots of sensitive company and customer information? Probably not (both for security and performance reasons). A departmental 5 MB database with an Access front-end that tracks contributions to the donut and pizza funds? Not going to make a whole lot of difference, at ...


20

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


20

Granting all privileges to all tables within the database is achieved with GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;


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