Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Take a few minutes and do something fun for yourself

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.

A Digital Wish Card for Ken

I saw a note yesterday on Twitter that Ken Fisher (@sqlstudent144) was in the hospital for emergency surgery. I'm asking that anyone that knows Ken, has heard him speak, read his blog, or done one of his crossword puzzles, leave a positive note, thought, or well wish in the thread for this editorial.

I've gotten to know Ken over the last few years, and been blessed to see him in person and get a hug. He's a wonderful man, and we've had a number of private conversations on various topic. Ken is someone that I communicate with every month or two, even though we rarely see each other in person.

Ken is also an amazing blogger, providing all sorts of information on SQL topics, regular homework and more. He is one of the more popular bloggers that is on SQLServerCentral. He has taught many people how to improve their SQL Server skills. He has also entertained us with a number of SQL related crossword puzzles over the years.

I hope Ken is doing well. I'm sure we'll get an update at some point, but I thought it might be nice to have a long list of comments on a digital card of sorts for Ken and his family. Please consider writing something in this thread and wishing Ken a speedy recovery.

Ken, my brother, I hope you recover quickly and I look forward to seeing you sometime soon in the future for another hug.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
  Featured Contents

Business Reasons For Implementing Power BI

Brandon Forest from SQLServerCentral

Data is stored in heterogenous environments in many organizations. Enterprise reporting for management and other departments is created using a variety of tools.  There are Excel Workbooks, SSRS Reports, Tableau Dashboards, flat files, data streams from MySQL databases, etc.  Each data extract or report is created by a BI or DB Development Team member.  Often […]

Automating Oracle Database Deployments using the Hybrid Approach

Additional Articles from Redgate

Learn how to use the 'hybrid' approach in the Deployment Suite for Oracle to automate Oracle database deployments. In this approach, the team maintains the current state of the database in version control during development, then, at key stages, generates and tests the migrations script that will be used to deploy the new database changes safely.

Monitoring SQL Server with PowerShell Core Object Setup

Additional Articles from MSSQLTips.com

In this series of tips we will introduce PowerShell scripts with T-SQL code that can be used to monitor your SQL Server environment. This first set of scripts creates the core objects needed for this solution.

From the SQL Server Central Blogs - Upload files to ADLS Gen2

James Serra from James Serra's Blog

I discover a small new feature the other day, but a very useful one. Previously, there was no way to upload files to an ADLS Gen2 storage account via...

From the SQL Server Central Blogs - Collecting custom parameters/variables for Azure Data Factory deployment

Rayis Imayev from Data Adventures

(2020-June-22) It's a noble act to develop a very cool database solution that will bring value to your customers. In addition to this, you can help...

 

  Question of the Day

Today's question (by Evgeny Garaev):

 

Memory-Optimized Table Truncation

There were several improvements in the SQL Server 2019 for memory-optimized tables. Does SQL Server 2019 support table truncation for memory-optimized tables?

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)

Getting Subtotals

I am testing some aggregations and have this setup code:

CREATE TABLE ProductSales
( SaleID INT IDENTITY(1,1) NOT NULL CONSTRAINT ProductSalesPK PRIMARY KEY
, ProductCat VARCHAR(100)
, ProductName VARCHAR(100)
, SaleDate DATETIME
, SaleTotal NUMERIC(10,2)
)
GO
INSERT dbo.ProductSales
    (ProductCat, ProductName, SaleDate, SaleTotal)
VALUES
    ('Laptop', 'HP Spectre', '2020-01-02', 100 ),
    ('Laptop', 'Lenovo Thinkpad', '2020-01-05', 200 ),
    ('Laptop', 'AlienWare', '2020-02-02', 300 ),
    ('Mobile', 'iPhone', '2020-02-12', 50 ),
    ('Mobile', 'Samsung Galaxy', '2020-03-12', 100 ),
    ('Watch', 'Apple Watch', '2020-02-22', 200 ),
    ('Watch', 'Garmin Forerunner', '2020-02-23', 400 )
GO

I want to run a sum by category and month showing the category and month, with

  • total for that category for each month with sales
  • total for the category
  • total sales

I want no extra rows. I have this code:

SELECT 
	ps.ProductCat
	, MONTH(saledate)
	, SUM(ps.SaleTotal) AS TotalSales
 FROM dbo.ProductSales AS ps
 GROUP BY xxx(ps.ProductCat, MONTH(saledate))

