Tag Info

Hot answers tagged

200

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


74

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


44

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


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

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


28

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

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

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


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

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


11

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

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

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


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


9

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



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