If you ever get a message like this when trying to create a diagram in SQL 2005

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."

Here's step by step what you have to do:

Brian posted today a small review on Red Gate's SQL Prompt, and SQL Completion Tool... the best part: it's free for a limited time! I haven't checked it yet, but it looks amazing, a must have I would say. SQL Prompt works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, and UltraEdit32.

SQL Prompt provides Intellisense? style auto-completion for Microsoft SQL Server editors. It will tell you the exact formatting you should use for your SQL commands, while you are writing them and will therefore help you write fast, perfectly formatted SQL statements.

Ray posted a nice entry about ColdFusion and Pagination, useful and easy to implement. I commented that it should only be used for small recordsets, because even if you do not show all records, transferring 100k entries from the DB to the application server to only display 10 or 20 is not efficient at all.

Some people suggested using LIMIT and OFFSET, which can only by done with mysql.... I hate to admit it, but mssql is so much better than mysql, expect for that ... I can't believe that we still don't have that functionality... anyways, Adam also posted a link to an msdn article explaining a couple of different approaches to tackle this problem.

I, on the other hand, have been using a stored procedure for years to achieve this goal. I have it in production retrieving pages out of a table with over 5 million records... at it works flawlessly. Let me add that you do need indexes, defragging, maintenance, lots of RAM ... this SP doesn't do miracles :)

You can download the SP here. It works perfectly with mssql 2000 / 2005. Let me emphasize that I did not write this. I got it from a sql exchange article a few years ago. Yes, it can be improved I guess, since one of its major limitations is lacking of a way to query over left/right outer joins. You can query aggregated functions, embedded  selects, inner joins (ansi format) ... You can still get around them using views, which is how I've done it in the past... in fact, if the query is too complicated, you might as well write a view for it anyways.

As far as the ColdFusion side, here's an invocation I have to one of my tables:

If you need to count all rows from a table without any conditional, there's a much better and faster way than select count(*). This code works with Microsoft SQL 2000 and 2005.

This is old news, I know ... but many people don't know it ... When using Views in Microsoft SQL 2000 (haven't checked 2005), after altering a table the view will not update itself automatically ... it will get completely messed up ... the views point to the column number, not name, therefore after adding a field, some of your columns will be shifted.

The solution is simple, yet confusing if you're not aware of it ... you can click edit the view, and all your columns will show fine, but if you open the view they'll be wrong ... all you have to do is edit the view, save it again, and close ... simple, but necessary ...

A co-worker ran into this today and lost precious hours until he chose to ask :) now you know, and knowing is half of the battle.

SQL 2005 just came out, and since I am at a point where I really need to upgrade, I decided to look for options. Options include MySQL 5.0 (right), PostgreSQL, Ms-SQL 2005, and Oracle 10g.

I found several documents and interesting articles comparing this DBRMS’s…

Informational:
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_mysql.htm
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp
http://www.mysql.com/news-and-events/news/article_976.html

Critiques:
http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx

Comparisons (excellent):
http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html

And last but not least, a PDF document called Features, strengths and weaknesses comparison between MS SQL 2005 (Yukon) and Oracle 10g databases (attached).

Well, out of the batch, mysql is out… I’m looking for a reliable and scalable DBRMS that can handle hundreds of transactions per second in an active/active cluster environment. The introduction of views and stored-procs was interesting, it’s still missing essential locks, rollbacks, constrains, data validation, and I’m pretty sure that if I start playing with cursors and t-sql it will break.

PostgreSQL still doesn’t fully support the clustering I’m looking for, but I’ll keep looking, I might be wrong. Ms-SQL 2005 seems very promising, and I read some articles claiming that clustering is easier. I had a horrible time accomplishing this with SQL 2000 because another requirement is no disk-arrays… the servers are not even in the same continent.

I know Oracle will fulfill, but I have to study the price impact and code migration time. I’ll be installing ms-sql 2005 this week or weekend, and hopefully I’ll elaborate more early next week.

Database Mirroring (Extend log shipping, automatic failover to a standby server)

SQL Server 2005 significantly enhances the capabilities of log shipping by providing a database mirroring option. Database mirroring allows continuous streaming of the transaction log from a source server to a single destination server. In the event of failure to the primary system, applications can reconnect to the database on the secondary server almost immediately, without waiting for recovery to finish. The secondary database instance detects the failure of the primary server within several seconds and accepts database connections almost immediately after detecting the failure. Unlike failover clustering, the mirrored server is fully cached and ready to accept workloads because of its synchronized state. Well, what this really means is that you need three MSSQL server instances. However only a single instance is actually active and then up-to-date standby database should be available (which can not be accessed by users until failover)

Oracle has a very similar mirroring as compared to MSSQL by having Oracle physical standby with dataguard. The only difference is that with Oracle just two instances are required: active and passive.

The SQL odyssey continues. Today I found myself writing a trigger for a VoIP call detail records table where some numbers came with special technical prefixes; the format was 945#5555555555… or sometimes even 123#456#5555555555

So instead of looping through the # signs with charindex, I decided to write ListLen and ListLast function to almost complete the collection.

In the trigger I had statements such as

SET @number = dbo.ListLast(@number,’#’)

Really helpful.

Get the functions here.

So say you did run into a comma delimited field, but this time with IDs. To make things worth, there is a second comma delimited field that contains data related to the first field in the same order.

E.g.
Field1 (ids): 1,3,5,8,10
Field2 (prices): 10,44,18,40,115

Your task: you need to first the price for ID number 5.

Ok, so now in you coldfusion code you could query the entire table, loop through the records, add an extra column, and user querysetcell to match all prices for the specific ID. Of course, the ID might be in different positions for every row.

If definitely defeats the definition of relational database, but I have seen this time after time and it is extremely handy to have the solution already written by the time you need it.

Solution: Let’s combine the UDF ListGetAt with the ListFind that I wrote in my previous blog.

The ListFind will return the position for the ID in every row while ListGetAt returns the token at the specified position. It works just like it would in Coldfusion, but you can apply it to an entire recordset.

For example, let’s find the price for ID number 5 in the previous example

Select dbo.ListGetAt(field2,dbo.ListFind(field1,5,’,’),’,’) as thisPrice from table

Download the SQL function here.

Some time we run into bad database design were a single fields contain a comma delimited list of pointers or attributes; sometime we even do it ourselves to kick out some quick database… for example in a typical Coldfusion security framework you may have a database field with a comma delimited list of roles in the users table.

e.g.
uname: rob; roles: admin, dbadmin, user; datestamp: 6/13/2005

So, you may query the DB with a bunch of like operators which may or may not be accurate. You cannot query the DB with like ‘%admin%’ because it will return true if you’re an admin or dbadmin.

Of course you can use like ‘admin,%’ or ‘%,admin,%’ or ‘%,admin’ or ‘admin’… but that’s too much!

Here’s where my small SQL UDF’s come handy. I replicated the ListFind coldfusion function as a SQL function.

You can now query from the users table

select * from users where dbo.ListFind(roles,’admin’,’,’) > 0.

The third parameter works just like in CF so you can specify other characters such as pipe ‘|’ as a delimited if needed.

Download the SQL function here.