SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

The Login Types

The Voice of the DBA

Knowing What You Don’t Know

Today we have a guest editorial from Grant Fritchey as Steve is away on his sabbatical.

I’ve been reading a fascinating history about Audrey Hepburn and her experiences growing up under occupation during World War II. Not only is her personal story riveting, but the details around the occupation are also frequently new knowledge for me. I’m a huge history nerd. I read quite a bit of history, and it just fascinates me. However, reading this book, I realized that I have enormous holes in my knowledge, forcing me to reexamine what I think I know.

I believe it’s vital that we, as data professionals, continuously reexamine what we think we know as well. I recently wrote a blog post on this same topic, lamenting that people weren’t learning Extended Events because the early launch was, frankly, bad. Yet, things have radically improved, but lots of people, including ones I respect and admire, hadn’t gone back to reexamine the issue. They didn’t know that things had changed. They had gaps in their knowledge.

So, my question today, how do you go about identifying that you have gaps in your knowledge?

Let’s paraphrase Donald Rumsfeld a bit. There are the things that you know you know. There are the things that you know that you do not know. Having this boundary is vital. Being honest about it is even more so. However, there are also the things that you do not know that you do not know. How do you go about tracking those down? Should you bother?

I have a recent example from a question online. A group was taking server backups. The whole server. They weren’t backing up databases. Well, a database became corrupted. They went to their backup, but couldn’t restore the entire thing. That would mean taking down the server. They managed to pluck an MDB file out. However, that wouldn’t restore, no matter what they did. Then they started trying to attach it, which also wasn’t working. In short, they didn’t know what they didn’t know about backups, restores, attachments, corruption, and disaster recovery. That lack of knowledge about their lack of knowledge hurt them.

Short of sprinting straight into a brick wall, learning the hard way, how do we go about narrowing that gap? How do you go about narrowing that gap? Do you bother trying to narrow the gap?

Grant Fritchey

Join the debate, and respond to today's editorial on the forums

Featured Contents

Move Data from SQL Server to Azure Blob Storage with Incremental Changes – Part 2

Ginger Keys Daniel from SQLServerCentral

In part 2 of this article, learn how you can move incremental changes in a SQL Server table using Azure Data Factory.

New release: SQL Change Automation 4.1

Additional Articles from Redgate

We’ve heard from users that they really care about static data and were missing the feature in the SQL Change Automation SSMS plugin. In v4.1, we’ve added support for the tracking of static data tables to the SSMS extension, alongside existing support in the VS extension. Maya Malakova has written a technical summary showing how to use this feature. Learn more about the release here.

Types of SQL Server Indexes

Additional Articles from MSSQLTips.com

In this tip we will go through each type of index that is available in SQL Server and give an overview as well as advice on which index types fit each particular use case.

From the SQL Server Central Blogs - My experiences with Imposter Syndrome: T-SQL Tuesday #122

Kenneth.Fisher from SQLStudies

Jon Shaulis (blog|twitter) is hosting T-SQL Tuesday this month. Thanks Jon! And the subject is obvious if you read the ... Continue reading

From the SQL Server Central Blogs - Knocking Off for Six Weeks

Steve Jones - SSC Editor from The Voice of the DBA

This won’t be the last blog I write for six weeks, but it is the last one I’ll syndicate and in which write about technical stuff. Today is my...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Login Types
In the sys.server_principals DMV, there is a type that corresponds to the type of principal stored in a row. There are SQL logins, Windows Logins, Windows Groups, Server Roles, and two other types. What are they?
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Choosing the Column

I set up a table like this:

CREATE TABLE [dbo].[Employees]
(
[EmpID] [int] NULL,
[EmpSSN] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Lastname] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Salary] [money] NULL
) ON [PRIMARY] 
GO
INSERT INTO dbo.Employees
VALUES
( 1, '12345', 'Steve', 'Jones', 120000.0000 ), 
( 2, '55544', 'Andy', 'Warren', 130000.0000 ), 
( 3, '77788', 'Brian', 'Knight', 140000.0000 ), 
( 4, '98765', 'Tom', 'Thumb', 100000.0000 )

GO

I now run this code:

DECLARE @i INT = 3, @j int;
SELECT CHOOSE(@i, FirstName, LastName, Salary)
 FROM dbo.Employees AS e
GO

What happens?

Answer: All 4 salaries are returned in a single column result set

Explanation: The index in this case acts as the array choice and "salary" is the value selected. As this is inside a SELECT query, this column is returned, with all matching rows. Ref: CHOOSE() - https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-choose-transact-sql?view=sql-server-ver15

Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 - Administration

