5

I would start by using Windows Auth and @useself='True', which will use the source server's SQL Server Agent Service Account to make the remote connection. If you get a login failure, look in the target server's SQL Server Error Log to determine what Windows principal to create a login for on the target server. So the linked server definitaion would be ...


2

Rows are created with a Surrogate, numeric key that keeps the database nice and tidy but, for each record, is there a Natural key that would be meaningful to the Users? For example, a User might log into your Application using an email address. Each User record would have both a Surrogate, numeric, UserId that the User never sees plus their email ...


2

It depends. There is no one-size-fits-all answer. Yes, a database is a persistent store for data -- focus on that when designing the schema. Meanwhile, let me point out that DATE_FORMAT(last_edit, '%d/%m/%Y') AS last_edit, implies something that needs to be strongly stated: Store the data in a generic format: Store Dates and times in the database in a ...


2

"could this potentially affect SQL Server's read/write performance?" yes. Here are some things to consider: 1 - The type of file(s) matters in context of performance. Files which are often read/written to will of course share the resources with the SQL Server files. Files which aren't being accessed when SQL Server files are being accessed won't ...


2

is there any permanent pitfall where the damage done might be irreversible which makes this habit potentially "deadly"? Short answer: No. Very little you can do to your database is "deadly". That said, changing Primary and Foreign keys is a pain in most DBMSs and should be avoided, if possible. Also, you'll need indexes to support all of ...


1

I think this is very much personal (and team) preference. I do not consider the use of defaults bad practice. It's more a design thing, both approaches serve different purposes (in my opinion anyway). Without a default value, you force the caller to provide a value. And you express the fact that you can't anticipate a value if none is given, e.g. for a ...


1

As your procedure is written, you cannot pass only the @first_name parameter, since the other parameters has no default value. But I assume you meant them to have NULL as default, or that you will pass NULL for them. There is nothing special about NULL. SQL Server will create a plan based on the values for the parameters (aka Parameter Sniffing). That plan ...


1

Possible realization. Main table CREATE TABLE main ( id INT UNSIGNED PRIMARY KEY, type VARCHAR(16) ); Prepare main table for referencing ALTER TABLE main ADD COLUMN for_reference INT UNSIGNED AS (CASE WHEN type LIKE 'allowed%' THEN id END) STORED, ADD UNIQUE key_for_reference (for_reference); Create ...


1

Consider tools like: A. Red Gate SQL Clone, which is designed for scenarios like this. It will use following components: 1. You will restore single backup of the production database within the tool. 2. Tool will create virtual files for databases in all your environments and create a differential logs for each of them. 3. You will create separate database ...


1

Consider also replication. You can replicate only your tables so that you don't override any stored procedures or functions that the developers are working on. You can set up a publisher on production and 2 subscribers: one on TST and one on DEV. It's almost real time and very suitable for scenarios like yours. I always try to go for replication instead of ...


1

You almost certainly have a very large number of virtual log files (VLFs) in the problematic log file of the database in question. Shutting down and restarting SQL Server causes the recovery process to run on all databases on the instance. For databases that have a very large number of VLFs this process can be agonizingly slow. You can see the VLFs via ...


1

What version of Oracle are you using? Are you actually setting values for the SGA components and/ or the PGA separately? If so, why? If you're using a recent version of Oracle, the starting point would normally be to set memory_target and let Oracle size the various memory components automatically. Unless, of course, you have some reason to need to set ...


1

That design structure is commonly referred to as entity-attribute-value, or EAV. Some people hate it, others see the inherent beauty in its design. There is nothing wrong with the design, as long as it's carefully thought out, and implemented properly. Some of the more common "problems" people associate with EAV are: Good chance for poor performance ...


1

I have used a combination of GIT as well as naming files in a way that I can search for easily within vs code or similar. I will store file names as an example as the following. [Sales][Department].sql [Sales][Staff].sql [Profit][Staff].sql I will then be able to search for key works. If i make a variation of the script for a difference cut/flavour of ...


1

The answer is, as usual, it depends, in this case, primarily on whether your values are subject to change. Although there are lots of competing principles, the primary principle at play is that in a relational database you want to avoid updating primary (and therefore foreign) key values at all cost. Others might argue that some of the other factors you've ...


Only top voted, non community-wiki answers of a minimum length are eligible