Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Call a loved one to catch up and really listen to them

 

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.

Cursors Without Performance Penalties

One of the very common ways that many developers learn to write code is through loops. Almost every language as some sort of FOR and WHILE looping structure. Many people build these in T-SQL, which often creates performance issues as the engine inefficiently deals with the RBAR (row by agonizing row) processing.

That seems like it might change in the next version of SQL Server. There is an interesting research paper from the SQL Server team that explains how they are investigating a technique that will make decode the inner workings of code that uses a cursor and apply the work efficiently to any table operations. If this actually turns out to be true, then cursors in T-SQL might be like cursors in PL/SQL, where this is the preferred way to write code.

Suddenly cursors will have no real performance penalty. If that's the case, I suspect we'll start to see lots of new refactored code in many organizations. Loops make sense and are easy to read. I don't know that I'd change any existing code, but I'd consider changing new code. Do you feel the same way? I wonder how many of you would prefer writing loop based code as opposed to complex single statement SELECTs.

I'm not sure we'll actually change our habits, and you probably agree if you clicked the link. Today is April 1, or April Fool's Day, so this whole thing is completely fabricated. Hopefully you smiled and this helps you cope with the day a little better.

Steve Jones - SSC Editor

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

Redgate SQL Source Control
 
 Featured Contents

SQL v.Next Gets Native Python in SQL Server

Steve Jones - SSC Editor from SQLServerCentral

The next version of SQL Server is in early preview and there is a new feature that is going to be very useful for large enterprises that want to run Big Data Clusters and perform additional data science tasks. Instead of embedding Python code in a string and submitting it to the Launchpad engine, we […]

“Working” From Home? Watch A Bunch of Free SQL Server Videos on YouTube.

Additional Articles from SQLServerCentral

You’re new at this working from home thing, and you’re trying to put Game of Thrones up on a second monitor while you work? Listen, that’s not gonna play out well. You’re gonna get distracted and you’re not gonna get any work done. Instead, if you’re gonna have something in the background, learn about SQL Server.

Never Create Columns with ANSI_PADDING set to OFF

Additional Articles from SQLServerCentral

There is no good reason for having ANSI_PADDING set to OFF when you create tables in SQL Server. It was provided purely for legacy databases that had code that assumed the old CHAR behavior for dealing with padding, and its use has now been deprecated.

SQL Server Transaction Log Management eBook Download

Free eBook: SQL Server Transaction Log Management

Press Release from Redgate

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works.

From the SQL Server Central Blogs - Data BIts – Episode 3 – Ginger Grant

Kevin3NF from Dallas DBAs

Trainer and Consultant extraordinaire Ginger Grant stops by to talk Machine Learning, Data Bricks, Certifications, Norwegian pastries and proper chocolate frosting Find Ginger at Desert Isle SQL or on Twitter
The post Data BIts...

From the SQL Server Central Blogs - Transforming JSON to CSV with the help of Flatten task in Azure Data Factory - Part 2 (Wrangling data flows)

Rayis Imayev from Data Adventures

(2020-Mar-26) There are two ways to create data flows in Azure Data Factory (ADF): regular data flows also known as "Mapping Data Flows" and Power Query based data flows also...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Wargames Quotes

What is the famous quote from Wargames that the computer says to the young human (through the speech box) when he first connects?

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 Cloud Time

I create a server and database in the US-West region in Azure. When I connect to the database, I run this code:

SELECT GETDATE() AT TIME ZONE 'US Mountain Standard Time'

If the current time in Denver (mountain time zone) is 4:34pm on March 30, what is returned? Ignore seconds and lower precision.

Answer: 2020-03-12 22:34 -07:00

