Skip to main content

Questions tagged [best-practices]

Best practices are generally and informally recognized as the methods and processes that have been shown over time to be superior to those achieved by other means.

Filter by
Sorted by
Tagged with
74 votes
4 answers

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
Derek Downey's user avatar
  • 23.5k
213 votes
5 answers

Granting access to all tables for a user

I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all ...
PlaidFan's user avatar
  • 2,235
280 votes
7 answers

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

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
bernd_k's user avatar
  • 12.3k
120 votes
3 answers

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
elranu's user avatar
  • 1,303
21 votes
3 answers

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

Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column. For example, suppose we have a lookup table called ...
Nishant's user avatar
  • 879
6 votes
3 answers

"SELECT *" why is it an antipattern

On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can ...
Thomas E.'s user avatar
13 votes
2 answers

Is it recommended to install extensions into pg_catalog schema?

Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?
zam6ak's user avatar
  • 471
103 votes
18 answers

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

I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’. On the internet I found some blogs saying it adds nothing to documentation,...
Racer SQL's user avatar
  • 7,386
40 votes
1 answer

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
TheIdiot's user avatar
  • 503
19 votes
2 answers

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
Ross Bush's user avatar
  • 505
10 votes
5 answers

Over use/correct use of schemas?

Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise. Basically, every object that I create is going into a schema with the schema name reflecting a ...
Stuart Blackler's user avatar
46 votes
12 answers

How could DBAs be more 'programmer friendly'?

The answers and comments on the version and version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
Jack Douglas's user avatar
28 votes
4 answers

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

Not using FK constraints is my company's untold rule. FK constraints are used only when designing ERD and not used when creating tables. According to my senior, in real practice, those are very time ...
user2652379's user avatar
6 votes
2 answers

Seperate volumes for tempdb, data, and backups on SQL Servers in VMs

When configuring new SQL Servers on VMs, it is a good idea to have the Database, TempDB, logs, and backups on separate logical drives, even if the underlying storage is the same? I know that this is a ...
buddinggeek's user avatar
44 votes
3 answers

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

I have found plenty of information on what STATISTICS are: how they are maintained, how they can be created manually or automatically from queries or indexes, and so on. But, I have been unable to ...
Solomon Rutzky's user avatar
35 votes
4 answers

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own ...
jrara's user avatar
  • 5,333
22 votes
4 answers

Where should you define foreign keys?

Is it better to define foreign keys in the database or in the code part of an application?
newuser's user avatar
  • 681
13 votes
3 answers

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
user avatar
11 votes
4 answers

Help me choose a RAID level combination for a SQL Server 2008 instance

I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2. I am going to make new RAID configuration. I have 6 SCSI ...
adopilot's user avatar
  • 2,423
7 votes
1 answer

SHRINKFILE best practices and experience [duplicate]

Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type ...
Csaba Toth's user avatar
6 votes
1 answer

Is splitting a CSV column into a separate table (enforcing 1NF) unnecessary complication?

I had very carefully asked the table designer why he had chosen to create CSV columns in multiple tables. The designer's answer was that splitting CSV columns into separate tables was "...
Ascendant's user avatar
  • 429
5 votes
2 answers

When should I NOT set the Maximum Server Memory option in SQL Server?

In SQL Server, I normally set the Maximum Server Memory setting to one of the popular best practices, such as leaving 10% or 4GB, whichever is higher.
SomeGuy's user avatar
  • 2,013
45 votes
6 answers

Best practices on common person fields (Name, email, address, gender etc...) [closed]

What are the most common best practices on length and data type on common fields like: First Name Last Name Address Email Sex State City Country Phone Number etc....
Snow_Mac's user avatar
  • 563
43 votes
7 answers

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

Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object). But I ran across a case now where I'm tempted to use ...
J.D.'s user avatar
  • 38k
27 votes
3 answers

What is more efficient, a where clause or a join with million plus row tables?

