Stack Exchange Network

Stack Exchange network consists of 175 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.

3
votes
1answer
51 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
66 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
195 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
43 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
46 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
226 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
49 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
57 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
62 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
80 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
25 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
103 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
240 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 ...
20
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
278 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
125 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
120 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
298 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
869 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
25 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 ...
7
votes
1answer
3k 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
43 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
98 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
235 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
153 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
5k 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
51 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
1k 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
105 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
54 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
40 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
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 ...
8
votes
1answer
3k 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
38 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
194 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
58 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
531 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
261 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
2k 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 ...