141

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 ...


44

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 ...


42

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, ...


39

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 ...


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 ...


21

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: ...


12

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 ...


12

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 system is designed, there is only one application which has exclusive access to the database, so referential constraints and ID columns are not that important at ...


11

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) ...


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

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 ...


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

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.


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 ...


9

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 against going the varchar route. Use either a lookup table with valid values and an integer id with a unique constraint on the valid values, or an enum as it was ...


8

From user point of view, I would want to know whether whatever I am doing at given moment is going to fail as soon as possible. Which is why I suggest going for key-up validation. Few reasons for it: gives user nice real-time interaction and security feel (system is watching whether you do what you're supposed to correctly) helps user to focus at one task ...


8

Sql Server 2008 R2 Express has the following limitation: 10GB size limit per database No agent to run scheduled tasks Limited to run on 1 physical cpu (will use multiple cores on the single cpu) 1GB ram limit If you can run your app within these limits then you could upgrade from SQL Server 2000 to 2008 Express. Pricing: SQL Server 2012 Standard ...


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