Words: 612
Time to read: ~ 3 minutes
Table of Contents
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!
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
ODBC Scalar functions. First time I ran across these (not too long ago), I was very surprised.
https://docs.microsoft.com/en-us/sql/t-sql/functions/odbc-scalar-functions-transact-sql?view=sql-server-ver15
Thanks for very interesting article.
it came as a surprise to me to find out the different ways of setting aliases https://github.com/ktaranov/sqlserver-kit/blob/master/SQL Server Name Convention and T-SQL Programming Style.md#tsql-alias