What happens when I drop a huge table with LOBs? will the space be reused? - Thanks in advance for your help. I have a 5TB database with mutliple filegroups. The primary filegroup has one file, the .mdf and which is about 2.5TB. About 700GB of the space in the primary FG is made up of a single table which contains LOBs (Varbinar(max) column). After copying the table to a new […]

Finding experation date - Hi, We installed the SQL Server evaluation copy, and I am trying to find out when it will expire. Is there a way I can query the system to find this information? Thank you

Auditing logins, logoffs, better way to do it in 2017? - A bit of history - When the company I am doing work for implemented SOX practices about a decade ago, one of the things that came out was that for certain "critical" databases, all logins (and logoffs) were to be tracked, which program was used to connect, which workstation, os user and database account. At […]

SQL Server 2017 - Development

SELECT FROM sys tables does not work with 3 part database naming? - I am running a query from a utilities database, and I want to query a vendor database we have. I am running the query below from inside the vendor database and it works as expected. SELECT * FROM sys.dm_db_index_usage_stats xINNER JOIN sys.indexes iON x.object_id = i.object_idAND x.index_id = i.index_id Then when I change to […]

SQL Server 2016 - Administration

DBCC CheckDB causes TempDB grows massively ? - Hi Currently we are running DBCC CheckDB script in our server using SQL JOb every week Concern is it takes 3 hours and affect Tempdb Size grows massively after 4 times running in a row ( first time was successful and the rest is failed ) I am thinking to adjust the parameter so it […]

Replicate a Table Receiving Inserts From a Publisher and Replicate Back - Let me quickly explain my situation and I've also included a diagram. My company is in the process of migrating to a better and sound high availability design but we need to have a temporary solution for the design we have currently. For redundancy, we have a transactional replication setup from Primary to Subscriber. We […]

SQL Server 2016 - Development and T-SQL

Surprised that Row_Number() has better performance than IDENTITY - I was doing some testing to see if it's more efficient to use an identity column or to use ROW_NUMBER() to simulate a unique ID for the result set. My thinking was that ROW_NUMBER() would need to do some type of evaluation of the result set in order to assign the value where IDENTITY would […]

How to add Computed column with URL (Fixed string + another column value) - Hi, I want to add a new column to the table. Eg Table A ID 1 2 3 Desire Output Table A ID URL 1 www.google.com/sessionid = 1 (i.e 'www.google.com/sessionid'+ = ID) 2 www.google.com/sessionid = 2 (i.e 'www.google.com/sessionid'+ = ID) […]

Administration - SQL Server 2014

DB Mirroring - Mirror server went down completely.Principal server is up and running. So once the Mirror server is back online, does the DB Mirroring will be re establish or the mirroring needs to be reconfigured? There is no witness and it is configured as async mode. Thanks

SQL Server 2019 - Administration

Sudden failure of Kerberos delegation with linked servers - This is not limited to SQL 2019, my issue is that suddenly on 1/8 all the linked servers on various SQL servers, that use Windows authentication, stopped working due to delegation failure ("Login failed for NT AUTHORITY/ANONYMOUS LOGON"). Kerberos authentication works fine on all servers, but not delegation. This applies to a variety of SQL […]

SQL Server 2019 - Development

group by ignore null value - Hi, I am not actually a programming but my job did need to write a bit of sql queries. Hope someone can help me here is my code select i.item_code,i.cost_standard,(select item_price.item_price where item_price.price_group = 'wholesale') as A,(select item_price.item_price where item_price.price_group = 'vnd')as B from item i left join item_price on i.item_code = item_price.item_code where i.item_code […]

Integration Services

Excel Connection Error: "External table is not in the expected format" - I hope your are doing well. I've been breaking my head since yesterday. I hope you can help me. I have an error on the production server. SSIS can no longer connect to an Excel file. I have an error "external table is not in the expected format". I work with Excel 2010 32 bits […]

General

Where is the Forum FAQ? - Where can a forum user find the FAQ or details on how to perform forum actions like deleting a post? I assume you can delete a post but I can't figure out how to, there is no DELETE link like there is an EDIT or REPLY link and its not clear within the interface how […]

Performance Tuning

Why is this query slow? - #pastetheplan https://www.brentozar.com/pastetheplan/?id=SJF21m0lU I'm trying to figure out what's causing this query (part of a stored procedure) to be so slow. The NOLOCK hints were an earlier attempt to stop deadlocks, I'm guessing they are likely no longer necessary. I did add an index that helped a ton, but this is still pretty slow and I'm […]

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.