Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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.

4
votes
2answers
576 views

Move data from a table to another without losing data

I have a log table which is currently holding millions of records. I want to enable partitioning on that table, so what I did for now is: Created a partition function and a partition scheme. Created ...
1
vote
1answer
21 views

Approaches towards Marking Membership of Group?

Given this somewhat SQL pseudo-code: CREATE TABLE foo( id NUMBER(5) PRIMARY KEY, marked BOOL ); I'll keep track of whether ID's have been "marked" or not. Alternatively, I've thought to ...
5
votes
1answer
175 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 ...
0
votes
0answers
24 views

Best practice for record snapshot? (versioning?)

What's the best practice for storing a record snapshot? Use case example: Assume a user bought a product in an online store. It is a pleasant experience that the user can view a copy of what he saw ...
4
votes
3answers
65 views

Can a MS SQL Server be named different than host computer?

I have a virtual machine that was built by our IT department to host various processes. They named the computer something incomprehensible like "XYSVC105" and I am not allowed to change it. I have ...
3
votes
2answers
89 views

Using PostgreSQL functions instead of joining?

I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum. I have a function: CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$ ...
5
votes
1answer
134 views

How can we manage cross-database dependencies across environments?

I've put off asking this question for some time since it's tricky to encapsulate our situation and challenges without a wall of text, but the situation is getting worse so I'll do my best. I'm looking ...
1
vote
1answer
62 views

Using RDBMS with pseudo foreign keys VS using NoSQL solutions

According to some database administrators for our org, it is generally not advisable to actually enforce foreign-key relations within our MySQL DBs. Instead, it is preferred to simply represent this ...
1
vote
1answer
648 views

Best Practices for Using Temp Tables in Stored Procedures

I recently developed a stored procedure which essentially queries data from several different tables, manipulates it, and then inserts the result in a certain table. It ended up being one INSERT ...
1
vote
0answers
45 views

Best practice for large database

I have a mySql database and produce webpages through use of php. I have a database that in simplified form collects data of the form: date|name|score which is unique for date|name. From this ...
3
votes
3answers
136 views

Bad Practice to Store Calculated Data in Database? [closed]

Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database. Storing in the database avoids the need to calculate ...
1
vote
0answers
41 views

alternative to database for wildcard search of large number of records [closed]

I want to provide a website service that involves letting users do a wildcard search over a large list of strings (over 100 million strings). Since I've used a LAMP stack in the past and want to use ...
1
vote
1answer
52 views

SQL Server Database Tuning Advisor - Temp index being dropped

Recently I ran a trace on our database so I could then plug it into Database Engine Tuning Advisor and see what queries needed to be improved and where there might be a real need for indexes. While ...
1
vote
1answer
244 views

mongo 3.6 replication and write concern timed out

Not sure how to ask this. I have a process that is importing thousands of records to the database using a bulkwrite. I process 1000 documents and then write that and then get the next thousand and ...
1
vote
1answer
43 views

Special Considerations for SQL Server with 512 GB RAM / 64 Cores?

Apart from some Windows Hotfixes are there any special considerations (i.e. "things to worry about") when running SQL on a server with 512 GB RAM and 64 logical cores? I'm aware of some guidance when ...
0
votes
2answers
29 views

Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows?

I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction. As per the calculations, ...
2
votes
2answers
45 views

Schema For “Other - Add Comment”

Many user forms contain an option for "Other - Add Comment" where the user can provide an item not found in the given list. In other words, the developer sets the list of items. The user must either ...
2
votes
3answers
181 views

Storing Procedure Fields That Were Used

I am creating a database for production work (specifically lab testing). Most Work is for production, therefore performed strictly according to the Procedure for that Product. By itself, this is ...
1
vote
2answers
136 views

Best Practice to Store an Admin Configured Variable [closed]

Some background: Users submit a form that has a category. If (category has manager) { send manager email } else { send 'default' manager an email } For the sake of argument, manager_category ...
0
votes
0answers
18 views

Many table with different (even inherited) relationships with one another

The situation: Short explanation I have many to many relationships between almost everything and I want to know what would be the best approach; The situation: Extended explanation Let's call the ...
5
votes
1answer
1k 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 ...
24
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 ...
1
vote
1answer
43 views

Number of databases on a server. Should I try to consolidate these objects together? [closed]

Background Hi, as a BI developer I create various scripts for a middle-size company (200-300 employees). These scripts are used to manipulate / aggregate / clean data from different sources and ...
0
votes
1answer
37 views

Is using a Trigger to Insert a new Record into a View seen as bad practice? [closed]

So I was recently having a conversation with one of my new work colleagues and we got onto the topic of how best to INSERT data into multiple tables. Their teaching taught them to not use a TRIGGER ...
0
votes
1answer
40 views

Dilemma of Generating tables dynamically to improve performance

Background: Think of an application that lets people make surveys with custom questions, In a particular case, interview families, An interviewer goes to House 1 and interviews two members Member 1 ...
1
vote
0answers
95 views

What are best practices for managing DB record versioning with cross-reference tables?

In our system, we need to be able to track the changes to our objects and to be able to generate a history file for each such object. For the most part, this is straightforward: We can either create ...
2
votes
1answer
56 views

SQL application connection using one Windows user

We are currently looking to update an old application security structure and would like to go for an approach where the application connects to SQL via one Windows login with the required Read/Write ...
5
votes
2answers
324 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
666 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
144 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. ...
7
votes
3answers
450 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
30 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
45 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
308 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
118 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
32 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
40 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
39 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
608 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. ...
3
votes
4answers
3k 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
53 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
974 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
31 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
355 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
47 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
308 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
328 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
224 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
116 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 ...