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
0
votes
0answers
10 views

Should I use the native user table (mysql.user) OR create a new user table? [closed]

In your opinion, I should use the native user table (mysql.user) or should to create a new table (CREATE TABLE user...)?
1
vote
0answers
25 views

Centralizing Databases

I have a very challenging scenario at hand and would like to hear suggestions on the solution I am considering implementing. I have several databases that have similar (non-identical) structures and ...
1
vote
1answer
42 views

How to delete foreign key with lots of references

Table B have several thousand records that references table A with a foreign key. The application sends a request to delete the row from table A. It's important that the synchronous operation would ...
3
votes
1answer
57 views

SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?)

We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ...
6
votes
1answer
89 views

Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In ...
3
votes
2answers
198 views

SQL table with same name as catalog view

When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be ...
0
votes
1answer
53 views

Flash storage and auto-grow

Our databases files (data and log) of (a lot of) SQL Servers are hosted on flash storage (SAN), what would you use as auto-grow property for databases having size round 500GB (data) and 150Gb (log)? ...
2
votes
0answers
51 views

Questions and advice for complying with Federal tax information (FTI) Security Guidelines for Federal, State and Local Agencies per Publication 1075

I've been able to find writings on compliance topics (e.g. HIPAA, SARBANES-OXLEY, PCI DSS) but I haven't found much on FTI (Federal tax information) and Publication 1075 outside of the IRS website ...
3
votes
1answer
386 views

Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at ...
0
votes
1answer
51 views

Do SET Statements happen in order?

Since I could not find it out here, do set statements happen in order scripted? Below is query I found. Would I get the same result every time it ran? DECLARE @Num VARCHAR(10) = '10'; DECLARE @...
2
votes
0answers
84 views

Disable and Rename sa, years after build

Answers and comments at a recent popular question Does changing "sa" password require a SQL restart (in mixed mode)? indicate that renaming and disabling sa at build time is a best practice. ...
12
votes
2answers
1k views

Does changing “sa” password require a SQL restart (in mixed mode)?

We discovered a SQL "sa" account being used in a way it should not have been, so we are changing sa passwords on all our SQL instances. (We have SQL 2005 through 2017 servers running in mixed ...
0
votes
1answer
66 views

Best Practices When Inserting Data (opinion question) [closed]

I'd just like to see what's the best practice when interfacing with a database. When inserting/modifying data in a database, is it best practice to do logic checking prior to interfacing with the DB (...
4
votes
2answers
99 views

How to store/organize all of these scripts?

I am about 10 months in to my DBA role and I am already accumulating quite the collection of scripts. Does anyone have any best practices or recommendations on how to store all of these scripts? I ...
1
vote
0answers
26 views

Strategy to manage foreign key from another system/database

There is a system in the company which stores all the consultants working for the company. Our project needs to store the consultants assigned to clients. We use the id of the consultant in our ...
0
votes
1answer
25 views

2 Different database or 1 Single database [closed]

I have a Windows 7 machine running MySQL database and I have a Linux machine running PostgreSQL database. Preferably I would like to have only 1 machine housing both database as currently they are co-...
0
votes
1answer
148 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
2answers
276 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 ...
21
votes
5answers
7k 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
2answers
378 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
148 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
174 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
372 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
899 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
27 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 ...
9
votes
1answer
4k 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
47 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 ...
3
votes
2answers
104 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
278 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
161 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
6k 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
53 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 ...
4
votes
3answers
2k 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
1answer
119 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
1k 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
55 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
45 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
46 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
184 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
137 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 ...
11
votes
2answers
4k 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 ...
25
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
48 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
39 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
44 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
237 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
59 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
584 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
288 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 ...