Tag Info

Hot answers tagged

194

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


69

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


43

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


35

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


27

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


26

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


17

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.


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

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

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 you database, and causing bottle-necks. Identifying a bottle-neck in itself needs a knowledgeable person, ...


15

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


15

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


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


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

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


9

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.


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

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


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

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


9

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


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

You should never use multiple columns to hold more than one of the same item. This is bad database design and will be a major pain to use (try to write a query that will fetch and display all of a user's photos, or delete one photo, and you will see what I mean). Another issue is that you might want to store other info about each photo--such as when it was ...


8

Here's one simple, but massively useful benefit of enforcing foreign key relationships: ON DELETE CASCADE ON UPDATE CASCADE By enforcing a foreign key relationship with ON DELETE|UPDATE conditions, your RDBMS does a bunch of work for you, for free, that you then don't have to do in your code. Furthermore, this allows you to separate model logic from ...


8

That statement is kinda true, but not really. A Bloom Filter is a data structure that is useful for storing multiple hashes. Generally, you would implement it as an array of bits or ints where each cell in the array maps to a bit position in the binary representation of an integer -- usually, a hash. To see if the hash that you are looking for is stored in ...


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