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
201
votes
6answers
74k views

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 ...
162
votes
19answers
47k views

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 ...
155
votes
5answers
385k views

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 ...
94
votes
19answers
28k views

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,...
91
votes
3answers
66k views

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 ...
46
votes
12answers
6k views

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 ...
46
votes
4answers
33k views

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 ...
44
votes
6answers
61k views

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....
44
votes
5answers
137k views

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 ...
44
votes
4answers
31k views

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 ...
43
votes
1answer
22k views

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 ...
40
votes
2answers
106k views

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 ...
38
votes
3answers
17k views

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 ...
34
votes
1answer
30k views

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 ...
33
votes
1answer
15k views

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

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 ...
29
votes
4answers
28k views

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 ...
27
votes
7answers
5k views

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 ...
25
votes
4answers
3k views

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

I am used to working in very secure environments and so I design my permissions to a very fine degree of granularity. One thing that I normally do is to explicitly DENY users the ability to UPDATE ...
24
votes
7answers
4k views

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 ...
23
votes
4answers
56k views

What steps are necessary to safely restart a machine hosting Microsoft SQL Server?

Are there any special steps necessary to prevent data corruption when restarting a server hosting an MS SQL Server instance? For example, I recently encountered the recommendation of stopping the SQL ...
21
votes
5answers
8k views

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 ...
21
votes
4answers
7k views

Where should you define foreign keys?

Is it better to define foreign keys in the database or in the code part of an application?
21
votes
5answers
4k views

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 t1.id, t2.stuff FROM someTable t1 ...
17
votes
3answers
90k views

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...) -...
16
votes
6answers
37k views

What is the recommended RAID configuration for an Oracle database?

RAID (Redundant Arrays of Inexpensive Disks) comes with different configurations (RAID-0, RAID-1...). What is the recommended RAID configuration that I should set up and use when installing an Oracle ...
16
votes
2answers
36k views

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 ...
15
votes
3answers
7k views

What does a best-practice SQL Server maintenance plan look like? [closed]

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
15
votes
3answers
638 views

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'...
14
votes
6answers
12k views

Splitting Tables in MySQL. Good practice?

I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data. The tables look like this: [...
14
votes
2answers
5k views

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 ...
14
votes
1answer
6k views

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 ...
13
votes
2answers
2k views

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 ...
13
votes
1answer
44k views

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. ...
12
votes
2answers
2k views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get executed?...
12
votes
2answers
6k views

MongoDB: co-locate the mongos process on application servers

I would like to ask a question about a best practice described in this document: http://info.mongodb.com/rs/mongodb/images/MongoDB-Performance-Best-Practices.pdf Use multiple query routers. Use ...
12
votes
2answers
83k views

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 ...
12
votes
1answer
479 views

Best practices for connecting DBs that are in different geographical regions

We are about to set up SQL Servers in different countries. We need to link them, but we don't have to have a direct link (as in linked server). In other words, they can be loosely coupled. Is it ...
11
votes
4answers
2k views

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

There are two reasons that prompts me to ask this question: tSQLt The T-SQL testing framework tSQLt considers it an issue of "High Severity" when there exists columns with a non-default collation. ...
11
votes
4answers
12k views

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 ...
11
votes
2answers
8k views

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?
11
votes
3answers
3k views

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 ...
11
votes
4answers
6k views

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 ...
11
votes
1answer
5k views

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

I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives. Performance From my research, it seems that varchar(max) should ...
11
votes
2answers
971 views

Unused Indexes Best Practices

Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a ...
11
votes
2answers
5k views

If no plans to uninstall, can Log and Update Cache folders in Setup Bootstrap folder be removed?

I have several versions of SQL Server, used for testing, installed on my laptop (2012, 2014, 2016, and 2017). I noticed the other day that there was a folder containing prior versions of files across ...
10
votes
2answers
18k views

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 ...
10
votes
2answers
903 views

Can I losslessly decompose this table?

I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills. In essence, I have a table with the following primary key (PK for brevity): ...
10
votes
3answers
19k views

Storing time with milliseconds in database

Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds). I was originally going to ...
9
votes
5answers
3k views

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 ...
9
votes
5answers
10k views

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 ...