Skip to main content
62 votes

SQL as a means of avoiding "releases"

Problem scenarios like these tend to suffer from a very confused train of thought that mixes ideas. In an attempt to answer this, I am going to have to systematically unravel all of the discrete ...
Flater's user avatar
  • 52.7k
43 votes

Using a GUID as a Primary Key

GUIDs are by definition "Globally Unique IDentifiers". There's a similar but slightly different concept in Java called UUIDs "Universally Unique IDentifiers". The names are interchangeable for all ...
28 votes

Using a GUID as a Primary Key

Will this always be unique? Always? no, not always; it's a finite sequence of bits. Say I had a database containing millions and millions of rows with a GUID as the Primary Key. Millions and ...
VoiceOfUnreason's user avatar
24 votes

SQL as a means of avoiding "releases"

Monkey Patching It seems that the requirement from your boss is to have the ability to Monkey Patch the system while bypassing all other software development tools, processes and methodologies. Monkey ...
Ben Cottrell's user avatar
13 votes
Accepted

Relational integrity without relations

The reasons for such design decisions are often not technical ones, but organizational ones. I have seen this happen in the real world, in situations of the following kind: At the time when the ...
Doc Brown's user avatar
  • 210k
12 votes
Accepted

When should I use stored procedures?

You have a couple of perfectly good scenarios already. There are lots of other reasons too. EF is really good at CRUD and at pretty straight forward reporting. Sometimes, though, EF is not the ...
Joel Brown's user avatar
  • 2,398
12 votes

Using a GUID as a Primary Key

The GUID or UUID will very likely be unique because of how they are generated and they provide a safe way to guarantee uniqueness without having to communicate with a central authority. Benefits of ...
icirellik's user avatar
  • 429
12 votes
Accepted

Is using spacing effectively equivalent to the long method code smell?

Is using spacing effectively equivalent to the long method code smell? No. Never let line number concerns eliminate whitespace. If anything, this shows that line counting is not a good quality ...
candied_orange's user avatar
11 votes

Why does Microsoft SQL server default to failing on deadlock?

In general, the database doesn't have enough information to realistically retry the operation. It needs the application to retry. Imagine you have a banking application transferring $100 from A to B. ...
Justin Cave's user avatar
  • 12.8k
10 votes

Do we have 2 logical query processings, one with indexes and one without indexes?

tl;dr: Query planners are a big deal. In general, before an RDBMS backend executes a query, it first plans the query. There's more than one way to access the data, and the planner evaluates some of ...
J_H's user avatar
  • 7,600
8 votes
Accepted

Is it OK to create and drop databases during unit tests

The first thing to note is that, if you are creating and dropping DBs as part of your tests, then they aren't unit tests; they're better described as integration tests. Pedantic point aside, then the ...
David Arno's user avatar
  • 39.4k
8 votes

How do you approach one database per developer when working with a very large databases?

We have a similar size live database, but we don't develop against a full size copy. Each developer has an almost empty version on their own computer, which is used for most of their work, including ...
Robin Bennett's user avatar
8 votes

SQL as a means of avoiding "releases"

That code is in stored procedures inside a SQL-based database does not excuse it from version control, version management, testing, and all the usual dance steps of software development. For ...
Pablo H's user avatar
  • 665
7 votes

Why use using-statements when you want to reopen connections?

.net already uses connection pooling, so when you create/dispose a connection, you are not actually opening and closing a database connection, just fetching and returning a connection to the pool. So ...
JacquesB's user avatar
  • 59.9k
7 votes

How to make ability to order records in DB?

Four options for you. I like option 4 but the first one is easiest. Option 1. Just start one higher The values in the Order column don't matter, just their relative values. Whether you number ten ...
John Wu's user avatar
  • 26.6k
6 votes
Accepted

Partial name matching in millions of records

Without knowing the full details of what you need, you probably want to do one of the following: Use an existing search tool, like Sphinx or Lucene Perform n-gram approximate matching I don't fully ...
svidgen's user avatar
  • 15.1k
6 votes

Creating database connections - Do it once or for each query?

Developers sometimes want to optimize or simplify their code to re-use their database connection objects. Given the context of this question puts us in the .Net ecosystem, this is almost always a ...
Joel Coehoorn's user avatar
6 votes
Accepted

Determining the best way(s) of adding unit tests to a large project that makes good use of stored procedures

A stored procedure and the C# code that uses it are different units, so a single unit test can not test them both. You'll need separate unit tests: Unit tests that mocks the stored procedure and ...
Idan Arye's user avatar
  • 12.1k
6 votes
Accepted

Event sourcing vs SQL Server Temporal Tables

No. Temporal tables are not a replacement for event sourcing. They are complementary technologies. Temporal tables simply make a copy of the data to another table before updating or deleting the ...
Kasey Speakman's user avatar
6 votes

Using a GUID as a Primary Key

Person p1 = new Person(); p1.ID=GUID.NewGUID(); PersonRepository.Insert(p1); This is by far the most important reason for using GUIDs. The fact that you can create a unique id without your code ...
Ewan's user avatar
  • 77.5k
6 votes
Accepted

What is the difference between end of support and end of life?

End of Life == End of License The footnote on that same page reads: The following definitions will help you better understand MSL (Microsofot Support Lifecycle) terminology. Lifecycle: ...
svidgen's user avatar
  • 15.1k
6 votes

Logical separation of database content

While I'm not really an experienced architect, but as the question stands, you might not want to change much at all. You just add a Blogs table, and a "blog_id" column to posts (just as your "comment"...
Misamoto's user avatar
  • 229
6 votes
Accepted

Single database call and iterate in memory vs multiple database calls and multiple smaller iterations

"The less database calls the better" is demonstrably false. Were that the case, the preferred technique would be to load vast sections of the database into the application when the application starts ...
Robert Harvey's user avatar
6 votes
Accepted

Regulations about collecting data from software running at clients hardware (apps 'phoning home')

If you are UK-based you are subject to the GDPR. This regulation may or may not apply, depending on whether you are processing personal data (e.g. of customers or customer's employees), where personal ...
amon's user avatar
  • 135k
6 votes

How to do versioning for stored procedure?

You have, broadly speaking, two choices: Make the stored procedure backward compatible, Add a second, v2, stored procedure to the database and leave the original unchanged. The first approach can be ...
David Arno's user avatar
  • 39.4k
6 votes

How to do versioning for stored procedure?

Stored procedures don't support versioning. You will need to make a version of your database with two sprocs with the version in the name mySproc_v1() mySproc_v2() Your API can support versioning in ...
Ewan's user avatar
  • 77.5k
6 votes

Passthrough database views - what's the point?

The reason you would do this is it gives you the freedom to change the table however you like as long as you keep the “interface”, aka the view, intact. This could be a anything from putting it in a ...
jmoreno's user avatar
  • 11k
6 votes

SQL as a means of avoiding "releases"

we have no DevOps pipeline, Kubernetes, etc. due to IT department restrictions, so everything deployment-wise is manual at this point While you added it as a passing comment, this is the most ...
Ian Kemp's user avatar
  • 399
5 votes
Accepted

Using Entity Framework as substitute for SSIS, SSAS and SSRS

It would work but it is using the wrong tools for the job. The whole process here is DB-centric, so use the DB-centric tools. SSIS will be able to reads your datasources and update your data warehouse....
gbjbaanb's user avatar
  • 48.6k
5 votes
Accepted

How to make ability to order records in DB?

There are multiple approaches that you can use: The easiest one is to forgo the UNIQUE constraint on the column that specifies the ordering (either as linked list or as sequence number). It is then ...
Bart van Ingen Schenau's user avatar

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