225 votes
Accepted

Is adding the ‘tbl’ prefix to table names really a problem?

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 ...
user avatar
  • 15.6k
148 votes

Is adding the ‘tbl’ prefix to table names really a problem?

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'...
user avatar
  • 41.4k
120 votes

Is adding the ‘tbl’ prefix to table names really a problem?

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 ...
user avatar
75 votes
Accepted

Is it still best practice to avoid using the default ports for SQL Server?

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 ...
user avatar
57 votes
Accepted

What's a good use case for SELECT * in production code?

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 ...
user avatar
  • 696
43 votes

Is adding the ‘tbl’ prefix to table names really a problem?

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 "...
user avatar
  • 30.9k
38 votes
Accepted

Not using foreign key constraints in real practice. Is it OK?

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 ...
user avatar
37 votes

What's a good use case for SELECT * in production code?

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 ... ) ...
user avatar
34 votes

Granting access to all tables for a user

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 ...
user avatar
31 votes

Is adding the ‘tbl’ prefix to table names really a problem?

comDon't verListen prepTo adjThose adjOther nouPeople. proYou auxShould advAlways verUse nouPrefixes prepWith proYour adjTable nouNames. proI auxHave advEven verStarted gerUsing proThem prepIn ...
user avatar
  • 4,125
30 votes
Accepted

Should I explicitly DENY UPDATE to columns that should not be updated?

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 ...
user avatar
28 votes

Grant access to all tables of a database

Postgres 14 ... adds the predefined role pg_read_all_data to make this simple: GRANT pg_read_all_data TO myuser; The manual: pg_read_all_data Read all data (tables, views, sequences), as if having ...
user avatar
22 votes

When is it better to create STATISTICS instead of creating an Index?

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 ...
user avatar
  • 61.3k
22 votes

Is adding the ‘tbl’ prefix to table names really a problem?

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 ...
user avatar
  • 361
22 votes
Accepted

What are the current best practices concerning varchar sizing in SQL Server?

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. ...
user avatar
21 votes

Granting access to all tables for a user

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 ...
user avatar
  • 311
21 votes

Bad Practice to Store Calculated Data in Database?

If computers were infinitely fast, then, No, you would never store a value that could be calculated from other columns in the database. Storing calculated values is a violation of database ...
user avatar
20 votes
Accepted

Storing a Billing Address Best Practice in Orders Table

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 ...
user avatar
  • 8,250
19 votes
Accepted

Why is mixing column collations in a single database considered bad?

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 ...
user avatar
18 votes

What problems are solved by splitting street addresses into individual columns?

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 ...
user avatar
17 votes

Is it recommended to install extensions into pg_catalog schema?

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. ...
user avatar
15 votes
Accepted

Unused Indexes Best Practices

This DMV only maintains statistics since the last SQL Server restart; the view gets wiped out completely and everything starts from scratch. More importantly, the rows in this view for any specific ...
user avatar
15 votes

Should I explicitly DENY UPDATE to columns that should not be updated?

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 ...
user avatar
15 votes
Accepted

Does changing "sa" password require a SQL restart (in mixed mode)?

Q1: Does changing the sa password require a SQL restart? No, but changing the authentication mode does. Since you're just changing the password and the authentication mode is already set to mixed, ...
user avatar
14 votes

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

You've addressed a boatload of items in your question. Thanks for being so thorough! Just a couple of things I notice off hand: How various hardware / load factors influence what should be ...
user avatar
  • 67.5k
13 votes

When should I use a unique constraint instead of a unique index?

To quote MSDN as an authoritative source: There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation ...
user avatar
  • 231
13 votes

Is adding the ‘tbl’ prefix to table names really a problem?

I've read a few blog posts like this or this (there's quite a few) after I inherited my first SQL Server Instance and noticed many objects where prefixed, I wanted to start developing new ones with a ...
user avatar
  • 1,681
13 votes
Accepted

How would I track all price changes in a db in order to get the price of 'x' product on 'y' date

If I understand the scenario appropriately, you should define a table that retains a Price time series; therefore, I agree, this has a lot to do with the temporal aspect of the database you are ...
user avatar
  • 8,250
13 votes

Is it still best practice to avoid using the default ports for SQL Server?

Even though security through obscurity isn't actual security I won't say there aren't any cases where it helps. If an attacker wants to know where your service is listening they can easily find out, ...
user avatar
  • 15.4k

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