Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
5
votes
2answers
670 views

Surrogate key vs Natural key

I have a table called devices. Most of the devices that will get stored in this table can be uniquely identified by their serial number and part number. But there are some device types that do not ...
2
votes
1answer
1k views

Table valued function VS Stored procedure

There's a function that : returns only 1 row has only 1 parameter has multiple joins on different tables, each only to return a single scalar value is called only once at each login of a web ...
2
votes
1answer
324 views

Correct case for each reserved word in SQL Server? [closed]

Is the most correct spelling for int upper case INT or lower case int? There are many other reserved words in SQL Server. How to find out the best case for each word? For example the picture below ...
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. ...
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 ...
1
vote
1answer
31 views

Data integration name of best practices to avoid corruption, caused by becoming out of sync?

I have identified an issue in the design of a system and I am trying to find the right language to describe the cause of the issue so I can report it to managers, and get the issue fixed. The problem ...
1
vote
0answers
86 views

Best Practice - Database design for 'conditional join' [duplicate]

I need to create a database to store Jobs. Each Job can have 1-* Step Each step is classified as one of either Type A, B or C. Each Type has specific details and other tables that join on them so ...
1
vote
1answer
742 views

Named instance configured on dynamic default port

I was recently asked to take a look at at some SQL Servers for a friend and carry out a few config and security audits. There were a couple of servers belonging to the same application group which ...
-3
votes
3answers
251 views

How to administer large databases? [closed]

This is my situation right now: I'm working with SQL Server 2012, and I have 2 databases, one is 3.5 GB and the other one is 7.6 GB, and the company I work with is going to migrate the server to a new ...
2
votes
1answer
51 views

Maintainability of schema changes during runtime of an app [closed]

We have an application which accepts JSON objects from X number of third party services and unfortunately we're locked into using SQL to store it. The objects must be flat (no nesting), but each third ...
0
votes
1answer
44 views

How to query for something like “last active” per column in Access

Let's Say i have two Tables, let's call them PC-Info | ID | Hostname | Location | |:---|----------:|:-----------------:| | 1 | Hostname1 | This Room | | 2 | Hostname2 | That Room ...
0
votes
1answer
44 views

Availability groups taking time to change states

I created a 2 node AG setup as a POC today and when I tried to do an automatic failover by stopping the sql server service on the primary node, the one in the secondary went into resolving state and ...
1
vote
2answers
1k views

Is it bad form to use “LIMIT 1” on queries that should only return one row anyway? [closed]

If you have a query like this: SELECT * FROM table WHERE primary_key_column = 123 LIMIT 1; What do you make of the LIMIT 1? It is technically not necessary, as we are selecting on a unique column. ...
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 ...
0
votes
2answers
56 views

Best practice for table to table operation

