SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Adding N/A

The Voice of the DBA

Daily Coping Tip

Thank three people you feel grateful to and well them why

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.

Using Dynamic SQL

There are many ways that you can structure your T-SQL code to solve complex problems. Throughout my career, I've learned different ways, and also some patterns to avoid. However, those change over time. For example, there was a time when heavily loading tempdb caused issues in the v6 era. As a result, I tried to avoid using them. These days, I'd consider them a good option to think about.

One of the techniques that I see people trying often is Dynamic SQL. This is where you don't write the code directly, but you write code that will put together a string of code to execute, and then you call this with EXECUTE() or some other mechanism to get your results.

There are places where you might find Dynamic SQL useful. I saw an article from Erik Darling recently that gives you some situations where Dynamic SQL can help. These are often situations where performance is worse with a bunch of conditional code that is jumbled together. Separating out code into different procs or functions can help here, but that becomes a maintenance headache in many ways.

Dynamic SQL isn't something to just try without thinking carefully about the ramifications. SQL Injection is a real concern, though you can mitigate the risk with some careful coding. However, you need to be careful how you structure code, and don't just concatenate strings together and execute them. Read up on good practices from others, and ensure you test your code thoroughly. In fact, this is one place where automated testing can become important, as you learn of different injection techniques, you can add new tests to ensure your code isn't vulnerable.

As you work with T-SQL, it's important to continue your education, learn how the platform and language work, how performance changes between versions, and what options are to be adopted or dropped. Becoming a better T-SQL developer takes work and practice, and it's an area where we should be constant students.

Steve Jones - SSC Editor

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

Featured Contents

Monitor SQL Server Error Logs with PowerShell

WillemG from SQLServerCentral

Receive email notifications of non-standard entries in your SQL Server Error Log files with a little PowerShell.

Using Azure SQL Data Sync to Replicate Data

Additional Articles from MSSQLTips.com

Learn how to setup and use Azure SQL Data Sync to synchronize and replicate data from various locations including Azure SQL Database, cloud and on-premises databases.

Win a Pluralsight subscription with Redgate’s competition

Additional Articles from Redgate

Redgate is giving you the chance to win a three-month subscription to the technical skills platform Pluralsight and Redgate goodies with their latest competition. To enter, visit the Redgate Forums and answer the question ‘What’s your favorite SQL Monitor feature and why?’

From the SQL Server Central Blogs - Automatic Sample Sizes for Statistics Updates

matthew.mcgiffen 73574 from Matthew McGiffen DBA

Looking at how SQL Server samples your data when doing auto-stats updates. Part of a series on understanding statistics, to help you achieve optimal performance of your queries on...

From the SQL Server Central Blogs - Cannot expand VMDKs with NVMe storage controller on VMware without a reboot

kleegeek from Technobabble by Klee from @kleegeek

After doing some digging today with a Client, we hit a roadblock on something that I need to share with using the NVMe virtual disk controller on the VMware...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Adding N/A
I have a query that returns sales totals from a table. My query is: SELECT ms.saleyear , ms.salemonth , ms.currMonthSales , LAG (ms.currMonthSales, 1) OVER (ORDER BY ms.saleyear , ms.salemonth) AS prevsales FROM dbo.MonthSales AS ms ORDER BY ms.saleyear DESC, ms.salemonth desc; The currMonthSales value is a numeric value. I want this to display "N/A" when the value is NULL. What should I do to the query?
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 Future Synonym

I have a new instance of SQL Server 2019 installed. The only database I have created is the "sandbox" database. I run this code:

CREATE SYNONYM mysyn FOR newdbw.dbo.sometable

What happens?

Answer: The synonym is created with deferred name resolution

Explanation: The synonym is created with deferred name resolution. Ref: CREATE SYNONYM - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-synonym-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

