Getting back to SQL Server

2016 Dec 24 at 15:02 » Tagged as :microsoft, sql, partition,

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.

Table Variables.

Why on earth does SQL server define something called a table variable. 

DECLARE @HelloWorld TABLE (
    ID INT IDENTITY,
    Description VARCHAR(1000)
)

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,
    Description VARCHAR(1000)
)

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.

SELECT  *
FROM    (
        SELECT  TOP 20
                t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
        FROM    table1 t
        ORDER BY
                field1
        ) t
WHERE   rn > 10

 

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.