The Bit Bucket

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own database roles, and generally, you should do this instead of using the fixed database roles like db_owner, or db_datareader and db_datawriter, as it lets you allocate just the required permissions. To allow scripting out the permissions assigned to these roles, we’ve added a tool called ScriptUserDefinedDatabaseRolePermissions.

2020-01-01

Wow, it’s been a really big year in many ways. I had a list of goals for the year, and overall I’m pretty happy with where it’s ended up. One area that remains a challenge for me is personal health. But there are things I have in place that make me confident that one will be addressed better in 2020.

I liked the way that a few of my friends this year posted that they aren’t making New Year resolutions, they’re just stating goals for the next year. That’s a great way to look at things.

2019-12-31

It’s useful to be able to find one string within another string. T-SQL has two basic functions for that: CHARINDEX and PATINDEX.

CHARINDEX is used to see if one string appears within another string. Take a look at this code:

In this first example, I asked SQL Server to look for the string lot in the string Hellothere. You can see that it found it at position 4.

2019-12-30

I’ve had a lot of great feedback on my series of blog posts about Snowflake, and explaining the concepts for an existing SQL Server audience.

Just out for Christmas is a new free eBook for our SDU Insiders. I’m preparing a three part series on Snowflake for SQL Server users:

• Part 1: Core Concepts (available now)
• Part 2: SQL Language (coming soon)
• Part 3: Administration (sometime after that :-))

You can get part one now! And did I mention free? (for SDU Insiders)

2019-12-27

One issue that comes up time and again in the forums is when an INSERT statement fails with a violation of a UNIQUE or PRIMARY KEY constraint but when the user checks the existing table, the value that’s being complained about isn’t already in the table.

The Symptom

For example, a table might have primary key values of 2, 12, and 14.

When an INSERT is performed, there is an error telling you that you can’t insert a duplicate key value of say 15.

2019-12-26

Hi folks,

2019 has been another really big year. The number of readers of this blog has really increased in the last year and I’m pleased you’re here as one of them.  You make it all worthwhile.

Thanks to so many who reached out during the year, with wonderful insights and ideas.

Just wanted to wish you all the best for the holiday and Christmas season, from here down under.

2019-12-25

REPLICATE is an easy function to work with. It takes a string and a number of times you want it repeated. Here’s an example:

In this case, I’ve asked it to replicate (i.e. duplicate many times) an A character. I’ve asked for 5 of them. I put an X at each end so you could see the effect more clearly.

There are many things this can be used for, but for example, if I wanted to draw a line with 100 dashes, I could just ask for REPLICATE(’-’, 100).

2019-12-23

As with most products today, Snowflake has substantial handling of encryption to protect client data.

All editions claim to provide “Always-on enterprise grade encryption in transit and at rest”.

In Transit

First, for connections, HTTPS and TLS 1.2 are used throughout the system.

If the customer is using external stages (holding locations in cloud-storage accounts), the data can be encrypted while stored in the stage, and then travel encrypted into Snowflake. To do that, you create an “encrypted stage” where you tell Snowflake the master encryption key (i.e. the client side key) when creating the stage:

2019-12-20

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own server roles, and generally, you should do this instead of using the fixed server roles, as it lets you allocate just the required permissions. To allow scripting the permissions that have been assigned to the roles, we’ve added a tool called ScriptUserDefinedServerRolePermissions.

It’s a function and doesn’t require any parameters.

2019-12-18

It’s that time of year where people give each other presents. Why not give yourself a present and learn something new while you’ve got a chance?

Our latest course release is: SQL Server Integration Services for Developers and DBAs.

You’ll find it, and our other courses here now:

https://sqldownunder.com/

• 4 Steps to Faster SQL Server Applications (Free)
• Protecting SQL Server Data with Encryption
• Writing T-SQL Queries for SQL Server (Low cost)
• SQL Server Reporting Services for Developers and DBAs
• SQL Server Spatial for Developers and DBAs
• SQL Server Indexing for Developers
• SQL Server Service Broker for Developers and DBAs
• SQL Server Service Broker for Developers and DBAs
• Working with SQL Server Replication

And many more coming soon.

2019-12-18