Uncommon SQL

Words: 612

Time to read: ~ 3 minutes

Table of Contents

  1. Intro
  2. ODBC Date Functions
  3. INSERT Alias
  4. Default Option
  5. = DEFAULT
  6. What about with no default?
  7. CURRENT
  8. And so forth

Intro

Recently the DBA Team Lead and I were reviewing some SQL code, and we came across some SQL that neither of us had frequently encountered before. This led to a brief watercooler moment where we shared some uncommon SQL that we had seen. Perfect blog post material, I think.

ODBC Date Functions

From a previous post, I talked about ODBC date functions.

I’m using AdventureWorks2014 here.

/* The 10 employees who have been the longest at the company */
SET NOCOUNT ON;

SELECT TOP (10) WITH TIES
        HE.JobTitle,
        HE.HireDate,
        {d '2006-06-30'} AS start_of_company,
        DATEDIFF(DAY, {d '2006-06-30'}, HE.HireDate) AS days_since_company_start
FROM    HumanResources.Employee AS HE
ORDER BY    days_since_company_start;

INSERT Alias

An unexpected item that we found recently was that INSERT INTO statements care about correct column names. That’s all though, nothing else seems to faze them.
This means that you can add the most ridiculous aliases or part names to the column and SQL Server won’t care. As far as I can tell, it will just ignore them.

/* Prefixes get prefixed */
SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.Hires', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Hires;
END;

CREATE TABLE dbo.Hires (
    hire_id int IDENTITY(1, 1) NOT NULL
        CONSTRAINT [PK dbo.Hires hire_id] PRIMARY KEY CLUSTERED,
    job_title varchar(50) NOT NULL,
    hire_date datetime2(7) NOT NULL,
    is_on_salary bit NULL
        CONSTRAINT [DF dbo.Hires is_on_salary] DEFAULT (0)
);

TRUNCATE TABLE dbo.Hires;

WITH OldestHires AS (
SELECT TOP (10) WITH TIES
        HE.JobTitle AS job_title,
        HE.HireDate AS hire_date,
        ROW_NUMBER() OVER (ORDER BY HE.HireDate) AS rn
FROM    HumanResources.Employee AS HE
ORDER BY    HE.HireDate
)
INSERT INTO dbo.Hires (
    [0].[1].[2].[3].[4].[5].[6].[7].[8].[9].job_title,
    a.b.c.d.e.f.g.h.i.j.k.l.m.n.o.p.q.r.s.t.u.v.w.x.y.z.hire_date,
    [1/0].[%].[OUT_OF_BOUNDS].[   ].is_on_salary
)
SELECT  OH.job_title,
        OH.hire_date,
        CASE 
            WHEN OH.rn % 3 = 0 THEN NULL
            ELSE 1
        END AS is_on_salary
FROM    OldestHires AS OH;

SELECT  *
FROM    dbo.Hires;
GO

Default Option

Let’s contrive an example. Let us say that we have a table called dbo.Hires and we’ve added a column called is_on_salary.
Since most of the hires are salaried, we have added a new default constraint setting the value to 0.
Unfortunately, it looks like the default constraint hasn’t been applied yet…

/* Our dbo.Hires table */
SET NOCOUNT ON;

SELECT  *
FROM    dbo.Hires;

= DEFAULT

Recently, my DBA Team Lead pointed me to a piece of code where the syntax was: UPDATE T SET COLUMN = DEFAULT

Now, I had never seen this before, and I wasn’t quite sure that this method would work. I wasn’t wholly surprised, though when a quick test proved that it does.

/* UPDATE DEFAULT */
SET NOCOUNT ON;

UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary IS NULL;

SELECT  *
FROM    dbo.Hires;

What about with no default?

Okay, that seems to add the default constraint to a column. What about when there is no defined constraint on the column. Will it error out then?

/* Removing our default constraint */
ALTER TABLE dbo.Hires
    DROP CONSTRAINT [DF dbo.Hires is_on_salary]

SELECT  'Pre update' AS [status],
        *
FROM    dbo.Hires;

UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary = 0;

SELECT  'Post update' AS [status],
        *
FROM    dbo.Hires;

Nope! As mentioned in the docs – if there is no default, and the column can become NULL, then NULL will be inserted.

CURRENT

Finally, we have CURRENT.
While the vast majority of scripts manually define the database context for commands, such as ALTER DATABASE AdventureWorks, etc., you can tell SQL Server:
Hey! Use the current database context!

/* CURRENT Database Context */
SET NOCOUNT ON;

ALTER DATABASE AdventureWorks2014 SET PAGE_VERIFY NONE;

SELECT 'Pre change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';

ALTER DATABASE CURRENT SET PAGE_VERIFY CHECKSUM;

SELECT 'Post change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';

And so forth

Thre’s probably a lot more but these are the ones that we talked about. If you have any uncommon SQL, let me know!

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

4 thoughts on “Uncommon SQL”

  1. I miss the UPDATE WRITE-Syntax:
    DROP TABLE IF EXISTS #tmp

    CREATE TABLE #tmp (id INT IDENTITY NOT NULL PRIMARY KEY,
    txt VARCHAR(max)
    )

    GO
    INSERT INTO #tmp (txt) VALUES (‘Zeile 1’)
    go
    UPDATE t
    SET — the very seldom used .WRITE syntax (with offset length parameter = NULL) appends the value to the existing. This can be a big performance benefit,
    — if the existing entry is already big
    txt .WRITE (CHAR(13) + CHAR(10) + t2.txt, NULL, NULL) — important: no t. alias may be used before txt.WRITE (otherwise it causes an error)
    FROM #tmp AS t
    CROSS JOIN #tmp AS t2
    ;
    GO 10
    SELECT *
    FROM #tmp AS t

    Reply
  3. Pingback: Uncommon SQL Tricks – Curated SQL

Leave a Reply

%d bloggers like this: