World of Whatever

A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Friday, February 23, 2018

Pop Quiz - REPLACE in SQL Server

It's amazing the things I've run into with SQL Server this week that I never noticed. In today's pop quiz, let's look at REPLACE

DECLARE
    @Repro table
(
    SourceColumn varchar(30)
);

INSERT INTO 
    @Repro
(
    SourceColumn
)
SELECT
    D.SourceColumn
FROM
(
    VALUES 
        ('None')
    ,   ('ABC')
    ,   ('BCD')
    ,   ('DEF')
)D(SourceColumn);

SELECT
    R.SourceColumn
,   REPLACE(R.SourceColumn, 'None', NULL) AS wat
FROM
    @Repro AS R;

In the preceding example, I load 4 rows into a table and call the REPLACE function on it. Why? Because some numbskull front end developer entered None instead of a NULL for a non-existent value. No problem, I will simply replace all None with NULL. So, what's the value of the wat column?

Well, if you're one of those people who reads instruction manuals before attempting anything, you'd have seen Returns NULL if any one of the arguments is NULL. Otherwise, you're like me thinking "maybe I put the arguments in the wrong order". Nope, , REPLACE(R.SourceColumn, 'None', '') AS EmptyString that works. So what the heck? Guess I'll actually read the manual... No, this work, I can just use NULLIF to make the empty strings into a NULL , NULLIF(REPLACE(R.SourceColumn, 'None', ''), '') AS EmptyStringToNull

Much better, replace all my instances of None with an empty string and then convert anything that is empty string to null. Wait, what? You know what would be better? Skipping the replace call altogether.

SELECT
    R.SourceColumn
,   NULLIF(R.SourceColumn, 'None') AS MuchBetter
FROM
    @Repro AS R;

Moral of the story and/or quiz: once you have a working solution, rubber duck out your approach to see if there's an opportunity for improvement (only after having committed the working version to source control).

Thursday, February 22, 2018

Altering table types, part 2

Altering table types - a compatibility guide

In yesterday's post, I altered a table type. Pray I don't alter them further. What else is incompatible with an integer column? It's just a morbid curiosity at this point as I don't recall having ever seen this after working with SQL Server for 18 years. Side note, dang I'm old

How best to answer the question, by interrogating the sys.types table and throwing operations against the wall to see what does/doesn't stick.

DECLARE
    @Results table
(
    TypeName sysname, Failed bit, ErrorMessage nvarchar(4000)
);

DECLARE
    @DoOver nvarchar(4000) = N'DROP TABLE IF EXISTS dbo.IntToTime;
CREATE TABLE dbo.IntToTime (CREATE_TIME int);'
,   @alter nvarchar(4000) = N'ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME @type'
,   @query nvarchar(4000) = NULL
,   @typeName sysname = 'datetime';

DECLARE
    CSR CURSOR
FORWARD_ONLY
FOR
SELECT 
    T.name
FROM
    sys.types AS T
WHERE
    T.is_user_defined = 0

OPEN CSR;
FETCH NEXT FROM CSR INTO @typeName
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY   
        EXECUTE sys.sp_executesql @DoOver, N'';
        SELECT @query = REPLACE(@alter, N'@type', @typeName);
        EXECUTE sys.sp_executesql @query, N'';
        
        INSERT INTO
            @Results
        (
            TypeName
        ,   Failed
        ,   ErrorMessage
        )
        SELECT @typeName, CAST(0 AS bit), ERROR_MESSAGE();
    END TRY
    BEGIN CATCH
        INSERT INTO
            @Results
        (
            TypeName
        ,   Failed
        ,   ErrorMessage
        )
        SELECT @typeName, CAST(1 AS bit), ERROR_MESSAGE()
    END CATCH
    FETCH NEXT FROM CSR INTO @typeName
END
CLOSE CSR;
DEALLOCATE CSR;

SELECT
*
FROM
    @Results AS R
ORDER BY
    2,1;
