SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

SWITCHOFFSET()

The Voice of the DBA

Daily Coping Tip

See how many people you can smile at today

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

The Multilingual Programmer

This editorial was originally published on Feb 24, 2017. It is being re-run as Steve is on holiday.

At the recent SQL Konferenz in Germany, the keynote was from Michael Rhys of Microsoft. His talk was on the evolution and design of the U-SQL language. If you haven't looked at it, U-SQL is what the Azure Data Lake (ADL) uses, and it's designed to improve your ability to query various data sources in the ADL. If you want to know more, and begin working with U-SQL, we have a stairway you can go through.

Michael opened his talk by looking at the languages he'd learned in his career. He started with APL and moved on from there. He asked if anyone had used APL, and there were few of us. It was my second language at University, and one I didn't enjoy. The nature of the language was un-intuitive to me, and I was glad I only suffered for a few months. If you'd like to try it, you can tryapl.

I thought this would make a fun discussion, so I wanted to ask: what languages did you learn for programming and in what order?

For me, I started with BASIC, and a little assembler with early systems. I moved to Pascal in high school, trying to develop fun games and computer assisted homework help for myself. In University, I began with LISP, which caused plenty of people to drop out of computing. I'm not sure if that was a good idea or not, but I enjoyed that. From there, I went to APL, Assembler, Fortran and C before switching away from computers for a bit. When I returned, C++ was all the rage, and I soon found jobs that paid me to write FoxPro/Clipper code, then VB, then a touch of Java before the web became popular and I worked in ASP and ASP.NET. Along the way SQL became more and more of my career, and I'm glad it did.

These days I'm trying to improve my C#, PowerShell, and Python skills, more for fun than anything else, but those are sueful as both languages are useful in data work. I haven't done much with R, but I have fingers crossed that the sp_execute_external_script call that allows a parameter of @language=N'Python' gets added to SQL Server before I need to learn any R. After all, most of the R libraries exist in Python, and I find the language much more intuitive.

Let us know today what your journey has been, and if you haven't been a developer, maybe its time to learn some programming skills. After all, I think that's important for a DBA.

Steve Jones - SSC Editor

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

Featured Contents

Change the Instance Collation by Rebuilding Databases in SQL Server 2017

iLearnSQL from SQLServerCentral

As a part of my DBA activities, I do a lot of SQL Server installations every week. Most of the time, I install the instance with the default collation. A collation is a configuration setting in SQL Server that determines how the database engine should read the data. SQL Server has huge list of collations […]

The Lock Escalation Threshold – Part 3

Additional Articles from SQLPerformance.com

The examples so far in this series have all used repeatable read isolation to acquire shared locks on qualifying rows and hold them to the end of the transaction. This was convenient for the demos but repeatable read isolation isn’t common in everyday use.

Building a SQL Computational Framework for MACD Indicators

Additional Articles from MSSQLTips.com

This article looks at how to calculate MACD indicators using a set of T-SQL code and data stored in a SQL Server database.

From the SQL Server Central Blogs - Types of data consulting engagements and what you can expect from them

Meagan Longoria from Data Savvy

There are multiple ways organizations can engage with a data (DBA/analytics/data architect/ML/etc.) consultant. The type of engagement you choose affects the pace and deliverables of the project, and the...

From the SQL Server Central Blogs - Book Review – SQL Server Query Tuning and Optimization (2nd Edition)

Koen Verbeeck from Koen Verbeeck

The nice people from Packt Publishing sent me a digital copy of Benjamin Nevarez new book to review: SQL Server Query Tuning and Optimization – Optimize Microsoft SQL Server...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	SWITCHOFFSET()
The SWITCHOFFSET function will help you change a datetimeoffset value to a new time zone with a second parameter. What does the second parameter represent?
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)

The 12 days of Christmas

I've created a couple of tables that handle the gifts from the Twelve Days of Christmas song. Here's the code:

CREATE TABLE TwelveDays (gift VARCHAR(100))
GO
INSERT dbo.TwelveDays (gift) VALUES 
( 'A partridge in a pear tree'),
( 'Two turtle doves'),
( 'Three French hens'),
( 'Four calling birds'),
( 'Five gold rings'),
( 'Six geese a-laying'),
( 'Seven swans a-swimming'),
( 'Eight maids a-milking'),
( 'Nine ladies dancing'),
(  'Ten lords a-leaping'),
(  'Eleven pipers piping'),
(  'Twelve drummers drumming   ')
GO
CREATE TABLE Numbers (n INT, word VARCHAR(20))
GO
INSERT dbo.Numbers (n, word) VALUES 
 (1, 'A '),
 (2, 'Two'),
 (3, 'Three'),
 (4, 'Four'),
 (5, 'Five'),
 (6, 'Six'),
 (7, 'Seven'),
 (8, 'Eight'),
 (9, 'Nine'),
 (10, 'Ten'),
 (11, 'Eleven'),
 (12, 'Twelve')
