Press "Enter" to skip to content

Day: January 26, 2018

A Definition Of Functional Programming

Published 2018-01-26 by Kevin Feasel

Kevin Sookocheff contrasts functional programming with its imperative cousin:

Functional programming is a form of declarative programming that expresses a computation directly as pure functional transformation of data. A functional program can be viewed as a declarative program where computations are specified as pure functions.

I think that if you’re a set-based SQL developer, functional programming languages will make the most intuitive sense.  They’re a bit harder to wrap your mind around if you’ve grown up as an imperative C-style developer, but are still worth the effort.

Comments closed

Building A Windows Failover Cluster

Published 2018-01-26 by Kevin Feasel

David Fowler continues his series on building a test lab:

Now I don’t want to get into the details of Quorum, there are plenty of great posts out there that explain it far better than I can but in a nutshell, each node in the cluster has a vote and we really want the total number of votes to be an odd number.  But we’ve only got two servers, does that mean that we need a create another server to make an odd number?  Well, no we don’t.  What we can use is what’s known as a file share witness, and that’s simply a file share that each of the nodes in the cluster can access.  That file share will effectively act as our third vote.

So first thing that you’re going to need to do is create a file share somewhere, the best place for that in our setup would be on the domain controller or somewhere that we know is always likely to be available.  So go and do that now, call it what you like but make sure that the servers are going to have full rights to it.  As this is just our own personal little test lab and we’re not too worried about best practices you could possibly open it up to EVERYONE (probably not a great idea in a production environment but not the end of the world if we want to be lazy in our own little play pen).

David also shows how to set up an Availability Group.

Comments closed

More Fun With SSMS

Published 2018-01-26 by Kevin Feasel

Wayne Sheffield continues his SSMS tools and tips series.  Since our last look, he’s added a few more tips.  First, Wayne shows how to show and hide blocks of T-SQL using Outlining.  Then, he gets to something I find useful in SSMS:

There are many editing items in SSMS that makes formatting and navigating your code easier than ever. Most of these Quick Editing Tips that follow are available from the Advanced submenu on the Edit menu

In particular, I like showing whitespace characters, as I’m kind of a whitespace tyrant.  But there are several other helpful options in that menu.

From there, Wayne shows how to use bookmarks in SSMS, which is something I tend not to do.  Finally, you can see the SSMS web browser.

Comments closed

Avoid Scalar Functions In Computed Columns

Published 2018-01-26 by Kevin Feasel

Daniel Hutmacher shows why you should not include scalar functions inside computed column definitions:

Scalar functions can be a real headache when you’re performance tuning. For one, they don’t parallelize. In fact, if you use a scalar function in a computed column, it will prevent any query that uses that table from going parallel – even if you don’t reference that column at all!

Read on for a demonstration.

Comments closed

Azure And The Kappa Architecture

Published 2018-01-26 by Kevin Feasel

Jared Zagelbaum describes the Kappa architecture and points out how there’s limited built-in support in Azure for it:

You can’t support kappa architecture using native cloud services. Cloud providers, including Azure, didn’t design streaming services with kappa in mind. The cost of running streams with TTL greater than 24 hours is more expensive, and generally, the max TTL tops out around 7 days. If you want to run kappa, you’re going to have to run Platform as a Service (PaaS) or Infrastructure as a Service (IaaS), which adds more administration to your architecture. So, what might this look like in Azure?

Read the whole thing.

Comments closed

Using Schemas For Database Management

Published 2018-01-26 by Kevin Feasel

Jana Sattainathan explains the benefits of using schemas to segment out functional sections of a database:

My recommendation on how to manage permissions goes like this:

  1. Create Database with appropriate Schemas – like HR/Finance (or) Staging/ETL etc
  2. Create objects like tables and views inside the appropriate Schemas
  3. Create database roles such as db_finance_admin_role, db_developer_role, db_ddl_deployer_role etc
  4. Grant permissions at the Schema level to database roles as shown in the example above
  5. Create AD groups (instead of individual logins) like Finance_DB_Admins, IT_Developers etc
  6. Grant database role membership to AD groups instead of individual logins – EXEC sp_addrolemember N’db_developer_role’, N’IT_Developers’

Doing it this way allows you to separate the concerns. For example the db_developer_role can be granted more or less permissions and all the groups granted that role will automatically get that. Also, you are free to use the AD groups across instances in multiple databases with different permissions.

Click through for more details, including how to get to separate schemas from an all-dbo database.

Comments closed

Visualizing Progress Using Power BI

Published 2018-01-26 by Kevin Feasel

Stacia Varga methods of visualizing progress toward a goal using Power BI:

Another interesting way to look at goal tracking for a goal in which time is an important element, such as my daily Move goal, is to use a KPI visualization.

Just as many businesses use KPIs, which is an abbreviation for key performance indicators, I can use a KPI to see my current metric value, as of the last date for which I have collected data. In Power BI, not only can I see this value, but I can also see how it compares to the target at a glance, through the use of color. Red is bad and green is good, by default, but I can use the formatting options to change this. And I can see how the value trends over time, much like my current line and clustered column chart does.

Click through for several techniques.

Comments closed

Defining Table-Valued Function Metadata

Published 2018-01-26 by Kevin Feasel

Bill Fellows has a query which gets input parameters for table-valued functions:

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

Click through for the script.

Comments closed