TypeNameFailedErrorMessage
bigint0
binary0
bit0
char0
datetime0
decimal0
float0
int0
money0
nchar0
numeric0
nvarchar0
real0
smalldatetime0
smallint0
smallmoney0
sql_variant0
sysname0
tinyint0
varbinary0
varchar0
date1Operand type clash: int is incompatible with date
datetime21Operand type clash: int is incompatible with datetime2
datetimeoffset1Operand type clash: int is incompatible with datetimeoffset
geography1Operand type clash: int is incompatible with geography
geometry1Operand type clash: int is incompatible with geometry
hierarchyid1Operand type clash: int is incompatible with hierarchyid
image1Operand type clash: int is incompatible with image
ntext1Operand type clash: int is incompatible with ntext
text1Operand type clash: int is incompatible with text
time1Operand type clash: int is incompatible with time
timestamp1Cannot alter column 'CREATE_TIME' to be data type timestamp.
uniqueidentifier1Operand type clash: int is incompatible with uniqueidentifier
xml1Operand type clash: int is incompatible with xml

Wednesday, February 21, 2018

Pop quiz - altering column types

Pop quiz

Given the following DDL

CREATE TABLE dbo.IntToTime
(
    CREATE_TIME int
);

What will be the result of issuing the following command?

ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME time NULL;

Clearly, if I'm asking, it's not what you might expect. How can an empty table not allow you to change data types? Well it seems Time and datetime2 are special cases as they'll raise errors of the form

Msg 206, Level 16, State 2, Line 47 Operand type clash: int is incompatible with time

If you're in this situation and need to get the type converted, you'll need to make two hops, one to varchar and then to time.

ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME varchar(10) NULL;
ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME time NULL;

Thursday, January 25, 2018

What are all the functions and their parameters?

What are all the functions and their parameters?

File this one under: I wrote it once, may I never need it again

In my ever expanding quest for getting all the metadata, I how could I determine the metadata for all my table valued functions? No problem, that's what sys.dm_exec_describe_first_result_set is for. SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.foo(@xmlMessage)', N'@xmlMessage nvarchar(max)', 1) AS DEDFRS

Except, I need to know parameters. And I need to know parameter types. And order. Fortunately, sys.parameters and sys.types makes this easy. The only ugliness comes from the double invocation of row rollups


SELECT 
    CONCAT
    (
        ''
    ,   'SELECT * FROM '
    ,   QUOTENAME(S.name)
    ,   '.'
    ,   QUOTENAME(O.name)
    ,   '('
        -- Parameters here without type
    ,   STUFF
        (
            (
                SELECT 
                    CONCAT
                    (
                        ''
                    ,   ','
                    ,   P.name
                    ,   ' '
                    )
                FROM
                    sys.parameters AS P
                WHERE
                    P.is_output = CAST(0 AS bit)
                    AND P.object_id = O.object_id
                ORDER BY
                    P.parameter_id
                FOR XML PATH('')
            )
        ,   1
        ,   1
        ,   ''
        )

    ,   ') AS F;'
    ) AS SourceQuery
,   (
        STUFF
        (
            (
                SELECT 
                    CONCAT
                    (
                        ''
                    ,   ','
                    ,   P.name
                    ,   ' '
                    ,   CASE 
                        WHEN T2.name LIKE '%char' THEN CONCAT(T2.name, '(', CASE P.max_length WHEN -1 THEN 'max' ELSE CAST(P.max_length AS varchar(4)) END, ')')
                        WHEN T2.name = 'time' OR T2.name ='datetime2' THEN CONCAT(T2.name, '(', P.scale, ')')
                        WHEN T2.name = 'numeric' THEN CONCAT(T2.name, '(', P.precision, ',', P.scale, ')')
                        ELSE T2.name
                    END
                    )
                FROM
                    sys.parameters AS P
                    INNER JOIN
                        sys.types AS T2
                        ON T2.user_type_id = P.user_type_id
                WHERE
                    P.is_output = CAST(0 AS bit)
                    AND P.object_id = O.object_id
                ORDER BY
                    P.parameter_id
                FOR XML PATH('')
            )
        ,   1
        ,   1
        ,   ''
        )
    ) AS ParamterList
FROM
    sys.schemas AS S
    INNER JOIN
        sys.objects AS O
        ON O.schema_id = S.schema_id
WHERE
    O.type IN ('FT','IF', 'TF');

How you use this is up to you. I plan on hooking it into the Biml Query Table Builder to simulate tables for all my TVFs.

Monday, January 22, 2018

