232
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 ...
- 16k
159
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'...
- 43.2k
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 ...
- 182k
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 "...
- 31.9k
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 ...
- 4,325
32
votes
Is there a generic term for tables and views?
Postgres (and also relational algebra) calls them relations - tabular things of rows and columns that you can query from. This includes tables, views, materialised views, temporary tables, or even ...
- 514
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 ...
- 361
22
votes
Accepted
Is there a generic term for tables and views?
A "thing with columns and rows" is a relation. This is the word Codd used in his seminal paper, starting the RDBM revolution.
A "thing that can change but retain its identity" - is ...
- 691
19
votes
Delete rows older than x days without locking table
You need to delete rows in batches to avoid lock escalation. Lock escalation happens when SQL Server switches from row or page locks to locking the entire table. Lock escalation conserves memory ...
- 4,160
16
votes
Accepted
What is a “transition table" in Postgres?
You know how there are OLD and NEW record-variables for FOR EACH ROW triggers?
Transition tables are the FOR EACH STATEMENT equivalents. They're tables with the old and new tuples, so your triggers ...
- 57.3k
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 ...
- 1,689
13
votes
What is a “transition table" in Postgres?
I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table....
- 64.6k
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 ...
- 294
11
votes
Is adding the ‘tbl’ prefix to table names really a problem?
One of the features of a relational database management system is that it separates the logical presentation of information to clients from the physical storage. The former is columns in a rowset. The ...
- 25.1k
11
votes
Is adding the ‘tbl’ prefix to table names really a problem?
tl;dr It (very likely) adds redundant information, leading to cognitive overhead, and should therefore be removed.
Context is a wonderful thing, especially in computer systems. Out of context you ...
- 403
10
votes
Accepted
What does GRANT CONTROL on a schema do in MSSQL?
To see what CONTROL on schema brings to you, create a test user without any permission, grant CONTROL on schema to it and check what permissions he has after it:
create user test;
grant control on ...
- 11.2k
10
votes
Accepted
At what point is a (disk-based) table removed from memory after data is read from it?
This is a complicated topic, much of which is covered in this Microsoft Docs article.
To answer a couple of points:
Are (disk-based, so no Hekaton) tables persisted in memory for longer than the ...
- 29.8k
10
votes
Should I split semantically almost identical entities into different tables?
To me it sounds like you have one entity / object, a Note. You can have a NoteType or IsNoteShared field to differentiate between if it's a personal or shared note.
Or you can even get away without an ...
- 39.5k
9
votes
Is adding the ‘tbl’ prefix to table names really a problem?
This has been covered many times, but probably most famously by Joe Celko, an American SQL and relational database expert. I've personally been on the receiving end of one of his rants online (felt ...
- 252
8
votes
Accepted
Sqlite Create Table with Check column from other table
Looking up a value from another table would require a subquery, but the documentation says:
The expression of a CHECK constraint may not contain a subquery.
There is no such restriction on a ...
- 5,283
8
votes
Is adding the ‘tbl’ prefix to table names really a problem?
There are lots of answers here that I agree with that tell you that this isn't a very valuable naming convention. For those people who aren't convinced by the other answers I'm going to try to ...
- 4,793
8
votes
Accepted
How do I CREATE a table only if it doesn't exist in PostgreSQL?
It needs to check if the table already exists and create only if not found?
There is no standards-compliant method at all, but PostgreSQL 9.1+ supports a common extension with the IF NOT EXISTS ...
- 64.6k
8
votes
Accepted
SQLServer - treat table as index
What you ask for is what we call a "clustered index" in the SQL Server world. The clustered index is the data. I.e., the b-tree for the index is you actual table data.
A clustered index don'...
- 17.9k
7
votes
Accepted
What should I set my table_definition_cache to?
I hope this links can help you to decide your fine tunning. A little later my answer, sorry about.
I could resolve some problems in 5.5 MySQL version using this information.
Here: you can check how to ...
- 124
7
votes
Accepted
Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list?
Add a column list to your code:
SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON
INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn
(...
- 277
7
votes
Accepted
Table size grows indefinitely after deleting and then inserting the same data
If you have access to My Oracle Support:
Space Used By Tables Are Not Being Re-used With Repeat Delete/insert Operation (Doc ID 1601805.1)
So, without just copy-pasting the contents from there, the ...
- 41.1k
7
votes
How bad is to prefix table names to order them exactly as I want?
Its (more than) a little insane. If you add a account_post_comment field you need to renumber and rework all of your code.
Better is if you use alter table (or CREATE TABLE to add a comment and use ...
- 8,009
7
votes
Accepted
Export Postgres data without superuser privileges
COPY ... TO <file> creates the output file on the server, which, in addition to requiring elevated privileges, in many cases is not useful since not everyone has access to the server file system....
- 26.9k
6
votes
Accepted
Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed?
Renaming a table does not change the underlying object_id for that table. Foreign Key relationships are tracked using object_id's.
Take a look at the information in select * from sys.foreign_keys.
...
Only top scored, non community-wiki answers of a minimum length are eligible