Building Better Stored Procedures (SQLServerCentral 3/14/2019)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [Building Better Stored Procedures]( - [Introduction to SQL Server Security — Part 3]( - [Git, GitHub, GitLab, BitBucket, What’s the difference?]( (From the SQLServerCentral Blogs) - [Scalar UDF Inlining in SQL Server 2019]( (From the SQLServerCentral Blogs) The Voice of the DBA The Soft Skill of Respect When I started to work more closely with the product development teams at [Redgate Software](, I found it interesting that we devoted so many resources to user experience, testing, and other non-development tasks. Coming from more focused groups, this was new to me. At times this felt like an indulgence, and at times a lot of overhead that slowed down the coding process. Over time, I've grown to appreciate having different skills in our teams. Not that we build software in the best way possible, and certainly not the quickest, but we have some well coordinated and knowledgeable teams. We're still evolving and learning how to put together teams of people and effectively build software, but I think we're moving in the right directions. As soon as I read [this piece on hard and soft skills](, I thought of Redgate and all the non-coding people that build our products. What struck me was how often I'd devalued non technical skills, but the more I work with our (and other) software, the more I appreciate that those other roles are important. The piece rambles and wanders around the point, but I do see that there are a couple important takeaways I get from this. First, we need to respect others that perform different work from us if we are going to build synergy in our teams. The synergy is creating something that none of us could do individually, or even separately. We get more done by working together and that means we must be a team. Teams only come from respect between individuals, and that includes the managers. The second thing that I realized, is that it is important to understand a basic level of what other jobs entail. Too often we data professionals have been upset that application developers don't know more SQL. Many of us learn some of the domain specific knowledge of the data we deal with, in order to talk with customers, produce reports, etc. Following our own advice, we ought to know more about the development platforms, the challenges, their testability, and more, if for no other reason than we can better understand how the entire system works and can talk about it with others I certainly know that relationships and people are incredibly important in larger projects, which is another important takeaway from the article. We all do need to improve our hard skills, learning to code more efficiently and securely. We also need to remember the importance of softer skills, and the need for a wider variety of skills in our teams. A little appreciation for the need for more than expert level coders might help us create better teams that function more efficiently and help us produce better software. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( --------------------------------------------------------------- The Voice of the DBA Podcast Listen to the [MP3 Audio]( ( 3.8MB) podcast or subscribe to the feed at [iTunes]( and [Libsyn](. [feed]( The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. ADVERTISEMENT [SQL Prompt]( Become a more efficient SQL developer with SQL Prompt Learn how to write SQL faster and more efficiently with these exclusive short videos from Data Platform MVPs and SQL Server experts. With SQL Prompt you can strip out the repetition of coding and write SQL 50% faster. [Check out the tips]( [Database DevOps]( Continuous Delivery for SQL Server Databases Spend less time managing deployment pain and more time adding value. [Find out how with database DevOps]( Featured Contents  [] [Building Better Stored Procedures]( Steve Jones from [SQLServerCentral.com]() In a followup, this article includes discussion of more advanced features of using stored procedures.[More »](Procedures/183979/) ---------------------------------------------------------------  [] [Introduction to SQL Server Security — Part 3]( Additional Articles from [SimpleTalk]() SQL Server supports partially contained databases. This gives you the option of adding database users with a password inside the database. This makes the database easier to move to another instance or participate in an Always On Availability Group. In this article, Robert Sheldon explains how to work with users in contained databases.[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Git, GitHub, GitLab, BitBucket, What’s the difference?]( Steve Jones from [SQLServerCentral Blogs]( I was watching Brent’s webcast session on GitHub recently. I’ve struggled to explain this to users in the past, and...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Scalar UDF Inlining in SQL Server 2019]( Wayne Sheffield from [SQLServerCentral Blogs]( How does Scalar UDF Inlining affect the performance of scalar functions? SQL Server 2019 introduces a new feature called “Scalar UDF...[More »]( Question of the Day Today's Question (by Evgeny Garaev): I have two simple tables created and populated like this: create table t1(c1 int); create table t1(c1 int); insert into t1(c1) values(1), (2), (3), (4); create table t2(c2 int); insert into t2(c2) values(1), (2); What will be the result if I execute a query like this: select c1 from t1 where t1.c1 in (select c1 from t2); Think you know the answer? [Click here](, and find out if you are right. --------------------------------------------------------------- We keep track of your score to give you bragging rights against your peers. This question is worth 1 point in this category: T-SQL. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. Yesterday's Question of the Day Yesterday's Question (by Steve Jones): I have a data frame with some baseball data: > HR.hitters rank players Hr yrs.played 1 1 Barry Bonds 762 22 2 2 Hank Aaron 755 23 3 3 Babe Ruth 714 22 4 4 Alex Rodriguez 696 22 5 5 Willie Mays 660 22 6 6 Ken Griffey, Jr. 630 17 7 7 Albert Pujols 634 22 8 8 Jim Thome 612 22 9 9 Sammy Sosa 609 18 10 10 Frank Robinson 586 21 I want to retrieve just the player name and home runs for Ken Griffey, Jr. and Albery Pujols. How can I get this? Answer: > HR.hitters[6:7,2:3] Explanation: To address certain rows and columns, we use a matrix like addressing, with the rows listed first, then the columns. If we want multiple rows and columns, we can separate a range with a colon to get that data. Ref: 15 Easy Solutions to Data Frame problems - [click here]( --------------------------------------------------------------- [» 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]( : [SQL Server 2017 - Administration]( [how change name of listen]( - Hello everyone how can i change the name of listen alwayson AG I do not want to remove it and create it... [Replication]( - I really hope someone can help as this is driving me crazy! I'm trying to configure SQL Server Merge Replication with... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Merge Statement Issue]( - I have Table with date , itemid , amount 03/01/2019 1  45 03/01/2019  2  46 i got data from other table and my result have... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Administration]( [Rename server]( - Hi all! I have a server named : EDVARDWIN8\SQLEXPRESS2012 It should have the name SP-DESKTOP\SQLEXPRESS I tried to use the find function on... [Performance Issue]( - Hello All, I have a select query which is taking 3mins to get the results. I have verified the below. 1)Fragmentation and... [Security roles]( - All, Firstly I'm not necessarily asking for a complete answer. I'm happy to be pointed in the right direction to solve... [TDE and absolutely ridiculous amounts of corruption?]( - I've searched around quite a bit and can't find anyone else with this problem, but ever since we implemented TDE... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [Difference in checksum calculations.]( - All, Thank you for looking into my question. I am facing an issue with difference in checksum calculations for the same... [Can I hint a join order or can I hint the first index seek ?]( - Hi, I have a query that is not performing well. The execution plans starts with an index seek on the transaction... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [ODBC Driver 17 for SQL Server - Linked Server failure]( - I have a problem where my linked server works for about 12 minutes and then fails. Sorry I do not... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [insert to .XLSX fails if len(column) > 255]( - Error: ERROR MESSAGE:  String orbinary data would be trun [Select latest and 2nd latest date rows per user]( - I have the following query to select rows where the LAST_UPDATE_DATE field is getting records that have a date value... [why did WHERE EXISTS delete the contents of whole table?]( - the inner query returns only 36 rows, so I expected the DELETE WHERE EXISTS to delete only 36 rows. But... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [Top most manager name with out CTE]( - Hi, I need to get the Top most manager name with employee name. for eg:- epmid | empname | mgrid 1 emp1 Null 2 emp2 1 3... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [trimming SSNs]( - I have thousands of social security numbers I need to trim to leave only the last 4 digits... it is... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Filtering by date crashes report\query]( - Hi, I have the following sql query SELECT DISTINCT                         scheme.ABC.users_text_field, scheme.efg.PAL_efg, scheme.efg.PAL_oqty, scheme.efg.PAL_product, scheme.efg.PAL_date, &nb --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server Newbies]( [Sql Query to find invalid SSN Numbers]( - I would like to write a query that returns any Employees (id, ssn, and name) with an invalid SSN along... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [Creating a custom field and creating an expression to populate it]( - Hi, I'm looking to create a custom field and am not 100% sure if I can use an SQL statement to... [Font Awesome in Reports]( - Hello friends! We have a web application that uses font awesome for icons and in an attempt to keep things uniform,... --------------------------------------------------------------- [SQL Server 7,2000]( : [T-SQL]( [Use case and join in a stament]( - Question; After trying a lot of different options I wonder if the solution I want to use is possible. The T-SQL... 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](. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it [here](. --------------------------------------------------------------- This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: webmaster@sqlservercentral.com