Staging Metadata Framework for the Unknown

Staging metadata framework for the unknown

That's a terrible title but it's the best I got. A client would like to report out of ServiceNow some metrics not readily available in the PowerBI App. The first time I connected, I got a quick look at the Incidents and some of the data we'd be interested in but I have no idea how that data changes over time. When you first open a ticket, maybe it doesn't have a resolved date or a caused by field populated. And since this is all web service stuff and you can customize it, I knew I was looking at lots of iterations to try and keep up with all the data coming back from the service. How can I handle this and keep sane? Those were my two goals. I thought it'd be fun to share how I solved the problem using features in SQL Server 2016.

To begin, I created a database called RTMA to perform my real time metrics analysis. CREATE DATABASE RTMA; With that done, I created a schema within my database like USE RTMA; GO CREATE SCHEMA ServiceNow AUTHORIZATION dbo; To begin, we need a table to hold our discovery metadata.

CREATE TABLE 
    ServiceNow.ColumnSizing
(
    EntityName varchar(30) NOT NULL
,   CollectionName varchar(30) NOT NULL
,   ColumnName varchar(30) NOT NULL
,   ColumnLength int NOT NULL
,   InsertDate datetime NOT NULL
    CONSTRAINT DF_ServiceNow_ColumnSizing_InsertDate DEFAULT (GETDATE())
);

CREATE CLUSTERED COLUMNSTORE INDEX
    CCI_ServiceNow_ColumnSizing
    ON ServiceNow.ColumnSizing;
The idea for this metadata table is that we'll just keep adding more information in for the entities we survey. All that matters is the largest length for a given combination of Entity, Collection, and Column.

In the following demo, we'll add 2 rows into our table. The first batch will be our initial sizing and then "something" happens and we discover the size has increased.

INSERT INTO
    ServiceNow.ColumnSizing
(
    EntityName
,   CollectionName
,   ColumnName
,   ColumnLength
,   InsertDate
)
VALUES
    ('DoesNotExist', 'records', 'ABC', 10, current_timestamp)
,   ('DoesNotExist', 'records', 'BCD', 30, current_timestamp);

Create a base table for our DoesNotExist. What columns will be available? I know I'll want my InsertDate and that's the only thing I'll guarantee to begin. And that's ok because we're going to get clever.

DECLARE @entity nvarchar(30) = N'DoesNotExist'
,   @Template nvarchar(max) = N'DROP TABLE IF EXISTS ServiceNow.Stage;
    CREATE TABLE
        ServiceNow.Stage
    (
    
    InsertDate datetime CONSTRAINT DF_ServiceNow_Stage_InsertDate DEFAULT (GETDATE())
    );
    CREATE CLUSTERED COLUMNSTORE INDEX
        CCI_ServiceNow_Stage
    ON
        ServiceNow.Stage;'
,   @Columns nvarchar(max) = N'';

DECLARE @Query nvarchar(max) = REPLACE(REPLACE(@Template, '', @Entity), '', @Columns);
EXECUTE sys.sp_executesql @Query, N'';

We now have a table with one column so let's look at using our synthetic metadata (ColumnSizing) to augment it. The important thing to understand in the next block of code is that we'll use FOR XML PATH('') to concatenate rows together and the CONCAT function to concatenate values together.

See more here for the XML PATH "trick"

If we're going to define columns for a table, it follows that we need to know what table needs what columns and what size those columns should be. So, let the following block be that definition.

DECLARE @Entity varchar(30) = 'DoesNotExist';

SELECT
    CS.EntityName
,   CS.CollectionName
,   CS.ColumnName
,   MAX(CS.ColumnLength) AS ColumnLength
FROM
    ServiceNow.ColumnSizing AS CS
WHERE
    CS.ColumnLength > 0
    AND CS.ColumnLength =  
    (
        SELECT
            MAX(CSI.ColumnLength) AS ColumnLength
        FROM
            ServiceNow.ColumnSizing AS CSI
        WHERE
            CSI.EntityName = CS.EntityName
            AND CSI.ColumnName = CS.ColumnName
    )
    AND CS.EntityName = @Entity
GROUP BY
    CS.EntityName
,   CS.CollectionName
,   CS.ColumnName;

We run the above query and that looks like what we want so into the FOR XML machine it goes.