Explanation: The answer is returned with UTC time and the offset. In this case, Denver is in daylight savings time and is 6 hours behind UTC. Ref: AT TIME ZONE - https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-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 - Development
Workaround maximum length for a nonclustered index in SQL Server - Hi, hope everyone is well. I need to create a unique nonclustered index but that key lenght is 17oo bytes. I know the work around is to create the index with include. My question is: would that index with unique be unique? Will an insert fail if it is breaking the logic of the unique […]
SQL Server 2016 - Administration
Availability Group Failover - Hi We have a Avaliabilty group setup with a 4 node (plus cloud witness) setup.  Two of each nodes are in seperate dataceneters and HA partners. It hasn't been an issue until now but in the last 5 crazy days one node, has a couple of times, failed over to the other HA node.  It […]
Administration - SQL Server 2014
How is access (read,write) granted to NT user logins? - I have a user who is trying to edit a Crystal report. She is getting ODBC errors. When I investigate, I first log using her credentials. When I open OBBC32ad.exe and examine the System datasource, the database tied to this Crystal report is not listed in the drop down of Databases. If I log into […]
Index Optimize - Based on my understanding during the index optimize it will not take the whole database down. However, there would be impact on the performance. Do you agree with this? If yes, would you schedule any jobs during the index maintenance?
Development - SQL Server 2014
Search with Full Text Index vs "normal" search - Hello, i need your help because i am feeling bad with Full text index. I need to do a search for clients, so i have implemented a full text index on my table "b_utentes". My code is fine, is working very well. But, someone ask me to do a code without use of full text...ok, […]
SQL 2012 - General
Always on DB - Log huge and no free space - Hi, So I've got an Always on database that is 1.4TB in size, the Always on is working fine but the log file just keeps on growing, as of now is at 683GB in size with less than .1% free. We have regular log backups and I need to find out whats using all this […]
How to separate value exist on temp table dynamically separated by comma ? - How to separate value exist on temp table dynamically separated by comma ? problem How to separate value exist on temp table dynamically separated by comma ? i work on sql server 2012 i need to separate field TeamName by comma teamname ,teamname+'Date' i need to loop to teamname filed in tmp table #Teams then […]
SQL Server 2012 - T-SQL
Adding another table to a query but only wanting the top 1 for each record - I currently have query that gives me the results i needs BUT i am needing to add a new table/column to the query. select name, state, id, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as app, version, maddr, city, zip, customers.phone, contacts.sort, contacts.email, install_date, population, customers.notes, CASE databasetype WHEN 1 THEN 'Vision' […]
Finding error in nested stored procedure - Hi, Hope everyone is keeping safe from covid-19. I need to find which particular procedure is giving an error in the nested stored procedure. Also, as the log table in the database does not have a separate column for error message, I am unable to go any further. The error I get is - String […]
SQL Server 2019 - Administration
Instance connect through SSMS needs the port - Hi all, I have a weird issue where my ssms cant connect to the instance on SQL Server 2019 if i haven't given the port number. Have tried several things and i couldnt get it to work.   Any ideas what it could be?   thanks R
SQL Server 2019 - Development
Masking part of a filename with a certain pattern - Hi there I have the following input string which represents the name of a file I have created SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv There are 7 files named in the target directory SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv Now this is time date stamped and could be 7_Of_7 of  3_of_3 etc   Now I want to be able to identify the part in the […]
SQL Server Newbies
SQL Server – Transact SQL - varbintohexsubstring - Hello there, I need your appreciated help. My version is : Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 6.1 (Build 7601: Service Pack 1) I have problem with this transaction : return sys.fn_varbintohexsubstring(1,@pbinin,1,0) Because on Activity Monitor of SQL Server […]
SSDT
Expression with multiple IFs - I am trying to create an expression to derive a data column value based off two other data columns.  In English If VARA is greater than three characters long an the first character is "1" then VARB = VARC + ".A" + the last two characters of VARA ELSE If VARA is greater than three […]
Anything that is NOT about SQL!
SQLWatch - Hi, I’m wondering if someone with knowledge/experience of SQLWatch could help me out with something. We have SQLWatch set up on 2 DEV servers and 1 Central monitoring server, its working fine and the data from the 2 DEV servers is coming over to the central server, I can see alerts are being recorded in […]
T-SQL
pulling multiple numbers out of a string - I need help pulling multiple number values out of a string field name is Details and here are some examples of the values DETAILS Maximum width is 13'2", maximum length is 30'. Minimum total size for all custom rugs is 25 sq/ft. Maximum width is 15', maximum length is 25'. Minimum total size for all […]
 

 

RSS FeedTwitter

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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -