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

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.

0
votes
1answer
58 views

Is it good practice to create a separate table for views count?

I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for ...
8
votes
1answer
129 views

Database Design for Forecasting Review

I am trying to learn more about Relational Databases and I figured there is no better way to learn then to actually do something. I decided to make a personal attempt to look at Personal Budget ...
19
votes
5answers
5k views

Is it still best practice to avoid using the default ports for SQL Server?

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. On a server with a single, default instance, the following ports ...
1
vote
1answer
47 views

Is it best practice to revoke server permissions to the server “public” role on SQL Server instances?

We have evaluated the Microsoft "best practice" policies (located at C:\Program Files (x86)\Microsoft SQL Server\number\Tools\Policies\DatabaseEngine\1033) on each of our SQL Server instances (example ...
3
votes
3answers
90 views

“SELECT *” why is it an antipattern

On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can ...
2
votes
1answer
48 views

Storing sensitive information in a SQL Server project

I am very new with encryption in SQL Server so I am stumbling over things that require prior experience. I have done my googling, but I need reassurance so here we go: Scenario I have a SQL Server ...
5
votes
2answers
139 views

Best practice around non-production databases refreshes and TDE

Say we have a production system with several databases encrypted using TDE and a self-generated certificate. We regularly need to take this database and refresh our non-production systems. This ...
4
votes
2answers
682 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
23 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
899 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
25 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
69 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
93 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
147 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
115 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
2k 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
48 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
347 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
49 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
71 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 ...
2
votes
1answer
548 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
46 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
31 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 ...
6
votes
1answer
2k 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
45 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
42 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 ...
2
votes
0answers
133 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
427 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
793 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
192 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
829 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
46 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
425 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
158 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
36 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
41 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
40 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
833 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. ...
5
votes
4answers
5k 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
55 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
987 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 ...