GO

I want to be sure the rows return in order. I have this query sketched:

SELECT gift 
 FROM dbo.TwelveDays AS td
 INNER JOIN dbo.Numbers AS n

 ORDER BY n

which ON clause should I use?

Answer: SUBSTRING( gift, 1, CHARINDEX(' ', td.gift)) = n.word

Explanation: I need to find the substring that contains the number of gift items and use that to match against the numbers table. From these choices, only using CHARINDEX to find a space and using that as the end of the SUBSTRING value (from 1) works. Ref:

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

SQL Server database monitoring tool. - Hi Expertise, Kindly suggest SQL Server database Centralize monitoring tools with historical reports for all target DB Servers ON-Prime and CLOUD based setup. Thanks

Why are the logical reads appearing in IO STATISTICS even after cleaning Cache - Hello, I am trying to understand the output of IO STATISTICS . I run some queries like as bellow : DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS SET STATISTICS TIME ON SET STATISTICS IO ON SELECT * FROM [HumanResources].[Employee] SET STATISTICS TIME OFF SET STATISTICS IO OFF The output is SQL Server Execution Times: CPU time = 0 […]

SQL Server 2017 - Development

A question about unique keys - I've been away from SQL Server for a number of years. Years ago what I am asking could not be done. I assume that it still can't be done, but rather than make an assumption I thought that I should ask. Below is a completely fictitious example, but it illustrates my question. Supposing that I […]

SQL Server 2016 - Development and T-SQL

For Each Loop Query (SSIS) - In above screenshot can anyone tell me how can I enter value of "Files" field as expression?

calculate 12 months of data from a given date from a user - I have a query that it seems to be off dont know what wrong but I need to show last 12 months of data based off a user input for a date. my query is this. My results show different in SSRS. /*Declare @Date as Date = '11/1/2022' Declare @prop as int = 8000 --*/SELECT […]

Administration - SQL Server 2014

Parallel Execution Plan - Or not. - Hello. SQL Server 2014 - 3 Numa Nodes - 6 processors in each Numa Node (not that is not the issue) - MAXDOP = 6. I have been running some testing with Substring Queries (LIKE) vs FTS Catalog (CONTAINS) Search and experimenting with CHARINDEX and COLLATION (Binary). As part of the testing with CHARINDEX and […]

Error on File - Hi Experts, I have a database with multiple files on multiple drives and one of the drive is restricted to a defined size. Now because the index maintenance job is failing with below error. How can I rectify this without increase the disk space. Could not allocate space for object 'dbo.Table1'.'PK_Table1_ID' in database 'DB1' […]

how to check the table is being used(selecting/inserting/updating/deleting/locki - how to check the table is being used(selecting/inserting/updating/deleting/locking) by some sql statement? many thanks!

SQL Azure - Development

Azure DevOps and branches - Hi all Firstly, full disclosure, we're only getting started with this so late to the party (and complete novices). Tools we're using (not just for DevOps):- SQL Clone SQL Compare SQL Prompt SSMS (V18) We're just getting started with Clone and found it to be very useful. The bit that's not so […]

Azure Data Factory

Please help , how to write data in data lake ? - I want to read data from json file and write in a data lake by azure data factory, so can anyone tell me the procedure I am new to data engg field, Thanks in advance

Upskill Azure Data Factory knowledge - Hey guys, I'm a DE with of 2 experience and I've been working with Azure Data Factory as the main ETL tool for 6 months now. However, I feel I need to gather more knowledge and assume more responsibilities inside my team but most of the senior professionals don't have much time to teach me. […]

SQL Server 2022 - Development

Using SQL - Convert column to rows - not a straight forward pivot - Hello All, I have below sample table. Code is also included. create table Trade_test (portfoliocode nvarchar(10), tradedate varchar(10), tradeamount varchar(10) ); insert into Trade_test values('A','01012021','100') insert into Trade_test values('A','01012022','1000') insert into Trade_test values('B','01012018','100') insert into Trade_test values('C','01012019','100') I need to pivot the data to have below output. I have tried few things but not getting […]

What's the logic of the NOT IN clause? - I thought I understood the "NOT IN" logic very well until I run into a case where the presence of NULL values affects the result in a way I was not expecting. The setup is super simple: CREATE TABLE A1(ID1 INT); CREATE TABLE A2(ID2 INT); INSERT INTO A1(ID1) VALUES (1), (2), (NULL); INSERT INTO A2(ID2) […]

Struggle Bus - Varbinary(max) Performance Suggestions? - I have a very large table I am dealing with that has a varbinary(max) column. I have a Order By that is extremely slow on this field and also I have a Convert as well to a NVARCHAR(MAX) but even without the convert it is slow. How can I somehow increase the performance on this […]

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.