Tag: MS SQL Server

..contributing to IT in London since 2002..

Recursive Relationships in Databases

One of the tricky issues in entity relationship model is the recursive relationships. Sometimes an entity needs to refer to itself. For example, think of an organisational chart for the roles of employees in a company; each employee is managed by a manager, each manager manages 0 or more employees. The records of all the …

To upgrade or not to upgrade XAMPP 1.7.3

XAMPP is just another package of: Apache HTTP Server, MySQL, PHP + PEAR + Zend Framework, phpMyAdmin, Perl, FileZilla FTP Server, Mercury Mail Server, OpenSSL, Webalizer, etc. like WAMP, EasyPHP (the first of its kind I’ve known), and others: http://en.wikipedia.org/wiki/Comparison_of_WAMPs XAMPP 1.7.3 includes: — Apache 2.2.14 (released in October 2009) and PHP 5.3.1 (released in …

Paging records in MS SQL Server 2005

After years of torturing their SQL Server users, Microsoft provided a “better” way of paging records in version 2005, and it is a weird one! The following example is from their website, an article about ROW_NUMBER (Transact-SQL).

It is simply unacceptable, non-standard, impractical! By the way, you can not use column numbers (e.g. ORDER …

MS SQL Server: Temporary Tables

Just a way of using temporary tables in Microsoft SQL Server, if you want to duplicate one or more records quickly, you can use commands like this:

This is much shorter if you have 50 fields in your source table. Otherwise, you have to specify each field and their data types;

MS SQL Anomalies 3

For those who are still using ancient data types such as CHAR be carefull if you are also using REPLACE function in MS SQL servers: Let’s test VARCHAR and CHAR fields on a simple table like the following:

The output is: (version MS SQL Server 2000) As usual, Microsoft developers are full of surprises!

Deceiving MS SQL Server: UPDATE Command

First version:

It does NOT complain and it seems all right, and runs without a problem, but since I selected the records with INNER JOIN and know that there are records that must be updated, we must find another way of updating those records. The problem is our table is referring to itself and …

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 …

MS SQL Server: Anomalies 2

We had a database in MS SQL 2000 server. The server/database was configured NOT to use recursive triggers. We backed up and restored the database on another machine with MS SQL 2005. Again the database is NOT using recursive triggers. However, our application started to behave strangely. On one of the tables, we had 2 …

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 …

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 …