DECLARE @Entity varchar(30) = 'DoesNotExist'
,   @ColumnSizeDeclaration varchar(max);

;WITH BASE_DATA AS
(
    -- Define the base data we'll use to drive creation
    SELECT
        CS.EntityName
    ,   CS.CollectionName
    ,   CS.ColumnName
    ,   MAX(CS.ColumnLength) AS ColumnLength
    FROM
        ServiceNow.ColumnSizing AS CS
    WHERE
        CS.ColumnLength > 0
        AND CS.ColumnLength =  
        (
            SELECT
                MAX(CSI.ColumnLength) AS ColumnLength
            FROM
                ServiceNow.ColumnSizing AS CSI
            WHERE
                CSI.EntityName = CS.EntityName
                AND CSI.ColumnName = CS.ColumnName
        )
        AND CS.EntityName = @Entity
    GROUP BY
        CS.EntityName
    ,   CS.CollectionName
    ,   CS.ColumnName
)
SELECT DISTINCT
    BD.EntityName
,   (
        SELECT
            CONCAT
            (
                ''
            ,   BDI.ColumnName
            ,   ' varchar('
            ,   BDI.ColumnLength
            ,   '),'
            ) 
        FROM
            BASE_DATA AS BDI
        WHERE
            BDI.EntityName = BD.EntityName
            AND BDI.CollectionName = BD.CollectionName
        FOR XML PATH('')
) AS ColumnSizeDeclaration
FROM
    BASE_DATA AS BD;

That looks like a lot, but it's not. Run it and you'll see we get one row with two elements: "DoesNotExist" and "ABC varchar(10),BCD varchar(30)," That trailing comma is going to be a problem, that's generally why you see people either a leading delimiter and use STUFF to remove it or in the case of a trailing delimiter LEFT with LEN -1 does the trick.

But we're clever and don't need such tricks. If you look at the declaration for @Template, we assume there will *always* be at final column of InsertDate which didn't have a comma preceding it. Always define the rules to favor yourself. ;)

Instead of the static table declaration we used, let's marry our common table expression, CTE, with the table template.

DECLARE @entity nvarchar(30) = N'DoesNotExist'
,   @Template nvarchar(max) = N'DROP TABLE IF EXISTS ServiceNow.Stage;
    CREATE TABLE
        ServiceNow.Stage
    (
    
    InsertDate datetime CONSTRAINT DF_ServiceNow_Stage_InsertDate DEFAULT (GETDATE())
    );
    CREATE CLUSTERED COLUMNSTORE INDEX
        CCI_ServiceNow_Stage
    ON
        ServiceNow.Stage;'
,   @Columns nvarchar(max) = N'';

-- CTE logic patched in here

;WITH BASE_DATA AS
(
    -- Define the base data we'll use to drive creation
    SELECT
        CS.EntityName
    ,   CS.CollectionName
    ,   CS.ColumnName
    ,   MAX(CS.ColumnLength) AS ColumnLength
    FROM
        ServiceNow.ColumnSizing AS CS
    WHERE
        CS.ColumnLength > 0
        AND CS.ColumnLength =  
        (
            SELECT
                MAX(CSI.ColumnLength) AS ColumnLength
            FROM
                ServiceNow.ColumnSizing AS CSI
            WHERE
                CSI.EntityName = CS.EntityName
                AND CSI.ColumnName = CS.ColumnName
        )
        AND CS.EntityName = @Entity
    GROUP BY
        CS.EntityName
    ,   CS.CollectionName
    ,   CS.ColumnName
)
SELECT DISTINCT
    @Columns = (
        SELECT
            CONCAT
            (
                ''
            ,   BDI.ColumnName
            ,   ' varchar('
            ,   BDI.ColumnLength
            ,   '),'
            ) 
        FROM
            BASE_DATA AS BDI
        WHERE
            BDI.EntityName = BD.EntityName
            AND BDI.CollectionName = BD.CollectionName
        FOR XML PATH('')
) 
FROM
    BASE_DATA AS BD;

DECLARE @Query nvarchar(max) = REPLACE(REPLACE(@Template, '', @Entity), '', @Columns);
EXECUTE sys.sp_executesql @Query, N'';

