|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
The Left Joins | |
I have a number of tables with this data:
CREATE TABLE CustomerLeft (CustomerID INT, CustomerName VARCHAR(20)) GO CREATE TABLE CustomerContact (CustomerID INT, CustomerEmail VARCHAR(100)) GO CREATE TABLE EmailDomain (CustomerEmail VARCHAR(100), domain VARCHAR(20)) GO INSERT dbo.CustomerLeft (CustomerID, CustomerName) VALUES (1, 'Steve'), (2, 'Andy'), (3, 'Brian') GO INSERT dbo.CustomerContact (CustomerID, CustomerEmail) VALUES (1, 'steve.jones@red-gate.com'), (2, 'awarren@sqlservercentral.com') GO INSERT dbo.EmailDomain (CustomerEmail, domain) VALUES ('steve.jones@red-gate.com', 'red-gate.com') GOIf I run this query, how many rows are returned? SELECT * FROM dbo.CustomerLeft AS cl LEFT JOIN dbo.CustomerContact AS cc ON cc.CustomerID = cl.CustomerID INNER JOIN dbo.EmailDomain AS ed ON ed.CustomerEmail = cc.CustomerEmail | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Carlo Romagnano) |
Even More OUTER APPLY What does the last query? declare @t table(i int) insert into @t SELECT * FROM (VALUES ('1') ,('2') ,('3') ,(NULL) ) AS V([i]) SELECT t.i --,MSG.msg FROM @t t OUTER APPLY ( SELECT msg = CASE t.i WHEN 1 THEN 'ONE' WHEN 2 THEN 'TWO' WHEN 3 THEN 'THREE' ELSE CAST(1/0 AS VARCHAR(10)) END ) MSG Answer: All rows returned and the command completes successful. Explanation: If the result of the subquery is not used in the SELECT list and nor is referenced in subsequent JOIN or subquery, than all the command is not parsed. It's the same as SELECT t.i FROM @t t APPLY is a usefull clause to run a subquery with changing parameters. Ref: APPLY - https://learn.microsoft.com/en-us/sql/t-sql/queries/from-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 2017 - Administration |
Troubleshoot a application job - Hi All, All of a sudden from yesterday, one of the application job which connects to SQL Server is failing intermittently. The job is scheduled every 15 mins. It sometimes gets executed successfully and sometimes fails. The user shares some generic SQLException and he feels that it is an issue with SQL Server. The front […] |
SSPI handshake - Why do we see this SSPI handshake failed with error code? Is this something to concern about? |
SQL Server 2016 - Development and T-SQL |
Best Practice vs Best Performance of Splitting a Table - Due to the nature of a specific table and the needs of different development groups we need to split a table into two separate but closely related tables. Without getting too indepth on the details of this the basic question came up. Is it best to use the identity key of the primary (or base) […] |
Clustered colunstore index vs Clustered date index on partitioned table - Hello everyone. Was just looking for some input on where I may get the best performance between implementing a clustered columnstore index or a clustered index on a date (ServiceStartDate) on a table also being partitioned by the ServiceStartDate Since I can only have one clustered index on the table I'm trying to figure […] |
Development - SQL Server 2014 |
Need to find max items within each set - In batches a certain action is done on each order# following specified sequence. Action is marked as +1 (in) and -1(out), within life of each batch there are situations when all orders are out. I need to know max number of items processed within each set, where the set is interval while some orders are […] |
QUERY HELP - Hi all I have the following table DECLARE @T table (A int ) insert into @T values (1) ,(0) ,(1) ,(0) ,(1) ,(2) ,(1) ,(0) ,(1) ,(2) ,(3) ,(2) ,(0) ,(1) The results for B column should look like Which means every time after A=0 it's a start for new group ranking . Thank […] |
SQL Server 2019 - Administration |
Downlevel CE for a stored proc - Hello, I am running a sp on sql server 2019. sp_blitzcache reports it using a downlevel cardinality estimator. My database is on compatibility lever 150 and there are no trace flags in the query. What else I could check to find out why it is using a downlevel cardinality estimator and how can I resolve […] |
below script with STUFF function executes 2014 but not in 2019 version - Select distinct LabOrderUnique, STUFF( (SELECT DISTINCT '^' + lotr1.TestName FROM [Ncs_conv_IntergyMM_Labordertestresult] AS lotr1 WHERE lotr1.LabOrderUnique = Lotr.LabOrderUnique FOR XML PATH('')), 1, 1, '') as TestName from [dbo].[Ncs_conv_IntergyMM_Labordertestresult] Lotr Above script i am able to execute in Sql server 2014 in 1 min but not able to execute in 2019 version 6hrs also. but same […] |
SQL Server 2019 - Development |
Using a comma seperated list in a where clause - I have a table and some incoming JSON as follows: DECLARE @DeviceSerialNumber NVARCHAR(100), @ChannelIDs NVARCHAR(50) DECLARE @Json NVARCHAR(MAX) SET @Json = '{"ChannelIDs":[11,12,17,14,13],"SerialNumber":"940860"}' SET @DeviceSerialNumber = JSON_VALUE(@JSON, '$.SerialNumber') SET @ChannelIDs = REPLACE ( (Replace (JSON_QUERY(@JSON, '$.ChannelIDs'), '[','')) ,']' ,'') select @DeviceSerialNumber, @ChannelIDs this give me the values Serial Number = 940860 and ChannelIDs = '11,12,17,14,13' I […] |
Alias for a table that can be used for the duration of a procedure? - I'm not sure if this can be done, but is there a way to use a table alias for the duration of a stored proc, including sub procedures? For example if you wanted to have an alias Customers that could refer to either the DailyCustomers table or the MonthlyCustomers table, perhaps set by a flag? […] |
Reporting Services |
Power BI Report Server with Oracle data source - We have an Oracle data source configured in Power BI Report Server. We have the Oracle 19c Client installed and configured. We used information from the Microsoft website to configure the server. Works great for a while but then we start receiving the following error when running a report using the Oracle data source - […] |
Cannot save the Page Setup to Landscape, or Export to PDF in Landscape - When I change the Page Setup to Legal/Landscape and narrow margins (.25 each side), it runs ok. But when I save the Report and Open it later, the page settings are back to the default Portrait/Letter and margins 1 everywhere. And even when the right Page Settings are in effect, the exported PDF still looks […] |
Powershell |
Import Xcel file - $File = "C:\CostLoads\Book1.xlsx" $Instance = "xxxxx" $Database = "DBA_USAGE" $fileName = [System.IO.Path]::GetFileNameWithoutExtension($File) foreach($sheet in Get-ExcelSheetInfo $File) { $data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable $tablename = $fileName + '-' + $sheet.Name Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -AutoCreateTable -Table $tablename } I'm trying to import my xcel file into SQL but receive […] |
SQL Server 2022 - Administration |
Add DB to a AG - complains about space on secondary - Dear all, I removed a DB from an AG a few days ago to shrink the logs. When I try to add again, I get this error: The share in the secondary has plenty of space, several times the space of the DB and the logs. Any idea or suggestion to fix this problem and […] |
SQL Server 2022 - Development |
Failed to retrieve server version - Hi Forum, I'm trying to set up Visual Studio (VS) 2022 & SQL Server (SS) 2019 to create SSIS, SSRS & SSAS Projects. I've found that SSIS won't Install in VS if SS is installed, so I've installed VS first, gone to the extensions & installed the SSIS/SSRS & SSAS software. Then I've installed the […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |