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
49
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 ...
155
votes
5answers
390k 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 ...
91
votes
3answers
67k 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 ...
206
votes
6answers
75k 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 ...
94
votes
19answers
29k 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,...
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 ...
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?
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 ...
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 ...
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 ...
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 ...
38
votes
3answers
18k 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 ...
10
votes
2answers
19k 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 ...
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?
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 ...
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 ...
17
votes
3answers
93k 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...) -...
6
votes
1answer
5k views

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

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 ...
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 ...
11
votes
4answers
13k 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
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 ...
8
votes
3answers
3k views

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

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

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

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. http://www.brentozar.com/archive/2012/11/how-to-...
4
votes
2answers
746 views

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 ...
1
vote
1answer
922 views

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 ...
1
vote
1answer
258 views

SQL Server Database Table Partitioning Consideration

I have a SQL Server table with 42 million + records. This table acts as a staging area for transaction records loaded from a different system. Data from this staging table have to be transformed and ...
0
votes
1answer
954 views

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. ...
162
votes
19answers
48k 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 ...
44
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 ...
34
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 ...
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 ...
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 ...
12
votes
2answers
84k 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 ...
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 ...
2
votes
3answers
4k views

What are the best practices for triggers to maintain a revision number on records?

I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number ...
6
votes
3answers
5k views

Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster

SQL Server 2008 R2 multi-instance cluster (on VMware vSphere 5.1) 2 nodes, each running 2 instances (4 instances in total) 16GB RAM per node. The only time the instances are on the same node is when ...
15
votes
3answers
654 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'...
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 ...
6
votes
2answers
695 views

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

A clarification of MERGE best practices

I just wanted to ask a clarification about something on the MSDN from the "Optimizing MERGE Statement Performance" page. I am working with a data warehouse that takes in records from many different ...
6
votes
3answers
13k views

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

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

maintaining full text index on large table

I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching on a handful of ...
3
votes
1answer
19k views

Efficient way to insert/update/delete table records from complex query in Postgres 9.x

I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day. My initial approach was just clear data from my table and re-...
3
votes
4answers
9k views

How to properly increment an int identity in SQL Server if the Identity Increment isn't set?

When I use SQL Server with an int based identity column, I always use the auto increment. But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what ...