Bam, look at it now. We took advantage of the new DROP IF EXISTS (DIE) syntax to drop our table and we've redeclared it, nice as can be. Don't take my word for it though, ask the system tables what they see.

SELECT
    S.name AS SchemaName
,   T.name AS TableName
,   C.name AS ColumnName
,   T2.name AS DataTypeName
,   C.max_length
FROM
    sys.schemas AS S
    INNER JOIN
        sys.tables AS T
        ON T.schema_id = S.schema_id
    INNER JOIN
        sys.columns AS C
        ON C.object_id = T.object_id
    INNER JOIN
        sys.types AS T2
        ON T2.user_type_id = C.user_type_id
WHERE
    S.name = 'ServiceNow'
    AND T.name = 'StageDoesNotExist'
ORDER BY
    S.name
,   T.name
,   C.column_id;
Excellent, we now turn on the actual data storage process and voila, we get a value stored into our table. Simulate it with the following.
INSERT INTO ServiceNow.StageDoesNotExist
(ABC, BCD) VALUES ('Important', 'Very, very important');
Truly, all is well and good.

*time passes*

Then, this happens

WAITFOR DELAY ('00:00:03');

INSERT INTO
    ServiceNow.ColumnSizing
(
    EntityName
,   CollectionName
,   ColumnName
,   ColumnLength
,   InsertDate
)
VALUES
    ('DoesNotExist', 'records', 'BCD', 34, current_timestamp);
Followed by
INSERT INTO ServiceNow.StageDoesNotExist
(ABC, BCD) VALUES ('Important','Very important, yet ephemeral data');
To quote Dr. Beckett: Oh boy

Friday, December 29, 2017

Python Azure Function requestor's IP address

Python Azure Function requestor's IP address

I'm working on an anonymous level Azure Function in python and couldn't find where they stored the IP address of the caller, if applicable. It's in the request headers, which makes sense but not until I spent far too much time looking in all the wrong places. A minimal reproduction would look something like

import os
iptag = "REQ_HEADERS_X-FORWARDED-FOR"
ip = "Tag name:{} Tag value:{}".format(iptag, os.environ[iptag])
print(ip)

Now, something to note is that it will return not only the IP address but the port the call came in through. Thus, I see a value of 192.168.1.200:33496 instead of just the ipv4 value.

Knowing where to look, I can see that the heavy lifting had already been done by the most excellent HTTPHelper but as a wise man once said: knowing is half the battle.

import os
from AzureHTTPHelper import HTTPHelper
http = HTTPHelper()
#Notice the lower casing of properties here and the trimming of the type (REQ_HEADERS)
iptag = "x-forwarded-for"
ip = "Tag name:{} Tag value:{}".format(iptag, http.headers[iptag])
print(ip)

Yo Joe!


Thursday, November 9, 2017

What's my transaction isolation level

What's my transaction isolation level

That's an easy question to answer - StackOverflow has a fine answer.

But, what if I use sp_executesql to run some dynamic sql - does it default the connection isolation level? If I change isolation level within the query, does it propagate back to the invoker? That's a great question, William. Let's find out.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

DECLARE
    @query nvarchar(max) = N'-- Identify iso level
SELECT CASE transaction_isolation_level 
WHEN 0 THEN ''Unspecified'' 
WHEN 1 THEN ''ReadUncommitted'' 
WHEN 2 THEN ''ReadCommitted'' 
WHEN 3 THEN ''Repeatable'' 
WHEN 4 THEN ''Serializable'' 
WHEN 5 THEN ''Snapshot'' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Test iso level
SELECT CASE transaction_isolation_level 
WHEN 0 THEN ''Unspecified'' 
WHEN 1 THEN ''ReadUncommitted'' 
WHEN 2 THEN ''ReadCommitted'' 
WHEN 3 THEN ''Repeatable'' 
WHEN 4 THEN ''Serializable'' 
WHEN 5 THEN ''Snapshot'' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID'

EXECUTE sys.sp_executesql @query, N'';

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

I begin my session in read uncommitted aka "nolock". I then run dynamic sql which identifies my isolation level, still read uncommitted, change it to a different level, confirmed at read committed, and then exit and check my final state - back to read uncommitted.

Finally, thanks to Andrew Kelly (b|t) for answering the #sqlhelp call.