229 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 ...
billinkc's user avatar
  • 15.9k
158 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'...
Brent Ozar's user avatar
  • 42.9k
122 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 ...
Aaron Bertrand's user avatar
77 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 ...
Sean Gallardy's user avatar
58 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 ...
Rusty's user avatar
  • 706
48 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 ...
Michael Green's user avatar
44 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 ...
Ganbayar Gansukh's user avatar
44 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 "...
Philᵀᴹ's user avatar
  • 31.7k
37 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 ...
ErikE's user avatar
  • 4,305
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 ... ) ...
David Browne - Microsoft's user avatar
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 ...
Aaron Bertrand's user avatar
25 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 ...
Basil Bourque's user avatar
23 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. ...
Solomon Rutzky's user avatar
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 ...
phyrfox's user avatar
  • 361
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 ...
Solomon Rutzky's 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 ...
Jim Garrison's 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 ...
Aaron Bertrand's 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 ...
Solomon Rutzky's 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, ...
Sean Gallardy's 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 ...
Hannah Vernon's user avatar
  • 70k
14 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 ...
tinonetic's user avatar
  • 240
14 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 ...
MDCCL's user avatar
  • 8,510
13 votes
Accepted

What are the best practices regarding lookup tables in relational databases?

By IDN, I take it you mean an IDENTITY, SEQUENCE or AUTO_INCREMENT field? You should take a look here and here. Note, section 5 (Misusing Data values as Data Elements) of the first reference, ...
Vérace's user avatar
  • 29.7k
13 votes

What are the best practices regarding lookup tables in relational databases?

There is a third approach which has some of the advantages of your two options - put an actual code in the code table. By this I mean a short character sequence that captures the essence of the full ...
Michael Green's user avatar
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 ...
Nelson's user avatar
  • 1,689
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, ...
Tom V's user avatar
  • 15.7k
12 votes

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

Late answer but could be of use to other readers. Please, have in mind that there are lots of maintenance or reporting tasks, you can create, that carry unseen risks associated with them. What would ...
Alex Kirilov's user avatar
12 votes

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

Yes, adding a prefix that denotes the type of the object is a problem, and unnecessary. Because, Sometimes objects begin life as something but will end up becoming something else. (e.g. table tblXxx ...
sampathsris's user avatar
11 votes
Accepted

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

Problems that can be solved by splitting include Validation Any one part of the name can be compared to a master list. Those which do not match can be rejected. Postcode / zipcode is an obvious ...
Michael Green's user avatar

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