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
  • 47.6k
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
  • 11.7k
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
  • 203k
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,388
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.7k
9 votes
Accepted

Use of booleans in a database

I get your co-workers point about the possibility of some cases where a bool could eventually become more than true or false - in some cases, but not realy this one. But I would very much suggest ...
Kritner's user avatar
  • 394
9 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
  • 4,754
8 votes

How does a query execution plan affect query execution?

If you're a programmer, you'll notice that SQL doesn't look much like normal code. Normal code is written in what's known as an imperative style: it tells the computer what to do. SQL, on the other ...
Mason Wheeler's user avatar
8 votes

Why are triggers seen as a last resort to solve multiple paths in cascading deletes?

I believe there is distrust of triggers because once they are created they are not well displayed in various interfaces, and don't communicate they are being run in very user friendly ways. This ...
Froome's user avatar
  • 808
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.1k
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
  • 598
7 votes
Accepted

Multi-tenant database design

In the multi tennent systems I have worked on as a DBA and Developer we used 1 database per client. That database was completely self contained and did not rely on any shared database, not even for ...
RubberChickenLeader's user avatar
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
  • 57.7k
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.2k
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

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
  • 14.6k
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
  • 72.4k
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
  • 14.6k
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
  • 133k
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.1k
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
  • 72.4k

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