Dynamic keywords and description meta tags

After publishing the "Are keywords and description attributes useful?" post, I will explain how to create dynamic keywords and description meta tags. If you have a dynamic page which, for example, retrieves records from a table, according to query parameters, it is quite easy to populate the meta tags with relevant keywords or a description strictly related to the recordset itself.


Let's imagine you have a product page that is populated with product information based on parameters. That page is always the same and if you use relevant part of the fetched recordset to build up the keywords and description meta tags, you will finally have appropriate meta tags (which is very useful for your site ranking and indexing).
As you may understand - in that particular situation - it is very easy and I won't get into it.

Imagine now that you want or need to use a different set of keywords or descriptions. I would suggest to create two new tables, one containing your relevant keywords and one with your descriptions. That is a situation where you do not really need to strictly relate those new table records with the real content on the page. Now that seems to be exactly the contrary of what I wrote in the aforementioned post. Don't misunderstand me: I am not saying you should have keywords or descriptions completely uncorrelated to the real page content. It might be that you need to use some sort of rotating descriptions that are related to the page content, but not strictly.
Another situation might be when you do need correlated keywords and descriptions, but they are not present in the recordset needed to populate the page. In this case, you should establish a relation between the table containing the page data and the two tables (keywords and description). To do so you could create two related columns and filter data with a join or a specific where clause.

Ok, now what? We need to retrieve random records from the two tables and sql can help us with the Newid() function (introduced in SQL Server 2000). Using a simple query like:

SELECT TOP 10 *, NEWID() AS newid FROM keywordstablename ORDER BY NEWID()

you will get a random set of 10 records from the table. The recordset can then be used to fill up your meta tags.
Just to be clear, if you need to filter the keywords recordset, the above query is not enough, but I believe that - according to your situation - you can create an appropriate query. This could be an example:

SELECT TOP (10) *, NEWID() AS newid
FROM keywordstablename INNER JOIN tablename2 ON keywordstablename.ID = tablename2.ID

Other situations where you might need to use the NEWID() function: retrieve a random user, a quote, tips (useful for web applications) or whatever you like.

There are times when you need to fetch random records from a table, but you need to do it according to the specific column (I will use the id field in the example) of the table itself and in order. In order to do it, you can use the RAND() function. Let's  start from the beginning.
Declare the variables:

DECLARE @MaxValue int
DECLARE @RandomNumber float

Get the highest id from the table:

SELECT @MaxValue = Max(id) FROM tablename

Get a random number:

SELECT @RandomNumber = RAND() * @MaxValue

Query for the top 10 records:

SELECT TOP 10 * 
FROM tablename
WHERE id >= @RandomNumber
ORDER BY id ASC

In my opinion this way is not as efficient as the one using the NEWID() function and less "random". In addition if the RandomNumber is very near to the MaxValue, you might end up with less than 10 records.
Anyway, now you can use the best solution for your web page.

Implementing dynamic keywords and descriptions is not really difficult as you can see, but - as far as I know - useful. You just need a bit of imagination.
Enjoy!