Stairway to Advanced T-SQL Level 11: Using Logical Operators (2020-11-04)

 SQLServerCentral Newsletter for November 4, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Advanced T-SQL Level 11: Using Logical Operators]( - [Explore the Role of Normal Forms in Dimensional Modeling]( - [Monitoring Amazon RDS SQL Servers with SQL Monitor v11]( - [Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA]( - [From the SQL Server Central Blogs - ASF 034: Alex Yates interview (part 1)]( - [From the SQL Server Central Blogs - Migrating SQL Server container images to the Github Container Registry]( Question of the Day - [Enumerate the Python List]( The Voice of the DBA  Daily Coping Tip Find a new perspective on a problem you face 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. Maintaining Data in a Flow I've seen many creative solutions for implemented in the data platform. Often these are data driven solutions that help us work at scale. This are most often with T-SQL or PowerShell (PoSh), as those are the two most common languages. Less often I see Perl, Bash, or Python, though I expect that latter to grow in usage over time. Recently I saw a presentation where someone was using PoSh to solve a problem. In this case, they were reading some data from a text file and using that to drive the solution. That's a common pattern I've seen, but what about maintaining that text file, or that data? Too often I've seen a process set up that might use a text file, or more often, uses data in a table. This gives us a data driven, maintainable system, but no one maintains it. Or most often, someone maintains it for a bit but then stops. That's natural. As humans, not many of us are good at doing the same type of action over, and over, and over again, without mistakes or forgetfulness. This is especially true in an organization where people change jobs, take vacation, and have other interruptions to their work. One of the things that DevOps has tried to get organizations to embrace is the use of automation to keep a process going over time. We try to remove the dependency on any particular person, or people. I like the idea of a PoSh script reading from a text file, but the more complete solution mentions or describes a way to maintain the text file. Is there some inventory system that produces this, or maybe some deployment process that includes a way to update this file and ensure the process grows as our organization does. Building a complete solution that adapts to a changing environment is hard, but it ought to be our goal. Ensuring that we account for data maintenance is something we ought to consider as we close the loop between building , deploying, and operating software. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Advanced T-SQL]( [Stairway to Advanced T-SQL Level 11: Using Logical Operators]( Greg Larsen from SQLServerCentral SQL Server supports a number of different logical operators. These operators can be used for testing Boolean conditions that return true, false and unknown in your T-SQL code.  Logical operators are useful for defining constraints to limit the rows be processed when selecting or updating data. This chapter will provide an overview of the logical […] [Explore the Role of Normal Forms in Dimensional Modeling]( Additional Articles from MSSQLTips.com In this tip we walk through an exercise of developing a data model based on the characteristics of the data requirements. [Monitoring Amazon RDS SQL Servers with SQL Monitor v11]( Additional Articles from Redgate SQL Monitor v11 introduces native support for SQL Server instances hosted on Amazon Web Services (AWS) Relational Database Service (RDS) platform. In this post we'll explore the metrics you need to keep an eye on with Amazon RDS SQL Server monitoring, how a monitoring tool will help you manage the process of migrating databases to the Amazon cloud, as well as measures its success, and how SQL Monitor allows you to monitor all your SQL Server instances, wherever they are hosted, through a single pane of glass. [Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA]( Press Release from Redgate Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks". From the SQL Server Central Blogs - [ASF 034: Alex Yates interview (part 1)]( KamilN78 from SQL Player Blog Introduction Alex is a Data Platform MVP who loves DevOps. He’s has been helping data professionals apply DevOps principles to relational database development and deployment since 2010. He’s most... From the SQL Server Central Blogs - [Migrating SQL Server container images to the Github Container Registry]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold A couple of months ago Docker announced that they would be implementing a 6 month retention policy for unused images in the Docker Hub. This was due to kick...   Question of the Day Today's question (by BTylerWhite):  Enumerate the Python List I have the following code in Python: animals = ["cat", "dog", "elephant", "fox", "horse"] for index, animal in enumerate(animals, start=1): print(animal) print(index) What will be the result of printing the index variable after the loop completes? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Thom A) UTF8 and varchar lengths Consider you have below table with the following single value inserted: CREATE TABLE dbo.Test (S varchar(10) COLLATE Latin1_General_100_CI_AI_SC_UTF8); INSERT INTO dbo.Test (S) VALUES(N'?'); What would be the resulting value returned from the below query? SELECT LEN(REPLICATE(S,10000)) AS ReplicatedLength FROM dbo.Test; Answer: 2666 Explanation: When declaring a length of a varchar (or nvarchar) the length parameter does not mean the total number of characters that can be stored, but the number of bytes. Both datatypes, without using MAX, can store up to 8,000 bytes; varchar stores the characters using single bytes while nvarchar double bytes (hence why it is capped at 4,000). When using a collation such as a UTF-8 collation, each characters stored in the string can use different amount of bytes. A character like "A" uses a single byte, "æ" uses 2 bytes, and "?" uses 3. As a maximum of 8,000 bytes then only 2,666 ? characters can be stored (with 2 bytes spare) in a varchar(8000). The reason that 10,000 characters are not stored is that REPLICATE will not implicitly convert a non-MAX datatype to a MAX. As a result the varchar's length property is "capped" at 8,000 bytes, and any characters that did not fit are truncated. Ref: - [char and varchar (Transact-SQL)]( - [REPLICATE (Transact-SQL)]( [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 [SQLServer Always-On and Bitlocker]( - Hi all, we have installed MSSQL 2017 with HA and Always on AGs on a Bitlocker-encrypted device. The latter is unencrypted on Bootup. The Service account for SQL is gMSA/AD-based. On server startup HA is brought up correctly but the SQL service is not started, hence it is configured for autostart. We have tried to […] [Foglights]( - Hi,  We are looking into the monitoring software foglights? any thoughts? SQL Server 2016 - Administration [I cannot add a replica because of difference between instances]( - Hi There, Let me start with to say I am not a SQL administrator, but I would like to understand. I was busy with creating a sql cluster, so far so good. I created a database, created a backup, enabled Always on on every instance and whilst creating the availability group with the wizard I […] [DQS LogOnWindowFailedToConnect]( - HI every one I'm running into problems in DQS I'll appreciate your advice, I tried several things but it is not running yet, we have the following error: SQL Server Data Quality Services --------------------------------------------------------------------------------  Message Id: LogOnWindowFailedToConnect Cannot connect to SERVER\INSTANCE.  A .NET Framework error occurred during execution of user-defined routine or aggregate […] [Queries]( - I am looking to see if there are any ways to improve the run time for select queries. Thoughts and ideas? I have select * from Table takes approx 40 mins. I know if I select required columns that might be little faster. But I need all columns for ETL. Select w/o data from tables […] [Looking for good beginner Data Base Administration book for advanced developer]( - Hello SSC, I have been a SQL developer for close to 20 years. I know some DBA tasks like, backup restore, installation, granting access to users, etc. I have been trying to find a beginners book that can slowly walk me through all of the common administration tasks. I think like a developer, so topics […] SQL Server 2016 - Development and T-SQL [sys.dm_db_index_physical_stats returning wrong object_ID in MSDB database]( - I'm running an index maintenance script on the MSDB database that loops through a table of indexes with fragmentation over 10% and right after applying Security Update for SQL Server 2016 Service Pack 1 GDR (KB4505219)(0000)(x64)(en), the script started generating an error at this point: ALTER INDEX [pk_MSdbms_map] ON [dbo].[ExternalMailQueue] REBUILD Msg 1914, Level 16, State […] Administration - SQL Server 2014 [How to minimize the SQL Server log growth]( - In SQL Server 2014, I go to my database properties and tempdb's database properties and change the recovery model to simple to avoid the big log growth, I need to use select sql statment to query data from one table and then insert into another table, I know if I use where statement to perform […] [Database backup/checkDB problems]( - Hi all, Got a very odd problem, and one I'm hoping has a solution. Running the following code: BACKUP DATABASE [MyDB] TO DISK = N'MyLocation\MyFileName.bak' WITH NOFORMAT, NOINIT, NAME = N'MyFileName', SKIP, REWIND, NOUNLOAD, STATS = 10 Results in the following exception: Msg 3203, Level 16, State 1, Line 1 Read on "MyLocation\MyDB.mdf" failed: 1117(The […] Development - SQL Server 2014 [Do I need a trigger?]( - I want to test a condition during an insert or an update and only permit the insert or update if the condition is valid. The condition requires the evaluation of a value in multiple rows. My scenario is this; Multiple people can participate in the review of a submission. Only one person (at a time) […] [How to improve the performance of insert?delete?update sql statement](e3%80%81delete%e3%80%81update-sql-statement) - as it was said in the title, which we should pay attention to when optimizing insert?delete?update sql statement? thanks The following points is what I know : don't use big transaction perform insert?delete and update sql statement by batch , and the numbers of batch is not too big if there is where statement, we'd […] SQL 2012 - General [TDE Drama]( - My workplace uses a 3rd party key management system as an encryption provider for TDE. Most of the time, things go swimmingly, but apparently this morning I totally FUBARed one of our non-prod servers. I enabled the EKM provider stetting, created the Cryptopgraphic provider, created the initial credential, created a windows login that I added […] SQL Server 2019 - Administration [Any way to use ReportServer in HA AND have two separate SSRS servers?]( - This is a little awkward to explain, but we have 2 SQL Server instances that have a Standard license. Based on this article, it is entirely possible to setup availability groups for the SSRS DB's (ReportServer and ReportServerTempDB) The author of that article clearly uses Enterprise since hes got two DBs in one availability group. […] General [Tip for getting trifocals made for computer monitor use.]( - I want to share with those of you advancing in years something I learned over 40 years ago regarding trifocal lenses for using with computer monitors. I was in my 30's when I developed the need for trifocal lenses. When I got my first pair I soon discovered that the 'standard' narrow band of focus […] Analysis Services [Import a PBIX file into Azure Analysis Services]( - I want to import a Power BI PBIX file into Azure Analysis Services. I don't see any recent information on how to do that. Is it possible? If so, how?   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -