74 votes

NoSQL within SQL Server

When he reviewed the database schema he stated that all foreign keys and other such constraints should be removed as this is business logic and should be applied within the business layer. Then he's ...
user avatar
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 ...
user avatar
15 votes
Accepted

500 databases or 1 database with 500 tables or just 1 table with all the records?

Personally, I'd go with option 3 because: It is normalized and simple Easy to query for reports Easy to back up(just 1 database to worry about) If you index the table well, performance shouldn't be ...
user avatar
  • 1,402
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 ...
user avatar
  • 186k
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 ...
user avatar
  • 2,378
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 ...
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 ...
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. ...
user avatar
  • 12.7k
10 votes

Enterprise software: Where should the code go?

Performance does neither increase or decrease because "you put most of the code into database" or because "you keep the code out of the database". The key point is to put the right parts of the code ...
user avatar
  • 186k
10 votes

Why does databases use text?

You are mixing serialize with compress. Can use XML serialzation to store a form or class. You can compress text and store it in a binary. You might get all of 7:1 compression. For that compression ...
user avatar
  • 1,937
9 votes
Accepted

Should I store images in my database or file system in this application?

Why not do both? Database is the backing, ultimate store for the image. The public side can be a simple read from db but you can easily extend that into a read-through disk cache and also take ...
user avatar
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 ...
user avatar
  • 394
8 votes
Accepted

When deciding a Primary Key, can I use Date part of a DateTime field

You can use a date field as a primary key, but you can't key on PART of a field. You'd have to make a separate field with just the date. This particular condition isn't something that would normally ...
user avatar
  • 2,074
8 votes
Accepted

How to infer the result of a query?

Reasoning about a query's meaning (i.e. the results it should produce) using the naive procedural approach you describe is perfectly fine. As you say, with complex queries it's often the only easy way ...
user avatar
  • 27.2k
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 ...
user avatar
  • 808
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 ...
user avatar
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 ...
user avatar
  • 38.3k
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 ...
user avatar
7 votes
Accepted

Is a series of OR or a single IN SQL statement better?

SQL Server will typically optimize the IN to the same as the multiple OR statements. Take this for example: use AdventureWorks; go select BusinessEntityID from HumanResources.Employee where ...
user avatar
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 ...
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 ...
user avatar
  • 54.8k
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 ...
user avatar
  • 24.8k
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 ...
user avatar
6 votes

Looking for advice for the following MS SQL Schema

Six joins doesn't sound like a lot to me, and I work with multi-tenant databases all day long. My advice is to join on your tenant ID at each step, and build your primary keys with tenant ID in them. ...
user avatar
  • 1,828
6 votes

Why does databases use text?

You are suffering from a common problem among engineers: that of over-optimization in one frame. The two classical constraints of computation are time & space. They are generally opposed; you ...
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 ...
user avatar
  • 13.2k
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 ...
user avatar
  • 11.9k
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 ...
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 ...
user avatar
  • 64.9k

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