|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Incremental Statistics | |
I have run this on SQL Server 2022 for the Sales database:
ALTER DATABASE Sales SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON)I then run this in the Sales database: USE Sales GO CREATE STATISTICS CustomerStats1 ON dbo.Customer (CustomerKey, EmailAddress) WITH INCREMENTAL = OFFThe dbo.Customer table is partitioned. How are statistics created? | |
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) |
The LAGging NULL I have this data in a SQL Server 2022 table: player yearid team HR Alex Rodriguez 2012 NYY 18 Alex Rodriguez 2013 NYY 7 Alex Rodriguez 2014 NYY NULL Alex Rodriguez 2015 NYY 12 Alex Rodriguez 2016 NYY 9 If I run this code, what are the results returned in the hrgrowth column? SELECT player , yearid , hr , hr - LAG (hr, 1, 0) IGNORE NULLS OVER (ORDER BY yearid) AS hrgrowth FROM dbo.playerstats; Answer: 18, -11, NULL, 5, -3 Explanation: The results are: 18, -11, NULL, 5, -3 The LAG has a default of 0 if a null is returned, so the first value is 0. The third value is null because a NULL for 2014 subtracting anything returns NULL. The 2015 row, however, shows a 5 as the IGNORE NULLs column for LAG() ignores the NULL and goes back to the 2013 value for the LAG function. Ref: LAG - https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16 |
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 2016 - Development and T-SQL |
Merge rows in SQL - I have a table like below ID Ident source val opendate closedate IsActive -------------------------------------------------------- 101 euid01 File01 x010 01-01-2023 01-01-2999 1 101 euid02 File01 x111 01-01-2023 01-01-2999 1 101 euid02 File01 x222 01-01-2023 01-10-2024 0 102 euid01 File11 x010 01-01-2023 01-01-2999 1 102 euid02 File12 x333 01-01-2023 01-01-2999 1 102 euid02 File10 x444 01-01-2023 01-10-2024 […] |
Development - SQL Server 2014 |
How can I tell if an UPDATE command updated any rows? - I am calling an Update routine from C# and need to determine if the Update routine found a matching row to update. If it does not, I call the Insert Routine. I was hoping that if no row was updated, I would get an error which would tell me that I need to do the […] |
SQL Server 2019 - Administration |
It is difficult to learn Oracle. - I am a new student to learn Sql syntax. Is there any skills I need to master first? |
SQL Server 2019 - Development |
problem with PIVOT Table - Thanks in Advance! I'm having trouble grouping all counts and sums on one line for each county which should not look like (Code attached) Note one county is repeated and most are like that: County MH SA DD Cost Camden 0 0 1 152.88 Pitt […] |
Pivot Using SQL - I need some help with pivot. For the following data set: and produce an output like this: |
Iterative query to sum a colum relating to dates. - Hi, I have a simple table with 5 columns as follows with about 80,000 rows, I wish to sum the Hours for each Client at 2 week intervals. In other words starting at a particular EndDate, sum the Hours for each client for 2 week period. ID, ClientID, EndDate, Hours 1 , […] |
how to force SS to run certain SP in parallel? - hi everyone I have a SP that is run in SSIS. That particular SP is basically running a bunch of SP. See below for code. SP1 and SP2 are independent of each other so they can definitely be run in parallel. They all read data from the same table but each writes to a different […] |
cannot read the next data row for dataset x - Hi, a peer asked me today to help on this ssrs error. There is a lot going on in this report but the dataset in question uses a table meant just for ssrs and is filtered in the report by the userid of the person running the report. When i run the query with a […] |
SQL Azure - Development |
Copying table data efficiently - Environment: Azure db with P15 (4000 DTU) Table 1: Having 90 coluumns of which 25 columnd encrypted , having approximately 60 million records Table 2: Exact similar structure as Table 1 with no records Requirement : Need to copy data from Table 1 to Table 2 In quicker time and least usage of DTUs. workdone: […] |
Reporting Services |
Report server errors - http://servername/reports/browse/ could not load folder contents http://servername/reportserver The server principal "NT SERVICE\SQLServerReportingServices" is not able to access the database "ReportServerTempDB" under the current security context. How do I fix this ? Does this have anything to do with SPNs ?https://learn.microsoft.com/en-us/sql/reporting-services/report-server/register-a-service-principal-name-spn-for-a-report-server?view=sql-server-2017 |
Reporting Services (2019) - Changing Service Account - This is something that happened while investigating a problem - so not impacting me any more - but I wonder what I'm missing. When trying to change the Service Account in the Reporting Services Configuration Manager from built-in to a domain account, it fails to connect to the database with the error: A connection could […] |
Design Ideas and Questions |
Should testing of Business Report changes be allowed in Prod? - When I arrived at the company 2.5 years ago, the environments were a mess! Dev crap in Prod, Prod stuff in dev, many databases didn't have a Dev version, etc. It has been a grueling marathon, but the environment is mostly stabilized. I've even set up a PreProd environment where users can test deployments or […] |
MySQL |
MySQL joining - a tricky problem from a newby - Hi, I'm not even sure whether this is possible. I've three tables, one containing a location ID, and a location name. The second table is a users table, which contains the kind of information you'd expect, i.e. name address etc. The third table is a transactions table containing rows describing transactions which can be described […] |
SQL Server 2022 - Administration |
Blocking Connection to Server - Good Afternoon, We recently procured a service management software for our company, which uses SQL server 2019( 15.0.2125.1) as backend. The client application uses sql usernames to login into the application . I find this as a security issue, as any internal user can directly connect to the server either through ODBC or through SSMS […] |
Migrating standalone database to always-on cluster. - I am planning to migrate a currently standalone database to SQL Always On. This would be an OS & SQL upgrade & migrate activity to an entire new box of servers running on latest Windows Server & SQL Server. I have never perform this configuration before and will be referring to steps which is stated […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |