A Basic Recursive CTE and a Money Lesson

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

When I was a six or seven year old, my Mom asked me a question. She asked if I’d rather have $1,000,000 at the end of the month, or a penny on day 1, with the note that each day of the month, she’d double what I’d gotten the first day. Doing quick math in my head, $0.01, $0.02, $0.04, etc, I said a million.

Was I right? Let’s build a recursive CTE.

Recursion is an interesting computer science technique that stumps lots of people. When I was learning programming, it seemed that recursion (in Pascal) and pointers (in C), were the weed out topics.

However, they aren’t that bad, and with CTEs, we can write recursion in T-SQL. I won’t cover where this might be used in this post, though I will give you a simple CTE to view.

There are two parts you need: the anchor and the recursive member. These are connected with a UNION ALL. There can be multiple items, but we’ll keep things simple.

I want to first build an anchor, which is the base for my query. In my case, I want to start with the day of the month, which I’ll represent with a [d]. I also need the amount to be paid that day, which is represented with [v]. I’ll include the $1,000,000 as a scalar at the end. My anchor looks like this:

WITH myWealth ( d, v)
       AS (

— anchor, day 1
           SELECT
              ‘d’ = 1
            , ‘v’ = CAST( 0.01 AS numeric(38,2))
           UNION ALL

Now I need to add in the recursive part. In this part, I’ll query the CTE itself, calling myWealth as part of the code. For my query, I want to increment the day by 1 with each call, so I’ll add one to that value.

SELECT
   myWealth.d + 1

For the payment that day, it’s a simple doubling of the previous day. So I can do this a few days: addition or multiplication. I’ll use multiplication since it’s easier to read.

SELECT
   myWealth.d + 1
, myWealth.v * 2

My FROM clause is the CTE itself. However I need a way to stop the recursion. In my case, I want to stop after 31 days. So I’ll add that.

FROM
  myWealth
WHERE
  myWealth.d <= 31

Now let’s see it all together, with a little fun at the end for the outer query.

WITH  myWealth ( d, v )
        AS (
  — anchor, day 1)
             SELECT
                ‘d’ = 1
              , ‘v’ = CAST(0.01 AS NUMERIC(38, 2))
             UNION ALL
— recursive part, get double the next value, end at one month
             SELECT
                myWealth.d + 1
              , myWealth.v * 2
              FROM
                myWealth
              WHERE
                myWealth.d <= 31
           )
  SELECT
      ‘day’ = myWealth.d
    , ‘payment’ = myWealth.v
    , ‘lump sum’ = 1000000
    , ‘decision’ = CASE WHEN myWealth.v < 1000000 THEN ‘Good Decision’
                        ELSE ‘Bad decision’
                   END
    FROM
      myWealth;

When I run this, I get some results:

2016-05-17 18_48_04-Start

Did I make a good choice? Let’s look for the last few days of the month.

2016-05-17 18_48_16-Start

That $1,000,000 isn’t looking too good. If I added a running total, it would be worse.

SQLNewBlogger

If you want to try this yourself, add the running total and explain how it works.

Changing a Computed Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was working with a computed column the other day, and realized I had the wrong definition. In this case, I was performing some large calculation, and the result was larger than an int. However the first part of the formula was an int, which resulted in an implicit conversion to an int.

I needed to change the formula, and then realized that plenty of people might not work with computed columns much, and not realize how you alter a computed column.

You don’t.

In fact, you need to drop the column and add it back. In my case, this was what I did. Here was my table:

CREATE TABLE SiteStats
(
StatID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
, StateDate DATE DEFAULT SYSDATETIME()
, StatMonth TINYINT
, StatYear int
, PageVisits INT
, TimeOnSite TIME
, Engagement AS (PageVisits * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite))
)

I wanted to cast the PageVisits part of the column to a bigint to solve the issue. I first needed to do this:

ALTER TABLE dbo.SiteStats
DROP COLUMN Engagement

Once that’s done, I can do this:

ALTER TABLE dbo.SiteStats
  ADD Engagement AS (CAST(PageVisits AS BIGINT) * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite));
GO

Now I have a new definition that works great.

Some of you might realize that this could be an issue with columns in the middle of the table, and it is. However you shouldn’t worry about column order. Select the columns explicitly and you can order them anyway you want.

SQLNewBlogger

A quick post, five minutes. Even if you had to search for how this works, you could do this in 10-15 minutes, tops. Research, write why you did this and potential issues with your system.

Am I a sysadmin?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was doing some security testing and wondered if I was a sysadmin. There are a few ways to check this, but I thought there should be a function to tell me.

There’s this code, of course:

SELECT
ServerRole = rp.name,
PrincipalName = SP.name
FROM sys.server_role_members rm
Inner JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id
Inner JOIN sys.server_principals SP
ON rm.member_principal_id = SP.principal_id
where sp.name = SUSER_SNAME()
and rp.name = ‘sysadmin’

That lets me know if my login is a sysadmin. However, there is a function that you can use. IS_SRVROLEMEMBER() is a function that you can use, passing in a server role as a parameter. The code I’d use to check on sysadmin membership is this:

SELECT IS_SRVROLEMEMBER(‘sysadmin’);

If I run this, I get a 1 if I’m a member, or a 0 if I’m not.

2016-04-12 11_38_34-Settings

Using this function in your code allows you to make decisions based on role membership for the users involved, and perhaps alert them of needs for certain rights.

SQLNewBlogger

This was a quick one, really about 10 minutes to organize and write. Most of the time was writing the code to join system tables. If you tackle this subject, talk about how you  might use this, or where this type of check could come in handy in your code (maybe before taking some action).

Explicitly using tempdb with ##tables

I had someone ask me last night if this statement would create a permanent table in tempdb with a strange name or a global temp table:

CREATE TABLE tempdb..##mytable
( id int
);

My suspicion was that this would always be a temp table, but since I usually don’t include tempdb, I decided to test things. I quickly opened up SSMS and tried it. I got this message:

2016-04-21 13_55_14-Microsoft Solitaire Collection

That seems to imply what I suspected. The ## override everything and determine the table type. When I look in the list of tables, I see my table there as a temporary one.

2016-04-21 13_56_21-Start

This is the behavior I’d expect, and it acts the same in SQL 2012, 2014, and 2016 (RC2).

I don’t think there’s an advantage to using tempdb..##mytable, or even creating a permanent table (until restart) using tempdb.dbo.mytable, but if there is, please note something in the comments.

#SQLNewBlogger–Adding Local Accounts

 

What do you do if you need a process running under Local Service to connect to your SQL Server? Most of the advice out there is to change the login account. I actually agree with that, but there are times you can’t, or don’t want to.

There are certainly times when I’ve seen some automated process use one of these accounts:

  • NT Authority\Network Service
  • NT Authority\Local Server

Often this is because someone doesn’t want to bother to learn how to enable other accounts for their application, which isn’t a good excuse. In my case, I had a local VSTS agent service running as part of a demo, where I had very limited rights. I couldn’t affect a change, and I needed to get a new login for SQL Server.

I searched a bit, but most advice said to just change the account, after all, if you had a process connecting from another machine, Local Service won’t work. However I found one item on Stack Overflow that helped.

Here’s my Login list. As you can see, I have Network Service, but not Local Service.

2016-03-25 12_50_57-Alarms & Clock

I the run this code:

CREATE LOGIN [NT AUTHORITY\LOCAL SERVICE] FROM WINDOWS;

This gives me a new login.

2016-03-25 12_52_48-Alarms & Clock

In my situation, I then had to add this to the dbcreator role, but I could treat this like any other login and assign the minimum privileges needed.

SQLNewBlogger

I had to solve this and decided to write about it. The writing took 10 minutes, the research was 15-20 minutes to find a good reference and experiment a bit.

A good learning exercise, and all of you should know how to do this. Prove it with your own blog.

Default Data Masking

Dynamic Data Masking is a neat new feature in SQL Server 2016. I didn’t think much of it when it was introduced in Azure SQL Database, but since then I realize there is some value here. Even if it’s just making life simpler for developers.

I’ve been experimenting with this a bit, learning how it works, and one of the options we have for masking data is to use the default option. However, what seems misleading to me here is that this doesn’t use a default from the column. Instead it replaces the values with

  • 4 x’s (xxxx) if the column size is > 4 characters (same for numerals)
  • the number of x’s that fit in the column if the size is < 4.
  • 0 for numbers

This makes some sense, but not completely. I think I’d prefer to set a default mask for all types, so that I don’t disclose a value is a number or string (or date or anything).  I also see that NULLs are disclosed, another potential area I’d prefer to keep hidden.

I also think the name is misleading. I’ d prefer to see this called something like xmask, or defaultmask, not default.

If you want to learn more, you can look at a piece I’ve written to cover how this works, details on the default mask, or check out our list of resources at SQLServerCentral.

The OUPUT Clause in an INSERT–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I got asked a question about the OUTPUT clause recently and realized I didn’t remember the syntax. I’ve rarely used this, so I had to look it up and thought this would be a good basic post.

The idea with OUTPUT is that the data from the inserted and deleted tables can be output from the INSERT statement, outside of your triggers. This is the same data, but you can access it in the insert.

The format is

INSERT xxx OUTPUT yyyy INTO @zzz VALUES (or SELECT) mmmm

The xxx is your normal insert target, table or view. The yyyy from the OUTPUT clause is a list of items to output. These is a comma separated list of fields in the format of inserted.col1, inserted.col2.

The @zzz is a table variable. No inserts into scalar variables. This has to work with the set based nature of T-SQL. This means you’ll need to declare this variable. The mmmm is your normal insert stuff.

Example

Here’s a quick, short example. Let’s say  I have this table:

CREATE TABLE MyCustomers
(
MyID INT IDENTITY(1, 1)
, MyCustomer VARCHAR(200)
, Active TINYINT
);
GO
INSERT dbo.MyCustomers
VALUES (‘Acme’, 1), (‘Roadrunner’, 0), (‘Bugs’, 1)

I want to insert data into the table, and capture the identity value of MyID  as well as the name, separately from the insert. Note, I might really have a TRY..CATCH in production to deal with issues.

If I add a new row, the identity should be 4. I want to capture this. I’ll first declare my OUTPUT variable.

DECLARE @customers( id int, customer varchar(200);

I don’t have to make this match the entire table, I can use a subset.

Next, let’s build the INSERT. I want to capture the two fields from the inserted table, so we’ll include those.

INSERT dbo.MyCustomers
OUTPUT Inserted.MyID
, Inserted.MyCustomer
INTO @customers
VALUES
(‘Wile E Corp’, 1);

I also need to output my table variable

SELECT
*
FROM
@customers;

If I run this, I’ll see this:

2016-03-11 14_19_28-Settings

Of course, I can do other processing with my table variable, using the output elsewhere in code.

SQLNewBlogger

This is a quick look at how you can use the OUPUT clause. This took me about 10 minutes to play with and remember the syntax, and 10 more minutes to write.

I’d encourage you to play with this and write your own blogs. What can you discover about this construct?

Reference

OUTPUT – https://msdn.microsoft.com/en-us/library/ms177564.aspx

What Database am I in?

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw someone trying to get the database context recently and they were confused. They had some code referencing @dbname, but received an error with this code. I thought maybe they meant @@DBMAME, but when I looked, there isn’t a variable tracked by SQL Server.

A quick search (really experimenting with SQL Prompt) showed me there is a DB_NAME() function. Without a parameter, this returns my current context. That’s useful in programming systems.

If I pass in a database ID, I get the name.

I haven’t typically had an issue here. In code, if I doubt anything, I issue a USE statement to set context, but if you are looking to take actions in a generic script based on the database, this can help.

Finding Strings

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A quick one today, just looking for strings. I wrote an article on this, so there’s more detail there, but here’s a bit of code you can look through and see what it does.

CREATE TABLE dbo.MyTable ( mystring VARCHAR(200) );

GO

INSERT dbo.MyTable

    ( mystring )

  VALUES

    ( ‘This is a 7’ )

  , ( ‘There is a 7 in this string’ )

  , ( ‘Why must 7 always be here’ )

  , ( ‘No 7s here. JK’ )

  , ( ‘My 7s here’ )

  , ( ‘An 7s here.’ )

  , ( ‘On 7s here.’ );

GO

SELECT

    ‘SetPosition’ = SUBSTRING(mystring, 4, 1)

  , ‘FindThe7’ = SUBSTRING(mystring, CHARINDEX(‘7’, mystring), 1)

  FROM

    dbo.MyTable AS mt;

DROP TABLE dbo.MyTable;

Create a Database Master Key–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the first things you need in a SQL Server database in order to implement encryption is a database master key, DMK. This is simple to create, though you need one in each database that will support encryption.

The syntax is easy, with only really an option to specify a password. There is no name, as there’s a single DMK per database. Set your context to the correct database and end enter:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Som3thingR3ally$|tr0ng’;

When you execute this, you’ll just get a result message. At least, if it works you will. The password must conform to the password requirements of your Windows OS, which is good.

Note: This is a securable code, like the password for a user account. Make sure you store this in a password manager for your organization.

By default, this is protected by your password as well as the Service Master Key (SMK) on your instance. In practice this doesn’t usually mean much for you, but be aware of this.

You do need CONTROL permission on the database, though usually I’d expect a db_owner or more permissions to actually create these keys.

And, of course, back up the key as soon as you can.

SQLNewBlogger

This was about 5 minutes work for me. I would guess most new bloggers could read, understand, and produce an explanation of this in 30 minutes.

References

CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx

Create a Database Master Key – https://msdn.microsoft.com/en-us/library/aa337551.aspx