We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows. Sample structures: MasterTable (Id, UserId, Created, Updated...) -...
Jeremy Boyd's user avatar
24 votes
1 answer

Grant access to all tables of a database

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data. ...
d33tah's user avatar
  • 389
14 votes
1 answer

How to setup local database development process for small web team?

Background I am working on creating a new development process for a small web team of about 4 programmers and 4 designers, with the obvious potential to grow the team in the future. Our product is a ...
Derek Downey's user avatar
  • 23.5k
14 votes
2 answers

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

We discovered a SQL "sa" account being used in a way it should not have been, so we are changing sa passwords on all our SQL instances. (We have SQL 2005 through 2017 servers running in mixed ...
BradC's user avatar
  • 9,994
11 votes
3 answers

What is the best method to add error handling in SQL 2005 stored procs?

What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling? Additionally, whats the best way to handle multiple error scenarios in a stored ...
kacalapy's user avatar
  • 2,052
11 votes
3 answers

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

I would need to track a products price changes so that I can query the db for a product price at a given date. The information is used in a system that calculates historical audits so it must return ...
Gunnar Norred's user avatar
5 votes
3 answers

Is a surrogate key better than a natural key in this case

I copied this code from here: CREATE TABLE records( email TEXT REFERENCES users(email), lat DECIMAL, lon DECIMAL, depth TEXT, upload_date TIMESTAMP, comment TEXT, PRIMARY ...
Songo's user avatar
  • 293
5 votes
2 answers

Splitting a large table to improve performance

This is a follow-up to an earlier question. I have a SQL Server 2008 R2 Standard server, that holds a single database, which itself has almost nothing except a large table. The table is 100+ million ...
KenD's user avatar
  • 303
4 votes
2 answers

What is the best practice for mapping from natural keys to integer-based keys? (ETL)

This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific ...
Chris Aldrich's user avatar
3 votes
1 answer

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
JohnCand's user avatar
  • 525
2 votes
1 answer

How would you implement conversation groups/ locking for multiple users of a Service Broker Queue?

How do you implement conversation groups for multiple users using their own instance of the user applications but sending related messages to the service broker queue? Any good example of this kind ...
fa1c0n3r's user avatar
  • 617
2 votes
3 answers

Table naming and Database normalization

I'm creating a database of simulation results, and am trying to do so the right way. I've attempted to show relationships between data to make sure nothing redundant is thrown in. My current ...
ijustlovemath's user avatar
1 vote
1 answer

table design to track significant events associated with an item

An Orders table I'm dealing with at present in design phase has about 10 or more columns. Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders ...
mg1075's user avatar
  • 755
0 votes
1 answer

How to revoke DBADM and clean up any related objects from DB2 LUW

This question has started because of us taking copies of production backups and restoring them into lower environments (with scrambled data of course) for developers to practice and/or debug against. ...
Chris Aldrich's user avatar
177 votes
20 answers

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the ...
Tom Hunter's user avatar
  • 2,169
59 votes
1 answer

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
Dan Leary's user avatar
  • 693
44 votes
2 answers

Database model with users, roles and rights

I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user. Below is ...
taudorf's user avatar
  • 543
38 votes
1 answer


I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of ...
Solomon Rutzky's user avatar
27 votes
7 answers

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT, t2.stuff FROM someTable t1 ...
Ben Brocka's user avatar
  • 2,063
26 votes
7 answers

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

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the ...
Greg Burghardt's user avatar
23 votes
5 answers

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. On a server with a single, default instance, the following ports ...
user3399498's user avatar
15 votes
3 answers

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question isn'...
Chris Aldrich's user avatar
14 votes
2 answers

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
Steven's user avatar
  • 319
9 votes
5 answers

Parallelism Best Practices

What are the best practices with setting parallelism in general? I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default ...
user avatar
9 votes
1 answer

Why does the fill factor in SQL Server default to 0 (100%)?

I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95? Is ...
NTDLS's user avatar
  • 405
8 votes
3 answers

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
Noah Yetter's user avatar