Wednesday, May 07, 2008

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:

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 :)

2 comments:

Patrick Wolf said...

Hi,

about the "SQL Server "outfunctions" Oracle" :-)

having a quick look at the provided links, it looks like that the "hierarchical queries" in SQL server can't really detect cycles or the additional flags the hierarchical queries in Oracle provides IS_LEAF, ...
I think that are not so unimportant features when dealing with hierarchical queries, especially the "is leaf"

Greetings
Patrick

Gints Plivna said...

Hello!

You are right it seems the only possibility is to set maxrecursion hint to control how many rcursions are being done. However my point about "outfunctioning" was that CTE's in SQL Server can be recursive and in Oracle cannot, nothing more :)
BTW another unknown feature for me was that in SQL Server CTE's can be for all DML statements not only for Selects :)