Which keyword(s) replaces the XXXX and gives me less rows in the result set?

Answer: ROLLUP

Explanation: Only CUBE and ROLLUP are valid here. ROLLUP will return less results. Ref: GROUP BY - https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

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
Using two different backup products for each of data and log -   To shorten a long story, my organisation has decided to have a vendor perform daily full backups using "Backup Product A" whilst the DBA will perform transaction log backups using "Backup Product B". The recommendation to use 1 system overall was overruled. In the event a restore is required it will be the DBA's […]
SQL Server 2017 - Development
Update table and then executeJob in loop - I need to write a query which will first update the table, then execute the job and if the job is successful, again update statement. This would be a loop. this is how it looks DECLARE @dttm datetime DEClare @datetable table (dttm datetime) insert into @datetable values ('2017-01-01 00:00:00.000'), ('2018-01-01 00:00:00.000'), ('2019-01-01 00:00:00.000') DECLAre Upddate […]
Adding NOT NULL to column with existing rows causes page bloat - Hi, I'd like to know why the page count is almost exactly doubled for a table with existing rows, having updated the column in question with values so none are NULL (this step also causes double pages, but I understand why, fragmentation becomes 99% so i REBUILD clustered index), then running ALTER TABLE ... ALTER […]
SQL Server 2016 - Administration
How to change the Scan to Seek and optimize - Hello, Attached sample query and actual plan, I want to further optimize the query and change the scan to seek? Do I have to create an IDX or modify the query in this case?  Please let me know thoughts  
Compare sql-2012 and sql-2016 settings - Hi everyone, W are just doing a migration from sql 2012 enterprise  to sql 2016 standard. This involves migrating lots of sql jobs running on sql 2012. I would like to make sure that performance doesn't degrades after we migrate. I would like to check and compare all configurations ( Sever  as well as DB […]
is Clustered Columnstore index UNIQUE by default? - Or should I explicitly specify 'CREATE UNIQUE CLUSTERED COLUMNSTORE INDEX....' ? If I don't specify UNIQUE,  can a clustered index actually be non-unique..??
Failover Cluster Instance Share - Hi, I am using AlwaysOn Availability Groups in SQL Server 2016 with a primary and secondary replica. I have a Quorum file share witness on a separate file server. This file server needs to be rebooted, so I'm wondering what I need to consider before rebooting this box... Anyone have experience with this? Also, I […]
SQL Server 2019 - Administration
Pintable into cache - Pintable into cache to avoid fragmentation I have a table(OLTP database) which gets fragmented every day and it goes through the defragmentation process every week.Which means I am doing the same process over and again every week. Can I pin such tables in the memory permanently or for a specific period of time and write […]
Stretch database - Is there anything similar to a stretch database in mysql where an on-premise mysql database can be stretched into aws cloud.Similar to sqlserver stretch database on-premise being stretched into sqlserver in azure. Thanks    
SQL Server Newbies
stored procedure to delete user and login - I was trying these but it does not work: USE [MyDatabase] GO /****** Object: StoredProcedure [dbo].[sp_deletelogin] Script Date: 4.7.2020 3:29:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_deletelogin] @szLogin varchar(50),@defaultdb varchar(50) as if not exists ( select 0 from sys.sql_logins where name = @szlogin) begin EXECUTE AS LOGIN='sa'; exec ('delete login […]
Reporting Services
Upgrade SSRS server but not DB engine? - I need to upgrade the SSRS report server so that it is compatible with Chrome/Edge. However the database server where the SSRS DBs live is not at a stage where I can upgrade it yet as there are 3rd party vendor DBs that need to be investigated. Is this possible? The report and DB servers […]
General
Beginner needing help - Removing post
Integration Services
Connecting To Quickbooks via SSIS - Hello everyone, Been doing some googling trying to find some info on connecting to Quickbooks desktop from SSIS.  Everything I'm getting back is third party options where I have to pay.  Trying to avoid that if I can.  Seems like some sort of ODBC driver or something should be available for this where I can […]
COVID-19 Pandemic
Daily Coping 6 Jun 2020 - Today’s tip is to think of something you’ve always wanted to do and never tried. http://voiceofthedba.com/2020/07/06/daily-coping-6-jul-2020/
Daily Coping 3 Jul 2020 - Today’s tip is to thank a friend for the joy they bring into your life. http://voiceofthedba.com/2020/07/03/daily-coping-3-jul-2020/
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -