Relational databases: some simple rules (part 2)

Tweet

This is the second part of the two parts article "Relational databases: some simple rules".

In the first part we have seen the ideas of tables and the way we can relate them.
And now... the conclusion!

Primary and foreign keys
We now need to move on a bit, but please keep in mind the example made in the first part of the article: two tables, with a relation for the state column.
The names table has the following columns: ID, name, surname, address, post code, city, state.
The states table has the following columns: ID, state.
In the persons table, the “state” column will contain only IDs related to the states table.
The ID column in the person table is a primary key. The ID in the state table is a primary key.
The “state” column in the persons table is a foreign key.
Quite easy, isn’t it?

Relational databases: some simple rules (part 1)

Tweet

It doesn't matter what we prefer to use, either Ms Access or SQL Server or Oracle, or even MySql, but when we want to create a new database, we really need to consider its structure, plan it and finally create all the database elements.
To do such a thing might seem easy – and in general it is – but a good development plan is what we need to avoid pitfalls and future issues with the structure of the database. The complexity of a database is surely one of the things to be considered, in fact it is clear that a more complex database will surely be more complex to develop.
Some simple rules can be taken into account and in this two parts article we are going to see some important points we need to keep in mind when planning a new database.

SQL Server: compare tables

Tweet

When we import, export or synchronise tables, it's a hard job if we have many records.
In the past, I've found some difficulties when I need to compare two tables in SQL Server.
The tables contained different records and I needed to consolidate them in the first table.
The first thing I wanted to do was to actually see which were the differences: I needed a list of records in order to understand the situation.
I've found a good solution for that, and let me say, a quite unexpected solution indeed.

SQL: AND vs OR (operators)

Tweet

In SQL Server we can use different operators in our queries: AND, OR, LIKE, BETWEEN and so on. In this short post we are going to see AND and OR because I've noticed that some could get confused by the results obtained when using them.

So, let's clear the situation a bit, because we really need to know what we are doing!

SQL: which columns should have an index

Tweet

In SQL tables might have primary keys and indexes. The primary key identifies each record. The key must be unique, it can't be NULL and each table must have just one key.
Indexes are something different: they are used to speed up the queries on a specific table. That means we absolutely need indexes on large tables which we often query.

SQL: subqueries

Tweet

In this short post, I would like to introduce the concept of subqueries in SQL.

A subquery is a query statement inside another query statement. As you already know the order in which a statement is processed is quite important. That is a basic rule like in maths: writing something like 2+2*5 is different than writing (2+2)*5. The first operation gives 12, the second 20.
In SQL the principle is identical.

SQL: the power of left, right and IN

Tweet

As the title mentions, in this short article I will show you how to benefit from the use of the left and right SQL functions together with the IN operator.
Before doing so, we need to understand what are the two functions and what the operator is doing.
Please follow me and see what we can do!

SQL Server: SQL Server maintenance solution

Tweet

Today we are going to see a wonderful set of utility, we might need to manage our SQL Server.
We are talking about SQL Server maintenance Solution.

"The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs. I designed the solution for the most mission-critical enterprise environments, and it is used in many organizations around the world. Numerous SQL Server community experts recommend the SQL Server Maintenance Solution, which has been a Gold and Silver winner in the 2011 and 2010 SQL Server Magazine Awards. The SQL Server Maintenance Solution is free."

ASP: database connection strings (basic)

Tweet

After a few days of deserved rest, here we get back to work! No that I want to, but I'm sure you are all waiting for new posts ... ... ... or not?

In the following short article I would like to list all possible database connection strings, just in case you need them in your projects. We will see just the basic way of connecting to data sources, so keep in mind that specific additional security parameters may be used, in case you need them.
Ready? Then please follow me...

SQL Server: linked server

Tweet

When working in a MS SQL Server environment, it is sometimes really useful to connect to another DBs. Those databases can be on the same machine or on the net, it doesn't matter. We can connect to those data sources using linked servers in MS SQL server.
In this article we will see how to do it.

SQL Server: useful query for DBAs

Tweet

If you are a Database Administrator and you work with MS SQL Server, there are some useful queries you could use to check how things are going with your databases. Being always aware of what's going on is very important, and in some cases a good control panel with appropriate queries can be developed just to quickly display the databases status.
We are going to explore some interesting administration queries. Just select those that you might be interested in and add them to your personal control panel.

SQL: wildcards

Tweet

While we all know about the "%" wildcard, we might not know that there are other interesting ways of using special characters when building queries.
I suppose you are aware of what a wildcard is. Anyway, if you're new to SQL, wildcards are special characters used in queries and they can be used to substitute one or more characters while retrieving data from a database.
We must remember that we can use wildcards only with the "LIKE" operator.

Before going into the available wildcards, let me show you a basic example:

SELECT productCode, description, price
FROM prodTable
WHERE description LIKE 'p%'

The above query will retrieve all the products that have a description starting with "p" from the prodTable table.
Now, let's see which are the available wildcards.

SQL SERVER & ACCESS: working on large tables

Tweet

When dealing with large tables, with big quantities of data, it might be a good idea to link a SQL Server table to Ms Access.
I know that the above statement doesn't sound too much professional, but in my experience it is incredibly easier to manage data using the Ms Access interface than using SQL Server Management Studio (SSMS).