I have a table loaded daily (let's call it A_table), with the data from this table I have to load another table (B_table). A is replaced every day (truncated and loaded anew), B is the final table, ...
8
votes
2answers
1k views

Is having relationships between separate databases bad practice?

I am working with a client that has multiple databases. There are several master level databases that have relationships back to them from instance level databases (application specific DBs). The ...
1
vote
0answers
46 views

Is the source system typically one of the fields used to uniquely identify business key?

One of the key components of designing a data vault is identifying enterprise-wide unique business keys ("business key" AKA "natural key"). It's not enough to use OrderID to identify records in an ...
4
votes
5answers
420 views

What is the best practice for selecting from two tables with multiple relations?

I have two tables with a structure like below: and am selecting the data with below query: SELECT (SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = DN) DN, (SELECT Name FROM ...
1
vote
1answer
50 views

Table or dictionary in code on a client for data that's not changed often?

Suppose I have a table where data won't be changed frequently. Say, a table "currencies" with a list of currencies which I accept on my website. I'd change it once in a while probably. What are pros ...
6
votes
2answers
653 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 ...
0
votes
1answer
595 views

Availability groups NIC best practices

I am planning to build a new AG setup in my QA environment with the following configuration. What I want to know is, can I have 2 heartbeat networks between the nodes for redundancy also, how many NIC'...
-1
votes
2answers
554 views

What's a good percentage/mix of used data, unallocated, and index space for a database

When I look at the Disk Usage report for my database [SQL SERVER] I currently have about 50% used space, 20% unallocated, and 30% index. Is there a good ratio try and hit? I know everything is ...
1
vote
1answer
265 views

Is it bad practice NOT to delete unused database entities? [closed]

A recent code review by a client revealed that there some database entities (tables, views, stored procedures) were no longer being used in our Oracle 12c database. We have hundreds of stored ...
1
vote
1answer
762 views

Should I be wary of ON DUPLICATE KEY UPDATE?

I'm new to MySQL, but I have reasonable experience with SQL Server. I know in SQL Server I should be very cautious about using MERGE statements because they can lead to some problems. Are there any ...
0
votes
1answer
404 views

Is it against normal form to include columns with date parts if there is a datetime column in the same table?

I am working on a table that will hold batch data. The table will have a BatchDate column as a DateTime type. I'm considering adding a column for BatchYear as an integer and BatchPeriod (which could ...
1
vote
0answers
70 views

What is the correct way to store multiple values of an entity's property with a “primary” or “default” one?

In previous database model I've had an entity that had a simple name property. Later I'd find out that a single name was no longer sufficient - rarely entities had multiple names and it was time to ...
8
votes
3answers
2k views

Should developers be allowed to use LocalDB vs a “development” instance?

Much like the vein of the question that was posted here previously around "Should developers be able to query production databases?" I wanted to get your thoughts on another particularly annoying ...
93
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,...
1
vote
1answer
64 views

refreshing 500k rows per day, what primary key to use? [closed]

Usually I create a PK of type int/bigint for every table. But in this case I have some doubts. I have a table which is updated many times a day, the total number of rows is around 500k, but they get ...
0
votes
1answer
140 views

Is it best practice to install client tools (like Oracle SQL Developer) on the database server or another machine?

A company doing a code review asked for credentials to an Oracle database server. They logged in but soon came back asking where SQL Developer was located, stating that they couldn't find it in the "...
7
votes
2answers
3k views

Is it okay to have a fill factor of 100 on the clustered index that is on an ID column?

So I have a table with a clustered index on an ID column (which is an auto-increment column) in SQL Server. Is it okay to have a fill factor of 100 on this since the data will always get written with ...
2
votes
3answers
139 views

Restrictions on user-defined queries

I have a client that wants me to implement a webpage that allows users to enter custom queries in a text box so they can directly query the SQL Server database. I am concerned about users entering ...
2
votes
2answers
2k views

Storing Address Data Model

I'm revising our customer database from a one-to-one relation to a one-to-many. Specificaly we are introducing a new billing address and a shipping address. When researching I came across this post ...
1
vote
1answer
199 views

Best way to create dynamic table for search

There is a table of documents: CREATE TABLE `docs` ( `doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `doc_uuid` binary(16) NOT NULL, `sender_uuid` varchar(40) NOT NULL, `receiver_uuid` ...
2
votes
1answer
193 views

Creating a helper UDF or stored procedure and limiting external access

I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'. I'm working on a security system and I want client ...
1
vote
1answer
158 views

OLTP to OLAP process causing deadlocks and impacting customers

I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. ...
0
votes
0answers
1k views

Is VSS Backup a better solution than the native backup?

Since i started to work as a DBA, i'm making normal backups ( with scripts, ad hoc and etc ). What would be the better solution for a backup? lets say I need to migrate a 2tb database to development ...
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 ...
1
vote
1answer
658 views

Mongodb performance: compound query vs moving to other collection

My application uses mongodb to store order information. At some point orders are sent after which I need to generate some reporting of all orders in a timerange. To give an idea of my order document: ...
1
vote
1answer
672 views

Key/value table: what is a good way to organize it if value could be foreign key?

Suppose a have a structure of two tables Table Key names: |_Key name_|_Parent_key_id_| | key1 | NULL | | key2 | key1 | Table Key values: |_Key__|_Value__|_Version__| | ...
6
votes
2answers
188 views

SQL perf issue - configure server correctly first or troubleshoot issues one by one?

This is for SQL Server 2012. I am troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc. Right off the bat, I can see ...
2
votes
0answers
48 views

What is the best way to keep track of 1000 RDS hosted databases?

I want to know what is 'best-practices' for managing an extreme number of RDS instances. I have no problem managing five. But managing dozens or hundreds, that's another task. I'm thinking use a ...
1
vote
2answers
529 views

How to Apply a Title-Case RegEx Condition for Object Names with Policy Based Management

I'm implementing PBM on a SQL Server 2014 instance and want to enforce a condition that all objects are created outside the dbo schema and all object names are created in title-case (i.e., OrderDetail)...
2
votes
1answer
1k views

Is EXECUTE AS OWNER in msdb a security risk?

I've been tasked with creating a system that allows non-privileged users to start and stop SQL Server Agent jobs. I'm creating stored procedures in the dbo schema in the msdb database using the WITH ...
6
votes
1answer
464 views

Principle of least privilege for sql server agent jobs

I've just read this article which advocates using the sa account for ownership of sql server agent jobs. http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account He has a couple ...
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 ...
1
vote
1answer
576 views

Login Schema Role User Setup

Environment: Windows Server 2012 SQL Server 2014 Standard Public website (Site-A) on Server Private website (Site-B) on Server Windows App running on workstations SQL Server 2014 Standard: 2 ...
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 ...
15
votes
3answers
634 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'...
11
votes
2answers
964 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 ...