"SQL Server Windows NT - 64 bit" in Task Manager: Memory and CPU high - Hello, It's weird that suddenly I can't access to my sql server database (it says it's being recovered) and i found that the sql service is using a LOT OF my memory and disk in the task manager Could any one help me please to resolve this ? Thank you

Long SSIS job failed with connection failure - I have a long ETL SSIS process, one step is to call a web service. The web service is slow for it process each person's record. Sometimes the job time out after run a couple of hours. with the below message: Status: CreateDriverDirectionsReport.ProcessRoutePath: [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation Status: CreateDriverDirectionsReport: Connection failure […]

SQL Server 2017 - Development

Some questions regarding Service Broker - Hi all, I have a third-party database/application that I'm attempting to augment with functionality using Service Broker. The scenario is the following: Through the application, INSERT/UPDATE/DELETE operations are performed on SourceTable, in SourceServer.SouceDatabase When this occurs, I want to perform a similar INSERT/UPDATE/DELETE on DestinationTable, in DestinationServer.DestinationDatabase I have no control over the application, nor […]

Lots of CTE and dynamic query - I have tried optimizing the query maximum, but still any inputs will help declare @firmID Varchar(50) declare @query as nvarchar(max) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET @query = ' WITH CTE_Firm_Option_Field AS ( SELECT FOF.Field_label , FOF.Field_used , FOF.Field_id , FOF.Field_type FROM dbo.Firm_Option_Field FOF WITH(NOLOCK) WHERE firm_id='+@firmID+Char(10) +Char(13) +' ), CTE_Firm_Roles AS( SELECT […]

SQL Server 2016 - Administration

Find SSRS reports with custom data source - Greetings, I've found a few threads similar to what I'm looking for but not exactly. I have a couple hundred reports in SSRS, and most of them use a "Shared" data source, but a small handful of them have a "Custom" data source. I am a novice with SQL. I just need to find out […]

Issue with replication - Our network team recently updated our GPO and since that time, we are having an issue with a transactional replication. The symptom is that the Snapshot Agent fails to start with an error that "A required privilege is not held by the client." Our SQL Server Agent is running as an windows account; one suggested […]

Multiple listener for same AG - I am trying to educate myself and understand what are some of the advantages of creating multiple listeners for the same AG? Any feedback is highly appreciated.

T-log Alert - i need to configure alert on multiple servers when the t-log reaches or exceeds threshold limit . i dont want to configure alert on each server for this . how can we configure for all servers in a single script and request to guide to achieve this .

SQL Server 2016 - Development and T-SQL

Array into table. - Hi I'm tasked with importing data into SQL thats pretty much JSON but not quite . I've used OPENROWSET/OPENJSON to import into a staging table and the data looks like this What I need to achieve is migrate that to a single table with the following structure I'm having no success , I even […]

Development - SQL Server 2014

trigger email alerts in case file is delivered or failed for the job - Hi all, I have two SQL jobs. These jobs execute SSIS packages and then send files to e-mail. I need to create one more SQL Job that would be send an alter to email about file delivering (file was success delivered or not). Could you help me? Thanks

Preventing concurrent inserts - Hi all, In this, very simplified scenario, we have a table with two fields and few records: create table log_test1 (c1 int, c2 int) go insert into log_test1 values (1,11), (2,12) go In a stored procedure, we select max from one of the columns, add one, and insert back into the table: select @v1 […]

SQL Server 2019 - Administration

Developer vs Enterprise - Hi Experts, Is there any difference between Developer & Enterprise Edition of SQL Server (2019 ,2017 & 2014) both in features & performance wise? TIA

Who Update the Stored procedure Last time? - Hello EveryOne, Need your help once again. I have lots of Stored PRocedures in my Database and we are 3 sql dba/developer . But need to know who updated the stored procedure last time? or by whom the stored procedure was updated last time?? Any Help or suggestion would be appriciated. Thanks in advance.

SQL Server 2008 - General

SQL Server maximum Server Memory - I am using SQL Server 2008 R2 Express edition. I have already set Maximum Server memory to 2Gb. Is there any benefit if I Change it from 2Gb to 3Gb?

Powershell

Export table to csv including varbinary datatype - I'm exporting all tables in a databases to individual csv files using the code below and it's working well except that varbinary and timestamp datatypes are exporting as System.Byte[] instead of the value. $server = "ServerName" $database = "DatabaseName" $tablequery = "SELECT schemas.name as schemaName, tables.name as tableName from sys.tables inner join sys.schemas ON tables.schema_id […]

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.