T-SQL Decommenter Part I (SQLServerCentral 11/8/2018)

[SQLServerCentral - www.sqlservercentral.com]( A community of more than 1,600,000 database professionals and growing Featured Contents - [T-SQL Decommenter Part I]( - [Azure EA Financial Reporting and Granular Access to Data Through the Enterprise]( - [Help Redgate improve Data Masker for SQL Server?]( - [The 5 Scariest Moments for a SQL Server Developer]( (From the SQLServerCentral Blogs) - [Signing Stored Procedures That Access Multiple Databases]( (From the SQLServerCentral Blogs) Featured Script - [view generator]( The Voice of the DBA The Cost of Switching This editorial was originally published on Jan 20, 2015. It is being republished as Steve is at the PASS Summit. I read the book [Space Prison]( a few years ago. It's about a group of humans, attacked and left as prisoners on a harsh planet. There's a few hundred of them, many dying in the first few weeks. They must fight just to survive against the planet's weather, as well as it's plant and animal life. However they persevere and get revenge on the race that left them there. Not the survivors themselves, but their descendents, hundreds of years later. It's a story about a group of people truly investing for the future. Not one they'll see, but one their children's children's children will experience. I thought about that when I saw a post from Ray Terrill asking for AlwaysOn Availability Groups to be a part of Standard Edition in the next version of SQL Server. He makes a few arguments, but one that is interesting is the idea that the other possibilities for databases with good HA/DR cost less. I certainly agree that the cost of switching, and retraining (or rehiring) is high, it's all about how you approach things. If a company really wants to play long ball, to look to the future and start making decisions for not the next year or two, but for ten years in the future, then is the cost of switching high? Imagine I'm a medium sized company with 500 employees. I have some IT staff (10-20) and say 20 SQL Server standard instances for various things. Replacing one SQL Server that provides basic database services now with something like PostgreSQL might slow me down in development for a couple years, but my staff would gain experience, and I'd have options. I might easily be able to replace more of, or all of, my instances in the next five years. Every decision from then on might be whether I get value specific to SQL Server, or can I use another platform that costs less. I know that overall SQL Server provides fantastic value and there isn't anything else that can replace it wholesale, but the base level of database services that many applications need can be provided by other products, and unless Microsoft gives us good value in Standard Edition, I think it's a matter of time before the people writing the checks start to think about other options. Good security, encryption, and HA/DR are becoming not "nice to haves", but necessary for many applications. However not so necessary that I think I want to pay Enterprise Edition prices for most of my applications. Steve Jones from [SQLServerCentral.com]( Join the debate, and [respond to today's editorial on the forums]( ADVERTISEMENT [SQL Source Control]( How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. [Learn more]( [SQL Change Automation]( CI/CD for your SQL Server database Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. [Try it free]( Featured Contents  [] [T-SQL Decommenter Part I]( Toby Ovod-Everett from [SQLServerCentral.com]() Introduction to a T-SQL query that remove comments from T-SQL code[More »]( ---------------------------------------------------------------  [] [Azure EA Financial Reporting and Granular Access to Data Through the Enterprise]( Additional Articles from [SimpleTalk]() With the billing data from your Azure Enterprise Agreement collected into one database, it is still necessary to break down the costs and provide access to the correct departments and individuals. This is important for chargeback and further analysis. In this article, Feodor Georgiev demonstrates how to provide granular access to the billing data.[More »]( ---------------------------------------------------------------  [] [Help Redgate improve Data Masker for SQL Server?]( Redgate are looking for people to help them get a deep understanding of compliance and SQL Server data masking requirements in order to ensure they can provide a world class solution. In return for your feedback you’ll have early access to new releases, insights into new features and their ongoing support to get up and running. [More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [The 5 Scariest Moments for a SQL Server Developer]( Bert Wagner from [SQLServerCentral Blogs]( While families and friends are scaring each other this Halloween week with stories of ghosts and ghouls, I thought it’d...[More »]( ---------------------------------------------------------------  [] From the SQLServerCentral Blogs - [Signing Stored Procedures That Access Multiple Databases]( david.fowler 42596 from [SQLServerCentral Blogs]( A while back I wrote a post, Digitally Signing a Stored Procedure To Allow It To Run With Elevated Permissions Recently Manish...[More »]( Question of the Day Today's Question (by Steve Jones): I have a Sales database that is in use by various clients. I want to set this database to the restoring state while we do some work and then make it available for clients again later. I do not want to restore the database from a backup. What can I do? 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: Backup. We'd love to give you credit for your own question and answer. To submit a QOTD, simply log in to the [Contribution Center](. ADVERTISEMENT [Expert Scripting and Automation for SQL Server DBAs]( Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. [Get your copy from Amazon today](. Yesterday's Question of the Day Yesterday's Question (by ben.brown 83879): I have created a few tables: CREATE TABLE table_a (id CHAR(1)) ; GO INSERT Table_a (id) VALUES ('A'), ('B'), ('C') ; GO CREATE TABLE table_b (id CHAR(1)) ; GO INSERT Table_b (id) VALUES ('A'), ('B') ; GO CREATE TABLE table_c (id CHAR(1)) ; GO INSERT Table_c (id) VALUES ('A') ; GO I then run this query: SELECT RCOUNT = COUNT(*) FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.ID = TABLE_B.ID INNER JOIN TABLE_C ON TABLE_B.ID = TABLE_C.ID ; What is returned for RCOUNT? Answer: 1 Explanation: The value of 1 is returned. This is a very simple query but throws a lot of people who think the answer is 3. In multi-join queries, SQL server incrementally joins two sets together. In this example, Table_A is left joined to Table_B creating set AB (with 3 rows). Set AB is then inner joined to Table_C, leaving only 1 row remaining. Some people will read it as Table_A LEFT JOINING to everything below it. For example, Table_A (3 rows) LEFT JOINS to an already formed set BC (Table_B INNER JOINED to Table_C), leaving all 3 rows intact. This is incorrect. --------------------------------------------------------------- [» Discuss this question and answer on the forums]( Featured Script [view generator]( Flashx from [SQLServerCentral.com]() Exemple (Target Schema RLS must exists) : EXEC [SEC].[usp_generate_view_script] @SourceSchema = N'dbo', @TargetSchema = N'RLS' [More »]( 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]( [Secondary DB in restoring state]( - Hi, Please advise. I have  two sql server instances..A01 is prod which is online and running. A02 which is secondary and now... [What events (list) are initiative a failover on AlwaysOn?]( - I'm looking for a link from the Microsoft website what is describe exactly (with a list) when will happening a... --------------------------------------------------------------- [SQL Server 2017]( : [SQL Server 2017 - Development]( [Combine two tables based on FY year]( - My sample data looks like below queries . mytable1 has data for all products for all quarters currently. I have shown... [Column names on INNER JOINS]( - In my code below I have Invoices.InvID [Using SUM() In CASE Statement / Best Practices]( - All, I'm working on building a semantic layer within an ETL database so as to improve our end to end performance.... [how to get rows from two datetime fields with hours on morning time.]( - I have this problem How can I get rows in this example. We are looking for crimes in our database... --------------------------------------------------------------- [SQL Server 2016]( : [SQL Server 2016 - Development and T-SQL]( [I am looking for help on how to write this SQL Statement]( - It appears to be an uncommon task. I am looking for help on how to write this SQL Statement. I am... [SSIS Toolbox Different on 2 Servers]( - I have 2 servers running SQL 2016, but 1 server seems to have "Lost" toolbox items that were there before.... [While Loop Stored Procedure]( - Hi, Can help me with an issue I am facing. I need to create a single stored procedure that uses the... [Filtered non-clustered index showing filters for one person but not for another person]( - I'm working with the dba of a server I often pull data from to tune performance of queries against a... [Execute a Stored Procedure against every row insert in Table]( - I have around 200 tables and we have insert in each table with in each seconds. so there are around 90... --------------------------------------------------------------- [SQL Server 2014]( : [Administration - SQL Server 2014]( [Slow run time]( - I have a query which is running slow for approx 15 mins, however, the slow run time has no impact... --------------------------------------------------------------- [SQL Server 2014]( : [Development - SQL Server 2014]( [does use of large datatypes where much smaller ones will do, affect performance?]( - when a table has one hundred columns, where 40 of them have datatypes far exceeding the max(len()) of data actually in them, will this... [Recursive CTE for Supervisor Hierarchy WITH Effective Date of Reporting Relationship - Updated with DDL and Sample Data]( - Okay, I'm editing my request per Lynn's suggestion. Below, in the comments, I'm posting the DDL to generate the sample... --------------------------------------------------------------- [SQL Server 2012]( : [SQL 2012 - General]( [RPC_Out enabled or disabled?]( - Hi, I am trying to check if RPC_OUT is enabled or not. It my understanding that this is only enabled for linked... --------------------------------------------------------------- [SQL Server 2012]( : [SQL Server 2012 - T-SQL]( [Returning a record when a missing value in a table exists.]( - Hello all,  I have a scenario where I need to show a currency exchange in a result set. The problem I've... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 - General]( [Write Conflict]( - Hi all, So this issue is driving me bat guano insane. I have MS Access linked to SQL tables. 1. There is... --------------------------------------------------------------- [SQL Server 2008]( : [SQL Server 2008 Performance Tuning]( [Very erratic performance in last few days.]( - I apologize for such a general question, and there is not enough info for a detailed answer, but ANY general... --------------------------------------------------------------- [Reporting Services]( : [Reporting Services]( [Adjusted SQL Agent job schedule for SSRS report keeps reverting back to original timing!]( - I have an SSRS SQL Agent job running for an SSRS report (email subscription) which was set to run every... --------------------------------------------------------------- [Data Warehousing]( : [Integration Services]( [SSIS packages]( - Hi All, I am new to SSIS and working on a difficult task and need suggestions or advices. How should I... 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