Reputation: 41 Craftsman
- Active Members
- Active Posts:
- 123 (0.07 per day)
- 08-July 10
- Profile Views:
- Last Active:
- Apr 24 2014 06:53 AM
- OS Preference:
- Favorite Browser:
- Favorite Processor:
- Who Cares
- Favorite Gaming Platform:
- Your Car:
- Who Cares
- Dream Kudos:
Posts I've Made
Posted 11 Jul 2013Is there a version of SQL Server on your work machine? And has the database been attached to this instance?
Posted 17 Jun 2013First of all, a view is kind of analogous to a table, except that it is read-only. It can also be used to provide a more "logical" view of the data in a table by traversing foreign key fields or formatting data. Even simple things such as renaming fields from their database friendly name, to their user friendly name.
A stored procedure is most analogous to a function / method, inside a SP you can execute SQL code e.g. insert new rows etc. However what the main benefit of a SP is is that it is stored at the database level of an application; so, if you have a desktop application, web application, mobile application etc all using the same database you can write business logic code in a stored procedure and it can just be invoked from all the applications, any updates automatically get applied to all applications. Also (and possibly most importantly) is that stored procedures are compiled on the server - every time you send a query to the database it has to be checked and optimized etc - but with a stored procedure it is already done. They also help to prevent SQL injection attacks.
The drawbacks of views are that any keys you have on tables no longer exist; so querying large views is equivalent to querying an unindexed table, views do not accept parameters either, but stored procedures can.
In database speak there is two different things you can do with data, "project" and "restrict". If you want to project data (i.e. include fields, join tables, do unions etc etc) use a view, if you want to restrict, you should probably use a stored procedure, because these can have parameters. You can execute a stored procedure on a view as well.
Posted 12 Jun 2013So as I understand it you might have something like this:
And you want to exclude the second row because it is a duplicate of row 1? If this is the case you need to use DISTINCT.
SELECT DISTINCT C1, C2 FROM <view>
Posted 11 Jun 2013When referring to consistency in the terms of ACID properties; consistency is that the database should always be in a valid state after every single write (INSERT or UPDATE).
So the consistency of your database is defined by the requirements and rules you establish for it, so foreign keys constraints, proper data types and probably most importantly transactions (and their isolation levels). LInQ handles a lot of transactional work for you, but I think you should research into transactions and isolation levels.
A quick summary though: A transaction is used when you need an "all or nothing" approach to 2+ writes. So if you need to write to one table, and then update another, after the first write, the database is in an invalid state, until the update to the second table is done. If the write succeeds and the update fails, the database is in an invalid state (or more relevantly inconsistent state). Wrapping these two commands in a transaction will effectively isolate the commands; meaning they have only happened in the scope of the current execution. Committing the transaction will make it visible to all, and rolling the transaction back will erase all trace of it happening.
This may seem a rather trivial example, but if the update was actually an update to 1000000 rows, there'd be some time between the database being in consistent states.
Posted 4 Jun 2013MySQL and Access are both separate RDBMS; so a database created in MySQL will not be compatible with a database created in Access.
Usually RDBMS's can export their database schema so it can be imported in another RDBMS. However this relies on small implementations of the SQL standard in the RDBMS. Unfortunately, Access does not implement the SQL standard in the same way MySQL does so this feature is not appropriate.
Access is a very poor multi-user database, it doesn't really fulfill ACID. My advice would be to use MySQL where possible and only use Access when you absolutely positively need to (i.e. one user or there is only going to be two users using the database lightly). The problem is MySQL doesn't really have any good frontends like Access does. PHPMyAdmin is the one I've used the most.
yes your could still host it online, but you'd need a way to connect your front-end to the Access database engine
- Member Title:
- D.I.C Head
- 29 years old
- February 4, 1986
- Full Name:
- Andrew Pleasants
- Years Programming:
- Programming Languages:
- C#, Java, PHP, ASP.NET
- Click here to e-mail me