Having decided to establish friendly relations with microsoft SQL server after an estrangement of nearly 15 years, I am playing around with SQL server on linux installed via docker. Here are some more of the interesting issues that I encountered.
Why on earth does SQL server define something called a table variable.
DECLARE @HelloWorld TABLE (
ID INT IDENTITY,
Why not use the more common temporary table? well mssql has them too, been having them for a longer time. One SO Q&A has a lengthy discussion on this and one thing that jumped out is:
Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option
When I first started on mysql ever so long ago, I always used temporary tables, those days mysql didn't have subqueries and all these years later, mysql subqueries are appallingly slow. On postgresql i've never ever created a temporary table just checked the syntax and it says
Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.
Mysql also follows the ansi syntax but behaviour is the same as in postgresql. sql server also behaves the same but the syntax is different
CREATE TABLE #HelloWorld (
ID INT IDENTITY,
Temporary tables are identified by a # prefix to the name.
Limit and Offset
the last version of MSSQL that i worked with was SQL Server 2000. That didn't have the LIMIT clause but had something else; TOP. If you wanted to use an OFFSET you had tie yourself up in knots. While I was away they had introduced window functions, so you can now use the
ROW_NUMBER partition function, which made things slightly easier.
In 2012, they apparently introduced OFFSET and FETCH keywords which is very different to offset in say postgresql. And the FETCH keyword is an unnecessary complicated way for doing LIMIT,OFFSET, complicated but at least it works. Here is a query on Northwinds
SELECT * FROM customers ORDER BY contactname OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
It must be added that LIMIT is not part of ANSI SQL but it's very common.
- Why you shouldn't use PHP + MySql + Apache bundles »
- « Changing the backend db of a live django app.