Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

A best practice will not always be superior for every case. In addition, a best practice can be superseded when another best practice is determined to be better over time.

376 questions
266
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 int primary key, code varchar(10) NULL ); go create…
bernd_k
  • 12,001
  • 23
  • 74
  • 109
201
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 tables in a specified database. I must be…
PlaidFan
  • 2,115
  • 2
  • 13
  • 7
174
votes
19 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 development team can't even connect to the production…
Tom Hunter
  • 2,139
  • 3
  • 16
  • 11
118
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 is not really necessary? Even if you are using an…
elranu
  • 1,283
  • 2
  • 9
  • 7
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, and also that “it takes longer to read…
Racer SQL
  • 7,228
  • 10
  • 55
  • 111
70
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 would like to do is move some of the application…
Derek Downey
  • 23,190
  • 11
  • 77
  • 104
57
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 associated with specific users. If I wanted to move the…
Dan Leary
  • 673
  • 1
  • 6
  • 5
51
votes
5 answers

Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling

I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem comes to figuring out which other tasks I want…
Josh
  • 673
  • 1
  • 6
  • 7
46
votes
12 answers

How could DBAs be more 'programmer friendly'?

The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing about the divide between DBAs and programmers in…
Jack Douglas
  • 38,607
  • 15
  • 97
  • 174
44
votes
6 answers

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

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
  • 553
  • 1
  • 4
  • 7
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 the table definition of users, roles and…
taudorf
  • 543
  • 1
  • 5
  • 7
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 find any guidance or "best practices" information…
Solomon Rutzky
  • 67,276
  • 7
  • 144
  • 280
43
votes
5 answers

Unit testing of stored procedures

I've been considering this for quite a long time now. The basic question is: how to unit test stored procedures? I see that I can set up unit tests relatively easily for functions in the classic sense (I mean they get zero or more arguments and…
dezso
  • 30,069
  • 13
  • 96
  • 141
42
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 it but would feel cheap if I did. My use case is…
J.D.
  • 30,123
  • 7
  • 45
  • 96
39
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 using it after every statement. I am just…
TheIdiot
  • 491
  • 1
  • 4
  • 4
1
2 3
…
25 26