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.

learn more… | top users | synonyms

2
votes
1answer
408 views

Any problem with same conversation handle used by multiple services communicating with one target queue in a multiplexed service broker order queue?

I asked a question about implementing conversation groups in a SQL Service Broker setup which has several User Services communicating with the Server Service (Processing xml data requests to be sent ...
2
votes
0answers
269 views

going from heirarchical XML to heirarchical SQL tables then SQL to c# objects to go to user view, how should I store the data in the databases?

Hi I'm getting my data from a web service using their getData call and it comes in as fairly chunky heirarchical XML, first everything all at once, like <allData> <product1> ...
7
votes
3answers
187 views

How to handle reporting of an extreme amount of databases in SQL Server 2005?

I'm looking for some advice on how to handle reporting for our environment. We currently have 16 servers with 20 instances of SQL Server 2005. We have over 6,600 databases and growing across these ...
2
votes
1answer
476 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 ...
6
votes
2answers
901 views

Stored Procedures Best Practices: Fenced or Unfenced?

I believe I understand the reasons behind fenced and unfenced stored procedures. Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine ...
0
votes
1answer
404 views

Should I use mysql_upgrade after mysqld_safe when upgrading MariaDB?

I'm wanting to know the best practice to do a MariaDB upgrade on opensuse. I am getting this error: mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'a_database'': Cannot load from ...
1
vote
0answers
171 views

Enforce a hard limit on write execution time during Amazon RDS write stalls

I am trying to recover from the situation where Amazon RDS write operations stall (some discussion on why this is here). If I show full processlist, I will see something like this: 318263 myapp1 ...
3
votes
2answers
11k 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 ...
4
votes
1answer
95 views

What are the pitfalls when storing data retrieved from an api

I can get some financial data from our vendor via their API. In this case API is too strong a word. Basically we can get only an array of orders for a given time range. What we paid them for the ...
2
votes
1answer
630 views

How to design database for tree view with an infinite depth

I am trying to combine C# and SQL2005 for an application which need to have tree view control. One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That ...
2
votes
2answers
2k views

Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

I'm designing a system for multiple users to queue their requests to a web service which takes requests such as creating new entries, changing entries, requesting data. Many of the web service calls ...
3
votes
1answer
225 views

Optimizing queries against a set of tables that contain partitioned data

I have a third party program that creates and populates a series of tables to hold some historical data: data_33 -- contains data for 12/01/11 - 12/05/11 data_34 -- contains data for 12/05/11 - ...
1
vote
1answer
2k views

Best practices for ensuring up to date sequences after table import

We perform weekly table moves between several schema using expdp and impdp. Some tables have sequences associated with the ID columns and I'd like to ensure that the sequences are always up to date on ...
1
vote
1answer
370 views

Normalization: Should common columns of two tables be pulled out into a third table?

Should I further normalize my current design to eliminate redundancies? I have the following tables: empl: standard user table empl_cat: employee "category" empl_admin_cat: administrative ...
2
votes
3answers
330 views

What are the design alternatives when having multiple “type” tables

I am working on a database design and see myself with lots of *_type tables (e.g. user_type, product_type, etc.) where the structure of these tables is the same, basically: user_type ( id int pk ...
0
votes
1answer
134 views

Best practices in securing Oracle Standard edition one [closed]

I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application ...
8
votes
1answer
708 views

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

“Table of Constants” - is this common practice?

College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the ...
2
votes
1answer
511 views

Replicate SQL Data Inside Firewall to Outside Firewall - Suggestions

Currently I have an internal CRM application that houses all my data and runs my entire company. The front end is ASP, and the back end is SQL. The external site will run SQL What I'd like to do ...
14
votes
4answers
3k 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?
4
votes
2answers
439 views

Huge database logging of event type rows and ways to optimize it

We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years. Usual use of these events is selecting some of them from the ...
6
votes
2answers
331 views

Best practices for change management with indexes

Our IT shop is first starting to build a group of DBA's. All of us (myself included) have come over from the application development/architecture world, so the DBA world is still fairly new to us. ...
2
votes
1answer
781 views

tempdb on raid1 isolated or raid10 shared with high write db

PROPOSAL 2 8x600gb raid 10 mdf array 4x600gb raid 10 ldf array PROPOSAL 3 8x600gb raid 10 mdf array 2x600gb raid 1 ldf array 2x600gb raid 1 tempdb mdf/ldf array READ/WRITE STATS ON ...
3
votes
1answer
2k views

minimum privileges required for a user

I am configuring a 10g Express database on Windows 2003 that will be the back end for a public web server. There are many other things to do to harden the database but a good place to start is user ...
-3
votes
1answer
710 views

Best practices for finding and updating a handful of records?

Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".
7
votes
1answer
807 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
2answers
348 views

How to quantify TEMPDB usage in relation to other databases hosted on same hardware?

Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN: Config1 (sql 2008): 8 disks @ raid 10 data 2 disks @ raid 1 logs 2 disks @ raid 1 tempdb 2 disks ...
5
votes
1answer
832 views

What is the optimal way to design an approval queue and audit trail for entities in SQL Server 2008 R2?

Simplified Explanation: I have three main tables: customer, phone, and customer_phone to resolve the many-to-many. I need to ensure that only admins can insert/update these tables without approval. ...
9
votes
6answers
6k 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: ...
1
vote
2answers
10k views

How to join two table and show one query result in MySQL?

I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there ...
17
votes
1answer
4k 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 ...
2
votes
3answers
797 views

View Design Best Practices

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables the desired description field obtained through the FK id relationship both? ...
2
votes
1answer
8k views

Multiple left joins or subquery?

I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites. I have the ...
5
votes
2answers
4k 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 ...
80
votes
18answers
16k 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 ...
2
votes
2answers
957 views

Best practices for creating partition schemes and functions

I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning. My main question is: Should I use the same Partition Scheme and Function for ...
4
votes
1answer
923 views

Nested queries or temp (#) tables in SQL Server

I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' ...
2
votes
2answers
812 views

Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts

Normally in java or C# , I would use a following construct when try and catch errors. Bad Exception Handling in C# and Java try { // do something } catch(Exception ex) { } Good Exception ...
3
votes
1answer
222 views

Possible to add KEEPFIXED PLAN option to reduce the recompile in SqlServer2k5 on Production Env?

I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env? I'd know if we may gain performance after that or on the other hand it will be ...
15
votes
4answers
9k 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 ...
3
votes
1answer
486 views

SQL Globalization - DateTimeOffset

What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones? Which is best ? Use the SQL DateTime data type and insert ...
0
votes
1answer
148 views

What are the first necessary actions needed to help secure a database's schema & data? [closed]

This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions. It seems like there should be a good checklist out there for all to agree on ...
7
votes
5answers
721 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 ...
3
votes
1answer
690 views

Webapp & MySQL: Row Level Security

I am trying to emulate row level security on a webapp I am developing using MySQL. Using this method: Creating a database with the the required tables where the data pertaining to all the users will ...
2
votes
1answer
349 views

Decision tables in the database: a valid solution?

I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases. I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish ...
18
votes
4answers
6k 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 ...
1
vote
1answer
392 views

Storing Passport Numbers in a database

We're developing a travel/tourist application for a tours company who want to be able to input traveler information into an online database. Is there any security best practice for storing passport ...
1
vote
1answer
232 views

Design Tagging module in the database

I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
9
votes
4answers
3k 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 ...
2
votes
0answers
178 views

Stored procedures over SQL in the code, good idea? [duplicate]

Possible Duplicate: What are the arguments against or for putting application logic in the database layer? Assuming I got over the problem of maintainability and debugging. My assumption is ...