Broken SQL objects: Views, functions, stored procedures, triggers

Designing a perfect entity relationship diagram for our database together with a marvellous table structure is never good enough.. Have look at the following example (for MS SQL Server) creating two tables and a view:

A view, a function or something else, we try to write re-usable code so that we do not have … Read moreBroken SQL objects: Views, functions, stored procedures, triggers

Update Query on MS Access

Microsoft programmers are always very particular on their way of approaching problems. Since they are the “biggest” software developers in the world, they have to set the standards!? Problem: Updating field(s) of a table using a complex (sub) query. Solution: 1. In standard SQL, you can this:

It makes sense, right? No, not according … Read moreUpdate Query on MS Access

Firebird SQL: The Power of Simplicity

Firebird SQL Server has always been a “proper” database system since it forked from Borland’s famous Interbase. I just love it! In some big database applications, I found that the developers preferred to duplicate a set of tables for probably performance reasons. For example, in most accounting databases, there is a notion of company so … Read moreFirebird SQL: The Power of Simplicity

MS SQL Server: Using Remote Servers (Linked Servers)

Sometimes I need to have a quick way of transferring data from one database to another running on a separate machine, especially when the table structures are identical or similar, it saves time. M$ SQL Server 2005 has a feature called linked servers. Give a name to your server: SERVER123 Choose a provider depending on … Read moreMS SQL Server: Using Remote Servers (Linked Servers)

MS SQL Server: Anomalies

Backup/restore databases: After the restore, you must revoke (sp_revokelogin) and grant access (sp_grantlogin) to users again, esp. if you are restoring the database on a different machine. Note: I use mixed mode authentication and prefer not to use Windows or domain users. VARCHAR or TEXT: Pro VARCHAR: They recommend to avoid using fields of type … Read moreMS SQL Server: Anomalies