Tag Info

Hot answers tagged

213

In my experience working on very large projects, you have to be very clear on where business logic lives. If you allow an environment where individual developers can put business logic in the business object layer or in a stored procedure as they see fit, a large application becomes VERY difficult to understand and maintain. Stored procedures are great for ...


89

Some Observations Stored procedures give you code reuse, and encapsulation (two pillars of software development), Only if you use them correctly in the context in which they are supposed to be used. The same claim can be said about functions (in structured programming) or methods (in object oriented programming), and yet, we see 1K functions and ...


65

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


46

The rationale is that relying on a stored procedure layer limits portability and ties you to a certain DB. Added maintenance costs are also cited as a reason. I also wanted to comment on this point you made: (stored procedures) protect you from SQL injection attacks It's actually the parametrized querying that protects you, which you can easily do in ...


41

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


36

I would add as an initial disclaimer that when you say DB and this question has tags indicating several platforms, that we are talking about a traditional relation database - i.e. a well-defined system which manages data in tables, columns and rows, according to Codd et al. This is a well-defined paradigm with well-understood boundaries. If you are talking ...


30

Some of the reasons that I agree stored procs aren't a best practice. Business and application logic should be in the code not in the database. Putting logic in the DB is mixing up concerns. You can't test stored procs as seamlessly as code in your conventional unit test projects with the rest of the application logic. I don't find stored procs as being ...


27

I can't speak for anybody else, but I usually just type SQL in "freehand", as you put it. Most of the visual query designers seem (to me) to increase complexity rather than reducing it. I should add that I don't write SQL anywhere close to full-time either.


22

How about a logical semantically correct naming system that avoids duplication, tautology and abbreviations? That and a Glossary and a logical / physical ER diagram of the database, preferably hosted on a Wiki of some sort, is about all you can do. Plus what Jason Holland says in the comment below! :-)


21

You don't need to "debate" this. You need to benchmark it. The SELECT MAX() is can be very, very slow and may get slower as the table gets bigger. There's little to discuss once you have measurements. Also, this is very likely to suffer from a race condition in that two concurrent transactions should be able to apply the same ID to a new record. A ...


19

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.


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


16

For me, a company's high regard for MS certs is the employer version of a "code smell". In other words, it's a fairly clear indicator that the company is not very good at evaluating a programmer's real abilities. The certification exams tend to focus on the minutiae of programming tasks; they're the programming equivalent of evaluating an architect by ...


16

Foreign keys should be enforced where it makes sense to enforce them. If a column must refer to something else then it should have a foreign key restriction. If the column might refer to something, but the business model suggests that it is not always the case, then it is acceptable to have a null in there, and have the foreign key present to ensure that, ...


16

This was the official line when I worked for one of the Big Five a few years back. The rationale was that since SPs are tied to particular implementations (PL/SQL vs T/SQL vs ...), they unnecessarily limit technology choices. Having lived through the migration of one large system from T/SQL to PL/SQL, I can understand the argument. I think it's bit of a ...


16

Stored procedures give you code reuse, and encapsulation (two pillars of software development), Yes, but at the cost of being able to meet other agile design goals. They're more difficult to maintain, for one thing. If the project I'm on is any indication, you'll likely end up with multiple, incompatible SPs that do essentially the same job, with no ...


16

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


13

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


12

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


11

I assume this is due to the history of SQL. According to wikipedia, SQL was formerly known as SEQUEL (Structured English Query Language) until it had to be changed because SEQUEL was already a trademark of another company. SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL ...


11

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


10

I'm sure it depends, but I never use visual designers. Once you have a good feel of SQL and think in SQL, writing it becomes second nature. I would have a much more frustrating time using any visual designer than just writing raw SQL.


10

All three of the companies I work for used stored procedures for their application logic with SQL Server. I have not really seen things the other way. But to me they are a big mess. There typically aren't very good error handling facilities or code re-use facilities with stored procedures. Let's say you have a stored procedure that returns a dataset you ...


10

Advantages of SQL Server over MySQL: Transactions. MySQL doesn't fully support transactions (any operation on MyISAM tables, as well as any DDL statements, will silently commit a pending transaction, which makes the transaction support practically useless) SQL Server, Visual Studio, and the entire .NET ecosystem, are built to work together. While you can ...


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


10

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


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


9

How do you version stored procedures on the server? If you redeploy stored procedures to the server from version control, you blow out the stored execution plan. Stored procedures should not be modifiable directly on the server, otherwise how do you know what's really running _right now? If they are not, the deployment tool needs access to write stored ...


9

Martin Fowler and Pramod Sadalage have written an excellent article on this subject: Evolutionary Database Design I suggest you follow his advice, e.g. that a dedicated DBA reviews the changes and merges them into the database master, while each developer can test things on his very own database. In my own company, we've also found that Database ...


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



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