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 ...
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;
ON DATABASE database_name
The REVOKE is necessary because
The key word PUBLIC indicates that the privileges are to be granted to
all roles, including those that ...
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 ...
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, ...
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 ...
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 ...
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.
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 ...
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 ...
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):
FROM BigTable A, OtherBigTable B
ORDER BY Somecolumn
That's a recipe for disaster. Notice ...
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 ...
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 ...
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 "...
It is a terrible practice.
... 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 ...
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 ...
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 ...
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 ...
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 ...
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, ...
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 ...
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 ...
What’s a good use of select * in production?
IMO only things like this:
create table #foo(a int, b int, c int, d int)
select * from #foo
with q as
select a, b, c
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 ...
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
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, ...
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.
As the HR director I am able to access anything in the HR schema, as the IT ...
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 ...
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 ...
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 ...
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>;