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.
32
votes
5answers
10k 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 ...
7
votes
5answers
671 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
4answers
2k 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 ...
38
votes
12answers
4k 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 ...
16
votes
4answers
5k views
PostgreSQL Stored Procedure 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 ...
26
votes
5answers
9k views
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....
7
votes
3answers
2k 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 ...
4
votes
2answers
440 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 ...
0
votes
1answer
242 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.
...
14
votes
2answers
3k 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 ...
7
votes
2answers
5k 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...) ...
2
votes
3answers
2k 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 ...
13
votes
3answers
867 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
...
7
votes
1answer
740 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 ...
2
votes
1answer
728 views
What are the best practices for ETL of data from one OLTP system to another?
First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything ...
5
votes
4answers
3k 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 ...
3
votes
2answers
8k 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 ...
2
votes
3answers
291 views
designing multiple tables while eliminating duplication?
As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating.
What I'm trying ...
2
votes
1answer
421 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 ...
2
votes
2answers
438 views
What reason is there to create a physical table for a report output?
I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view ...
1
vote
2answers
4k 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 ...
1
vote
1answer
178 views
Should indexes automatically be placed into their own bufferpool?
We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX.
Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG ...
1
vote
1answer
471 views
What are the best practices for ETL of data from one OLTP system to another, Part 2?
Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, ...
0
votes
1answer
139 views
Relational database primary key requirements
I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc. The top-level entity corresponds to the business ...
