Is it a best practice to use SP (store Proc) for every single SQL call in .NET applications? Is it encouraged for performance reasons and to reduce surface area for SQL injection attacks (in web applications)? ...
We are currently reviewing how we store our database scripts (tables, procs, functions, views, data fixes) in subversion and I was wondering if there is any consensus as to what is the best approach? Some of the factors we'd need to consider include: Should we checkin 'Create' scripts or checkin incremental changes with 'Alter' script...
What are the pros/cons for including a date field as a part of a primary key? ...
My previous job involved maintenance and programming for a very large database with massive amounts of data. Users viewed this data primarily through an intranet web interface. Instead of having a table of user accounts, each user account was a real first-class account in the RDBMS, which permitted them to connect with their own query ...
I can read the mysql docs and they are pretty clear. But, how does one decide which character set to use? On what stuff does collation have an effect? I'm asking for an explanation of the two and how to choose them... Thanks! ...
In a one-to-many relationship what's the best way to handle data so it's flexible enough for the user to save the slave data before he saves the master table data. reserving the row ID of the master so the I can save de slave data with the reserved master id save slave data in a temporary table so that when we save the master data we c...
Hi, Does anyone know of a way to monitor table record changes in a SQL Server (2005 or 2008) database from a .Net application? It needs to be able to support multiple clients at a time. Each client will "subscribe" when it starts, and "unsubscribe" when it exits. Multiple users could be accessing the system at once and I want to refl...
I'm writing what will be an intranet application, and one of its features is roughly analogous to content voting - not unlike what SO, Amazon, and many other sites do. Assuming each votable piece of content has a unique ID, and each user (they're authenticated) has a unique ID, the easiest way would seem to be to have a "votes" table......
I found http://www.databaseanswers.org/data_models/ very useful. Any other suggestions? ...
Dear, I have a web application (ASP.net 2.0) that has a database (SQL Server) at the background. I'm considering ways how to handle database concurrency if two users insert the same data to the same table at the same time. Are there any way to handle this case? Thanks in advance. Jimmy ...
Is there a (free) tool or framework which allows defining views over multiple data backends (preferably for java and based on objects)? For example i have 2 databases: one database provides a table or class (doesn't matter much) PersonX(name, address), and the other one provides PersonY(name, dateOfBirth) Now i want to create a view wh...
What is a good book to become an SQL master? That is, learn how to tweak queries, table design, optimizing the server, etc. I use mostly MySQL and Postgres, but maybe a DBMS agnostic book would come handy. The "optimizing the server" part is probably specific to each DBMS. I would like to know of a good book to learn advanced SQL and d...
I have read lots of information about page caching and partial page caching in a MVC application. However, I would like to know how you would cache data. In my scenario I will be using LINQ to Entities (entity framework). On the first call to GetNames (or whatever the method is) I want to grab the data from the database. I want to save ...
Hi all, I have some databases that have four files each; one for PRIMARY, IDX, IMAGE, LOG their initial sizes were set to 200MB each but they are consuming far less space, about 100MB total. 1) is it possible to resize the database files while they are in use down to more reasonable sizes, say "current consumption" + 10MB? 2) is it p...
I have a table like so: keyA keyB data keyA and keyB together are unique, are the primary key of my table and make up a clustered index. There are 5 possible values of keyB but an unlimited number of possible values of keyA,. keyB generally increments. For example, the following data can be ordered in 2 ways depending on which key c...
Our organization provides a variety of services to our clients (e.g., web hosting, tech support, custom programming, etc...). There's a page on our website that lists all available services and their corresponding prices. This was static data, but my boss wants it all pulled from a database instead. There are about 100 services listed. ...
I have unevenly distributed data(wrt date) for a few years (2003-2008). I want to query data for a given set of start and end date, grouping the data by any of the supported intervals (day, week, month, quarter, year) in PostgreSQL 8.3 (http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC). The prob...
When using Castle ActiveRecord, is it possible to determine during runtime which connection string to use? As I understand it, ActiveRecord has to be initialized only once during the application's lifetime and this means that database connection strings have to be configured prior to initialization. However, is it still possible to det...
I've inherited a database that has a structure with a table of products, a table consisting of some product attributes and another table to build the relationship between these attributes and a given product. A user can filter the products by a combination of these attributes, meaning that if more than one attribute is selected only pro...
How can I create a empty .mdb file? I'm using ADO.NET and C#. Thanks! ...