In both Oracle and MySQL, you can do:

create table T1 as select * from T1

This CREATE TABLE AS statement basically clones table T1 with its structure and data in T2. This can be pretty handy at times.

The equivalent of that in Sql Server is SELECT INTO. For example, you can do:

select * into T2 from T1

to achieve similar results.

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

…

Sometimes you need to think backwards.

Here was the problem. I needed to match up some IP address ranges to the company that owns them. Looking for a simple solution to the problem I came up with storing the IP address block patterns in the database as follows:

ip_pattern
----------------
127.%.%.%
192.168.%.%
10.%.%.%

Any idea why I choose % as the wildcard?

That's right - it's the wildcard operator in SQL for the LIKE statement.

So now when I have have an IP address 192.168.1.1, I can do what I like to call a backwards LIKE query:

SELECT company, ip_pattern
FROM company_blocks
WHERE '192.168.1.1' LIKE ip_pattern

This works on SQL Server and MySQL, and I would think it should work fine on any database server.

In both Sql Server and Oracle, permission changes to a user take into effect right away, even when said user is connected at the time you made the change.

In MySql, it is a little different, depending on how the permissions are given. If you use the GRANT statement, then it takes into effect right away. However, if you create user and give it permissions by manipulating the user table in the mysql system database directly, that is, using Sql statements, then you need to issue:

flush privileges

for those changes to be picked up.

Sql Server 2005 has a command line tool named sqlcmd. MySQL has a command line tool named mysql. Oracle has a command line tool called sqlplus. They can all be used for interactive query processing and batch scripts processing. They do similar things, albeit in different ways. They are functionally equivalent.

For Sql Server 2005, when in interactive mode of sqlcmd, use

:r c:MyFolderMyScript.sql

to read and execute a script file. You may have to type

go

afterwards, if the last line of the script file does not end with the word go.

To use sqlcmd in batch mode, that is, to run a sql script and then get out, use:

sqlcmd -i c:MyFolderMyScript.sql -S MyServerName -E

Replace -E with -U LoginName if you use Sql authentication

For MySQL, while in interactive mode of mysql, use

. c:MyFolderMyScript.sql (on Windows)

Note there should be a backward …

In Oracle and MySql, to get the column name and data type of a table, you can use:

desc MyTable

or

describe MyTable

The equivalent of desc in Sql Server is sp_columns. Therefore, run the command below will get similar results:

sp_columns MyTable

In sqlcmd, you can connect to a different server by this command:

:connect MyServer

The above command will attempt to use integrated / Windows Active Directory authentication. To use traditional Sql Server authentication, use this instead:

:connect MyServer -U MyLogin

You can do the same kind of thing in Oracle Sql Plus by using this command:

conn[ect] MyLogin@OracleSID

For mysql command line, use this:

connect -h MyServer -u MyLogin -p

That is assuming that you are allowed to login to the MySql server from whereever you are now.

VMWare recently released a freeware called VMWare Player that can play a pre-built virtual machine file. A virtual machine is an OS bundled with whatever the virtual machine creator put there. This is perfect for people to test-drive various operating systems and software, without going through the hassle of installing themselves. VMWare currently provides virtual machines preloaded with RedHat, Novell Suse, ubuntu, Oracle, MySql, and Bea, among others.

Memory used to be a bottleneck for virtualization software to take off. However, on the hardware side of things, both Intel and AMD are pushing 64-bit processors pretty aggressively now. With 64-bit architecture, the memory space the operating system can access increases exponentially (from 2^32 to 2^64). With the push towards 64-bit and the emergence of virtualization technology, I wonder what kind of impact this will have …

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column FROM table
ORDER BY RAND() 
FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM 
( SELECT column FROM table
  ORDER BY …