database

Storing a file in a database as opposed to the file system?

Generally, how bad of a performance hit is storing a file in a database (specifically mssql) as opposed to the file system? I can't come up with a reason outside of application portability that I would want to store my files as varbinaries in SQL Server. ...

Default database IDs; system and user values

Hi As part of our current database work, we are looking at a dealing with the process of updating databases. A point which has been brought up recurrently, is that of dealing with system vs. user values; in our project user and system vals are stored together. For example... We have a list of templates. 1, <system template> 2, <syst...

Oracle XE or SQL Server Express

Hi people, I'm starting a new project here (Windows Forms) and was wondering what's the best option today for a small (free as in beer) dbms. I've used SQL Server Express on the past projects, but time and time again I hear people saying that the product from Oracle is faster and more powerful. It will be used in a small company (aroun...

What is a good way to open large files across a WAN?

I have an application deployed into multiple zones and there are some issues with opening larger documents (20-50MB) across the WAN. Currently the documents are stored in Zone 1 (Americas) and a link stored in the database to the docs. I have heard some things about blobs in oracle and store binary in sql server 2005. And then per...

SQL Database documentation?

specifically, I use SQL Server 2005, but the solution might be brand independent. Need: You have 40 database tables, in some, you may have a column which is a integer values 1-9 representing some enum. Sure it makes sense in code, but not if you need to SQL the DB in a report getting only rows of type "active" but you have no clue if ac...

What FoxPro data tools can I use to find corrupted data?

I have some SQL Server DTS packages that import data from a FoxPro database. This was working fine until recently. Now the script that imports data from one of the FoxPro tables bombs out about 470,000 records into the import. I'm just pulling the data into a table with nullable varchar fields so I'm thinking it must be a weird/corrup...

What's the best database storage device?

So, the answer should probably be presented in tiers for the size of the application, but I'm wondering about people's experience with choosing where the disk drives used by the database should reside. Here are some of the options: JBOD - (just a bunch of disks) Old fashioned internal disks - fast but not very expandable NAS - Slow bu...

Best tool for auto-generating SQL change scripts for SQL Server

I'm doing a survey for the best SQL change script generators out there, specifically for MS SQL Server. I do know of Redgate SQL Compare, but I'd like to know what others use, and if there are free versions of such software that I could try. Any suggestions? ...

Fast database access test from .Net

What would be a very fast way to determine if your connectionstring lets you connect to a database? Normally a connection attempt keeps the user waiting a long time before notifying the attempt was futile anyway... ...

HOWTO - Compare a date string to datetime in SQL Server?

In SQL Server I have a DATETIME column which includes a time element. Example: '14 AUG 2008 14:23:019' What is the best method to only select the records for a particular day, ignoring the time part? Example: (Not safe, as it does not match the time part and returns no rows) DECLARE @p_date DATETIME SET @p_date = CONVERT(...

How big would such a database be?

I'm trying to figure out how big a certain database would be (it hasn't been created yet). I know how many rows and what the tables will be. Is there a feature in Oracle that will tell me the size of such a theoretical database? Is there a known math formula I can use? I know there is a feature to determine the size of an existing databa...

Good Resources for Relational Database Design

I'm looking for a book/site/tutorial on best practices for relational database design, tuning for performance etc. It turns out this kind of resource is a bit difficult to find; there's a lot of "here's normalization, here's ER diagrams, have at it," but not much in the way of real examples. Anyone have any ideas? ...

What's your worst database accident happened in production?

For example: Updating all rows of the customer table because you forgot to add the where clause. What was it like, realizing it and reporting it to your coworkers or customers? And, what's your lessons learned? ...

Database Case Insensitive Index?

I have a query where I am searching against a string: SELECT county FROM city WHERE UPPER(name) = 'SAN FRANCISCO'; Now, this works fine, but it doesn't scale well, and I need to optimize it. I have found an option along the lines of creating a generated view, or something like that, but I was hoping for a simpler solution using an in...

What is a good way to denormalize a mysql database?

I have a large database of normalized order data that is becoming very slow to query for reporting. Many of the queries that I use in reports join five or six tables and are having to examine tens or hundreds of thousands of lines. There are lots of queries and most have been optimized as much as possible to reduce server load and incr...

What are the advantages of using a single database for EACH client?

In a database-centric application that is designed for multiple clients, I've always thought it was "better" to use a single database for ALL clients - associating records with proper indexes and keys. In listening to the Stack Overflow podcast, I heard Joel mention that FogBugz uses one database per client (so if there were 1000 client...

How do you keep two related, but separate, systems in sync with each other?

My current development project has two aspects to it. First, there is a public website where external users can submit and update information for various purposes. This information is then saved to a local SQL Server at the colo facility. The second aspect is an internal application which employees use to manage those same records (conc...

How should I handle autolinking in wiki page content?

What I mean by autolinking is the process by which wiki links inlined in page content are generated into either a hyperlink to the page (if it does exist) or a create link (if the page doesn't exist). With the parser I am using, this is a two step process - first, the page content is parsed and all of the links to wiki pages from the so...

How to organize dataset queries to improve performance

I don't know when to add to a dataset a tableadapter or a query from toolbox. Does it make any difference? I also dont know where to create instances of the adapters. Should I do it in the Page_Load? Should I just do it when I'm going to use it? Am I opening a new connection when I create a new instance? This doesn't seem ...

[ADO.NET ERRROR]: CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file HelloWorld.mdf failed...

Anyone seen this before? CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:\Documents and Settings\..\App_Data\HelloWorld.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. I'm asking on a friend...