andy_pleasants's Profile User Rating: -----

Reputation: 41 Craftsman
Group:
Active Members
Active Posts:
123 (0.08 per day)
Joined:
08-July 10
Profile Views:
2,876
Last Active:
User is offline Apr 24 2014 06:53 AM
Currently:
Offline

Previous Fields

Country:
GB
OS Preference:
Linux
Favorite Browser:
Chrome
Favorite Processor:
Who Cares
Favorite Gaming Platform:
Playstation
Your Car:
Who Cares
Dream Kudos:
0
Icon   andy_pleasants has not set their status

Posts I've Made

  1. In Topic: Network related or instance related error occurred

    Posted 11 Jul 2013

    Is there a version of SQL Server on your work machine? And has the database been attached to this instance?
  2. In Topic: View vs. Stored Procedure

    Posted 17 Jun 2013

    First 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.
  3. In Topic: Remove duplicate consecutive records

    Posted 12 Jun 2013

    So as I understand it you might have something like this:

    C1 C2
    A B
    A B
    A C

    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>
    
    
  4. In Topic: What has to be considered in order to keep a database consistent?

    Posted 11 Jun 2013

    When 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.
  5. In Topic: MySQL vs. Access

    Posted 4 Jun 2013

    MySQL 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

My Information

Member Title:
D.I.C Head
Age:
28 years old
Birthday:
February 4, 1986
Gender:
Full Name:
Andrew Pleasants
Years Programming:
3
Programming Languages:
C#, Java, PHP, ASP.NET

Contact Information

E-mail:
Click here to e-mail me

Friends

Comments

Page 1 of 1
  1. Photo

    macosxnerd101 Icon

    16 Mar 2011 - 15:13
    Sorry about the accidental downvote there. :(
Page 1 of 1