Stairway to T-SQL DML Level 5: The Mathematics of SQL: Part 2 (SQLServerCentral 2019-05-29)

 SQLServerCentral Newsletter for May 29, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to T-SQL DML Level 5: The Mathematics of SQL: Part 2]( - [SQL in the City Summits Down Under]( - [Free eBook: Inside the SQL Server Query Optimizer]( - [From the SQL Server Central Blogs - Scripting SQL Server objects with dbatools – Beyond default options]( - [From the SQL Server Central Blogs - DMV’s for the Beginner]( Question of the Day - [Getting the Winners]( The Voice of the DBA  Long Live All Passwords I remember the first time I had to create a password for a system. It was an application that a friend and I wrote, and we wanted to separate the ways that we stored data, so we added a login routine to the system. It stored a (plain text) password, but hey, this was 1983. We loaded the password and made a string comparison with the entry by the user. Since that time, most organizations I've worked in have had password policies in place on the network. These passwords often had expiration dates, with short warnings for users to change their passwords. The requirement to change never seemed to come at a good time, and with the requirement to not reuse one of the last 5 or 10 passwords, this resulted in users often adding a 1, 2, 3, etc. to the same password. As an IT pro, I often tried to get users to choose new passwords, which fell on deaf ears. Microsoft is rethinking the idea of passwords expiring. There's [a piece that explains how they feel this requirement is not helping security](. This is similar to [a piece at SANS]( that notes the practice is not helpful. Changing passwords has a cost to your organization in extra tickets for a help desk, lost productivity from dealing with expiration, and frustration from users. What's worse, far too many users just "update" a password with a number or year. Neither of which is effective. The current advice is MFA (multi-factor authentication), passphrases, and password managers. Personally I try to use all of these where I can, and choose long passwords. It does seem that many companies are updating apps to allow longer passwords and 2FA. I'd like to see this embedded into more frameworks, making it easier for developers to implement secure authentication systems. That, or use existing systems out there, like oAuth, though there is an argument how secure this is. Even if there are issues, it's likely better than what most developers would build. In SQL Server, we have [group Managed Service Accounts]( (gMSA), which are a good way to avoid password management, and ensure complex passwords. We should disable, and set a complex password for, the sa account. While I wouldn't advocate changing this regularly, I would have alerts if the account is enabled. Like most people, I try to avoid changing passwords unnecessarily, especially these days where I have accounts at dozens (or hundreds) of places. I do, however, ensure that all are different and unrelated. At least then if a password is compromised, I don't worry about someone logging into a different service I use with the same credentials. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](  [Redgate SQL Provision](  Featured Contents [Stairway to T-SQL DML]( [Stairway to T-SQL DML Level 5: The Mathematics of SQL: Part 2]( Greg Larsen from SQLServerCentral.com Joining tables is a crucial concept to understanding data relationships in a relational database. When you are working with your SQL Server data, you will often need to join tables to produce the results your application requires. Having a good understanding of set theory, and the mathematical operators available and how they are used to join tables will make it easier for you to retrieve the data you need from SQL Server. [SQL in the City Summits Down Under]( Press Release from Redgate Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today. [Free eBook: Inside the SQL Server Query Optimizer]( Press Release from SQLServerCentral.com This free eBook from Redgate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. From the SQL Server Central Blogs - [Scripting SQL Server objects with dbatools – Beyond default options]( Cláudio Silva from SQLServerCentral Probably you had the need to script out some objects from a SQL Server instance/database and this is quite easy. You just need to right click on the object... From the SQL Server Central Blogs - [DMV’s for the Beginner]( SQLEspresso from SQLServerCentral I give performance presentations at many different events to all levels of SQL Server professionals. Over time I’ve noticed that some DBAs and developers have never looked at the...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Getting the Winners I have created this data frame in R: > champs <- data.frame(c('2019', '2018', '2017'), c('Virginia', 'Villanova', 'North Carolina'), c('Texas Tech', 'Michigan', 'Gonzaga')) > champs c..2019....2018....2017.. c..Virginia....Villanova....North.Carolina.. c..Texas.Tech....Michigan....Gonzaga.. 1 2019 Virginia Texas Tech 2 2018 Villanova Michigan 3 2017 North Carolina Gonzaga > names(champs) <- c('Years', 1, 2) I want to access the winner column, and try this: champs$1 I get an error. What should I do? 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) Remove the Grant I create a new role, called Sales, in my database. I run this: GRANT SELECT, INSERT, UPDATE on dbo.CustomerDiscount TO Sales After adding other permissions, I realize that we don't want users to change data already in this table. What code do I run to remove this ability from the group? Answer: REVOKE UPDATE on dbo.CustomerDiscount FROM Sales Explanation: To remove the permission, we use REVOKE. DENY adds a new permission preventing inserts, but the question is on changing information in the table. Ref: REVOKE - [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 [error Output File cleanup does not delete files script d'olla]( - Hello everyone, I use olla scripts in my backup and maintenance operations there is a step that overwrites the old log file Output File Cleanup this and it comes out with an error Message Run as user: xxxxx \ SQLAGENT2K8QUA. ... 20190214_220307.txt "Access denied." Q: \ Backup \ log "\" DatabaseBackup_FULL_0xBEE0AC49E7982D49842F71412C701125_1_20190228_220234.txt "Access denied." Q: \ […] SQL Server 2017 - Development [Primary Keys]( - What is a good way to determine which combinations of fields should make up a primary key? [Reading data from 0x05]( - Hi We currently have a table, with a column of a varbinary(MAX). This contains data which precedes with 0x50 – from what I understand means that the data has a ZIP format and I believe it has been compressed. We need to be able to read this data. I know that this is a .docx […] SQL Server 2016 - Administration [Linked server error 18456]( - Hello Team, Iam getting the below error while creating the Linked server with windows authentication. Msg 18456, Level 14, State 1, Line 88 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.   [Lock Request Timeout period Exceeded Error 1222]( - A long running job is doing a SELECT INTO a table, so the table gets created at the same time as it's being populated. If I try to expand the DB's list of tables, I get the error "Lock Request Timeout period Exceeded Error 1222"  Is there a way to get around this ? [Error: Nonqualified transactions are being rolled back]( - Every morning, the Prod Backup file is copied to the Reporting server, and restored. This morning, the restore step seems to have completed, but the step shows the following error, causing the overall job to fail, so subsequent steps did not run. Thoughts on the cause(s) for this on a just restored DB ? Error: […] [Restore DB]( - what is the procedure to restore a database of size 2 TB with a minimal down time ? [Migrating from (mostly) 2K8 to 2016]( - HI all, About to start this project and would be grateful for any good comprehensive articles rated highly by the good folks here at SSC TIA  SQL Server 2016 - Development and T-SQL [Organizing and cleaning up AS400 DB2 data in SQL server]( - I've been dealing with data migration out of ancient AS400 piece by piece through linked server connection and I'm having hard time figuring out the best way to structure everything into more or less decent way. I'm joining multiple tables into views to get a piece of data I'm looking for and then creating tables […] Development - SQL Server 2014 [TLS 1.2 configuration with MS SQL 2014]( - Dear Gurus,  I have MS SQL 2014 Installed with following configurations. Windows 2016 SS. MS SQL 2014 + SP3 64 Bit EE. TLS 1.0 Client : Disable Server Disable TLS 1.1 Client : Disable Server Disable TLS 1.2 Client : Enable Server Enable ODBC Drivers : 64 Bit: 11(2014.120.6024.00) MSODBCSQL11.DLL Below query works fine […] [Fetching unique record within a stored procedure]( - I have a table "Mytable" containing a list of IDs. There is a web application and the user logging into the web application is assigned an ID from the table mentioned above. So if 2 users are logging in concurrently, an ID from this table is given to each of the user. The ID and […] SQL Server 2012 - T-SQL [Net Earned in Triangles without a Triangular Join?]( - Something I've been trying to figure today is can I achieve a figure for net earned in our insurance triangles without using a triangular join. I'm sure I could do this with a Window Function, but I can't seem to think of a way to do so. The way the figures work is to do […] SQL Server 2008 - General [Encyrption and change field as same time]( - Hello all. I am doing this to modify a field and encrypt data in that field: --Update EncryptedName field with a new name UPDATE dbo.Tbl_Table SET EncryptedName = 'My Name' Where dbo.Tbl_Table .ID = 1234 Go --Encrypt the field. OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MySymmetricCertificate; UPDATE dbo.Tbl_Table SET EncryptedName = EncryptByKey(Key_GUID('MySymmetricKey'), EncryptedName ) […] [Duplicate title]( - Duplicated post. plz ignore Analysis Services [Need to perform leaf level aggregation]( - So I have a DW with a table with some pre-aggregated scores (not my choice) and the counts of animals separated by breed and a date. To calculate the average score across breeds, I need to the following calculation: ([AverageScore]*[CountOfAnimalsForBreed])/TotalCountOfAnimals The problem is that when the cube rolls up for a specific date, it is […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -