Reaping the benefits of the Window functions in T-SQL (SQLServerCentral 04/02/2019)

 [SQL Server Central]( The Voice of the DBA  The Counter Offer – Part 1 Today we have a guest editorial from Andy Warren as Steve is out of town. You’ve been in your job for a while when a former colleague or recruiter calls to see if you’re interested in something new, something that pays better than your current job. Or maybe you’ve grown bored or frustrated and you’re ready for a new challenge. Either way, you update your resume and start interviewing until the stars align and you get an offer – a good one. Now what? You schedule time with the boss, still feeling the thrill of victory and the validation of someone else wanting to hire you and at the same time dreading the “I’m leaving” conversation. It usually goes better than you might expect because it’s not the first time or the last time they’ll lose an employee. They’ll work through the details of a transition plan with you and they will probably ask what made you decide to move on. That’s part curiosity, part wanting to know what they might do better, and part wondering if there is a chance to change your mind. If you’re in that “I’m done, moved on, no looking back state” of mind then it’s just a matter of counting down the days. But what if it’s not quite that simple? What if after you explain what you’re leaving the boss says “what if we could fix that?”? Ah, now you’re in the soup. They come back a few days later with a counter offer. More money, different challenges, training money, new job title – whatever they think will fix the reason you were leaving. What do you do now? Would you stay if they matched the offer from the new job? Or if it was close? What if it was $10k more? If you say thanks but no thanks then you can resume the countdown. But what if you accept the counter offer? Now you have to go back to the new employer and say that you’ve changed your mind. That’s never fun because they feel – correctly – that you used them for leverage. But wait, there’s more! What if they raise their offer, the counter-counter offer? Does your head hurt yet? It’s never as simple as a formula. Take some time to understand what matters to you before you start looking and you’ll find that navigating the offer/counter-offer is less stressful, but still not easy. Andy Warren [Join the debate, and respond to today's editorial on the forums](  [Redgate Database Devops](  Featured Contents [Reaping the benefits of the Window functions in T-SQL]( SQLServerCentral.com | SQL Server 2012 Exploring the potential benefits of the T-SQL Window functions (OVER, LAG, and LEAD) in this piece. You can learn how to achieve up to 50% performance boost by replacing a single function call. [Enforce SSL for connecting to AWS RDS Instance of SQL Server]( MSSQLTips.com | SSL Learn how to force all SQL Server connections to an AWS RDS instance to use SSL encrypted connections. [Have your say on the state of SQL Server monitoring]( Redgate | monitor In this blog, we review the key insights from last year’s State of SQL Server Monitoring report. One year on, how has this changed? Read the blog to find out how you can help us define the state of 2019 plus a chance to win a $250 Amazon voucher.   Question of the Day Today's question (by DinoRS):  BITting out the impossible? I have the following Table: CREATE TABLE #Bitwize ( bitcol bit) I add some value to this table like this: INSERT into #Bitwize VALUES (1) Then I check what I get from this: SELECT * from #Bitwize I then do some fancy stuff to the table like this: UPDATE #Bitwize SET bitcol = bitcol ^ 1 I ask myself if and what has happened to the statement and table so I issue this: SELECT * from #Bitwize What are my results? Think you know the answer? [Click here]( and find out if you are right.  [Redgate SQL Compare](   Yesterday's Question of the Day (by Steve Jones - SSC Editor) Sneakers Quotes Sneakers was a thriller movie released in 1992. Finish this quote from the movie: "The world isn't run by weapons anymore, or energy, or money. It's run by little ones and zeroes, little bits of data. It's all _________." Answer: just electrons Explanation: The answer is just electroncs. Happy April Fools Day. Ref: Sneaker's Quote: [( 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](. --------------------------------------------------------------- [Converting a connections to project in all packages]( - Hello Using SQL 2016 I have inherited a project that has a number of packages Rather than create a project connection, seperate identical source and destination package connections have been created So, they all have the same name but are specific to the package I would like to have a project source and destination connection […] [Using the OUTPUT CLAUSE in a INSERT statement.]( - I have an export table from another SQL-server database. This table contains an identity. I want a new identity and using the OUTPUT clause of the INSERT statement, I want to create a temp table, which holds both the OLD identity and the NEW identity. This was possible with the MERGE statement. How can I […] [setup replication for near real time reporting]( - I am thinking about using transnational replication on a handful of tables for real time external 3rd party feeds, reporting and various stats screens. Current setup consists of mirroring (synchronous std edition) for HA and 15min Log shipping for offsite DR. Does anyone know of any potential issues in adding replication into the mix? I’ve […] [Splits file attachments in they exceed maximum limit]( - Hi I’ve developed a package where I loop through files from a specific path and send them as an attachment per location, each location has it’s own email address where the files are sent to. The file size attachment limit should be less than 8MB, if the total attachments is greater than 8MB i want […] [Can see all images when uploading an image]( - I was readding my avatar to the site, and notice you can see every image in there. I assume that’s not intended? Also, I don’t appear to be able to paste images into the textbox any more? Having to save the image and then upload seems like a (rather large) loss of functionality and lowers […] [Survey]( - Each time I open the browser at the SSC homepage I am again offered the opportunity to take part in the Flyway survey, previously I seem to recall a survey remembered that I had done this once and so couldn’t do so again; not sure if this was the use of cookies or a simple […] [New to SQL, easy question]( - Hello, I apologize for the question but I just can’t seem to get the outcome. I’m trying to have the LastName Value in the Professor table in alphabetical order. Here is what I have so far: SELECT Professor.LastName, Professor.FirstName, Department.DeptName, * from Professor order by LastName asc; From Department INNER JOIN Professor ON Department.DeptCode = […] [What interview questions have you heard?]( - Hello, I am a data analyst but my current job does not require much SQL. I am basically self-taught from different online courses and books, but still feel I lack a full understanding to perform well in technical interviews. Could anyone share example questions you have seen in an interview? Or preferred prep tools? [How to resolve SSIS solution open message : The package .. is missing ..]( - How to resolve SSIS solution open message : The package .. is missing .. This package has been skipped, and project will continue to load. This must be a quite common issue / question .. A developer decides to change his mind about which solution a dtsx package should reside in and moves it on […] [Gateway timout]( - Have received a gateway timeout error several times, the error also says please try again later! Tried to get screenshot but unable to do so. Site stats suggest not a lot of activity when this occurred. [Exercise problems]( - I couldn’t find a better forum to post this to. I am wondering does anybody know of any websites or sources that have exercise problems (with solutions) that are free and also cost money? [SQL Server Instance Copy]( - I am trying to find the terminology or a point in the right direction about an idea I had. I have a production server, and a new staging server. I know how to do backups and restores of databases. That’s easy. My question is, is there a way to do a whole copy of an […] [Several issues with 2019-03-30 site migration]( - (this was originally posted to but that fell into a bad state and doesn’t show up anywhere, nor do its replies; this is a re-post of the items that were not subsequently posted individually ; most recently I reposted it as and it was fine until I edited it only 30 seconds after […] [Case in Where]( - Hello community, I have build this query and what i want is : 1. When fo.dprocesso not null then return all documents with this value 2.If fo.dprocesso is null then return all documents with the supplier name like ( fo.nome ). Declare @Process as varchar(10) Declare @Suppliername Varchar(60) Set @Processo = ” Set @Suppliername […] [Not granting permission vs. checking Deny]( - For any given permission such as ‘Select’ if I don’t checked ‘Grant’ the user cannot do a SELECT statement. How is this any different than checking ‘Deny’ for the permission? In other words, if I leave all checkboxes unchecked how is that any different than if I checked the ‘Deny’ checkbox?   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -