Laden...
|
|
|
|
|
|
| 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 ) GOI 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 salesI 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 |
| 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/ |
| |
| ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. [email protected] |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Laden...
Laden...
