DBMSes are different...

Recently had to do some work with SQL Server. As my experience with it is quite limited I've got a few interesting impressions, which probably are absolutely trivial for SQL Server adepts :)

Do you know that SELECT query can create new tables?

OK in SQL Server it can. So I had to create backup of a table and firstly was quite frustrated not finding anything like CTAS (CREATE TABLE ... AS SELECT). I was on a wrong track however. The right syntax is SELECT INTO as in following example:

CREATE TABLE persons (
prs_id INT IDENTITY NOT NULL PRIMARY KEY,
prs_name VARCHAR(30) NOT NULL) ;

INSERT INTO persons (prs_name) VALUES ('GINTS');
INSERT INTO persons (prs_name) VALUES ('JOE');

SELECT *
INTO persons_bkp
FROM persons;

SELECT * FROM persons_bkp;
-------------------------
1 GINTS
2 JOE


So till now I was under the impression that SELECT is DML statement however obviously that's not true at least for SQL Server.

Do you know that SQL Server can handle recursive queries?

At least since version 2005 it can. Long ago I have heard that SQL Server has nothing similar to START WITH CONNECT BY. This site comparing SQL Server and Oracle seemed to approve my thoughts. However DBMSes are different and one has to look for different ways accomplishing the same goals. And in SQL Server one can use Common table expressions (aka query factoring clause or with clause in Oracle world) to get hierarchical results. And BTW here SQL Server "outfunctions" Oracle because it allows to recursively reference the same common table in its definition, which is not allowed by Oracle. Here are two articles if you are interested in how exactly it is done:

• Recursive Queries Using Common Table Expressions
• Recursive Queries in SQL Server 2005

So in conclusion these two features which are possible in both DBMSes, but are implemented in completely different ways supports my previous article that all databases are different :)