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
1
vote
1answer
29 views

SQL Server evaluates where condition before or during the run?

I have a Stored Procedured that looks similar to this: CREATE PROC Campaign.STP_CampaignTransaction_Get ( @id INT @first_name VARCHAR(50) @last_name VARCHAR(...
1
vote
0answers
27 views

Best practices for users credentials [closed]

I am designing a C# client to connect to a MySQL DB and am having second thoughts on my design. The users have credentials in order login in the C# client, so now it is designed like this: 1 - the C# ...
0
votes
1answer
21 views

Best practice for exposing record IDs to API users

I am extending an API to include the ability to retrieve and update a specific record after the record is created by the user. Currently, these records are created with an auto generated integer key,...
0
votes
1answer
23 views

How to allow only a certain type of foreign key in a table

I'm designing a new DB. I have employee and order table. order table has deliverer_id to indicate who delivered the order. But only employee who is "deliverer" should be allowed. I thought of 3 ...
0
votes
0answers
14 views

How to design a SQL Database Table and workflows for Customer Data in a Order?

At the moment I practice with Python and SQL a lot, and for now I am trying to create a Order Workflow with the Data saved inside an SQL Database for testing. For example my Customer Table looks ...
0
votes
2answers
106 views

Database Design for Pokémon Review

I am trying to learn more about Relational Databases. So, I did a pokemon database gathering the stats available on the Smogon website. I have done some research thus far and would like to get some ...
0
votes
3answers
78 views

MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another

We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. ...
1
vote
2answers
125 views

How to restart MS SQL server right, what is best practice to do that?

I`m quite new to DB administration. I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens....
0
votes
2answers
580 views

Best Practice for Oracle SGA and PGA Memory Components Size

I have a Server that has 128GB RAM. I have read here and there that the size of SGA + PGA can bee 80% of the RAM. I wonder if there is any best practice for this configuration that could allow Oracle ...
0
votes
0answers
24 views

How can you effectively visualise a complex relational database?

I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that ...
3
votes
1answer
182 views

SQL Server Linked Server Best Practices - Step by Step

Hello and thanks for stopping by. I'm an accidental DBA looking for some guidance in creating Linked Servers the correct way. MS provides complete descriptions of the datatype and descriptions of ...
0
votes
1answer
54 views

DB Structure: one table to hold all domain values? [duplicate]

One database I recently start working with was designed with one main table containing all possible values, and foreign keys in other tables are specifying the nature of each of those items. CREATE ...
34
votes
7answers
8k views

What's a good use case for SELECT * in production code?

Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object). But I ran across a case now where I'm tempted to use ...
-1
votes
1answer
30 views

Is there a best practice design for handling different types of the same object which have different properties?

This may be a rather naive question and I am not even sure if I am asking it in the best way, so my apologies if not. I would like to know if there is a standard, or best way to handle cases where ...
0
votes
0answers
24 views

Mysql Sharding. Application Layer vs Mysql Layer

I am going to create shards in mysql table and I am stuck at a dileman whether to go for application layer sharding or let MySQL handle it. I mean, wether I should be figuring out which shard to go ...
0
votes
0answers
43 views

Condition table theory

Is there a good way to create a "table of conditions"? I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. ...
0
votes
1answer
28 views

Database Architecture Identifiers

I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, ...
1
vote
0answers
26 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
77 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
72 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
237 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
251 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
81 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
99 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 ...
4
votes
1answer
1k 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
65 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
430 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. ...
14
votes
2answers
2k 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
83 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
338 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
27 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
26 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
536 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
501 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
9k 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
899 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
256 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
307 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
650 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
1k 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 ...
12
votes
1answer
8k 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
93 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
114 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
495 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
235 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 ...
2
votes
1answer
8k 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
57 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 ...
7
votes
3answers
4k 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
159 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
2 3 4 5
7