Linking a SQL Server table to Ms Access is fairly easy. From the file menu we can find the appropriate command to link an external table. We actually have different choices, but what we need to do is to connect to the ODBC service and link to a SQL Server database. It is clear that the first step is to create a System DSN for the appropriate SQL Server database. To do so is quite easy, because the ODBC control panel will guide us through the procedure: just follow the steps and create the DSN connection.
Once we have done that, we can link a specific table inside the connected database directly from Ms Access.

When Ms Access has a linked table, we can freely work on the data contained and benefit from the flexible tools provided by Access. Some of this basic tools are not really available in SSMS and even every day shortcuts like cut, copy and paste are managed in an easier way through the Ms Access interface.

Some little tricks need to be follow, though.

SQL & ASP: custom queries for the user (a theoretical post)

Tweet

This post was conceived as a theoretical experiment on dynamic queries. The original idea was to let the user create custom SQL queries against a database.
 The whole thing is not so crazy, and it kept forming in my mind while I was thinking about it.

Imagine a situation where a user can select a table from the list of tables in a SQL Server database. That is not a problem; infact we can retrieve table names with:

SELECT name
FROM sys.Tables

SQL: how to update two (or more) tables at the same time

Tweet

******IMPORTANT NOTE******
Only now after a few years of comments, I understand the big confusion I generated, and I understand it's been my fault and for that I do apologize.
The query works but NOT in MSSMS: the query works in ASP or in a Ms Access environment with linked tables to MS SQL. I hope this clarify a bit...


Sometimes, when searching for an answer, we end up making things too much complicated, while easy solutions are just round the corner. This is the case of a simple task like updating two related tables with just one SQL query.

Suppose we have two related tables. The first contains user names, and the second email addresses related to the first table names.

First table ("names")

ID	name
1	John
2	James

The second table ("addresses")

ID	address
1	First Street
2	Second Street

How do we change the name and the street of the first record (with id equal to 1)?
With one simple query.

ASP: create an automatic email alert

Tweet

Recently, I had to create an automatic alert for a company intranet. The final user wanted to receive an e-mail - and just one email - a month prior to an expiration date. Basically I had a list of names and a related date which represent an issue date for a document. This document expires after one year and the final user needed to receive an email alert a month before the expiration date.

It looks like an easy task, doesn't it? In the following post, we are going to see the logic behind it and how to build the alert system.

Some assumptions

As usual we need to consider some things before going into the theory behind it all.
As said above, we need a table (called 'table') in our database with a date field. The date is representing our expiration date, and we call it 'exp_date'.
Then we need another field, that we can call 'email_notification'.
There will surely be other fields in our table, however, for the purpose of the following exercise, we don't really care. Let's say we have just another field, called 'name' (aside for the usual id field).
Thus, our table structure is the following:

id - int (unique)
name - varchar(255)
exp_date - smalldatetime
email_notification  - numeric(18,0) - default value = 0

That is all.

ASP: why am I still using it?

Tweet

I have recently debated with a friend of mine about my stubborn belief in classic ASP. If you are reading The Web Thought, you already know that I mainly program in classic ASP, but you might not know why I still use it, and probably will go on using it.
Here I'll explain my ideas.

A little bit of history

First of all I would like to be clear on one thing: ASP is not a programming language, strictly speaking, but a scripting engine. VBScript is the main language used in ASP pages. In fact, my first steps into programming were made trying to understand VBA (Visual Basic for Applications). So, please be aware that in this article I will refer to ASP as a general term, not strictly to the scripting engine.

ASP: conditional update or insert record

Tweet

There are situations when we need to insert or update a record in a table according to its presence. The condition is: if the record is already in the table, then update it, otherwise insert it.

Here we will see how to do it.

Some assumptions

To achive our goal, the first task is to determine which is the condition upon which we decide to update or insert the record.
I will show you an example, that will apply to a specific situation, however, after understanding the logic, you can think of something related to your personal goal.
In the following example, we have a table (table1) with 3 columns: id, field1 and field2. We have a form submitting data. Then there's another table (table2), linked to the first table through the id field. The columns of this second table are: id and field3.
The first table (table1) might have records linked to the second table (table2), while table2 already contains records. That is a classic situation in relational databases. The point here is that we know that a unique record is present in table2, and we want to insert a new unique record in table1 if it's not already present, otherwise we need to update it.
I hope that the above explanation is clear enough.

SQL Server: create a database with queries (the full life cycle of a database)

Tweet

When we think about a SQL Server database, we usually consider databases and tables as static objects while records contained as dynamic objects. What I mean is that we are inclined to build the structure of our database (the db itself, the tables, index and so forth), and then manipulate (insert, update, delete) the data contained in the tables. Some times we use temporary tables.
In this post we will see how to:

1. create a database
2. create a table
3. create an index
4. drop the index
5. drop or truncate the table
6. drop the database

The full life cycle of a database: from creation to destruction!

SQL Server: use the NewId() function to create unique identifiers

Tweet

A few months ago, I have published an article mentioning the NewId() SQL function. In that post, I was explaining how to create dynamic keywords and description meta tags. However the NewId() function can be used in different situations and it becomes very handy, when we need unique identifiers.
Let's see how it works.

The basics

The NewId() function returns a unique identifier; that means a unique set of numbers and letters. If we use the function in a query in SQL Server Management Studio like:

print newid()

the result will be a unique identifier like:

5C6C9372-49F9-4A5C-BCFA-DA6CDB525CAB

Interestingly enough, the above result is unique and can be used in various way. For example, we can use the function to generate personal identification numbers (PINs), login passwords, ids, serial numbers or product keys, simple primary keys etc. At the same time we can use the function to create random sorting of data.