217

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


186

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


136

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


114

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


113

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


69

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


55

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.


52

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


51

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


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


37

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


34

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


29

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


25

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


25

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


25

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!


24

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits. The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there1. This way we can be sure we only ever capture orders for customers that ...


21

The privilege on DATABASE only grants general connection rights to the database and no more. A user with just that privilege can only see what the general public is allowed to see. To grant read access to all tables, you also need privileges on all schemas and tables: GRANT USAGE ON SCHEMA public TO myuser; -- more schemas? GRANT SELECT ON ALL TABLES IN ...


21

You question revolves around - When is it a good thing to just create statistics vs create index (which create stats). From my sql server internals notes (SQLSkills class- IE1 and IE2) and SQL Server internals book, below is my limited understanding : SQL Server statistics are nothing but system objects that contain vital information about the index key ...


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

It may be that I was doing something wrong here since I am very new to PostgreSQL. But this only solved the first part of the problem for me - setting the privileges on all existing tables. In order for permissions to be correctly set for my user on new tables, that are created I has to set default permissions for the user: ALTER DEFAULT PRIVILEGES IN ...


19

The recommendation of keeping all column collations to the database default seems more like guidelines or best practices to me. You are entirely correct here. Why is it considered such a serious error by some? For the same reason that you will often hear / read that "you should never use:" CURSORs GOTO statements SQLCLR WITH (NOLOCK) etc, etc, etc ...


19

Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved. You also do not need, nor want, to specify a column length larger than the maximum actual size that will be stored for a variety of reasons: query memory ...


18

I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists. It is useful to split up addresses into distinct fields, but you can never, EVER design for every possible pathological aberration of address formats and components the human brain can devise. ...


17

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


16

Don't install extensions to pg_catalog (unless that's their default: very few extensions are designed that way), because you don't mess with system catalog, ever. @Chris demonstrates one reason why. There are others. However, the "public" schema is in no way special. It's just the default schema that's pre-installed in standard distributions so we can get ...


16

Conceptually speaking, although in your business environment Order and Address are ideas that are closely associated, they are in effect two separate entity types, each with its own set of applicable properties (or attributes) and constraints. Therefore, as previously stated in comments, I agree with @Erik, and you should organize the logical layout of your ...


16

I completely agree with @Aaron on the technical aspect of this. Beyond that I would say, regarding best practices: Given that it's the DBA's job / responsibility to protect the data, default approach should be to do just that, as the DBA sees fit, and require a solid business case for making a change. A hypothetical-future-potential-somewhat-possible-given-...


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