The Stack Overflow podcast is back! Listen to an interview with our new CEO.
124

If you create one per query / transaction, it is much easier to manage "closing" the connections. I can see why common sense dictates that you should open one and use it throughout, but you will run into problems with dropped connections and multithreading. So your next step will be to open a pool, say of 50, connections and keep them all open, doling them ...


74

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 an idiot, and some excerpt from your codebase is likely to end up on The Daily WTF someday. You're absolutely right that his approach doesn't make sense, and ...


41

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 practical use. GUIDs are central to how Microsoft envisioned database clustering to work, and if you need to incorporate data from sometimes connected sources, ...


38

Best practice it to create one connection per query - and in the case of displaying data, best practice is to have the query bring in all the needed data in one go. Background information: In .NET, calling SqlConnection.Open() will by default always transparently use connection pooling (see "Using Connection Pooling with SQL Server" on MSDN). So you can ...


32

JOIN and INNER JOIN are the same, the inner keyword is optional as all joins are considered to be inner joins unless otherwise specified. The difference between JOIN and FULL OUTER JOIN is the same as the difference between INNER JOIN and FULL OUTER JOIN. An INNER JOIN will only return matched rows if a row in table A matches many rows in table B the table ...


28

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 millions, you are probably safe. A million millions, and the likelihood of a collision becomes significant. There's good news, though: you've already run out ...


27

NVarchar is used for Unicode. If your database is not storing multilingual data you can keep using Varchar. As an example: N'abc' simply converts your string to unicode.


23

By default SQL server uses the Windows-1252 character codes for varchar. It contains most of characters for latin-based languages (English, German, French, etc.) But it does not contain characters for non-latin based languages (Polish, Russian, etc.). As stated by @Pieter B, nvarchar is used to get around that issue because it is for Unicode which ...


18

Because MS SQL Server has poor support for UTF-8 compared to other RDBMS. MS SQL Server follows the convention, used within Windows itself, that "narrow" strings (char in C++, CHAR or VARCHAR in SQL) are encoded in a legacy "code page". The problem with code pages is that they have a limited number of characters (most are single-byte encodings, which ...


17

Without a sound grasp of database internals, you are bound to misuse it. Writing SQL which runs, and does its job, but without creating the proper index in the database may work in development, but works very poorly in production, effectively killing your database, and causing bottle-necks. Identifying a bottle-neck in itself needs a knowledgeable person, ...


14

Sounds to me that your interviewer was not looking for a data scientist answer but was simply looking to make sure you understand that "normalization" != "performance". So I'll keep this answer at the level that I'm guessing he wanted. Normalization means minimizing redundancy in stored data. Instead you setup relationships (often with foreign constraints)...


14

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 an issue Also, Performance aside, here are some reasons why you would want to avoid option 1 and 2. Cons if you go with 500 databases, 1 per each customer: ...


13

You've run into a problem that many have before you...a database optimized for reading is seldom good for write efficiency and vice versa. One approach that has evolved from this read-write impediment is CQRS (Command Query Responsibility Segregation). Despite Wikipedia linking the two together CQRS and CQS are technically different. CQS just demands that a ...


11

It's not silly at all. Consider the following possibilities: Store just HTML. That 's silly! Once stored this way, editing it would be painful: you either should decode it, or simply forbid any edition or force users to write HTML. Store just text. It may work. Until, maybe, you find that converting text to HTML is the bottleneck¹ which slows your ...


11

I would talk about how there are many things which can be done to improve performance. The first thing is always to investigate if the correct indexes are in place. Of particular concern in a normalized database is making sure FKs are indexed. Likely this would fix many performance issues. Other things to look at would be rewriting the SQL code to use more ...


11

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 GUIDs as a Primary Key: You can copy data between different shards of a cluster and not need to worry about PK collisions. It allows you to know your primary ...


10

Using stored procedures is one way, and has been in widespread use for many years. A more modern way to interact with SQL Server databases from C# (or any .NET language) is to use Entity Framework. The advantage of Entity Framework is that it provides a higher level of abstraction. To quote from Microsoft (https://msdn.microsoft.com/en-us/data/jj590134 ): ...


10

You might want to look at the answer to this similar question here: https://stackoverflow.com/questions/11329823/add-where-clauses-to-sql-dynamically-programmatically We've found that a SPROC which takes in a bunch of optional parameters and implements the filter like this : CREATE PROC MyProc (@optionalParam1 NVARCHAR(50)=NULL, @optionalParam2 INT=NULL) ...


10

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 into the database (or to keep them out). Parts which helps to reduce the network traffic might be a good fit for stored procedures. Parts which do heavy ...


10

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 you lose the ability to search the text which is the primary purpose of a database.


10

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 perfect tool. Some other reasons (scenarios) to consider using stored procedures in combination with Entity Framework would include: You have complex units of ...


10

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 metric. Methods and functions should be short. Very short. If you can break them down into anything smaller it's worth considering doing just that. But line ...


9

Generally, think sets. Try to avoid OO and procedural code for a while. Forget about the next .net framework if you're serious. I left most non-SQL work behind years ago and never looked back. Start following a more DB specific site actively like DBA.SE (which has development stuff too), even read the highest scoring answers to see coding techniques in ...


9

I prefer to put binary in the database, but that's primarily because I know where it is, and I don't need a systems guy to set up a network share or webserver. I can control who can retrieve the data through my application, rather than rely on permissions on the file system. I can do it on a very granular level very easily. I get much simpler transactional ...


9

We want to move to a web-based app so we can eliminate the dependency on our physical location for hiring new sales force members. If that is the main motivation behind your project, please consider not to rewrite the application. If I got your right, you will still have a defined user base, and don't want your app to be opened to millions of users (that's ...


9

This really depends on the DB system, but one major thing you have to consider with BLOBs is transaction processing. By externalization to the filesystem, one takes changes to the binary data out of the transactions. That will typically result in faster write operations, opposed to the situation where the DB assures you ACID compliance with full rollback ...


9

As deeply as you have time for. There is a lot of fluff these days about being "database agnostic", but unless you like the idea of lowest-common-denominator features defining your best efforts you need to know how your database works. You'll get better results on non-trivial projects if you decide to make your schema and database application agnostic ...


9

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 advantage of numerous infrastructure tricks to better enhance performance. The wins here are: simpler data backup -- database backups are fun and easy, no file ...


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